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.

ship_0-1643844420340.png

SplitRecord: Splitting records for: 1 records per flowfile.

 

Entire nifi flow looks like below: 

ship_1-1643845739705.png

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?

 

TIA...

0 REPLIES 0
; ;