Hi,
I am trying to see as to whats the best way to generate primary keys while uploading into database tables.
Also if data needs to be stored in mutiple tables with foreign key constraints, whats the best way to pass aroung the primary key of parent while uploading the child record.
If I use the DatabaseOutputTableComponent, It wouldnt return any outputs on successfully uploaded records.
Which means, I wouldnt be able to pass around the primary key (assuming i create it at the database level using a trigger for instance)
Thanks.
I am trying to see as to whats the best way to generate primary keys while uploading into database tables.
Also if data needs to be stored in mutiple tables with foreign key constraints, whats the best way to pass aroung the primary key of parent while uploading the child record.
If I use the DatabaseOutputTableComponent, It wouldnt return any outputs on successfully uploaded records.
Which means, I wouldnt be able to pass around the primary key (assuming i create it at the database level using a trigger for instance)
Thanks.
-
Following are the options I had tried.
Using DBInput Component, read the Database high kry from a lookup table and use it. But the problem here is that , there is no component which can take multiple inputs to gather the key and the data. I tried SIMPLE_GATHER, but that gives an error (I have posted the error as another thread)
Reformat Component Can take only one input. But I can make Reformat Component itself lookup the high key.
But the problem here is that, Reformat Component has only one output port, which I can use this port to load Single Table data. But I can not continue down loading child tables in the same graph.
Data Intersect Component has mutiple output ports, but each output port, gives out different data and also needs a common key to merge among input ports, so that cant be used here. The same is with Mere or Join Components - need a common merge key.
My Graph Flow is is like this:
Filter and extract data from input file
Read high key from db lookup table
Use High Key and data to insert parent table
Proceed to next child table
Use the previously generated parent primary key as the foreign key for the child table
Read High Key for the child from the db lookup table
Combine the above to populate the child table.
And then proceed to the next child table, if any.
Is it possible to do the above in a single graph?
If not i think i will have to keep each table flow in a new graph. -
You may use functionality of DBOutputTable component - autoGeneratedColumns ( http://wiki.cloveretl.org/doku.php?id=c ... utputtable ). It allows you to query DB after insert and get values of those fields/columns which were automatically populated by the DB. Whether this will work with your DB depends on the JDBC driver for that particular DB. It for sure works with MySQL and Oracle, PostgreSQL does not support it.
The link above will lead you to the component doc page where are also some examples of configuring the component to output such info.
Please sign in to leave a comment.
Comments 3