cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Warehousing & Analytics
Engage in discussions on data warehousing, analytics, and BI solutions within the Databricks Community. Share insights, tips, and best practices for leveraging data for informed decision-making.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Add timestamp to table name using SQL Editor

BobDobalina
New Contributor II

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

1 ACCEPTED SOLUTION

Accepted Solutions

shan_chandra
Esteemed Contributor

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`;")

Screenshot 2024-01-11 at 11.19.16โ€ฏAM.png

Hope this helps!!!

Thanks,

Shan

View solution in original post

3 REPLIES 3

Lakshay
Esteemed Contributor

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

shan_chandra
Esteemed Contributor

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`;")

Screenshot 2024-01-11 at 11.19.16โ€ฏAM.png

Hope this helps!!!

Thanks,

Shan

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

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group