Google BigQuery - Download Query Results to Google Cloud Storage

Overview

Turn the results of your SQL SELECT statement into CSV files that get stored in Google Cloud Storage (GCS).

When exporting data from BigQuery, a file cannot contain more than 1GB of data. As a result, this Template automatically splits up data that is >1GB in size into multiple files. The name of these files will be the Bucket File Name provided, with _# base 0 enumeration appended to the file name, before the file extension. This is the default behavior provided by Google.

Ex. If you provide a file name of data.csv and your data is 2.4GB in total, 3 files would need to be generated. The names of these files will be data_0.csv, data_1.csv, and data_2.csv.

Variables

Name	Reference	Type	Required	Default	Options	Description
Query	BIGQUERY_QUERY	Alphanumeric	✅	None	-	Standard SQL query to be executed against BigQuery. Does not support Legacy SQL.
Bucket Name	BIGQUERY_BUCKET_NAME	Alphanumeric	✅	None	-	Name of the GCS bucket to store the results file(s) in.
Bucket File Name	BIGQUERY_DESTINATION_FILE_NAME	Alphanumeric	✅	None	-	Name of file to be generated with the results. Should be `.csv` extension. If the file size is >1GB, file name will be enumerated with `_#` before the extension.
Bucket Folder Name	BIGQUERY_DESTINATION_FOLDER_NAME	Alphanumeric	➖	None	-	Folder where the file(s) should be uploaded. Leaving blank will place the file in the root directory.
Service Account	GOOGLE_APPLICATION_CREDENTIALS	Password	✅	None	-	Either JSON from a Google Cloud Service account key or fill in with ${ALLI_GOOGLE_CLOUD_CREDENTIALS}, a built in workflow environment variable that will populate this input with valid credentials.
Project ID	BIGQUERY_PROJECT	Alphanumeric	➖	-	-	This is the project name used for big query connections, which can be found in the setting section of "Mange Client". If unsure of exact project name, fill in with ${ALLI_GOOGLE_CLOUD_PROJECT} and it will pull the value from client settings.
Location	BIGQUERY_LOCATION	Alphanumeric	➖	-	-	This is the location/region where your project is stored, which can be found in the setting section of "Mange Client". If unsure of exact location/region, fill in with ${ALLI_GOOGLE_CLOUD_BIGQUERY_LOCATION} and it will pull the value from client settings.

YAML

Below is the YAML template

YAML


source:
  template: Google BigQuery - Download Query Results to Google Cloud Storage
  inputs:
    BIGQUERY_QUERY:
    BIGQUERY_BUCKET_NAME:
    BIGQUERY_DESTINATION_FILE_NAME:
    BIGQUERY_DESTINATION_FOLDER_NAME:
    GOOGLE_APPLICATION_CREDENTIALS:
    BIGQUERY_PROJECT:
    BIGQUERY_LOCATION:
  type: TEMPLATE
guardrails:
  retry_count: 1
  retry_wait: 0h0m0s
  runtime_cutoff: 1h0m0s
  exclude_exit_code_ranges:
    - 101
    - 102
    - 200
    - 203
    - 205
    - 206
    - 10
    - 14