How to use constant values in dynamic spark sql query

Question

I have a dictionary as below

city = {"AP":"VIZAG", "TELANGANA":"HYDERABAD"}

and also I have a list which I need to loop for all state tables as below

states = ['AP','HYDERABAD']

for st in states:
 df = spark.sql(f"""select * from {st} where city = {city}["{st}"]""")

In above df I am trying to filter city based on dictionary value as per state. But I am not able to do it

mpSchrader · Accepted Answer · 2020-10-21 14:09:24Z

0

By combining two filter conditions you can do the expected filtering.

selected_city = 'AP'
df = df.filter(
    (F.col('city') == selected_city)
    & (F.col('state') == cities[selected_city])
)

It is a simple change: You can use isin to filter a column based on a list [Docs].

cities = list(city.keys())

df = df.filter(F.col('city').isin(cities))

If you want to construct more complex conditions based on a dictionary see this question.

[Edit] Updated answer based on OPs comment. Will leave the old one in there for completeness.

answered Oct 21, 2020 at 12:18

mpSchrader

9525 silver badges20 bronze badges

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

3 Comments

Thanks for your response. But I need to filter only if state='AP' then city should filter with'VIZAG' which is the dictionary value for AP

Cool. Just edited the answer, hope I understood what you mean.

Thanks for your help