Databricks Spark: How to pass value from Python/Scala to Spark SQL

This article will explain how to use Python or Scala variables in Spark SQL without wrapping the SQL statement with spark.sql.

Step 1: Create a new table

%sql

drop table if exists tbl_friends;

create table tbl_friends(name string, age int);

Step 2: Scala variable assignment.

One important thing to remember is to use a two-part namespace inside spark.conf.

Using a variable without namespace will result in NULL.

In this example, we have used myapplication.name, feel free to use change based on your requirement.

%scala

val age = 30
val name = "Rachel Green"

spark.conf.set("myapplication.name", name)
spark.conf.set("myapplication.age", age)

Step 3: Access the spark.conf.set variable from Spark SQL

Numeric values can be used with or without quotes.

'${myapplication.age}' or ${myapplication.age}

Inserting data into table tbl_friends

%sql

INSERT INTO tbl_friends values ('${myapplication.name}',${myapplication.age})

Step 4: Python variable assignment

%python

age = 31
name = "Ross Geller"

spark.conf.set("myapplication.name", name)
spark.conf.set("myapplication.age", age)

Step 5: Insert new values into Table

%sql

INSERT INTO tbl_friends values ('${myapplication.name}',${myapplication.age})

Step 6: Query the data

%sql

select * from tbl_friends

%sql

select * from tbl_friends where age = ${myapplication.age}