Export query results to Azure Storage
BigQuery Omni lets you directly export the result of a query against a BigQuery external table to Azure Storage.
Before you begin
Ensure that you have an Azure Storage connection. Within the connection, create a policy for the Azure Storage container path that you want to export to. Within that policy, create a role that has the
Microsoft.Storage/storageAccounts/blobServices/containers/write
permission.You must reserve slots to run queries in BigQuery Omni. For information on how to reserve slots, see Getting started with Reservations.
Export query results
BigQuery Omni writes to the specified Azure location regardless of any existing content. The export query can overwrite existing data or mix the query result with existing data. We recommended that you export to an empty Azure Storage container.
To run a query, use the Google Cloud console or the bq
command-line tool:
Google Cloud console
In the Google Cloud console, open the BigQuery page.
In the Query editor field, enter a standard SQL export query. Standard SQL is the default syntax in the Google Cloud console.
EXPORT DATA WITH CONNECTION `CONNECTION_REGION.CONNECTION_NAME` OPTIONS( uri="azure://AZURE_STORAGE_ACCOUNT_NAME.blob.core.windows.net/CONTAINER_NAME/FILE_PATH/*", format="FORMAT" ) AS QUERY
Replace the following:
CONNECTION_REGION
: the region where the connection was created.CONNECTION_NAME
: the connection name that you created with the necessary permission to write to the container.AZURE_STORAGE_ACCOUNT_NAME
: the name of the Azure Storage account to which you want to write the query result.CONTAINER_NAME
: the name of the container to which you want to write the query result.FILE_PATH
: the path where you want to write the exported file to. It must contain exactly one wildcard*
anywhere in the leaf directory of the path string, for example,../aa/*
,../aa/b*c
,../aa/*bc
, and../aa/bc*
. BigQuery replaces*
with0000..N
depending on the number of files exported. BigQuery determines the file count and sizes. If BigQuery decides to export two files, then*
in the first file's filename is replaced by000000000000
, and*
in the second file's filename is replaced by000000000001
.FORMAT
: supported formats areJSON
,AVRO
, andCSV
.QUERY
: the query to analyze the data that is stored in a BigQuery external table.
For example, the following query exports query results from BigQuery to
azure://account_name.blob.core.windows.net/container/*
in Azure Storage:EXPORT DATA WITH CONNECTION `azure-eastus2.write-conn` OPTIONS( uri="azure://account_name.blob.core.windows.net/container/*", format="CSV" ) AS SELECT * FROM my_dataset.my_table
Click Run.
bq
Use the query
command and specify standard SQL syntax by using the
--nouse_legacy_sql
or --use_legacy_sql=false
flag.
To run the export query, enter the following:
EXPORT DATA WITH CONNECTION `CONNECTION_REGION.CONNECTION_NAME` \ OPTIONS(uri="azure://AZURE_STORAGE_ACCOUNT_NAME.blob.core.windows.net/CONTAINER_NAME/FILE_PATH/*", format="FORMAT", ...) \ AS QUERY
Replace the following:
CONNECTION_REGION
: the region where the connection was created.CONNECTION_NAME
: the connection name that you created with the necessary permission to write to the container.AZURE_STORAGE_ACCOUNT_NAME
: the name of the Azure Storage account where you want to write the query result.CONTAINER_NAME
: the name of the container to which you want to write the query result.FILE_PATH
: the path that you want to write the exported file to. It must contain exactly one wildcard*
anywhere in the leaf directory of the path string, for example,../aa/*
,../aa/b*c
,../aa/*bc
, and../aa/bc*
. BigQuery replaces*
with0000..N
depending on the number of files exported. BigQuery determines the file count and sizes. If BigQuery decides to export two files, then*
in the first file's filename is replaced by000000000000
, and*
in the second file's filename is replaced by000000000001
.FORMAT
: supported formats areJSON
,AVRO
, andCSV
.QUERY
: the query to analyze the data that is stored in a BigQuery external table.
Troubleshooting
If you get an error related to quota failure
, then check if you have reserved
capacity for your queries. For more information about slot reservations, see
Before you begin in this document.
What's next
- For more information about exporting table data, see Exporting table data.