Create Vertica Projection ( Segmented and Unsegmented)

For the below example :
The cluster has 3 nodes .

Sales table has 101 rows.

System k-Safety is 1.

Creating a projection( Segmented):

DROP PROJECTION IF EXISTS sales_p2;

CREATE PROJECTION sales_p2(code,amount,sales_date)

AS

SELECT code,amount,sales_date

FROM sales

ORDER BY sales_date

SEGMENTED BY hash(code)  

All Nodes offset 0 KSAFE ;

SEGMENTED BY hash(code)  -Choose the segmentation column with large number of unique values like primary key .

All Nodes will distribute the data evenly across ALL Nodes.

Offset 0 means segmentation will start from Node 0.
KSAFE is required when the projection is segmented. KSAFE without any k-num defaults to system K-Safety number.

Refresh Projection:

SELECT refresh('sales');

Query to analyse the data distribution:

SELECT ps.node_name,p.projection_name, row_count

FROM projection_storage ps JOIN projections p

ON p.projection_name=ps.projection_name

WHERE ps.anchor_table_name='sales' AND p. projection_basename='sales_p2'

ORDER BY p.projection_name ,ps.node_name;

As k-safe = 1, System creates 2 projections ( postfixed with _b0 and _b1 )

Below image demonstrates the concept of buddy projection.

Creating a projection( Unsegmented):

DROP PROJECTION IF EXISTS sales_p1;

CREATE PROJECTION sales_p1 (code, amount, product_id, sales_date)

AS (

SELECT code, amount,product_id,sales_date

FROM sales

ORDER BY sales_date

)

UNSEGMENTED ALL NODES;

Select refresh ('sales');

Unsegmented Projection will create replication of the projection across mentioned Nodes.