Spark SQL passing variables - Synapse (Spark pool)

Question

I have the following SparkSQL (Spark pool - Spark 3.0) code and I want to pass a variable to it. How can I do that? I tried the following:

#cel 1 (Toggle parameter cell):
%%pyspark
stat = 'A'

#cel2:
select * from silver.employee_dim where Status= '$stat'

Answer 1

Hello @,
Thanks for the ask and using the Microsoft Q&A platform .

I tried the below snippet and it worked , Please do let me know how it goes .
cell1

%%pyspark  
tablename = "yourtablename"  

cell2

%%pyspark  
query = "SELECT * FROM {}".format(tablename)  
print (query)  
from pyspark.sql import SparkSession  
spark = SparkSession.builder.appName("sample").getOrCreate()  
df2 = spark.sql(query)   
df2.show()  

Thanks
Himanshu
Please do consider clicking on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

Answer 2

Hi Himanshu,

Thank you for your reply. But we would like to keep the second cell as SQL.
We are evaluating to migrate some Databricks notebooks to Spark pool. For example:

Thanks

Answer 3

I found this solution. However, I don't know if it is the best solution: