How to use columns to create queries (e.g. WHERE clause)?

user_1875107 0
Viewed 85

I have a table with a column with one row with where clause.

from pyspark.sql.types import *
where_clause_df=spark.createDataFrame([('A > 1',)],schema=StructType([StructField("a_where", StringType(), nullable=True)]))
where_clause_df.createOrReplaceTempView("where_clause").show()

spark.sql("select * from where_clause").show()
+-------+
|a_where|
+-------+
|  A > 1|
+-------+

With another table,

sample_df=spark.createDataFrame([(1,)],schema=StructType([StructField("A", IntegerType(), nullable=True)]))
sample_df.createOrReplaceTempView("sample")
spark.sql("select * from sample").show()

I want to use this a_where to apply with the table sample. Something like:

spark.sql("""
select * from sample where (select a_where from where_clause)
""").show()

Is it possible with Spark SQL ?

pyspark apache-spark-sql apache-spark
2 Answers

tl;dr Use collect on the where_clause table.

Think of data as something available (almost) always on executors where you are not allowed to execute queries from. That's by design.

Since you want to execute queries you should have all you need on the driver and so you need to bring this extra metadata for your queries (like where clauses) to the driver. Bingo! That's exactly collect.

Mind though that the data you can "download" to the driver using collect has to be within the amount of memory available for this one single driver process (and that's likely the case).

user_13053440

You are trying to extract the where clause string from your TempView, hence getting the error

You can modify your code slightly to achieve this

where_clause_df=sql.createDataFrame([('A > 1',)],schema=StructType([StructField("a_where", StringType(), nullable=True)]))

where_clause_df.createOrReplaceTempView("where_clause")

sql.sql("select * from where_clause").show()


sample_df=sql.createDataFrame([(1,)],schema=StructType([StructField("A", IntegerType(), nullable=True)]))
sample_df.createOrReplaceTempView("sample")

sql.sql("select * from sample").show()

### Contains 'A > 1'
where_clause = sql.sql("select a_where from where_clause").collect()[0][0]

query = f"""
    select * 
    from sample 
    where {where_clause}
"""

sql.sql(query).show()

+---+
|  A|
+---+
+---+

Further if there are multiple conditions , you can iterate over them and modify the query in each iteration to extract the results

user_91089120
Related