Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: Transformation doesn't end

  1. #1

    Question Transformation doesn't end

    Hi!

    Is there any particular reason for a transformation does not end even after performing all steps?

    The transformation: csv input ->table input ->javascript-> dimension lookup1 -> dimension lookup2 -> dimension lookup3 -> dimension lookup4 -> dimension lookup5 -> dimension lookup6 -> dimension lookup7 -> dimension lookup8 -> insert/update.

    Tks
    Léo Müller

  2. #2

    Default

    Hi,

    I am afraid I cannot give you a reason, but I have run into the same problem numerous times. Often some change in the transformation got it working. Right now I am working on a transformation, which I keep on reducing to find the problematic operator.
    Do you end up with some operators having like a multiple of 10001, then it might be a problem with an incorrectly defined input in one of the operators. If you end up with not all of your data read from the table input as I am right now, try reducing your transformation till it reads all data.

    Bye

    Holger
    Last edited by Hymmen; 07-13-2009 at 09:11 AM.

  3. #3
    pstoellberger Guest

    Default

    where does this happen? in spoon or kitchen/pan?
    are all the transformation steps marked as "finished"?
    which database are you using? could it be that kettle can't close the db connections?

  4. #4

    Default

    This happening in spoon.

    I using MS SQL 2005 to read and insert.

    The transformation steps are marked as Running. Only the first step is finished (csv file input).

    This transformation is running since last Friday and it read all the records that should have read more than 48 hours ago. I use the same connection to others transformations and these are all ok.

  5. #5
    pstoellberger Guest

    Default

    have you tried to run it again?
    if you can attach the transformation so people can have a better look at it and maybe reproduce the problem or find its solution

  6. #6

    Default

    I tried run again and the same thing hapens.

    The transformation is attached now.

    Tks for the help!
    Attached Files Attached Files

  7. #7
    Join Date
    Feb 2009
    Posts
    296

    Default

    Okay.. you will need to check if you're actually running into a problem or if your transformation is just REALLY slow. I suspect the latter one, just looking at the SQL you're running.

    Your also have a lot of potential in the Java Script step. Try replacing all that hunk of JS-Script with this function:

    var nuSeqRecurso_converted = parseInt(nuSeqRecurso,36)

    Also get rid of the compatibility mode. This alone should speed up things.

    Now check if you're really having a problem with PDI. While your transformation is running you're sending one SQL for each line in the CSV file. Does the database idle or does it (more likely) have problems with satisfying your requests?

    Try to get the SQL straight. I don't know what exactly you're trying to do but using LEFT OUTER JOIN, UNION, Sub-Selects, Functions within the WHERE part and a bunch of CASE statements just freaks me out.
    My best guess is that your query performance is just ****ty and PDI has nothing to do with it.

    The database lookups look nice but you should enable caching - this will speed up all lookups if you're getting multiple line of matching lookups.

    The commit-size on the Insert/Update step is pretty low. You might also wish to increase that one.

    In order to find the bottleneck - press CTRL+T in your transformation, choose the Monitoring tab and enable performance monitoring. Those pretty chats you're getting will tell you which of your steps do not output as many rows as they read from others.

    However I must emphasis it again: I think your SQL is not performing nicely and your source database is dying during your job. Check with a DBA to have him check the DB performance during your job. If I'm right - ask him to help you optimise your query. Or split it into multiple queries. Or get the PDI job do at least some of the operations (like the additions, the cases, the coalesce or the case on the length of the string).
    Fabian,
    doing ETL with his hands bound on his back

  8. #8

    Default

    I do not know if he is having exactly the same problems as I have run into often enough. But, in my case the transformations run fine up to a certain point an then nothing happens anymore. The operators remain in the status running, but I could leave the transformation running for decades and it will probably never end.
    In my latest case it seems that Spoon does not like certain flows:
    do nothing
    Input< > merge upper data flow with new data from lower
    find more data

  9. #9

    Default

    Tks Fabian!

    That SQL is the worst here, and in this case are running at a big database! Before, the database not suport the SQL, and the DBA up TEMP space to 400GB! Then, it worked. That is the biggest SQL at the biggest Court from Brasil....
    I will make the changes and try run again. After, i will tell you the result! Tks again!

    Léo Müller

  10. #10
    Join Date
    Feb 2009
    Posts
    296

    Red face

    Again... try to optimise that SQL. Just bumping up the temp space won't make that stuff run any faster.

    If you're planning on doing that transformation on a regular basis you should get support on that SQL. Get someone who can optimise it. Start using the various transformation steps to get workload off the database and on the transformation host.

    This will help both your DBA and you. Trust me.

    Anyway - I'm happy I was of some help.
    Fabian,
    doing ETL with his hands bound on his back

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.