Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Table exists with variable in a job

  1. #1
    Join Date
    Jun 2010
    Posts
    11

    Default Table exists with variable in a job

    Hi,
    I am new to Kettle. I'd like to use Table Exists, to write to my Vertica table only if it exists. If it doesn't exist, I'd like to write to a log somewhere some message.
    I get a list of tables I need to move from Oracle in a file. I read from a file and Copy rows to result. In another transformation I get rows from result and Set Variable (assignTabNameTransform). In another transformation I have tableInput from Oracle into table Output in Vertica (inputOutputTransform).
    In my Job I have Start->assignTabNameTransform->TableExists->inputOutputTransform.
    There is a green arrow between TableExists and InputOutputTransform, but is that what I need? Does that mean that my inputOutputTrasnform will run if TableExists?
    In my TableExists, I set Table name to ${tabname}, which is my variable name.
    Where do I place my "if table doesn't exist, append a message to some file"?
    Sorry for, what must be basic question, I am sure.
    Marjana

  2. #2
    Join Date
    Sep 2007
    Posts
    834

    Default

    In your job add a new job entry, create a hop from tableExists toward this new entry, right-click it, and select evaluation --> follow when result is false.

    This will work just for a single table. For a list of tables, you should put everything but the first transformation into a sub-job and loop over the tables (in the job entry dialog window, check "execute for every input row"),

  3. #3
    Join Date
    Jun 2010
    Posts
    11

    Default

    Thanks, I got it working. I am not sure how to write a message to a file though. I found "Write to log" and that works. But I'd like to have it write to a file that someone can just look at and see my short messages (and not to have to look at a very big log - I am moving around 10000 tables).

  4. #4
    Join Date
    Sep 2007
    Posts
    834

    Default

    Instead of "Write to log", use a Transformation job entry, and call a transformation that does what you want.

  5. #5
    Join Date
    Jun 2010
    Posts
    11

    Default

    Sorry I must be blind. I am looking at transformations design and I don't see anything I could use to write to a file something like:
    "table ${tabname} does not exist"
    I tried transformation that has only Text File. In Fields tab I put ${tabname} in Name column, but this doesn't work (file gets created but it is empty).
    Please help (I know it is basic thing but I am not very bright today).

  6. #6
    Join Date
    Sep 2007
    Posts
    834

    Default

    In order to send data to a text file, you have to build a stream containing that data. Please take a look at the attached transformation. In this case I used a Get Variables step to build a single dataset (1 row, 1 column), but this is not the only way for accomplishing that,
    Attached Files Attached Files

  7. #7
    Join Date
    Mar 2010
    Posts
    159

    Default

    Gosh you're nice Maria.

    Things will get easier Marjana as the difference between variables/params and fields becomes more evident. Good luck.

    Regards,
    Jeremy

  8. #8
    Join Date
    Jun 2010
    Posts
    11

    Default

    You are nice Maria, thanks, I have my message file now working.

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.