Creating and Querying External Data Sources

This page describes how to query data stored outside of BigQuery.

An external data source (also known as a federated data source) is a data source that you can query directly even though the data is not stored in BigQuery. Instead of loading or streaming the data, you create a table that references the external data source. You can create a permanent external table or a temporary table.

BigQuery offers support for querying data directly from Google Cloud Storage and Google Drive. Use cases include:

• Loading and cleaning your data in one pass by querying the data from an external data source (a location external to BigQuery) and writing the cleaned result into BigQuery storage.
• Having a small amount of frequently changing data that you join with other tables. As an external data source, the frequently changing data does not need to be reloaded every time it is updated.

Limitations include the following:

• BigQuery does not guarantee data consistency for external data sources. Changes to the underlying data while a query is running can result in unexpected behavior.
• If query speed is a priority, load the data into BigQuery instead of setting up an external data source. The performance of a query that includes an external data source depends on the external storage type. For example, querying data stored in Google Cloud Storage is faster than querying data stored in Google Drive. In general, query performance for external data sources should be equivalent to reading the data directly from the external storage.
• You cannot run a BigQuery job that exports data from an external data source.
• You cannot use the TableDataList JSON API method to retrieve data from tables that reside in an external data source. For more information, see Tabledata: list.

BigQuery supports the following data-source formats:

To directly query an external data source, provide the Cloud Storage or Google Drive URI path to your data and create an external table that references that data source. For CSV and JSON files, you can also include the table schema. If you omit the schema, BigQuery attempts to automatically detect the schema. For Avro and Cloud Datastore backup files, BigQuery reads the schema from the data source.

Before you begin

• Identify a data file that you plan to use as your external data source.
• If you plan to use the bq command-line tool instead of the web UI, ensure that you have activated BigQuery and installed the gcloud SDK. For more information, see the Before you begin section of the command-line tool quickstart.
• The command-line tool examples on this page assume that you have set a default project so that you can omit the project_id flag. For information on how to set a default project, see gcloud SDK documentation for gcloud config set.

Creating external tables

An external table is a permanent table that references an external data source, which means that the data is not stored in BigQuery. To create an external table, use the external table type. Because the table is permanent, you can share the table with others who also have access to the underlying external data source.

Creating an external table using Cloud Storage

Creating an external table using Google Drive

Before you begin, ensure that you have enabled:

• Google Drive access and the
• Google Drive API.

To specify a Google Drive file, use one of these URI formats:

https://drive.google.com/open?id=[FILE_ID]

https://docs.google.com/spreadsheets/d/[FILE_ID]/edit#gid=[IDENTIFIER]

Querying external data using temporary tables

To directly query an external data source without creating a permanent table, run a query that creates a temporary table. Querying a temporary table is useful for one-time, ad-hoc queries over remote data, or for extract, transform, and load (ETL) processes.

The example below creates a table definition file that contains the schema. The example then uses the definition file and the data source URIs to create and query a temporary table.

Querying data from Cloud Storage

Querying data from Google Drive

Before you begin, ensure that you have enabled Google Drive access.

To specify a Google Drive file, use one of these URI formats:

https://drive.google.com/open?id=[FILE_ID]

https://docs.google.com/spreadsheets/d/[FILE_ID]/edit#gid=[IDENTIFIER]

Creating table definition files

A table definition file contains metadata about a table, such as the table's source format and related properties. Table definition files can be used to create permanent external tables or temporary external tables. The command-line tool and the API support the creation and use of table definition files.

To create and use a table definition:

Many table definition configuration settings apply to more than one source format. For detailed information about settings and source formats, see the API Reference descriptions for each of the following settings:

For the complete list of table definition configuration settings, see configuration.query.tableDefinitions in the API Reference.

Table definitions based on CSV source files

The following command creates a table definition based on a CSV file named fed- sample.csv in a Cloud Storage bucket named [BUCKET], where the schema is stored in /tmp/fed-sample-schema.json and the generated definition file is stored in /tmp/fed-sample-def:

bq mkdef --source_format=CSV 'gs://[BUCKET]/fed-sample.csv' /tmp/fed-sample-schema.json > /tmp/fed-sample-def

The contents of fed-sample-def (schema values omitted for brevity):

{
  "csvOptions": {
    "allowJaggedRows": false,
    "allowQuotedNewlines": false,
    "encoding": "UTF-8",
    "fieldDelimiter": ",",
    "quote": "\"",
    "skipLeadingRows": 0
  },
  "schema": {
    "fields": [
      ...
    ]
  },
  "sourceFormat": "CSV",
  "sourceUris": [
    "gs://[BUCKET]/fed-sample.csv"
  ]
}

You can modify a table definition file so that subsequent calls to bq mk or bq query use the updated values. For example, if you decide that you want to allow jagged rows, you can modify that property in fed-sample-def:

