BigQuery Create external table
External tables let BigQuery query data that is stored outside of BigQuery storage
Create external table example
\n\n CREATE EXTERNAL TABLE dataset.CsvTable OPTIONS (\n format = 'CSV',\n uris = ['gs://bucket/path1.csv']\n );\n\n\n
External table with pipe delimiter
\n\n CREATE OR REPLACE EXTERNAL TABLE dataset.CsvTable\n (\n x INT64,\n y STRING\n )\n OPTIONS (\n format = 'CSV',\n uris = ['gs://bucket/path1.csv'],\n field_delimiter = '|',\n max_bad_records = 5\n );\n\n\n
External table with autodetect partitions
\nThe external path is gs://bucket/path/field_1=first/field_2=1/data.csv, the partition columns would be field_1 (STRING) and field_2 (INT64).
\n\n CREATE EXTERNAL TABLE dataset.AutoHivePartitionedTable\n WITH PARTITION COLUMNS\n OPTIONS (\n uris=['gs://bucket/path/*'],\n format=csv,\n hive_partition_uri_prefix='gs://bucket/path'\n );\n\n\n
External table with declared partitions
\nThe following example creates an externally partitioned table by explicitly specifying the partition columns. The external file path has the pattern gs://bucket/path/field_1=first/field_2=1/data.csv
\n\n CREATE EXTERNAL TABLE dataset.CustomHivePartitionedTable\n WITH PARTITION COLUMNS (\n field_1 STRING, -- column order must match the external path\n field_2 INT64\n )\n OPTIONS (\n uris=['gs://bucket/path/*'],\n format=csv,\n hive_partition_uri_prefix='gs://bucket/path'\n );\n\n
Explanation
The External table has the following options
\n\n| Options | \n|
|---|---|
| allow_jagged_rows | \n\n BOOL\n If true, allow rows that are missing trailing optional columns.\n Applies to CSV data.\n | \n
| allow_quoted_newlines | \n\n BOOL\n If true, allow quoted data sections that contain newline characters in the file.\n Applies to CSV data.\n | \n
| compression | \n\n STRING\n The compression type of the data source. Supported values include: GZIP. If not specified, the data source is uncompressed. \n Applies to CSV and JSON data.\n | \n
| description | \n\n STRING\n A description of this table.\n | \n
| enable_logical_types | \n\n BOOL\n If true, convert Avro logical types into their corresponding SQL types. For more information, see Logical types.\n Applies to Avro data.\n | \n
| encoding | \n\n STRING\n The character encoding of the data. Supported values include: UTF8 (or UTF-8), ISO_8859_1 (or ISO-8859-1).\n Applies to CSV data.\n | \n
| expiration_timestamp | \n\n TIMESTAMP\n The time when this table expires. If not specified, the table does not expire.\n Example: "2025-01-01 00:00:00 UTC".\n | \n
| field_delimiter | \n\n STRING\n The separator for fields in a CSV file.\n Applies to CSV data.\n | \n
| format | \n\n STRING\n The format of the external data. Supported values include: AVRO, CSV, DATASTORE_BACKUP, GOOGLE_SHEETS, NEWLINE_DELIMITED_JSON (or JSON), ORC, PARQUET. \n The value JSON is equivalent to NEWLINE_DELIMITED_JSON.\n | \n
| decimal_target_types | \n\n ARRAY | \n
| json_extension | \n\n STRING\n For JSON data, indicates a particular JSON interchange format. If not specified, BigQuery reads the data as generic JSON records.\n Supported values include: GEOJSON (Preview). GeoJSON data. For more information, see Loading GeoJSON data. \n | \n
| hive_partition_uri_prefix | \n\n STRING\n A common prefix for all source URIs before the partition key encoding begins. Applies only to hive-partitioned external tables.\n Applies to Avro, CSV, JSON, Parquet, and ORC data.\n Example: "gs://bucket/path".\n | \n
| ignore_unknown_values | \n\n BOOL\n If true, ignore extra values that are not represented in the table schema, without returning an error.\n Applies to CSV and JSON data.\n | \n
| max_bad_records | \n\n INT64\n The maximum number of bad records to ignore when reading the data.\n Applies to: CSV, JSON, and Sheets data.\n | \n
| null_marker | \n\n STRING\n The string that represents NULL values in a CSV file.\n Applies to CSV data.\n | \n
| projection_fields | \n\n STRING\n A list of entity properties to load.\n Applies to Datastore data.\n | \n
| quote | \n\n STRING\n The string used to quote data sections in a CSV file. If your data contains quoted newline characters, also set the allow_quoted_newlines property to true.\n Applies to CSV data.\n | \n
| require_hive_partition_filter | \n\n BOOL\n If true, all queries over this table require a partition filter that can be used to eliminate partitions when reading data. Applies only to hive-partitioned external tables.\n Applies to Avro, CSV, JSON, Parquet, and ORC data.\n | \n
| sheet_range | \n\n STRING\n Range of a Sheets spreadsheet to query from.\n Applies to Sheets data.\n Example: ?sheet1!A1:B20?,\n | \n
| skip_leading_rows | \n\n INT64\n The number of rows at the top of a file to skip when reading the data.\n Applies to CSV and Sheets data.\n | \n
| uris | \n\n ARRAY | \n
Syntax reference
CREATE [OR REPLACE] EXTERNAL TABLE [IF NOT EXISTS] [[project_name.]dataset_name.]table_name\n[(\n column_name column_schema,\n ...\n)]\n\n[WITH PARTITION COLUMNS\n [(\n partition_column_name partition_column_type,\n ...\n )]\n]\nOPTIONS (\n external_table_option_list,\n ...\n);