BigQuery - CREATE EXTERNAL TABLE

Question

How use CREATE EXTERNAL TABLE DDL statement in BigQuery?

Another big datawarehouses solutions like SnowFlake and Hive Based(Presto, AWS Athena...) have it, and its so useful.

user_14048579 · Answer

Update 10/14/2020, CREATE EXTERNAL TABLE is released today.

CREATE EXTERNAL TABLE dataset.table 
OPTIONS (
  format = 'NEWLINE_DELIMITED_JSON',
  uris = ['gs://bucket/*.json']
);

CREATE EXTERNAL TABLE option is not available in BigQuery, but as an alternative you can use BigQuery command-line-interface to achieve this:

Create Table Definition File:

$ bq mkdef --autodetect --source_format=NEWLINE_DELIMITED_JSON "gs://gcp-bucket/*.json" > myschema

Create External Table:

$ bq mk --external_table_definition=myschema bq_dataset.bq_ext_table

Documentation Link:
https://cloud.google.com/bigquery/external-data-sources