BigQuery destination
Beta
Data Destinations is currently available as a Private Beta product and the information contained in this document is subject to change. Data Destinations is not a HIPAA Eligible Service or PCI compliant and should not be enabled in workflows that are subject to HIPAA or PCI. To learn more, see Private beta legal information.
BigQuery, the serverless data warehouse available on Google Cloud, lets you stream events directly into a table in your own Google Cloud project for real-time analysis alongside your other data.
- Category: Warehouse
- Release status: Beta
- Authentication: Service account key
- Regional availability: US1
Get early access to native destinations for data warehouses, observability tools, and event pipelines.
You can send all event types supported by Event Streams to BigQuery. This destination has no user-identifier restriction.
Before you set up a BigQuery destination, make sure you have:
- A Google Cloud project that contains the BigQuery API.
- An existing BigQuery dataset in that project. Twilio doesn't create the dataset for you, so it must already exist before you save the destination.
- A Service Account JSON Key for a service account granted the
roles/bigquery.dataEditorrole on the dataset (the role covers reading the dataset's metadata and creating and writing tables within it), found in your Google Cloud project's IAM & Admin settings.
Info
See Set up a destination for the full UI and Destinations API setup flow common to every destination type.
To create a BigQuery destination in the Twilio Console:
- From your Google Cloud account, gather the settings listed in Destination settings.
- In the Twilio Console, go to Data > Data Destinations > Destinations > Add destination subscription.
- Complete the Add destination subscription wizard.
To create a BigQuery destination through the Destinations API, send a POST request to the Destinations endpoint and set destinationType to bigquery. For authentication and the other request body fields, see Create a destination.
POST "https://destinations.twilio.com/v1/ControlPlane/Destinations"
Set the config object in the request body as follows:
| Name | Type | Description |
|---|---|---|
credentials.credentialType (Required) | string | Set to SERVICE_ACCOUNT_KEY. |
credentials.credentialsJSON (Required) | string | Your Google service account JSON key, which authenticates to the BigQuery Storage Write API. |
dataset (Required) | string | Target BigQuery dataset. |
location | string | The dataset location, for example US, EU, or us-east1. The dataset must already exist because this destination doesn't create it. |
project (Required) | string | Google Cloud project ID that owns the target dataset. |
The following example shows a request body. Replace the uppercase placeholder values with your own.
1{2"destinationType": "bigquery",3"name": "My BigQuery destination",4"config": {5"credentials": {6"credentialType": "SERVICE_ACCOUNT_KEY",7"credentialsJSON": "CREDENTIALS_JSON"8},9"dataset": "DATASET",10"project": "PROJECT"11}12}
Use bigquery as the destinationType.
| Field | Description | Required | Default or choices |
|---|---|---|---|
| Service Account JSON Key | Google Service Account JSON key used to authenticate to BigQuery. Sensitive, entered once and stored encrypted. | Yes | None |
| GCP Project | Google Cloud project ID that owns the target dataset. | Yes | None |
| Dataset | Target BigQuery dataset. Cannot be changed after the destination is created. | Yes | None |
| Location | Dataset location (for example, US, EU, us-east1), for your reference. Twilio doesn't use this to locate or create the dataset. | No | None |
Twilio authenticates to BigQuery by using your Service Account JSON Key. The key is stored encrypted and is only used to call the BigQuery APIs on your behalf.
The first time you save a subscription, Twilio automatically creates the
destination table twilio_events_<subscription ID> in your configured
dataset.
| Field | BigQuery column |
|---|---|
| Event ID | event_id |
| Event type | event_type |
| Event time | event_timestamp |
| Event payload | payload (JSON) |
BigQuery doesn't support clustering on JSON-type columns, so Twilio clusters the table
on event_type instead. Queries that filter or aggregate on
event_type benefit from clustering. Queries that filter on fields inside
the payload JSON column don't, and may scan more data than expected,
which can affect query performance and cost.
When you save a BigQuery destination, Twilio verifies your Service Account JSON Key and confirms your dataset is visible to that service account before marking the destination active. If validation fails, check that:
- Your Service Account JSON Key is valid and hasn't been revoked or rotated in Google Cloud.
- The Dataset you entered already exists in the specified GCP Project (Twilio doesn't create datasets on your behalf).
- Your service account has the
roles/bigquery.dataEditorrole on the dataset (the role covers reading dataset metadata and creating and writing tables in that dataset).
Twilio doesn't currently offer a way to manually trigger a test event for this destination from either the Console or the CLI. To confirm delivery is working, wait for a real event of a subscribed type to fire.
| Cause | What you'll see | Fix |
|---|---|---|
| Invalid or revoked Service Account JSON Key | Validation fails with an auth error | Generate a new key in Google Cloud and update the destination |
| Dataset doesn't exist, or isn't visible to the service account | Validation fails with a configuration error | Create the dataset first, or grant the service account access, then retry |
| Service account lacks table create/write permission | Table creation fails when you save a subscription | Grant the service account the roles/bigquery.dataEditor role on the dataset |
If Twilio can't deliver events to your table, Twilio sends an error notification to the Debugger (available in US1 region only). If Twilio continues to encounter delivery errors, Twilio notifies you every 20 minutes after the initial error.