Six Stars

tMap compare integer before UPDATE in expression filter

Hello,

I want to compare two Integer values before update filed.
I have this message when I execute "Incompatible operand types Integer and String"

 

Here is my problem:

I want to insert / update rows and get the exact number of rows inserted and updated for my log filr.

 

Exemple:

- Staging table (Table A)  : 4 rows        Dimension table (Table B) : 5 rows

If  I update one field or one row in my staging table , I must have nb_line_updated =1 in my file log but it put me the number of rows in Staging table (4 rows). I deduce that Talend update all fields...

 

I saw in this topic (https://www.talendforge.org/forum/viewtopic.php?id=25268) how to get the exact number of rows updated, I need to use "Equals" method if a value of my staging table has changed.  "Equals" method returns a boolean True or False.

If !row2.NON.equals(row3.NOM) is TRUE so I update and if it's FALSE I don't update .

it works for String and Date type but not works for Integer type.


Can someone help me how I can figure that with tmap or explain me an other way?
Any help would be really appreciated

Thank you.

 

Version of Talend :Talend Open Studio for Data Integration 6.3.1

 

JobJobSetting jdbc dimension tableSetting jdbc dimension tableSetting jdbc dimension tableSetting jdbc dimension tabletmap inner join -equalstmap inner join -equalslog file Excellog file Excel

 

 

 

 

  • Data Integration
Tags (2)
24 REPLIES
Eleven Stars TRF
Eleven Stars

Re: tMap compare integer before UPDATE in expression filter

For string variables like var1 use:

row1.NOM == null || row.NOM.equals("") ? "" : row1.NOM

For integer variables like var5 use:

row1.age == null || row.age.equals("") ? null : row1.age

(you cannot put "" into an integer variable).

 

For output flow, use conversion from string to integer for age:

Integer.parseInt(row1.age)

TRF
Six Stars

Re: tMap compare integer before UPDATE in expression filter

Thanks @TRF to reply me .

-The probleme if i put 

row1.age == null || row.age.equals("") ? null : row1.age

I have a java.lang.NullPointerException.

 

-row1.age in output is  an integer type , why I should to convert into string ?

For output flow, use conversion from string to integer for age:

Integer.parseInt(row1.age)

 

Eleven Stars

Re: tMap compare integer before UPDATE in expression filter

OK, the problem here is that row1.age will NEVER be a String. The incoming table shows it as an Integer. As such you don't need the following check...

 

row1.age == null || row.age.equals("") ? null : row1.age

You are allowing nulls for these fields and this is what causes your null pointer exception issue. If var5 is null, you cannot make use of the "equals" method. The same for var6. As such you need to implement logic similar to this....

((var5==null && var6==null) || (var5!=null && var6!=null && var6.equals(var5)))

Basically the above says where var5 and var6 are null (therefore equal) OR var5 and var6 both have values which are equal. 

This way you avoid the null pointer exception

 

 

Rilhia Solutions
Seven Stars

Re: tMap compare integer before UPDATE in expression filter

Hi Everyone,

I guess maria requirement is something like below

 Source Table: Person

(Id Integer, Name String, Age Integer)

Id   Name Age

1    John   21

2    Richard  23

3   Sree 

Expected output:

Target table: Destinaton

(Id Integer, Name String, Age Integer)

Id   Name Age

1    John   21

2    Richard  23

3   Sree     NULL

 

When source table value and target table value does not equal then we should update the target table values as source table values.

 

If there is any updating in source table Name column that should also reflect into the target table.

Example: Source: Name: John into John Kenedy 

TargetTable: Name should get update

 

validating String is not a problem but it throws null pointer exception error for Integer to the Integer type.

 

Integer_Integer.PNG

 

 

Eleven Stars

Re: tMap compare integer before UPDATE in expression filter

If you look at @maria94's job you will see that Age cannot be a String. It is set as an Integer. The issue she has is what I have described above. She is trying to use a method (equals) on an un-initialized object (var6.equals). If var6 is null it cannot make use of "equals". 

Rilhia Solutions
Six Stars

Re: tMap compare integer before UPDATE in expression filter

Thanks @rhall_2_0 to reply me.

 

Here is my problem:

I want to insert / update rows and get the exact number of rows inserted and updated for my log filr.

 

Exemple:

- Staging table (Table A)  : 4 rows        Dimension table (Table B) : 5 rows

If  I update one field or one row in my staging table , I must have nb_line_updated =1 in my file log but it put me the number of rows in Staging table (4 rows). I deduce that Talend update all fields...

 

I saw in this topic (https://www.talendforge.org/forum/viewtopic.php?id=25268) how to get the exact number of rows updated, I need to use "Equals" method if a value of my staging table has changed.  "Equals" method returns a boolean True or False.

If !row2.NON.equals(row3.NOM) is TRUE so I update and if it's FALSE I don't update .

 

1.PNG

 

 

2.PNG

 

 

3.PNG

 

 

tmaptmap

 

 

log filelog file

 

 

Eleven Stars

Re: tMap compare integer before UPDATE in expression filter

This is a different issue to the first one. It is a good idea to separate these issues into different posts. This will help others find solutions to their problems.


How are you deciding whether a row should be an update or insert? If it is related to a key field in the table you can do this using one database component. Configure the Field Options to select your Update Key field and set the Action on Data to be "Insert or Update". See below.... ActionOnData.jpg

 

Field Options.jpg

 

Once you have this set properly, you can have the component decide whether it is an insert or update and you can use the related counts to tell you accurately how many of each you have.

Rilhia Solutions
Six Stars

Re: tMap compare integer before UPDATE in expression filter

Thanks @rhall_2_0 for your answer, but you can't insert and update in the same time with your exemple ?

 

Maybe my tMap is wrong ...

63.PNG

C.PNGD.PNG

Capture.PNG

 

 

 

 

 

 

 

Eleven Stars

Re: tMap compare integer before UPDATE in expression filter

You shouldn't need the tMap lookup anymore if you are just checking to see if the record exists. That is ALL handled by the DB component. You will not need to split the flow, just plug it all into the same DB component and configure as I suggested (take a backup first in case you want to stick with your way).

Rilhia Solutions
Six Stars

Re: tMap compare integer before UPDATE in expression filter

It doesn't work for me ...

with your method , if i update 1 field , it  update all rows and the number of rows updated is wrong . 

I configured as your suggested 

 

 

In my exemple : I update one field and add 1 row.

2.PNG

 

 

o.PNG

 

 

 

pm.PNG

 

The number of rows inserted is exact and the number of rows updated is wrong.

1.PNG

 

 

 

 

Eleven Stars

Re: tMap compare integer before UPDATE in expression filter

If the ID exists in the DB it will update the record, if the ID does not exist then it will insert the record. Is this not the functionality you want? An update can only occur (in any DB) if the key field is available. You cannot update a non-existent record. I think maybe you are expecting something different to what I have interpreted?

Rilhia Solutions
Six Stars

Re: tMap compare integer before UPDATE in expression filter

I want to return the exact number of row updated in my job for the log file.

 

if I update 1 field , it will be returned nb_lines_updated= 1 in the log file and not nb_lines_updated= 5.

Currently, it returns the total number of rows of staging table.

 

The INSERT is work.

1.PNG

Eleven Stars

Re: tMap compare integer before UPDATE in expression filter

I see. This says to me that your record from your file is updating multiple DB records. So your ID is not unique in the database. Is that what you expect? If so there is an easy work around assuming that all records output from your file will either insert or update. You can identify the number of records from your file (using a similar technique to the one you are using to count the number of inserts). All you do is use the following logic....

{Number of records updated} = {Number of records in file} - {Number of records inserted}

Rilhia Solutions
Eleven Stars TRF
Eleven Stars

Re: tMap compare integer before UPDATE in expression filter

But you don't have any filter to avoid all existing rows to be updated.

So, if you have 6 rows in the input with 1 new one, you have 5 rows updated. It's normal.

Are you looking for a strategy to update only rows with at least 1 field changed compared to the actual value?

 


TRF
Six Stars

Re: tMap compare integer before UPDATE in expression filter

Yes , I want to update only rows with at least 1 field changed when i compare new data (staging table ) and old data (dimension data).

 

For UPDATE, i had used 'Equals' function to compare between new and old data .

 

I do not know if you have any other solution than my method.

 

 

 

 

 

Six Stars

Re: tMap compare integer before UPDATE in expression filter

My ID is unique (primary key  of my table) .

My problem is similar with this post . Insert or update on duplicate key but not update all fields

Eleven Stars

Re: tMap compare integer before UPDATE in expression filter

The easiest way to achieve this is to either create a hash of your complete record and compare hashes OR (an even simpler way) concatenate your fields from your database query (your lookup), concatenate your fields from your input file (in exactly the same way....and do it as a String) then compare using "equals".

 

However, I now see that you have already worked out how to do that with the first part of your question. You are now looking to identify how to update your record in your DB only where there are differences. The easiest way to do this is with a unique key in your database. Do you have this? If so, simply return the unique key from your lookup where you find a change has occurred. If you do not have a unique key, this will be a lot harder. You might be able to get round this using the t{DB}Row component to dynamically create an update statement that suits each row. Ideally you will have access to a unique key.

Rilhia Solutions
Eleven Stars TRF
Eleven Stars

Re: tMap compare integer before UPDATE in expression filter

So, your 1st design was not so bad as soon as you don't have to deal with delete.

You should have something like that:

                             TABLEB
                                | lookup
TABLEA --main--> tMap (inner join using ID) --reject--> insert newRecords
|
onSubjobOK
|                            TABLEB
|                               | lookup
TABLEA --main--> tMap (inner join using all fields) --reject-->update TABLEA with TABLEB fields
                                |
                             no action required

As suggested by @rhall_2_0, computing a hash key based on all the fields is the solution as soon as you have lot of (+10?) fields.

Let me know if yo want to know how.


TRF
Six Stars

Re: tMap compare integer before UPDATE in expression filter

Thanks guys .

I made @TRF solution and it works  (I think Smiley LOL). 

@TRF can you check if it's right or not pls.

 

 

 

jobjob

 

INSERT tMAPINSERT tMAP

 

 

UPDATE tMAPUPDATE tMAP

 In tMAP for INSERT and UPDATE, it preferable to put "Unique Match " or "All match" in join model ? I don't know what is the difference between both.

 

If I use Hash, it's like in this article Efficient Lookups with Talend Open Studio's Hash Components ??

@TRF or @rhall_2_0 Can you tell me how it works pls ?

 

 

 

 

 

 

 

 

 

 

Eleven Stars

Re: tMap compare integer before UPDATE in expression filter

This will be wrong. The only reason you are getting 1 update is because of the unique match option. 

 

For the moment forget hashing the records, it is completely different to what you are thinking. What you need to do is return all matches, concatenate all of the fields (from the main and lookup), then compare the concatenated values in a tMap variable. If they match, then use that to filter out the record. If they do not match, use that to allow the record to be updated.

Rilhia Solutions
Eleven Stars TRF
Eleven Stars

Re: tMap compare integer before UPDATE in expression filter

@maria94, your design is OK for 1st subjob (insert) but I made a mistake for 2nd subjob update.

After tMap3, for rejected records, you need to have another tMap like tMap_3 but with only ID for the join.

Then, for matching records, get the values from TABLEB instead of TABLEA.

 

For Hash, not, this is not the way.

I'll come back soon with complements.


TRF
Eleven Stars TRF
Eleven Stars

Re: tMap compare integer before UPDATE in expression filter

Concerning the "matching" strategy, the idea is to compare the MD5 signature for old record with the MD5 signature for new record.

To compute the signature, create a routine like this one (Repositry > Code > Routines then Create Folder and Create Routine) :

package routines;

import java.security.*;

public class GetHashCode {
	public static String getMD5(String strCode) {
		java.security.MessageDigest msg;
		String digest1 = "";
		try {
			msg = java.security.MessageDigest.getInstance("MD5");
			msg.update(strCode.getBytes(), 0, strCode.length());
			digest1 = new java.math.BigInteger(1, msg.digest()).toString(32);
		}
		catch (NoSuchAlgorithmException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return digest1.toUpperCase();
	}
}

The getMD5 method expect a string and return the MD5 signature for this string.

Now, to use the routine in a tMap, follow this example:

Capture.PNG

Push the result to a tHashOutput.

Do the same for both old and new records, then using the ID and rowMD5 fields, do a inner join, rejected records will give you all changed records. Imagine you have 100 fields, this method can be very usefull.

 


TRF
Six Stars

Re: tMap compare integer before UPDATE in expression filter

For UPDATE, it works with this design .

Look .

 

-Before UPDATE on TALEND

  • In sql server , I updated 2 rows ID 1 and  2  on TABLE Asql.PNG

     

  • tMAP UPDATE on Talend

 

Capture.PNG

 

  • AFTER UPDATE on TALEND

In sql server , I get values updated in TABLE B

sqlaf.PNG

  • After execute the job

 

 

6.PNG

 

 

 

 

 

 

 

Eleven Stars TRF
Eleven Stars

Re: tMap compare integer before UPDATE in expression filter

OK sorry, I thought TABLEA was the target

TRF