Hi @Kaniz Fatma and @Daniel Sahal , Thanks for reaching out , appreciate it.
Background to help you understand the context .
Problem statement-
We are facing performance issues during Postgres data loading from GCS storage (parquet format) . Data load is done in 2 parts.
First to create postgres intermediate_table by reading GCS data as spark dataframe and save it in Postgres as table .
Then from intermediate load into main table (with some logic ) .We have already implemented the code and it is working fine for smaller data sets
But when it comes to large dataset , it is posing issue .
We ensured there is no constraint in Postgres intermediate table also there is one PK in postgres main table for merging functionality. Postgres is also in GCP.
Elaborating what has been said above with code snippet.
Stage-1 ->
Creation of Postgres intermediate table (persistent table) by writing spark data frame as table in Postgres (overwrite mode) from the data stored in GCS (parquet data format ~ volume 25-30 GB, total number of part files - 71)
Code snippet –
df.write.mode("overwrite").option("numPartitions", number ).format("jdbc").option("url", url).option("dbtable", dbtable).option("user", user_nm).option("password",passwrd).save()
Stage -2 -> Inserting Postgres intermediate table (created in step-1) data into main Postgres table (persistent) via insert statement (SCD type-1).
Code
snippet –
INSERT INTO main_tab (SELECT columns FROM intermediate_table ) ON CONFLICT do something DO UPDATE SET
Postgres and Databricks resource details
Attached screen shots
Tried options ,
- We ensured to use maxfilepertrigger ,batchsize , num of partition options as well in above code to ingest bulk data.
Then we saw spark-postgres option and tried below ways
option -1 – writing dataframe and giving option as “postgres” ex- write.format("postgres")
failed with - ClassNotFoundException: Failed to find data source: postgres. Please find packages
Option 2- We were trying low level pyspark API as suggested in spark-postgres-library ( ex- sc._jvm.io.frama.parisni.PGUtil) but
PGutil is not available in code as well
We tried with postgressql.driver as well but it didn’t work out
https://docs.databricks.com/external-data/postgresql.html
Regards,
