JDBC to Spark Dataframe - How to ensure even partitioning?

Question

I am new to Spark, and am working on creating a DataFrame from a Postgres database table via JDBC, using spark.read.jdbc.

I am a bit confused about the partitioning options, in particular partitionColumn, lowerBound, upperBound, and numPartitions.

---

• The documentation seems to indicate that these fields are optional. What happens if I don't provide them?
• How does Spark know how to partition the queries? How efficient will that be?
• If I DO specify these options, how do I ensure that the partition sizes are roughly even if the partitionColumn is not evenly distributed?

Let's say I'm going to have 20 executors, so I set my numPartitions to 20.
My partitionColumn is an auto-incremented ID field, and let's say the values range from 1 to 2,000,000
However, because the user selects to process some really old data, along with some really new data, with nothing in the middle, most of the data has ID values either under 100,000 or over 1,900,000.

• Will my 1st and 20th executors get most of the work, while the other 18 executors sit there mostly idle?

• If so, is there a way to prevent this?

Answer 1

I found a way to manually specify the partition boundaries, by using the jdbc constructor with the predicates parameter.

It allows you to explicitly specify individual conditions to be inserted in the "where" clause for each partition, which allows you to specify exactly which range of rows each partition will receive. So, if you don't have a uniformly distributed column to auto-partition on, you can customize your own partition strategy.

An example of how to use it can be found in the accepted answer to this question.