How do you insert custom timeuuid's to cassandra without the now() function?

Question

I was trying to insert a specific timeuuid to cassandra and the only way I managed to insert one was using the now() function, because I assume, the now function knows what format the database likes it.

How do I create cqlsh command for this?

Currently I have this:

INSERT INTO my_table (tid) VALUES ( now() )

But I would like to be able to have 100% control of what date I insert for testing purposes when I am debugging my node.js or whatever program interfacing cassandra.

It would be nice to have something like:

INSERT INTO my_table (tid) VALUES ( 12/OCTOBER/2014 )

without it crashing

Thanks!

Edea

Answer 1

Take a look at minTimeuuid and maxTimeuuid functions. It's a bad idea to insert their result, but for testing purposes it can be OK.

http://cassandra.apache.org/doc/cql3/CQL.html#timeuuidFun

Sunn

doing minTimeuuid('2014-10-12') worked for me in cqlsh during an insert query to the cassandra database. Apparently, single vs double quotes might make a difference (did for me). Careful with that. – Edea

Could we possibly have a little more detail at to why it is a "bad idea" except in testing? – Richellericher

@Richellericher Because we will have the same id from call minTimeuuid or maxTimeuuid with the same data. It's not true UUID, it's just top and bottom border values. – Fresher

Answer 2

timeuuid's are a complex format. Valid values are described here.

timeuuid

Uses the time in 100 nanosecond intervals since 00:00:00.00 UTC (60 bits), a 
clock sequence number for prevention of duplicates (14 bits), plus the IEEE 801 MAC
address (48 bits) to generate a unique identifier. For example: 
d2177dd0-eaa2-11de-a572-001b779c76e3 

This has a good discussion of timeuuid. I do not know what order these bits appear in, but if it is from left to right you could concat:

Time (first 15 digits = 60 bits): 00000000-0000-000

Sequence (next 3 digits = 12 bits, ignores last 2 bits of sequence): 0-00

Last 2 bits sequence + MAC: 000-000000000000

Then increment the Time or the Sequence as needed for entries. But it would probably be a lot easier to just work with timestamps.

If you want to do this:

INSERT INTO my_table (tid) VALUES ( "2014-10-12" )

you need to use the timestamp type.

Quirk

sorry to be snarky. datastax is a great place to go for cassandra doc. – Quirk

don't worry, you don't have to apologize. I was just so surprised/annoyed/shocked that the source I was looking at didn't have a clear explanation of the syntax. But reckoned that if I had the problem, probably someone else would have it in the future and it wouldn't hurt to help future people. Thanks anyway! :) – Edea

I am not sure why its not working do you have an idea?, INSERT INTO commits (hash, keyframe, tid) VALUES (0x000000001bfdeb0a304b372dd8dda123b3fd1a00, true, "2012-01-01"); – Edea

Bad Request: line 1:100 no viable alternative at input '2012-01-01' – Edea

huh. what does describe table commits return? – Quirk

CREATE TABLE commits ( hash blob, keyframe boolean, tid timeuuid, PRIMARY KEY (hash) ) – Edea

WITH bloom_filter_fp_chance=0.010000 AND caching='KEYS_ONLY' AND comment='' AND dclocal_read_repair_chance=0.000000 AND gc_grace_seconds=864000 AND index_interval=128 AND read_repair_chance=0.100000 AND replicate_on_write='true' AND populate_io_cache_on_flush='false' AND default_time_to_live=0 AND speculative_retry='99.0PERCENTILE' AND memtable_flush_period_in_ms=0 AND compaction={'class': 'SizeTieredCompactionStrategy'} AND compression={'sstable_compression': 'LZ4Compressor'}; – Edea

my bad, i was writing about timestamp type. i have updated my answer – Quirk

so what does d2177dd0-eaa2-11de-a572-001b779c76e3 stand for? – Edea

updated with an explanation and how you could do it. i have not done this before but it should work – Quirk

Answer 3

I you have 2 options:

1. use one of the minTimeuuid or maxTimeuuid described here
2. Implement a Timeuuid value in Node.js by using the details in UUIDGen

I'd say the first approach would be easier for testing purposes.

Silverstein

Hi @AlexPopescu, Is there an updated documentation for this? minTimeuuid and maxTimeuuid no longer accept serialized dates as arguments from what I am able to tell. – Dink

@flavian: no longer as in what Cassandra version, what cqlsh, etc. etc. – Silverstein

Cassandra 2.2.0. I created a full spec issue here: datastax-oss.atlassian.net/projects/JAVA/issues/… – Dink