topic Re: tmap inner join rejecting too many rows in Talend Studio

tmap inner join rejecting too many rows

Anonymous — Tue, 23 Oct 2018 06:08:33 GMT

HI,

I have a job which performs and update or insert on a table but it currently takes too long to run. In the past I've replaced a single dboutput component with 2 and used a map inner join to split out the inserts and updates so I can more efficiently deal with the data.

In this particular job I'm not getting the results I'm expecting. The inner join seems to be rejecting too many rows and I'm getting errors about inserting duplicate keys from the dbOutput that is configured for inserts.

I've confirmed in the database that the majority of the data that is being rejected by the inner join exists in the database.

Can anyone help me in diagnosing what is wrong with the job or what I've missed?

Thanks

Re: tmap inner join rejecting too many rows

TRF — Tue, 23 Oct 2018 06:18:46 GMT

Probably due to your inner join condition or maybe an upper/lower case problem.
You don't share enough information to be sure of what happen (complète tMap and sample data for each case should be great)

Re: tmap inner join rejecting too many rows

Anonymous — Tue, 23 Oct 2018 06:24:33 GMT

I've attached screen shots of the tmap to the original post

On the main input I have all columns

The lookup input I've limited to the primary key (a single column) and have joined it to the main row

The outputs are based on the same table as the lookup but duplicated - 1st is for updates and the second is for inserts

Re: tmap inner join rejecting too many rows

TRF — Tue, 23 Oct 2018 07:45:42 GMT

As the design seems to be ok, search the reason with your data.

Re: tmap inner join rejecting too many rows

Anonymous — Tue, 23 Oct 2018 07:51:17 GMT

Any ideas where to start there? I've confirmed that the keys from the main input that are being rejected by the inner join actually exist in the output table. I'm assuming that means they exist in the lookup table as there's no filtering there

Re: tmap inner join rejecting too many rows

TRF — Tue, 23 Oct 2018 08:00:33 GMT

Check for spaces at the end of the strings, upper/lower case, and so on. Unable to do more if don't share sample data.

Re: tmap inner join rejecting too many rows

Anonymous — Tue, 23 Oct 2018 08:07:29 GMT

I'm unable to share a full data set but I can share keys (they are what's being matched anyway)

The keys are all 64 byte hashed strings. the list below is the first 20 that are output from the inner join reject. I've copied these into a sql query against the same table using a where key in query and have found all of them in the db so I don't understand why they are being rejected by the inner join

1076f3a30e2754ba2a91d979e34f6bcce3894db06097379ca9f0407840313d8
10b9063e59e7cf38e4249c2fa85ce9ebe9805fe952dec6776306a9a45862375
10c55734895e920e0d827d9abcdb32fb282b7e74b96774dc4ea1df9585f1c6b
1191e47d39416a792c33fa485c60d28fb0133b69c9d8e362eeaccde1ca6cb16
11af72a910ac4acf367eef9e6b761e0980842c30d4e9809840f4141d5163ede
123729d5d90048f70e14c86cf5f6b153ed202a7c80ffa5bb215f1573ecc11b4
124eabefd6e9c110734004ccc039bf7fed0bdfca7df71e9cf160de35fbf2133
1299ac65733b5a3d774265fbfe8396b8611e5e3321855dbc541cd301e71fe5e
12b946b1029081bf55269ca19023e0846525dac80a888fdedfad708c723dbfd
1375f53651cff383d9aca5da90de6f4d859a2069e920c39f9700fe8a86e463c
13b42b6edf95609f47cbc126dbd0cb8cc8f2dc6460f7ee25487cedc321079f9
13fa1710b8b0e4816d6eaad9668dab6dfa7ea9f1d07291fa5072e857e94522
14128969dda869dfc9c0da312bc3083b4a2fe3c232918d9ef319a2c763c2d36
14b6b5ad9bc7124b7f65fcc24bc12fe6e7d88015fd3443c1fca0ae97db68b5c
14e9fb03ec3e152833fb9e4541198dce6719bd09349b4b309118e285eb5a3f0
150bc11aeeaa3cdbdc1e27085b0f6c584c27e05f255e303898dcd12426f110
15287fce017a7b874a7466be0d7e0423a7068b9cf19c4d7fa299d35128c7e05
15dd044ee9f809cdf83b19270e5eca74ece7684050fc3eb545b7734cbbff52f
15e81eddfab44be16ac53a8653feab50859b4c5508a915679e33c271d2b54df
1618358b525d4245ce1b96b5976cfe18ac6cab6841513cd3ecd76c517341948

Re: tmap inner join rejecting too many rows

Anonymous — Wed, 24 Oct 2018 07:08:55 GMT

any assistance?

Re: tmap inner join rejecting too many rows

Anonymous — Wed, 24 Oct 2018 07:49:39 GMT

Hi,

Could you please try the Unique Match option in tmap and let us know the results? I could see that you are using All Matches option in your current join.

Since you are trying to check whether the incoming data is having a matching record in DB, unique match should be enough in your case.

Warm Regards,

Nikhil Thampi

Re: tmap inner join rejecting too many rows

Anonymous — Fri, 26 Oct 2018 03:16:50 GMT

Thanks @TRF and @nthampi for your help. Through adding extra debugging I eventually discovered that the consumer_key column in my db was a char field and any values that were shorter than the length of the field had trailing spaces added as padding. I changes the settings on the MSSQLInput to trim String/Char columns and now it works perfectly.

Re: tmap inner join rejecting too many rows

Anonymous — Sat, 27 Oct 2018 20:03:01 GMT

@AB - Congrats for identifying the root cause of the issue.

Could you please mark your last post as the answer to this topic (which contains the explanation of your solution)? This will help to enrich the content of our Talend community.

Warm Regards,

Nikhil Thampi