Search code examples
apache-nifi

Copy data from database1 into database2 in single transaction (Apache NiFi)

We have two databases: database1 and database2. Everyday we want to copy from database1 all data from two tables: db1.table1 and db2.table2 to database2 to tables with the similar (not same) structure in single transaction.

Now I've following solution, but i'm not sure about single transaction:

  1. SELECT * FROM db1.table with ExecuteSQL processor;
  2. Create buffer table1 in db2 and put data with PutDatabaseRecord with pre-SQL query:
DROP TABLE IF EXISTS db2.buffer_table1;
CREATE TABLE db2.buffer_table1
AS (SELECT t1.account_name, t1.cabinet_id
    FROM db1.table1 t1
    WHERE 1=2);
  1. Grab all data from db1.table2.
  2. Create buffer table2 in db2 and put data with PutDatabaseRecord with pre-SQL query.
  3. If no errors occured i'll have data from db1 in buffer tables in db2.

For actions above i don't really need transactional state, but in the next step I should put data from buffer tables into real tables in single transaction.

I need:

  • delete data from db2.table2 and db2.table1 (order is important because foreign key);
  • insert into db2.table1 AS select from buffer1;
  • insert into db2.table2 AS select from buffer2;
  • drop both buffer tables.

I can put all sql comands above into single ExecuteSQL processor, but I guess it's not transactional (i didn't find any information about this).

For example after first step (delete from) error may occur while inserting data into database. And i'll lost all data that was there before and processor will throw error, but data already lost, rollback will be applied only to last failed operation. Or it's wrong?

Is it possible to do it only with Apache NiFi without stored procedure in PostgreSQL server? Any ideas?

Solution

  • Seem like this works (inside ExecuteSQL):

    BEGIN;
... //actions here
COMMIT;