Console

Follow the web-based authentication steps when you create anexternal tablein the Google Cloud console. When you areprompted, clickAllow to give BigQuery Client Tools accessto Drive.

gcloud

1. In the Google Cloud console, activate Cloud Shell.

   Activate Cloud Shell

   At the bottom of the Google Cloud console, aCloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

2. Enter the following command to ensure that you have the latest version ofthe Google Cloud CLI.

   gcloud components update

3. Enter the following command to authenticate with Drive.

   gcloud auth login --enable-gdrive-access

API

Request the appropriateOAuth scope for Drivein addition to the scope for BigQuery:

1. Sign in by running thegcloud auth login --enable-gdrive-access command.
2. Obtain the OAuth access token with the Drive scope thatis used for your API by running thegcloud auth print-access-token command.

Python

1. Create an OAuth Client ID.

2. Set upApplication Default Credentials (ADC)in your local environment with the required scopes by doing thefollowing:

   1. Install the Google Cloud CLI, theninitialize it by running the followingcommand:

      gcloudinit

   2. Create local authentication credentials for your Google Account:

      gcloudauthapplication-defaultlogin\--client-id-file=CLIENT_ID_FILE\--scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform

      ReplaceCLIENT_ID_FILE with the filecontaining your OAuth Client ID.

      For more information, seeUser credentials provided by using the gcloud CLI.

Java

1. Create an OAuth Client ID.

2. Set upApplication Default Credentials (ADC)in your local environment with the required scopes by doing thefollowing:

   1. Install the Google Cloud CLI, theninitialize it by running the followingcommand:

      gcloudinit

   2. Create local authentication credentials for your Google Account:

      gcloudauthapplication-defaultlogin\--client-id-file=CLIENT_ID_FILE\--scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform

      ReplaceCLIENT_ID_FILE with the filecontaining your OAuth Client ID.

      For more information, seeUser credentials provided by using the gcloud CLI.

Console

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

   Go to BigQuery

2. In the left pane, clickexploreExplorer:

   

   If you don't see the left pane, clicklast_pageExpand left pane to open the pane.

3. In theExplorer pane, expand your project, clickDatasets, andthen select a dataset.

4. In the details pane, clickCreate tableadd_box.

5. On theCreate table page, in theSource section:

   • ForCreate table from, selectDrive.

   • In theSelect Drive URI field, enter theDrive URI.Note that wildcards are not supported for Drive URIs.

   • ForFile format, select the format of your data. Valid formatsfor Drive data include:

     • Comma-separated values (CSV)
     • Newline delimited JSON
     • Avro
     • Sheets
     Note: In the Google Cloud console, choosing Avro or Datastorebackup hides theAutomatically detect option because these filetypes are self-describing.

6. (Optional) If you choose Sheets, in theSheet range (Optional)box, specify the sheet and cell range to query. You can specify a sheetname, or you can specifysheet_name!top_left_cell_id:bottom_right_cell_idfor a cell range; for example, "Sheet1!A1:B20". IfSheet range is notspecified, the first sheet in the file is used.

7. On theCreate table page, in theDestination section:

   • ForDataset name, choose the appropriate dataset, and in theTable name field, enter the name of the table you're creating inBigQuery.

     

   • Verify thatTable type is set toExternal table.

8. In theSchema section, enter theschema definition.

   • For JSON or CSV files, you can check theAuto-detect option toenable schemaauto-detect.Auto-detect is not available for Datastore exports,Firestore exports, and Avro files. Schema information for thesefile types is automatically retrieved from the self-describing sourcedata.
   • Enter schema information manually by:
     • EnablingEdit as text and entering the table schema as a JSONarray.Note: You can view the schema of an existing table in JSONformat by entering the following command in the bq command-line tool:bq show --format=prettyjsonDATASET.TABLE.
     • UsingAdd field to manually input the schema.

9. ClickCreate table.

10. If necessary, select your account and then clickAllow to give theBigQuery client tools access to Drive.

