I want to use a WHERE statement with two variables within the where clause. I've done research on this looking at how to use variables in SQL statements in Databricks and Inserting Variables Using Python, Not Working. I've tried to implement the solutions provided but it's not working.
a= 17091990
b = 30091990
df = spark.sql[' SELECT * FROM table WHERE date between "a" AND "b" ']
- databricks
asked Aug 20, 2019 at 1:09
JozamvgJozamvg
1472 silver badges10 bronze badges
6
Whats the error that you are getting? Maybe because of the date format? Date format should be separated by dash[-], somthing like this I Think A = '09-17-1990' and B = '09-30-1990'
Aug 20, 2019 at 1:17
Can you clarify how it is "not working". Can you provide some sample data and also show what the expected vs actual output is?
Aug 20, 2019 at 1:17
@RonelCalinisan No, the value its an int
Aug 20, 2019 at 1:39
@Dijkgraaf I dont know how to do it. Ive been trying a different ways to use the variables in the query but doesnt work
Aug 20, 2019 at 1:40
@Jozamvg so your date there is int and not Date?
Aug 20, 2019 at 1:42
1 Answer
answered Aug 20, 2019 at 7:55
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
%sqldrop 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.
%scalaval 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
%sqlINSERT INTO tbl_friends values ['${myapplication.name}',${myapplication.age}]
Step 4: Python variable assignment
%pythonage = 31
name = "Ross Geller"spark.conf.set["myapplication.name", name]
spark.conf.set["myapplication.age", age]
Step 5: Insert new values into Table
%sqlINSERT INTO tbl_friends values ['${myapplication.name}',${myapplication.age}]
Step 6: Query the data
%sqlselect * from tbl_friends
%sqlselect * from tbl_friends where age = ${myapplication.age}