CREATE PROJECTION

Creates metadata for a projection in the Vertica catalog.

Note: 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 ] 
...projection-name 
...[ ( 
........{ projection-col | grouped-clause
......... [ ENCODING encoding-type ] 
......... [ ACCESSRANK integer ]  
........}[,...] 
...) ]
AS SELECT 
...select‑list from-clause
...[ 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`.
`projection‑name`	The name of the projection to create. The projection is created in the same schema as the anchor table. 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 one or more tables, 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-clause	A comma-separated list of data sources to query.
`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 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. Vertica recommends segmenting large tables. unsegmented‑clause: Specifies to create an unsegmented projection. 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;
`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. Vertica sets projection K-safety as follows: `KSAFE` and `OFFSET` clause omitted: Uses system K-safety. `OFFSET` clause is omitted and `KSAFE` is specified: Uses `KSAFE` setting. `KSAFE` is omitted and the `OFFSET` clause is specified: Uses `OFFSET` setting. If the `CREATE PROJECTION` statement specifies `KSAFE` and the `OFFSET` clause, Vertica returns an error. For general information, see K-Safety in Vertica Concepts.

Privileges

See Projection Privileges.

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.

Creating Projections with Expressions

The following example shows a projection that calculates the product of two numbers. The anchor table is defined as follows:

=> CREATE TABLE values (a INT, b INT);

To create a projection that calculates the product of a and b, use a statement like the following:

=> CREATE PROJECTION values_product (a, b, product_value) 
   AS SELECT a, b, a*b FROM values 
   SEGMENTED BY HASH(a) ALL NODES KSAFE;

To query that projection, you must use the name that Vertica assigned to it:

=> SELECT * FROM values_product_b0;

=> SELECT * FROM values_product_b1;

Grouping Correlated Columns

The following example shows how to group highly correlated columns bid and ask. The stock column is stored separately.

=> CREATE TABLE trades (stock CHAR(5), bid INT, ask INT);
=> CREATE PROJECTION tradeproj (stock ENCODING RLE, 
   GROUPED(bid ENCODING DELTAVAL, ask)) 
   AS (SELECT * FROM trades) KSAFE 1;

The following example show how to create a projection that uses expressions in the column definition. The projection contains two integer columns a and b, and a third column product_value that stores the product of a and b:

=> CREATE TABLE values (a INT, b INT=> CREATE PROJECTION product (a, b, product_value) AS 
   SELECT a, b, a*b FROM values ORDER BY a KSAFE;