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.

did you see anywhere mentioning of `CREATE EXTERNAL TABLE` DDL? I don't think it is available in BigQuery yet. so you might want to make a feature request - https://issuetracker.google.com/issues/new?component=187149&template=0 — Mikhail Berlyant, Oct 06 '20 at 00:56
CREATE EXTERNAL TABLE is released today (10/14/2020), please check this out: https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_external_table_statement — Yun Zhang, Oct 14 '20 at 19:30
wow, that is impressive list in today's release!!! great job BigQuery Team!! thank you!! — Mikhail Berlyant, Oct 14 '20 at 19:43
@YunZhang Its not possible set `{"hivePartitioningOptions": {"mode": "STRINGS"}` equivalent, like in `bq mk --external_table_definition`, and "AUTO" are default, wich to me is a impeditive yet. — Émerson Engroff, Oct 17 '20 at 22:38
@YunZhang Another important think to me, is `information_schema.columns` work with external tables too, like to native tables. `bq show ` works fine with external tables. — Émerson Engroff, Oct 17 '20 at 22:45
@ÉmersonEngroff: I have prefixed "BigQuery" in the title for more clarify. If you don't like, please revoke it ! — Soumendra Mishra, Oct 20 '20 at 17:40

Soumendra Mishra · Answer 1 · 2020-10-19T03:47:34.913

8

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

edited Oct 19 '20 at 03:47

answered Oct 06 '20 at 05:26

Soumendra Mishra

3,483
1
12
38

I see that, but its not a standard in the area. – Émerson Engroff Oct 06 '20 at 12:39
I agree. You can use BigQuery client API, if you want ! – Soumendra Mishra Oct 06 '20 at 13:03
Yes, but i prefer something more close to ANSI/ISO standard for SQL, a standard de facto in the market. – Émerson Engroff Oct 06 '20 at 23:22

B. Tek · Answer 2 · 2022-12-05T10:25:35.713

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

BigQuery - CREATE EXTERNAL TABLE

2 Answers2

Linked