CODEWITHSUNDEEP
google-bigquery
E. Faslo
E. Faslo

Reputation: 375

Google BigQuery: Add date to table name when creating a table

I am writing a query I am planning to schedule using Big Query UI.

I would like to add a _TABLE_SUFFIX to this table which is equal to CURRENT_DATE.

How could I achieve that?

This is the query I am working on: 

IF 
  today != DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 DAY)
THEN 
  CREATE TABLE `project.dataset.tablename_<insert_current_date_here>`
  AS
  SELECT CURRENT_DATE() as today;
END IF;

Upvotes: 3

Views: 6621

Answers (3)

Greg
Greg

Reputation: 1991

Update (2023-01-09): I think Samuel's approach using an official templating solution here is ideal.

The best bet would be to generate the query dynamically, and then execute it statically.

This could be done using something like python.

from datetime import datetime

def get_query():
    return '''IF 
    today != DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 DAY)
    THEN 
    CREATE TABLE `project.dataset.%s`
    AS
    SELECT CURRENT_DATE() as today;
    END IF;''' % str(datetime.now())

Upvotes: 4

Bernie Lindner
Bernie Lindner

Reputation: 71

You could (whether you should is another debate) create dynamic table names via BQ's SQL procedural language capability, specifically the EXECUTE IMMEDIATE statement.

e.g.

DECLARE today STRING DEFAULT STRING(DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 DAY));

EXECUTE IMMEDIATE format("""
CREATE TABLE `project.dataset.tablename_%s` AS
SELECT CURRENT_DATE() as today
""", today);

For more also see towardsdatascience.com/how-to-use-dynamic-sql-in-bigquery.

Note you might also now get error location issues with EXECUTE IMMEDIATE , if so try changing/checking your Processing location in Query Settings, see here

Upvotes: 0

Samuel
Samuel

Reputation: 76

BigQuery supports a template system for destination table names in scheduled queries. To add the current date to the table name, use the provided template syntax. For example, tablename_{run_time|"%Y%m%d"} would output tablename_YYYYMMDD.

bigquery scheduled query settings

Upvotes: 4

Related Questions