Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Best way to perform an equivalent of UNION in MySQL in PDI

  1. #1
    Join Date
    Nov 2011
    Posts
    22

    Default Best way to perform an equivalent of UNION in MySQL in PDI

    Hi,

    I am trying to see the best way to perform a UNION in PDI. I have 3 table input steps with 3 different queries that gives me two columns in the results like the Date and Tickets. I would like to combine the results from the 2 or more streams and add the values.

    I am currently using the Dummy step and then using the Group by step to add the values. I am not sure if there is a better way to do this instead of using the Dummy step and wondering if I can get any inputs on the same.

    I also tried using the Multiway Merge Join and doing a FULL OUTER JOIN but this is giving me the following results: Date_1, Tickets_1, Date_2, Tickets_2, Date_3, Tickets_3 so not sure if I am doing it right.

    PDI SPOON VERSION: 7.0.0

    Thank you,
    Malavika

  2. #2
    Join Date
    May 2016
    Posts
    282

    Default

    The Append stream step will do it for you, it will ask that the incoming streams have the same structure (fields name included) so you can previously use a Select values step if you need it for that.
    Regards
    OS: Ubuntu 16.04 64 bits
    Java: Openjdk 1.8.0_131
    Pentaho 6.1 CE

  3. #3
    Join Date
    Nov 2011
    Posts
    22

    Default

    Thank you for your response Ana.

    I believe Append streams can be used for just 2 input steps and there are cases where I have around 3-5 Table input steps and would like to perform a UNION equivalent.

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

    Default

    You can simply feed the multiple incoming streams into one step. Similar to in SQL though, you must have identical field layout.

    Select col1 as col1, cast(date1 as date) as col2 from table1
    UNION
    Select col65 as col1, cast(time44 as date) as col2 from table2

    This can be replicated in PDI as:

    Table Input1 ->\
    Table Input2 -> Sort1
    Table Input3 -> /

    Where Sort1 can really be any type of step. However there is no promise as to row order. Using a sort will allow you to force the order to what you want it to be.


    You don't want a Group By nor a JOIN (they each do basically the same thing as what you would get with the same commands in SQL!)


    Alternatively, you can actually put a UNION in your Table Input, and ask the DB itself to do the hard work.

Tags for this Thread

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.