How to create a variable from a SQL statement's output in Databricks

Question

I would like to create a variable that I can use later in multiple sql queries within the same notebook. The variable itself is simply an integer output from a sql statement such as:

    select count(people) from persons_table;

Then in subsequent sql command cells, I would simply call the variable in calculations such as:

    select (count(cars) / $variable) as car_rate from table_x;

The only variables I've used in Databricks have been simple widgets from a Python script (dbutils.widgets.text('name','value')).

I already have an alternative, where I have a temp table with the output, but would rather use a variable if this is possible.

why not use the common table expressions? docs.databricks.com/sql/language-manual/… — Alex Ott
– Alex Ott, Commented Sep 27, 2021 at 16:25
I can, but was wondering if a variable is an option. I have about 400 union'd queries, so I could wrap it and then use a CTE I guess. — Isolated
– Isolated, Commented Sep 27, 2021 at 18:19
it's possible to pass from Python (stackoverflow.com/questions/68794549/…), another possibility is to create a widget and refer to it, but I'm not sure about efficiency — Alex Ott
– Alex Ott, Commented Sep 27, 2021 at 19:53

JonB65 · Accepted Answer · 2022-03-14 10:29:20Z

1

I found this while looking for the same problem. As your qustion isn't answered I'll put the one I found for others. I'm not sure it is the best, but it works.

Use a python notebook.

Then you can use python variables.

peopleCount = spark.sql("select count(people) from persons_table").collect()[0][0]

The spark.sql reads the sql into a pyspark dataframe, if you just sent the SQL the variable would be a dataframe object. The collect reads the result from the dataframe into the variable. The [0][0] is the "offset" but you only need to worry about that if you have more than one column or row.

Then in the next cell

%sql 

select (count(cars) / '$peopleCount') as car_rate from table_x;

The %sql tells databricks this is a SQL cell and the variable needs to be in quotes with a $

answered Mar 14, 2022 at 10:29

JonB65

848 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create a variable from a SQL statement's output in Databricks

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related