· 6 years ago · Jan 17, 2020, 08:52 AM
1First, make sure you have cassandra tools
2
3$ sudo apt-get install cassandra-tools
4
5
6Then, let’s generate a small SSTable for a table that represents stock ticker data. This should be done within a cqlsh session:
7
8-- Create the schema
9
10
11
12CREATE KEYSPACE IF NOT EXISTS ticker WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };
13
14
15
16USE ticker;
17
18
19
20CREATE TABLE IF NOT EXISTS symbol_history (
21
22 symbol text,
23
24 year int,
25
26 month int,
27
28 day int,
29
30 volume bigint,
31
32 close double,
33
34 open double,
35
36 low double,
37
38 high double,
39
40 idx text static,
41
42 PRIMARY KEY ((symbol, year), month, day)
43
44) with CLUSTERING ORDER BY (month desc, day desc);
45
46
47
48-- Insert some records
49
50
51
52INSERT INTO symbol_history (symbol, year, month, day, volume, close, open, low, high, idx)
53
54VALUES ('CORP', 2015, 12, 31, 1054342, 9.33, 9.55, 9.21, 9.57, 'NYSE') USING TTL 604800;
55
56
57
58INSERT INTO symbol_history (symbol, year, month, day, volume, close, open, low, high, idx)
59
60VALUES ('CORP', 2016, 1, 1, 1055334, 8.2, 9.33, 8.02, 9.35, 'NASDAQ') USING TTL 604800;
61
62
63
64INSERT INTO symbol_history (symbol, year, month, day, volume, close, open, low, high)
65
66VALUES ('CORP', 2016, 1, 4, 1054342, 8.54, 8.2, 8.2, 8.65) USING TTL 604800;
67
68
69
70INSERT INTO symbol_history (symbol, year, month, day, volume, close, open, low, high)
71
72VALUES ('CORP', 2016, 1, 5, 1054772, 8.73, 8.54, 8.44, 8.75) USING TTL 604800;
73
74
75
76-- Update a column value
77
78
79
80UPDATE symbol_history USING TTL 604800 set close = 8.55 where symbol = 'CORP' and year = 2016 and month = 1 and day = 4;
81
82
83
84List SSTAbles
85
86$ sudo sstableutil ticker symbol_history
87
88
89Next, let’s flush memtables to disk as SSTables using nodetool:
90
91$ nodetool flush
92
93
94List SSTAbles now
95
96$ sudo sstableutil ticker symbol_history
97
98
99Then in a cqlsh session we will set a column value to null and delete an entire row to generate some tombstones:
100
101-- Set column value to null
102
103
104
105USE ticker;
106
107UPDATE symbol_history SET high = null WHERE symbol = 'CORP' and year = 2016 and month = 1 and day = 1;
108
109
110
111-- Delete an entire row
112
113
114
115DELETE FROM symbol_history WHERE symbol = 'CORP' and year = 2016 and month = 1 and day = 5;
116
117
118
119We proceed to flush again to generate a new SSTable
120
121$ nodetool flush
122
123We can see 2 SSTables now with
124
125$ sudo sstableutil ticker symbol_history
126
127
128then perform a major compaction yielding a single SSTable.
129
130$ nodetool compact ticker
131
132check it again wiht
133
134$ sudo sstableutil ticker symbol_history
135
136
137For retrieving SSTable data in JSON format:
138
139$ sstabledump /var/lib/cassandra/data/ticker/symbol_history-dd50194037af11eaa4d519e0ddc1a582/ma-3-big-Data.db
140
141and in a more compact format:
142
143$ sstabledump /var/lib/cassandra/data/ticker/symbol_history-dd50194037af11eaa4d519e0ddc1a582/ma-3-big-Data.db -d
144
145
146you can copy&paste into https://jsoneditoronline.org/ for better JSON inspection
147
148
149view sstable table statistics:
150
151$ nodetool tablestats ticker
152
153$ nodetool tablehistograms ticker symbol_history
154
155
156make a query
157
158cqlsh:ticker> select * from symbol_history where symbol = 'CORP' and year = 2016 ;
159
160
161check again
162
163$ nodetool tablehistograms ticker symbol_history