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:

[olufemi_anthony]

Updated usage as of DBR 15.2+

https://docs.databricks.com/en/notebooks/widgets.html#use-widget-values-in-spark-sql-and-sql-warehou...

[TomRenish]

CJS had the best answer by virtue of it being code-based rather than widget-based.  In a notebook where the value of the variable must continually be reset, widgets are suboptimal.  That said, the easiest way is to wrap the code in python:

%py

var1 = some_value ##string, int, whatever

var2 = some_other_value 

qry = f"select * from {var1} where some_field = {var2}" ##use python's format feature to place variables cleanly

display(spark.sql(qry))