How to schedule queries in BigQuery?

Type Your Question

How to schedule queries in BigQuery?

Saturday, 15 March 2025

GOOGLE

Google BigQuery is a powerful, fully managed, and serverless data warehouse that allows you to analyze massive datasets. Often, you'll need to automate the execution of your queries for tasks like data transformation, regular reporting, or building ETL (Extract, Transform, Load) pipelines. This document explains different ways to schedule queries in BigQuery, ensuring your data processing runs consistently and efficiently.

Why Schedule BigQuery Queries?

Scheduling queries in BigQuery provides several key benefits:

Automation: Automate routine tasks, eliminating the need for manual intervention and freeing up resources.
Regular Reporting: Generate daily, weekly, or monthly reports automatically, providing timely insights.
ETL Pipelines: Build and manage complex ETL processes, transforming and loading data from various sources into BigQuery on a schedule.
Data Consistency: Ensure data transformations and aggregations are performed consistently at specific intervals.
Resource Optimization: By scheduling queries during off-peak hours, you can potentially reduce costs and improve query performance.

Methods for Scheduling BigQuery Queries

Google Cloud provides several methods for scheduling BigQuery queries, each with its own advantages and use cases. The primary methods are:

Scheduled Queries (Recommended): The built-in BigQuery scheduled query feature allows you to schedule SQL queries directly within BigQuery. This is often the simplest and most straightforward approach for many use cases.
Cloud Functions: Utilize Cloud Functions (serverless, event-driven functions) to trigger BigQuery queries. This provides greater flexibility for more complex workflows and event-based scheduling.
Cloud Composer: Employ Cloud Composer, Google Cloud's managed Apache Airflow service, to orchestrate complex data pipelines involving multiple BigQuery queries and other tasks.

1. Scheduled Queries

Scheduled queries, sometimes referred to as Scheduled Query Service (SQS), is the preferred and simplest way to schedule query execution directly within BigQuery. It uses SQL code to define the query, destination table, and scheduling options.

Creating a Scheduled Query

Navigate to BigQuery in the Google Cloud Console: Go to the BigQuery UI in the Google Cloud Console.
Write Your SQL Query: Compose the SQL query you want to schedule. Ensure the query includes the INSERT or MERGE statement, or utilizes CREATE OR REPLACE TABLE AS SELECT ..., which writes the results into a destination table. Here's an example:
```
CREATE OR REPLACE TABLE your-project.your_dataset.scheduled_output AS
SELECT CURRENT_TIMESTAMP() AS execution_time, COUNT(*) AS total_records
FROM your-project.your_dataset.your_source_table;
```
Replace your-project.your_dataset.scheduled_output and your-project.your_dataset.your_source_table with your actual project ID, dataset ID, source table and destination table.
Schedule Query Configuration:
- Click "Schedule query" button in the BigQuery UI after editing the query.
- Define a schedule name.
- Specify the schedule frequency using either cron syntax or a predefined interval (e.g., hourly, daily, weekly, monthly). The default setting is using cron syntax (e.g., '0 0 * *' for daily at midnight).
- Choose a target dataset. This determines where BigQuery will store the results.
- Specify the dataset location (e.g., US, EU, asia-northeast1). Choose the dataset that matches the locations of all involved dataset or Multi-region option like "US" or "EU" whenever feasible.
- Error Management: Configure how errors will be handled, you can chose options like: "Stop queries" if one execution failed, or to still trigger on error.
- Notification: You can enable the "Notifications" option which will allow for logging activity to Pub/Sub. This can be useful if you'd want to use some serverless applications with audit logging enabled to determine and quickly address if some executions had issue.
Authorize Scheduled Query: BigQuery requires authorization to run queries on your behalf. You may need to create a new service account or authorize the scheduled query to use an existing service account with the necessary permissions. This Service Account must have roles/bigquery.jobUser permission. Depending on the destinations it could also require write permission to that table (e.g. roles/bigquery.dataEditor).
Save the Schedule: Save the schedule query configuration.

Scheduled Query Options

Cron Syntax: Uses standard cron syntax for defining schedule frequency (e.g., "0 0 * *" for daily at midnight). You can use cron job generator or other cron syntax tool to generate specific expression for scheduling execution (e.g. crontab guru)
Scheduling Intervals: Set the intervals on which schedule job runs. Default can be hourly or Daily (based on Cron expression entered.)
Service Account: Specify the service account that BigQuery uses to execute the query. Make sure the service account has the required BigQuery permissions (e.g., roles/bigquery.jobUser, and permissions on input/output tables.)
Error Management: Determine behavior in the event that an execution error (e.g. Query validation error), and manage the failure process and notifications that might need triggered from those processes.

When to use Scheduled Queries:

You need scheduled task at a regular frequency on table based execution that transform input tables to populate one or several destination tables (e.g. daily table consolidation, aggregation).
Whenever complex scheduling (that depends on some tasks external of Google) is not required.

