Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Dynamically generate DDL from text file input fields for table output

  1. #1
    Join Date
    May 2017
    Posts
    2

    Question Dynamically generate DDL from text file input fields for table output

    Consider the following three files:

    1.csv [contains 3 fields: a, b, c]
    2.csv [contains 4 fields: d, e, f, g]
    3.csv [contains 2 fields: h, i]

    My assignment is to load all three files to their respective table output. So

    File "1.csv" ->loads-> Table "1_csv"
    "Get File Names"
    File "2.csv" ->loads-> Table "2_csv"...

    How do I generate a DDL statement that creates the target table for each file? I am looking at the metadata injection step but I am not sure this fits my needs.

    Any advice?

    ---
    Pentaho Data Integration 7.0
    Postgres RDS
    Last edited by kristo; 05-09-2017 at 06:27 PM. Reason: Fixed typo in post title

  2. #2
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Do the tables already exist, or are you going to need to create it?
    If you need to create them, how do you know what the column types are?
    Are the CSV files consistent and you're loading them into matching existing tables?

  3. #3
    Join Date
    May 2017
    Posts
    2

    Default

    Quote Originally Posted by gutlez View Post
    Do the tables already exist, or are you going to need to create it?
    I need to create the tables from scratch every time
    If you need to create them, how do you know what the column types are?
    I don't. I assume all columns will be character data.
    Are the CSV files consistent and you're loading them into matching existing tables?
    That's the problem. They're not consistent. One file may have 3 columns, the new file may have 30 columns....so I would need to create corresponding tables.

  4. #4
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Seems a terrible workflow design plan.
    But yes, you can read the first line of the CSV to get the headers (column names), and feed that to a Meta-Data Injector that creates the tables, and then another to load the CSV into that table.

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.