Query Cloud Storage data in external tables
This document describes how to query data stored in a Cloud Storage external table.
Before you begin
Ensure that you have a Cloud Storage external table.
Required roles
To query Cloud Storage external tables, ensure you have the following roles:
- BigQuery Data Viewer (
roles/bigquery.dataViewer
) - BigQuery User (
roles/bigquery.user
) - Storage Object Viewer (
roles/storage.objectViewer
)
Depending on your permissions, you can grant these roles to yourself or ask your administrator to grant them to you. For more information about granting roles, see Viewing the grantable roles on resources.
To see the exact BigQuery permissions that are required to query external tables, expand the Required permissions section:
Required permissions
bigquery.jobs.create
bigquery.readsessions.create
(Only required if you are reading data with the BigQuery Storage Read API)bigquery.tables.get
bigquery.tables.getData
You might also be able to get these permissions with custom roles or other predefined roles.
Query permanent external tables
After creating a Cloud Storage external table, you can query it using
GoogleSQL syntax, the same as if
it were a standard BigQuery table. For example, SELECT field1, field2
FROM mydataset.my_cloud_storage_table;
.
Query temporary external tables
Querying an external data source using a temporary table is useful for one-time, ad-hoc queries over external data, or for extract, transform, and load (ETL) processes.
To query an external data source without creating a permanent table, you provide a table definition for the temporary table, and then use that table definition in a command or call to query the temporary table. You can provide the table definition in any of the following ways:
- A table definition file
- An inline schema definition
- A JSON schema file
The table definition file or supplied schema is used to create the temporary external table, and the query runs against the temporary external table.
When you use a temporary external table, you do not create a table in one of your BigQuery datasets. Because the table is not permanently stored in a dataset, it cannot be shared with others.
You can create and query a temporary table linked to an external data source by using the bq command-line tool, the API, or the client libraries.
bq
You query a temporary table linked to an external data source using the
bq query
command
with the
--external_table_definition
flag.
When you use the bq command-line tool to query a temporary table linked to an external
data source, you can identify the table's schema using:
- A table definition file (stored on your local machine)
- An inline schema definition
- A JSON schema file (stored on your local machine)
(Optional) Supply the --location
flag and set the value to your
location.
To query a temporary table linked to your external data source using a table definition file, enter the following command.
bq --location=LOCATION query \ --external_table_definition=TABLE::DEFINITION_FILE \ 'QUERY'
Replace the following:
LOCATION
: the name of your location. The--location
flag is optional. For example, if you are using BigQuery in the Tokyo region, you can set the flag's value toasia-northeast1
. You can set a default value for the location using the .bigqueryrc file.TABLE
: the name of the temporary table you're creating.DEFINITION_FILE
: the path to the table definition file on your local machine.QUERY
: the query you're submitting to the temporary table.
For example, the following command creates and queries a temporary table
named sales
using a table definition file named sales_def
.
bq query \
--external_table_definition=sales::sales_def \
'SELECT
Region,
Total_sales
FROM
sales'
To query a temporary table linked to your external data source using an inline schema definition, enter the following command.
bq --location=LOCATION query \ --external_table_definition=TABLE::SCHEMA@SOURCE_FORMAT=BUCKET_PATH \ 'QUERY'
Replace the following:
LOCATION
: the name of your location. The--location
flag is optional. For example, if you are using BigQuery in the Tokyo region, you can set the flag's value toasia-northeast1
. You can set a default value for the location using the .bigqueryrc file.TABLE
: the name of the temporary table you're creating.SCHEMA
: the inline schema definition in the formatfield:data_type,field:data_type
.SOURCE_FORMAT
: the format of the external data source, for example,CSV
.BUCKET_PATH
: the path to the Cloud Storage bucket that contains the data for the table, in the formatgs://bucket_name/[folder_name/]file_pattern
.You can select multiple files from the bucket by specifying one asterisk (
*
) wildcard character in thefile_pattern
. For example,gs://mybucket/file00*.parquet
. For more information, see Wildcard support for Cloud Storage URIs.You can specify multiple buckets for the
uris
option by providing multiple paths.The following examples show valid
uris
values:gs://bucket/path1/myfile.csv
gs://bucket/path1/*.parquet
gs://bucket/path1/file1*
,gs://bucket1/path1/*
When you specify
uris
values that target multiple files, all of those files must share a compatible schema.For more information about using Cloud Storage URIs in BigQuery, see Cloud Storage resource path.
QUERY
: the query you're submitting to the temporary table.
For example, the following command creates and queries a temporary table
named sales
linked to a CSV file stored in Cloud Storage with the
following schema definition:
Region:STRING,Quarter:STRING,Total_sales:INTEGER
.
bq query \
--external_table_definition=sales::Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=gs://mybucket/sales.csv \
'SELECT
Region,
Total_sales
FROM
sales'
To query a temporary table linked to your external data source using a JSON schema file, enter the following command.
bq --location=LOCATION query \ --external_table_definition=SCHEMA_FILE@SOURCE_FORMAT=BUCKET_PATH \ 'QUERY'
Replace the following:
LOCATION
: the name of your location. The--location
flag is optional. For example, if you are using BigQuery in the Tokyo region, you can set the flag's value toasia-northeast1
. You can set a default value for the location using the .bigqueryrc file.SCHEMA_FILE
: the path to the JSON schema file on your local machine.SOURCE_FORMAT
: the format of the external data source, for example,CSV
.BUCKET_PATH
: the path to the Cloud Storage bucket that contains the data for the table, in the formatgs://bucket_name/[folder_name/]file_pattern
.You can select multiple files from the bucket by specifying one asterisk (
*
) wildcard character in thefile_pattern
. For example,gs://mybucket/file00*.parquet
. For more information, see Wildcard support for Cloud Storage URIs.You can specify multiple buckets for the
uris
option by providing multiple paths.The following examples show valid
uris
values:gs://bucket/path1/myfile.csv
gs://bucket/path1/*.parquet
gs://bucket/path1/file1*
,gs://bucket1/path1/*
When you specify
uris
values that target multiple files, all of those files must share a compatible schema.For more information about using Cloud Storage URIs in BigQuery, see Cloud Storage resource path.
QUERY
: the query you're submitting to the temporary table.
For example, the following command creates and queries a temporary table
named sales
linked to a CSV file stored in Cloud Storage using the
/tmp/sales_schema.json
schema file.
bq query \ --external_table_definition=sales::/tmp/sales_schema.json@CSV=gs://mybucket/sales.csv \ 'SELECT Region, Total_sales FROM sales'
API
To run a query using the API, follow these steps:
- Create a
Job
object. - Populate the
configuration
section of theJob
object with aJobConfiguration
object. - Populate the
query
section of theJobConfiguration
object with aJobConfigurationQuery
object. - Populate the
tableDefinitions
section of theJobConfigurationQuery
object with anExternalDataConfiguration
object. - Call the
jobs.insert
method to run the query asynchronously or thejobs.query
method to run the query synchronously, passing in theJob
object.
Java
Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
Node.js
Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Node.js API reference documentation.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
Python
Before trying this sample, follow the Python setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Python API reference documentation.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
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 in
Cloud Storage, Google Drive,
Amazon S3, and Azure 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 alias fn
to the pseudocolumn.
bq query \
--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) -
DATASET
is 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 about using SQL in BigQuery.
- Learn about external tables.
- Learn about BigQuery quotas.