topic Create a table name without back tick when using set variable in Data Engineering

Create a table name without back tick when using set variable

akshayauser — Fri, 07 Jun 2024 18:01:08 GMT

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?

Re: Create a table name without back tick when using set variable

high-energy — Sat, 08 Jun 2024 14:44:58 GMT

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)

Re: Create a table name without back tick when using set variable

brockb — Sun, 09 Jun 2024 03:33:12 GMT

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-clause). 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.