Data Distiller | A Guide to Create Ad hoc Schema using SQL

[#1]

A Guide to Creating Schema Using SQL

Creating an schema via SQL is a powerful way to streamline your data queries within the platform. This guide will take you through the process step-by-step, showing you how to set up XDM-compatible schemas using SQL constructs directly within your queries in query service. By following these SQL syntax, you’ll be able to write efficient, clear queries that simplify the schema creation at runtime.

Getting Started

Before beginning, ensure you understand the data structure you’ll be working with, as this will guide your schema creation.

Types of Adhoc Schema Creation

Type 1: Basic Adhoc Schema

Use this approach to create a simple schema structure.

Create TableSchema demo_query_schema1 AS (name1 text ,age1 text);

Type 2: Adhoc Schema with Identity Map (With or Without Primary Identity)

This version allows you to designate a primary identity namespace, making it compatible with identity mapping.

Create TableSchema demo_query_schema2 AS (name2 text PRIMARY IDENTITY NAMESPACE 'IDFA',  age2 text) with (label='PROFILE');

Type 3: Schema with Identity Name and Description on Column

A. With Primary Identity

If you need an identity map and primary identity on specific columns, use this syntax:

Create TableSchema demo_query_schema4 AS (name3 text PRIMARY IDENTITY NAMESPACE 'IDFA',  age3 text, identityMap fieldgroup) with (label='PROFILE');

B. Without Primary Identity

If you need an identity map on specific column, use this syntax:

Create TableSchema demo_query_schema5 AS (name4 text, age4 text, identityMap fieldgroup) with (label='PROFILE');

Type 4: Schema with Existing Field Groups

To create a schema using predefined field groups:

Create TableSchema demo_query_schema7 AS (name5 text, `Loyalty Details` FIELDGROUP loyalty.program PRIMARY IDENTITY NAMESPACE 'IDFA' ) with (label='PROFILE');

Type 5: Adding Primary or Composite Keys

This schema type allows you to define primary or composite keys for more complex identity needs.

Create TableSchema demo_query_schema11 AS (name6 text, `Loyalty Details` FIELDGROUP (loyalty.program, loyalty.status) PRIMARY IDENTITY NAMESPACE 'IDFA' ) with (label='PROFILE');

Conclusion

By using SQL to create schemas, you can achieve flexibility and precision in your SQL queries. This guide serves as a reference for different schema structures, enabling you to design schemas that fit a variety of data needs. Start with the basics and expand to more complex setups as your data curation requirements becomes more complex.

Author: @_Sameeksha_