topic Re: How to do this 'simple' MySQL to MySQL job in Talend? in Talend Studio

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

Anonymous — Thu, 09 Jan 2014 23:03:09 GMT

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

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

Anonymous — Fri, 10 Jan 2014 03:21:58 GMT

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

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

Anonymous — Fri, 10 Jan 2014 17:31:20 GMT

Hi,
To your question, yes indeed

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

Anonymous — Sat, 11 Jan 2014 00:07:19 GMT

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!