To load data from Azure Delta Lake & Azure Data Bricks to Postgres SQL in single transaction( To insert 10 tables of data and rollback for any errors)

Question

I would like to load data (around 10 tables) from Azure delta lake to azure postgressql. I have tried jdbc connectivity in azure data bricks.I can able to insert and read data from delta lake to postgressql. But not able to achieve atomicity (to insert all tables data in a single transaction). Could you help better way to connect between azure delta lake to postgressql and how to achieve atomicity?

Data frequency is hourly feeds from source and volume of data is very huge. Thank you.

score 0 · Answer 1 · answered Jul 28 '21 at 14:27

0

If I understand well, you would like to have an atomicity for all postgresql operations every hour, right ?

You should deactivate AutoCommit by default conn.setAutoCommit(false), and commit explicitly when the 10 upserts are successful via jdbc conn.commit().

answered Jul 28 '21 at 14:27

amineben

1
1

Thanks for your response.conn.setAutoCommit works only to insert data within PostgresSQL tables through ADB. My requirement is to insert data from DeltaLake tables to PostgresSQL. Please find sample code below. import org.apache.spark.sql.SaveMode import java.sql.DriverManager val connection = DriverManager.getConnection(jdbcurl,connectionproperties) spark.sql("select * from delta_Employees") .write .mode(SaveMode.Append).jdbc(jdbcurl, "public.temp_Employees", connectionproperties) – naresh t Jul 28 '21 at 14:42

To load data from Azure Delta Lake & Azure Data Bricks to Postgres SQL in single transaction( To insert 10 tables of data and rollback for any errors)

1 Answers1