Types of UDxs

Vertica supports five types of user-defined extensions:

  • User-Defined Aggregate Functions (UDAF) allow you to create custom Aggregate Functions specific to your needs. They read one column of data, and return one output column. UDAFs can be developed in C++. Developing UDAFs is described in User‑Defined Aggregate Functions.
  • User-Defined Analytic Functions (UDAnF) are similar to UDSFs, in that they read a row of data and return a single row. However, the function can read input rows independently of outputting rows, so that the output values can be calculated over several input rows. UDAnFs can be developed in C++ and Java. Developing UDAnFs is described in Analytic Functions (UDAnFs).
  • User-Defined Scalar Functions (UDSFs) take in a single row of data and return a single value. These functions can be used anywhere a native function can be used, except CREATE TABLE BY PARTITION and SEGMENTED BY expressions. UDSFs can be developed in C++, Python, Java, and R. Developing UDSFs is described in Scalar Functions (UDSFs).

  • User-Defined Transform Functions (UDTFs) operate on table partitions (as specified by the query's OVER() clause) and return zero or more rows of data. The data they return can be an entirely new table, unrelated to the schema of the input table, including having its own ordering and segmentation expressions. They can only be used in the SELECT list of a query. UDTFs can be developed in C++, Python, Java, and R. Developing UDTFs is described in Transform Functions (UDTFs).

    To optimize query performance, you can use live aggregate projections to pre-aggregate the data that a UDTF returns. For more information, see Pre-Aggregating UDTF Results.

  • User-Defined Load allows you to create custom sources, filters, and parsers to load data. These extensions are used in the COPY statement. UDLs can be developed in C++, Java and Python. Developing UDLs is described in Load (UDLs).

While each UDx type has a unique base class, developing them is similar in many ways. Different UDx types can also share the same library.