topic tDBCommit every 1000 rows - how to? in Talend Studio

tDBCommit every 1000 rows - how to?

mr_burns — Fri, 15 Nov 2024 22:12:57 GMT

Hi,

since the talend mysql components have no option "Commit every" (see https://community.talend.com/s/question/0D55b00008C8K8dCAF/question-on-mysql-database-and-commit) to commit data e.g. every 1000 rows I tried to find a workaround but could not succeed.

Here is my flow:

As you can see in the component view and also in the log the tJavaFlex_1 together with tRESTClient_1 get data in chunks of 1000.

Every 1000 rows are move forward to tDBOutput_1 to write into the mysql database.

Using tBCommit_1 I wanted to commit all 1000 rows but this is not working, monitoring the mysql database shows that the data does not come in chunks of 1000.

At the end of the job (using tPost_1) I have a final commit and only then all 20000 records are committed as once.

tDBOutput_1 and tDBCommit_1 are set to "Use an existing connection" and point to same tDBConnection_1 component.

If I use the output 'Main' from tDBOutput_1 into tDBCommit_1 then every record is committed - but this is also not what I want.

Connecting tDBCommit_1 to tJavaFlex_1 (OnSubjobOk) is also not working:

Why tDBCommit_1 does not commit every 1000 rows? What I am missing please?

Re: tDBCommit every 1000 rows - how to?

Anonymous — Mon, 09 Jan 2023 15:18:49 GMT

Try to iterate each input row and commit for each iteration. eg:

tJavaFlex--row1--tFlowToIterate--iterate-->tFixedFlowInput--main--tRestClient......oncomponentok--tDBCommit

tFixedFlowInput: generate the current row.

Regards

Shong

Re: tDBCommit every 1000 rows - how to?

mr_burns — Tue, 10 Jan 2023 09:44:06 GMT

@Shong, thanks for reply.

I tried your suggestion:

and it is working now 😀.

tFixedFlowInput_1 I configured like:

Since it is working the problem is solved, but I do not really understand why I need to use tFlowToIterate_1 and tFixedFlowInput_1 to solve my problem.

Could you please explain it a bit more detailed?

Thanks a lot...

Re: tDBCommit every 1000 rows - how to?

borke02 — Tue, 10 Jan 2023 10:41:10 GMT

I wrote a component as a copy of tPostgresqlCommit years ago with a begin, main and end part where I can configure a number when a commit should be made. The component still works in Talend 8.0.1.

I don't know if I'm allowed to post the code here. Also, the component needs to be changed to MySQL.

Output:

Starting job CommitEvery at 11:20 10/01/2023.

[statistics] connecting to socket on port 3868

[statistics] connected

commited 1000

commited 2000

commited 3000

commited 4000

commited 5000

commited 6000

final commited 6666

[statistics] disconnected

Job CommitEvery ended at 11:20 10/01/2023. [Exit code = 0]

Regards,

Frank

Re: tDBCommit every 1000 rows - how to?

mr_burns — Tue, 10 Jan 2023 11:31:49 GMT

@Frank Borkenhagen :

Thanks but in my case the webservice itself needs to be chunked to keep the responding data packages small.

To chunk only the insertion into the mysql database is not what I needed.

But may it is interesting for someone else.

Re: tDBCommit every 1000 rows - how to?

Anonymous — Wed, 11 Jan 2023 01:58:23 GMT

tDBCommit commit all the changes cached in the DB connection object. In your job design, tDBCommit is only executed one time, if you iterate the input data, tDBCommit is executed for each iteration.