cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

How do I pass parameters to my SQL statements?

__Databricks_Su
Contributor
 
1 ACCEPTED SOLUTION

Accepted Solutions

__Databricks_Su
Contributor

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)

View solution in original post

15 REPLIES 15

__Databricks_Su
Contributor

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)

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 ;

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

Worked in my scenario. thank you ๐Ÿ™‚

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

AmitIndap
New Contributor II

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)

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
New Contributor II

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

easimadi
New Contributor II

@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
New Contributor II

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
New Contributor II

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
New Contributor II

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

SherifAbouKlila
New Contributor II

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
New Contributor II

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

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group