Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: How to use "Return auto-generated key"?

  1. #1

    Default How to use "Return auto-generated key"?

    Hi!

    I'm building a customer migration job from one database to another in physically separate servers. I got most of it done, and I just need to learn how to use the "Return auto-generated key" feature of the Table output component. I need to capture the new id, so that I can use it in a subsequent transformation to relate one table row, to a row in a different table.

    I have a client, mailserver, and client_mailserver tables. I first insert into client, then into mailserver. I want to capture the new row id for mailserver, so that I can create an entry in the junction table client_mailserver. Could somebody point me to a wiki entry or an article that shows how this is done?

    Thanks!

    Hector

  2. #2
    Join Date
    Nov 2013
    Posts
    382

    Default

    Check the box. Select any name you like for the new field on the text box "Name of the auto-generated key". You will have a new field named whatever you typed there so you can use it on next steps.

    Edited: I am ssuming you have a table with automatic id sequence, so new rows on the output table get a new id generated by your db system. This is the value you will get on your field.

  3. #3

    Question

    Quote Originally Posted by DepButi View Post
    Check the box. Select any name you like for the new field on the text box "Name of the auto-generated key". You will have a new field named whatever you typed there so you can use it on next steps.

    Edited: I am ssuming you have a table with automatic id sequence, so new rows on the output table get a new id generated by your db system. This is the value you will get on your field.
    Yes, the destination table has an auto-generated key. I already did the obvious stuff you described.

    Name:  autokeygen_1.jpg
Views: 448
Size:  14.0 KB

    This is what the whole transformation looks so far:

    Name:  autokeygen_3.jpg
Views: 428
Size:  14.0 KB

    I'm getting the following error:
    Abort.0 - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : Aborting after having seen 1 rows

    Name:  autokeygen_2.jpg
Views: 428
Size:  31.8 KB

    As far as I know, 5.2.0.0 build 1 is the latest version of kettle/spoon.

    So there's two things that I want help with.

    1) Get past the error
    2) How to actually use it in subsequent transformation. Notice, I did not say step. I need to use the new ID in the next transformation in the job.

    My Google-Fu hasn't yielded any usable results, after looking for several days now. That's the reason I asked to get pointed to FAQ, blog post, or article that explains how to #2. All of my ETL experience has been with Microsoft's DTS and SSIS. I've done this with those technologies, and trying to figure it out with kettle/spoon.

    Thanks!

    Hector

  4. #4
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    mailserver_table is complaining about an error, and is throwing an error to the error handling (as defined)
    Abort step will abort a transform as soon as it gets a row... So it is aborting.

    Assuming that mailserver_table is the table output that you want to use "return Autogenerated key", then you have to flow out from that Table Output step.

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.