Spark SQL passing a variable

Question

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")

Answer 1

You are almost there just missed s :)

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

Answer 2

You can pass a string into sql statement like below

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

Answer 3

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}'""")

Answer 4

You can try like this in spark 2.0 onwards with sparksession

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

Answer 5

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)

Answer 6

I like the f-Strings approach (PySpark):

table = 'my_schema.my_table'

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

Answer 7

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}'")