One Star

how to fetch primary keys from db and set it to tmap entries

Hi,
I have a requirement where I have a set of excel files, I need to either create or update the entries in db. The creation part has been done. So if the run the job again it should update those entries. I searched in talend forum and i got an option for tmysqloutput as "update or insert".
First is this approach correct?
If so for update requires the primary key from db. How to fetch the primary keys of the entries in excel sheet and assign to it.
Kindly help as soon as possible.

Thanks,
Senthil
25 REPLIES
Moderator

Re: how to fetch primary keys from db and set it to tmap entries

Hi,
You can use "Update or Insert" to achieve your goal.
The primary keys of the entries to update the table in DB in case there is a duplicated one. You can set any primary key you wanted in schema setting by manual(checkout the "Key" option) .
See my screenshot
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: how to fetch primary keys from db and set it to tmap entries

Hi,
You can use "Update or Insert" to achieve your goal.
The primary keys of the entries to update the table in DB in case there is a duplicated one. You can set any primary key you wanted in schema setting by manual(checkout the "Key" option) .
See my screenshot
Best regards
Sabrina

Hi Sabrina,
Thanks for the reply. I tried what you said but its creating new entry for me. I think if the primary key is empty it creates an entry else it updates. Is my assumption correct?
If so how assign the existing primary key from db to the entries in tmap of excel sheet.

Thanks,
Senthil
Moderator

Re: how to fetch primary keys from db and set it to tmap entries

Hi,
Do you mean you have a primary keys in DB? Is it listed in schema setting? Could you checkout the consistent schema for the existing primary key in tMap?
How did you set your DB output? Could you please upload your schema setting and DB column screenshots into forum so that I can know your situation better.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: how to fetch primary keys from db and set it to tmap entries

Hi,
Do you mean you have a primary keys in DB? Is it listed in schema setting? Could you checkout the consistent schema for the existing primary key in tMap?
How did you set your DB output? Could you please upload your schema setting and DB column screenshots into forum so that I can know your situation better.
Best regards
Sabrina

Hi,
Yes i have the entries in db and thr is a primary key for it. I have attached the tmap image where primary key value will be empty as the mysql primary key is autoincrement. It works fine for creation. When i run it again the entries should be updated but it gets inserted again instead of updating. If put the primary key value from db (manual insert in tmap) the entry is updated. I dont know how to assign the primary key to all entries.

Thanks,
Senthil
Moderator

Re: how to fetch primary keys from db and set it to tmap entries

Hi,
I have seen your tMap screenshot and found that there is no "rest_ Id" in your input source. The "rest_Id" is your existed primary key in your DB?
The input source is inconsistent with DB table column?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: how to fetch primary keys from db and set it to tmap entries

Hi,
I have seen your tMap screenshot and found that there is no "rest_ Id" in your input source. The "rest_Id" is your existed primary key in your DB?
The input source is inconsistent with DB table column?
Best regards
Sabrina

Hi,
Yes the input (excel sheet doesnt have the primary key). Whether I need to fetch it from db and set it? If so how to do it? Else is thr anyother way to update the entry without the primary key Kindly help me.

Thanks,
Senthil
Seven Stars

Re: how to fetch primary keys from db and set it to tmap entries

I don't understand: if you don't have the primary key in the file and you want to update the DB with the data in the file, how can you know which record in the DB to update? You don't have any information about the data already there!
One Star

Re: how to fetch primary keys from db and set it to tmap entries

I don't understand: if you don't have the primary key in the file and you want to update the DB with the data in the file, how can you know which record in the DB to update? You don't have any information about the data already there!

Hi Alevy,
Yes you are correct thats my situation. Is thr any possibility to fetch the primary key from db based on where condition and set it to the entries and update it. If this is possible then i can update the entry with the where condition. Kindly help me.
Thanks,
Senthil
Seven Stars

Re: how to fetch primary keys from db and set it to tmap entries

It's a data problem, not a job design problem. If you don't have data in the file that you can match to data in the DB to identify the record then there's no way to do an update.
One Star

Re: how to fetch primary keys from db and set it to tmap entries

It's a data problem, not a job design problem. If you don't have data in the file that you can match to data in the DB to identify the record then there's no way to do an update.

Hi,
The file has columns other than primary key because the primary key is autoincrement and mysql creates on its own. Now if run the job again few entries may already exist or might not, so based on it i need to update or create. For update i need the primary key.

Thanks,
Senthil
Seven Stars

Re: how to fetch primary keys from db and set it to tmap entries

