Dynamic View as Target Table

[tnewbie]

Hello All,

I want to use a View as the underlying table of an Oracle SCD2 component (tDBSCD) and the view is created on the fly...view definition is changing for each incoming record however my Talend job is considering the view definition that was at the start of the job and is not considering the view definition changes that are happening on the fly...have anyone worked on a similar situation. To explain more:

1) I had a view v_mysource --> create or replace view v_mysource as select * from employee where emp_id=123;

2) I have a file that has 3 employee records say emp_id=111, 222, 333

I want to generate my view definition as create or replace view v_mysource as select * from employee where emp_id=111 for the first incoming record, emp_id=222 for second incoming record etc

I am able to generate the view on the fly (i can see the view definition from sqlplus when the job is running) 

but Talend all through the job is treating the view as create or replace view v_mysource as select * from employee where emp_id=123;

Can you think of a way to use the changed view definition. I am attaching my job flow for your better understanding.

In this example if there are 10 source records my view definition changes 10 times but the SCD component is considering the view definition as it was at the start of the job... any suggestions please

[tnewbie]

Hello Experts,

Please share your thoughts on my below request...

[Anonymous · Accepted solution]

You need to iterate record and create view dynamically based on current record before it is used. eg.
.....tXMLMap--tFlowToiterte--tDBRow--oncomponentok--tFixedFlowInput--main--tMap---tOracleSCD.

on tFixedFlowInput, generate the current record.

Can you try and let me know it fixes your issue?

Regards
Shong

[tnewbie]

Thanks Shong...

Unfortunately we can not use that approach because there are too many columns in each table and close to 34 tables, i am scared we will end up with tooo many context variables and too much of custom coding.

[tnewbie]

Shong,

I have data coming in, in real time from Message queues and my biggest problem here is, i have 34 tables on which i need to do an SCD2 and 15 of these tables on an average will have close to 25 million records(record lengths are decently big too), so when i try to run my talend job, it tries to cache the target table before it can kick start the process and because of the volume Talend is not able to come out of the caching task even after an hour. So i thought of creating a view on the fly where view can fetch only the unique id related data and thus the volume will be very minimal and we can get rid of this caching approach, unfortunately Talend takes the view definition and view data as it was at the start of the job and the intermittent changes happening in the view definition doesn't seem to reflect during the process.

From the tool perspective can you help me understand if there is a way to stop caching SCD table at the start of the job.

[Anonymous]

This is why you need to iterate over the incoming data. When you iterate, it reloads the look up on the tMap. So you were to follow Shong's example, it should work for you. I'm not sure why you are concerned about Context Variables, since I don't believe you would need any.

[tnewbie]

Thanks rhall...

May be i am wrong...to the best i know, i can generate record using FixedFlowInput in 3 three ways - 1. values hardcoded, 2. using context variables 3. using global variables like ((String)globalMap.get("row1.column_value")) 

I am new to Talend, so please help me if there any other way to generate a record.

[Anonymous]

The tFlowToIterate creates globalMap key/value pairs for each column in a row. So when you use the tFlowToIterate it will create your row in the globalMap for the lifetime of the iteration. Your understanding of the key naming convention of the tFlowToIterate in your post above is absolutely correct.