Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Table Input from - Execute SQL from File

  1. #1
    Join Date
    Nov 2015
    Posts
    4

    Default Table Input from - Execute SQL from File

    I have a series of SQL files I would like to run and insert into a MySQL data base.

    I have the SQL Script runner set up to run from file - but I cannot figure out how to create a table using the results from the SQL Script.
    (The SQL Script I'm running is a Select Statement)

    I am on a Windows machine so I cannot use MySQL BulkLoader.

    Is it possible to use a Table Input (or Table Output) to run SQL from File (as is possible in the SQL Script tool)? - If it is not how would I go about doing this?

    Please Advise.

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    The only clean way Table-Input accepts a SELECT statement at runtime is via variable substitution.
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Oct 2014
    Posts
    26

    Default

    You can be on two ways:
    You can create a transformation that receives the SQL statements as a parameter. On table input, mark the option 'Replace variables in script?' and on SQL field just writes the variable. for example "${SQL_READ}".
    The problem on this approach is that PDI does not know too much about the data input, so it dificults writing the transformation. You will have to create a default value from this parameter to give hints to PDI.

    The second alternative is to give a try on Dynamic SQL row:
    http://wiki.pentaho.com/display/EAI/Dynamic+SQL+row

  4. #4
    Join Date
    Nov 2015
    Posts
    4

    Default

    Thanks marabu and orair,

    But the issue I am running into with the first method (Writing the SQL Into a variable) is that the Text File input is taking each row of the SQL query and making it a unique row as opposed to keeping the output all in one row.
    Ex:
    SELECT
    C1
    ,C2
    FROM TABLE1

    This is read in as:
    row1: SELECT
    row2: C1
    row3: ,C2
    row4: TABLE1

    The form needed is
    row1: SELECT C1 ,C2 FROM TABLE1


    The Dynamic SQL Row would have the same problem - I would need to use Spoon (as opposed a Database script) to write in the SQL text to a field in the data base. This is a requirement of the project.

    Thanks Again!

  5. #5
    Join Date
    Nov 2015
    Posts
    4

    Default

    Update and Note:

    I've been able to read the file(s) in and use the concatenate / group by to merge the data into one row in the field. This way Pentaho assigns the data input.
    I'm presently at the step of assigning the query text as a variable and passing it to the Dynamic SQL Row query - Will do it this way and try the simple Table output with variable substitution.

  6. #6
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    I would have used Load-File-Content-In-Memory instead of Text-File-Input.
    Sorry I forgot to mention this.
    So long, and thanks for all the fish.

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.