Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: execute sql by file

  1. #1
    Join Date
    Feb 2011
    Posts
    152

    Default execute sql by file

    Hi all,

    Is there a way to execute sql scripts that are stored in files? What I have is a database table which lists out the files I need to execute, but I can't seem to figure out how to execute them via file URLs. Neither 'execute row sql script' nor 'execute sql script' seem to support this.

    Should I be asking for a feature or is there some other way to do this?

  2. #2
    Join Date
    Sep 2011
    Posts
    190

    Default

    How about making a job that loops through the files and stores the SQL-query in a table (id,SQL-query) and then call 'execute row sql script'?

  3. #3
    Join Date
    Feb 2011
    Posts
    152

    Default

    I was trying to avoid putting them into the database. They are much easier to manage as files not fields. If Kettle doesn't support it natively, I may put in for a feature request.

  4. #4
    Join Date
    Feb 2008
    Posts
    107

    Default

    Hi dnrickner,

    This is possible with just the filenames stored in the DB table, by doing this :

    1. A root job with a transformation ("Get Filenames") connected to a Job entry ("Execute SQL from file") which has its "Execute for every input row" flag ticked.

    2. The transformation "Get Filenames" consists of a Table Input step to read the file names from the database in the correct order, and a "Copy rows to result" step to make the rows with the filenames flow to the next step in the root job.

    3. A transformation called "Set FILE_NAME variable" which converts the "file_name" field into a FILE_NAME variable using a "Get rows from result" transformation step followed by a "Set Variables" step.

    4. The "Execute SQL from file" job is the "Set FILE_NAME variable" transformation followed by a "SQL" scripting job step.

    I've enclosed a zip file contain this scenario. You can create the "test.sqlfiles" table I used with the following SQL.

    DROP TABLE IF EXISTS `sqlfiles`;
    CREATE TABLE `sqlfiles` (
    `file_order` int(11) NOT NULL,
    `file_name` varchar(256) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    INSERT INTO `sqlfiles` VALUES (1,'C:\\kettletest\\sqlfile1.sql'),(2,'C:\\kettletest\\sqlfile2.sql');

    Let me know if that works for you.

    If I'm over-complicating this, I'd love to hear a simpler way.

    Cheers,
    Paul
    Attached Files Attached Files

  5. #5
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    or you can do it in a single transform.
    1. use text file input to read all your files - read the whole file into 1 field.
    2. use execute sql to run the sql using the field above.

    Simple.
    I did it before.

    Problem is with this solution, is every sql file must return exactly the same metadata/field structure. do that and away you go!

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.