Mapping from one to multiple tables while including generated ids
Ok, I have what I believe is a very basic requirement,
I am migrating from a flat table to a set of related tables layout where there is a main entry and a number of sub-entries keyed off main entry PK
So I read from old table , map the fields, write main entry, get generated key, but now I cannot access any of the original data except what was written to main table - which is useless as I need to write data.
Attached is the image of what I am trying to do, I need to add a line from tMap1 to tJoin1 but I am not allowed to.
What obvious thing am I missing?
-HH
For the lack of replies, I assume it is an actual limitation. Strange - seems like a basic thing to do to covert one row into parent/child set.
I hacked my way around the limitation by writing the old ID into an unused column in the parent table (to create a newid-oldid map) and then re-reading the original data and joining it with the new data to recover the new id. Really hacky thing to do - seems like there should be a more direct way of doing this. - like ability to pass data around the output component or at least ability to pass data into an output component that is NOT being output (as the data is passed by to next component, it would be usable there along with LastInsertId)
-HH
Hello I sorry to say that there are some design error in your job. In Talend, you are not allowed to create a cycle flow in a job, see 1468. About how to use tMysqlLastInsertId component, see 4895.
I've read those links and perhaps I am not understanding some fundamental way Talend works - but I am not seeing the cyclical dependency here. I am getting data, I write some of it, get new id, and write the id with the rest of the data. Where is the cycle other that the row by row reading of the data - or is this what Talend can't do?
Lets take a step back - here is a basic scenario:
I have a table:
OLDDATA
| OLDID | NAME | PROP1 | PROP2 |
and I need to convert it to two tables:
ITEMS (parent)
| ITEMID | NAME |
and
PROPS (child)
| PROPID | ITEMID | PROP_NAME | PROP_VALUE |
Pretty standard stuff so far. Important to note that NAME is not unique and I cannot reuse OLDID as ITEMID.
If I were programming this, I would read a row from OLDTABLE, write name to ITEMS, get lastInsertId, and write PROPS rows. No cyclical dependencies here. No need to read the same data multiple times and pray it comes out exactly the same and in same order.
So, what am I missing here? How can this be done in Talend without resorting to reading data multiple times and trying to match it up somehow?
Also, I note that in the lastInsertId link you posted, the suggestion is to write all the rows to ITEMS (in my case) table and then "onSubjobOk" execute tMysqlLastInsertId inside a subjob. As I understand it, onSubjobOk triggers when entire subjob is completed - so when ALL the rows were written to ITEMS table. So, if I am processing a few million rows, would tMysqlLastInsertId return a few million ids? Or does it return only the last insert id? In the latter case this is useless, but if it is capable of returning ALL the inserted IDs from previous job, how do I match those Ids to the original data??
Perhaps I am not understanding the whole "flow" concept - is there more documentation of how exactly is data passed from a component to component?
Thanks.
-HH
In the latter case this is useless, but if it is capable of returning ALL the inserted IDs from previous job, how do I match those Ids to the original data??
It only returns the last inserted id, not all the new inserted ids. About your request, can you take an exmaple with some data? What's the data looks like in OLDDATA table and what are your expected result?
Best regards
shong
In the latter case this is useless, but if it is capable of returning ALL the inserted IDs from previous job, how do I match those Ids to the original data??
It only returns the last inserted id, not all the new inserted ids. About your request, can you take an exmaple with some data? What's the data looks like in OLDDATA table and what are your expected result? Best regards shong
Ok, sorry it took a while, but here is an example:
Source is in table "SOURCE"
Target is in table "TARGET_ITEMS" and "TARGET_PROPS"
Each row in SOURCE get inserted into ITEMS and then into PROPS.
I can't see a way to do this without adding columns to ITEMS table AND reading the data multiple times - neither behavior is desirable.
Here is Source Data :
mysql> select * from SOURCE ORDER id; +----+--------+-----------+-----------+-----------+-------------------------------------------------------------+ | id | name | prop1 | prop2 | prop3 | description | +----+--------+-----------+-----------+-----------+-------------------------------------------------------------+ | 1 | Item 1 | i1_p1_val | i1_p2_val | i1_p3_val | | | 2 | Item 2 | i2_p1_val | i2_p2_val | i2_p3_val | | | 3 | Item 3 | i3_p1_val | i3_p2_val | i3_p3_val | | | 4 | Item 3 | i4_p1_val | i2_p2_val | i1_p3_val | This Item has same name and some prop values as other items | +----+--------+-----------+-----------+-----------+-------------------------------------------------------------+
Target Data before import:
mysql> select * from TARGET_ITEMS ORDER BY item_id; +---------+---------------------+ | item_id | item | +---------+---------------------+ | 1 | Existing new Item 1 | | 2 | Existing new item 2 | +---------+---------------------+ mysql> select * from TARGET_PROPS ORDER BY prop_id; +---------+---------+-----------+---------------------------------------+ | prop_id | item_id | prop_name | prop_value | +---------+---------+-----------+---------------------------------------+ | 1 | 1 | prop3 | | | 2 | 2 | prop2 | Prop2 Value - Prop1 and 3 are not set | | 3 | 2 | Source | newapp | | 4 | 1 | prop1 | Prop1 value | | 5 | 1 | Source | newapp | +---------+---------+-----------+---------------------------------------+
Hello guy I have sent a demo job to you, if you don't receive a email from me, please send me a email.
Thanks for the demo job. I examined it and best I can figure, the cornerstone of it is getting the latest Id from the table each time and adding 1 to it - this seems like a dangerous thing to do as not all databases give out sequential id's (for example master-master replication setup in mysql) and there may be other users in the DB.
But while examining your job I found a way to do something I was trying to from the beginning - pass something into mysqlOutput that will NOT be written into the table. Attached is the image of my new job.
In here, I am using tMap_1 to change "name" to "item" column, adding an item_id column blank, and passing all other source columns to TARGET_ITEM table output. (see image 2)
In the TARGET ITEM output I do unset the "extend insert" (so that LastInsertId works) but I am also (and this is the key to this) check "use field options" in Advanced Settings and uncheck the Update and Insert for all columns except for "item_id" and "item" (see image 3)
As a result, output of tMysqlLastInsertId contains all of the source row plus the new item_id generated by DB - I can then map it out to separate inserts for each property (see image 4)
This seems to work - at least on the small scale I have tried it on so far - is there any issues with this kind of an approach?
Thanks again.
-HH
Update: Re-uploaded images
Hi all,
i want to ask question to all of team in the forum about how to update two different table in job.Because i create script to update some field in the two table. So how i want to use toracle output?
