How do I pass parameters to my SQL statements?

[__Databricks_Su · Accepted solution]

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

Here's an example using String formatting in Scala:

val param = 100

sqlContext.sql(s"""SELECT * FROM table1 where param=$param""")

Note the 's' in front of the first """. This lets you substitute $param's in a Scala string.

Here's an example using String formatting in Python:

param = 100

query = "SELECT * FROM table1 where param={}".format(param)sqlContext.sql(query)

[RahuLrEDDy1]

I tried the same with insert query like below

val a = 1207 val b = "amit" val c = 20 sqlContext.sql(s"""insert into table employee select t.* from (select $a, $b, $c) t""")

But, I got the following error

Exception in thread "main" org.apache.spark.sql.AnalysisException: cannot resolve 'amit' given input columns ;

[pvsatishkumarre]

How can we pass date parameter in python to spark-sql

[poonamkpatil]

Worked in my scenario. thank you 🙂

[vr]

Isn't it a bad practice to embed SQL parameters by string formatting?

[AmitIndap]

query = 'SELECT * FROM dbsnp where rs_id="{}"'.format(rs_id)

Add single quotes around the query

,

Surround the query in single quote

query = 'SELECT * FROM dbsnp where rs_id="{}"'.format(rs_id)

[MarinFerara]

If you are on Python 3.6 or higher, you can even use f-strings:

query= f'SELECT * FROM dbsnp where rs_id={rs_id} where {key}="abc"'

[chandras]

how do you use the pass multiple paramters in Spark SQL?can yu please help me

[easimadi]

@chandras​ 

The sting Interpolation for scala is below.

You can use it to build your query.

Similar exists for python.

1. Your parameters

val p1 = "('0001','0002','0003')"
val p2 = "chandras"

2. Build the query

val query  = s"select * from df where id in $p1 and name = $p2"

3. Then you can query it.

val df1 = sqlContext.sql(query)

[jccorrea]

1 - option sqlContext.sql("use %s" % db)

2 - option

>>> db = "juliodb" >>> print(db) juliodb >>> query = 'use {}'.format(db) >>> sqlContext.sql(query) DataFrame[] >>> b = sqlContext.sql("show tables") >>> b.show() +---------+-----------+ |tableName|isTemporary| +---------+-----------+ | firsttb| false| +---------+-----------+

>>>

[jccorrea]

3 - option

>>> id = 2 >>> >>> sqlContext.sql('select from firsttb where a = {}'.format(id)) DataFrame[a: int, b: string] >>> result = sqlContext.sql('select from firsttb where a = {}'.format(id)) >>> result.show() +---+-----+ | a| b| +---+-----+ | 2|cesar| +---+-----+

>>>

[MikeBrewer]

all these solutions pass sql to python or scala... not passing Parma to sql.

[SherifAbouKlila]

you can use widget

1- Create widget

Python

dbutils.widgets.text("y",'3')

or

SQL

CREATE WIDGET TEXT y DEFAULT '3'

2- Access the current value of the widget

Python

dbutils.widgets.get("y")

or

SQL

SELECT * FROM test_table WHERE key=getArgument("y") --key=$y -- The old way of creating widgets in SQL queries with the $<parameter> syntax still works as before

For more information about widgets. please check this link

https://docs.databricks.com/user-guide/notebooks/widgets.html

[MarinFerara]

You might want to take a look at Widgets https://docs.databricks.com/notebooks/widgets.html