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

  1. In the Google Cloud console, open the BigQuery page.

    Go to BigQuery

  2. 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 * with 0000..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 by 000000000000, and * in the second file's filename is replaced by 000000000001.
    • FORMAT: supported formats are JSON, AVRO, and CSV.
    • 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

  3. 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 * with 0000..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 by 000000000000, and * in the second file's filename is replaced by 000000000001.
  • FORMAT: supported formats are JSON, AVRO, and CSV.
  • 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