Aggregate Unpivot In-Memory Tables

An aggregate unpivot in-memory table is created by specifying the result_table option while using the /aggregate/unpivot endpoint. Aggregate unpivot in-memory tables are also not persisted by default but can be persisted (like a table) using the result_table_persist option. Aggregate unpivot in-memory tables can be created from any table or view.

The aggregate unpivot in-memory table will be sharded if the shard key is not part of the pivoted_columns parameter. The aggregate unpivot in-memory table will be replicated if the source table or view is replicated.

Several limitations are discussed in further detail in the Limitations section.

Creating an Aggregate Unpivot In-Memory Table

To create an aggregate unpivot in-memory table using the /aggregate/unpivot endpoint requires six parameters:

1. the name of the data set to create the aggregate unpivot in-memory table from
2. the new column that will host the column headers of the pivoted columns from the source data set
3. the new column that will host the values of the pivoted columns from the source data set
4. the pivoted columns in the source data set
5. the type of encoding to use
6. the name of the in-memory table to store the results in

Examples

Given source table customers that lists the phone numbers for a customer in the database, an aggregate unpivot in-memory table can be created in Python like so:

gpudb.aggregate_unpivot(
  table_name = "customers",
  variable_column_name = "phone_type",
  value_column_name = "phone_num",
  pivoted_columns = ["phone1", "phone2", "phone3"],
  encoding = "json",
  options = {
    "result_table":"customer_phone_nums"
  }
)

Creating a persisted aggregate unpivot table in Python using a students table that lists a student's ID and grades per quarter:

gpudb.aggregate_unpivot(
  table_name = "students",
  variable_column_name = "quarter",
  value_column_name = "grade",
  pivoted_columns = ["q1", "q2", "q3", "q4"],
  encoding = "json",
  options = {
    "result_table":"student_grades",
    "result_table_persist":"true"
  }
)

Limitations and Cautions

There are a few limitations and cautions when creating and using aggregate unpivot in-memory tables:

• Creating an aggregate unpivot in-memory table results in an entirely new data set, so be mindful of the memory usage implications
• The column types for the pivoted_columns must match, e.g., int8 to int8, char4 to char4, etc.
• An aggregate unpivot in-memory table cannot be created using a non-charN string column type
• An aggregate unpivot in-memory table cannot be created using a store-only column type
• If an aggregate unpivot in-memory table is created and its source data set's rows are updated (or removed), the in-memory table will not be updated to reflect the changes in the source data set
• An aggregate unpivot in-memory table cannot be created from a collection
• An aggregate unpivot in-memory table has no permanence by default because it is not protected and will expire after the default TTL setting