CODEWITHSUNDEEP
sqlselect
G G
G G

Reputation: 1069

Spark SQL passing a variable

I have following Spark sql and I want to pass variable to it. How to do that? I tried following way.

 sqlContext.sql("SELECT count from mytable WHERE id=$id")

Upvotes: 18

Views: 78002

Answers (7)

Tanveer
Tanveer

Reputation: 900

if the id is in string then the easiest way is the strong formatting. For string, pyspark expects it in the string so provide a single quote as well.

 sqlContext.sql(f"SELECT count from mytable WHERE id='{id}'")

Upvotes: 0

GadaaDhaariGeek
GadaaDhaariGeek

Reputation: 1030

Since the accepted answer didn't work for me, I am writing my own answer using string interpolation.

sqlContext.sql(s"""SELECT count from mytable WHERE id='${id}'""")

Upvotes: 5

Luiz Viola
Luiz Viola

Reputation: 2436

I like the f-Strings approach (PySpark):

table = 'my_schema.my_table'

df = spark.sql(f'select * from {table}')

Upvotes: 1

Ivan Suarez
Ivan Suarez

Reputation: 1

You could use a concatenation, with this the engine understands the query, I leave an example:

First:

In a variable inserts the value to pass in the query (in this case is a date)

date= spark.range(1).withColumn('date',regexp_replace(date_add(current_date(),-4),"-","")).toPandas().to_string().split()[4]

Result = '20220206'

Second:

query = '''
SELECT 
    *
FROM 
    table
WHERE
    country = '''+' '+date+''' 
'''
df= spark.sql(query)

Upvotes: 0

Dileep Dominic
Dileep Dominic

Reputation: 519

You can try like this in spark 2.0 onwards with sparksession

spark.sql(s"""SELECT count(*) from mytable WHERE id='$id' """)

Upvotes: 0

WoodChopper
WoodChopper

Reputation: 4375

You are almost there just missed s :)

sqlContext.sql(s"SELECT count from mytable WHERE id=$id")

Upvotes: 12

David
David

Reputation: 11593

You can pass a string into sql statement like below

id = "1"
query = "SELECT count from mytable WHERE id='{}'".format(id)
sqlContext.sql(query)

Upvotes: 15

Related Questions