We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Live aggregation with legacy table — Vertica Forum

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