Get the last inserted data from Cassandra
We have a primary key in our table as UUID
. To get the latest entry, we order a field desc uuid
. However, since this field is not integer, we do not always get the last record. Is there a way to get the latest entry? Please note that we do not want to create cluster columns.
Here is my table
ArtistId (partition key) | SongId (primary key, uuid) | Song
--------------------------------------------
1 | 9a9fa429-c349-4137 | abc
1 | 9a9fa429-b349-6137 | cde
1 | 9a9ga429-a349-6132 | fgh
2 | 249ga429-a909-6542 | ijk
2 | 249kg429-a239-3652 | lmn
1 | i55oa429-a909-3462 | opq
1 | e4235k59-4ik9-6542 | rst
+3
source to share
1 answer
TL & DR: TimeUUID type cql3 to rescue!
If you have a db schema like this:
create table music (
artist_id int,
song_id timeuuid,
song text,
primary key (artist_id, song_id)
) with clustering order by (song_id desc);
Please note these changes to the original schema:
-
uuid
changed totimeuuid
Key - sorted in reverse order
The table contains some data:
artist_id | dateOf(song_id) | song
-----------+--------------------------+------
1 | 2015-05-29 13:25:15+0300 | baz
1 | 2015-05-29 13:25:11+0300 | bar
1 | 2015-05-29 13:25:06+0300 | foo
2 | 2015-05-29 13:25:19+0300 | qux
To get the latest song_id file for a fixed artist_id file, you can run a query like:
select artist_id,dateOf(song_id),song from music where artist_id=1 limit 1;
+5
source to share