How can I pass variables to spark SQL using python?

Question

I am writing spark code in python. How do I pass a variable in a spark.sql request?

    q25 = 500
    Q1 = spark.sql("SELECT col1 from table where col2>500 limit $q25 , 1")

Currently the above code is not working? How do I pass variables?

I have also tried,

    Q1 = spark.sql("SELECT col1 from table where col2>500 limit q25='{}' , 1".format(q25))

+8

Viv June 16 17 at 6:39 am

2 answers

Tiny.D · Answer 1 · 2017-06-16T07:07:52+0000

You need to remove the single quote and q25

formatted strings like this:

Q1 = spark.sql("SELECT col1 from table where col2>500 limit {}, 1".format(q25))

Update:

Based on your new requests:

spark.sql("SELECT col1 from table where col2>500 order by col1 desc limit {}, 1".format(q25))

Please note that SparkSQL does not support OFFSET, so the query cannot work.

If you need to add multiple variables, you can try this way:

q25 = 500
var2 = 50
Q1 = spark.sql("SELECT col1 from table where col2>{0} limit {1}".format(var2,q25))

Deepesh kumar · Answer 2 · 2017-07-10T04:54:09+0000

All you have to do is add s (String interpolator) to the string. This allows the variable to be used directly in the string.

val q25 = 10
Q1 = spark.sql(s"SELECT col1 from table where col2>500 limit $q25)