Passing variable to Postgresql from Spark with scala

Question

Usually I can access Hive table from Spark by using the following:

val start = "20191009"
val end   = "20200112"
val df= ss.sql(s"""select * from myTable where dat between '$start' and '$end' """)

By previous code I can pass variable to the SQL by including $ in front of the variable.

Now I want to do same previous logic but with the postgres table. I have Postgres table that I connected to it through:

val statement = connection.createStatement()

var gg = statement.executeQuery("update myTable set firstV='NewValue' where SecondV =$val;")

I want to pass a variable to the previous sql ( to val variable).

score 2 · Accepted Answer · answered Jan 17 '20 at 21:42

2

Your second code snippet is not using the s modifier, hence $ substitution will not work. Try

var gg = statement.executeQuery(s"update myTable set firstV='NewValue' where SecondV =$val;")```

answered Jan 17 '20 at 21:42

Charlie Flowers

1,287
7
12

Rahman · Answer 2 · 2020-01-20T04:00:20.517

As @Charlie Flowers hit part of the issue; I missed the s modifier in front of the query. The other issue was trying to save the result of the Postgres table in a variable (in my case gg), the query will be directly executed on Postgres database/table, so I just removed the var gg = part.

statement.executeQuery(s"update myTable set firstV='NewValue' where SecondV =$val;")

Also, this helped me alot, since I used executeQuery while I have to use executeUpdate, so my query should be:

statement.executeUpdate(s"update myTable set firstV='NewValue' where SecondV =$val;")

Passing variable to Postgresql from Spark with scala

2 Answers2