Cassandra: Selecting a Range of TimeUUIDs using the DataStax Java Driver

Question

The use case that we are working to solve with Cassandra is this: We need to retrieve a list of entity UUIDs that have been updated within a certain time range within the last 90 days. Imagine that we're building a document tracking system, so our relevant entity is a Document, whose key is a UUID.

The query we need to support in this use case is: Find all Document UUIDs that have changed between StartDateTime and EndDateTime.

Question 1: What's the best Cassandra table design to support this query?

I think the answer is as follows:

CREATE TABLE document_change_events (
    event_uuid TIMEUUID,
    document_uuid uuid,
    PRIMARY KEY ((event_uuid), document_uuid)
) WITH default_time_to_live='7776000';

And given that we can't do range queries on partition keys, we'd need to use the token() method. As such the query would then be:

SELECT document_uuid 
 WHERE token(event_uuid) > token(minTimeuuid(?)) 
   AND token(event_uuid) < token(maxTimeuuid(?))

For example:

SELECT document_uuid 
 WHERE token(event_uuid) > token(minTimeuuid('2015-05-10 00:00+0000')) 
   AND token(event_uuid) < token(maxTimeuuid('2015-05-20 00:00+0000'))

Question 2: I can't seem to get the following Java code using DataStax's driver to reliability return the correct results.

If I run the following code 10 times pausing 30 seconds between, I will then have 10 rows in this table:

private void addEvent() {

    String cql = "INSERT INTO document_change_events (event_uuid, document_uuid) VALUES(?,?)";

    PreparedStatement preparedStatement = cassandraSession.prepare(cql);
    BoundStatement boundStatement = new BoundStatement(preparedStatement);
    boundStatement.setConsistencyLevel(ConsistencyLevel.ANY);

    boundStatement.setUUID("event_uuid", UUIDs.timeBased());
    boundStatement.setUUID("document_uuid", UUIDs.random());

    cassandraSession.execute(boundStatement);

}

Here are the results:

cqlsh:> select event_uuid, dateOf(event_uuid), document_uuid from document_change_events;

 event_uuid                           | dateOf(event_uuid)       | document_uuid
--------------------------------------+--------------------------+--------------------------------------
 414decc0-0014-11e5-93a9-51f9a7931084 | 2015-05-21 18:51:09-0500 | 92b6fb6a-9ded-47b0-a91c-68c63f45d338
 9abb4be0-0014-11e5-93a9-51f9a7931084 | 2015-05-21 18:53:39-0500 | 548b320a-10f6-409f-a921-d4a1170a576e
 6512b960-0014-11e5-93a9-51f9a7931084 | 2015-05-21 18:52:09-0500 | 970e5e77-1e07-40ea-870a-84637c9fc280
 53307a20-0014-11e5-93a9-51f9a7931084 | 2015-05-21 18:51:39-0500 | 11b4a49c-b73d-4c8d-9f88-078a6f303167
 ac9e0050-0014-11e5-93a9-51f9a7931084 | 2015-05-21 18:54:10-0500 | b29e7915-7c17-4900-b784-8ac24e9e72e2
 88d7fb30-0014-11e5-93a9-51f9a7931084 | 2015-05-21 18:53:09-0500 | c8188b73-1b97-4b32-a897-7facdeecea35
 0ba5cf70-0014-11e5-93a9-51f9a7931084 | 2015-05-21 18:49:39-0500 | a079b30f-be80-4a99-ae0e-a784d82f0432
 76f56dd0-0014-11e5-93a9-51f9a7931084 | 2015-05-21 18:52:39-0500 | 3b593ca6-220c-4a8b-8c16-27dc1fb5adde
 1d88f910-0014-11e5-93a9-51f9a7931084 | 2015-05-21 18:50:09-0500 | ec155e0b-39a5-4d2f-98f0-0cd7a5a07ec8
 2f6b3850-0014-11e5-93a9-51f9a7931084 | 2015-05-21 18:50:39-0500 | db42271b-04f2-45d1-9ae7-0c8f9371a4db

(10 rows)

But if I then run this code:

private static void retrieveEvents(Instant startInstant, Instant endInstant) {

    String cql = "SELECT document_uuid FROM document_change_events " + 
                 "WHERE token(event_uuid) > token(?) AND token(event_uuid) < token(?)";

    PreparedStatement preparedStatement = cassandraSession.prepare(cql);
    BoundStatement boundStatement = new BoundStatement(preparedStatement);
    boundStatement.setConsistencyLevel(ConsistencyLevel.LOCAL_QUORUM);

    boundStatement.bind(UUIDs.startOf(Date.from(startInstant).getTime()),
                        UUIDs.endOf(Date.from(endInstant).getTime()));

    ResultSet resultSet = cassandraSession.execute(boundStatement);

    if (resultSet == null) {
      System.out.println("None found.");
      return;
    }

    while (!resultSet.isExhausted()) {
      System.out.println(resultSet.one().getUUID("document_uuid"));
    }

}

It only retrieves three results:

