cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Mado
Valued Contributor II

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.

1 ACCEPTED SOLUTION

Accepted Solutions

Pat
Honored Contributor III

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

4 REPLIES 4

Pat
Honored Contributor III

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_Fatma
Community Manager
Community Manager

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
New Contributor II

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
New Contributor II

lol that attachment worked SO poorly.  Here it is:

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!