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

Question

question

scherian_188962 asked Oct 22, '20 scherian_188962 commented Oct 26, '20

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

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

Answer 1 · 2020-10-23T20:31:02Z

smadhavan answered Oct 23, '20 scherian_188962 commented Oct 26, '20

@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 Share

10 |1000 characters needed characters left characters exceeded

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

scherian_188962 · Oct 26, 2020 at 03:16 PM

thanks for the answer, this seems like a feasible workaround

1 ·

Answer 2 · 2020-10-24T06:01:03Z

Erick Ramirez answered Oct 24, '20 scherian_188962 commented Oct 26, '20

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 Share

10 |1000 characters needed characters left characters exceeded

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

scherian_188962 · Oct 26, 2020 at 03:13 PM

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 ·

question