Creating Live Aggregate Projection

Moderator: NorbertKrupa

Timbo
Intermediate
Intermediate
Posts: 52
Joined: Thu Jun 21, 2012 11:05 am
Location: London, UK

Creating Live Aggregate Projection

Post by Timbo » Thu Nov 19, 2015 6:22 pm

Hi,
Playing with Live Aggregate Projection for the first time and failed at the first hurdle, is it obvious what I'm doing wrong:-

CREATE PROJECTION point_max_lastupdated_agg AS
SELECT instrument_id, element_id ,max(last_updated) AS max_timestamp FROM Phoenix1.Point
group by instrument_id, element_id;

vsql:cre_point_max_lastupdated_agg.sql:3: WARNING 6852: Live Aggregate Projection "point_max_lastupdated_agg" will be created for "point". Data in "point" will be neither updated nor deleted
vsql:cre_point_max_lastupdated_agg.sql:3: WARNING 4468: Projection <PHOENIX1.point_max_lastupdated_agg> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
CREATE PROJECTION
select refresh('PHOENIX1.point');
refresh
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Refresh completed with the following outcomes:
Projection Name: [Anchor Table] [Status] [Refresh Method] [Error Count] [Duration (sec)]
----------------------------------------------------------------------------------------
"PHOENIX1"."point_max_lastupdated_agg": [point] [failed: projection is unsafe] [] [1] [0]

(1 row)


The projection creates ok, but won't refresh as the projection is unsafe!

The projections on the point table are safe:-
SELECT MARK_DESIGN_KSAFE(1);
MARK_DESIGN_KSAFE
----------------------
Marked design 1-safe
(1 row)

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Creating Live Aggregate Projection

Post by NorbertKrupa » Thu Nov 19, 2015 6:27 pm

How many nodes do you have? Are all your nodes up?
Checkout vertica.tips for more Vertica resources.

scutter
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: Creating Live Aggregate Projection

Post by scutter » Thu Nov 19, 2015 7:56 pm

Try adding KSAFE or KSAFE 1 at the end of the CREATE PROJECTION statement.

—Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

Timbo
Intermediate
Intermediate
Posts: 52
Joined: Thu Jun 21, 2012 11:05 am
Location: London, UK

Re: Creating Live Aggregate Projection

Post by Timbo » Fri Nov 20, 2015 9:12 am

All nodes up ok, everything is currently KSafe.

Tried the following syntax, but no luck:-
CREATE PROJECTION point_max_lastupdated_agg AS
SELECT instrument_id, element_id ,max(last_updated) AS max_timestamp FROM Phoenix1.Point
group by instrument_id, element_id
ALL NODES KSAFE;
vsql:cre_point_max_lastupdated_agg.sql:4: ERROR 4856: Syntax error at or near "ALL" at character 172
vsql:cre_point_max_lastupdated_agg.sql:4: LINE 4: ALL NODES KSAFE;
vsql:cre_point_max_lastupdated_agg.sql:4: ^
select refresh('PHOENIX1.point');

CREATE PROJECTION point_max_lastupdated_agg AS
SELECT instrument_id, element_id ,max(last_updated) AS max_timestamp FROM Phoenix1.Point
group by instrument_id, element_id
SEGMENTED BY MODULARHASH (instrument_id, element_id) ALL NODES KSAFE;
ERROR 6394: SEGMENTED BY / UNSEGMENTED is not allowed in aggregate projection. The aggregate projection is automatically segmented on group by columns

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Creating Live Aggregate Projection

Post by NorbertKrupa » Fri Nov 20, 2015 4:01 pm

What version are you on?
Checkout vertica.tips for more Vertica resources.

Timbo
Intermediate
Intermediate
Posts: 52
Joined: Thu Jun 21, 2012 11:05 am
Location: London, UK

Re: Creating Live Aggregate Projection

Post by Timbo » Fri Nov 20, 2015 4:53 pm

vsql --version
vsql version 07.02.0000, built for Linux64, contains support for command-line editing

scutter
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: Creating Live Aggregate Projection

Post by scutter » Fri Nov 20, 2015 7:23 pm

Use just KSAFE at the end of the definition, not ALL NODES KSAFE.

Btw - on the vertica version number check, use “select version()”. You posted your vsql client version, which doesn’t necessarily match the server version.

—Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

Post Reply

Return to “Vertica Performance Tuning”