Export query results to Azure Storage
This document describes how to export the result of a query that runs against a BigLake table to your Azure Blob storage.
Before you begin
- Ensure that you have a connection to access your Azure Blob storage.
Within the connection, you must create a policy for the Azure Storage
container path that you want to export to. Then, within that policy,
create a role that has the
- If you are on the flat-rate pricing model, then ensure that you have enabled the BigQuery Reservation API for your project. For information about pricing, see BigQuery Omni pricing.
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 recommend that you export the query result to an empty Azure Storage container.
In the Google Cloud console, go to the BigQuery page.
In the Query editor field, enter a Google Standard SQL export query:
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/bc*. BigQuery replaces
0000..Ndepending 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 by
*in the second file's filename is replaced by
FORMAT: supported formats are
QUERY: the query to analyze the data that is stored in a BigQuery external table.
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.
- Learn about BigQuery Omni.
- Learn how to export table data.
- Learn how to set up VPC Service Controls for BigQuery Omni.