Create a table with an option to enable it for profile
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.