topic Re: Databricks SQL - Conditional Catalog query in Get Started Discussions

Databricks SQL - Conditional Catalog query

102842 — Mon, 24 Jul 2023 20:26:07 GMT

Hi is there a way we can do

%sql
select * from {{ catalog }}.schema.table

Where `{{ catalog }}` is a template variable extracted/evaluated from either an environment variable, a databricks secret, or somewhere else? (note: not a widget)

Re: Databricks SQL - Conditional Catalog query

Tharun-Kumar — Tue, 25 Jul 2023 19:42:54 GMT

@102842

You can first create a variable to extract the value you want. Then you can use python's string formatting to substitute the variable inside the query.

Let's say catalog is the variable name. Then you can perform

command = f"select * from {catalog}.schema.table" spark.sql(command)

Re: Databricks SQL - Conditional Catalog query

102842 — Tue, 25 Jul 2023 19:44:23 GMT

Sorry should have specify, i don't want to use the `spark.sql` api. I would want to use it on:

%sql
select * from {{ catalog }}.schema.table

Re: Databricks SQL - Conditional Catalog query

Tharun-Kumar — Wed, 26 Jul 2023 05:05:01 GMT

Hi @102842

You can use query parameters to perform this - https://docs.databricks.com/sql/user/queries/query-parameters.html

You can define the catalog name as a query parameter. You should declare the catalog name parameter as a drop down list, because if we declare it as a string, it will add single quotes surrounding the value, during the substitution.