The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

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

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...

 

 

Comments

  • SruthiASruthiA Employee

    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

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

     

     

    Regards

    Mahesh

     

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.