โ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.
โ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}"
โ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}"
โ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};
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
โ09-28-2023 01:52 PM
Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโt want to miss the chance to attend and share knowledge.
If there isnโt a group near you, start one and help create a community that brings people together.
Request a New Group