topic Re: SQL script returns more data than I can store in RAM in Talend Studio

SQL script returns more data than I can store in RAM

Anonymous — Wed, 11 Jan 2017 09:29:55 GMT

Hi!
I use a large SQL script in tPostgresqlInput, which returns more than 100 GB of data while I am using a server with RAM 16 GB.
There is no way to cache all the data in memory, so setting xms/xmx is not a solution. Could you please give me a piece of advice what I can do in such a situation? Maybe I can fetch the data from PostgreSQL partitially (but remember that the SQL script is rather complex, it builds a data mart table) or swap selected data to the server hdd?
Thanks!

Re: SQL script returns more data than I can store in RAM

Anonymous — Tue, 17 Jan 2017 07:28:49 GMT

Hi,
What's your target output? Do you want to extract 100 GB of data from Postgresql and load it into other DB?
Best regards
Sabrina

Re: SQL script returns more data than I can store in RAM

Anonymous — Tue, 17 Jan 2017 08:10:54 GMT

Hi Sabrina!
Yes, it is another DB (PostgreSQL) on another server. There is no direct connection between source and target databases but both of them are connected to ETL-server.

Re: SQL script returns more data than I can store in RAM

TRF — Tue, 17 Jan 2017 10:26:58 GMT

Hi,
Why don't you simply push selected data on one or more CSV files, then reuse these files to load data into the target DB?
Regards,
TRF

Re: SQL script returns more data than I can store in RAM

Anonymous — Tue, 17 Jan 2017 11:08:15 GMT

As TRF said, you need to break this problem down. Even if your SQL query is really complicated, it can be filtered with a WHERE clause. A nice way to try to solve this problem could be as simple as iterating over multiple calls of this query and changing the WHERE clause each time. This would break the memory load into manageable chunks while not having the overhead of creating a load a files. You will need to think about what you will filter on and whether it is suitable to break the data into the chunk sizes you can work with though.

Re: SQL script returns more data than I can store in RAM

Anonymous — Fri, 20 Jan 2017 11:11:29 GMT

TRF, rhall_2.0
thanks for your answers. I thought there could be a way to fetch data from source RDBMS server by batches without any additional rework of my jobs.