[resolved] Talend Incremental Counter similar to type1 scd - Logic

[Anonymous]

I have to create a new talend job that creates a new column in target table. I have to implement auto-incremental logic in the job ...
So that I have to get the maximum id plus one every time I insert a value in the table for each time running the job ...
For example , If i run the job first time i have 100 records loaded to target table with max id (key is 100) - (so 100 records inserted)
Next time if i run the job for next set of data's for inserting 10 new or updating 10 old data, my key should be start as 101 to 110 for insert and same key shud be updated again in case of update job... Not again from 1 to 100 ... How to achieve in talend ... can u you suggest me ?
in tmap component can be achieved or which component to be used ? and also whether to use context / variable / expression ?? give me steps to achieve
Note : My Input component is postgresdbinput and output component is tredshiftoutput db . i need to create a new column in redshift DB with the above mentioned requirement . 

[Anonymous · Accepted solution]

Hi 
First of all, select the max id from target table, eg:
tRedshiftinput--main--tSortRow(order by max_id_column desc)--main--tSampleRow (only read the first line)--tJavaFlex
on tJavaFex, assign the max id to a context variable (int type, set its value as 1)

context.maxID=input_row.max_id_column;

In the next subjob, add a sequence id for each line, the start value is context.maxID+1, eg:
tPostgresqlInput--main--tMap--tredShiftOutput
on tMap, add a new column called ID which maps the max_id_column in target table, and set its value as:
Numeric.sequence("s1",context.max_id+1,1)
Regards
Shong

[Anonymous]

ok thanks

[Anonymous]

Error :
Exception in component tRedshiftOutput_1 .Can't infer the SQL type to use for an instance of java.util.UUID. Use setObject() with an explicit Types value to specify the type to use.
Iam changing the datatype from uuid in postgres (source) to varchar in redshift(target) . How to resolve ?

uuid to varchar