Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Loop Inside a Transformation

  1. #1
    Join Date
    Jun 2007
    Posts
    476

    Default Loop Inside a Transformation

    Hi, I'm currently developing a simple transformation that executes a series of SQL readed from a DB into another DB, I read a table like this:

    1 - INSERT INTO FOO VALUES (BAR);
    2 - UPDATE BAR SET FOO='BAR';
    3 - INSERT INTO BAR VALUES (FOO);

    my main problem is that if I'm running this proc. several times I get some conflicts in the insert, since my DB cant lock a table so 2 inserts will try to insert the same ID. My solution is create a "flag" file in where when I'm insertin i write 1 into the file, so the next insert checks if its 0 or 1.

    The problem is that when I notice a 1 (The table is locked) i need to create a loop, but SPOON doen't allow me to create loops inside a transformation.

    I'm attaching a png file where you can see the blue hop is the one creating the loop.

    My questions are:
    1) Why are loops not allowed in transformation but in Jobs the are allowed?
    2) I need an alternative to do this, I was thinking a javascript that reads the file and loops until its available, but I don't think this is the best way of doing it

    Thanks
    Rodrigo Haces
    TAM / Enterprise Architect
    Pentaho

  2. #2
    Join Date
    Jun 2007
    Posts
    476

    Default

    Forgot to attache the png, here it goes
    Attached Images Attached Images  
    Rodrigo Haces
    TAM / Enterprise Architect
    Pentaho

  3. #3
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    You can in fact do this without a problem if you make the transformation transactional (in the transformation settings dialog, Misc tab).

    Looping is not allowed because it makes the transformation non-deterministic.
    Suppose you have a loop and in each iteration you would add a single column.
    That would lead to an infinite amount of columns in the metadata system (and in reality as well).

    Please note that the problem is very simple to solve, just as well, with 2 separate transformation and a temp table.
    Last edited by MattCasters; 07-16-2009 at 02:45 PM.

  4. #4
    Join Date
    Jun 2007
    Posts
    476

    Default

    Quote Originally Posted by MattCasters View Post
    You can in fact do this without a problem if you make the transformation transactional (in the transformation settings dialog, Misc tab).
    This has to be in the 3.2??? I have 3.1 and read @ the doc "Make the transformation database transactional (before version 3.2: Use unique connections) ". Just tried and still got the same "This hop causes a loop in the transformation. Loops are not allowed!"
    Quote Originally Posted by MattCasters View Post
    Looping is not allowed because it makes the transformation non-deterministic.
    Suppose you have a loop and in each iteration you would add a single column.
    That would lead to an infinite amount of columns in the metadata system (and in reality as well).
    I understand this, but would be better (shurely its not easier) to have a sort of check that no new columns are appended when the loop is created? Actually I'm inserting a "slect values" that removes any column appended so this prob. doesn't happen
    Quote Originally Posted by MattCasters View Post
    Please note that the problem is very simple to solve, just as well, with 2 separate transformation and a temp table.
    Is there an example @ samples dir? or do you have any actual example? I'm attaching my ktr and png so that you can check it out and recomend the best solution.

    I solved it by a simple javascript as follows, but as I said, I don't think is the most optimal way of doing it:
    Code:
    var file="/home/rodrigo/SK.lock";
    do
    {
      var file=fileExists(file);
    } while(file)
    Attached Images Attached Images  
    Attached Files Attached Files
    Rodrigo Haces
    TAM / Enterprise Architect
    Pentaho

  5. #5
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    It's very likely you are making a simple problem very complex.
    I have no idea what you are talking about anymore nor do I want to invest more time.

    You were asking about this problem:

    Code:
    1 - INSERT INTO FOO VALUES (BAR);
    2 - UPDATE BAR SET FOO='BAR';
    3 - INSERT INTO BAR VALUES (FOO);
    I answered you need to use "Unique connections" (3.1 name) to solve it.
    End of story.

    Looping is not going to happen, no matter how hard you complain about it.

    Take care,
    Matt

  6. #6
    Join Date
    Jun 2007
    Posts
    476

    Default

    Quote Originally Posted by MattCasters View Post
    Code:
    1 - INSERT INTO FOO VALUES (BAR);
    2 - UPDATE BAR SET FOO='BAR';
    3 - INSERT INTO BAR VALUES (FOO);
    I answered you need to use "Unique connections" (3.1 name) to solve it.
    End of story.
    I thought that would enable the looping. If I have only 1 instance at time of this transformation enabled, this option would help, but my main problem and I dont know if this option resolves it is that this transformation is being used by 5 jobs that run at the same time but send different parametters. Any thoughts???

    Quote Originally Posted by MattCasters View Post
    Looping is not going to happen, no matter how hard you complain about it.
    I get it.. It would be niche though!!!
    Last edited by rhaces; 07-16-2009 at 09:05 PM. Reason: typo!
    Rodrigo Haces
    TAM / Enterprise Architect
    Pentaho

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

    Default

    Quote Originally Posted by rhaces View Post
    I thought that would enable the looping. If I have only 1 instance at time of this transformation enabled, this option would help, but my main problem and I dont know if this option resolves it is that this transformation is being used by 5 jobs that run at the same time but send different parametters. Any thoughts???
    It activates that each step in a transformation gets the same database connection, which solves your problem within 1 transformation on most databases. It does not solve the problem when you use it in multiple transformations.

    Quote Originally Posted by rhaces View Post
    I get it.. It would be niche though!!!
    He said "no loops"... there are enough other things people can shoot themselves with in their feet

    If you really want to loop, use a job to loop and execute the transformation that does the actual work.

    Regards,
    Sven

  8. #8
    Join Date
    Jun 2007
    Posts
    476

    Default

    Quote Originally Posted by sboden View Post
    If you really want to loop, use a job to loop and execute the transformation that does the actual work.

    Regards,
    Sven
    Thanks, I solved it with JavaScript, I will have to measure times and see if it can and needs to be optimized, and if so, I'll switch to a JOB

    Regards
    Rodrigo Haces
    TAM / Enterprise Architect
    Pentaho

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.