Create a table with an option to enable it for profile

NOTE
The SQL query provided below assumes the use of a pre-existing namespace.

Use a Create Table as Select (CTAS) query to create a dataset, assign datatypes, set a primary identity, create a schema, and mark it as profile-enabled. The example SQL statement below creates a dataset and makes it available for Real-Time Customer Data Platform (Real-Time CDP). Your SQL query will follow the format shown in the example below:

CREATE TABLE <your_table_name> [IF NOT EXISTS] (fieldname <your_data_type> primary identity namespace <your_namespace>, [field_name2 <your_data_type>]) [WITH(LABEL='PROFILE')];

The data types supported are: boolean, date, datetime, text, float, bigint, integer, map, array, and struct/row.

The SQl codeblock below provides examples to define struct/row, map, and array datatypes. Line one demonstrates row syntax. Line two demonstrates map syntax, and line three, array syntax.

ROW (Column_name <data_type> [, column name <data_type> ]*)
MAP <data_type, data_type>
ARRAY <data_type>

Alternatively, datasets can also be enabled for profile through the Experience Platform UI. For more information on marking a dataset as enabled for profile, see the enable a dataset for Real-Time Customer Profile documentation.

In the example query below, the decile_table dataset is created with id as the primary identity column and has the namespace IDFA. It also has a field named decile1Month of the map data type. The table created (decile_table) is enabled for profile.

CREATE TABLE decile_table (id text PRIMARY KEY NAMESPACE 'IDFA',
            decile1Month map<text, integer>) WITH (label='PROFILE');

On successful execution of the query, the dataset ID is returned to the console, as seen in the example below.

Created Table DataSet Id
>
637fd84969ba291e62dba79f
(1 row)

Use label='PROFILE' on a CREATE TABLE command to create a profile-enabled dataset. The upsert capability is turned on by default. The upsert capability can be overwritten using the ALTER command, as demonstrated in the example below.

ALTER TABLE <your_table_name> DROP label upsert;

See the SQl syntax documentation for more information on the use of the ALTER TABLE command and label as part of a CTAS query.