Query Drive data
This document describes how to query data stored in an Google Drive external table.
BigQuery supports queries against both personal Drive files and shared files. For more information on Drive, see Google Drive training and help.
You can query Drive data from a permanent external table or from a temporary external table that you create when you run the query.
Required roles
To query Drive external tables, ensure you have the following roles:
- BigQuery Data Viewer (
roles/bigquery.dataViewer
) - BigQuery User (
roles/bigquery.user
)
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.
Drive permissions
At a minimum, to query external data in Drive you must be
granted View
access to the Drive file linked to the external table.
Scopes for Compute Engine instances
When you create a Compute Engine instance, you can specify a list of scopes for the instance. The scopes control the instance's access to Google Cloud products, including Drive. Applications running on the VM use the service account to call Google Cloud APIs.
If you set up a Compute Engine instance to run as a
service account,
and that service account accesses an external table linked to a Drive
data source, you must add the
OAuth scope for Drive
(https://www.googleapis.com/auth/drive.readonly
) to the instance.
For information on applying scopes to a Compute Engine instance, see Changing the service account and access scopes for an instance. For more information on Compute Engine service accounts, see Service accounts.
Query Drive data using permanent external tables
After creating a Drive 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_drive_table;
.
Query Drive data using temporary 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.
Create and query temporary tables
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)
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'
Where:
LOCATION
is your location. The--location
flag is optional.TABLE
is the name of the temporary table you're creating.DEFINITION_FILE
is the path to the table definition file on your local machine.QUERY
is 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=DRIVE_URI \ 'QUERY'
Where:
LOCATION
is your location. The--location
flag is optional.TABLE
is the name of the temporary table you're creating.SCHEMA
is the inline schema definition in the formatFIELD:DATA_TYPE,FIELD:DATA_TYPE
.SOURCE_FORMAT
isCSV
,NEWLINE_DELIMITED_JSON
,AVRO
, orGOOGLE_SHEETS
.DRIVE_URI
is your Drive URI.QUERY
is 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 Drive with the following
schema definition: Region:STRING,Quarter:STRING,Total_sales:INTEGER
.
bq --location=US query \
--external_table_definition=sales::Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \
'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_FORMT=DRIVE_URI \ 'QUERY'
Where:
LOCATION
is your location. The--location
flag is optional.SCHEMA_FILE
is the path to the JSON schema file on your local machine.SOURCE_FILE
isCSV
,NEWLINE_DELIMITED_JSON
,AVRO
, orGOOGLE_SHEETS
.DRIVE_URI
is your Drive URI.QUERY
is 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 Drive using the
/tmp/sales_schema.json
schema file.
bq query \
--external_table_definition=sales::/tmp/sales_schema.json@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \
'SELECT
Total_sales
FROM
sales'
API
Create a query job configuration. See Querying data for information about calling
jobs.query
andjobs.insert
.Specify the external data source by creating an
ExternalDataConfiguration
.
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.
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.
Limitations
A BigQuery query can overload Google Sheets, resulting in an
error like Resources exceeded during query execution: Google Sheets service
overloaded.
Consider simplifying your spreadsheet; for example, by minimizing
the use of formulas. For more information, see external table
limitations.
What's next
- Learn about using SQL in BigQuery.
- Learn about external tables.
- Learn about BigQuery quotas.