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

[Anonymous]

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

[Anonymous]

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

[alevy]

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)?

[Anonymous]

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

[alevy]

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?

[Anonymous]

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

[alevy]

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.

[Anonymous]

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

[alevy]

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.

[Anonymous]

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

[alevy]

!!!
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).