Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: Perform an auto CREATE TABLE to store the output of a transformation

  1. #1
    Join Date
    Mar 2012
    Posts
    14

    Default Perform an auto CREATE TABLE to store the output of a transformation

    Hi everyone. I created a transformation in Kettle Spoon and now I want to output the result (all generated rows) in my Oracle database. I know I can do it with the Table Output step, but I'm searching for something that auto-creates my output table with all necessary fields. I do not want to manually adjust the DB table every time I add, for example, a new column in my Spoon-generated data.

    Is there a way to do that? Basically, I want to completely DELETE that table and auto-recreate it every time the transformation is run.

    Thank you,
    Lorenzo.

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    The question keeps coming back on the forum and I keep thinking it's a bad idea to do it.

  3. #3
    Join Date
    Jun 2009
    Posts
    2

    Default

    Firstly, thanks to the Pentaho developers for their very fine work.

    The reason this keeps coming back is that it is a common thing to want to do.

    Many of us are often creating/deleting tables in "scratchpad" databases that we completely control and which are not under source code or version control of any kind. We are wanting to take source data (such as a set of CSV files) and create a set of database tables so we can do further investigation/analysis on the data. The most convenient way would be to have a way to create tables in the destination datasource given a set of field definitions coming as output from a step. As you no doubt know there are many ways this could be accomplished.

    I used to use Microsoft Access (yikes!) to do this kind of thing, but it is a testament to the Pentaho development team that I am able to use a handy open source database server and Spoon instead. One thing that I miss is being able to quickly and conveniently turn a set of CSV files into a set of tables. I know that Spoon gives me a button to generate the SQL - but this part of the process is somewhat tedious and unnecessary for this particular use case. An option in the Table Output step could work - or another step type (Create Table) could generate and run DDL to create the table automatically. One concern would be if the table existed already. A way to deal with this is another checkbox that would allow the table to be dropped before creation. This would mirror the "Truncate table" functionality in Table Output.

    I understand the concerns re DDL being under version control in large production databases, but I don't think adding this functionality would create mayhem for all users, whereas having such functionality would make it much easier for many of us. Imagine being able to create a ktr that would take a directory containing a set of CSV files and turn them into database tables without having to go through the process of creating and running the SQL manually from Spoon for every table.

    Jon Hilton

  4. #4
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Jon, thank you for being so honest about using Access. Not a lot of people would be willing to admit to it.

    OK, I didn't say it couldn't be done. So here is how you do it:

    1) Load the transformation metadata (from file or repository) using the "Auto Doc" step. Use the metadata option. This will give you a meta field.
    2) Pass the meta field to a Script Values step where you use meta.getSQLStatementsString() to get the DDL.
    3) pass the SQL to an Execute row SQL Script step which creates or modifies the table.

    Here is an export from a repository example (can be used to ktr files as well) :

    AutoCreate database table.ktr

    HTH,
    Matt

  5. #5
    Join Date
    Jun 2009
    Posts
    2

    Default

    Thanks, Matt, for the prompt response.

    Quote Originally Posted by MattCasters View Post
    Jon, thank you for being so honest about using Access. Not a lot of people would be willing to admit to it.
    What can I say? It was there and it worked (sort of)

    1) Load the transformation metadata (from file or repository) using the "Auto Doc" step. Use the metadata option. This will give you a meta field.
    I did find previous posts outlining this approach. I could not open the ktr you sent for some reason (no error message - just didn't open) but I was able to inspect the XML and get some idea of the content. Unless I am misinterpreting, this would iterate over a directory containing previously defined transformations, and requires the transformations for each of the source files to be previously defined. While this is not exactly what I had in mind, I will give it a try, and it will help.

    Ideally I would like to be able to generate and use the metadata dynamically within a single transformation by iterating through a list of (say) CSV files in a directory, accepting the defaults for field types and lengths after analysis of a defined number of rows, creating the tables (using the filename of the CSV file as table name) and transforming the data. This is such a common thing for me to do that I would like to be able to create a transformation to do it in one click - but I am probably asking for too much and being lazy.

    At least using your approach I can save the database table create step when creating the transformations for the files. Thanks very much for your help.

    Jon Hilton

  6. #6
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    Hi.
    You can use Get File names to collect list of files and pass that list to following steps.

    Mick

  7. #7
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Sorry about that, it was a repository export from 4.4.0. However, 4.3.0 isn't totally forward compatible with 4.4.0.
    Try this one:

    Autocreate table.ktr

  8. #8
    Join Date
    May 2013
    Posts
    6

    Default Drop\Create tables runtime without predefined transformations

    Quote Originally Posted by projnet View Post
    Thanks, Matt, for the prompt response.



    What can I say? It was there and it worked (sort of)



    I did find previous posts outlining this approach. I could not open the ktr you sent for some reason (no error message - just didn't open) but I was able to inspect the XML and get some idea of the content. Unless I am misinterpreting, this would iterate over a directory containing previously defined transformations, and requires the transformations for each of the source files to be previously defined. While this is not exactly what I had in mind, I will give it a try, and it will help.

    Ideally I would like to be able to generate and use the metadata dynamically within a single transformation by iterating through a list of (say) CSV files in a directory, accepting the defaults for field types and lengths after analysis of a defined number of rows, creating the tables (using the filename of the CSV file as table name) and transforming the data. This is such a common thing for me to do that I would like to be able to create a transformation to do it in one click - but I am probably asking for too much and being lazy.

    At least using your approach I can save the database table create step when creating the transformations for the files. Thanks very much for your help.

    Jon Hilton
    Hi Jon\Matt:
    Is there a way to drop\recreate the table at runtime using pentaho(not reading meta from existing transformations) but reading from the source itself?

    Thanks
    Michael

  9. #9
    Join Date
    Jan 2014
    Posts
    1

    Default Execute Row sql script is giving error as INVALID character

    I am getting an error while executing sample transformation given by you.
    get sql step is working fine and giving correct sql statment. transformation is failing at EXECUTE ROW SQL SCRIPT step and giving invalid character as error while executing sql statement

    Help me with the solution.

    Thank you

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.