2. Cloud Functions

Cloud Functions allows you to trigger BigQuery queries based on events or scheduled times. A Cloud Function is a serverless execution environment that runs in response to triggers. You can use a Cloud Function triggered by a Cloud Scheduler job to initiate a BigQuery query.

Steps to Schedule BigQuery Queries with Cloud Functions

Write the Cloud Function: Create a Cloud Function written in Python (or other supported languages) that executes the BigQuery query. Use the BigQuery client library (google-cloud-bigquery) to interact with BigQuery. Here's a Python example:


from google.cloud import bigquery
import os

def run_bigquery_query(request):
 """Cloud Function to execute a BigQuery query."""

 # Initialize BigQuery client
 client = bigquery.Client()

 # Construct the query. Replace with your actual query and project/dataset IDs
 query = """
 SELECT CURRENT_TIMESTAMP() AS execution_time, COUNT(*) AS total_records
 FROM your-project.your_dataset.your_source_table;
 """

 destination_table_id = 'your-project.your_dataset.your_destination_table'
 # Configure the query job
 job_config = bigquery.QueryJobConfig(destination=destination_table_id, write_disposition="WRITE_TRUNCATE") #WRITE_APPEND / WRITE_EMPTY allowed as well

 # Run the query
 query_job = client.query(query, job_config=job_config)

 # Wait for the query to complete
 query_job.result()

 print(f"Query job finished. Table updated at: {destination_table_id}")
 return 'BigQuery query executed successfully!'

Replace your-project.your_dataset.your_source_table, your-project.your_dataset.your_destination_table and your-project with your actual project, source and output. Make sure destination table table either already exist, or you've granted permission for job to generate table and add rows (e.g. roles/bigquery.dataEditor permissions at project or dataset level. The WRITE_TRUNCATE flag overwrites any existing data with result of running your select command).

Deploy the Cloud Function: Deploy the Cloud Function to Google Cloud. You can deploy the function from Google Cloud Console using the inline editor or CLI. Ensure the Cloud Function's service account has the roles/bigquery.jobUser and write access on destination tables permissions.
Create a Cloud Scheduler Job: Create a Cloud Scheduler job that triggers the Cloud Function at the desired frequency.
- In Google Cloud Console, navigate to Cloud Scheduler
- Click "+ CREATE JOB"
- Give a job name (e.g., 'bq-scheduled-function')
- Select the region close to where the Cloud Function will run
- Specify a scheduling frequency using cron syntax. For instance:
  - '0 9 * *' triggers daily at 9:00AM.
  - '0 * *' to execute every hour.
- Select Target: HTTP
- Provide your deployed Cloud Functions trigger URL
- Select Method: 'POST' (important)
- You could also select OIDC token to authenticate with your Service Account. If done this way, you might have to authorize cloud scheduler to interact with deployed cloud function and your cloud functions will be more secured than having non-protected trigger end point (make sure the service accounts listed above match together, Cloud Scheduler calling, Cloud Function calling BQ).
- Create
Test the Function After setup is complete, ensure the whole end to end solution works by hitting run button for both scheduler and Cloud Function. Examine table destination, Cloud Function Logs and any error during processing and adjust to required adjustments.

Cloud Function Benefits

Event-driven Architecture: Respond to specific events beyond just scheduled times. Can trigger tasks based on other Google Cloud events.
Flexibility: More complex transformations and pre-processing steps can be done in the Cloud Function prior to execution, which may be important.

Cloud Function Limitations

Increase complexity of set-up due Cloud Function. Not really needed for basic queries.

When to Use Cloud Functions:

Required scheduled query execution but the trigger and the execution need customized conditions, validation, conditional parameters adjustments, and various configurations that requires writing code in Google Functions and adding an extra serverless component between scheduling triggers, and execution.

3. Cloud Composer

Cloud Composer is Google Cloud's managed Apache Airflow service. It allows you to orchestrate complex data pipelines using directed acyclic graphs (DAGs). Using Composer allows you to run jobs when other job depends on a task finishing prior or you needs orchestration that does a conditional execution, etc...

Steps to Schedule BigQuery Queries with Cloud Composer

