Databricks SQL - Conditional Catalog query

[102842]

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)

[Tharun-Kumar]

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

[102842]

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

[Tharun-Kumar]

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.