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 seems to look as it is limited to the logical conjunction (no IN and OR I am afraid) and simple predicates.
While, every else like limits, counts, ordering, groups and conditions is processed on the Spark side. One warning that is 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. We can use an arbitrary subquery as a table argument. It is just less convenient as compared to a predicate pushdown but otherwise it 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)
Visit given below links for more details: