How to do this 'simple' MySQL to MySQL job in Talend?

[Anonymous]

Here is my MySQL query to track daily the number of rows in a given table:

INSERT INTO updateRecords
SELECT
SUBDATE(CURDATE(),1),
'tableName',
COUNT(*)
FROM tableName

I don't know how I can turn this into a Talend job; I have several tables to do and I'd like to run one after the other as one big job
Here is my attempt at the query in a Talend tMySQLRow component:

"SELECT
SUBDATE(CURDATE(),1) as 'date',
'tableName' as 'table',
COUNT(*) as 'rows'
FROM tableName"

The error I am currently receiving is "Column 'date' cannot be null"
Thanks

[Anonymous]

Hi,
tXXXRow component is usually used to any type of sql statement excepts select statement, we use txxxInput component to execute a select statement, if you use use tXXXRow to execute a select statement, it returns a record set, and you are required to use a tParseRecordSet component after tXXXRow to parse the record sets.
If I understand your requirement well, do you want to monitor database modification(Inserted, updated, deleted)?
Best regards
Sabrina

[Anonymous]

Hi,
To your question, yes indeed

[Anonymous]

You could write your query dynamically by using context variables.
tFixedFlowInput --row1--> tFlowToIterate --- iterate --> tMysqlInput ---> any output...
For the tFixedFlowInput you setup a simple schema with one column called table_name.
Configure in the tFixedFlowInput the list of tables you want to measure. Alternatively you could read the list also from a text file with tFileInputDelimited. The only important thing is in the flow and the column table_name you will get the name of the table to check.
For the tMysqlInout you use this query:

"SELECT
   SUBDATE(CURDATE(),1) as curr_date,
   '" + ((String) globalMap.get("row1.table_name")) + "' as table_name,
   COUNT(1) as num_rows
FROM '" + ((String) globalMap.get("row1.table_name")) + "'"

Hint for creating queries for MySQL. I have no clue why people putting identifier into the quotas. This is absolute not necessary and could lead into trouble. Do not do this even if Talend unfortunately does this also by default - totally useless!! The only reason could be if you have identifier which is a keyword, but this is also a very bad design!