Export conversations to BigQuery

Insights lets you export your Insights conversation and analysis data to BigQuery so that you can perform your own raw queries. The export process writes a schema that is similar to the Speech Analysis Framework. This guide details the export process.

Create a new BigQuery table and dataset

The Insights Exporter requires a BigQuery table in order for the operation to be successful. If you don't have a target table, use this sample to create a new table and dataset using the bq command-line tool . See the BigQuery schema documentation for the output schema and column definitions.

BigQuery has some restrictions on the location of its data source. See Location considerations . Restrictions that apply to Cloud Storage buckets also apply to Insights. For example, if your BigQuery dataset is in the EU multi-region location, you can only export Insights data from one of the europe-* locations.

bq mk --dataset --location= LOCATION 
 PROJECT 
: DATASET 
bq mk --table PROJECT 
: DATASET 
. TABLE

Export conversation data to BigQuery

The export tool supports both filtering and writing data to customer-managed encryption key (CMEK) -protected tables. If you don't want to enable this feature, you can skip ahead and export your data to BigQuery .

Add filtering to the request (Optional)

Export to BigQuery is compatible with all combinations of filters that can be applied to conversation queries . For example, the following sample will export all conversations with 10 or more turns handled by agent_id "007" between January 1st 2021 and January 2nd 2021 PST:

FILTER='create_time>"2021-01-01T00:00:00-08:00" create_time<"2021-01-02T00:00:00-08:00" agent_id="007" turn_count>="10"'

Export data to a CMEK-protected table (Optional)

Provide your Insights service account with the Cloud KMS CryptoKey Encrypter/Decrypter role . See the known issues documentation about service account format. Once you have provided the correct role to your service account, add the fully-qualified name of the KMS key protecting the table to the export request:

KMS_KEY='projects/<project>/locations/<location>/keyRings/<key_ring>/cryptoKeys/<key_name>'

Specify write disposition option in the request (Optional)

CCAI Insights export supports the following write disposition options from BigQuery :

WRITE_TRUNCATE : If the table already exists, BigQuery overwrites the table data and uses the schema from the query result. This is the default option.
WRITE_APPEND : If the table already exists, BigQuery appends the data to the table.

For example, the following sample will append the exported data to an existing destination table:

WRITE_DISPOSITION='WRITE_APPEND'

Export your data to BigQuery

The following code sample demonstrates how to export your data. See the export reference documentation for complete details.

The export creates a long-running Operation object. You can poll the operation to check its status.

Permissions required for this task

To perform this task, you must have the following permissions :

contactcenterinsights.conversations.list on the parent resource

REST

Before using any of the request data, make the following replacements:

PROJECT_ID : your Google Cloud project ID.
DATASET : the name of the BigQuery dataset that the data should be exported to.
TABLE : the BigQuery table name that your Insights data should be written to.
FILTER_QUERY : a query that Insights uses to export only conversations that have specific properties. For example, entering the value "agent_id=\"007\"" will result in only conversations associated with agent 007 being exported.

HTTP method and URL:

POST https://contactcenterinsights.googleapis.com/v1/projects/ PROJECT_ID 
/locations/us-central1/insightsdata:export

Request JSON body:

{
  "bigQueryDestination": {
    "projectId": " PROJECT_ID 
",
    "dataset": " DATASET 
",
    "table": " TABLE 
",
  },
  "filter": " FILTER_QUERY 
"
}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Note: The following command assumes that you have logged in to the gcloud CLI with your user account by running gcloud init or gcloud auth login , or by using Cloud Shell , which automatically logs you into the gcloud CLI . You can check the currently active account by running gcloud auth list .

Save the request body in a file named request.json , and execute the following command:

curl -X POST \
-H "Authorization: Bearer $(gcloud auth print-access-token)" \
-H "Content-Type: application/json; charset=utf-8" \
-d @request.json \
"https://contactcenterinsights.googleapis.com/v1/projects/ PROJECT_ID 
/locations/us-central1/insightsdata:export"

PowerShell (Windows)

Note: The following command assumes that you have logged in to the gcloud CLI with your user account by running gcloud init or gcloud auth login . You can check the currently active account by running gcloud auth list .

Save the request body in a file named request.json , and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
-Method POST `
-Headers $headers `
-ContentType: "application/json; charset=utf-8" `
-InFile request.json `
-Uri "https://contactcenterinsights.googleapis.com/v1/projects/ PROJECT_ID 
/locations/us-central1/insightsdata:export" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

{
  "name": "projects/ PROJECT_ID 
/locations/us-central1/operations/ OPERATION_ID 
"
}

Python

To authenticate to Insights, set up Application Default Credentials. For more information, see Set up authentication for a local development environment .

  from 
  
 google.cloud 
  
 import 
  contact_center_insights_v1 
 
 def 
  
 export_to_bigquery 
 ( 
 project_id 
 : 
 str 
 , 
 bigquery_project_id 
 : 
 str 
 , 
 bigquery_dataset_id 
 : 
 str 
 , 
 bigquery_table_id 
 : 
 str 
 , 
 ) 
 - 
> None 
 : 
  
 """Exports data to BigQuery. 
 Args: 
 project_id: 
 The project identifier that owns the data source to be exported. 
 For example, 'my-project'. 
 bigquery_project_id: 
 The project identifier that owns the BigQuery sink to export data to. 
 For example, 'my-project'. 
 bigquery_dataset_id: 
 The BigQuery dataset identifier. For example, 'my-dataset'. 
 bigquery_table_id: 
 The BigQuery table identifier. For example, 'my-table'. 
 Returns: 
 None. 
 """ 
 # Construct an export request. 
 request 
 = 
  contact_center_insights_v1 
 
 . 
  ExportInsightsDataRequest 
 
 () 
 request 
 . 
 parent 
 = 
 ( 
  contact_center_insights_v1 
 
 . 
  ContactCenterInsightsClient 
 
 . 
 common_location_path 
 ( 
 project_id 
 , 
 "us-central1" 
 ) 
 ) 
 request 
 . 
 big_query_destination 
 . 
 project_id 
 = 
 bigquery_project_id 
 request 
 . 
 big_query_destination 
 . 
 dataset 
 = 
 bigquery_dataset_id 
 request 
 . 
 big_query_destination 
 . 
 table 
 = 
 bigquery_table_id 
 request 
 . 
 filter 
 = 
 'agent_id="007"' 
 # Call the Insights client to export data to BigQuery. 
 insights_client 
 = 
  contact_center_insights_v1 
 
 . 
  ContactCenterInsightsClient 
 
 () 
 export_operation 
 = 
 insights_client 
 . 
  export_insights_data 
 
 ( 
 request 
 = 
 request 
 ) 
 export_operation 
 . 
 result 
 ( 
 timeout 
 = 
 600000 
 ) 
 print 
 ( 
 "Exported data to BigQuery" 
 )

Java

To authenticate to Insights, set up Application Default Credentials. For more information, see Set up authentication for a local development environment .

  import 
  
 com.google.api.gax.longrunning. OperationTimedPollAlgorithm 
 
 ; 
 import 
  
 com.google.api.gax.retrying. RetrySettings 
 
 ; 
 import 
  
 com.google.cloud.contactcenterinsights.v1. ContactCenterInsightsClient 
 
 ; 
 import 
  
 com.google.cloud.contactcenterinsights.v1. ContactCenterInsightsSettings 
 
 ; 
 import 
  
 com.google.cloud.contactcenterinsights.v1. ExportInsightsDataRequest 
 
 ; 
 import 
  
 com.google.cloud.contactcenterinsights.v1. ExportInsightsDataResponse 
 
 ; 
 import 
  
 com.google.cloud.contactcenterinsights.v1. LocationName 
 
 ; 
 import 
  
 java.io.IOException 
 ; 
 import 
  
 org.threeten.bp.Duration 
 ; 
 public 
  
 class 
 ExportToBigquery 
  
 { 
  
 public 
  
 static 
  
 void 
  
 main 
 ( 
 String 
 [] 
  
 args 
 ) 
  
 throws 
  
 Exception 
 , 
  
 IOException 
  
 { 
  
 // TODO(developer): Replace these variables before running the sample. 
  
 String 
  
 projectId 
  
 = 
  
 "my_project_id" 
 ; 
  
 String 
  
 bigqueryProjectId 
  
 = 
  
 "my_bigquery_project_id" 
 ; 
  
 String 
  
 bigqueryDataset 
  
 = 
  
 "my_bigquery_dataset" 
 ; 
  
 String 
  
 bigqueryTable 
  
 = 
  
 "my_bigquery_table" 
 ; 
  
 exportToBigquery 
 ( 
 projectId 
 , 
  
 bigqueryProjectId 
 , 
  
 bigqueryDataset 
 , 
  
 bigqueryTable 
 ); 
  
 } 
  
 public 
  
 static 
  
 void 
  
 exportToBigquery 
 ( 
  
 String 
  
 projectId 
 , 
  
 String 
  
 bigqueryProjectId 
 , 
  
 String 
  
 bigqueryDataset 
 , 
  
 String 
  
 bigqueryTable 
 ) 
  
 throws 
  
 Exception 
 , 
  
 IOException 
  
 { 
  
 // Set the operation total polling timeout to 24 hours instead of the 5-minute default. 
  
 // Other values are copied from the default values of {@link ContactCenterInsightsStubSettings}. 
  
  ContactCenterInsightsSettings 
 
 . 
 Builder 
  
 clientSettings 
  
 = 
  
  ContactCenterInsightsSettings 
 
 . 
 newBuilder 
 (); 
  
 clientSettings 
  
 . 
 exportInsightsDataOperationSettings 
 () 
  
 . 
 setPollingAlgorithm 
 ( 
  
  OperationTimedPollAlgorithm 
 
 . 
 create 
 ( 
  
  RetrySettings 
 
 . 
 newBuilder 
 () 
  
 . 
  setInitialRetryDelay 
 
 ( 
 Duration 
 . 
 ofMillis 
 ( 
 5000L 
 )) 
  
 . 
  setRetryDelayMultiplier 
 
 ( 
 1.5 
 ) 
  
 . 
  setMaxRetryDelay 
 
 ( 
 Duration 
 . 
 ofMillis 
 ( 
 45000L 
 )) 
  
 . 
  setInitialRpcTimeout 
 
 ( 
 Duration 
 . 
 ZERO 
 ) 
  
 . 
  setRpcTimeoutMultiplier 
 
 ( 
 1.0 
 ) 
  
 . 
  setMaxRpcTimeout 
 
 ( 
 Duration 
 . 
 ZERO 
 ) 
  
 . 
  setTotalTimeout 
 
 ( 
 Duration 
 . 
 ofHours 
 ( 
 24L 
 )) 
  
 . 
 build 
 ())); 
  
 // Initialize client that will be used to send requests. This client only needs to be created 
  
 // once, and can be reused for multiple requests. After completing all of your requests, call 
  
 // the "close" method on the client to safely clean up any remaining background resources. 
  
 try 
  
 ( 
  ContactCenterInsightsClient 
 
  
 client 
  
 = 
  
  ContactCenterInsightsClient 
 
 . 
 create 
 ( 
 clientSettings 
 . 
 build 
 ())) 
  
 { 
  
 // Construct an export request. 
  
  LocationName 
 
  
 parent 
  
 = 
  
  LocationName 
 
 . 
 of 
 ( 
 projectId 
 , 
  
 "us-central1" 
 ); 
  
  ExportInsightsDataRequest 
 
  
 request 
  
 = 
  
  ExportInsightsDataRequest 
 
 . 
 newBuilder 
 () 
  
 . 
 setParent 
 ( 
 parent 
 . 
  toString 
 
 ()) 
  
 . 
  setBigQueryDestination 
 
 ( 
  
  ExportInsightsDataRequest 
 
 . 
 BigQueryDestination 
 . 
 newBuilder 
 () 
  
 . 
  setProjectId 
 
 ( 
 bigqueryProjectId 
 ) 
  
 . 
  setDataset 
 
 ( 
 bigqueryDataset 
 ) 
  
 . 
  setTable 
 
 ( 
 bigqueryTable 
 ) 
  
 . 
 build 
 ()) 
  
 . 
 setFilter 
 ( 
 "agent_id=\"007\"" 
 ) 
  
 . 
 build 
 (); 
  
 // Call the Insights client to export data to BigQuery. 
  
  ExportInsightsDataResponse 
 
  
 response 
  
 = 
  
 client 
 . 
  exportInsightsDataAsync 
 
 ( 
 request 
 ). 
  get 
 
 (); 
  
 System 
 . 
 out 
 . 
 printf 
 ( 
 "Exported data to BigQuery" 
 ); 
  
 } 
  
 } 
 }