Create a Cloud Composer Environment: Create a new Cloud Composer environment within Google Cloud Platform (GCP). Specify your compute configuration, service account settings. Cloud Composer environment might require initial spin-up and it can take up 30 minutes to one hour. The associated Composer Environment cost money while Cloud Function and Scheduling query feature do not.
Write a DAG: Create an Apache Airflow DAG (Directed Acyclic Graph) defined in Python that contains a task to execute a BigQuery query. Here's a simplified example:
```
from airflow import DAG
from airflow.providers.google.cloud.operators.bigquery import BigQueryExecuteQueryOperator
from datetime import datetime

with DAG(
 dag_id='bigquery_example_dag',
 start_date=datetime(2023, 1, 1),
 schedule_interval='0 0 * *', # Daily at midnight
 catchup=False,
 default_args={'project_id': 'your-project-id'}
) as dag:
 run_query = BigQueryExecuteQueryOperator(
 task_id='run_bigquery_query',
 sql="""
 SELECT CURRENT_TIMESTAMP() AS execution_time, COUNT(*) AS total_records
 FROM your-project.your_dataset.your_source_table;
 """,
 destination_dataset_table='your-project.your_dataset.your_destination_table',
 write_disposition='WRITE_TRUNCATE',
 use_legacy_sql=False
 )

 
```
Make sure your service account listed above (or Airflow user) contains appropriate set of permissions and IAM configurations such as : "BigQuery Job User" role, "Storage Bucket Administrator", IAM roles with the write table roles required for your workload at both project/dataset level, storage permissions so that the task execution are performed as your Service account

Replace placeholders your project_id, your-project.your_dataset.your_source_table, your-project.your_dataset.your_destination_table. You can setup the query inline (e.g. query string within operator above), upload .sql, set the bucket or run with Jinja and generate based on pre-existing file in directory, add multiple dependent tasks. You might need also additional installations. Consider storing your .sql code separately from your .py airflow so code looks cleaner and maintainable, easier testing, etc. Refer official bigquery provider Airflow site.
Upload the DAG: Upload the DAG to the Cloud Composer environment. Upload Python, shell script and .sql used via composer "UI --> Buckets --> dags."
Airflow will automatically find your upload DAG! Airflow Web Server then handles DAG and task run using UI to determine scheduled, dependencies status of given workloads. After initial parsing time it becomes visible in the Airflow web interface within your Composer environment.
Manage the DAG: Monitor and manage the DAG from the Airflow web UI. The Dag Run Status contains full view of dependencies completion as part of your tasks that defines query being executed.
Make adjustments depending on requirements, consider variables passing via templating on existing environment, add other steps in dependency tasks to adjust for required ETL workflows.

Cloud Composer Benefits

Complex Orchestration: Handle sophisticated workflows with multiple dependencies, retries, and conditional logic.
Scalability: Supports large-scale data pipelines with distributed execution.
Integration: Integrates seamlessly with other Google Cloud services and third-party tools.

Cloud Composer Limitations

Requires an Airflow environment, thus having increased set up complexities relative to more out-of-the-box components such as Scheduled Queries that have been recently offered on BigQuery platform itself.
Might require maintenance since the version in used need security patched.
Cloud Composer has significant associated cost compare with Cloud Functions and scheduling BQ jobs for basic ETL tasks.

When to use Cloud Composer:

Utilized complex inter-related transformations across multiple different systems (BigQuery, Google Cloud Storage, Compute Engine etc) in orchestrating large-scale ETLs requiring multi-layered orchestrations, and complex tasks, workflows. Use BigQuery transfer jobs instead if ingestion (instead of transformation tasks) from several sources into a few different destination tables required..
Tasks executions in various external system dependencies such a shell executions that involves Big Query client actions, DataProc based Spark applications/transformations,

Choosing the Right Method

The optimal method for scheduling BigQuery queries depends on the complexity of your requirements:

Simple Schedules: If you just need a basic periodic table processing operation (i.e table-to-table operations based on defined date criteria, aggregations for a set frequency schedule on table based operations only), Scheduled Queries are the most appropriate.
Event-Triggered Actions or Moderate Workload Complexity: For moderately-orchestrated transformations requiring conditionals or more sophisticated validation use Cloud Functions triggered by Cloud Scheduler. It provide increased level of code controlled operation based on task scheduled (vs cron schedule alone.)
Complex Orchestrations and Dependency management : If more orchestrated tasks requirements with interdependent scheduling, advanced validations are requires useCloud Composer.

Best Practices for Scheduled Queries

Service Account Management: Securely manage the service accounts used for running scheduled queries, granting only the necessary permissions.
Monitor Execution Logs: Regularly review execution logs (using Cloud Logging) to identify errors and optimize query performance and cost effectiveness .
Use Incremental Updates: When processing large datasets, implement incremental updates instead of overwriting the entire table, reduces data scanned on transformations and minimize overall costs and resource allocations in the bigdata architecture.
Cost Optimization: Optimize your queries to reduce processing costs. Use techniques like data partitioning, clustering, and appropriate data types to minimize the amount of data scanned.
Avoid unbounded results Use a limit or date partitioned approach that avoids running expensive query (on the large scans). Review execution logs to observe whether data scans/query cost aligns as required during schedule time frame and whether optimization are required..

Conclusion

By understanding and applying the methods described above, you can effectively schedule BigQuery queries and automate your data processing workflows, enabling timely data insights, reporting, and the seamless operation of complex ETL pipelines. Select the right method for schedule management using criteria described and start reaping value. Consider security permissions and cost effectiveness to be essential at each development step so optimal resources utilization.