{
  "csvOptions": {
    "allowJaggedRows": true,
  ...

The following options are available only for table definitions based on CSV files:

Table definitions based on JSON source files

The following command creates a table definition based on a JSON file named fed-sample.json in a Cloud Storage bucket named [BUCKET], where the schema is stored in /tmp/fed-sample-schema.json and the generated definition file is stored in /tmp/fed-sample-def:

bq mkdef --source_format=NEWLINE_DELIMITED_JSON 'gs://[BUCKET]/fed-sample.json' /tmp/fed-sample-schema.json > /tmp/fed-sample-json-def

The contents of fed-sample-json-def (schema values omitted for brevity):

{
  "autodetect": true,
  "schema": {
    "fields": [
      ...
    ]
  },
  "sourceFormat": "NEWLINE_DELIMITED_JSON",
  "sourceUris": [
    "gs://[BUCKET]/fed-sample.json"
  ]
}

There are no configuration settings specific to JSON source files, but many of the general settings apply to JSON files. For more information, see General table definition settings.

Table definitions based on Google Sheets source files

The following command creates a table definition based on a Google Sheet stored on Google Drive specified by [GOOGLE_DRIVE_URI], where the schema is auto- detected and the generated definition file is stored in /tmp/google-sheet-def:

bq mkdef --source_format=GOOGLE_SHEETS "[GOOGLE_DRIVE_URI]" > /tmp/google-sheet-def

The contents of google-sheet-def:

{
  "autodetect": true,
  "sourceFormat": "GOOGLE_SHEETS",
  "sourceUris": [
    "[GOOGLE_DRIVE_URI]"
  ]
}

You can modify the table definition to skip leading rows by adding a new googleSheetsOptions section. The following example modifies the table definition so that it skips the first 3 rows:

{
  "autodetect": true,
  "sourceFormat": "GOOGLE_SHEETS",
  "sourceUris": [
    "[GOOGLE_DRIVE_URI]"
  ],
  "googleSheetsOptions": {
    "skipLeadingRows": 3
  }
}

For a list of general configuration settings, , see General table definition settings.

Enabling Google Drive access

Avro and Cloud Datastore backup schema detection

If you use an Avro file or a Cloud Datastore backup as an external data source, BigQuery detects the schema for you.

When BigQuery detects schemas, it might, on rare occasions, change a field name to make it compatible with BigQuery SQL syntax.

To see the detected schema, create an external table and use the bq show command. Alternately, you can use the BigQuery web UI to see the schema.

For information about data type conversion, see:

• Date type conversion from Loading Data from Cloud Datastore.
• Avro format data conversion table.

CSV and JSON schema auto-detection

If you specify a CSV or JSON file without including a schema description, BigQuery makes a best-effort attempt to automatically infer the schema. Automatic detection is currently available in the command-line tool and the BigQuery API.

BigQuery starts the inference process by selecting a random file in the data set and scanning up to 100 rows of the file to use as a representative sample. BigQuery then examines each field and attempts to assign a data type to that field based on the values in that sample.

Schema auto-detection is enabled by default. To disable auto-detection, you can pass the --noautodetect flag when creating the table definition using mkdef or modify an existing table definition file by changing the value of the autodetect property to false. For example, the following excerpt from a table definition JSON file disables auto-detection:

{
  "autodetect": false,
  ...
}

Other automatic detection details include:

Compression

BigQuery recognizes gzip-compatible file compression when opening a file.

CSV Delimiter

BigQuery detects the following delimiters:

• comma (,)
• pipe (|)
• tab (\t)

CSV Header

BigQuery infers headers by comparing the first row of the file with other rows in the data set. If the first line contains only strings, and the other lines do not, BigQuery assumes that the first row is a header row.

CSV Quoted new lines

BigQuery detects quoted new line characters within a CSV field and does not interpret the quoted new line character as a row boundary.

Timestamps

BigQuery detects a wide array of timestamp formats, including, but not limited to:

• `yyyy-mm-dd` in any order
• `yyyy-mm-dd hh:mm:ss`
• `yyyy-mm-dd hh:mm:ss.sss`

Other timestamp details:

• Date separators can be "-", "/", or "."
• A time zone can be appended using an offset or name

The _FILE_NAME pseudo column

Tables based on external data sources provide a pseudo column named _FILE_NAME. This column contains the fully qualified path to the file to which the row belongs. This column is available only in external tables that reference Google Cloud Storage and Google Drive.

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 demonstrates selecting _FILE_NAME by assigning the alias fn to the pseudo column.

1. Create a table definition for the table wikipedia using the URI for the files from a Cloud Storage bucket. For example:

   bq mkdef --source_format=CSV "gs://[BUCKET_URI]" id:integer,name:string,category:integer,depth:string > /tmp/wikipedia

   The [BUCKET_URI] comprises your bucket name and your filename. For example, if the Cloud Storage bucket is named mybucket and the data file is named fed-sample.csv, the bucket URI would be gs://mybucket/fed-sample.csv.

2. Query the _FILE_NAME pseudo column:

   bq query --project_id=<project ID> --external_table_definition=wikipedia::/tmp/wikipedia 'select name, _FILE_NAME as fn from wikipedia where name contains "Alex";' 

Limits

The limits for external data sources are the same as the limits for load jobs, as described in the Load jobs section on the Quota Policy page.