ERROR 3586: Insufficient projections to answer query--Live agg projection

Mahesh_Benne

I am creating live agg projection on 40 node cluster. i am getting follwoing error.. but no error in single node cluster

here is the steps i followed

CREATE TABLE stg.clicks(
   user_id INTEGER,
   page_id INTEGER,
   click_time TIMESTAMP NOT NULL);

CREATE PROJECTION stg.clicks_anchorp AS
   SELECT * FROM stg.clicks SEGMENTED BY HASH(user_id) ALL NODES KSAFE 1;


 INSERT /*+direct*/ INTO stg.clicks
   VALUES(1, 10001, '2010-10-10 10:10:10'::TIMESTAMP);
   INSERT /*+direct*/ INTO stg.clicks
   VALUES(1, 10001, '2010-10-10 10:10:10'::TIMESTAMP);
   INSERT /*+direct*/ INTO stg.clicks
   VALUES(2, 2002, '2011-10-10 10:10:10'::TIMESTAMP);
   INSERT /*+direct*/ INTO stg.clicks
   VALUES(2, 2002, '2011-10-10 10:10:10'::TIMESTAMP);
   INSERT /*+direct*/ INTO stg.clicks
   VALUES(3, 3003, '2012-10-10 10:10:10'::TIMESTAMP);
   INSERT /*+direct*/ INTO stg.clicks
   VALUES(3, 3003, '2012-10-10 10:10:10'::TIMESTAMP);
   INSERT /*+direct*/ INTO stg.clicks
   VALUES(3, 3003, '2012-10-10 10:10:10'::TIMESTAMP);
commit;

CREATE PROJECTION stg.clicks_agg
   AS SELECT user_id, page_id, click_time::DATE click_date,
   COUNT(*) num_clicks FROM stg.clicks
   GROUP BY user_id, page_id, click_time::DATE;

select START_REFRESH();

SELECT * FROM stg.clicks_agg;
SELECT * FROM stg.clicks_agg ORDER BY user_id;

 

Here is the error on 40 node cluster..

vertica=> SELECT * FROM stg.clicks_agg ORDER BY user_id;
ERROR 3586:  Insufficient projections to answer query
DETAIL:  No projections eligible to answer query
HINT:  Projection clicks_agg not used in the plan because the projection is not safe.
vertica=>

 

 

Please help me on this...

 

 

SruthiA

Hi,

 

     Please create a k-safe projection and try executing the query

 

CREATE PROJECTION stg.clicks_agg
   AS SELECT user_id, page_id, click_time::DATE click_date,
   COUNT(*) num_clicks FROM stg.clicks
   GROUP BY user_id, page_id, click_time::DATE ksafe 1;

 

 

Thank you,

 Sruthi

Mahesh_Benne

Thanks for your help..it worked now..now i need to implment it in production...

 

 

Regards

Mahesh