Does spark predicate pushdown work with JDBC?

by

Spark DataFrames support predicate push-down with JDBC sources but term predicate is used in a strict SQL meaning. It means it covers only WHERE clause. Moreover it looks like it is limited to the logical conjunction (no IN and OR I am afraid) and simple predicates.

Everything else, like limits, counts, ordering, groups and conditions is processed on the Spark side. One caveat, already covered on SO, is that df.count() or sqlContext.sql("SELECT COUNT(*) FROM df") is translated to SELECT 1 FROM df and requires both substantial data transfer and processing using Spark.

Does it mean it is a lost cause? Not exactly. It is possible to use an arbitrary subquery as a table argument. It is less convenient than a predicate pushdown but otherwise works pretty well:

n = ... # Number of rows to take
sql = "(SELECT * FROM dummy LIMIT {0}) AS tmp".format(int(n))
df = sqlContext.read.jdbc(url=url, table=sql)

Note:

This behavior may be improved in the future, once Data Source API v2 is ready:

More Related Contents:

  1. Convert pyspark string to date format
  2. How to split Vector into columns – using PySpark
  3. How to use JDBC source to write and read data in (Py)Spark?
  4. Load CSV file with Spark
  5. Applying UDFs on GroupedData in PySpark (with functioning python example)
  6. Pyspark: Split multiple array columns into rows
  7. How to change a dataframe column from String type to Double type in PySpark?
  8. Count number of non-NaN entries in each column of Spark dataframe with Pyspark
  9. Spark DataFrame: Computing row-wise mean (or any aggregate operation)
  10. Passing a data frame column and external list to udf under withColumn
  11. Updating a dataframe column in spark
  12. How to use a Scala class inside Pyspark
  13. Filter Pyspark dataframe column with None value
  14. PySpark converting a column of type ‘map’ to multiple columns in a dataframe
  15. Best way to get the max value in a Spark dataframe column
  16. Apache Spark Python Cosine Similarity over DataFrames
  17. Count number of non-NaN entries in each column of Spark dataframe in PySpark
  18. Load CSV file with PySpark
  19. GroupBy column and filter rows with maximum value in Pyspark
  20. Reshaping/Pivoting data in Spark RDD and/or Spark DataFrames
  21. Cannot find col function in pyspark
  22. What is the best way to remove accents with Apache Spark dataframes in PySpark?
  23. How to pass a constant value to Python UDF?
  24. How to zip two array columns in Spark SQL
  25. When to cache a DataFrame?
  26. Multiple condition filter on dataframe
  27. How to return a “Tuple type” in a UDF in PySpark?
  28. How to connect HBase and Spark using Python?
  29. How to pivot on multiple columns in Spark SQL?
  30. Apache Spark — Assign the result of UDF to multiple dataframe columns

Leave a Comment