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}"

[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: