Correlating cqlsh keys to sstable entries

Summary

Sometimes it may be useful to search for a given partition key in a given set of sstables and using the sstable2json output. If the partition key is stored as a certain type this will not match the key value as stored in the sstable file.

Details

Keys are stored in the sstables as a blob values and therefore there is a need to correlate these to the keys in cqlsh. The easiest way to do this is by using a typeAsBlob method in cqlsh.

For example:

Here is an example:

cqlsh> select * from markc.test;

key | col1 | col2
------+------+------
key5 | 115 | 225
key1 | 111 | 221
key4 | 114 | 224
key3 | 113 | 223
key2 | 112 | 222

(5 rows)

The keys in the associated sstable as are follows (using the sstablekeys tool):

sstablekeys markc-test-jb-1-Data.db
6b657935
6b657934
6b657933
6b657932

Now to correlate using the textAsBlob function in cqlsh

cqlsh> select key, textAsBlob(key) from markc.test;

key | textAsBlob(key)
------+-----------------
key5 | 0x6b657935
key1 | 0x6b657931
key4 | 0x6b657934
key3 | 0x6b657933
key2 | 0x6b657932

(5 rows)

This can now be used to find the key with sstable2json like so:

sstable2json markc-test-jb-1-Data.db -k 6b657935
[
{"key": "6b657935","columns": [["","",1433178701756000], ["col1","115",1433178701756000], ["col2","225",1433178701756000]]}
]

Further info

Some further reading / links:

http://docs.datastax.com/en/cql/3.1/cql/cql_reference/blob_r.html