Add timestamp to table name using SQL Editor

[BobDobalina]

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

[Lakshay]

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

[shan_chandra · Accepted solution]

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

[BobDobalina]

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