Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Divide one table into 3

  1. #1

    Default Divide one table into 3

    Hi

    I'm managing a big table with this structure:

    SOURCETABLE

    id , user_code, name, surname, telephone, address, country, location, email,department, position, role..........

    I want to migrate to a different schema. Same data but 3 tables involved:

    T1

    user_code(PK), name, surname

    T2

    fk(user_code), telephone, address, country, location, email

    T3

    fk(user_code), department, position, role,


    What i have done so far is:

    Table Input > Select Values >Insert/Update (to avoid users that exist on destination) Worked flawlessly inserting selected values to T1,T2 and T3.


    My guess is this ETL is quick and dirty. is there a cleaner and smarter way to do it that works?

    Thanks

    Name:  divide.jpg
Views: 36
Size:  11.4 KB
    Last edited by Joselitux; 01-28-2014 at 01:14 PM. Reason: error

  2. #2
    Join Date
    Nov 2013
    Posts
    382

    Default

    You can create all values on a single step, the insert/update only the ones you need on each i/u step:

    table input --> all values --> Insert/update1 --> Insert/update3 --> insert/update2 --error-->write to log

  3. #3

    Default

    Worked ok on one line and seems cleaner and simpler.

    Thanks DepButi for your suggestion

  4. #4
    Join Date
    Nov 2013
    Posts
    382

    Default

    Most probably you don't need either the SetValues step ... I don't know which kind of transformation are you doing, but SQL itself can handle most data transformations directly. Extract, concatenation, cast, mathematical expressions, case, ... lots of options to work with

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

    Default

    That's not Set Values, but rather "Select Values" - which limits which columns are available downstream.
    I see no issues with how the original transform was presented.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

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.