Live aggregation with legacy table

MS440 · April 2015

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?

NorbertK · April 2015

Are you missing a GROUP BY in your original statement?

MS440 · April 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 · April 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 · April 2015

Thanks!

Live aggregation with legacy table

Comments

Leave a Comment