Use python variable in sql query databricks

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

Use python variable in sql query 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

Use python variable in sql query databricks

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

Use python variable in sql query databricks

Select All Rows
%sqlselect * from tbl_friends where age = ${myapplication.age}

Use python variable in sql query databricks

Select based on condition

How do you use a Python variable in SQL query in Databricks?

Databricks Spark: How to pass value from Python/Scala to Spark....
Step 1: Create a new table. ... .
Step 4: Python variable assignment %pythonage = 31. ... .
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..

Can we use Python variable in SQL query?

We often need to pass variables to SQL select query in where clause to check some conditions. In the user signup form user enter his/her details. You can take those values in Python variables and insert them into a table.

How do you pass a parameter in SQL query using Python?

You can pass parameters/arguments to your SQL statements by programmatically creating the SQL string using Scala/Python and pass it to sqlContext. sql(string). Note the 's' in front of the first """..
Your parameters. val p1 = "('0001','0002','0003')" ... .
Build the query. ... .
Then you can query it..

Can we use variables in Spark SQL?

The short answer is no, Spark SQL does not support variables currently. The SQL Server uses T-SQL, which is based on SQL standard extended with procedure programming, local variables and other features. Spark SQL is a pure SQL, partially compatible with SQL standard.