Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: How to use "Split field to rows" step

  1. #1
    Join Date
    Aug 2010
    Posts
    27

    Default How to use "Split field to rows" step

    Hi All,

    I am new to kettle. Following is the transformation on which I am working currently.

    1. I am taking the input from excel file which contains list of my business logic entities which are saperated by ";" .
    2. After this I am using "Split Fileds to rows" function.
    3. After this step i am having a "modified java script" stpe in which i hardcode some values.
    4. And finally I enter all hardcoded and values form the "Split fields to rows" step into database.

    But the above transformation is not working for me I am getting following error.

    2010/08/12 19:42:14 - org.pentaho.di.trans.steps.databasejoin.DatabaseJoin - ERROR (version 3.2.2-stable, build 10951 from 2009-09-15 15.34.19 by buildguy) : Unexpected error :
    2010/08/12 19:42:14 - org.pentaho.di.trans.steps.databasejoin.DatabaseJoin - ERROR (version 3.2.2-stable, build 10951 from 2009-09-15 15.34.19 by buildguy) : java.lang.NullPointerException
    2010/08/12 19:42:14 - org.pentaho.di.trans.steps.databasejoin.DatabaseJoin - ERROR (version 3.2.2-stable, build 10951 from 2009-09-15 15.34.19 by buildguy) : at org.pentaho.di.trans.steps.databasejoin.DatabaseJoinMeta.getFields(DatabaseJoinMeta.java:333)
    2010/08/12 19:42:14 - org.pentaho.di.trans.steps.databasejoin.DatabaseJoin - ERROR (version 3.2.2-stable, build 10951 from 2009-09-15 15.34.19 by buildguy) : at org.pentaho.di.trans.steps.databasejoin.DatabaseJoin.lookupValues(DatabaseJoin.java:54)
    2010/08/12 19:42:14 - org.pentaho.di.trans.steps.databasejoin.DatabaseJoin - ERROR (version 3.2.2-stable, build 10951 from 2009-09-15 15.34.19 by buildguy) : at org.pentaho.di.trans.steps.databasejoin.DatabaseJoin.processRow(DatabaseJoin.java:151)
    2010/08/12 19:42:14 - org.pentaho.di.trans.steps.databasejoin.DatabaseJoin - ERROR (version 3.2.2-stable, build 10951 from 2009-09-15 15.34.19 by buildguy) : at org.pentaho.di.trans.step.BaseStep.runStepThread(BaseStep.java:2889)
    2010/08/12 19:42:14 - org.pentaho.di.trans.steps.databasejoin.DatabaseJoin - ERROR (version 3.2.2-stable, build 10951 from 2009-09-15 15.34.19 by buildguy) : at org.pentaho.di.trans.steps.databasejoin.DatabaseJoin.run(DatabaseJoin.java:265)
    2010/08/12 19:42:14 - org.pentaho.di.trans.steps.databasejoin.DatabaseJoin - Finished processing (I=0, O=0, R=1, W=0, U=0, E=1)
    2010/08/12 19:42:14 - db ktr - Transformation detected one or more steps with errors.
    2010/08/12 19:42:14 - db ktr - Transformation is killing the other steps!
    2010/08/12 19:42:14 - Modified Java Script Value 2.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    2010/08/12 19:42:15 - Modified Java Script Value.0 - Finished processing (I=0, O=0, R=2, W=1, U=0, E=0)
    2010/08/12 19:42:15 - Spoon - The transformation has finished!!
    2010/08/12 19:42:15 - db ktr - ERROR (version 3.2.2-stable, build 10951 from 2009-09-15 15.34.19 by buildguy) : Errors detected!


    Can anybody please help me on this issue.

    Thanks in advance.

  2. #2
    Join Date
    Sep 2009
    Posts
    810

    Default

    Hi there,
    You are using a database join step somewhere. It is failing for some reason. From your description I don't quite the role of the join step in your transformation. Maybe it is an off-screen zombie causing trouble? If it should not be there, delete it

    In case you really need the join step you should make sure it is configured properly. It also helps to check the data coming in. Preview the output of the step preceding the join step (right click and select preview).

    Cheers

    Slawo

  3. #3
    Join Date
    Aug 2010
    Posts
    27

    Default

    Thanks man...
    I figured out the problem. The issue was with the way we I am hitting the query to database. Thanks for reply.

    I have one more question. Do you know any step which will execute my queries which contains if else conditions and return the result of that query to next step. I can't use "table input" step here. E.g. of query is as below

    IF EXISTS (<Business logic query>)
    BEGIN
    <Business logic query>
    END
    ELSE
    BEGIN
    <Business logic query>
    EN

  4. #4
    Join Date
    Sep 2009
    Posts
    810

    Default

    Hi there,

    IF EXISTS sounds like you are trying to do control logic inside a transformation. Maybe you should consider doing it in a job instead. That's what they are good at.

    I figure you need to check whether an entity exists and tailoring your queries around the condition. There are some job entries for checking existence of entities in the "conditions" section of a Kettle job. Maybe you could consider factoring out the condition handling into the job, calling the transformation further down the control flow (possibly giving it some parameters as well).

    something like this maybe:
    JOB
    if some table exists, truncate it, otherwise create it
    call TRANSFORMATION [that can safely assume that the table exists and is empty]

    I am not sure what exactly your query requirements are, but it is a common technique to embed a transformation inside a job that uses a sequence of control logic first to ensure the transformation has a reliable state to work with. In short: jobs are designed for control flow. They control what to do and in what order. They routinely create/drop tables, indexes, views. They are used to execute arbitrary SQL (SQL step from the scripting section), but you don't get to look at individual rows. Transformations on the other hand are good at working with individual rows. Conditions, filters etc. always apply to individual rows.

    Hope that helps you along,
    Best Regards

    Slawo

  5. #5
    Join Date
    Aug 2010
    Posts
    27

    Default

    Hi Slawo,

    Thanks for reply .

    Let me tell you my business requirement. I have an XML file with me . That XML file contains other business related data and queries which i want to execute on database if certain condition is satisfied. So these queries contains following things
    1. Simple SQL query
    2. Database join/IN queries
    3. The transact SQL blocks (Which we are discussing right now)
    4. Procedures

    So i want a step so that I can run all the above queries into that step and get the corresponding result into next step.I think we can do this with table input step but We can not access output variable of table input step into next step so I am stuck.

    The queries which i am going to execute are mainly select or insert queries. So I want a step by which I can run at least any transact SQL query or PL/SQL block and complex IN queries into it, so that I will blindly put those queries into that block and will access it's output in next step. E.g of one of the query is as below :

    DECLARE <variable>
    DECLARE <another variable>
    SET <set some parameters>
    IF EXISTS (<Business logic query>)
    BEGIN
    <Business logic query>
    END
    ELSE
    BEGIN
    Select IDENT_Current('Person')
    END


    I am dying to get this step . The workaround is to use sqlplus. But I am not known to it. Can anybody elaborate how to use sqlplus, Or can post example of it.
    Last edited by nikhil_d; 08-16-2010 at 09:01 AM.

  6. #6
    Join Date
    Sep 2009
    Posts
    810

    Default

    Hi there,

    if your xml file already contains some queries, that you would like to execute, you might want to check out the "Get Data from XML" step (which would put the sql into fields) and execute the sql using the "Execute row SQL script" step. You don't get a peek at results then.

    There's plenty of information about sqlplus on the net, like here: http://cisnet.baruch.cuny.edu/holowczak/oracle/sqlplus/
    You could call sqlplus from a simple shell script in a kettle job using the "Shell" job entry from the scripting section.

    All in all it sounds to me like your XML file pretty much contains the transformation logic already. In this case you may find it easier to work with it by using the XML input step to extract all relevant data into fields and using a JavaScript step to examine and execute the logic using functions like fireToDB. You'd be able to pump out possible results to next steps as well.

    There's a short tutorial on how to use the fireToDB function here:
    http://type-exit.org/adventures-with...-and-firetodb/

    This post explains how to generate rows using the javascript steps:
    http://type-exit.org/adventures-with...entaho-kettle/

    Cheers

    Slawo

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.