How can I access python variable in Spark SQL?

[haseebkhan1421]

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),'01_',SUBSTRING(RunID,1,6),'01_') FROM RunID_Pace").first()[0] + RunID_Pace[18:] 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.

[morganmazouchi · Accepted solution]

@haseebkhan1421​ , You can create a temporary view table in your python cell using "DataFrame.createOrReplaceTempView("table_name")" and query that table in SQL. check here for seeing an example

[Nirupam]

You can use {} in spark.sql() of pyspark/scala instead of making a sql cell using %sql.

This will result in a dataframe. If you want you can create a view on top of this using createOrReplaceTempView()

Below is an example to use a variable:-

# A variable

var = "Hello World"

# Using f in pyspark spark.sql.

spark.sql(f""" SELECT '{var}' AS Message """)

# Using format

spark.sql(""" SELECT '{}' AS Message """.format(var))