insert dummy data to Cassandra Counter table

Question

I know I cannot insert in counter table , but how to use update if the table is empty , the update statement requires k_where

I tried many update statement but without hope if any example to fix this will be highly appreciated

Answer 1

create table test_counter(a text primary key, v counter);

update test_counter set v = v + 1 where a = 'dummy';

select * from test_counter;

 dummy             | v
-------------------+---
 some_counter_name | 1

take into account that besides primary key column you may only have counter columns in the table that you define with counters. Also you always update the data, partition key is simply upserted.

If you want to put counter back to zero you have to read it's current value and then you have to set to v = v - read_value. Note that there might be additional concurrent updates.

Counters may sometimes be updated even if the client got a message the update failed so they are not 100% reliable.

After receiving the comments :) it looks like you are running into some sort of Cassandra naming bug. Here is my exploration of your problem:

I started with your original problem:

Update "QueueMetric"
    Set "TotalCompletedMessages" = TotalCompletedMesseages + 1
    where "QueueName" = 'Test Queue one'
    and "Time" = now(); 

So I created a simple table:

create table QueueMetric(
    QueueName text primary key,
    Time timeuuid,
    TotalCompletedMessages counter
)

InvalidRequest: code=2200 [Invalid query] message="Cannot mix counter and non counter columns in the same table"

Then I figured your create statement is something like:

create table QueueMetric(
    QueueName text,
    Time timeuuid,
    TotalCompletedMessages counter,
    primary key (QueueName, Time)
)

Update QueueMetric Set TotalCompletedMessages = TotalCompletedMesseages + 1 where QueueName='Test Queue one' and Time = now();

I get the same exception here ... I also tried with a predefined uuid instead of now

Your example also doesn't make sense because why would you cluster counters by timeuuid?

Thing is it's one time thing, even with 10000 req per second you would have to generate them for 100 years to get a collision. I think what you actually need is a timestamp.

So then I try to define the time column as string:

create table QueueMetric(
    QueueName text,
    Time text,
    TotalCompletedMessages counter,
    primary key (QueueName, Time)
)

Update QueueMetric Set TotalCompletedMessages = TotalCompletedMesseages + 1 where QueueName='Test Queue one' and Time = 'd14d44b2-e4d1-11e6-bf01-fe55135034f3';

I also failed with this one ...

so now my idea is that time is somehow wrong name

create table QueueMetric(
    QueueName text,
    Test text,
    TotalCompletedMessages counter,
    primary key (QueueName, Test)
);

 Update QueueMetric Set TotalCompletedMessages = TotalCompletedMesseages + 1 where QueueName='Test Queue one' and Test = 'd14d44b2-e4d1-11e6-bf01-fe55135034f3';

This also didn't work ... let's try with just partition key:

create table QueueMetric(
    QueueName text,
    Time timeuuid,
    TotalCompletedMessages counter,
    primary key ((QueueName, Time))
);

Update QueueMetric Set TotalCompletedMessages = TotalCompletedMesseages + 1 where QueueName='Test Queue one' and Time = now();

Now it's really getting strange ... I'll try with different name;

create table QueueMetric2(
    QueueName text,
    Time timeuuid,
    TotalCompletedMessages counter,
    primary key ((QueueName, Time))
);

Update QueueMetric2 Set TotalCompletedMessages = TotalCompletedMesseages + 1 where QueueName='Test Queue one' and Time = now();

ok it might be the time:

create table QueueMetric3(
    QueueName text,
    SomethingOther text,
    TotalCompletedMessages counter,
    primary key ((QueueName, SomethingOther))
);

Update QueueMetric3 Set TotalCompletedMessages = TotalCompletedMesseages + 1 where QueueName='Test Queue one' and SomethingOther = 'd14d44b2-e4d1-11e6-bf01-fe55135034f3';

It simply looks like there has to be primary key and column?

create table QueueMetric4(
    QueueNamePlusTimeUUID text,
    TotalCompletedMessages counter,
    primary key (QueueNamePlusTimeUUID)
);

Update QueueMetric4 Set TotalCompletedMessages = TotalCompletedMesseages + 1 where QueueName='Test Queue one d14d44b2-e4d1-11e6-bf01-fe55135034f3';

now I'm really confused, lowercase everything?

create table queuemetric5(
    queuenameplustimeUUID text primary key,
    totalcompletedmessages counter
);

update queuemetric5 set totalcompletedmessages = totalcompletedmessages + 1 where queuename='Test Queue one d14d44b2-e4d1-11e6-bf01-fe55135034f3';

This also doesn't work :)

create table abc(
    a text primary key,
    t counter
);

update abc set t = t + 1 where a='Test Queue one d14d44b2-e4d1-11e6-bf01-fe55135034f3';

and this totally works now :) it looks to me you are having some strange names actually let's try the same thing you had in the beginning but with different names:

create table hohoho(
    n text,
    o timeuuid,
    m counter,
    primary key (n, o)
);

update hohoho set m = m + 1 where n='Test Queue one' and o = now();

I guess we should file this as some sort of a bug ... it looks to me your problem is naming only :) Looks like Queue is something special in cql

:)