CREATE PROJECTION (UDTFs)

Creates metadata in the Vertica catalog for projections that invoke user-defined transform functions (UDTFs).

Important: Currently, live aggregate projections can only reference UDTFs that are developed in C++.

Syntax

CREATE PROJECTION [ IF NOT EXISTS ] projection-name
...[ ( 
........{ projection-col | grouped-clause
......... [ ENCODING encoding-type ] 
......... [ ACCESSRANK integer ] 
........} [,... ] 
...) ]
AS {
......batch‑query FROM { prepass-query sq‑results | table‑ref }
......| prepass-query
...}

Parameters

IF NOT EXISTS

Specifies to generate an informational message if an object already exists under the specified name. If you omit this option and the object exists, Vertica generates a ROLLBACK error message. In both cases, the object is not created.

The IF NOT EXISTS clause is useful for SQL scripts where you want to create an object if it does not already exist, and reuse the existing object if it does.

For related information, see ON_ERROR_STOP.

projection‑name

The name of the projection to create, where projection-name conforms to conventions described in Identifiers. Vertica creates the projection in the same schema as the anchor table.

projection‑column

The name of a projection column.

If you do not specify projection column names, Vertica uses the anchor table column names that are specified in the SELECT statement.

grouped‑clause

See GROUPED Clause.

ENCODING encoding‑type

Specifies the column encoding type, by default set to AUTO.

ACCESSRANK integer

Overrides the default access rank for a column. Use this parameter to increase or decrease the speed at which Vertica accesses a column. For more information, see Overriding Default Column Ranking.

table‑col
expr‑with‑table‑cols

A table column or expression of table columns to include in the projection.

batch-udtf(batch‑args)

The batch UDTF to invoke each time the following events occur:

  • Tuple mover mergeout
  • Queries on the projection
  • If invoked singly, on data load operations

Important: If the projection definition includes a pre-pass subquery, batch‑args must exactly match the pre-pass UDTF output columns, in name and order.

prepass-udtf(prepass‑args)

The pre-pass UDTF to invoke on each load operation such as COPY or INSERT.

If specified in a subquery, the pre-pass UDTF returns transformed data to the batch query for further processing. Otherwise, the pre-pass query results are added to projection data storage.

OVER (PARTITION BATCH BY partition‑col‑expr[,...]

Specifies the UDTF type and how to partition the data it returns:

In both cases, the OVER clause specifies partitioning with one or more column expressions from the SELECT list. The first partition‑col‑expr is the first column expression in the SELECT list, the second partition‑col‑expr is the second column expression in the SELECT list, and so on.

Note: The projection is implicitly segmented and ordered on PARTITION BY columns.

AS (batch‑output‑cols)
AS (prepass‑output‑cols)

Optionally names columns that are returned by the UDTF.

If a pre-pass subquery omits this clause, the outer batch query UDTF arguments (batch‑args) must reference the column names as they are defined in the pre-pass UDTF.

table‑ref

A schema table with the columns to include in the projection.

sq‑results Subquery result set that is returned to the outer batch UDTF.

Privileges

See Projection Privileges. The projection creator also must have EXECUTE privileges on all UDTFs that are referenced by the projection.

Restrictions

Vertica does not regard live aggregate projections as superprojections, even one that includes all table columns.

UDTF Types

CREATE PROJECTION can define live aggregate projections that invoke user-defined transform functions (UDTFs). Vertica invokes UDTFs at multiple points of projection processing:

Vertica stores all UDTF results in projection ROS containers, thereby enabling faster response time when you query the projection.

UDTF Specification Options

A projection definition can specify up to two UDTFs, in any of the following ways:

Examples

See Examples in Pre-Aggregating UDTF Results.

See Also

Pre-Aggregating UDTF Results