google-cloud-platformgoogle-bigqueryddlexternal-tables

Execute DDL to create a native table in BigQuery from files stored on GCS

I would like to create a native table in BigQuery from a CSV file with DDL statements in a way that replicates the "create table from..." feature in BigQuery's UI as shown in the screenshot below:

enter image description here

My ultimate goal is use the query in a Dataform sqlx file.

When I try to do it with a CREATE TABLE statement, it fails with error Unknown option: format.

CREATE TABLE `dataset_name.table_name` (
field1 STRING,
field2 STRING
) 

OPTIONS(format = 'CSV',
uris = ['gs://path/to/file.csv'],
field_delimiter = ',',
skip_leading_rows=1);

While the query below works fine...

CREATE EXTERNAL TABLE `dataset_name.table_name` (
field1 STRING,
field2 STRING
) 

OPTIONS(format = 'CSV',
uris = ['gs://path/to/file.csv'],
field_delimiter = ',',
skip_leading_rows=1);

...but throws a warning message regarding disabled caching enter image description here

Is it only a matter of enabling metadata caching or am I missing something else ?

Thank you for your help !

Solution

  • You are not using the right SQL function. You have to use LOAD DATA as explained in the documentation