Query Drive external tables | BigQuery | Google Cloud Documentation
Skip to main content Console English Deutsch Español Español – América Latina Français Indonesia Italiano Português Português – Brasil עברית 中文 – 简体 中文 – 繁體 日本語 한국어 Sign in BigQuery Start free Home Documentation Data analytics BigQuery Guides Stay organized with collections Save and categorize content based on your preferences. Query Drive external tables BigQuery lets you run GoogleSQL queries on data stored in a Google Drive external table so that you can analyze spreadsheets and comma-separated value (CSV) files directly from Drive.
You can query both personal and shared Drive files using either a permanent external table or a temporary external table that you create when you run the query. For more information about Drive, see Google Drive training and help . Limitations For limitations related to external tables, see external table limitations . 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 format FIELD : DATA_TYPE , FIELD : DATA_TYPE . SOURCE_FORMAT is CSV , NEWLINE_DELIMITED_JSON , AVRO , or GOOGLE_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 is CSV , NEWLINE_DELIMITED_JSON , AVRO , or GOOGLE_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 and jobs.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 . from google.cloud import bigquery import google.auth # Create credentials with Drive & BigQuery API scopes. # Both APIs must be enabled for your project before running this code. credentials , project = google . auth . default ( scopes = [ "https://www.googleapis.com/auth/drive" , "https://www.googleapis.com/auth/bigquery" , ] ) # Construct a BigQuery client object. client = bigquery .
Client ( credentials = credentials , project = project ) # Configure the external data source and query job
The rest of this page is on the live site.