BigQuery

Setting up the BigQuery destination takes two parts: choosing how data is loaded (through a Cloud Storage bucket, or with direct inserts), then creating the destination in Pipeloom.

The BigQuery destination connector:

• Writes each stream into a typed table, partitioned and clustered for cheaper queries.
• Supports every sync mode, including deduplication.
• Maps each source namespace to a BigQuery dataset.

Before you start

You need:

• A Google Cloud project with BigQuery turned on.

• A BigQuery dataset to write to.

  Note: a BigQuery query can only use datasets in the same location. If you'll combine the synced data with other datasets in your queries, create them all in the same Google Cloud location. See Introduction to datasets.

• A Google Cloud service account with the BigQuery User and BigQuery Data Editor roles, and a key for it in JSON format.

Step 1: Choose a loading method

Through a Cloud Storage bucket (recommended)

Use this for production. Data is uploaded to a Cloud Storage (GCS) bucket and then loaded into BigQuery, which is faster for large volumes.

To set it up:

1. Create a Cloud Storage bucket with its protection tools set to none or Object versioning. The bucket must not have a retention policy.
2. Create an HMAC key and access ID.
3. Grant the Storage Object Admin role to the service account. It must be the same service account you give the destination in Step 2.
4. Make sure Pipeloom can reach the bucket. The connection test that runs when you save the destination checks this.

The bucket must be encrypted with a Google-managed encryption key, which is the default for new buckets. Buckets that use customer-managed encryption keys (CMEK) aren't supported. You'll find the setting on the bucket's Configuration tab, in the Encryption type row.

Batched standard inserts

A simpler setup with nothing to stage: data is sent straight to BigQuery with its SDK. It suits small volumes and quick tests.

Step 2: Create the BigQuery destination in Pipeloom

Create a new destination and choose BigQuery. You can do this from the Destinations page, or from a Destination step on a pipeline canvas.

Fill in the form:

1. For Project ID, enter your Google Cloud project ID.

2. For Dataset Location, choose the location of your BigQuery dataset.

   :::warning You can't change the location later. :::

3. For Default Dataset ID, enter the BigQuery dataset ID. Streams whose source doesn't set a namespace are written here.

4. For Loading Method, choose GCS Staging (recommended) or Batched Standard Inserts. For GCS Staging, also enter:

   • GCS Bucket Name and GCS Bucket Path: the bucket, and the folder in it, for staged files.
   • HMAC Access Key and HMAC Secret: the HMAC key from Step 1. The access ID is 61 characters long for a service account (24 for a user account); the secret is a 40-character base64 string.
   • GCS Tmp Files Post-Processing: whether staged files are deleted from the bucket after loading (the default) or kept.

5. For Service Account Key JSON, paste the service account's key in JSON format.

   :::note Copy the whole key file, including the opening and closing braces. :::

Optional settings:

• CDC deletion mode: what happens here when a row is deleted in a source that uses change data capture. Hard delete (the default) deletes the row here too; Soft delete keeps it as a tombstone record that marks it as deleted.

• Legacy raw tables: write only the legacy raw-table format, for compatibility with older setups. See Raw tables (legacy).

• Job Execution Project ID: leave it empty to run BigQuery jobs in the project you entered as Project ID. To run them in a different project, for example so syncs don't compete with analytics workloads for the same project quotas, enter that project's ID. Query, load and copy jobs then count against the job project's quotas, while data still lands in datasets under Project ID. The service account needs the BigQuery Job User role on the job project, as well as its roles on the dataset project.

• Internal table dataset name: the dataset for the connector's internal tables, and for raw tables in legacy raw-tables mode. Defaults to airbyte_internal.

Save the destination. Pipeloom tests the connection, and the destination is ready once the test passes.

Sync modes

This destination supports every sync mode:

It supports namespaces: each namespace maps to a BigQuery dataset.

Output tables

Besides the columns in your stream's schema, each table has these metadata columns:

• _airbyte_raw_id
• _airbyte_generation_id
• _airbyte_extracted_at
• _airbyte_meta

Tables are partitioned by day on _airbyte_extracted_at (partition boundaries are in UTC), and clustered by _airbyte_extracted_at and the table's primary key. Filter on the partitioning column in a WHERE clause and BigQuery scans fewer partitions, which lowers query costs. The connector doesn't turn on Require partition filter, but you can turn it on for the tables yourself.

Raw tables (legacy)

With Legacy raw tables on, each stream is written to a raw table of its own in the airbyte_internal dataset, unless you set a different Internal table dataset name. Raw tables aren't deduplicated.

A raw table has these columns:

• _airbyte_raw_id
• _airbyte_generation_id
• _airbyte_extracted_at
• _airbyte_loaded_at
• _airbyte_meta
• _airbyte_data

_airbyte_data holds the record as JSON. See metadata columns for the others.

Naming

Follow BigQuery's dataset naming rules.

The connector replaces invalid characters with _ when writing. Datasets whose names start with _ are hidden in BigQuery's Explorer panel, so when a converted namespace would start with _, the connector puts an n in front of it.

Data type mapping

Troubleshooting

Permission errors

The service account is missing permissions.

• Make sure it has the BigQuery User and BigQuery Data Editor roles, or equivalent permissions.
• With GCS staging, make sure it also has access to the bucket and path, or the Cloud Storage Admin role, which covers everything needed and more.

The HMAC key is wrong.

• Make sure the HMAC key was created for the same service account, and that the account can access the bucket and path.

HTTP 400 "Request had invalid euc header" during upload

If a sync fails with BigQueryException: 400 Bad Request and the message Request had invalid euc header:

• The error comes from Google's BigQuery upload API, and it's usually temporary. Run the sync again; it normally succeeds.
• If it happens on every sync, the cause is harder to pin down, and Google doesn't document this error. These checks may help:
  • Make sure the service account key hasn't been rotated or revoked since you set up the destination.
  • Run fewer syncs into the same BigQuery project or table at once. Contention can contribute.

Quota exceeded for concurrent script queries per project

If a sync fails with:

BigQueryException: Quota exceeded: Your project_and_region exceeded quota for
concurrent script queries per project.

• This quota is per project, so syncs share it with everything else in the project, such as analytics queries or Dataform pipelines.
• Set Job Execution Project ID to a separate project, so the connector's jobs count against that project's quota instead. Data still lands in datasets under Project ID.
• Or sync fewer streams, or run fewer syncs into the project, at the same time.

Load job timeouts

If a sync fails with Fail to complete a load job in big query:

• BigQuery load jobs time out after 30 minutes of waiting. Very large batches, or a long BigQuery queue, can hit this limit.
• Two syncs loading into the same BigQuery table at the same time aren't supported, and can also cause this timeout. Make sure each table is written by only one sync.
• Load less per sync: use an incremental sync mode, or split the streams across more syncs.