Node.js

To authenticate to Insights, set up Application Default Credentials. For more information, see Set up authentication for a local development environment .

  /** 
 * TODO(developer): Uncomment these variables before running the sample. 
 */ 
 // const projectId = 'my_project_id'; 
 // const bigqueryProjectId = 'my_bigquery_project_id'; 
 // const bigqueryDataset = 'my_bigquery_dataset'; 
 // const bigqueryTable = 'my_bigquery_table'; 
 // Imports the Contact Center Insights client. 
 const 
  
 { 
  
 ContactCenterInsightsClient 
 , 
 } 
  
 = 
  
 require 
 ( 
 ' @google-cloud/contact-center-insights 
' 
 ); 
 // Instantiates a client. 
 const 
  
 client 
  
 = 
  
 new 
  
  ContactCenterInsightsClient 
 
 (); 
 async 
  
 function 
  
 exportToBigquery 
 () 
  
 { 
  
 const 
  
 [ 
 operation 
 ] 
  
 = 
  
 await 
  
 client 
 . 
 exportInsightsData 
 ({ 
  
 parent 
 : 
  
 client 
 . 
  locationPath 
 
 ( 
 projectId 
 , 
  
 'us-central1' 
 ), 
  
 bigQueryDestination 
 : 
  
 { 
  
 projectId 
 : 
  
 bigqueryProjectId 
 , 
  
 dataset 
 : 
  
 bigqueryDataset 
 , 
  
 table 
 : 
  
 bigqueryTable 
 , 
  
 }, 
  
 filter 
 : 
  
 'agent_id="007"' 
 , 
  
 }); 
  
 // Wait for the operation to complete. 
  
 await 
  
 operation 
 . 
 promise 
 (); 
  
 console 
 . 
 info 
 ( 
 'Exported data to BigQuery' 
 ); 
 } 
 exportToBigquery 
 ();

Export data to another project (Optional)

By default, Insights BigQuery export writes data to the same project that owns the Insights data. However, you can also export to BigQuery in another project.

Ensure that your Insights service account has BigQuery access to the recipient project using either the IAM console or with gcloud :

gcloud projects add-iam-policy-binding RECEIVER_PROJECT 
\
    --member=serviceAccount:service- PROJECT_NUMBER 
@gcp-sa-contactcenterinsights.iam.gserviceaccount.com \
    --role=roles/bigquery.admin

To export your data to a specific project, input the recipient project's ID number in the project_id field in the BigQueryDestination object.

Query the data in BigQuery

Run this command to query the data in BigQuery. See the BigQuery Quickstart documentation for more query options:

gcloud config set project PROJECT 
bq show DATASET 
. TABLE

Querying exported conversations:

bq query --use_legacy_sql=false \
   "SELECT conversationName FROM DATASET 
. TABLE 
"

Export conversations to BigQuery Stay organized with collections Save and categorize content based on your preferences.

Create a new BigQuery table and dataset

Export conversation data to BigQuery

Add filtering to the request (Optional)

Export data to a CMEK-protected table (Optional)

Specify write disposition option in the request (Optional)

Export your data to BigQuery

Permissions required for this task

REST

curl (Linux, macOS, or Cloud Shell)

PowerShell (Windows)

Python

Java

Node.js

Export data to another project (Optional)

Query the data in BigQuery

Export conversations to BigQuery