Debugging SSTables in 3.0 with sstabledump
sstabledump
This tool outputs the contents of the specified SSTable in the JSON format.
Depending on your task, you may wish to flush the table to disk (using nodetool flush) before dumping its contents.
sstabledump [options] sstable_file
Generate a small SSTable for a table that represents stock ticker data
cqlsh
-- Create the schema
CREATE KEYSPACE IF NOT EXISTS ticker WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };
USE ticker;
CREATE TABLE IF NOT EXISTS symbol_history (
symbol text,
year int,
month int,
day int,
volume bigint,
close double,
open double,
low double,
high double,
idx text static,
PRIMARY KEY ((symbol, year), month, day)
) with CLUSTERING ORDER BY (month desc, day desc);
-- Insert some records
INSERT INTO symbol_history (symbol, year, month, day, volume, close, open, low, high, idx)
VALUES ('CORP', 2015, 12, 31, 1054342, 9.33, 9.55, 9.21, 9.57, 'NYSE') USING TTL 604800;
INSERT INTO symbol_history (symbol, year, month, day, volume, close, open, low, high, idx)
VALUES ('CORP', 2016, 1, 1, 1055334, 8.2, 9.33, 8.02, 9.35, 'NASDAQ') USING TTL 604800;
INSERT INTO symbol_history (symbol, year, month, day, volume, close, open, low, high)
VALUES ('CORP', 2016, 1, 4, 1054342, 8.54, 8.2, 8.2, 8.65) USING TTL 604800;
INSERT INTO symbol_history (symbol, year, month, day, volume, close, open, low, high)
VALUES ('CORP', 2016, 1, 5, 1054772, 8.73, 8.54, 8.44, 8.75) USING TTL 604800;
-- Update a column value
UPDATE symbol_history USING TTL 604800 set close = 8.55 where symbol = 'CORP' and year = 2016 and month = 1 and day = 4;
Flush memtables to disk as SSTables
nodetool flush
Generate some tombstones
set a column value to null and delete an entire row to generate some tombstones
-- Set column value to null
USE ticker;
UPDATE symbol_history SET high = null WHERE symbol = 'CORP' and year = 2016 and month = 1 and day = 1;
-- Delete an entire row
DELETE FROM symbol_history WHERE symbol = 'CORP' and year = 2016 and month = 1 and day = 5;
Flush again to generate a new SSTable
nodetool flush
Perform a major compaction yielding a single SSTable
nodetool compact ticker
sstabledump
sstabledump /var/lib/cassandra/data/ticker/symbol_history-ee07f100592511e8b578f36b98b5be6e/mc-3-big-Data.db
[
{
"partition" : {
"key" : [ "CORP", "2016" ],
"position" : 0
},
"rows" : [
{
"type" : "static_block",
"position" : 67,
"cells" : [
{ "name" : "idx", "value" : "NASDAQ", "tstamp" : "2018-05-16T16:26:51.731809Z", "ttl" : 604800, "expires_at" : "2018-05-23T16:26:51Z", "expired" : false }
]
},
{
"type" : "row",
"position" : 67,
"clustering" : [ 1, 5 ],
"deletion_info" : { "marked_deleted" : "2018-05-16T16:27:32.188177Z", "local_delete_time" : "2018-05-16T16:27:32Z" },
"cells" : [ ]
},
{
"type" : "row",
"position" : 67,
"clustering" : [ 1, 4 ],
"liveness_info" : { "tstamp" : "2018-05-16T16:26:51.746925Z", "ttl" : 604800, "expires_at" : "2018-05-23T16:26:51Z", "expired" : false },
"cells" : [
{ "name" : "close", "value" : 8.55, "tstamp" : "2018-05-16T16:27:02.349768Z" },
{ "name" : "high", "value" : 8.65 },
{ "name" : "low", "value" : 8.2 },
{ "name" : "open", "value" : 8.2 },
{ "name" : "volume", "value" : 1054342 }
]
},
{
"type" : "row",
"position" : 143,
"clustering" : [ 1, 1 ],
"liveness_info" : { "tstamp" : "2018-05-16T16:26:51.731809Z", "ttl" : 604800, "expires_at" : "2018-05-23T16:26:51Z", "expired" : false },
"cells" : [
{ "name" : "close", "value" : 8.2 },
{ "name" : "high", "deletion_info" : { "local_delete_time" : "2018-05-16T16:27:25Z" },
"tstamp" : "2018-05-16T16:27:25.368374Z"
},
{ "name" : "low", "value" : 8.02 },
{ "name" : "open", "value" : 9.33 },
{ "name" : "volume", "value" : 1055334 }
]
}
]
},
{
"partition" : {
"key" : [ "CORP", "2015" ],
"position" : 207
},
"rows" : [
{
"type" : "static_block",
"position" : 316,
"cells" : [
{ "name" : "idx", "value" : "NYSE", "tstamp" : "2018-05-16T16:26:51.709104Z", "ttl" : 604800, "expires_at" : "2018-05-23T16:26:51Z", "expired" : false }
]
},
{
"type" : "row",
"position" : 316,
"clustering" : [ 12, 31 ],
"liveness_info" : { "tstamp" : "2018-05-16T16:26:51.709104Z", "ttl" : 604800, "expires_at" : "2018-05-23T16:26:51Z", "expired" : false },
"cells" : [
{ "name" : "close", "value" : 9.33 },
{ "name" : "high", "value" : 9.57 },
{ "name" : "low", "value" : 9.21 },
{ "name" : "open", "value" : 9.55 },
{ "name" : "volume", "value" : 1054342 }
]
}
]
}
]
Internal Representation Format
sstabledump /var/lib/cassandra/data/ticker/symbol_history-ee07f100592511e8b578f36b98b5be6e/mc-3-big-Data.db -d
[CORP:2016]@0 Row[info=[ts=-9223372036854775808] ]: STATIC | [idx=NASDAQ ts=1526488011731809 ttl=604800 ldt=1527092811]
[CORP:2016]@0 Row[info=[ts=-9223372036854775808] del=deletedAt=1526488052188177, localDeletion=1526488052 ]: 1, 5 |
[CORP:2016]@67 Row[info=[ts=1526488011746925 ttl=604800, let=1527092811] ]: 1, 4 | [close=8.55 ts=1526488022349768 ttl=604800 ldt=1527092822], [high=8.65 ts=1526488011746925 ttl=604800 ldt=1527092811], [low=8.2 ts=1526488011746925 ttl=604800 ldt=1527092811], [open=8.2 ts=1526488011746925 ttl=604800 ldt=1527092811], [volume=1054342 ts=1526488011746925 ttl=604800 ldt=1527092811]
[CORP:2016]@143 Row[info=[ts=1526488011731809 ttl=604800, let=1527092811] ]: 1, 1 | [close=8.2 ts=1526488011731809 ttl=604800 ldt=1527092811], [high=<tombstone> ts=1526488045368374 ldt=1526488045], [low=8.02 ts=1526488011731809 ttl=604800 ldt=1527092811], [open=9.33 ts=1526488011731809 ttl=604800 ldt=1527092811], [volume=1055334 ts=1526488011731809 ttl=604800 ldt=1527092811]
[CORP:2015]@207 Row[info=[ts=-9223372036854775808] ]: STATIC | [idx=NYSE ts=1526488011709104 ttl=604800 ldt=1527092811]
[CORP:2015]@207 Row[info=[ts=1526488011709104 ttl=604800, let=1527092811] ]: 12, 31 | [close=9.33 ts=1526488011709104 ttl=604800 ldt=1527092811], [high=9.57 ts=1526488011709104 ttl=604800 ldt=1527092811], [low=9.21 ts=1526488011709104 ttl=604800 ldt=1527092811], [open=9.55 ts=1526488011709104 ttl=604800 ldt=1527092811], [volume=1054342 ts=1526488011709104 ttl=604800 ldt=1527092811]