Problem to upload tStatsCatcher Data in Database

Highlighted
Five Stars

Problem to upload tStatsCatcher Data in Database

Hi everyone,

 

   I got a very weird problem. I’ve attached photos of my job in post so you can have a better idea of what the issue might be, don’t hesitate to ask me for more details as my job is particularly complex.

I have four jobs that will execute the same tasks but with different input data. 3 of them work perfectly while the fourth one (which is meant to be exactly the same) has an issue.

 

   For each job, I’ve set a tStatCatcher, linked to a tDBOutput. To set the table that would receive the data from my tStatCatcher, I’ve used the MySQL code provided by Talend on their website to get exactly the same columns as my tStatCatcher, so I don’t even need to use a tMap for my stats. The problem is that in one of my jobs, everything runs perfectly but the Stats won’t upload in my DB, and I get the following error message, twice (I guess at the beginning and at the end of the job, when the tStatCatcher tries to get the current datetime) :

 

Data truncation: Incorrect datetime value: '' for column 'moment' at row 1

 

   It seems quite weird, because if I put a tLogRow after my tDBOutput, it displays the stats,including the « moment ».

D4A69D48.PNG8B28ACA9.PNG

 

 

   So I have a first operation (blue one) that extracts CSV data from a tFileInputDelimited to a database table (jira_loader).

 

MicrosoftTeams-image (1).png

 

 

   When this step is over, the next one (pink one) is a translation of the datas freshly uploaded. I use a translation CSV file to convert my data to IDs that correspond to a parameters table in my DB. The translation file contains the infos to translate 6 columns, therefore it is included 6 times in my job, using joins with the main input in my tMap to indicate which input column has to be translated with which file data. I then map the translated data into a result table (and also in a histo_mvt table to keep archives of everything).

 

   My last task is then initiated (green one). I want to write my data from result table into a tFileOutputJSON. I use the same method as before to re-translate my IDs into the value corresponding, which are contained in my param table, so again I put as much tDBInputs as I need to translate all of my columns. Just before that I also want to filter the data taken from the result table so I put a filter on the tstatcatcher table to extract only the data concerning this job and not the 3 other ones, and only the data where the message column equals « success » to exclude the failures. Then I put a tAggregateRow to select only the maximum « moment » data, so in the end I get the date of the last successful job. In my tMap4, I compare it with the dat of my input data using the following filter expression in my output :

 

TalendDate.compareDate(jira_result_output.modification_time_ticket ,AggregateRow_output.moment) == -1  &&  jira_result_output.id_customer == 1

 

MicrosoftTeams-image (2).png

I’ve spent a lot of time trying to solve the problem but I can’t find any explanation or spot any difference between my job that could be linked to that stat problem.

 

Thanks for your help !

 

Moderator

Re: Problem to upload tStatsCatcher Data in Database

Hello,

What does your date format look like? What's Mysql DB version are you using?

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Five Stars

Re: Problem to upload tStatsCatcher Data in Database

Hi,

 

We are using the DateTime "YYYY-MM-DD HH:MMSmiley FrustratedS" format and our My SQL Database version is 5.7.17. 

We stay available for more informations. 

 

Best Regards, 

Antoine

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog