topic Re: INSERT in Talend Studio

INSERT

Anonymous — Thu, 18 Apr 2019 08:19:14 GMT

Hi all!

I am new to the forum, so first of all, in case this post is not in the right place, I ask that you move it where appropriate.

I am creating a TALEND with API connection, with what is achieved is to make a GET of all available accounts and they are saved in a HASH.
An Excel file with information is loaded, in a TMap it is checked if those accounts exist or not, in case they exist an INSERT BULK is done in case there is not an UPDATE BULK.
The problem that I find is when a unique ID to insert arrives. I need to be able to control in some way that when it is a unique ID I do an INSERT without bulk but I can not find the correct key.
I have created a global variable to do the account through Java but it does not work.

I attach captures.

Re: INSERT

Anonymous — Thu, 18 Apr 2019 08:51:46 GMT

Hi,

Since you are using an API to extract the data and you are using the hash memory for interim storage, I believe you are playing with low data volume and not in millions of records.

So my first suggestion will be to avoid bulk components to avoid complexity in overall flow. Now, coming to the lookup query, I would suggest you to take the maximum value of unique id and then use it as a starting value of a sequence inside your tMap. This will make sure that you are having unique ids during insert without any issues.

Numeric.sequence("s1",context.max_id,1)

In this case, context.max_id will have the current max value.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: INSERT

Anonymous — Thu, 18 Apr 2019 08:59:09 GMT

Thanks for your reply!

I do not understand very well where I should place that numerical sentence inside the tMap? Create a variable in the tMap and that statement put it as an expression?

Thank you!

Re: INSERT

Anonymous — Thu, 18 Apr 2019 09:03:50 GMT

Hi,

You are right. Your unique id should be mapped to the target variable where the sequence will be used to generate new unique key. If you have not added this target DB variable in tMap, you will have to add it as an additional variable.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: INSERT

Anonymous — Thu, 18 Apr 2019 09:18:39 GMT

Thanks Again for your fast reply!

I created this typology in tMap

My ID comes from row 18, and the map to the new variable:

I know it is not correct, but I want to make sure that I have understood correctly what you have told me.

Is this option as I propose it correct?

Thanks again!

Re: INSERT

Anonymous — Thu, 18 Apr 2019 09:39:19 GMT

Hi,

I would do something like below where I will fetch the existing id max value to a context variable as first step.

In the second subjob, I will start loading the data with id value starting from current max value+1.

The value from DB will be fetched as below.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: INSERT

Anonymous — Thu, 18 Apr 2019 10:18:41 GMT

Thanks for your faster reply!!

It is a good solution with what I have stated, really. But I have some doubts that I hope you can guide me in your answer .

The first, when consulting an Excel file of input and then go through the tMap if I put there that numeric variable +1 will not work the insert or update that "filter" discriminatory by ID since in the Excel does not come any ID, the ID the I pick up a subquery in the row18 (GET API connection).

It had occurred to me to implement what you said but in the INSERT or UPDATE part by means of some type of tFilterRow, that is:

Create a counter of ROWs per ID, if the result is <= 2 that makes a BULK if it does not make an INSERT

That solution as I could raise it? I have tried with a tFilterRow but it does not work either, I can not think of how I could place that IF.

Thanks again!!

Re: INSERT

Anonymous — Thu, 18 Apr 2019 10:38:38 GMT

Hi,

If my understanding is correct, you are clear about adding the id using sequence for insert records. Your current query is how to separate insert and update records based on input data, right?

If you are having the ids from input files itself, then you can separate the data to two flows by checking whether the input id value is null or not. All those records with null id value are candidates for insert and all those records with id value are candidates for update. In the tDBoutput component schema, you need to put a tick mark to the column which will act as key for insert or update.

If your scenario is different from above, where you do not have a key from input file, then you will have to do lookup with DB before identifying insert and update records. Join the input dataset with lookup table using inner join and all those records which are failing inner join are candidates for insert (where you can use the sequence from my previous post).

The tMap will look something like below.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: INSERT

Anonymous — Thu, 18 Apr 2019 10:51:18 GMT

Thanks again!

I will try to explain a little the talend that I am developing.

All the connection I do is via API, so the first thing I do besides request and collect a login token and save it (first line), is to make a call to the DB of the entity to which I want to do the INSERTION or UPDATE.

The second, when receiving the data from an EXCEL file, I opened that file with a tFileInputExcel and using the figure of tHashInput, I mapped all the Excel fields on the one hand and the HashInput retrieved the GET data from the previous image.

In the tMap I perform the corresponding mapping of each field with the API field.

From there, if the ID exists (recovered in the previous subquery) it does an UPDATE if it does not do an INSERT.

If it is INSERT, it goes through a path, creates a JSON and inserts the data through BULK (<1 record)

If it is UPDATE it does the same process through bulk.

What I try is to control when I only have to insert or update a single record, which can not be a BULK but a simple INSERT or UPDATE.

The problem is that I do not know how to perform or what solution is correct, since everything tested does not work for me.

Thanks for your Help!