topic Re: Look up to get max id in Talend Studio

Look up to get max id

A_San — Sat, 16 Nov 2024 04:55:36 GMT

New to talend. I am trying to load a table say stg0 from a source file. one of the columns in target is batch_id. For every run, the batch id should increment by 1.

For ex: If I load file 1 and later file 2, the batch id should be 1 for file 1 and 2 for file 2. So I should look up on the STG0 table and get the max batch id and increment by 1. I am trying to use tmap to achieve this. My main connection to tmap is the source delimited file and look up table is STG 0. In tmap, I cannot join between source file and stg0. I am not sure how to go about this. Any help would be appreciated.

Re: Look up to get max id

root — Mon, 19 Aug 2019 20:00:22 GMT

One way I can think (if I understand your question well ), is:
Read the stg0 table and find the max batch_id and save it in a context/ globalMap variable.

Then, load the file and connect it to target via tMap.

In the tMap, where the target batchid is present, set the value as the context/ globalMap variable + 1

HTH.

Re: Look up to get max id

A_San — Mon, 19 Aug 2019 21:00:17 GMT

Do you mean to say not to have a look up?

I can try that. Could you please let me know how I can save it in a context/ globalMap variable ? Should this be done in Tmap?

Re: Look up to get max id

A_San — Mon, 19 Aug 2019 21:04:12 GMT

This is what I have currently. Please see screenshot of my job

SS1.PNG

Re: Look up to get max id

root — Mon, 19 Aug 2019 21:19:15 GMT

I would have tDBInput --> tJavaRow where I capture the output of the query. Then, assign that value to context/ globalMap:

lets say you have the query as: select max(id_batch_id) as 'current_max_batch_id' from stg_0_id_cntl_nmc;
Keep this id as Int in the schema

// in tJavaRow
// setting context
context.current_max_batch_id = input_row.curr_batch_id
OR
// setting via GlobalMap
globalMap.put("curr_batch_id" = input_row.curr_batch_id;

Then, on subjob ok, you can do the file list --> fileinputdelimited --> tMap --> tDB Output

in tMap, for the target field of batch_id, you can use the expression
// if context:
context.current_max_batch_id +1
or
// (int)globalMap.get("current_max_batch_id") + 1;
// (Integer)

HTH.

Re: Look up to get max id

JR1 — Mon, 19 Aug 2019 21:35:46 GMT

Or without custom coding using tJavaRow, you could have your tDBInput component's output (column name "count") end in a tSetGlobalVar and set the value to row1.count (replace row1 with the name of your connector). You could then use this variable in your tMap (without any lookup) and raise it by one using

((Integer)globalMap.get("count")) + 1

as an output expression. If you can solve something without custom code components, you should do so.

Re: Look up to get max id

A_San — Mon, 19 Aug 2019 22:06:36 GMT

I am getting this error in tJavaRow.

Error in the component's properties:current_max_batch_id cannot be resolved or is not a field

Also how do I link tJavaRow to the main flow which is file list --> fileinputdelimited --> tMap --> tDB Output

Re: Look up to get max id

A_San — Mon, 19 Aug 2019 22:08:10 GMT

I am not sure I follow this - tDBInput component's output (column name "count") end in a tSetGlobalVar and set the value to row1.count (replace row1 with the name of your connector). Could you please explain? I am very new to Talend.

tSetGlobalVar is also a custom code, isnt it?

Re: Look up to get max id

JR1 — Tue, 20 Aug 2019 07:04:51 GMT

OK. Looking at your job, I have an additional question. Do you want the number to increase withing the job, i.e. for each file you are loading in this one job, or do you want this to increase between job runs as well, i.e. yesterday this job ran and in the end the number was 164 and today it should start with 165?

Re: Look up to get max id

A_San — Tue, 20 Aug 2019 15:15:09 GMT

Batch Id should increase between job runs. I have another column file_id.

When I run the job today, the batch id will be say 1 and if there are 2 files to be loaded in this batch, then the file id will be 1 and 2 for the respective files.

Next time when I run the job, the batch id will be 2 and the file id will be reset to 1 and depending on the no. of files, it will be incremented by 1.

Please see attached screenshot for example.

SS2.PNG

Re: Look up to get max id

JR1 — Tue, 20 Aug 2019 18:44:15 GMT

OK, here we go.

I have recreated the job as a rudimentary structure. In a first step, you create a tDBInput and put in the "max" statement. The schema of this component must just be "count" of type "Integer" (feel free to chose a different column name). If your table contains a lot of records, executing this query may take a while - use the usual query optimisation techniques like indexes in this case (outside this job).

Then you store this max value in a global variable using tSetGlobalVar (increased by one). Name it "count" or whatever.

Then you can use this global variable in your tMap (tMap_1 in the picturre above) and all the records will get the same value for "batch" (which is what you want)

Let us know, if this solves your problem.

Re: Look up to get max id

A_San — Thu, 22 Aug 2019 17:47:56 GMT

JR,

I did as you said. Not sure if I missed something. I am getting some errors. Please see screenshot.

Appreciate all your help.

SS4.PNG
SS3.PNG
SS5.PNG

Re: Look up to get max id

JR1 — Thu, 22 Aug 2019 18:24:06 GMT

Yes, you missed something. It is "Count" which needs to have double quotes around it and row1.count + 1 must not have them.

Re: Look up to get max id

A_San — Thu, 22 Aug 2019 18:44:43 GMT

Thank you JR.

Could you also please let me know why I am getting "Type mismatch: cannot convert from char to String" error in tMap?

Re: Look up to get max id

JR1 — Thu, 22 Aug 2019 19:23:02 GMT

Sorry, I missed that. Sure: change the 'S' to "S" for column Id_stus. Single characters in single quotes are treated as Char in Java, whereas double quotes define a String. Basically, you tell Talend to put a Char value ('S') into a String column.

Re: Look up to get max id

A_San — Thu, 22 Aug 2019 20:01:40 GMT

I am gettting this error now. Reason could the table is empty. How and Where do I say that if max(batch_id) is null, then count should be 1 to start with?

Exception in component tSetGlobalVar_1 (STG_0_LD_CNTL_NMCP)
java.lang.NullPointerException

Re: Look up to get max id

JR1 — Thu, 22 Aug 2019 20:18:36 GMT

How about modifying your SQL statement so that it always returns at least 1. If you are using PostgreSQL, you could try with the following (not a PostgreSQL expert):

SELECT COALESCE(MAX(batch_id), 1) FROM XXXX

COALESCE always returns the first value of the parameters that is not null.

If this does not work, you could put a tMap after the "count" tDBInput and set the value to 1 by using the following expression

row1.count == null ? 1 : row1.count

Re: Look up to get max id

A_San — Thu, 22 Aug 2019 20:36:30 GMT

Many many thanks, JR. The job ran fine. The data looks good as expected.