Google BigQuery

You can set up recurring syncs of your Amplitude event data to BigQuery through the Amplitude UI,  or manually start a sync of your historical data. This article outlines the process for connecting your Amplitude and BigQuery accounts, and then syncing your data.

Considerations

• To help save usage costs for your queries, Amplitude partitions the events table by the date of event_time. See the BigQuery docs for more information about table partitioning.

• Amplitude runs a daily integrity check for your exports. Each day, in the background, it checks the number of events exported over the last few days. If the number of events exported doesn't match the number of events that should have been exported, Amplitude automatically runs a backfill for that date.

• It's possible to export duplicate events, especially if there is a backfill for a set of already-exported data. To help with this, Amplitude creates a table function within the provided dataset named deduplicated_<appid>. This table function provides a deduplicated view of the data.

  Example

  1SELECT * FROM `testProject.testDataset.deduplicated_EVENTS_12345`('2022-01-01', '2022-02-10');

• Depending on your company's network policy, you may need to add the following IP addresses to your allowlist to allow Amplitude's servers to access your BigQuery instance:

  • Amplitude US IP addresses:
    • 52.33.3.219
    • 35.162.216.242
    • 52.27.10.221
  • Amplitude EU IP addresses:
    • 3.124.22.25
    • 18.157.59.125
    • 18.192.47.195

Prerequisites

To get started with exporting to BigQuery, you need the following:

• A BigQuery project with the BigQuery Transfer service enabled.
• A service account for Amplitude. This lets Amplitude to export your data to your Google Cloud project. Your service account needs these roles enabled:
  • BigQuery User
  • BigQuery Data Editor
  • A custom role that has the following permissions enabled:
    • bigquery.transfers.get
    • bigquery.transfers.update
    • bigquery.datasets.update

After you've created a service account, generate and download the service account key file and upload it to Amplitude. Make sure you export Amplitude's account key in JSON format.

Set up a recurring data export to BigQuery

Creating a recurring data export is a simple, three-step process. Each sync completes within ten minutes, and all jobs are visible in Amplitude.

To set up a recurring export of your Amplitude data to BigQuery, follow these steps:

1. In Amplitude Data, click Catalog and select the Destinations tab.
2. In the Warehouse Destination section, click Big Query.
3. On the Getting Started tab, select the data you'd like to export and the export's cadence. You can Export events ingested today and moving forward, Export all merged Amplitude IDs, or both. For events, you can also specify filtering conditions to only export events that meet certain criteria.
4. Review the schemas for the Event table and the Merge IDs table and click Next.
5. Specify the BigQuery dataset to receive your Amplitude data, and upload the service account key file. This file must be in JSON format.
6. Click Next. Amplitude attempts a test upload to test the credentials. If the upload is successful, click Finish to complete the BigQuery destination configuration and activation.

All future events or merged users are automatically sent to BigQuery. Amplitude exports files to your BigQuery account every hour.

Event table schema

The Event table schema includes the following columns:

Merged User table schema

The Merged User table schema contains the following:

BigQuery deduplication table

To create a deduplication table in BigQuery:

 1CREATE OR REPLACE TABLE FUNCTION `amplitude_bq_ingestion`.deduplicated_EVENTS(start_date DATE, end_date DATE)
 2AS
 3SELECT *
 4FROM (SELECT *,
 5             ROW_NUMBER() OVER (
 6                 PARTITION BY uuid
 7                 ) rn
 8      FROM `amplitude_bq_ingestion`.`EVENTS`
 9      WHERE DATE(event_time) >= start_date
10        and DATE(event_time) <= end_date) t
11WHERE rn = 1;