Creating a New Projection with CREATE PROJECTION Command
Create a new projection using the CREATE PROJECTION command:
CREATE PROJECTION projection-name ( columns-and-encodings ) AS SELECT columns FROM tables [ WHERE join-predicates ] [ ORDER BY columns ] [ hash-segmentation-clause | range-segmentation-clause | UNSEGMENTED ALL NODES ] [ KSAFE [ k-num ] ]
In this command, you can define the list of columns included in the projection, optionally with encoding information. You can specify the sort criteria as a SELECT query in the ORDER BY clause.
Unlike a materialized view, a projection is intended to hold the raw table data. For this reason, the SELECT queries in projection definitions cannot include complex transformations, aggregations, or analytic functions.
You can omit the KSAFE parameter or specify it without a value. In this case, the projection is created with the current system level of K-safety. Unsafe projections are not permitted.
For every projection created using the CREATE PROJECTION command, one of the following must be true:
- The projection is replicated (by specifying UNSEGMENTED ALL NODES).
- The projection is segmented and created with KSAFE 1 or higher.
CREATE PROJECTION customer_p AS SELECT id, name_first, name_last FROM customer ORDER BY id SEGMENTED BY HASH(id) ALL NODES KSAFE 1
When you create a projection, the anchor table is automatically refreshed, and the projection gets updated.