is it possible to do "CREATE OR REPLACE TABLE foo_{@run_time|"%Y%m%d"} AS" in a scheduled query?

user_1919374 0
Viewed 189

I am wondering if/how i can run a CTAS type statement via a scheduled query but wrangle the @run_date or @run_time param to be the yyyymmdd suffix of the table i want to create or replace.

So the scheduled query would look like:

CREATE OR REPLACE TABLE foo_{@run_date|"%Y%m%d"} AS
select 'hello'

Such that if i was to run it on date of '2021-07-01' i would create the table called foo_20210701

I'm just not quite sure for to wrangle the default @run_date param to include it in my table name.

google-bigquery
1 Answers

As mentioned in a answer to a similar question and in the documentation:

Parameters cannot be used as substitutes for identifiers, column names, table names, or other parts of the query.

There is however a workaround. You could use EXECUTE IMMEDIATE to run a SQL script defined as a string. Eg.

EXECUTE IMMEDIATE "SELECT CURRENT_DATE()"

Although it is very hacky it could be used like this achieve what you need.

EXECUTE IMMEDIATE CONCAT('CREATE TABLE `some_project.some_dataset.foo_', CURRENT_DATE(), '` AS SELECT "hello" as column_name' );

And below an approach using a DECLARE statement to keep the table name as a variable

DECLARE table_name STRING DEFAULT CAST(CURRENT_DATE() AS STRING);

EXECUTE IMMEDIATE
  CONCAT('CREATE TABLE `some_project.some_dataset.foo_', table_name, '` AS SELECT "hello" as column_name' );
user_62681180
Related