Vertica Array and Row types

You can manipulate Vertica Array and Row datatypes in Semarchy xDI, and use them in data flows.

Design and reverse-engineering

Design Vertica Array and Row datatypes in Vertica metadata objects. They can be automatically added when reverse-engineering tables, or you can manually design them in. For more information, see the Getting started page.

Read data

To read data inside Vertica Array and Row datatypes:

  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.

  5. Run your mapping.

Write data

The Semarchy xDI Vertica component supports writing to Array and Row types in a mapping.

Requirements

Writing to Array and Row types has four requirements:

  • A repetition key: to populate an array, you must map the item column after the array as a key. Designer creates an array entry for each value of this repetition key.

  • An update key: you must define at least one column not belonging to an array as a key. This is 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 populated level.

  • You must use an Append Integration strategy.

Limitations

  • Arrays with complex types, such as nested Arrays or Arrays containing Rows, are not supported.

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

When mapping at least one column of a Row, xDI sets any unmapped column of the Row to NULL.