Partitioning Postgres Read in Spark

Question

I am trying to query huge data from postgres using SparkSQl. I do see 100 partionion on the query stage , however , there is only one query running and only one executer executing.

Code :

df = sqlcontext.read.format('jdbc').options(url=params['url']
                                            ,driver=params["driver"]
                                            ,dbtable=tableName
                                            ,user=params['user']
                                            ,password=params['password']
                                            ,numPartitions=numberOfPartitions
                                            ,partitionColumn=partitionC
                                            ,lowerBound=lowerB
                                            ,upperBound=upperB).load()

The partitionC is of type date, I even tried a similar thing on a numeric column. I also made sure that the data is well balanced.

How to make spark execute multiple queries to postgres?

already referred to SparkSQL PostgresQL Dataframe partitions

score 2 · Accepted Answer · answered Sep 12 '19 at 03:21

Truns out you need to have a Index on the column in Database on which you are partitioning the data.

So the following implemnetation is quite valid.

df = sqlcontext.read.format('jdbc').options(url=params['url']
                                            ,driver=params["driver"]
                                            ,dbtable=tableName
                                            ,user=params['user']
                                            ,password=params['password']
                                            ,numPartitions=numberOfPartitions
                                            ,partitionColumn=partitionC
                                            ,lowerBound=lowerB
                                            ,upperBound=upperB).load()

The partitionC is of type numeric as a timestamp, with index in the databse

score 0 · Answer 2 · answered Sep 10 '19 at 02:54

0

Please provide numeric column as the partition column.

Provide the lower value and the upper value of the numeric column.

Please keep number of partions as 10. If there are to many database might clog.

answered Sep 10 '19 at 02:54

loneStar

3,780
23
40

I have tried it with numeric column, but no luck. Also the lower value and the upper value are numeric respectively when i tried it with numeric column – Neha Jirafe Sep 10 '19 at 03:00
Low and upper bound are min and max of the numeric column – loneStar Sep 10 '19 at 03:06
And see how many executors are processing in the executor tab. Also please cache and do count rather than show on data frame. – loneStar Sep 10 '19 at 03:09
@NehaJirafe You still have the issue. – loneStar Sep 11 '19 at 13:53

Partitioning Postgres Read in Spark

2 Answers2