topic Jobs getting stuck while loading large data tables to target in Talend Studio

Jobs getting stuck while loading large data tables to target

Anonymous — Sat, 16 Nov 2024 02:40:16 GMT

Hi Experts,

I am using Talend Open Studio for Data Integration Version: 6.3.1. I am using Open Source version. I am trying to load data from a postgres table (10 million records) to a postgres table in my Target database. When I trigger the job, I only see that the job started, but it is not loading any data to my target table and it is not even failing. Even if I try to load the same to a flat file, even then it is not starting.

Can someone please help.

Re: Jobs getting stuck while loading large data tables to target

Anonymous — Thu, 16 Apr 2020 14:58:20 GMT

Hello @suvi

Can you post a screenshot of the output components configuration window?or the job itself?

Re: Jobs getting stuck while loading large data tables to target

Anonymous — Thu, 16 Apr 2020 15:09:57 GMT

Hi Snishtala,

Thanks for the response. I have attached the screenshot of my job. There is nothing in my job. It is just dump of data from table to a flat file.

Talend issue.png

Re: Jobs getting stuck while loading large data tables to target

Anonymous — Thu, 16 Apr 2020 15:19:30 GMT

@suvi - What is the error on input component?

Is it related to query given in the component? Can you try putting limit on your query to see if its loading data to file at all?

Re: Jobs getting stuck while loading large data tables to target

Anonymous — Thu, 16 Apr 2020 15:54:02 GMT

@snishtala Yes on applying the filter at SQL query level, I am able to insert data to Table as well as file, but if I want to insert the entire data in one flow, its not happening.

Is there something that can be done to achieve it in one go. I tried increasing the batch job size and also the commit limit but still facing the issue.

The reason for getting it done in one go is, some of the tables are having around 60M records and if I split by applying filter, I will have to create multiple flows.

Re: Jobs getting stuck while loading large data tables to target

Anonymous — Thu, 16 Apr 2020 16:07:10 GMT

@suvi - Okay in that case what is the batch size you are using in output component?

And also are you using cursor in DB input component in advanced settings?

Re: Jobs getting stuck while loading large data tables to target

Anonymous — Thu, 16 Apr 2020 16:20:38 GMT

@snishtala I tried different batch size 1000, 10000, 100000 and same I tried for commit size.

No I have not tried the cursor from the Input component.

Re: Jobs getting stuck while loading large data tables to target

Anonymous — Thu, 16 Apr 2020 16:26:04 GMT

@suvi - Okay rather than batch try using the extended insert in input component(set it starting from 1000) and leave the commit at 10000. And also try putting the cursor value same as whatever you put for extended insert.

Re: Jobs getting stuck while loading large data tables to target

Anonymous — Thu, 16 Apr 2020 17:04:26 GMT

@snishtala I tried to look for the "Extended Input" option but I am not able to locate it. Could you please let me know where or how to do that.

I am pretty new to Talend tool.

Re: Jobs getting stuck while loading large data tables to target

Anonymous — Thu, 16 Apr 2020 17:05:26 GMT

@snishtala sry typo "Extended Insert"

Re: Jobs getting stuck while loading large data tables to target

Anonymous — Thu, 16 Apr 2020 17:07:01 GMT

@suvi - In the advanced settings of output component.

Any luck using cursor?

Re: Jobs getting stuck while loading large data tables to target

Anonymous — Thu, 16 Apr 2020 18:14:14 GMT

@snishtala I am not seeing the option in the Advanced section. I am using Open Source 6.3 version.

I do not see any progress with the cursor option.

Advance option for Target.png

Re: Jobs getting stuck while loading large data tables to target

Anonymous — Thu, 16 Apr 2020 18:22:47 GMT

@suvi - My bad. I totally forgot that its for postgresql . you wont see that option. you are right.

Re: Jobs getting stuck while loading large data tables to target

Anonymous — Thu, 16 Apr 2020 18:27:50 GMT

@suvi - Try taking a look at the below link.

https://community.talend.com/t5/Design-and-Development/Inserting-1-000-000-rows-into-PostgreSQL-table-on-RDS/m-p/123164#M77497

Re: Jobs getting stuck while loading large data tables to target

Anonymous — Sun, 19 Apr 2020 03:49:58 GMT

@snishtala I did some changes into my job. Setting cursor to 100K, commit level to 100K, batch load to 100K, check the option on Trim all spaces, Tmap: ignore traling zeros while load and finally fetch year wise data. I am able to do 5M data insertion using open source and a python script in a Linux server in 20 min.

Re: Jobs getting stuck while loading large data tables to target

Anonymous — Sun, 19 Apr 2020 04:10:56 GMT

@suvi - glad you found a solution.