Bringing together the Apache Cassandra experts from the community and DataStax.

Want to learn? Have a question? Want to share your expertise? You are in the right place!

Not sure where to begin? Getting Started

 

question

scherian_188962 avatar image
scherian_188962 asked ·

How do I set a TTL for a table with counters?

the use case is, I want to maintain an aggregated data about customers doing txns on a daily basis

model is

CREATE TABLE compute_by_customer(
    customer_id text,
    txn_date date,
    txn_amount counter,
    txn_count counter,
    PRIMARY KEY (customer_id,txn_date)
);

The application queries deal with current_date and recent dates of customer transactions, hence I do not want to maintain records that are past 30days.

Since txndate is associated, there will never be a case where a past dated txn can be performed by a customer, hence information about a particular record remains intact after the date is passed.

How can I set TTL or the workaround in this scenario?

counter
10 |1000 characters needed characters left characters exceeded

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

Erick Ramirez avatar image
Erick Ramirez answered ·

TTLs are not supported for tables with counters because of the inherent problems with how to merge an expired and not-yet expired counter values in SSTables. This is a very difficult problem to solve in a distributed environment where nothing is shared.

At this point, the only supported operations on counters are:

  • increment
  • decrement
  • delete

For obsolete transactions, my recommendation is that you add a logic in your application to delete them. Cheers!

1 comment Share
10 |1000 characters needed characters left characters exceeded

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

seems like a good workaround

but the delete operation also the partition key, we will have to first get hold of all the partition keys to perform the operation which would not be feasible

0 Likes 0 · ·
smadhavan avatar image
smadhavan answered ·

@scherian_188962, I am going to answer this question partially here.

First, Time-To-Live (TTL) is not supported on counter columns. Also, we will not be able to insert records into this table USING TIMESTAMP. What does that mean?

If we try to execute the below it would get us an exception like as follows,

CREATE TABLE compute_by_customer(
customer_id text,
txn_date date,
txn_amount counter,
txn_count counter,
PRIMARY KEY (customer_id,txn_date)
) with default_time_to_live = 600;
InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot set default_time_to_live on a table with counters"

One way to workaround here would be to modify the application to calculate the count and leverage it using a non-counter column as mentioned here leveraging LWT/CAS.

Additional Resources on counter table and expiring data:

1 comment Share
10 |1000 characters needed characters left characters exceeded

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

thanks for the answer, this seems like a feasible workaround

1 Like 1 · ·