Friday, February 17, 2012

Talend: Setting up database logging for a project

Talend: Setting up database logging for a project

When executing Talend ETL jobs it is quite important to store the logging information. This is not only relevant for the last execution of an ETL job, but keeping a longer logging history can be quite an advantage. This logging information can be stored in flat files or in a database. We will have a look at the latter option here. I will not go into too much detail, but provide a quick overview. I expect that your are familiar with the basic functionality of Talend Open Studio for Data Integration.
  1. Open Talend and create a new job. Drop the tLogCatcher, tStatCatcher, tMeterCatcher components on the design area:
  2. Click on tLogCatcher. In the component settings click on Edit Schema. In the schema dialog click on the save button. Give the schema a name. Do the same for tStatChatcher and tMeterCatcher. The schemas will then show up in the repository under Generic schemas:
  3. As we don’t need these components any more, deactivate all of them by right clicking on them and choosing deactivate.
  4. Create a new connection to your database of choice in the repository. This will be the database where all the logging data will be stored.
  5. Next we will create the logging tables: Add three tCreateTable to the design area and link each of them with an onSubjobOk row:
  6. In the component settings, assign for each of them the repository database connection.
  7. In the component settings, assign one of the generic repository schemas to one of the three components and assign a table name:
  8. Run the job. All the three logging tables should now exist in your database.
  9. Let’s add these tables to the repository database connection we defined early: Right click on the connection and choose Retrieve schema. Choose the three logging tables and click Ok.
  10. Now we can assign these repository schemas/table definitions to the project settings. In the main menu click on File > Edit project properties. Click on Stats & Logs and then tick On Databases. Assign the repository database connection and assign the respective repository schemata  to the log tables. Finally tick Catch components statistics.

Now logging is set up for your project.

In future you can run some simple SQL statements to retrieve some info about the performance of your ETL jobs.

1 comment: