Skip to main content

Live aggregation with legacy table

  • April 15, 2015
  • 4 replies
  • 23 views
MS440

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?

 

 

 

 

 

    4 replies

    NorbertK
    Forum|alt.badge.img
    • NorbertK
    • Participating Frequently
    • April 15, 2015

    Are you missing a GROUP BY in your original statement?

    MS440
    • MS440
    • Author
    • New Participant
    • April 15, 2015

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

    NorbertK
    Forum|alt.badge.img
    • NorbertK
    • Participating Frequently
    • April 15, 2015

    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)
    MS440
    • MS440
    • Author
    • New Participant
    • April 16, 2015

    Thanks!

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement