[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
Highlighted
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
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

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach

Watch