Re: How to query TTL on collections ?

[Maxim Parkachov]

Hi Alain,

thanks a lot for detailed answer.


> You can set values individually in a collection as you did above (and
> probably should do so to avoid massive tombstones creation), but you have
> to read the whole thing at once:
>

This, actually, is one of the design goals. At the moment I have two
(actually more) "normalised" tables, which have data as separate columns.
But the use case, actually, requires to read all items every time product
is queried, thus move to collection to reduce amount of queries. Moreover,
items in collection is append only and expires using TTL. Map seem to be
excellent fit for that.


> Furthermore, you cannot query the TTL for a single item in a collection,
> and as distinct columns can have distinct TTLs, you cannot query the TTL
> for the whole map (collection). As you cannot get the TTL for the whole
> thing, nor query a single item of the collection, I guess there is no way
> to get the currently set TTL for all or part of a collection.
>

Yes, this is unfortunate. I could not find a way to query individual
element of collection and TTL for individual element, thanks for confirming
that this is not possible.


> Another idea of hack I'm just thinking about could be to add a 'ttl' field
> that would get the updates as well, any time a client updates the TTL for
> an entry, you could update that 'ttl' field as well. But again, you would
> still not be able to query this information only for an item or a few, it
> would be querying the whole map again.
>

This is, actually, very good idea, maybe adding field something like
"expires_at" will solve my problem. I received another advise to do it.

For one off query, it is possible to get TTL by finding corresponding
sstable and using sstabledump, it shows all information including TTL, but
this is very cumbersome.

Regards,
Maxim.

P.S. Your company's blog is excellent.

Re: How to query TTL on collections ?

[Alain RODRIGUEZ]

Hello Maxim.

I think you won't be able to do what you want this way. Collections are
supposed to be (ideally small) sets of data that you'll always read
entirely, at once. At least it seems to be working this way. Not sure about
the latest versions, but I did not hear about new design for collections.

You can set values individually in a collection as you did above (and
probably should do so to avoid massive tombstones creation), but you have
to read the whole thing at once:

```
$ ccm node1 cqlsh -e "SELECT items[10] FROM tlp_labs.products WHERE
product_id=1;"
:1:SyntaxException: line 1:12 no viable alternative at input '['
(SELECT [items][...)

$ ccm node1 cqlsh -e "SELECT items FROM tlp_labs.products WHERE
product_id=1;"
 items

 {10: {csn: 100, name: 'item100'}, 20: {csn: 200, name: 'item200'}}
```

Furthermore, you cannot query the TTL for a single item in a collection,
and as distinct columns can have distinct TTLs, you cannot query the TTL
for the whole map (collection). As you cannot get the TTL for the whole
thing, nor query a single item of the collection, I guess there is no way
to get the currently set TTL for all or part of a collection.

If you need it, you would need to redesign this table, maybe split it. Make
the collection a different table for example, that you would then be
referenced in your current table.
Another idea of hack I'm just thinking about could be to add a 'ttl' field
that would get the updates as well, any time a client updates the TTL for
an entry, you could update that 'ttl' field as well. But again, you would
still not be able to query this information only for an item or a few, it
would be querying the whole map again.

I had to test it because I could not remember about this, and I think my
observations are making sense. Sadly, there is no 'good' syntax for this
query, it's just not permitted at all I would say. Sorry I have no better
news for you :).

C*heers,
---
Alain Rodriguez - al...@thelastpickle.com
France / Spain

The Last Pickle - Apache Cassandra Consulting
http://www.thelastpickle.com

Le mer. 19 juin 2019 à 09:21, Maxim Parkachov  a
écrit :

> Hi everyone,
>
> I'm struggling to understand how can I query TTL on the row in collection
> ( Cassandra 3.11.4 ).
> Here is my schema:
>
> CREATE TYPE item (
>   csn bigint,
>   name text
> );
>
> CREATE TABLE products (
>   product_id bigint PRIMARY KEY,
>   items map>
> );
>
> And I'm creating records with TTL like this:
>
> UPDATE products USING TTL 10 SET items = items + {10: {csn: 100, name:
> 'item100'}} WHERE product_id = 1;
> UPDATE products USING TTL 20 SET items = items + {20: {csn: 200, name:
> 'item200'}} WHERE product_id = 1;
>
> As expected first records disappears after 10 seconds and the second after
> 20. But if I already have data in the table I could not figure out how to
> query TTL on the item value:
>
> SELECT TTL(items) FROM products WHERE product_id=1;
> InvalidRequest: Error from server: code=2200 [Invalid query]
> message="Cannot use selection function ttl on collections"
>
> SELECT TTL(items[10]) FROM products WHERE product_id=1;
> SyntaxException: line 1:16 mismatched input '[' expecting ')' (SELECT
> TTL(items[[]...)
>
> Any tips, hints, tricks are highly appreciated,
> Maxim.
>

How to query TTL on collections ?

[Maxim Parkachov]

Hi everyone,

I'm struggling to understand how can I query TTL on the row in collection (
Cassandra 3.11.4 ).
Here is my schema:

CREATE TYPE item (
  csn bigint,
  name text
);

CREATE TABLE products (
  product_id bigint PRIMARY KEY,
  items map>
);

And I'm creating records with TTL like this:

UPDATE products USING TTL 10 SET items = items + {10: {csn: 100, name:
'item100'}} WHERE product_id = 1;
UPDATE products USING TTL 20 SET items = items + {20: {csn: 200, name:
'item200'}} WHERE product_id = 1;

As expected first records disappears after 10 seconds and the second after
20. But if I already have data in the table I could not figure out how to
query TTL on the item value:

SELECT TTL(items) FROM products WHERE product_id=1;
InvalidRequest: Error from server: code=2200 [Invalid query]
message="Cannot use selection function ttl on collections"

SELECT TTL(items[10]) FROM products WHERE product_id=1;
SyntaxException: line 1:16 mismatched input '[' expecting ')' (SELECT
TTL(items[[]...)

Any tips, hints, tricks are highly appreciated,
Maxim.