Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Live aggregation with legacy table

I am trying to use count(*) in one of the projection's columns. Always getting "Cannot create... : no suitable anchor projection).

All the examples that I've been able to find are using count(column_name). In my case there are no numeric type columns in the table...

Here is what I tried:

 

-- Original query:

SELECT id, timestamp, count(*) as n from original_table; -- OK, but I want a projection


-- Creating anchor projection as per documentation:

CREATE PROJECTION original_table_a as select id, timestamp, 0 as n from original_table;
SELECT REFRESH(original_table);

SELECT MARK_DESIGN_KSAFE(0); -- I have a single node

-- Creating my target LAP:

CREATE PROJECTION original_table_lap 

AS SELECT id, timestamp, count(*) as n from original_table

GROUP BY id, timestamp;

 

At this point I'm getting the "... no suitable anchor projection" 

 

Any suggestions?

 

 

 

 

 

Comments

  • Are you missing a GROUP BY in your original statement?

  • Yes, that's a typo. There is a group by id, timestamp there...

     

    BTW, the anchor projection works and I am able to get the info from it, but I want to create a target projection that will have this count(*) as n column and am unable to do that due to "no suitable anchor"...

  • For some reason, Vertica doesn't like "timestamp" for a column name with Live Aggregate Projections:

     

     

    dbadmin=> CREATE TABLE original_table(
    dbadmin(> id INTEGER,
    dbadmin(> tmstmp TIMESTAMP NOT NULL);
    CREATE TABLE

    dbadmin=> CREATE PROJECTION original_table_anchor AS
    dbadmin-> SELECT * FROM original_table SEGMENTED BY HASH(id) ALL NODES KSAFE;
    CREATE PROJECTION

    dbadmin=> CREATE PROJECTION original_table_agg
    dbadmin-> AS SELECT id, tmstmp,
    dbadmin-> COUNT(*) row_count FROM original_table
    dbadmin-> GROUP BY id, tmstmp;
    WARNING 6138: Aggregate/Top-k projection "original_table_agg" will be created for "original_table". Data in "original_table" will be neither updated nor deleted
    WARNING 6138: Aggregate/Top-k projection "original_table_agg_b1" will be created for "original_table". Data in "original_table" will be neither updated nor deleted

    dbadmin=> COPY original_table FROM STDIN;
    Enter data to be copied followed by a newline.
    End with a backslash and a period on a line by itself.
    >> 1|2015-04-15 15:49:36.304669
    >> 1|2015-04-15 15:49:36.304669
    >> 1|2015-04-15 15:49:36.304669
    >> 2|2015-04-15 15:49:37.520158
    >> 2|2015-04-15 15:49:37.520158
    >> 3|2015-04-15 15:49:38.120493
    >> \.

    dbadmin=> SELECT id, tmstmp, row_count FROM original_table_agg;
    id | tmstmp | row_count
    ----+----------------------------+-----------
    1 | 2015-04-15 15:49:36.304669 | 3
    2 | 2015-04-15 15:49:37.520158 | 2
    3 | 2015-04-15 15:49:38.120493 | 1
    (3 rows)
  • Thanks!

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.