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)
Creating Live Aggregate Projection
Moderator: NorbertKrupa
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: Creating Live Aggregate Projection
How many nodes do you have? Are all your nodes up?
Checkout vertica.tips for more Vertica resources.
Re: Creating Live Aggregate Projection
Try adding KSAFE or KSAFE 1 at the end of the CREATE PROJECTION statement.
—Sharon
—Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC
Vertica Consultant, Zazz Technologies LLC
Re: Creating Live Aggregate Projection
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
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
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: Creating Live Aggregate Projection
What version are you on?
Checkout vertica.tips for more Vertica resources.
Re: Creating Live Aggregate Projection
vsql --version
vsql version 07.02.0000, built for Linux64, contains support for command-line editing
vsql version 07.02.0000, built for Linux64, contains support for command-line editing
Re: Creating Live Aggregate Projection
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
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
Vertica Consultant, Zazz Technologies LLC