Union

Performing a Union

To perform a union of data sets, the /create/union endpoint requires five parameters:

1. the name of the memory-only table to be created
2. the list of member data sets to be used in the union operation; the result will contain all of the elements from the first data set and all of the elements from the second one
3. the list of columns from each of the given data sets to be used in the union operation
4. the list of column names to be output to the resulting memory-only table
5. the union mode specified in the options input parameter
   • union_all (the default option)
   • union or union_distinct

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

CREATE OR REPLACE TABLE example.lunch_or_dinner_menu_all AS
SELECT
    food_name AS lunch_or_dinner_food_name,
    category,
    price
FROM
    example.lunch_menu
UNION ALL
SELECT
    food_name,
    category,
    price
FROM
    example.dinner_menu

1
2
3
4
5
6
7
8
9

kinetica.create_union(
    table_name = "example.lunch_or_dinner_menu_all",
    table_names = ["example.lunch_menu", "example.dinner_menu"],
    input_column_names = [
        ["food_name", "category", "price"],
        ["food_name", "category", "price"]
    ],
    output_column_names = ["lunch_or_dinner_food_name", "category", "price"]
)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

CREATE OR REPLACE TABLE example.lunch_or_dinner_menu_unique AS
SELECT
    food_name AS lunch_or_dinner_food_name,
    category,
    price
FROM
    example.lunch_menu
UNION
SELECT
    food_name,
    category,
    price
FROM
    example.dinner_menu

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

kinetica.create_union(
    table_name = "example.lunch_or_dinner_menu_unique",
    table_names = ["example.lunch_menu", "example.dinner_menu"],
    input_column_names = [
        ["food_name", "category", "price"],
        ["food_name", "category", "price"]
    ],
    output_column_names = ["lunch_or_dinner_food_name", "category", "price"],
    options = {"mode": "union"}
)

Retrieving Union Data

To retrieve records from the union results:

• SQL
• Python

1
2
3

SELECT lunch_or_dinner_food_name, category, price
FROM example.lunch_or_dinner_menu_unique
ORDER BY lunch_or_dinner_food_name

1
2
3
4
5

gpudb.GPUdbTable(name = "example.lunch_or_dinner_menu_unique", db = kinetica).get_records_by_column(
    ["lunch_or_dinner_food_name", "category", "price"],
    options = {"order_by": "lunch_or_dinner_food_name"},
    print_data = True
)

Limitations and Cautions

• Performing a union between two data sets results in an entirely new data set, so be mindful of the memory usage implications.
• All data sets have to be replicated or not replicated, e.g., you cannot union replicated and non-replicated data sets.
• If attempting to perform a distinct union on sharded data sets, all data sets have to be sharded similarly (if all data is not on the same processing node, the distinct union can't be calculated properly).
• The result of a union operation does not get updated if source data set(s) are updated.
• The input_column_name parameter vector size needs to match the number of data sets listed, i.e. if you want to union a data set to itself, the data set will need to be listed twice in the table_names parameter.
• The input_column_name parameter vectors need to be listed in the same order as their source data sets, e.g., if three data sets are listed in the table_names parameter, the first data set's columns should be listed first in the input_column_name parameter, etc.
• The result of a union is transient, by default, and will expire after the default TTL setting.
• The result of a union is not persisted, by default, and will not survive a database restart; specifying a persist option of true will make the table permanent and not expire.