Recently I was working with Spark with JDBC data source. Consider following snippet:
val df = spark.read.(options).format("jdbc").load();
val newDF = df.where(PRED)
PRED is a list of predicates.
If PRED is a simple predicate, like x = 10, query will be much faster. However, if there are some non-equi conditions like date > someOtherDate or date < someOtherDate2, query is much slower than without predicate pushdown. As you may know, DB engines scans of such predicates are very slow, in my case with even 10 times slower (!).
To prevent unnecessary predicate pushdown I used:
val cachedDF = df.cache()
val newDF = cachedDF.where(PRED)
But it requires a lot of memory and - due to problem mentioned here - Spark' Dataset unpersist behaviour - I can't unpersist cachedDF.
Is there any other option to avoid pushing down predicates? Without caching and without writing own data source?
Note: Even if there is an option to turn off predicate pushdown, it's applicable only is other query may still use it. So, if I wrote:
// some fancy option set to not push down predicates
val df1 = ...
// predicate pushdown works again
val df2 = ...
df1.join(df2)// where df1 without predicate pushdown, but df2 with
explain()result and in SQL that was sent to database. As far as I remember database engines have often poor performance on very complex predicates with many non-equi conditions with OR between them. In my case, SQL without pushed-down WHERE clause was approx. 10 times faster - T. GawędaORpredicates are different than range searches, but the last time I've checked,ORs weren't pushed down, excluding cases of chained equality conditions on the same filed (x = 1 OR x = 2) - zero323udf[T, T](identity _). Forcing casting can work in a similar way. For example ` df.where(($"modifieddate" > "2010-05-28 00:00:00"))` wheremodifieddateis Timestamp won't be pushed whiledf.where(($"modifieddate" > lit("2010-05-28 00:00:00").cast("timestamp")))will be - zero323