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

One Star

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

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

Accepted Solutions
Seventeen Stars

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

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

All Replies
Seventeen Stars

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

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
One Star

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

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
Highlighted
Six Stars

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

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).
One Star

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

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

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.