Filtering a Pyspark DataFrame with SQL-like IN clause

by

String you pass to SQLContext it evaluated in the scope of the SQL environment. It doesn’t capture the closure. If you want to pass a variable you’ll have to do it explicitly using string formatting:

df = sc.parallelize([(1, "foo"), (2, "x"), (3, "bar")]).toDF(("k", "v"))
df.registerTempTable("df")
sqlContext.sql("SELECT * FROM df WHERE v IN {0}".format(("foo", "bar"))).count()
##  2

Obviously this is not something you would use in a “real” SQL environment due to security considerations but it shouldn’t matter here.

In practice DataFrame DSL is a much better choice when you want to create dynamic queries:

from pyspark.sql.functions import col

df.where(col("v").isin({"foo", "bar"})).count()
## 2

It is easy to build and compose and handles all details of HiveQL / Spark SQL for you.

More Related Contents:

  1. How to add a constant column in a Spark DataFrame?
  2. Spark Dataframe distinguish columns with duplicated name
  3. Pyspark: Split multiple array columns into rows
  4. How do I add a new column to a Spark DataFrame (using PySpark)?
  5. How to change a dataframe column from String type to Double type in PySpark?
  6. Count number of non-NaN entries in each column of Spark dataframe with Pyspark
  7. Retrieve top n in each group of a DataFrame in pyspark
  8. Rename nested field in spark dataframe
  9. Updating a dataframe column in spark
  10. Dividing complex rows of dataframe to simple rows in Pyspark
  11. Filter Pyspark dataframe column with None value
  12. PySpark converting a column of type ‘map’ to multiple columns in a dataframe
  13. How to explode multiple columns of a dataframe in pyspark
  14. Spark add new column to dataframe with value from previous row
  15. PySpark: multiple conditions in when clause
  16. Pyspark: Replacing value in a column by searching a dictionary
  17. Count number of non-NaN entries in each column of Spark dataframe in PySpark
  18. Create Spark DataFrame. Can not infer schema for type
  19. Pivot String column on Pyspark Dataframe
  20. Filtering DataFrame using the length of a column
  21. Multiple condition filter on dataframe
  22. How to return a “Tuple type” in a UDF in PySpark?
  23. Spark DAG differs with ‘withColumn’ vs ‘select’
  24. How to split Vector into columns – using PySpark
  25. creating spark data structure from multiline record
  26. Pyspark: explode json in column to multiple columns
  27. How to use a Scala class inside Pyspark
  28. Spark: Broadcast variables: It appears that you are attempting to reference SparkContext from a broadcast variable, action, or transforamtion
  29. Load CSV file with PySpark
  30. How to process RDDs using a Python class?

Leave a Comment