Join Talend logs / stats tables and custom logging table
Hi
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?
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.
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.
