Create a table name without back tick when using set variable

akshayauser
Databricks Partner

‎06-07-2024 11:01 AM

When i tried to create a table name with variable like this

-- Set a string variable
SET table_suffix = 'suffix';

-- Use dynamic SQL to create a table with the variable as a suffix in the table name
CREATE TABLE IF NOT EXISTS <dbname>.my_table_${table_suffix} (
    id INT,
    name STRING,
    age INT
)

I could able to fetch the data from this table like this.

select * from gmi_im.my_table_`suffix`

But I need it without back ticks . Is there any po way?

 

0 Kudos

high-energy
New Contributor III

‎06-08-2024 07:44 AM

 

I'm not that familiar with SQL variables. I can use them in queries, but can't seem to get the DDL to use them.

What you are trying to do is simpler in python. See below for an example.

table_suffix = 'suffix';

spark.sql(f"""
            CREATE TABLE IF NOT EXISTS shawn.default.table_with_{table_suffix} (
            id INT,
            name STRING,
            age INT
        )
        """ )

spark.sql(f"""
            INSERT INTO shawn.default.table_with_{table_suffix}
            VALUES
            (1, 'Batman', 30),
            (2, 'Robin', 20)
        """ )

df = spark.read.table(f"shawn.default.table_with_{table_suffix}")

display(df)
0 Kudos

brockb
Databricks Employee
Databricks Employee

‎06-08-2024 08:33 PM

Hi,

It's possible that the `identifier` clause is what you're looking for (https://docs.databricks.com/en/sql/language-manual/sql-ref-names-identifier-clause.html#identifier-c...). If so, this basic example should work:

DECLARE mytab = '`mycatalog`.`myschema`.t1';
DECLARE mytab_suffix = 'suffix';
CREATE TABLE IF NOT EXISTS IDENTIFIER(mytab || '_' || mytab_suffix) (c1 int);
SELECT * FROM IDENTIFIER(mytab || '_' || mytab_suffix);

Hope it helps.