1. Answers
  2. Scheduling query results export from BigQuery to Cloud Storage

How do I schedule query results export from BigQuery tables to Cloud Storage?

In this guide, we will automate the export of query results from BigQuery tables to Google Cloud Storage using Pulumi. We will create a BigQuery dataset, a BigQuery table, and set up a scheduled query to export the results to a Cloud Storage bucket.

Key Points

  • Create a BigQuery dataset and table.
  • Create a Cloud Storage bucket.
  • Schedule a query to export results from the BigQuery table to the Cloud Storage bucket.
import * as pulumi from "@pulumi/pulumi";
import * as gcp from "@pulumi/gcp";

// Create a BigQuery dataset
const dataset = new gcp.bigquery.Dataset("myDataset", {
    datasetId: "my_dataset",
    location: "US",
});

// Create a BigQuery table
const table = new gcp.bigquery.Table("myTable", {
    datasetId: dataset.datasetId,
    tableId: "my_table",
    schema: `[
        {"name": "name", "type": "STRING", "mode": "REQUIRED"},
        {"name": "age", "type": "INTEGER", "mode": "REQUIRED"}
    ]`,
});

// Create a Cloud Storage bucket
const bucket = new gcp.storage.Bucket("myBucket", {
    location: "US",
});

// Define a scheduled query
const scheduledQuery = new gcp.bigquery.DataTransferConfig("myScheduledQuery", {
    displayName: "Export BigQuery to Cloud Storage",
    dataSourceId: "scheduled_query",
    params: {
        destination_table_name_template: "export_table",
        write_disposition: "WRITE_TRUNCATE",
        query: `SELECT * FROM \`${dataset.datasetId}.${table.tableId}\``,
        destination_uri: `gs://${bucket.name}/exported_data`,
    },
    schedule: "every 24 hours",
    destinationDatasetId: dataset.datasetId,
});

export const bucketName = bucket.name;
export const datasetId = dataset.datasetId;
export const tableId = table.tableId;
export const scheduledQueryName = scheduledQuery.name;

Summary

In this guide, we created a BigQuery dataset and table, a Cloud Storage bucket, and scheduled a query to export the results from the BigQuery table to the Cloud Storage bucket. This setup ensures that query results are regularly exported, making data analysis and backup more efficient.

Deploy this code

Want to deploy this code? Sign up for a free Pulumi account to deploy in a few clicks.

Sign up

New to Pulumi?

Want to deploy this code? Sign up with Pulumi to deploy in a few clicks.

Sign up