question

scherian_188962 avatar image
scherian_188962 asked scherian_188962 commented

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
Toggle Comment visibility. Current Visibility: Viewable by all users

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

2 Answers

smadhavan avatar image
smadhavan answered scherian_188962 commented

@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
Toggle Comment visibility. Current Visibility: Viewable by all users

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

scherian_188962 avatar image scherian_188962 ·

thanks for the answer, this seems like a feasible workaround

1 Like 1 ·
Erick Ramirez avatar image
Erick Ramirez answered scherian_188962 commented

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
Toggle Comment visibility. Current Visibility: Viewable by all users

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

scherian_188962 avatar image scherian_188962 ·

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 ·