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

Mado
Valued Contributor II

‎11-15-2022 03:07 AM

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.

Labels:
0 Kudos

Pat
Esteemed Contributor

‎11-15-2022 11:37 AM

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

View solution in original post

CJS
New Contributor II

‎09-28-2023 01:43 PM

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

Preview file
154 KB
0 Kudos

CJS
New Contributor II
In response to CJS

‎09-28-2023 01:52 PM

lol that attachment worked SO poorly.  Here it is:

Preview file
154 KB
0 Kudos

TomRenish
New Contributor III

‎02-24-2025 05:01 PM

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

0 Kudos