CREATE PROJECTION

Creates metadata for a projection in the Vertica catalog.

For detailed information about using CREATE PROJECTION to create live aggregate projections and Top-K projections, see CREATE PROJECTION (Live Aggregate Projections). To create live aggregate projections that support user-defined transform functions, see CREATE PROJECTION (UDTFs).

Syntax

CREATE PROJECTION [ IF NOT EXISTS ] [[database.]schema.]projection
[ ( 
     { projection-col | grouped-clause
          [ ENCODING encoding-type ] 
          [ ACCESSRANK integer ]  
        }[,…] 
   ) ]
AS SELECT 
   select‑list FROM [[database.]schema.]table [ [AS] alias]
   [ ORDER BY column-expr[,…] ]
   [ segmentation-spec ]
   [ 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.

If the projection is segmented, Vertica uses this string as the projection base name when it creates unique identifiers for buddy projections. For more information, see Projection Naming in the Administrator's Guide.

projection‑col

The name of a projection column. The list of projection columns must match the select‑list columns and expressions in number, type, and sequence.

If projection column names are omitted, Vertica uses the anchor table column names specified in select‑list.

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.

select‑list

Specifies the columns or column expressions to select from the specified table, one of the following:

  • * (asterisk)

    Lists all columns in the queried tables.

  • expression [ [AS] output‑name] }[,…]

    A table column or column expression to select from the queried tables. You can optionally qualify expression with an output name, which can be used in two ways:

    • Label the column for display.
    • Refer to the column in the projection's ORDER BY clause.
FROM table [ [AS] ALIAS]

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

ORDER BY

Specifies columns from the SELECT list on which to sort the projection. The ORDER BY Clause cannot include qualifiers ASC or DESC. Vertica always stores projection data in ascending sort order.

If you order by a column with a collection data type (ARRAY or SET), queries that use that column in an ORDER BY clause perform the sort again. This is because projections and queries perform the ordering differently.

If you omit the ORDER BY clause, Vertica uses select‑list to sort the projection.

segmentation‑spec

Specifies how to distribute projection data with one of the following clauses:

  • hash‑segmentation‑clause: Specifies to segment projection data evenly and distribute across cluster nodes:
    SEGMENTED BY expression ALL NODES [ OFFSET offset ]
  • unsegmented‑clause: Specifies to create an unsegmented projection:
    UNSEGMENTED ALL NODES

If the anchor table and projection both omit specifying segmentation, the projection is defined with a hash segmentation clause that includes all columns in the SELECT list , as follows:

SEGMENTED BY HASH(column‑expr[,…]) ALL NODES OFFSET 0;

Vertica recommends segmenting large tables.

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

  • To prevent data loss and inconsistencies, tables must have at least one superprojection. You cannot drop a projection if that projection is the table's only superprojection.
  • You cannot drop a buddy projection if dropping that projection violates system K-safety.

See Also

Working with Projections