How to set a variable and use it in a SQL query

[Mado]

I want to define a variable and use it in a query, like below:

%sql
 
SET database_name = "marketing";
SHOW TABLES in '${database_name}';

However, I get the following error:

ParseException: 
[PARSE_SYNTAX_ERROR] Syntax error at or near ''''(line 1, pos 15)
 
== SQL ==
SHOW TABLES in ''

I am aware that I can use "spark.sql("query")" and python f-string. But, I would like to know how to use SQL for this purpose.

Thanks.

[Pat · Accepted solution]

Hi @Mohammad Saber​ ,

SET is used for Setting Spark parameters.

I think the closest alternative to what you are trying to achive is to use widgets

https://docs.databricks.com/notebooks/widgets.html#language-sql

CREATE WIDGET TEXT database DEFAULT "customers_dev"

Then

SHOW TABLES IN "${database}"

[Kaniz]

Hi @Mohammad Saber​, We haven’t heard from you since the last response from @Pat Sienkiewicz​, and I was checking back to see if his suggestions helped you.

Or else, If you have any solution, please share it with the community, as it can be helpful to others.

Also, Please don't forget to click on the "Select As Best" button whenever the information provided helps resolve your question.

[CJS]

Another option is demonstrated by this example:

%sql
 
SET database_name.var = marketing;
SHOW TABLES in ${database_name.var};


SET database_name.dummy= marketing;
SHOW TABLES in ${database_name.dummy};

• do not use quotes
• use format that is variableName.something and it will work in %sql.  I don't know why.  I attached an example from my environment.  I redacted names and data, but you can see that it works with .var and .dummy.  You'll have to trust that I didn't use quotes

FYI: Widget Documentation explains in the blue Note that using the widget method will not work when you Run All or run from another notebook: Databricks widgets | Databricks on AWS

[CJS]

lol that attachment worked SO poorly.  Here it is: