Iam having trouble to find the right way of moving data from oracle db to postgres db. Iam sure its nothing too complicated for the more experienced but iam quite new to nifi and still learning.
I want to move once every day a view from ora and put it into a table i created in postgre. Its roughly 4,2 milion records(1,1GB in avro).
When i was testing it with smaller amounts of data i wasnt experiencing too many problems, but once i started with the whole view i ran into a few.
Nifi version 1.9.1, OS Windows 2016, 4 cores, 16GB RAM, java 1.8
Iam using QueryDBTable or ExecuteSQLRecord, anyway i will get the data in avro. If i put it like this straight into postgre, i run into the problem that all the datatypes are "string" and not int, date, float whatsoever. So it wont even put it into the DB but finished as failed.
So i tried using AvroToJson -> JsonToSQL -> PutSQL, here i was experiencing heavy RAM usage and a lot of processing time. At one point i added 120GB ram to the server and nifi was able to take almost all of it just to process those 1,1Gb of data.
What path should i take here? Different conversion or processors to use? Any tips will be helpfull.