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.

Answer 1

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

Answer 2

I have the same problem and I saw the earlier solution. It worked but with twicking. I suggested an edit since create external table is working on GCP big query console, but the edit is not going through. Here is how I created external table to read csv data in GCS.

CREATE EXTERNAL TABLE IF NOT EXISTS PROJ_ID.SCHEMA_NAME.TABLE_NAME
    (
    col1 STRING,
    col2 STRING
    )
    OPTIONS(
      field_delimiter = '\t',
      format = 'csv',
      URIs = ['gs://bucket_name/prefix/*']
    );

Make sure that the dataset and the schema are already created