BigQuery Create external table

External tables let BigQuery query data that is stored outside of BigQuery storage

Create external table example

    CREATE EXTERNAL TABLE dataset.CsvTable OPTIONS (
        format = 'CSV',
        uris = ['gs://bucket/path1.csv']
      );

External table with pipe delimiter

    CREATE OR REPLACE EXTERNAL TABLE dataset.CsvTable
    (
        x INT64,
        y STRING
    )
    OPTIONS (
    format = 'CSV',
    uris = ['gs://bucket/path1.csv'],
    field_delimiter = '|',
    max_bad_records = 5
    );

External table with autodetect partitions

The 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).

    CREATE EXTERNAL TABLE dataset.AutoHivePartitionedTable
    WITH PARTITION COLUMNS
    OPTIONS (
      uris=['gs://bucket/path/*'],
      format=csv,
      hive_partition_uri_prefix='gs://bucket/path'
    );

External table with declared partitions

The 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

    CREATE EXTERNAL TABLE dataset.CustomHivePartitionedTable
    WITH PARTITION COLUMNS (
      field_1 STRING, -- column order must match the external path
      field_2 INT64
    )
    OPTIONS (
      uris=['gs://bucket/path/*'],
      format=csv,
      hive_partition_uri_prefix='gs://bucket/path'
    );

Explanation

The External table has the following options

Syntax reference

CREATE [OR REPLACE] EXTERNAL TABLE [IF NOT EXISTS] [[project_name.]dataset_name.]table_name
[(
  column_name column_schema,
  ...
)]

[WITH PARTITION COLUMNS
  [(
      partition_column_name partition_column_type,
      ...
  )]
]
OPTIONS (
  external_table_option_list,
  ...
);