Query Blob Storage data

This document describes how to query data stored in anAzure Blob Storage BigLake table.

Before you begin

Ensure that you have aBlob Storage BigLake table.

Required roles

To query Blob Storage BigLake tables, ensurethat the caller of the BigQuery API has the following roles:

• BigQuery Connection User (roles/bigquery.connectionUser)
• BigQuery Data Viewer (roles/bigquery.dataViewer)
• BigQuery User (roles/bigquery.user)

The caller can be your account or anBlob Storage connection service account.Depending on your permissions, you cangrant these roles to yourself or ask your administratorto grant them to you. For more information about granting roles, seeViewing the grantable roles on resources.

To see the exact permissions that are required to queryBlob Storage BigLake tables, expand theRequired permissions section:

You might also be able to get these permissions withcustom rolesor otherpredefined roles.

Query Blob Storage BigLake tables

After creating a Blob Storage BigLake table, you canquery it usingGoogleSQL syntax, the same as ifit were a standard BigQuery table.

Thecached query resultsare stored in a BigQuery temporary table. To query a temporaryBigLake table, seeQuery a temporary BigLake table.For more information about BigQuery Omni limitations and quotas, seelimitationsandquotas.

When creating a reservation in a BigQuery Omni region, use theEnterprise edition. To learn how to create a reservation with an edition, seeCreate reservations.

Run a query on the Blob Storage BigLake table:

1. In the Google Cloud console, go to theBigQuery page.

   Go to BigQuery

2. In the query editor, enter the following statement:

   SELECT*FROMDATASET_NAME.TABLE_NAME;

   Replace the following:

   • DATASET_NAME: the dataset name that you created

   • TABLE_NAME: the BigLake table that name you created

   • Clickplay_circleRun.

For more information about how to run queries, seeRun an interactive query.

Query a temporary table

BigQuery creates temporary tables to store query results.To retrieve query result from temporary tables, you can use the Google Cloud consoleor theBigQuery API.

Select one of the following options:

Query the_FILE_NAME pseudocolumn

Tables based on external data sources provide a pseudocolumn named_FILE_NAME. This column contains the fully qualified path to the file to which the row belongs. This column is available only for tables that reference external data stored inCloud Storage,Google Drive,Amazon S3, andAzure Blob Storage.

The_FILE_NAME column name is reserved, which means that you cannot create a column by that name in any of your tables. To select the value of_FILE_NAME, you must use an alias. The following example query demonstrates selecting_FILE_NAME by assigning the aliasfn to the pseudocolumn.

bqquery\--project_id=PROJECT_ID\--use_legacy_sql=false\'SELECT     name,     _FILE_NAME AS fn   FROM`DATASET.TABLE_NAME`   WHERE     name contains "Alex"'

Replace the following:

• PROJECT_ID is a valid project ID (this flag is not required if you use Cloud Shell or if you set a default project in the Google Cloud CLI)
• DATASETis the name of the dataset that stores the permanent external table
• TABLE_NAME is the name of the permanent external table

When the query has a filter predicate on the_FILE_NAME pseudocolumn, BigQuery attempts to skip reading files that do not satisfy the filter. Similar recommendations to querying ingestion-time partitioned tables using pseudocolumns apply when constructing query predicates with the_FILE_NAME pseudocolumn.

What's next

• Learn aboutusing SQL in BigQuery.
• Learn aboutBigQuery Omni.
• Learn aboutBigQuery quotas.