CREATE PROJECTION (Live Aggregate Projections)

Creates metadata for live aggregate projections in the Vertica catalog. Top-K projections are a type of live aggregate projection.

Information here focuses on creating live aggregate projections. For details about creating other types of projections, including projections with expressions, see CREATE PROJECTION.

Syntax

CREATE PROJECTION [ IF NOT EXISTS ] [[database.]schema.]projection
...[ ( 
........{ projection-col | grouped-clause
......... [ ENCODING encoding-type ] 
......... [ ACCESSRANK integer ] 
........} [,…] 
...) ]
AS SELECT {table-col | expr-with-table-cols }[,…] FROM [[database.]schema.]table [ [AS] alias]
... GROUP BY column-expr
... [ KSAFE [ k-num ] ]

CREATE PROJECTION [ IF NOT EXISTS ] projection-name
...[ ( 
........{ projection-col | grouped-clause
......... [ ENCODING encoding-type ] 
......... [ ACCESSRANK integer ] 
........} [,…] 
.....)
...]
AS SELECT {table-col | expr-with-table-cols } [,…] FROM [[database.]schema.]table [ [AS] alias]
... LIMIT num-rows OVER (PARTITION BY column-expr ORDER BY column-expr)
... [ KSAFE [ k-num ] ]

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.

[database.]schema Specifies the schema for this projection and its anchor table, where schema must be the same for both. If you specify a database, it must be the current database.
projection

Identifies the projection to create, where projection conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.
projection‑col

The name of a projection column.

If you do not specify projection column names, Vertica uses the anchor table column names 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 be included in the projection. If you specify projection column names, the two lists of projection columns and table columns/expressions must exactly match in number and order.
FROM table [ [AS] ALIAS]

Specifies the projection's anchor table, optionally qualified by an alias.

GROUP BY column‑expr[,…]

One or more column expressions from the SELECT list. The first column-expr must be the first column expression in the SELECT list, the second column-expr must be the second column expression in the SELECT list, and so on.
LIMIT num‑rows The number of rows to return from the specified partition.
OVER (PARTITION BY column‑expr [,…] Specifies window partitioning by one or more column expressions from the SELECT list. The first column-expr is the first column expression in the SELECT list, the second column-expr is the second column expression in the SELECT list, and so on.
ORDER BY column-expr [,…] [ASC | DESC]

The order in which the top k rows are returned, by default in ascending (ASC) order. All column expressions must be from the SELECT list, where the first column-expr must be the first column expression in the SELECT list to follow the last PARTITION BY column expression.

Top-K projections support ORDER BY NULLS FIRST/LAST.
KSAFE [ k‑num ]

Specifies K-safety for the projection, where k-num must be equal to or greater than system K-safety. Vertica ignores this parameter if set for unsegmented projections. If you omit k-num, Vertica uses system K-safety.

For general information, see K-Safety in an Enterprise Mode Database in Vertica Concepts.

Privileges

Superuser, or the following:

  • Anchor table owner
  • CREATE privilege on the schema

Requirements and Restrictions

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

For other requirements and restrictions, see:

Examples

See: