Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

BigQuery - CREATE EXTERNAL TABLE

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.

like image 894
Émerson Engroff Avatar asked Sep 01 '25 20:09

Émerson Engroff


1 Answers

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

like image 69
Soumendra Mishra Avatar answered Sep 03 '25 12:09

Soumendra Mishra