Text of docs.cloud.google.com › bigquery › docs › external-data-drive as Styx crawled it on Nov 19, 2026. The page may have changed since. Only its words are kept, without pictures or layout, and only the first part of a long page.

Open the live page

Create Google 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. Create Google Drive external tables Google Drive external tables let you query data in Drive files—such as CSV, newline-delimited JSON, Avro, and Google Sheets files— directly from BigQuery without loading the data into BigQuery storage.

You can create external tables over both personal and shared Drive files. This document describes how to create and query Drive external tables. Before you begin Before you create an external table, ensure that you have the required permissions to create the table. For more information on Drive, see Drive training and help . Retrieve Drive URIs To create an external table for a Google Drive data source, you must provide the Drive URI .

You can retrieve the Drive URI directly from the URL of your Drive data: URI format https://docs.google.com/spreadsheets/d/ FILE_ID or https://drive.google.com/open?id= FILE_ID where FILE_ID is the alphanumeric ID for your Drive file. Authenticate and enable Drive access Accessing data hosted within Drive requires an additional OAuth scope. To authenticate to BigQuery and enable drive access, do the following: Console Follow the web-based authentication steps when you create an external table in the Google Cloud console.

When you are prompted, click Allow to give BigQuery Client Tools access to Drive. gcloud In the Google Cloud console, activate Cloud Shell. Activate Cloud Shell At the bottom of the Google Cloud console, a Cloud 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.

Enter the following command to ensure that you have the latest version of the Google Cloud CLI. gcloud components update Enter the following command to authenticate with Drive. gcloud auth login --enable-gdrive-access API Request the appropriate OAuth scope for Drive in addition to the scope for BigQuery: Sign in by running the gcloud auth login --enable-gdrive-access command . Obtain the OAuth access token with the Drive scope that is used for your API by running the gcloud auth print-access-token command .

Python Create an OAuth Client ID . Set up Application Default Credentials (ADC) in your local environment with the required scopes by doing the following: Install the Google Cloud CLI, then initialize it by running the following command: gcloud init Create local authentication credentials for your Google Account: gcloud auth application-default login \ --client-id-file = CLIENT_ID_FILE \ --scopes = https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform Replace CLIENT_ID_FILE with the file containing your OAuth Client ID.

For more information, see User credentials provided by using the gcloud CLI . Java Create an OAuth Client ID . Set up Application Default Credentials (ADC) in your local environment with the required scopes by doing the following: Install the Google Cloud CLI, then initialize it by running the following command: gcloud init Create local authentication credentials for your Google Account: gcloud auth application-default login \ --client-id-file = CLIENT_ID_FILE \ --scopes = https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform Replace CLIENT_ID_FILE with the file containing your OAuth Client ID.

For more information, see User credentials provided by using the gcloud CLI . Required roles To create an external table, you need the bigquery.tables.create BigQuery Identity and Access Management (IAM) permission. Each of the following predefined Identity and Access Management roles includes this permission: BigQuery Data Editor ( roles/bigquery.dataEditor ) BigQuery Data Owner ( roles/bigquery.dataOwner ) BigQuery Admin ( roles/bigquery.admin ) If you are not a principal in any of these roles, ask your administrator to grant you access or to create the external table for you.

For more information on Identity and Access Management roles and permissions in BigQuery, see Predefined roles and permissions . Create external tables You can create a permanent table linked to your external data source by: Using the Google Cloud console Using the bq command-line tool's mk command Creating an ExternalDataConfiguration when you use the tables.insert API method Using the client libraries To create an external table: Console In the Google Cloud console, open the BigQuery page.

Go to BigQuery In the left pane, click explore Explorer : If you don't see the left pane, click last_page Expand left pane to open the pane. In the Explorer pane, expand your project, click Datasets , and then select a dataset. In the details pane, click Create table add_box . On the Create table page, in the Source section: For Create table from , select Drive . In the Select Drive URI field, enter the Drive URI . Note that wildcards are not supported for Drive URIs.

For File format , select the format of your data. Valid formats for Drive data include: Comma-separated values (CSV) Newline delimited JSON Avro Sheets (Optional) If you choose Sheets, in the Sheet range (Optional) box, specify the sheet and cell range to query. You can specify a sheet name, or you can specify sheet_name!top_left_cell_id:bottom_right_cell_id for a cell range; for example, "Sheet1!A1:B20". If Sheet range is not specified, the first sheet in the file is used.

On the Create table page, in the Destination section: For Dataset name , choose the appropriate dataset, and in the Table name field, enter the name of the table you're creating in BigQuery. Verify that Table type is set to External table . In the Schema section, enter the schema definition. For JSON or CSV files, you can check the Auto-detect option to enable schema auto-detect . Auto-detect is not available for Datastore exports, Firestore exports, and Avro files.

Schema information for these file types is automatically retrieved from the self-describing source data. Enter schema information manually by: Enabling Edit as text and entering the table schema as a JSON array. Note: You can view the schema of an existing table in JSON format by entering the following command in the bq command-line tool: bq show --format=prettyjson DATASET . TABLE . Using Add field to manually input the schema.

Click Create table . If necessary, select your account and then click Allow to give the BigQuery client tools access to Drive. You can then run a query against the table as if it were a standard BigQuery table, subject to the limitations on 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. See Download, save, and export data for more information. bq You create a table in the bq command-line tool using the bq mk command.

When you use the bq command-line tool to create a 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 create a permanent table linked to your Drive data source using a table definition file, enter the following command. bq mk \ --external_table_definition = DEFINITION_FILE \ DATASET .

TABLE Where: DEFINITION_FILE is the path to the table definition file on 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 named mytable using a table definition file named mytable_def . bq

The rest of this page is on the live site.