Copy page

Universal

Google BigQuery

Loads data from a BigQuery table using timestamp-based tracking and scheduled ingestion.

Sync Type: Incremental

Overview

The BigQuery Input allows you to read data from BigQuery tables or custom queries incrementally.

Requirements

Setting up a Service Account

1. Create a new service account:

Code
# Set your project ID
export PROJECT_ID="your-project-id"

# Create the service account
gcloud iam service-accounts create bigquery-reader \
    --description="Service account for BigQuery data reading" \
    --display-name="BigQuery Reader"

2. Grant the required permissions:

Code
# Grant BigQuery Data Viewer role (for reading data)
gcloud projects add-iam-policy-binding $PROJECT_ID \
    --member="serviceAccount:bigquery-reader@$PROJECT_ID.iam.gserviceaccount.com" \
    --role="roles/bigquery.dataViewer"

# Grant BigQuery Job User role (for executing queries)
gcloud projects add-iam-policy-binding $PROJECT_ID \
    --member="serviceAccount:bigquery-reader@$PROJECT_ID.iam.gserviceaccount.com" \
    --role="roles/bigquery.jobUser"

3. Create and save the service account key (credentials):

Code
# Create and save the key file
gcloud iam service-accounts keys create bigquery-reader-credentials.json \
    --iam-account=bigquery-reader@$PROJECT_ID.iam.gserviceaccount.com

This creates a bigquery-reader-credentials.json file in your current directory. Use the contents of this file as the value for the credentials_json secret in your input configuration.

Note: Keep this credentials file secure and never commit it to version control.

Configuration

The BigQuery Input is configured using the following settings:

Settings

Setting	Type	Required	Default	Description
project	string	Yes	-	The GCP project ID containing the BigQuery dataset
dataset	string	Yes	-	The BigQuery dataset ID containing the table
table	string	Yes	-	The BigQuery table ID to query data from
timestamp_column	string	Yes	-	The column containing timestamp values for incremental loading
query	text	No	-	Optional custom query

Secrets

Secret	Type	Required	Description
credentials_json	secret	No	JSON credentials to authenticate with Google Cloud (required for service account auth).

Features

Sync Mode

• Incremental Sync: Uses the timestamp_column provided to read new or updated records
• Automatically manages state between runs for incremental syncs
• On the first run the cursor is empty, so the whole table (or query result) is read; there is no backfill start setting

Query Support

• Simple table queries with automatic schema detection
• Custom SQL queries for complex data transformations
• Wraps custom queries in CTEs for incremental loading

Efficient Data Reading

• Uses BigQuery Query API for reliable data access
• Automatic connection and retry handling
• Supports both simple and complex queries

Best Practices

1. Query Optimization
   • Keep queries simple for better performance
   • Test custom queries with small data sets first

Example Configurations

Incremental Sync with Table

Code
{
  "settings": {
    "project": "my-project-123",
    "dataset": "my_dataset",
    "table": "my_table",
    "timestamp_column": "updated_at"
  },
  "secrets": {
    "credentials_json": {
      "value": "{ your service account JSON here }"
    }
  }
}

Troubleshooting

Common issues and solutions:

1. Permission Denied

   • Verify service account has both bigquery.dataViewer and bigquery.jobUser roles
   • Check project ID matches credentials
   • Ensure dataset and table exist

2. Invalid Query

   • Test custom queries in BigQuery console first
   • Verify column names and types
   • Check for proper SQL syntax

3. Timestamp Column Issues

   • Verify column exists in table/query results
   • Ensure column is TIMESTAMP type
   • Check column name matches exactly

4. Performance Issues

   • Review query efficiency
   • Check BigQuery usage quotas

Sync frequency

By default this input polls approximately every 10 seconds, with each sync beginning after the previous one completes. A cron schedule configured on the pipeline overrides this cadence. See Input Sync Frequency for details.

Each poll runs a query job against BigQuery, so put timestamp_column on a partitioned or clustered column, or set a cron schedule, to control query cost.