Vertica Array and Row types

Vertica Array and Row data types can be manipulated in Semarchy xDI and proceed in the data flows.

Design and Reverse Engineering

Vertica Array and Row data types are designed in the Vertica metadata. They are automatically reverse engineered with the tables or can be manually designed. See Getting Started with Vertica database.

Read data

To read data within Vertica Array and Row types:

  1. Create a mapping.

  2. Add the Vertica table containing Array and Row types as the source.

  3. Add any target datastore.

  4. Map the source columns to the target columns.

Finally, run the mapping.

Write data

The Semarchy xDI Vertica component supports writing to Array and Row types in a mapping. It only supports an Append Integration strategy.

Requirements

  • Repetition Key: To populate an array, the item column below the array has to be mapped as a key. An entry in the array will be created for each value of this Repetition Key.

  • Update Key: At least one column not belonging to an array must be defined as a key. This is used to uniquely identify a record in the target table.

  • At least one column of each source must appear in at least one target field (even in a SQL comment). This allows the optimizer to detect the required datastores for each level having to be populated.

Limitations

  • Arrays with complex types are not supported (i.e. Arrays containing other Arrays or containing Rows).

  • Aggregations are not supported in arrays. If needed, one or more stages can be used to pre-compute the data.

when at least a column of a Row is mapped, any unmapped column of this Row will be set to NULL.