Creating Live Aggregate Projections
You define a live aggregate projection with the following syntax:
=> CREATE PROJECTION proj-name AS SELECT select-expression FROM table GROUP BY group-expression;
For full syntax options, see CREATE PROJECTION.
=> CREATE PROJECTION clicks_agg AS SELECT page_id, click_time::DATE click_date, COUNT(*) num_clicks FROM clicks GROUP BY page_id, click_time::DATE KSAFE 1;
For an extended discussion, see Live Aggregate Projection Example.
The following requirements apply to live aggregate projections:
- The projection cannot be unsegmented.
- SELECT and GROUP BY columns must be in the same order. GROUP BY expressions must be at the beginning of the SELECT list.
The following restrictions apply to live aggregate projections:
- MERGE operations must be optimized if they are performed on target tables that have live aggregate projections.
- Live aggregate projections can reference only one table.
- Vertica does not regard live aggregate projections as superprojections, even those that include all table columns.
- You cannot modify the anchor table metadata of columns that are included in live aggregate projections—for example, a column's data type or default value. You also cannot drop these columns. To make these changes, first drop all live aggregate and Top-K projections that are associated with the table.
One exception applies: You can set and drop NOT NULL on columns that are included in a live aggregate projection.