Copy page

Universal

Google BigQuery Cron

Loads all data from a BigQuery table on a configured schedule.

Sync Type: Full Synchronisation

Overview

The BigQuery Cron Input allows you to read data from BigQuery tables or custom queries with support for full sync mode.

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 Cron 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
query	text	No	-	Optional custom query
cron	string (cron)	Yes	0 * * * *	Cron string for scheduling the ingest of your input. eg: 0 0 * * * - Daily at midnight, or */15 * * * * - Every 15 minutes. Defaults to an hour.

Secrets

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

Features

Sync Mode

• Full Sync: Reads all data from the table/query on each run
• Rows that are byte-identical within the same 1000-row checkpoint window are emitted once

Query Support

• By default this input will do a select all on the specified table
• Custom SQL queries for complex data transformations

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
   • Consider cost implications of full vs incremental sync

2. Scheduling

   • Consider BigQuery's quotas and pricing
   • Allow enough time between runs for processing

Example Configurations

Full Sync with Table

Code
{
  "settings": {
    "project": "my-project-123",
    "dataset": "my_dataset",
    "table": "my_table",
    "cron": "0 */1 * * *"
  },
  "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. Performance Issues

   • Use the regular BigQuery Input for larger tables that can be incrementally processed
   • Review query efficiency
   • Check BigQuery usage quotas

Sync frequency

This input runs on a cron schedule (default hourly, 0 * * * *), which you can configure on the pipeline. See Input Sync Frequency for details.