CODEWITHSUNDEEP
google-bigquerygoogle-cloud-storage
Michel Hua
Michel Hua

Reputation: 1777

Using variables in BigQuery DDL with external GCS bucket

Using BigQuery data definition language and standard SQL only, is it possible to use current date as a variable to use it as a gs:// path in the following query ?

CREATE OR REPLACE EXTERNAL TABLE staging.my_table
OPTIONS (
  format = 'NEWLINE_DELIMITED_JSON',
  uris = ['gs://bucket/path/to/logs/2020-10-08/*.jsonl']
)

Upvotes: 0

Views: 671

Answers (1)

Michel Hua
Michel Hua

Reputation: 1777

EXECUTE IMMEDIATE format("""
  CREATE OR REPLACE EXTERNAL TABLE staging.my_table
  OPTIONS (
    format = 'NEWLINE_DELIMITED_JSON',
    uris = ['gs://bucket/path/to/%s/*.jsonl']
  )
""", CAST(CURRENT_DATE() AS STRING));

The best practice is to use Hive partitioning on GCS though

Upvotes: 1

Related Questions