Data API : SQL queries

This document contains sample SQL queries which can be used to create tables for each API included in the Data API suite.

1. Conversations

   1
   create or replace TABLE DATA_API.PROD.DATA_API_COPILOT_CONVERSATIONS (
   2
   	TS_LAST_UPDATED VARCHAR(16777216) COMMENT 'Last updated timestamp of the record',
   3
   	CONVERSATION_ID VARCHAR(16777216) COMMENT 'Id identifying the unique conversation',
   4
   	USER_RECORD_ID VARCHAR(16777216) COMMENT 'Unique id of the user',
   5
   	TS_CREATED VARCHAR(16777216) COMMENT 'The timestamp of the first interaction in the conversation',
   6
   	CONVERSATION_ORIGIN VARCHAR(16777216) COMMENT 'Route/origin of the conversation - DM, Ticket, Channel, Notification',
   7
   	PRIMARY_DOMAIN VARCHAR(16777216) COMMENT 'Conversation domain',
   8
   );
2. Interactions

   1
   create or replace TABLE DATA_API.PROD.DATA_API_INTERACTIONS (
   2
   	TS_LAST_UPDATED VARCHAR(16777216) COMMENT 'Last updated timestamp of the record',
   3
   	CONVERSATION_ID VARCHAR(16777216) COMMENT 'Id identifying the unique conversation',
   4
   	USER_RECORD_ID VARCHAR(16777216) COMMENT 'Unique id of the user',
   5
   	INTERACTION_ID VARCHAR(16777216) COMMENT 'Id identifying the unique interaction',
   6
   	TS_INTERACTION VARCHAR(16777216) COMMENT 'Timestamp of the interaction',
   7
   	INTERACTION_PLATFORM VARCHAR(16777216) COMMENT 'Platform of the interaction - Slack/msteams etc.',
   8
   	INTERACTION_TYPE VARCHAR(16777216) COMMENT 'Type of the interaction - INTERACTION_TYPE_UTTERANCE, INTERACTION_TYPE_BOT_MESSAGE etc.',
   9
   	INTERACTION_LABEL VARCHAR(16777216) COMMENT 'LABEL for the interaction type UI form submission'
   10
   	PARENT_INTERACTION_ID VARCHAR(16777216) COMMENT 'Parent interaction id for UI Action link click and button click interactions',
   11
   	DATA_INTERACTION_DETAILS VARIANT COMMENT 'Details of the interaction (Json col)- content, type, domain, detail, entity etc.',
   12
   	ACTOR VARCHAR(16777216) COMMENT 'Actor - User/Bot'
   13
   );
3. Plugin calls

   1
   create or replace TABLE DATA_API.PROD.DATA_API_PLUGIN_CALLS (
   2
   	TS_LAST_UPDATED VARCHAR(16777216) COMMENT 'Last updated timestamp of the record',
   3
   	CONVERSATION_ID VARCHAR(16777216) COMMENT 'Id identifying the unique conversation related to the plugin call',
   4
   	USER_RECORD_ID VARCHAR(16777216) COMMENT 'Unique id of the user',
   5
   	INTERACTION_ID VARCHAR(16777216) COMMENT 'Id identifying the unique interaction related to the plugin call',
   6
   	PLUGIN_CALL_ID VARCHAR(16777216) COMMENT 'Unique Id identifying the plugin call',
   7
   	TS_CREATED VARCHAR(16777216) COMMENT 'Timestamp of the plugin call',
   8
   	TS_PLUGIN_UPDATED VARCHAR(16777216) COMMENT 'Timestamp of the latest plugin call update',
   9
   	PLUGIN_NAME VARCHAR(16777216) COMMENT 'Name of the executed Plugin - Product display name for the Native plugin and Config name for the Custom plugin',
   10
   	PLUGIN_STATUS VARCHAR(16777216) COMMENT 'Semantic Plugin status for each of the executed Plugin call',
   11
   	IS_PLUGIN_SERVED BOOLEAN COMMENT 'Whether or not plugin is served to the user - Plugin served or plugin waiting for user input',
   12
   	IS_PLUGIN_USED BOOLEAN COMMENT 'Whether or not plugin is successfully executed'
   13
   );
4. Plugin resources

   1
   create or replace TABLE DATA_API.PROD.DATA_API_PLUGIN_RESOURCES_V2 (
   2
   	PLUGIN_RESOURCE_ID VARCHAR(16777216) COMMENT 'Plugin resource item id as the hash of system_action_id, resource_id and resource_type',
   3
   	CONVERSATION_ID VARCHAR(16777216) COMMENT 'conversation_id identifying unique conversation (series_id)',
   4
   	INTERACTION_ID VARCHAR(16777216) COMMENT 'interaction_id identifying interaction corresponding to the system action',
   5
   	PLUGIN_CALL_ID VARCHAR(16777216) COMMENT 'Id for the plugin call',
   6
   	USER_RECORD_ID VARCHAR(16777216) COMMENT 'Id for the user record',
   7
   	RESOURCE_TYPE VARCHAR(16777216) COMMENT 'Type of Resource - FILE, FORM, KNOWLEDGE, Ticket etc.',
   8
   	RESOURCE_ID VARCHAR(16777216) COMMENT 'Resource ID - doc_id, ticket_id, etc.',
   9
   	DATA_RESOURCE_DETAILS VARIANT COMMENT 'Details of resources searched in the plugin - e.g., json of {name, url, domain, title, snippet}',
   10
   	IS_CITED BOOLEAN COMMENT 'If the resource is cited in the bot response',
   11
   	TS_LAST_UPDATED VARCHAR(16777216) COMMENT 'Last updated timestamp of the record',
   12
   	TS_CREATED VARCHAR(16777216) COMMENT 'timestamp of plugin call'
   13
   );
5. Users

   1
   create or replace TABLE DATA_API.PROD.DATA_API_COPILOT_USERS (
   2
   	USER_RECORD_ID VARCHAR(16777216) COMMENT 'Unique id of the user',
   3
   	FIRST_NAME VARCHAR(16777216) COMMENT 'First name of the user',
   4
   	LAST_NAME VARCHAR(16777216) COMMENT 'Last name of the user',
   5
   	EMAIL_ADDR VARCHAR(16777216) COMMENT 'Email address of the user',
   6
   	USER_PREFERRED_LANGUAGE VARCHAR(16777216) COMMENT 'User preferred language configured in the Assistant',
   7
   	HAS_ACCESS_TO_BOT BOOLEAN COMMENT 'Whether or not the user has access to the Assistant',
   8
   	DATA_EXTERNAL_SYSTEM_ID_DETAILS VARCHAR(16777216) COMMENT 'All external system identities from which user data is ingested',
   9
   	TS_LAST_UPDATED_AT VARCHAR(16777216) COMMENT 'Timestamp at which user was last updated in Moveworks',
   10
   	TS_FIRST_INTERACTION_AT VARCHAR(16777216) COMMENT 'Timestamp at which the users first interaction was recorded',
   11
   	TS_LAST_INTERACTION_AT VARCHAR(16777216) COMMENT 'Timestamp at which the users latest interaction was recorded',
   12
   );