Creating and loading data into the target table at the run time

[dilearner]

Hi,

Am new to pentaho DI Spoon and need some help on the following requirement.

We have a requirement where we know the source table name and the target table name. But the number of columns in the source table will change dynamically (based on certain actions from the UI layer).
So i cannot create a standard job by doing source to target column mapping.
Is there any procedure / transformation where we connect to the source system and the target system. Then run a sql statement like below
select * into target_schema.target_table from source_schema.source_table

Or can we achive this kind of requirement from one or more existing transformations? If yes can you please help me out asap.

[MattCasters]

The general consensus among the Kettle developers is to refuse to implement this feature.

[dilearner]

Thanks for the reply.

I cannot refuse this functionality. Is there any work around to do this. Can you pls help me out

[MattCasters]

Really, it's a bad idea, why would you want to do this?

[dilearner]

hmm,

Our database schema changes by adding some custom attributes from the product UI layer. So we cannot avoid the customer in using / creating that. So we need to expose those columns in our Datamart. Is there any way to achieve this?

[MattCasters]

Well, to be honest, the foolishness then started in the product UI layer, right? :-)

OK, so if you would really really would want to do this, despite the fact that everyone says it's madnes... here's how:

Load the transformation metadata using the "Auto Doc" step (Meta option) giving you a new field that contains the metadata (Java) object of the transformation. You ask that transformation for the list of SQL statements to be executed (in JavaScript for example). Then you execute all those statements (also in JavaScript) and that's it.

Have fun!

Matt

[marabu]

Let's say it can be a requirement to ressurect a relational table with a dynamic structure to get a working copy.
One can avoid this situation by introducing a meta-meta-layer to a data model, but you need the skills and the chance to do so.
There are other ways to achieve this (e.g. replication), but we do Kettle here.

So here is another way to go:

(1) Write a DROP TABLE statement to a file
(2) Append the necessary DDL statement
(3) Append the INSERT INTO statement for each data row
(4) Process the SQL-file one statement per row

To automate the whole process a job is needed.

[MattCasters]

Sooner or later I'll have to create an example. I'm just trying to postpone that time as long as possible since it opens Pandora's box of "DROP COLUMN" stupidity.

[dilearner]

Let's say it can be a requirement to ressurect a relational table with a dynamic structure to get a working copy.
One can avoid this situation by introducing a meta-meta-layer to a data model, but you need the skills and the chance to do so.
There are other ways to achieve this (e.g. replication), but we do Kettle here.

So here is another way to go:

(1) Write a DROP TABLE statement to a file
(2) Append the necessary DDL statement
(3) Append the INSERT INTO statement for each data row
(4) Process the SQL-file one statement per row

To automate the whole process a job is needed.

Thanks alot for your help. Am trying to implement the same in sql server. Will ping you if i need any more help

[MattCasters]

Here is an example, to be un-zipped in samples/transformations:

dynamic-table.zip

(updated to work on 4.3.0)