Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Variables and Parameters for SQL command

  1. #1
    Join Date
    Jun 2009
    Posts
    11

    Default Variables and Parameters for SQL command

    Hi. I'm using Kettle 4.0.1, and I'm sure this is a FAQ, but in spite of my reading various postings and documentation about parameters, named parameters, variables, etc. I still can't figure out what I'm doing wrong.

    My goal is this:

    1. Create a dynamic "startdate" and "enddate" that can be used in a SQL statement
    2. Using a Table Input module, execute a command like "SELECT * FROM mytable where somefield > $startdate and somefield < $enddate"

    My first try was a simple transform like this:

    Modified Java script value -> Table Input

    where the javascript code did nothing more than:
    Code:
    var start_date = "'2011-01-01 00:00:00'";
    var end_date = "'2011-12-31 23:59:59'"
    and the Table Input was:
    Code:
    SELECT * FROM mytable WHERE somefield > ? AND somefield < ?
    hoping it would take the parameters that had come out of the javascript step. But no luck.

    Next, turning to the idea of variables instead of parameters, I setup a job with two transforms - one that sets date variables, and the other I hoped would use them. i.e. the first transform doing

    Modified Java script value -> Set Variables

    where the Javascript code is the same as before and "Set Variables" creates a START_DATE and END_DATE variable (valid in the root job), and the second transform doing:

    Table Input

    where the SQL command is
    Code:
    SELECT * FROM mytable WHERE somefield > ${START_DATE} AND somefield < ${END_DATE}
    (and "Replace Variables in Script" checked).

    But no luck there either. (in fact the error log from the transform was "Unknown column '$' in where clause", as if it had NOT substituted the variables even though I had the checkbox checked.)

    So I'm stumped. Thanks to anyone who can help!

  2. #2
    Join Date
    Nov 2008
    Posts
    271

    Default

    1) Parameter:

    You cannot use MJSV to start a transformation. It needs an inbound stream to be 'activated'. In order to try, drop a generate rows on canvas with a single field and hop it directly to table input, and configure "insert data from step" box

    2) Variable:
    You need to check the "Replace variables in script", and use single quote around the variable call:
    Code:
    SELECT * FROM mytable WHERE somefield > '${START_DATE}'  AND somefield < '${END_DATE}'
    Please give it a try and let us know.
    Regards
    Andrea Torre
    twitter: @andtorg

    join the community on ##pentaho - a freenode irc channel

  3. #3
    Join Date
    Feb 2011
    Posts
    840

    Default

    I guess Andrea already pointed you in the right direction... but I'd like to say that yes, it is possible to use variables on SQL steps almost anywhere.

    I have one Execute SQL Script step that goes like this:
    Code:
    DELETE from DLODB001.DLOTB000_FINAL_${DLO_TIPO} where DT_BASE = '${ANO}-${MES}'
    And a transformation that ends on a table output, where the target table is named like this: "dlotb004_contas_outras_${DLO_TIPO}"

    Only problem with that later one is that PDI loses all it's fancy capabilities, since it can't automaticaly replace the variable, so things like the SQL commands on that SQL button just go to scrap.
    Join us on IRC! =)

    Twitter / Google+ / Timezone: BRT-BRST
    BI Server & PDI 5.4 / MS SQL 2012 / Learning CDE & CTools
    Windows 8 64-bit / Java 7 (jdk1.8.0_75)

    Quote Originally Posted by gutlez
    PLEASE NOTE: No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.

    I'm no expert.Take my comments at your own risk.

  4. #4
    Join Date
    Jun 2009
    Posts
    11

    Default

    Andrea and Joao, thank you for the help. I took the embedded single quotes out of the Javascript variable assignment and added them to the SQL command. i.e.
    Code:
    var start_date = "'2011-01-01 00:00:00'";
    var end_date = "'2011-12-31 23:59:59'";
    became
    Code:
    var start_date = '2011-01-01 00:00:00';
    var end_date = '2011-12-31 23:59:59';
    and the single quotes were added around ${START_DATE} and ${END_DATE} . I don't quite understand why it worked - it seemed like the end result would still be the same no matter where the encapsulating quotes are generated -- but at least it works now. The overall job calls two transforms now: "Set_Date_Vars" and "Query_Table".

    SET_DATE_VARS:
    Generate Rows -> Modifed Java script value -> Set Variables

    where "Generate Rows" does nothing but generate a dummy row to start the transform, MJSV sets the dates, and Set Variables creates the START_DATE and END_DATE variables.

    QUERY TABLE:
    Table Input -> (some kind of output)

    where "Table Input" has a SQL query that uses '${START_DATE}' and '${END_DATE}'

    While this works, it still feels rather clunky. Is there a better way than using multiple transforms, dummy rows, etc. that would let me accomplish this?

    1) Set a start/end date dynamically (i.e. something like START_DATE = 9 months ago, END_DATE = today)
    2) Run a SQL query to get records where submitdate >= START_DATE and submitdate <= END_DATE
    3) Parse each of those records (e.g. output them to a text file, Excel file, etc.)
    Last edited by dswhite42; 09-09-2011 at 01:37 PM.

  5. #5
    Join Date
    Nov 2008
    Posts
    271

    Default

    Quote Originally Posted by dswhite42 View Post
    While this works, it still feels rather clunky. Is there a better way than using multiple transforms, dummy rows, etc. that would let me accomplish this?
    Actually there is a number of ways. Kettle is amazingly flexible when it comes to use variables. You may find plenty of material on this forum and in some tech blogs and on the wiki page

    Furthermore there's a couple of fundamental books that greatly helps you conquer a robust idea of data-integration with kettle:


    Briefly, you can set variables:

    1. Editing kettle.properties file (Edit menù)
    2. Within a transformation, or a job, with a step/entry creatively called "Set Variables", and defining the appropriate scope.
    3. Using named parameters, imo the most flexible way. You can even map previous result (i.e. rows coming from a previous transformation) to named parameters.

    For your purpose, one rapid solution can be a Job with:
    - Transformation_1:

    • get system info step: to retrieve system date
    • calculator step: to calculate a previous date
    • set variables step: to pass previously defined field values to variables

    - Transformation_2:

    • use that variables wherever you can specify a variable, e.g. a select clause within a table input.

    HTH
    Last edited by Ato; 09-10-2011 at 06:14 AM.
    Andrea Torre
    twitter: @andtorg

    join the community on ##pentaho - a freenode irc channel

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.