topic Re: Join Talend logs / stats tables and custom logging table in Talend Studio

Join Talend logs / stats tables and custom logging table

Careau — Thu, 08 Jun 2023 19:34:09 GMT

I'm using talend integrated logging. The one specified in the project setting. And it's working fine.

But i have another custom logging table for specific purpose. I would like to join the talend's tables and mine with sql.

The talend table have no index, primary key and stuff like that. I can create them but I wonder if it could cause problem with talend if i add extra stuff on it's table?

Does anyone already did that ?

Re: Join Talend logs / stats tables and custom logging table

Lord-Vader — Fri, 09 Jun 2023 09:31:30 GMT

Hi @Guy Careau ,

If you want to join them you have to have a matching column. But if you want to just simply add them like append the schema must match too. so i think maybe you will probably get an error without a matching column.

Hope this helps.

Vader Out 💪.

Re: Join Talend logs / stats tables and custom logging table

anselmopeixoto — Fri, 09 Jun 2023 20:24:53 GMT

Hi @Guy Careau

You can use the *pid columns to create a relationship with your custom log table. In similar scenarios like yours, I usually consider a composite key with pid, father_pid, root_pid and system_pid to create a "stronger" key.

However, you need to be careful to avoid duplicated rows. For example: the stats table will always (considering a successful execution of a job) have at least two tuples for the same *pid columns, one for the begin and another for the end of Job execution. You can tell by the message_type column.

Other duplicates might be caused if you enable component statistics. In that case, the origin column will indicate the component and you'll also find a begin and an end tuple for each component execution.

Therefore creating a database primary key using the existing columns would not be an option. I guess there will be no problem if you add a custom column to the end of the table with a sequential value, for example, but I never tried it this way.

Re: Join Talend logs / stats tables and custom logging table

Careau — Mon, 12 Jun 2023 12:40:29 GMT

Yes I know that I can use the *pid column for joining my table and talend's.

But my question is can I create an index on the pid's column someting like

alter table TALEND_STAT add constraint TALEND_STAT_PK primary key ( PID);

as well as a constraint.

When the talend's logging ans statistic table will containt thousand of rows.