Hitachi Vantara Pentaho Community Forums
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Newbie: Get System Data / Set Variable

  1. #1
    Join Date
    Mar 2009
    Posts
    7

    Default Newbie: Get System Data / Set Variable

    OK I've tried to figure this out for a couple of hours; I'm sure I'm missing something simple but I can't find a thread that shows an example either...

    So I have a Job, in the Job I have Start and two transformations. The first Transformation uses a Get System Info object to try to pull the two command-line arguments. It does a check if they are null, if null it does an Abort. If not, it does a Set Variables.

    I am running the job in Spoon, in the Execute a Job window I set the two arguments 01 and 02 to numbers: 20 and 27. The job metrics says:
    START: Start of job execution: Start
    START: Job execution finished: Success
    Get Company Ids: Start of job execution followed unconditional link
    Get Comapny Ids: Job execution finished: Failure
    Job: Base Data Copy: Job execution finished Failure: finished

    What am I doing wrong? What have a missed?

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    And now for the unaltered log contents

    Regards,
    Sven

  3. #3
    Join Date
    Mar 2009
    Posts
    7

    Default

    Sure:
    2009/03/10 15:24:44 - SharedObjects - Reading the shared objects file [file:///Users/Ross/.kettle/shared.xml]
    2009/03/10 15:24:44 - Base Data Copy - Loading 0 notes
    2009/03/10 15:24:44 - Base Data Copy - Loading 4 job entries
    2009/03/10 15:24:44 - Base Data Copy - Loading 3 job hops
    2009/03/10 15:24:44 - Spoon - Starting job...
    2009/03/10 15:24:44 - Thread[Base Data Copy (Base Data Copy (Thread-44)),5,main] - exec(0, 0, START.0)
    2009/03/10 15:24:44 - Base Data Copy - Starting entry [Get Company Ids]
    2009/03/10 15:24:44 - Thread[Base Data Copy (Base Data Copy (Thread-44)),5,main] - exec(1, 0, Get Company Ids.0)
    2009/03/10 15:24:44 - Get Company Ids - Opening filename : [null]
    2009/03/10 15:24:44 - Get Company Ids - Opening transformation: [null] in directory [/]
    2009/03/10 15:24:44 - Get Company Ids - Loading transformation from repository [Get Company Ids] in directory [/]
    2009/03/10 15:24:44 - Get Company Ids - Looking for the transformation [Get Company Ids] in directory [/]
    2009/03/10 15:24:44 - Get Company Ids - Loading transformation [Get Company Ids] from repository...
    2009/03/10 15:24:44 - SharedObjects - Reading the shared objects file [file:///Users/Ross/.kettle/shared.xml]
    2009/03/10 15:24:44 - Get Company Ids - The used shared objects file 'null' doesn't contain any shared objects (or doesn't exist).
    2009/03/10 15:24:45 - Get Company Ids - Loading step with ID: 9
    2009/03/10 15:24:45 - Get Company Ids - Loading step with ID: 8
    2009/03/10 15:24:45 - Get Company Ids - Loading step with ID: 7
    2009/03/10 15:24:45 - Get Company Ids - Loading step with ID: 10
    2009/03/10 15:24:45 - Get Company Ids - ID_DIRECTORY=0
    2009/03/10 15:24:45 - Get Company Ids - Loaded the transformation [Get Company Ids] , directory == null : false
    2009/03/10 15:24:45 - Get Company Ids - Loaded the transformation [Get Company Ids] in directory /
    2009/03/10 15:24:45 - Get Company Ids - Starting transformation...(file=null, name=Get Company Ids, repinfo=Transformation)
    2009/03/10 15:24:45 - Get Company Ids - Transformation is pre-loaded.
    2009/03/10 15:24:45 - Get Company Ids - nr of steps to run : 4 , nr of hops : 3
    2009/03/10 15:24:45 - Get Company Ids - Dispatching started for transformation [Get Company Ids]
    2009/03/10 15:24:45 - Get Company Ids - Nr of arguments detected:0
    2009/03/10 15:24:45 - Get Company Ids - This is not a replay transformation
    2009/03/10 15:24:45 - Get Company Ids - I found 4 different steps to launch.
    2009/03/10 15:24:45 - Get Company Ids - Allocating rowsets...
    2009/03/10 15:24:45 - Get Company Ids - Allocating rowsets for step 0 --> Check if null
    2009/03/10 15:24:45 - Get Company Ids - prevcopies = 1, nextcopies=1
    2009/03/10 15:24:45 - Get Company Ids - Transformation allocated new rowset [Check if null.0 - Set Variables.0]
    2009/03/10 15:24:45 - Get Company Ids - prevcopies = 1, nextcopies=1
    2009/03/10 15:24:45 - Get Company Ids - Transformation allocated new rowset [Check if null.0 - Abort.0]
    2009/03/10 15:24:45 - Get Company Ids - Allocated 2 rowsets for step 0 --> Check if null
    2009/03/10 15:24:45 - Get Company Ids - Allocating rowsets for step 1 --> Set Variables
    2009/03/10 15:24:45 - Get Company Ids - Allocated 2 rowsets for step 1 --> Set Variables
    2009/03/10 15:24:45 - Get Company Ids - Allocating rowsets for step 2 --> Abort
    2009/03/10 15:24:45 - Get Company Ids - Allocated 2 rowsets for step 2 --> Abort
    2009/03/10 15:24:45 - Get Company Ids - Allocating rowsets for step 3 --> Get Company Ids
    2009/03/10 15:24:45 - Get Company Ids - prevcopies = 1, nextcopies=1
    2009/03/10 15:24:45 - Get Company Ids - Transformation allocated new rowset [Get Company Ids.0 - Check if null.0]
    2009/03/10 15:24:45 - Get Company Ids - Allocated 3 rowsets for step 3 --> Get Company Ids
    2009/03/10 15:24:45 - Get Company Ids - Allocating Steps & StepData...
    2009/03/10 15:24:45 - Get Company Ids - Transformation is about to allocate step [Check if null] of type [FilterRows]
    2009/03/10 15:24:45 - Get Company Ids - Step has nrcopies=1
    2009/03/10 15:24:45 - Check if null.0 - Starting allocation of buffers & new threads...
    2009/03/10 15:24:45 - Check if null.0 - Step info: nrinput=1 nroutput=2
    2009/03/10 15:24:45 - Check if null.0 - Got previous step from [Check if null] #0 --> Get Company Ids
    2009/03/10 15:24:45 - Check if null.0 - input rel is 1:1
    2009/03/10 15:24:45 - Check if null.0 - Found input rowset [Get Company Ids.0 - Check if null.0]
    2009/03/10 15:24:45 - Check if null.0 - output rel. is 1:1
    2009/03/10 15:24:45 - Check if null.0 - Found output rowset [Check if null.0 - Set Variables.0]
    2009/03/10 15:24:45 - Check if null.0 - output rel. is 1:1
    2009/03/10 15:24:45 - Check if null.0 - Found output rowset [Check if null.0 - Abort.0]
    2009/03/10 15:24:45 - Check if null.0 - Finished dispatching
    2009/03/10 15:24:45 - Get Company Ids - Transformation has allocated a new step: [Check if null].0
    2009/03/10 15:24:45 - Get Company Ids - Transformation is about to allocate step [Set Variables] of type [SetVariable]
    2009/03/10 15:24:45 - Get Company Ids - Step has nrcopies=1
    2009/03/10 15:24:45 - Set Variables.0 - distribution activated
    2009/03/10 15:24:45 - Set Variables.0 - Starting allocation of buffers & new threads...
    2009/03/10 15:24:45 - Set Variables.0 - Step info: nrinput=1 nroutput=0
    2009/03/10 15:24:45 - Set Variables.0 - Got previous step from [Set Variables] #0 --> Check if null
    2009/03/10 15:24:45 - Set Variables.0 - input rel is 1:1
    2009/03/10 15:24:45 - Set Variables.0 - Found input rowset [Check if null.0 - Set Variables.0]
    2009/03/10 15:24:45 - Set Variables.0 - Finished dispatching
    2009/03/10 15:24:45 - Get Company Ids - Transformation has allocated a new step: [Set Variables].0
    2009/03/10 15:24:45 - Get Company Ids - Transformation is about to allocate step [Abort] of type [Abort]
    2009/03/10 15:24:45 - Get Company Ids - Step has nrcopies=1
    2009/03/10 15:24:45 - Abort.0 - distribution activated
    2009/03/10 15:24:45 - Abort.0 - Starting allocation of buffers & new threads...
    2009/03/10 15:24:45 - Abort.0 - Step info: nrinput=1 nroutput=0
    2009/03/10 15:24:45 - Abort.0 - Got previous step from [Abort] #0 --> Check if null
    2009/03/10 15:24:45 - Abort.0 - input rel is 1:1
    2009/03/10 15:24:45 - Abort.0 - Found input rowset [Check if null.0 - Abort.0]
    2009/03/10 15:24:45 - Abort.0 - Finished dispatching
    2009/03/10 15:24:45 - Get Company Ids - Transformation has allocated a new step: [Abort].0
    2009/03/10 15:24:45 - Get Company Ids - Transformation is about to allocate step [Get Company Ids] of type [SystemInfo]
    2009/03/10 15:24:45 - Get Company Ids - Step has nrcopies=1
    2009/03/10 15:24:45 - Get Company Ids.0 - distribution activated
    2009/03/10 15:24:45 - Get Company Ids.0 - Starting allocation of buffers & new threads...
    2009/03/10 15:24:45 - Get Company Ids.0 - Step info: nrinput=0 nroutput=1
    2009/03/10 15:24:45 - Get Company Ids.0 - output rel. is 1:1
    2009/03/10 15:24:45 - Get Company Ids.0 - Found output rowset [Get Company Ids.0 - Check if null.0]
    2009/03/10 15:24:45 - Get Company Ids.0 - Finished dispatching
    2009/03/10 15:24:45 - Get Company Ids - Transformation has allocated a new step: [Get Company Ids].0
    2009/03/10 15:24:45 - Get Company Ids - This transformation can be replayed with replay date: 2009/03/10 15:24:45
    2009/03/10 15:24:45 - Get Company Ids - Initialising 4 steps...
    2009/03/10 15:24:45 - Check if null.0 - Running on slave server #0/1.
    2009/03/10 15:24:45 - Abort.0 - Running on slave server #0/1.
    2009/03/10 15:24:45 - Set Variables.0 - Running on slave server #0/1.
    2009/03/10 15:24:45 - Get Company Ids.0 - Running on slave server #0/1.
    2009/03/10 15:24:45 - Get Company Ids - Step [Check if null.0] initialized flawlessly.
    2009/03/10 15:24:45 - Get Company Ids - Step [Set Variables.0] initialized flawlessly.
    2009/03/10 15:24:45 - Get Company Ids - Step [Abort.0] initialized flawlessly.
    2009/03/10 15:24:45 - Get Company Ids - Step [Get Company Ids.0] initialized flawlessly.
    2009/03/10 15:24:45 - Check if null.0 - Starting to run...
    2009/03/10 15:24:45 - Set Variables.0 - Starting to run...
    2009/03/10 15:24:45 - Abort.0 - Starting to run...
    2009/03/10 15:24:45 - Get Company Ids.0 - Starting to run...
    2009/03/10 15:24:45 - Get Company Ids.0 - System info returned: [null], [null]
    2009/03/10 15:24:45 - Get Company Ids.0 - Signaling 'output done' to 1 output rowsets.
    2009/03/10 15:24:45 - Get Company Ids.0 - Finished processing (I=0, O=0, R=1, W=1, U=1, E=0)
    2009/03/10 15:24:45 - Check if null.0 - Sending row to true :Abort : [null], [null]
    2009/03/10 15:24:45 - Get Company Ids - Transformation has allocated 4 threads and 3 rowsets.
    2009/03/10 15:24:45 - Abort.0 - Row nr 1 causing abort : [null], [null]
    2009/03/10 15:24:45 - Abort.0 - Null Value
    2009/03/10 15:24:45 - Abort.0 - Finished processing (I=0, O=0, R=1, W=1, U=1, E=1)
    2009/03/10 15:24:45 - Get Company Ids - Transformation detected one or more steps with errors.
    2009/03/10 15:24:45 - Get Company Ids - Transformation is killing the other steps!
    2009/03/10 15:24:45 - Get Company Ids - Looking at step: Check if null
    2009/03/10 15:24:45 - Set Variables.0 - We dont received any row...setting the defaults values.
    2009/03/10 15:24:45 - Set Variables.0 - Finished after 0 rows.
    2009/03/10 15:24:45 - Set Variables.0 - Signaling 'output done' to 0 output rowsets.
    2009/03/10 15:24:45 - Set Variables.0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=0)
    2009/03/10 15:24:45 - Get Company Ids - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : Errors detected!
    2009/03/10 15:24:45 - Get Company Ids - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : Errors detected!
    2009/03/10 15:24:45 - Check if null.0 - Signaling 'output done' to 2 output rowsets.
    2009/03/10 15:24:45 - Check if null.0 - Finished processing (I=0, O=0, R=1, W=1, U=1, E=0)
    2009/03/10 15:24:45 - Get Company Ids - Looking at step: Set Variables
    2009/03/10 15:24:45 - Get Company Ids - Looking at step: Abort
    2009/03/10 15:24:45 - Get Company Ids - Looking at step: Get Company Ids
    2009/03/10 15:24:45 - Base Data Copy - Finished jobentry [Get Company Ids] (result=[false])
    2009/03/10 15:24:45 - Spoon - Job has ended.
    2009/03/10 15:26:12 - Spoon - edit job graph entry: Get Company Ids
    2009/03/10 15:26:33 - Get Company Ids - From step: Check if null, looking at 1 prev. steps.
    2009/03/10 15:26:33 - Get Company Ids - From step: Get Company Ids, looking at 0 prev. steps.
    2009/03/10 15:26:33 - Get Company Ids - Getting fields from step: Get Company Ids, type=SystemInfo
    2009/03/10 15:26:33 - Get Company Ids - Found fields to add: [Source_id String], [Dest_id String]

  4. #4
    Join Date
    May 2006
    Posts
    4,882

    Default

    You abort it yourself "Abort.0 - Row nr 1 causing abort : [null], [null]"... you sent a row to an abort step.

    Regards,
    Sven

  5. #5
    Join Date
    Mar 2009
    Posts
    7

    Default

    Yep... That works. I abort because the set variables fails, instead of the values from the command line, it has null, hence an abort. I figured a check to see if someone used the right syntax on the command-line is safer than a query with a null.

    The problem isn't the abort its that the set variable doesn't pull the value from the command-line.

  6. #6
    Join Date
    Apr 2008
    Posts
    4,686

    Default

    Can you post your transformation?

    The Get System Info is returning [null],[null] which is narrowing down the issue further.

  7. #7
    Join Date
    Mar 2009
    Posts
    7

    Default Transformation

    No problem.... Thanks!

    Ross

    <?xml version="1.0" encoding="UTF-8"?>
    <transformation>
    <info>
    <name>Get Company Ids</name>
    <description/>
    <extended_description> </extended_description>
    <trans_version>1</trans_version>
    <trans_status>1</trans_status>
    <directory>&#47;</directory>
    <log>
    <read/>
    <write/>
    <input/>
    <output/>
    <update/>
    <rejected/>
    <connection/>
    <table/>
    <step_performance_table/>
    <use_batchid>Y</use_batchid>
    <use_logfield>N</use_logfield>
    </log>
    <maxdate>
    <connection/>
    <table/>
    <field/>
    <offset>0.0</offset>
    <maxdiff>0.0</maxdiff>
    </maxdate>
    <size_rowset>10000</size_rowset>
    <sleep_time_empty>50</sleep_time_empty>
    <sleep_time_full>50</sleep_time_full>
    <unique_connections>N</unique_connections>
    <feedback_shown>Y</feedback_shown>
    <feedback_size>50000</feedback_size>
    <using_thread_priorities>Y</using_thread_priorities>
    <shared_objects_file/>
    <capture_step_performance>N</capture_step_performance>
    <step_performance_capturing_delay>1000</step_performance_capturing_delay>
    <dependencies>
    </dependencies>
    <partitionschemas>
    </partitionschemas>
    <slaveservers>
    </slaveservers>
    <clusterschemas>
    </clusterschemas>
    <modified_user>admin</modified_user>
    <modified_date>2009&#47;03&#47;09 17:40:38.000</modified_date>
    </info>
    <notepads>
    </notepads>
    <connection>
    <name>Staging</name>
    <server>10.202.42.25</server>
    <type>MYSQL</type>
    <access>Native</access>
    <database>business</database>
    <port>3306</port>
    <username>deposco</username>
    <password>Encrypted 2be98afc86aa7f2e4cb1dfd608e81ac8a</password>
    <servername/>
    <data_tablespace/>
    <index_tablespace/>
    <attributes>
    <attribute><code>FORCE_IDENTIFIERS_TO_LOWERCASE</code><attribute>N</attribute></attribute>
    <attribute><code>FORCE_IDENTIFIERS_TO_UPPERCASE</code><attribute>N</attribute></attribute>
    <attribute><code>IS_CLUSTERED</code><attribute>N</attribute></attribute>
    <attribute><code>PORT_NUMBER</code><attribute>3306</attribute></attribute>
    <attribute><code>QUOTE_ALL_FIELDS</code><attribute>N</attribute></attribute>
    <attribute><code>STREAM_RESULTS</code><attribute>Y</attribute></attribute>
    <attribute><code>USE_POOLING</code><attribute>N</attribute></attribute>
    </attributes>
    </connection>
    <order>
    <hop> <from>Check if null</from><to>Set Variables</to><enabled>Y</enabled> </hop> <hop> <from>Check if null</from><to>Abort</to><enabled>Y</enabled> </hop> <hop> <from>Get Company Ids</from><to>Check if null</to><enabled>Y</enabled> </hop> </order>
    <step>
    <name>Abort</name>
    <type>Abort</type>
    <description/>
    <distribute>Y</distribute>
    <copies>1</copies>
    <partitioning>
    <method>none</method>
    <schema_name/>
    </partitioning>
    <row_threshold>0</row_threshold>
    <message>Null Value</message>
    <always_log_rows>Y</always_log_rows>
    <cluster_schema/>
    <remotesteps> <input> </input> <output> </output> </remotesteps> <GUI>
    <xloc>246</xloc>
    <yloc>43</yloc>
    <draw>Y</draw>
    </GUI>
    </step>

    <step>
    <name>Check if null</name>
    <type>FilterRows</type>
    <description/>
    <distribute>N</distribute>
    <copies>1</copies>
    <partitioning>
    <method>none</method>
    <schema_name/>
    </partitioning>
    <send_true_to>Abort</send_true_to>
    <send_false_to>Set Variables</send_false_to>
    <compare>
    <condition>
    <negated>N</negated>
    <conditions>
    <condition>
    <negated>N</negated>
    <leftvalue>Source_id</leftvalue>
    <function>IS NULL</function>
    <rightvalue>Dest_id</rightvalue>
    </condition>
    <condition>
    <negated>N</negated>
    <operator>OR</operator>
    <leftvalue>Dest_id</leftvalue>
    <function>IS NULL</function>
    <rightvalue/>
    </condition>
    </conditions>
    </condition>
    </compare>
    <cluster_schema/>
    <remotesteps> <input> </input> <output> </output> </remotesteps> <GUI>
    <xloc>178</xloc>
    <yloc>106</yloc>
    <draw>Y</draw>
    </GUI>
    </step>

    <step>
    <name>Get Company Ids</name>
    <type>SystemInfo</type>
    <description/>
    <distribute>Y</distribute>
    <copies>1</copies>
    <partitioning>
    <method>none</method>
    <schema_name/>
    </partitioning>
    <fields>
    <field>
    <name>Source_id</name>
    <type>command line argument 1</type>
    </field>
    <field>
    <name>Dest_id</name>
    <type>command line argument 2</type>
    </field>
    </fields>
    <cluster_schema/>
    <remotesteps> <input> </input> <output> </output> </remotesteps> <GUI>
    <xloc>109</xloc>
    <yloc>104</yloc>
    <draw>Y</draw>
    </GUI>
    </step>

    <step>
    <name>Set Variables</name>
    <type>SetVariable</type>
    <description/>
    <distribute>Y</distribute>
    <copies>1</copies>
    <partitioning>
    <method>none</method>
    <schema_name/>
    </partitioning>
    <fields>
    <field>
    <field_name>Source_id</field_name>
    <variable_name>SOURCE_ID</variable_name>
    <variable_type>ROOT_JOB</variable_type>
    <default_value/>
    </field>
    <field>
    <field_name>Dest_id</field_name>
    <variable_name>DEST_ID</variable_name>
    <variable_type>ROOT_JOB</variable_type>
    <default_value/>
    </field>
    </fields>
    <cluster_schema/>
    <remotesteps> <input> </input> <output> </output> </remotesteps> <GUI>
    <xloc>256</xloc>
    <yloc>165</yloc>
    <draw>Y</draw>
    </GUI>
    </step>

    <step_error_handling>
    </step_error_handling>
    <slave-step-copy-partition-distribution>
    </slave-step-copy-partition-distribution>
    <slave_transformation>N</slave_transformation>
    </transformation>

  8. #8
    DEinspanjer Guest

    Default

    Did you try running the job from the command line using kitchen.sh or .bat? That might help narrow down the problem to something to do with specifying the arguments in Spoon or not.

    Also, in your job, when you created the first transformation entry, did you mess with any of the settings in it regarding passing arguments?

  9. #9
    Join Date
    Mar 2009
    Posts
    7

    Default Never tried

    Running the job via Kitchen... I'll give that a shot, and I didn't change anything from the default in passing arguments... Anything I can verify?

    Ross

  10. #10
    Join Date
    May 2006
    Posts
    4,882

    Default

    Just tried it ... works ok without changes.

    Run spoon, load the transformation. Run ... specify arguments for argument 1 and 2. press ok... runs ok.

    If you leave argument 1 or 2 empty it will abort as per your specification.

    So where exactly do you see a problem? Try again and run the transformation from file, instead of from repository.

    Regards,
    Sven

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.