topic How to set a variable and use it in a SQL query in Data Engineering

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

Mado — Tue, 15 Nov 2022 11:07:22 GMT

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.

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

Pat — Tue, 15 Nov 2022 19:37:54 GMT

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

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

CJS — Thu, 28 Sep 2023 20:43:43 GMT

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

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

CJS — Thu, 28 Sep 2023 20:52:43 GMT

lol that attachment worked SO poorly. Here it is:

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

olufemi_anthony — Wed, 15 Jan 2025 19:04:26 GMT

Updated usage as of DBR 15.2+

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

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

TomRenish — Tue, 25 Feb 2025 01:01:53 GMT

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