Hitachi Vantara Pentaho Community Forums
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Insert Update without primary key (or, why else wouldn't this be working?)

  1. #1
    Join Date
    Jun 2013
    Posts
    24

    Question Insert Update without primary key (or, why else wouldn't this be working?)

    I am working on a data sync transformation that will be run on demand by an end user. It has to be "polished".

    One of the steps is to update some preferences - that populate a dropdown list - based on what's in the source database. The target database has it's own selectable options table the data is being updated/inserted into, and gets it's own primary key when saved. So, the source data contains:

    ID, Label

    which maps to the target database as:

    value, label

    yet the full set of columns in the target database is:

    id, value, label, description, setting1, setting2, setting3, etc.

    The update/insert is failing and the kettle failure messages are typically unhelpful ("Errors Detected!"). I have the Update/Insert configured as:

    lookup keys:
    value = ID

    I think I am telling it to find a record in the table that matches where value in target is equal to the ID from the source database. These values are unique so this should work.

    I then have the update fields configured as:

    label = Label, Update = Y
    value = ID, Update = Y

    But this fails. Supposing that values need to be supplied for all the columns in the target database, I tried it as well with dummy values supplied by the Add Constants step, mapping those columns to appropriate values. That still fails. I am left to think that the target primary key (which isn't known in the source database) is needed to successfully update the record? I am guessing here. Any help appreciated.

  2. #2
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    I think that you do not have to update the ID (or Primary Key).
    Value = ID, Update = No
    -- Mick --

  3. #3
    Join Date
    Jun 2013
    Posts
    24

    Default

    I think the IDs is different contexts is confusing; the ID is NOT the primary key in the target database, it is the primary key in the source database.

    But, I tried it anyway, and it still fails. Same error.

  4. #4
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    A table synchronization can be as easy as adding two steps - Insert/Update is none of them.
    Attached Images Attached Images  
    So long, and thanks for all the fish.

  5. #5
    Join Date
    Jun 2013
    Posts
    24

    Default

    marabu, this at first seemed to me to be just what I wanted. But I am looking at the documentation and it appears too simple. Keep in mind the big differences between the two tables. The Diff seems to need to rely on a Key, which the source does not have. Certainly the Sync will need the target primary key to do the update, but that can't be in there because the previous step requires an identical number of columns, and there is no column in the source to align with the primary key column in the target.

  6. #6
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Quote Originally Posted by RobBrandt View Post
    The Diff seems to need to rely on a Key, which the source does not have.
    A key for Diff is any combination of fields capable of identifying a row. In your case SOURCE.ID should qualify as a key, since you try to use it for lookup in the Insert/Update step as well.

    Quote Originally Posted by RobBrandt View Post
    Certainly the Sync will need the target primary key to do the update
    The same as above holds for the Sync step: In your case TARGET.VALUE is an alternate key, that will do.

    The attached demo consists of three transformations:

    • PREPARE creates the target table in a memory based database
    • SHOWOFF simulates the source table by a Data Grid for convenience
    • CLEANUP drops the target table


    After playing with my demo you should be able to tell me where I'm wrong.
    Just add, delete or modify the Data Grid rows and rerun SHOWOFF
    Attached Files Attached Files
    So long, and thanks for all the fish.

  7. #7
    Join Date
    Jun 2013
    Posts
    24

    Default

    The one thing I haven't figured out yet is the final step, the sync. I have previewed my result and it has a list of items, all except 2 are "identical"; those 2 are "new".

    I've set up the sync step in a way that makes sense to me. I've also set up my source and target queries so that the column names are the same; I wasn't sure that was necessary, but that's the conditions were that got me this far so I am hesitant to change. But it makes it a bit confusing....

    In the Merge Rows step I have Reference rows origin set to the target table, and Compare rows origin set to the source table. This seemed reversed to me but when I did it the other way around the diff column showed the new rows as "deleted", so I put it this way that shows them as "new".



    For the sync, in the table "Keys to look up the values" I have:

    value = value
    option_group_id = option_group_id

    These two columns combined should uniquely identify a row. "values" are unique for each set of option groups.

    In the table Update fields I have for table field, stream field, and Update:

    value, value, Y
    label, label, Y
    option_group_id, option_group_id, Y

    Running this transformation results in "Error Detected"

  8. #8
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    You don't want to update keys, so "value" and "option_group_id" both should read Update = N, but that's not your error.
    In Spoon you might see only two red lines telling you "Error detected", but the console log should be more explicit.
    So long, and thanks for all the fish.

  9. #9
    Join Date
    Jun 2013
    Posts
    24

    Default

    "Synchronize after merge.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : It was not possible to find operation field [null] in the input stream!"

    Seems suspicious. I'll have to investigate further...

  10. #10
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Could be something very simple, like you forgot to set the fieldname on the "advanced" tab.
    So long, and thanks for all the fish.

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.