Support Questions
Find answers, ask questions, and share your expertise

nifi workflow optimization: copy large data from sql DB to Postgres DB

New Contributor

Hi All.. 

I need suggestions on how can I optimize the below nifi flow.

Requirement: I have below nifi flow which works fine but it is slow. I need to copy 20M records from SQL DB table to Postgres DB table. 

I have ExecuteSqlRecord(see below details on properties set) --> SplitRecord --> EvaluateJsonPath -->UpdateAttributes --> Lookup on FetchDistributedMapCache -->JoltTransformationJSON--> ConvertJsontoSQL-->PUTSQL.


ExecuteSqlRecord : I have Select statement with joining multiple tables. So each flowfile has 10000 records.


SplitRecord: Splitting records for: 1 records per flowfile.


Entire nifi flow looks like below: 


so flow runs fin but it takes long time to completely migrate the data from sql to Postgress. For 10Million records it took 16 hours. 

Question: How can I optimize this nifi flow and is there any pointers so that copying data can be faster here?



; ;