Create a Table with name Based on Current Date on Azure Databricks (preferably SQL, but could do Python)

Question

I need to create a table using the current date in Azure Databricks. Specifically I need the table name to look like this:

workbench.example_2003-11-5 (if the table were created today)

workbench.example_2003-11-6 (if the table were created tomorrow)

I tried the following:

SET p.myVar = CURRENT_DATE();

CREATE OR REPLACE TABLE workbench.example_${p.myVar} AS

SELECT
"1"
;

SELECT *
FROM workbench.example_${p.myVar}
;

This turns the following error:

Error in SQL statement: AnalysisException: could not resolve `prd.workbench.example_CURRENT_DATE` to a table-valued function; line 2 pos 5;
'Project [*]
+- 'UnresolvedTableValuedFunction [prd, workbench, example_CURRENT_DATE]

Declaring the parameter in a separate cell create a table named:

workbench.example_current_date()

Is there a way to pass a date into a table creation command Databricks SQL? If there is no way to do this using Databricks SQL can you do it in Databricks Python?

Answer 1

Please try below one if you are using Azure Databricks -

from pyspark.sql.functions import *

date = spark.sql("""select current_date() as cur_date""").first()["cur_date"]
dt = str(date).replace("-","_")

spark.sql("create table default.`{}` (id int)".format(dt))