Use of Python variable in SQL cell

[jar]

If using spark.conf.set(<variable_name>, <variable_value>), or just referring a widget value directly, in a Python cell and then referring to it in a SQL cell with ${variable_name} one gets the warning: "SQL query contains a dollar sign parameter, $param, which is deprecated. Please migrate to the parameter marker syntax, :param."

If one does so in a cell which creates a temporary view one gets the error "[UNSUPPORTED_FEATURE.PARAMETER_MARKER_IN_UNEXPECTED_STATEMENT] The feature is not supported: Parameter markers are not allowed in the query of CREATE VIEW. SQLSTATE: 0A000".

I'm using a > 15.2 runtime cluster.

So, what does one do?

[lingareddy_Alva · Accepted solution]

hi @jar 

This is a frustrating situation where Databricks is deprecating the ${} syntax but the replacement :param syntax doesn't work in all contexts, particularly with CREATE VIEW statements.

1. String Formatting/Templating
Use Python string formatting to build your SQL dynamically:

# Python cell
table_name = "my_table"
filter_value = "some_value"

sql_query = f"""
CREATE OR REPLACE TEMPORARY VIEW my_view AS
SELECT * FROM {table_name}
WHERE column = '{filter_value}'
"""

spark.sql(sql_query)

2. Use Databricks Widgets with String Formatting
If using widgets:

# Python cell
dbutils.widgets.text("param_name", "default_value")
param_value = dbutils.widgets.get("param_name")

create_view_sql = f"""
CREATE OR REPLACE TEMPORARY VIEW my_view AS
SELECT * FROM table
WHERE column = '{param_value}'
"""

spark.sql(create_view_sql)

[jar]

Frustrating indeed. Thank you, @lingareddy_Alva