Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Oracle to Postgre, moving data from view to table

avatar
New Contributor

Hi everyone,

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.


Thank you

1 REPLY 1

avatar

I to am attempting to migrate from Oracle to Postgres.  I am using the template this blogger recommends but am unable to get it to work with NiFi 1.9.2  https://blog.pythian.com/database-migration-using-apache-nifi/

Any help would be much appreciated!