Multiple parameter in IN clause of Spark SQL from parameter file

Question

I am trying to run spark query where I am creating curated table from a source table based upon values in parameter file.

properties_file.properties contains below key values:

substatus,allow,deny

SparkQuery is

//Code to load property file in parseConf

spark.sql(s"""insert into curated.table from source.table where 
substatus='${parseConf.substatus}'""")

Above works with single value in substatus. But Can someone help what shall i do if I need to use substatus in ${parseConf.substatus} for multiple values from param as below.

spark.sql(s"""insert into curated.table from source.table where substatus in '${parseConf.substatus}'""")

Vikhyat Srivastava · Accepted Answer · 2021-01-15 06:25:11Z

1

To resolve my problem, I updated my property file as:
substatus,'allow'-'deny'
Then in scala code, I implemented below logic:
val subStatus=(parseConf.substatus).replace('-',',')
spark.sql(s"""insert into curated.table from source.table where substatus in ('${subStatus}')""")
Above strategy helped in breaking the values in string to muliple parameters of IN clause.

answered Jan 15, 2021 at 6:25

Vikhyat Srivastava

133 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dharman · Accepted Answer · 2021-01-15 06:35:15Z

0

Equalto operator expects 1 value to be passed other than directly reading the value from parameter file who make in pass a one string. You need to break the values and then use IN clause inplace of equalto(=).

edited Jan 15, 2021 at 6:35

Dharman♦

33.9k27 gold badges106 silver badges157 bronze badges

answered Jan 15, 2021 at 6:29

Divyaansh Bajpai

2421 silver badge11 bronze badges

1 Comment

Vikhyat Srivastava Over a year ago

I followed same to resolve the issue. Posted my answer few mins ago. :)

Collectives™ on Stack Overflow

Multiple parameter in IN clause of Spark SQL from parameter file

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related