[resolved] How to generate an ID in a dataflow?

[Anonymous]

I've been trying several options to generate an ID in a talend dataflow but it didnt work out.
Basically, I see two ways considering data shall be written into a DataBase:
1) Create a DB column as auto_increment int field. This works fine if I create the target schema manually. But since I want to let tMySQLOutput component do this (using "Drop Table and Create" option) I tried to add such an auto_increment field as additional column in this component. This does not seem to be possible.
2) Use any of the components to insert a counter into dataflow that increments on each row before it comes to writing into DB.
I prefer option 2 because I can imagine various situations where I need such an artificial ID.
I found some posts here touching this issue but no solution. Any help is appreciated..
Thanks
dexter

[Anonymous · Accepted solution]

hi dexter_DSD,
you can use Talend method sequence that 's exactly what you're looking for (i hope) !

/**
* return an incremented numeric id
*
* {talendTypes} int | Integer
*
* {Category} Numeric
*
* {param} string("s1") sequence identifier
*
* {param} int(1) start value
*
* {param} int(1) step
*
* {example} sequence("s1", 1, 1) # 1, 2, 3, ...
*
* {example} sequence("s2", 100, -2) # 100, 98, 96, ...
*
*/

regards
laurent

[Anonymous]

Hi kzone,
this is exactly what I was hoping on. I added Numeric.sequence in a tMap and it provides me with an identifier i can use as PrimaryKey in a database.
Thanks for your quick help!
dexter

[Anonymous]

beware of concurrency issues (if such table is inserted from different processes);
auto-increment in DBMS guarantees consistency in case of two processes inserts simultaneously; talend sequence not (collision).

[Anonymous]

Hi emaxt6,
thanks for the hint. Indeed I use various sources in my dataflow, but I insert the ID after the subflows have been united. So no concurring ID sequences might intefere with each other. Using auto_increment in a DBMS is an option, but at least for now the output component to a DBMS is not able to generate the auto_increment field. (I dont want to alter the table nor create the schema in a manual step)
regards
dexter