cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

Create a table name without back tick when using set variable

akshayauser
New Contributor

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?

 

2 REPLIES 2

high-energy
New Contributor III

 

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)

brockb
Valued Contributor
Valued Contributor

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.

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!