You can then run a query against the table as if it were a standardBigQuery table, subject to thelimitationson external data sources.

After your query completes, you can download the results as CSV or JSON,save the results as a table, or save the results to Sheets. SeeDownload, save, and export datafor more information.

bq

You create a table in the bq command-line tool using thebq mk command. When you usethe bq command-line tool to create a table linked to an external data source, you canidentify the table's schema using:

• Atable definition file (stored onyour local machine)
• An inline schema definition
• A JSON schema file (stored on your local machine)

To create a permanent table linked to your Drive data source using atable definition file, enter the following command.

bqmk\--external_table_definition=DEFINITION_FILE\DATASET.TABLE

Where:

• DEFINITION_FILE is the path to thetable definition fileon your local machine.
• DATASET is the name of the dataset that contains the table.
• TABLE is the name of the table you're creating.

For example, the following command creates a permanent table namedmytableusing a table definition file namedmytable_def.

bq mk --external_table_definition=/tmp/mytable_def mydataset.mytable

To create a permanent table linked to your external data source using aninline schema definition, enter the following command.

bqmk\--external_table_definition=SCHEMA@SOURCE_FORMAT=DRIVE_URI\DATASET.TABLE

Where:

• SCHEMA is the schema definition in the formatFIELD:DATA_TYPE,FIELD:DATA_TYPE.
• SOURCE_FORMAT isCSV,NEWLINE_DELIMITED_JSON,AVRO, orGOOGLE_SHEETS.
• DRIVE_URI is yourDrive URI.
• DATASET is the name of the dataset that contains the table.
• TABLE is the name of the table you're creating.

For example, the following command creates a permanent table namedsaleslinked to a Sheets file stored in Drive with the followingschema definition:Region:STRING,Quarter:STRING,Total_sales:INTEGER.

bqmk\--external_table_definition=Region:STRING,Quarter:STRING,Total_sales:INTEGER@GOOGLE_SHEETS=https://drive.google.com/open?id=1234_AbCD12abCd \mydataset.sales

To create a permanent table linked to your external data source using a JSONschema file, enter the following command.

bqmk\--external_table_definition=SCHEMA_FILE@SOURCE_FORMAT=DRIVE_URI\DATASET.TABLE

Where:

• SCHEMA_FILE is the path to the JSON schema file on your localmachine.
• SOURCE_FORMAT isCSV,NEWLINE_DELIMITED_JSON,AVRO, orGOOGLE_SHEETS.
• DRIVE_URI is yourDrive URI.
• DATASET is the name of the dataset that contains the table.
• TABLE is the name of the table you're creating.

If yourtable definition filecontainsSheets-specific configuration,then you can skip leading rows and specify a defined sheet range.

The following example creates a table namedsales linkedto a CSV file stored in Drive using the/tmp/sales_schema.jsonschema file.

bqmk\--external_table_definition=/tmp/sales_schema.json@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \mydataset.sales

After the permanent table is created, you can then run a query against thetable as if it were a standard BigQuery table, subject to thelimitationson external data sources.

After your query completes, you can download the results as CSV or JSON,save the results as a table, or save the results to Sheets. SeeDownload, save, and export datafor more information.

API

Create anExternalDataConfigurationwhen you use thetables.insertAPI method. Specify theschema property or set theautodetect propertytotrue to enable schema auto detection for supported data sources.

Python

