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

   SQL

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

2. Interactions

   SQL

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

3. Plugin calls

   SQL

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

4. Plugin resources

   SQL

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

5. Users

   SQL

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