How do I schedule query execution with BigQuery Data Transfer Service?

In this example, we will schedule a query execution using Google BigQuery Data Transfer Service with Pulumi. The BigQuery Data Transfer Service allows you to automate data movement into BigQuery on a scheduled, managed basis.

We will define a gcp.bigquery.DataTransferConfig resource to create a transfer configuration that specifies the details of the data transfer, including the data source, destination dataset, and schedule.

import * as pulumi from "@pulumi/pulumi";
import * as gcp from "@pulumi/gcp";

// Define the destination dataset
const dataset = new gcp.bigquery.Dataset("myDataset", {
    datasetId: "my_dataset",
    friendlyName: "My Dataset",
    description: "A dataset for scheduled query execution",
    location: "US",
});

// Define the BigQuery Data Transfer configuration
const dataTransferConfig = new gcp.bigquery.DataTransferConfig("myDataTransferConfig", {
    displayName: "My Scheduled Query",
    dataSourceId: "scheduled_query",
    destinationDatasetId: dataset.datasetId,
    schedule: "every 24 hours",
    params: {
        destination_table_name_template: "my_table_{run_date}",
        write_disposition: "WRITE_TRUNCATE",
        query: `
            SELECT
                current_timestamp() as current_time,
                COUNT(*) as total_rows
            FROM
                \`my_project.my_dataset.my_table\`
        `,
    },
    emailPreferences: {
        enableFailureEmail: true,
    },
});

export const datasetId = dataset.datasetId;
export const transferConfigId = dataTransferConfig.id;

Key Points

• We create a BigQuery dataset where the results of the scheduled query will be stored.
• We define a DataTransferConfig resource to configure the scheduled query execution.
• The schedule property specifies how often the query should run (e.g., “every 24 hours”).
• The params property includes the query to be executed and other configuration options such as the destination table name template and write disposition.
• The emailPreferences property enables email notifications for transfer failures.

Summary

We have demonstrated how to use Pulumi to schedule query execution with Google BigQuery Data Transfer Service. By defining a DataTransferConfig resource, we can automate the execution of queries on a specified schedule and manage the data transfer process efficiently.