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.

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.

Example:

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.