topic Re: How can I access python variable in Spark SQL? in Data Engineering

How can I access python variable in Spark SQL?

haseebkhan1421 — Sun, 15 Aug 2021 18:00:17 GMT

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.

Re: How can I access python variable in Spark SQL?

morganmazouchi — Fri, 17 Sep 2021 17:19:45 GMT

@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

Re: How can I access python variable in Spark SQL?

Nirupam — Thu, 14 Apr 2022 08:06:28 GMT

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