HOW TO - Specify composite partition keys for SSTable utilities

Summary

This article provides the syntax to specify a composite partition key for the sstabledump and sstablepartitions commands.

Applies to

• DataStax Enterprise 6.7
• DataStax Enterprise 6.0
• DataStax Enterprise 5.1 

Symptoms

Several sstable utilities have partition key as the command argument:

sstabledump sstable_filepath [-k partition_key] [-x partition_key]

sstablepartitions sstable_filepath | sstable_directory [-k partition_key] [-x partition_key]

Although it is possible to specify a composite partition key, the DSE sstable utilities documentation shows examples using only a single column partition key but does not include the syntax for a composite partition key.

Analysis

For example, the sstabledump syntax to filter by partition key is

sstabledump sstable_filepath [-k partition_key]

where
-k, --key <partition keys to include>

In testing, the sstabledump output of a table containing composite partition key uses comma (,) as the column separator, but this is not the same separator used by the command line parser.

$ sstabledump ./aa-1-bti-Data.db -e

[ [ "abcde", "zo3j" ], [ "56789", "MrZazThePokemon" ], [ "12345", "glz9568" ]

The nodetool commands getsstables and getendpoints have examples specifying the composite partition key, but it is unclear whether those commands use the same syntax for composite partition keys.

Solution

The colon character (:) is column separator for a composite partition key for sstable utilities, and the space character ( ) is the separator for multiple partition keys. Use the same syntax to specify a composite partition key for the -x, --exclude-key argument in sstabledump and sstablepartitions commands. Testing confirmed the two sstable utilities shared the same composite partition key syntax used in the nodetool getsstables and getendpoints commands.

Examples

Schema

CREATE TABLE IF NOT EXISTS test_ks.trainers ( 
id text,
name text,
handle text,
caught int,
PRIMARY KEY ((id, name),handle)); 

Data

select * from test_ks.trainers ;

    id | name | handle          | caught
-------+------+-----------------+--------
 12345 | cg   | glz9568         | 1
 abcde | jm   | zo3j            | 3
 56789 | ii   | MrZazThePokemon | 2

(3 rows)

To discover the composite partition key values, dump the partition keys

$ sstabledump ./aa-1-bti-Data.db -e

[ [ "12345", "cg" ], [ "abcde", "jm" ], [ "56789", "ii" ]

To discover the composite partition key values, dump the whole sstable content, in JSON lines by partition format, for a more compact output

$ sstabledump ./aa-1-bti-Data.db -l

{"partition":{"key":["12345","cg"],"position":0},"rows":[{"type":"row","position":45,"clustering":["glz9568"],"liveness_info":{"tstamp":"2019-05-02T06:34:59.048824Z"},"cells":[{"name":"caught","value":1}]}]}

{"partition":{"key":["abcde","jm"],"position":46},"rows":[{"type":"row","position":89,"clustering":["zo3j"],"liveness_info":{"tstamp":"2019-05-02T06:34:59.054677Z"},"cells":[{"name":"caught","value":3}]}]}

{"partition":{"key":["56789","ii"],"position":90},"rows":[{"type":"row","position":144,"clustering":["MrZazThePokemon"],"liveness_info":{"tstamp":"2019-05-02T06:34:59.052049Z"},"cells":[{"name":"caught","value":2}]}]}

sstabledump example

Dump partitions [12345, cg] and [56789, ii] using a colon (:) to separate columns for a composite partition key and a space ( ) to separate multiple partition keys

$ sstabledump ./aa-1-bti-Data.db -l -k "12345:cg" "56789:ii"
{"partition":{"key":["12345","cg"],"position":0},"rows":[{"type":"row","position":45,"clustering":["glz9568"],"liveness_info":{"tstamp":"2019-05-02T06:34:59.048824Z"},"cells":[{"name":"caught","value":1}]}]}

{"partition":{"key":["56789","ii"],"position":46},"rows":[{"type":"row","position":144,"clustering":["MrZazThePokemon"],"liveness_info":{"tstamp":"2019-05-02T06:34:59.052049Z"},"cells":[{"name":"caught","value":2}]}]}

sstablepartitions example

Display partitions information of [12345, cg] and [56789, ii] using a colon (:) to separate columns for a composite partition key and a space ( ) to separate multiple partition keys

$ sstablepartitions "/var/lib/cassandra/data/test_ks/trainers-67f13c516ca411e9856c5318307c53d2/aa-3-bti-Data.db" -k "12345:cg" "56789:ii"
Processing test_ks.trainers-67f13c516ca411e9856c5318307c53d2 #3 (bti-aa) (145 bytes uncompressed, 135 bytes on disk)
              Partition size            Row count           Cell count      Tombstone count
  p50                     50                    1                    1                    1
  p75                    103                    1                    1                    1
  p90                    103                    1                    1                    1
  p95                    103                    1                    1                    1
  p99                    103                    1                    1                    1
  p999                   103                    1                    1                    1
  min                     43                    0                    0                    0
  max                    103                    1                    1                    1
  count                    2
  time                   420