How to generate a run id?

[Anonymous]

Hi!
I am loading snapshots from table1 to table2. I want to generate a run id which will be a unique number to identify any job run.
So I will have:
Table1

ID Name
1    a
2    b
3    c

Table2 (after two consecutive loads, each of which consists of three rows)

ID Name RunID
1    a        1
2    b        1 
3    c        1
1    a2      2
2    b2      2
3    c2      2

In this example I loaded Table1 into Table2, then updated Name column for each row of Table1, and then again loaded Table1 into Table2.
I want RunID to be generated by target database sequence run_id_seq (PostgreSQL).
Could please give me an advise how to accomplish such task? Thanks!

[JR1]

Hi
Sure. 

Create a sequence in PostgreSQL and remember its name.
Your job must first create the connection to PostgreSQL (tPostgreSqlConnection). 
The next subjob (OnSubjobOK) should look like this:
tPostgreSqlInput_1 --row1--> tFlowToIterate --iterate--> tPostgresqlInput_2 --row2--> tMap --> tPostgresqlOutput. 
In tPostgresqlInput_1 execute the query "select nextval(your sequence) AS seqid", create one field in the schema named seqid (DB type bigint) and pass the record into the output. 
Leave tFlowToIterate as it is.
Configure tPostgresqlInput_2 to your liking (i.e. query ID and Name).
In tMap create the field RunID in the desired output and set ((String)globalMap.get("row1.seqid")) as the expression.
Configure tPostgresqlOutput to your liking (i.e. Action on data "Insert" and schema ID, Name and RunID). The target table must not have ID as a key column (in fact none of the columns must be key columns).

Please let us know how this works out for you.
Regards,
Joachim

[Anonymous]

@JoRoesecke
Thanks, that helped!
This "((String)globalMap.get("row1.seqid"))" part is not very intuitive... How can one learn about this kind of coding in Talend?

[JR1]

Hi
Glad this works out for you. Unfortunately you can only learn about this kind of coding if you dive into Java and know how Talend stores global job "variables". In every Talend data integration job there always is an object "globalMap" (caution: in Java everything is case-sensitive). This is actually a Java HashMap (a Java object type storing key/value pairs). In your case, the component tFlowToIterate implicitly stores the input value "row1.seqid" into this object with key "row1.seqid" and the actual value in its value. This can then be retrieved in subsequent steps of your flow by using ((String)globalMap.get("row1.seqid")). This actually says the following: get the value you have in globalMap for key "row1.seqid" and cast it as a String. You will have to cast it as a String ("(String)" at the beginning) because Java does not know of the data type the value has (Java is a "strongly typed" language as compared to e.g. JavaScript). It would result in an error if you e.g. cast it as an Integer ("(Integer)") and either the output column is not of type "Integer" or the actual value was not an Integer. In order to fully understand this, you will have to gain some knowledge on Java and object-oriented programming in general. globalMap in this case is an instance (an object) of the class HashMap and "get" is a method defined in this class. It becomes intuitive once you can see, that every Talend job simply creates a huge Java programme or "script" (not to be confused with JavaScript).
Regards, Joachim

[vapukov]

some short examples You can find there - https://help.talend.com/search/all?query=tFlowToIterate&content-lang=en