How to capture Talend Job Activity into databases - Activity Monitoring Console

It is often useful to capture the statistics of a job to monitor the job performance and error codes. Talend has three ways to capture this information.

1. On console
2. In FileSystem
3. On Databases

My personal choice is to capture the logging information in a database since it will be very useful to store history and easy to track the trends of the job activity with a query. This could be very handy to identify any performance bottlenecks.

So here are the table definitions for the logs, stats and volume catchers used by the AMC. You can easily get these definitions by exporting the meta-data from tlogcatcher, tflowmeter and tstatcatcher to Generic Schemas in the repository and reusing them later. Here they are served without going through the pain of creating the metadata again :)

Create these tables on your database and configure your project to capture this information in the database so that the AMC can retrieve this info from database and display it for you.

CREATE TABLE [dbo].[logCatcher](

[moment] [datetime] NULL,

[pid] [varchar](20) NULL,

[root_pid] [varchar](20) NULL,

[father_pid] [varchar](20) NULL,

[project] [varchar](50) NULL,

[job] [varchar](255) NULL,

[context] [varchar](50) NULL,

[priority] [int] NULL,

[type] [varchar](255) NULL,

[origin] [varchar](255) NULL,

[message] [varchar](255) NULL,

[code] [int] NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[flowMeter](

[moment] [datetime] NULL,

[pid] [varchar](20) NULL,

[father_pid] [varchar](20) NULL,

[root_pid] [varchar](20) NULL,

[system_pid] [bigint] NULL,

[project] [varchar](50) NULL,

[job] [varchar](50) NULL,

[job_repository_id] [varchar](255) NULL,

[job_version] [varchar](255) NULL,

[context] [varchar](50) NULL,

[origin] [varchar](255) NULL,

[label] [varchar](255) NULL,

[count] [int] NULL,

[reference] [int] NULL,

[thresholds] [varchar](255) NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[statCatcher](

[moment] [datetime] NULL,

[pid] [varchar](20) NULL,

[father_pid] [varchar](20) NULL,

[root_pid] [varchar](20) NULL,

[system_pid] [bigint] NULL,

[project] [varchar](50) NULL,

[job] [varchar](50) NULL,

[job_repository_id] [varchar](255) NULL,

[job_version] [varchar](255) NULL,

[context] [varchar](50) NULL,

[origin] [varchar](255) NULL,

[message_type] [varchar](255) NULL,

[message] [varchar](255) NULL,

[duration] [bigint] NULL

) ON [PRIMARY]

GO

These table definitions are for SQL Server.

Happy Talending!

Praveena

Tips to interact with Databases efficiently using Talend

TIPS to interact efficiently with Databases using Talend

1. In the Database Output component choose the "Enable Parallel Execution" (Last option in the Advance Settings)
2. Use a reasonable batch-size (The default is 10,000 rows)
3. Use the ELT components and Bulk Execute components where ever applicable.
4. Choose only the columns you are interested in your data-sets. Avoid using select * from in your queries.
5. Choose only those rows that you are interested in by limiting them with a WHERE clause
6. Do not perform blocking operations like "Aggregate/Sorting" on the Talend. The DB is more efficient in doing these operations. For Aggregation/Sorting, the entire dataset has to be loaded before the operation is performed. So if the data-set is huge, it could choke the memory and cpu.
7. Understand the "Commit" options. Commiting frequently has pros and cons

•  Example - You have 20000 rows to be written to the database. Lets assume that we are commiting for every 5000 rows. If there is any error after the second commit (meaning after 5000 + 5000 = 10000 rows), our database will be in a state where rows have been written partially. Unless your code is re-runnable, you could end up in a situation where you need to first clean up before you start again..
• Commiting frequently avoids huge log files on the database.
• Commiting only at the end could have an impact on the memory usage if it is a large batch of rows that you are waiting to commit.

DataViewer - a useful feature to preview data

Many a times, we might be maintaining others code and would like to see what a component is retrieving. The DataViewer is a gem hidden in the context menu that could be quite useful to preview data.

Right Click on the DbComponent will give us the DataViewer for previewing data. 

Happy Talending!
Praveena