fromgoogle.cloudimportbigqueryimportgoogle.authcredentials,project=google.auth.default()# Construct a BigQuery client object.client=bigquery.Client(credentials=credentials,project=project)# TODO(developer): Set dataset_id to the ID of the dataset to fetch.# dataset_id = "your-project.your_dataset"# Configure the external data source.dataset=client.get_dataset(dataset_id)table_id="us_states"schema=[bigquery.SchemaField("name","STRING"),bigquery.SchemaField("post_abbr","STRING"),]table=bigquery.Table(dataset.table(table_id),schema=schema)external_config=bigquery.ExternalConfig("GOOGLE_SHEETS")# Use a shareable link or grant viewing access to the email address you# used to authenticate with BigQuery (this example Sheet is public).sheet_url=("https://docs.google.com/spreadsheets""/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing")external_config.source_uris=[sheet_url]options=external_config.google_sheets_optionsassertoptionsisnotNoneoptions.skip_leading_rows=1# Optionally skip header row.options.range=("us-states!A20:B49"# Optionally set range of the sheet to query from.)table.external_data_configuration=external_config# Create a permanent table linked to the Sheets file.table=client.create_table(table)# Make an API request.# Example query to find states starting with "W".sql='SELECT * FROM `{}.{}` WHERE name LIKE "W%"'.format(dataset_id,table_id)results=client.query_and_wait(sql)# Make an API request.# Wait for the query to complete.w_states=list(results)print("There are{} states with names starting with W in the selected range.".format(len(w_states)))

Java

importcom.google.auth.oauth2.GoogleCredentials;importcom.google.auth.oauth2.ServiceAccountCredentials;importcom.google.cloud.bigquery.BigQuery;importcom.google.cloud.bigquery.BigQueryException;importcom.google.cloud.bigquery.BigQueryOptions;importcom.google.cloud.bigquery.ExternalTableDefinition;importcom.google.cloud.bigquery.Field;importcom.google.cloud.bigquery.GoogleSheetsOptions;importcom.google.cloud.bigquery.QueryJobConfiguration;importcom.google.cloud.bigquery.Schema;importcom.google.cloud.bigquery.StandardSQLTypeName;importcom.google.cloud.bigquery.TableId;importcom.google.cloud.bigquery.TableInfo;importcom.google.cloud.bigquery.TableResult;importcom.google.common.collect.ImmutableSet;importjava.io.IOException;// Sample to queries an external data source using a permanent tablepublicclassQueryExternalSheetsPerm{publicstaticvoidmain(String[]args){// TODO(developer): Replace these variables before running the sample.StringdatasetName="MY_DATASET_NAME";StringtableName="MY_TABLE_NAME";StringsourceUri="https://docs.google.com/spreadsheets/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing";Schemaschema=Schema.of(Field.of("name",StandardSQLTypeName.STRING),Field.of("post_abbr",StandardSQLTypeName.STRING));Stringquery=String.format("SELECT * FROM %s.%s WHERE name LIKE 'W%%'",datasetName,tableName);queryExternalSheetsPerm(datasetName,tableName,sourceUri,schema,query);}publicstaticvoidqueryExternalSheetsPerm(StringdatasetName,StringtableName,StringsourceUri,Schemaschema,Stringquery){try{GoogleCredentialscredentials=ServiceAccountCredentials.getApplicationDefault();// Initialize client that will be used to send requests. This client only needs to be created// once, and can be reused for multiple requests.BigQuerybigquery=BigQueryOptions.newBuilder().setCredentials(credentials).build().getService();// Skip header row in the file.GoogleSheetsOptionssheetsOptions=GoogleSheetsOptions.newBuilder().setSkipLeadingRows(1)// Optionally skip header row..setRange("us-states!A20:B49")// Optionally set range of the sheet to query from..build();TableIdtableId=TableId.of(datasetName,tableName);// Create a permanent table linked to the Sheets file.ExternalTableDefinitionexternalTable=ExternalTableDefinition.newBuilder(sourceUri,sheetsOptions).setSchema(schema).build();bigquery.create(TableInfo.of(tableId,externalTable));// Example query to find states starting with 'W'TableResultresults=bigquery.query(QueryJobConfiguration.of(query));results.iterateAll().forEach(row->row.forEach(val->System.out.printf("%s,",val.toString())));System.out.println("Query on external permanent table performed successfully.");}catch(BigQueryException|InterruptedException|IOExceptione){System.out.println("Query not performed \n"+e.toString());}}}