Yes, you've just repeated what you said before so I'm just going to repeat what I said before: the only way to update the DB is if the file has data that you can use to find the record in the DB that you want to update i.e. the file data matches the DB data.
So explain what your file data schema is, what your DB data schema is, which fields in the file will be used to update the DB and which fields will match data already in the DB (if it's not a new record)?
One Star

Re: how to fetch primary keys from db and set it to tmap entries

Yes, you've just repeated what you said before so I'm just going to repeat what I said before: the only way to update the DB is if the file has data that you can use to find the record in the DB that you want to update i.e. the file data matches the DB data.
So explain what your file data schema is, what your DB data schema is, which fields in the file will be used to update the DB and which fields will match data already in the DB (if it's not a new record)?

Hi,
The example is as below. The files contains

Name Age Salary
John 25 1000
Tom 30 2000
David 35 3000

When I run the job the db entry would be
Id Name Age Salary
1 John 25 1000
2 Tom 30 2000
3 David 35 3000

Now if I again run the job with some other set of files, those files might contain the same data plus some new data, as below but not the Id
Name Age Salary
John 25 1000
Mike 23 4000
Tom 30 2000
Sara 27 2500
David 35 3500
When I run the job with this file the already existing entry should be updated( For eg: David's Salary from 3000 to 3500) and should insert Tom and Sara entry into the db.
How it can be done.

Thanks,
Senthil
Seven Stars

Re: how to fetch primary keys from db and set it to tmap entries

So is the Salary the only data that should be updated? Can you say for sure that the Names (or Name and Age combinations) are unique and won't need to be changed?
One Star

Re: how to fetch primary keys from db and set it to tmap entries

So is the Salary the only data that should be updated? Can you say for sure that the Names (or Name and Age combinations) are unique and won't need to be changed?

Consider the data from excel as
Name Age Course Amount
Tom 24 Maths 100
Dick 24 Maths 200
Harry 24 Bio 300
Now these three entries will be created in table as
NAME
ID Name Age
1 Tom 24
2 Dick 24
3 Harry 24

ID Course Amount NAME_ID
1 Maths 100 1
2 Maths 200 2
3 Bio 300 3

These are entries after the job is finished.
Now if I change the excel data as below

Name Age Course Amount
Tom 25 Maths 200
Tom 25 Phy 100
Dick 24 Eng 200
Harry 24 Bio 300
Sara 25 Che 250
Here the Tom age in parent table should be updated as 25 and Maths amount in child table for Tom should be updated as 200 and create a new entry for Phy in child table with foreign key as 1 for Tom.
Create new entry for Dick in child table for Eng.
Create new entry for Sara in parent table and child table.

Now the table should be as
NAME
ID Name Age
1 Tom 25
2 Dick 24
3 Harry 24
4 Sara 25

ID Course Amount NAME_ID
1 Maths 200 1
2 Maths 200 2
3 Bio 300 3
4 Phy 100 1
5 Che 250 4
Thanks,
Senthil
Seven Stars

Re: how to fetch primary keys from db and set it to tmap entries

OK, so we can take Name and Course as fixed values i.e. use those to look for the record in the table.
But what you want is complicated because of the need to use the parent's ID as a foreign-key when you do an insert but the parent might already exist or you might only be creating it now. I think the best way is to do the parent and child in separate steps.
First, read your file with just Name and Age in the schema, making Name the key field. Pass the flow to tUniqRow to eliminate the duplicates and then to your parent DB table. In the DB output component, set the "Action on data" to "Update or Insert". Now existing Names in the DB will be updated with the new Age and new Names will be inserted (with Age) and the ID generated by the DB.
As the second step, read your file again as the main flow to tMap with a lookup flow from the parent table, joined on Name. In the output, make NAME_ID and Course the key fields with Amount as the other field. In the DB output component, set the "Action on data" to "Update or Insert". Now existing NAME_ID and Course combinations in the DB will be updated with the new Amount and new NAME_ID and Course combinations will be inserted (with Amount) and the ID generated by the DB.
One Star

Re: how to fetch primary keys from db and set it to tmap entries

OK, so we can take Name and Course as fixed values i.e. use those to look for the record in the table.
But what you want is complicated because of the need to use the parent's ID as a foreign-key when you do an insert but the parent might already exist or you might only be creating it now. I think the best way is to do the parent and child in separate steps.
First, read your file with just Name and Age in the schema, making Name the key field. Pass the flow to tUniqRow to eliminate the duplicates and then to your parent DB table. In the DB output component, set the "Action on data" to "Update or Insert". Now existing Names in the DB will be updated with the new Age and new Names will be inserted (with Age) and the ID generated by the DB.
As the second step, read your file again as the main flow to tMap with a lookup flow from the parent table, joined on Name. In the output, make NAME_ID and Course the key fields with Amount as the other field. In the DB output component, set the "Action on data" to "Update or Insert". Now existing NAME_ID and Course combinations in the DB will be updated with the new Amount and new NAME_ID and Course combinations will be inserted (with Amount) and the ID generated by the DB.

Actually I done most of the part using tdenormalize, normalize and tmap. The entries are creating and update is done using the key field of the tmysqloutput. Everything works fine but i face issue due to this update that is when parent is already thr and a new child entry is read from the file, the foreign key relation returns 0 instead of parent id. Is thr any other way to fix in the same job
Seven Stars

Re: how to fetch primary keys from db and set it to tmap entries

Without seeing your job, I can't determine what the problem is. But I don't think it's possible to do everything you want in one.
One Star

Re: how to fetch primary keys from db and set it to tmap entries

Without seeing your job, I can't determine what the problem is. But I don't think it's possible to do everything you want in one.

The screenshot of the job is as below

The master table is rest_tab and its child table are addr_tab, menu_tab and item_tab. The child table has rest_tab_id as foreign key. menu_item_tab is a composite table has menu and item ids. This job works for creation part and updating part with the same value because the entry already exists in db. The update key in the table is given as the names. If a new child is been inserted then the foreign key relation returns 0.
Is thr a way to update it? Kindly help me.

Thanks,
Senthil
Seven Stars

Re: how to fetch primary keys from db and set it to tmap entries

!!!
You can only get the ID of an existing parent by querying the database. So you can either have a lookup flow from tMysqlInput (rest_tab) to tMap_3 or embed a tMysqlRow and tParseRecordSet before tNormalize_1.
The first is easier and faster but is taking you towards my previous suggestion anyway (which I think was a lot simpler).
One Star

Re: how to fetch primary keys from db and set it to tmap entries

!!!
You can only get the ID of an existing parent by querying the database. So you can either have a lookup flow from tMysqlInput (rest_tab) to tMap_3 or embed a tMysqlRow and tParseRecordSet before tNormalize_1.
The first is easier and faster but is taking you towards my previous suggestion anyway (which I think was a lot simpler).

I'm using tFilelist in my job so i need to use three different tfilelist in what you suggested and at anytime the same file will be processed or different files. Do you have any idea about it.

Can you post a screenshot of the job what you said. It would be easy to get it for me.

Thanks,
Senthil
Seven Stars

Re: how to fetch primary keys from db and set it to tmap entries

Why would you need three different tFileList?
If your concern is to ensure the lookup is reloaded for each file, all you need to do is have the iterate connection from tFileList go to an empty tJava and then use OnComponentOK from that to tFileInputExcel.
One Star

Re: how to fetch primary keys from db and set it to tmap entries

OK, so we can take Name and Course as fixed values i.e. use those to look for the record in the table.
But what you want is complicated because of the need to use the parent's ID as a foreign-key when you do an insert but the parent might already exist or you might only be creating it now. I think the best way is to do the parent and child in separate steps.
First, read your file with just Name and Age in the schema, making Name the key field. Pass the flow to tUniqRow to eliminate the duplicates and then to your parent DB table. In the DB output component, set the "Action on data" to "Update or Insert". Now existing Names in the DB will be updated with the new Age and new Names will be inserted (with Age) and the ID generated by the DB.
As the second step, read your file again as the main flow to tMap with a lookup flow from the parent table, joined on Name. In the output, make NAME_ID and Course the key fields with Amount as the other field. In the DB output component, set the "Action on data" to "Update or Insert". Now existing NAME_ID and Course combinations in the DB will be updated with the new Amount and new NAME_ID and Course combinations will be inserted (with Amount) and the ID generated by the DB.

With this job (as you said) the same problem will exist (if we insert a new child entry, the parent id returns as 0) because we are updating by name and not primary key, so the primary key will 0.
Seven Stars

Re: how to fetch primary keys from db and set it to tmap entries

It should work because you would be doing the load in two stages: the first adding any new parents and the second reading all parents (new and old) to use in the children. Note that I suggest two sub-jobs i.e. read the file twice.
One Star

Re: how to fetch primary keys from db and set it to tmap entries

OK, so we can take Name and Course as fixed values i.e. use those to look for the record in the table.
But what you want is complicated because of the need to use the parent's ID as a foreign-key when you do an insert but the parent might already exist or you might only be creating it now. I think the best way is to do the parent and child in separate steps.
First, read your file with just Name and Age in the schema, making Name the key field. Pass the flow to tUniqRow to eliminate the duplicates and then to your parent DB table. In the DB output component, set the "Action on data" to "Update or Insert". Now existing Names in the DB will be updated with the new Age and new Names will be inserted (with Age) and the ID generated by the DB.
As the second step, read your file again as the main flow to tMap with a lookup flow from the parent table, joined on Name. In the output, make NAME_ID and Course the key fields with Amount as the other field. In the DB output component, set the "Action on data" to "Update or Insert". Now existing NAME_ID and Course combinations in the DB will be updated with the new Amount and new NAME_ID and Course combinations will be inserted (with Amount) and the ID generated by the DB.

Only if select as auto commit in db level of talend, i can retrieve the parents records while creating child. Is thr any other way to retrieve without auto commit option. Will this cause any problem if a child record has not been created but the parent still exists.
Seven Stars

Re: how to fetch primary keys from db and set it to tmap entries

I don't see why you should need to set auto commit. As long as the tMysqlOuput in the first subjob and the tMysqlInput in the second subjob share the same tMysqlConnection, then the tMysqlInput should read back all the existing records and all the new uncommitted records