topic Re: How do I pass parameters to my SQL statements? in Data Engineering

How do I pass parameters to my SQL statements?

__Databricks_Su — Wed, 18 Feb 2015 21:26:45 GMT

Re: How do I pass parameters to my SQL statements?

__Databricks_Su — Wed, 18 Feb 2015 21:26:45 GMT

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)

Re: How do I pass parameters to my SQL statements?

RahuLrEDDy1 — Thu, 26 May 2016 19:23:43 GMT

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 ;

Re: How do I pass parameters to my SQL statements?

pvsatishkumarre — Fri, 08 Jul 2016 06:57:53 GMT

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

Re: How do I pass parameters to my SQL statements?

AmitIndap — Wed, 03 Aug 2016 18:44:18 GMT

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)

Re: How do I pass parameters to my SQL statements?

chandras — Sat, 22 Oct 2016 22:33:44 GMT

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

Re: How do I pass parameters to my SQL statements?

easimadi — Mon, 07 Nov 2016 00:24:14 GMT

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

Re: How do I pass parameters to my SQL statements?

jccorrea — Tue, 10 Jan 2017 12:21:18 GMT

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| +---------+-----------+

>>>

Re: How do I pass parameters to my SQL statements?

jccorrea — Tue, 10 Jan 2017 12:32:51 GMT

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| +---+-----+

>>>

Re: How do I pass parameters to my SQL statements?

poonamkpatil — Mon, 03 Sep 2018 12:45:05 GMT

Worked in my scenario. thank you 🙂

Re: How do I pass parameters to my SQL statements?

MikeBrewer — Tue, 26 Mar 2019 11:30:32 GMT

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

Re: How do I pass parameters to my SQL statements?

SherifAbouKlila — Tue, 17 Sep 2019 13:44:39 GMT

you can use widget

1- Create widget

Python

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

SQL

CREATE WIDGET TEXT y DEFAULT '3'

2- Access the current value of the widget

Python

dbutils.widgets.get("y")

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

Re: How do I pass parameters to my SQL statements?

MarinFerara — Mon, 28 Oct 2019 18:47:55 GMT

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

Re: How do I pass parameters to my SQL statements?

MarinFerara — Tue, 05 Nov 2019 06:56:51 GMT

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

Re: How do I pass parameters to my SQL statements?

vr — Wed, 08 Jun 2022 23:39:39 GMT

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

Re: How do I pass parameters to my SQL statements?

StephanieAlba — Tue, 09 May 2023 14:33:37 GMT

This answer from Stackoverflow is spot on. https://stackoverflow.com/a/72500870/1290485