Assign the scalar result of a SELECT statement to a variable or widget in Spark SQL (Databricks)

user_13821606 0
Viewed 650

I'm looking for the Spark SQL equivalent of the T-SQL select @variable = AVG(something) FROM ... construct.

I would like to assign the scalar returned from select avg(year) from values ("John", 33), ("Karen", 20), ("Gladys", 25) as data(name, year) to a variable or widget, and use it the where condition of later queries.

The reference on widgets shows how to do this for dropdown lists, but not for simple text widgets. All the text widget examples assign constants, not dynamic values.

This is in a pure SQL Databricks notebook so I'd prefer a vanilla SQL solution if one exists.

databricks apache-spark sql
1 Answers

You can use set to set variables, and recall them using ${var}:

spark.sql('set avg_var = (select avg(year) from values ("John", 33), ("Karen", 20), ("Gladys", 25) as data(name, year))')

spark.sql("select ${avg_var}").show()
+----------------+
|scalarsubquery()|
+----------------+
|            26.0|
+----------------+

In pure SQL it will look like

set avg_var = (select avg(year) from values ("John", 33), ("Karen", 20), ("Gladys", 25) as data(name, year));

select ${avg_var};
user_141657300
Related