topic Re: Add timestamp to table name using SQL Editor in Warehousing & Analytics

Add timestamp to table name using SQL Editor

BobDobalina — Wed, 10 Jan 2024 11:15:44 GMT

Hi,

am sure am missing something as this should be something trivial but am struggling to find how to add a suffix with a date to a table name.

Does anyone have a way to do this?

Thanks

Re: Add timestamp to table name using SQL Editor

Lakshay — Thu, 11 Jan 2024 17:13:57 GMT

Could you share more details on the issue you are facing?

Re: Add timestamp to table name using SQL Editor

shan_chandra — Thu, 11 Jan 2024 17:20:23 GMT

Hi @BobDobalina - Dynamic naming of table name is not allowed in DBSQL. However, you can try something similar

%python from datetime import datetime date_suffix = datetime.now().strftime("%Y%m%d") table_name = f"students{date_suffix}" spark.sql(f"CREATE TABLE IF NOT EXISTS hive_metastore.db.{table_name} AS SELECT * FROM hive_metastore.db.`students`;")

Hope this helps!!!

Thanks,

Shan

Re: Add timestamp to table name using SQL Editor

BobDobalina — Tue, 16 Jan 2024 16:27:31 GMT

Hi @shan_chandra,

that worked perfectly. For my specific case I wanted the previous month as the table name suffix so used the below.

# import datetime module import datetime # get current date curr_date = datetime.date.today() # get first day of current month first =curr_date.replace(day=1) # get last day of previous month last_month = first-datetime.timedelta(days=1) # create last month suffix formatted as YYYYMM suffix = last_month.strftime("%Y%m") # create time stamped table by renaming temp table table_name = f"hive_metastore.db.table_name_{suffix}" spark.sql(f"ALTER TABLE hive_metastore.db.table_name RENAME TO {table_name};")

Thanks for your help,

Bruno