3b593ca6-220c-4a8b-8c16-27dc1fb5adde
ec155e0b-39a5-4d2f-98f0-0cd7a5a07ec8
db42271b-04f2-45d1-9ae7-0c8f9371a4db

Why didn't it retrieve all 10 results? And what do I need to change to achieve the correct results to support this use case?

For reference, I've tested this against dsc-2.1.1, dse-4.6 and using the DataStax Java Driver v2.1.6.

This answer may help you : [so](http://stackoverflow.com/questions/17883202/time-series-data-selecting-range-with-maxtimeuuid-mintimeuuid-in-cassandra) — fhussonnois, May 22 '15 at 13:17

score 5 · Accepted Answer · answered May 22 '15 at 13:41

First of all, please only ask one question at a time. Both of your questions here could easily stand on their own. I know these are related, but it just makes the readers come down with a case of tl;dr.

I'll answer your 2nd question first, because the answer ties into a fundamental understanding that is central to getting the data model correct. When I INSERT your rows and run the following query, this is what I get:

aploetz@cqlsh:stackoverflow2> SELECT document_uuid FROM document_change_events 
WHERE token(event_uuid) > token(minTimeuuid('2015-05-10 00:00-0500')) 
  AND token(event_uuid) < token(maxTimeuuid('2015-05-22 00:00-0500'));

 document_uuid
--------------------------------------
 a079b30f-be80-4a99-ae0e-a784d82f0432
 3b593ca6-220c-4a8b-8c16-27dc1fb5adde
 ec155e0b-39a5-4d2f-98f0-0cd7a5a07ec8
 db42271b-04f2-45d1-9ae7-0c8f9371a4db

(4 rows)

Which is similar to what you are seeing. Why didn't that return all 10? Well, the answer becomes apparent when I include token(event_uuid) in my SELECT:

aploetz@cqlsh:stackoverflow2> SELECT token(event_uuid),document_uuid FROM document_change_events WHERE token(event_uuid) > token(minTimeuuid('2015-05-10 00:00-0500')) AND token(event_uuid) < token(maxTimeuuid('2015-05-22 00:00-0500'));

 token(event_uuid)    | document_uuid
----------------------+--------------------------------------
 -2112897298583224342 | a079b30f-be80-4a99-ae0e-a784d82f0432
  2990331690803078123 | 3b593ca6-220c-4a8b-8c16-27dc1fb5adde
  5049638908563824288 | ec155e0b-39a5-4d2f-98f0-0cd7a5a07ec8
  5577339174953240576 | db42271b-04f2-45d1-9ae7-0c8f9371a4db

(4 rows)

Cassandra stores partition keys (event_uuid in your case) in order by their hashed token value. You can see this when using the token function. Cassandra generates partition tokens with a process called consistent hashing to ensure even cluster distribution. In other words, querying by token range doesn't make sense unless the actual (hashed) token values are meaningful to your application.

Getting back to your first question, this means you will have to find a different column to partition on. My suggestion is to use a timeseries mechanism called a "date bucket." Picking the date bucket can be tricky, as it depends on your requirements and query patterns...so that's really up to you to pick a useful one.

For the purposes of this example, I'll pick "month." So I'll re-create your table partitioning on month and clustering by event_uuid:

CREATE TABLE document_change_events2 (
    event_uuid TIMEUUID,
    document_uuid uuid,
    month text,
    PRIMARY KEY ((month),event_uuid, document_uuid)
) WITH default_time_to_live='7776000';

Now I can query by a date range, when also filtering by month:

aploetz@cqlsh:stackoverflow2> SELECT document_uuid FROM document_change_events2 
WHERE month='201505'
  AND event_uuid > minTimeuuid('2015-05-10 00:00-0500')
  AND event_uuid < maxTimeuuid('2015-05-22 00:00-0500');

 document_uuid
--------------------------------------
 a079b30f-be80-4a99-ae0e-a784d82f0432
 ec155e0b-39a5-4d2f-98f0-0cd7a5a07ec8
 db42271b-04f2-45d1-9ae7-0c8f9371a4db
 92b6fb6a-9ded-47b0-a91c-68c63f45d338
 11b4a49c-b73d-4c8d-9f88-078a6f303167
 970e5e77-1e07-40ea-870a-84637c9fc280
 3b593ca6-220c-4a8b-8c16-27dc1fb5adde
 c8188b73-1b97-4b32-a897-7facdeecea35
 548b320a-10f6-409f-a921-d4a1170a576e
 b29e7915-7c17-4900-b784-8ac24e9e72e2

(10 rows)

Again, month may not work for your application. So put some thought behind coming up with an appropriate column to partition on, and then you should be able to solve this.

Good point about separate questions. I was thinking that my problem was something slightly incorrect with the code, so that was my focus but I wanted to open up responses to include more correct designs. With regards to the tokens, I was under the impression that timeuuids generated sequential tokens, but clearly that's not correct! Thanks for the recommendations on time bucketing. I just implemented the solution and it's worked well thus far. — peterl, Jun 19 '15 at 07:22

Cassandra: Selecting a Range of TimeUUIDs using the DataStax Java Driver

1 Answers1