Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: create table transformation step is not working in job

  1. #1
    Join Date
    May 2007
    Posts
    9

    Default create table transformation step is not working in job

    Hi,

    One of the transformation steps in my Job script is a SQL which will CREATE a table. And the next transformation step will do something with the table.
    It works fine when I run the spoon (transformation) script alone. But when I run the job, it gives errors. It showed that the first step (create the table) was successful, but the second step (select against the newly created table failed) since the table doesn't exists. Does anybody know why is it happening? Is there any trick to do this? Any suggestion is appreciated.

    error msg:
    ...
    2007/11/30 17:00:27 - Table input.0 - ERROR (version 2.5.2, build 252007 from 2007/11/05 11:20:37) : ORA-00942: table or view does not exist
    ...

    Thanks,
    Grace

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

    Default

    1) Don't do DDL in jobs/transformations, it's not nice

    2) Steps run in a parallel, not sequentially... the second step will start executing it's query before the first one is done. You would have to split the transformation in 2 and run them in a job (which does sequential processing)

    Regards,
    Sven

  3. #3
    Join Date
    May 2007
    Posts
    9

    Default

    I realized that I didn't use the terms of transformations and steps properly in the original post.

    What I really did was having CREATE TABLE in one transformation and query it in another transformation. And they works well when I run them as transformation, but gave the error when I have them in a job. So, I already have them in two separated transformations. Is there anything else I can do to make it work?

    Thanks!

    Grace

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

    Default

    Only thing I can think of at the moment is that you somehow use a different connection and the one connectiont doesn't see the table because of access rights.

    And as before, it's not really meant to execute DDL.

    Regards,
    Sven

  5. #5
    Join Date
    May 2007
    Posts
    9

    Default

    hi Sven, thanks a lot for your reply!

    Could you explain more why it's not a good idea to execute DDL in transformation? And what would be the best way to do it -- should I just do it in a store procedure?

    Thanks!
    Grace

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

    Default

    1) Technically... the current step you use was not really meant for DDL as far as I remember. It will probably work but it was not the intention. There were some e-mails a couple of months ago to make a new step or job entry specifically for that.

    2) If you create DDL you will probably get your DBA's against you and besides sys admin, you don't want to fight DBA's ... it's a battle you'll lose
    Reasons:
    - All DDL in a database should be under version control (that may be just my DBA side talking)
    - access rights: you create a table but normally you will have to put access rights on it for use outside of ETL
    - table proliferation: someone takes your job, executes it a couple of times and presto... a couple of new tables in your database
    - If your table is used in other tables it may require maintenance (reorganization), indexing, stats gathering, ...

    I did work on a few projects that created tables on the fly but for DBA's it's nicer to know that all DDL is under their control. You will get happier and more helpful DBA's. In the current environment I work in only the DBA's have DDL rights in production, which automatically shuts that door.

    Regards,
    Sven

  7. #7
    Join Date
    May 2007
    Posts
    9

    Default

    I think you are right about the DDL thing.

    In my kettle job, I create the table, query it and in the last transformation, the table is truncated and dropped. I meant to create a temp table, however, temp table is only accessible in it's current session and couldn't be queried in the next transformation.

    Regards,
    Grace

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

    Default

    For your original error... it must be something stupid... it just means that 1 session can't find the table created in the other.

    Regards,
    Sven

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.