How can I access python variable in Spark SQL?

Question

I have python variable created under %python in my jupyter notebook file in Azure Databricks. How can I access the same variable to make comparisons under %sql. Below is the example:

%python

RunID_Goal = sqlContext.sql("SELECT CONCAT(SUBSTRING(RunID,1,6),SUBSTRING(RunID,1,6),'01_') 
FROM RunID_Pace").first()[0] 
AS RunID_Goal

%sql
SELECT Type , KPIDate, Value
FROM table
WHERE
RunID = RunID_Goal (This is the variable created under %python and want to compare over here)

When I run this it throws an error: Error in SQL statement: AnalysisException: cannot resolve 'RunID_Goal' given input columns: I am new azure databricks and spark sql any sort of help would be appreciated.

Answer 1

One workaround could be to use Widgets to pass parameters between cells. For example, on Python side it could be as following:

# generate test data
import pyspark.sql.functions as F
spark.range(100).withColumn("rnd", F.rand()).write.mode("append").saveAsTable("abc")

# set widgets
import random
vl = random.randint(0, 100)
dbutils.widgets.text("my_val", str(vl))

and then you can refer the value from the widget inside the SQL code:

%sql
select * from abc where id = getArgument('my_val')

will give you:

Another way is to pass variable via Spark configuration. You can set variable value like this (please note that that the variable should have a prefix - in this case it's c.):

spark.conf.set("c.var", "some-value")

and then from SQL refer to variable as ${var-name}:

%sql 
select * from table where column = '${c.var}'

One advantage of this is that you can use this variable also for table names, etc. Disadvantage is that you need to do the escaping of the variable, like putting into single quotes for string values.

Answer 2

You cannot access this variable. It is explained in the documentation:

When you invoke a language magic command, the command is dispatched to the REPL in the execution context for the notebook. Variables defined in one language (and hence in the REPL for that language) are not available in the REPL of another language. REPLs can share state only through external resources such as files in DBFS or objects in object storage.

Answer 3

Here is another workaround.

# Optional code to use databricks widgets to assign python variables
dbutils.widgets.text('my_str_col_name','my_str_col_name')
dbutils.widgets.text('my_str_col_value','my_str_col_value')
my_str_col_name = dbutils.widgets.get('my_str_col_name')
my_str_col_value = dbutils.widgets.get('my_str_col_value')

# Query with string formatting
query = """
select *
from my_table
where {0} < '{1}'
"""

# Modify query with the values of Python variable 
query = query.format(my_str_col_name,my_str_col_value)

# Execute the query
display(spark.sql(query))

Answer 4

A quick complement to answer.

Do you can use widgets to pass parameters to another cell using magic %sql, as was mentioned;

dbutils.widgets.text("table_name", "db.mytable")

And at the cell that you will use this variable do you can use $ shortcut ~ getArgument isn't supported;

%sql
select * from $table_name