1. Clickhouse - Brown University Benchmark
This chapter followed the instruction of Brown University Benchmark
1.1 Downlaod data
wget is too slow, so I use axel to open 5 thread to download the data.
1
2
3
4
#wget https://datasets.clickhouse.com/mgbench{1..3}.csv.xz
[root@hadoop003 BrownUniversity]# axel -n 5 https://datasets.clickhouse.com/mgbench1.csv.xz
[root@hadoop003 BrownUniversity]# axel -n 5 https://datasets.clickhouse.com/mgbench2.csv.xz
[root@hadoop003 BrownUniversity]# axel -n 5 https://datasets.clickhouse.com/mgbench3.csv.xz
1.2 Unpack the data
1
2
3
4
5
6
7
[root@hadoop003 BrownUniversity]# xz -v -d mgbench{1..3}.csv.xz
...
[root@hadoop003 BrownUniversity]# ll
total 21398384
-rw-r--r-- 1 root root 7556519623 Jun 8 16:48 mgbench1.csv
-rw-r--r-- 1 root root 6090862569 Jun 8 16:41 mgbench2.csv
-rw-r--r-- 1 root root 8264547394 Jun 8 16:42 mgbench3.csv
1.3 Create the tables
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
CREATE DATABASE mgbench;
CREATE TABLE mgbench.logs1 (
log_time DateTime,
machine_name LowCardinality(String),
machine_group LowCardinality(String),
cpu_idle Nullable(Float32),
cpu_nice Nullable(Float32),
cpu_system Nullable(Float32),
cpu_user Nullable(Float32),
cpu_wio Nullable(Float32),
disk_free Nullable(Float32),
disk_total Nullable(Float32),
part_max_used Nullable(Float32),
load_fifteen Nullable(Float32),
load_five Nullable(Float32),
load_one Nullable(Float32),
mem_buffers Nullable(Float32),
mem_cached Nullable(Float32),
mem_free Nullable(Float32),
mem_shared Nullable(Float32),
swap_free Nullable(Float32),
bytes_in Nullable(Float32),
bytes_out Nullable(Float32)
)
ENGINE = MergeTree()
ORDER BY (machine_group, machine_name, log_time);
CREATE TABLE mgbench.logs2 (
log_time DateTime,
client_ip IPv4,
request String,
status_code UInt16,
object_size UInt64
)
ENGINE = MergeTree()
ORDER BY log_time;
CREATE TABLE mgbench.logs3 (
log_time DateTime64,
device_id FixedString(15),
device_name LowCardinality(String),
device_type LowCardinality(String),
device_floor UInt8,
event_type LowCardinality(String),
event_unit FixedString(1),
event_value Nullable(Float32)
)
ENGINE = MergeTree()
ORDER BY (event_type, log_time);
1.4 Import data
1
2
3
clickhouse-client --query "INSERT INTO mgbench.logs1 FORMAT CSVWithNames" < mgbench1.csv
clickhouse-client --query "INSERT INTO mgbench.logs2 FORMAT CSVWithNames" < mgbench2.csv
clickhouse-client --query "INSERT INTO mgbench.logs3 FORMAT CSVWithNames" < mgbench3.csv
2 Running Benchmarks
2.1 Benchmarks on logs1
– Q1.1: What is the CPU/network utilization for each web server since midnight?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
hadoop003 :) select machine_name, min(cpu) as cpu_min, max(cpu) as cpu_max, avg(cpu) as cpu_avg, min(net_in) as net_in_min, max(net_in) as net_in_max, avg(net_in) as net_in_avg, min(net_out) as net_out_min, max(net_out) as net_out_max, avg(net_out) as net_out_avg from (select machine_name, coalesce(cpu_user,0.0) as cpu, coalesce(bytes_in, 0.0) as net_in, coalesce(bytes_out, 0.0) as net_out from logs1 where machine_name in ('anansi', 'aragog', 'urd') and log_time>= TIMESTAMP '2017-01-11 00:00:00' ) as r group by machine_name;
SELECT
machine_name,
min(cpu) AS cpu_min,
max(cpu) AS cpu_max,
avg(cpu) AS cpu_avg,
min(net_in) AS net_in_min,
max(net_in) AS net_in_max,
avg(net_in) AS net_in_avg,
min(net_out) AS net_out_min,
max(net_out) AS net_out_max,
avg(net_out) AS net_out_avg
FROM
(
SELECT
machine_name,
coalesce(cpu_user, 0.) AS cpu,
coalesce(bytes_in, 0.) AS net_in,
coalesce(bytes_out, 0.) AS net_out
FROM logs1
WHERE (machine_name IN ('anansi', 'aragog', 'urd')) AND (log_time >= toDateTime('2017-01-11 00:00:00'))
) AS r
GROUP BY machine_name
Query id: 775f595f-05f6-40bb-87a5-cde7b3a0b511
┌─machine_name─┬─cpu_min─┬────────────cpu_max─┬─────────────cpu_avg─┬─────────net_in_min─┬─net_in_max─┬─────────net_in_avg─┬────────net_out_min─┬──net_out_max─┬────────net_out_avg─┐
│ urd │ 0 │ 23.493331909179688 │ 1.499065357465967 │ 1168.3499755859375 │ 368025.625 │ 22100.285078089873 │ 220.95001220703125 │ 108069.65625 │ 8129.441456009021 │
│ anansi │ 0 │ 22.5 │ 0.1426860871961902 │ 938.949951171875 │ 2355335.75 │ 9177.160347696004 │ 216.8800048828125 │ 2354536.25 │ 13467.316385337206 │
│ aragog │ 0 │ 31.14000129699707 │ 0.46608482347828106 │ 1318.68994140625 │ 27836488 │ 328922.02496959124 │ 308.9499816894531 │ 40642404 │ 825490.5620742807 │
└──────────────┴─────────┴────────────────────┴─────────────────────┴────────────────────┴────────────┴────────────────────┴────────────────────┴──────────────┴────────────────────┘
3 rows in set. Elapsed: 0.115 sec. Processed 206.05 thousand rows, 643.09 KB (1.80 million rows/s., 5.61 MB/s.)
– Q1.2: Which computer lab machines have been offline in the past day?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
hadoop003 :) select machine_name, log_time from logs1 where (machine_name like 'cslab%' or machine_name like 'mslab%') and load_one is NULL and log_time >= TIMESTAMP '2017-01-10 00:00:00' order by machine_name, log_time;
SELECT
machine_name,
log_time
FROM logs1
WHERE ((machine_name LIKE 'cslab%') OR (machine_name LIKE 'mslab%')) AND (load_one IS NULL) AND (log_time >= toDateTime('2017-01-10 00:00:00'))
ORDER BY
machine_name ASC,
log_time ASC
Query id: dcb3e95e-80b9-4103-a821-403e346a8c78
┌─machine_name─┬────────────log_time─┐
│ cslab9e │ 2017-01-10 14:14:15 │
│ cslab9e │ 2017-01-10 14:14:30 │
│ cslab9e │ 2017-01-10 14:14:45 │
│ cslab9e │ 2017-01-10 14:15:15 │
│ cslab9e │ 2017-01-10 14:15:30 │
│ cslab9e │ 2017-01-10 14:15:45 │
│ cslab9e │ 2017-01-10 14:16:15 │
│ cslab9e │ 2017-01-10 14:16:30 │
│ cslab9e │ 2017-01-10 14:16:45 │
│ cslab9e │ 2017-01-10 14:17:15 │
│ cslab9e │ 2017-01-10 14:17:30 │
│ cslab9e │ 2017-01-10 14:17:45 │
│ cslab9e │ 2017-01-10 14:18:15 │
│ cslab9e │ 2017-01-10 14:18:30 │
│ cslab9e │ 2017-01-10 14:18:45 │
│ cslab9e │ 2017-01-10 14:19:15 │
│ cslab9e │ 2017-01-10 14:19:30 │
│ cslab9e │ 2017-01-10 14:19:45 │
└──────────────┴─────────────────────┘
18 rows in set. Elapsed: 0.094 sec. Processed 1.67 million rows, 15.17 MB (17.75 million rows/s., 161.10 MB/s.)
– Q1.3: What are the hourly average metrics during the past 10 days for a specific workstation?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
hadoop003 :) SELECT dt,
hr,
AVG(load_fifteen) AS load_fifteen_avg,
AVG(load_five) AS load_five_avg,
AVG(load_one) AS load_one_avg,
AVG(mem_free) AS mem_free_avg,
AVG(swap_free) AS swap_free_avg
FROM (
SELECT CAST(log_time AS DATE) AS dt,
EXTRACT(HOUR FROM log_time) AS hr,
load_fifteen,
load_five,
load_one,
mem_free,
swap_free
FROM logs1
WHERE machine_name = 'babbage'
AND load_fifteen IS NOT NULL
AND load_five IS NOT NULL
AND load_one IS NOT NULL
AND mem_free IS NOT NULL
AND swap_free IS NOT NULL
AND log_time >= TIMESTAMP '2017-01-01 00:00:00'
) AS r
GROUP BY dt,
hr
ORDER BY dt,
hr;
SELECT
dt,
hr,
AVG(load_fifteen) AS load_fifteen_avg,
AVG(load_five) AS load_five_avg,
AVG(load_one) AS load_one_avg,
AVG(mem_free) AS mem_free_avg,
AVG(swap_free) AS swap_free_avg
FROM
(
SELECT
CAST(log_time, 'DATE') AS dt,
toHour(log_time) AS hr,
load_fifteen,
load_five,
load_one,
mem_free,
swap_free
FROM logs1
WHERE (machine_name = 'babbage') AND (load_fifteen IS NOT NULL) AND (load_five IS NOT NULL) AND (load_one IS NOT NULL) AND (mem_free IS NOT NULL) AND (swap_free IS NOT NULL) AND (log_time >= toDateTime('2017-01-01 00:00:00'))
) AS r
GROUP BY
dt,
hr
ORDER BY
dt ASC,
hr ASC
Query id: 67776a9d-3864-4467-acb2-87a2ad87ffcb
┌─────────dt─┬─hr─┬─────load_fifteen_avg─┬───────load_five_avg─┬────────load_one_avg─┬───────mem_free_avg─┬─swap_free_avg─┐
│ 2017-01-01 │ 0 │ 0.12023027762770652 │ 0.09362472295761108 │ 0.08744388920943796 │ 2170359.9625 │ 8388604 │
│ 2017-01-01 │ 1 │ 0.06573361121118068 │ 0.06255777780897916 │ 0.05611999999382533 │ 2170050.05 │ 8388604 │
│ 2017-01-01 │ 2 │ 0.06406694483011961 │ 0.08363861131171385 │ 0.07870277822948993 │ 2170090.8666666667 │ 8388604 │
│ 2017-01-01 │ 3 │ 0.0986541671678424 │ 0.1006138886945943 │ 0.10358000136911868 │ 2169553.683333333 │ 8388604 │
│ 2017-01-01 │ 4 │ 0.09523666650056839 │ 0.09710055496543646 │ 0.09572555573152688 │ 2167171.0083333333 │ 8388604 │
│ 2017-01-01 │ 5 │ 0.07341333317259947 │ 0.0877105557670196 │ 0.09117277721428158 │ 2169601.0791666666 │ 8388604 │
│ 2017-01-01 │ 6 │ 0.1249966664860646 │ 0.12358194496482611 │ 0.14162250099082788 │ 2169959.216666667 │ 8388604 │
│ 2017-01-01 │ 7 │ 0.11430861117939155 │ 0.09302722234278918 │ 0.08682444458051274 │ 2170149.6625 │ 8388604 │
│ 2017-01-01 │ 8 │ 0.10646722267071405 │ 0.09895083326846361 │ 0.10322805525890241 │ 2167045.495833333 │ 8388604 │
│ 2017-01-01 │ 9 │ 0.12251555596788724 │ 0.10479388938595852 │ 0.11136138855945318 │ 2162012.7875 │ 8388604 │
│ 2017-01-01 │ 10 │ 0.11412361202140649 │ 0.09653750006109477 │ 0.09687638846905126 │ 2161791.529166667 │ 8388604 │
│ 2017-01-01 │ 11 │ 0.11665944531559944 │ 0.10138361202552915 │ 0.10087499950944524 │ 2162073.8833333333 │ 8388604 │
│ 2017-01-01 │ 12 │ 0.13202694468200207 │ 0.12224000046650568 │ 0.12931416617357172 │ 2161852.125 │ 8388604 │
│ 2017-01-01 │ 13 │ 0.13301833358903725 │ 0.12086000063767036 │ 0.12991249983509381 │ 2161914.9833333334 │ 8388604 │
│ 2017-01-01 │ 14 │ 0.13185361102223397 │ 0.12042166745911041 │ 0.12537749845456952 │ 2161910.533333333 │ 8388604 │
│ 2017-01-01 │ 15 │ 0.12312361051638922 │ 0.10716138804952303 │ 0.11218249970212733 │ 2161943.1708333334 │ 8388604 │
│ 2017-01-01 │ 16 │ 0.12113694461683432 │ 0.09912972152233124 │ 0.10122527671046555 │ 2162018.1 │ 8388604 │
│ 2017-01-01 │ 17 │ 0.1147558331489563 │ 0.09416805561631918 │ 0.07953444479499013 │ 2161867.341666667 │ 8388604 │
│ 2017-01-01 │ 18 │ 0.1183680562923352 │ 0.09412694523731867 │ 0.09295555513817817 │ 2161808.3583333334 │ 8388604 │
│ 2017-01-01 │ 19 │ 0.12196722266574701 │ 0.097925555643936 │ 0.10160416695192301 │ 2161681.908333333 │ 8388604 │
│ 2017-01-01 │ 20 │ 0.09463472304244837 │ 0.09313277751207352 │ 0.09773722238411817 │ 2161829.6791666667 │ 8388604 │
│ 2017-01-01 │ 21 │ 0.10291055552661418 │ 0.09544166668007771 │ 0.0945444439110967 │ 2161906.216666667 │ 8388604 │
│ 2017-01-01 │ 22 │ 0.07539472257097563 │ 0.0695430559416612 │ 0.06634777738557508 │ 2161942.8208333333 │ 8388604 │
│ 2017-01-01 │ 23 │ 0.09157972242683173 │ 0.09328861000637213 │ 0.08586444506751528 │ 2161994.0541666667 │ 8388604 │
│ 2017-01-02 │ 0 │ 0.11643944370249908 │ 0.11347222266097863 │ 0.11106166713580023 │ 2161844.6666666665 │ 8388604 │
│ 2017-01-02 │ 1 │ 0.11753805540502071 │ 0.09894166647767028 │ 0.09913972189339498 │ 2161888.5166666666 │ 8388604 │
│ 2017-01-02 │ 2 │ 0.10927694514393807 │ 0.09175833314657211 │ 0.09419027888798155 │ 2161895.941666667 │ 8388604 │
│ 2017-01-02 │ 3 │ 0.11750833292802175 │ 0.10710861049592495 │ 0.09911694576051862 │ 2160942.995833333 │ 8388604 │
│ 2017-01-02 │ 4 │ 0.16006777820487816 │ 0.15108305824299653 │ 0.15381611017025232 │ 2159976.341666667 │ 8388604 │
│ 2017-01-02 │ 5 │ 0.12559138896564642 │ 0.11111666659514109 │ 0.11240972317755223 │ 2160101.933333333 │ 8388604 │
│ 2017-01-02 │ 6 │ 0.12220388899246852 │ 0.09916305554409822 │ 0.08808777916807836 │ 2160372.5 │ 8388604 │
│ 2017-01-02 │ 7 │ 0.09964111149311065 │ 0.09400999993085861 │ 0.09190583307839309 │ 2160514.591666667 │ 8388604 │
│ 2017-01-02 │ 8 │ 0.10217888889213403 │ 0.0866655558347702 │ 0.08541472086217254 │ 2160668.1 │ 8388604 │
│ 2017-01-02 │ 9 │ 0.08228083302577337 │ 0.09420055554558833 │ 0.10273527939086004 │ 2160456.095833333 │ 8388604 │
│ 2017-01-02 │ 10 │ 0.08421888885398705 │ 0.09449861111740271 │ 0.0848155550173639 │ 2160655.841666667 │ 8388604 │
│ 2017-01-02 │ 11 │ 0.12303055624167124 │ 0.1158838884284099 │ 0.12435083355521784 │ 2160461.725 │ 8388604 │
│ 2017-01-02 │ 12 │ 0.11161638877044121 │ 0.11003305533279975 │ 0.11490638946027805 │ 2160493.225 │ 8388604 │
│ 2017-01-02 │ 13 │ 0.12921444388727346 │ 0.11345722296585639 │ 0.11001138874950508 │ 2160430.4 │ 8388604 │
│ 2017-01-02 │ 14 │ 0.11105805585781733 │ 0.09359888959055146 │ 0.08493416606991862 │ 2160381.5791666666 │ 8388604 │
│ 2017-01-02 │ 15 │ 0.11183222172160943 │ 0.10292472175012032 │ 0.09062749987933785 │ 2160393.129166667 │ 8388604 │
│ 2017-01-02 │ 16 │ 0.08617916715641817 │ 0.09104333333671093 │ 0.10536333377822302 │ 2160193.8041666667 │ 8388604 │
│ 2017-01-02 │ 17 │ 0.08787111149479945 │ 0.08413500046978394 │ 0.07315277771946664 │ 2160329.4916666667 │ 8388604 │
│ 2017-01-02 │ 18 │ 0.09679638867576917 │ 0.09547444470226765 │ 0.08687333346654971 │ 2160225.154166667 │ 8388604 │
│ 2017-01-02 │ 19 │ 0.06433361123005549 │ 0.08074055584147573 │ 0.06748694416407185 │ 2160357.2333333334 │ 8388604 │
│ 2017-01-02 │ 20 │ 0.08769361122200886 │ 0.0962622221869727 │ 0.09008638939412776 │ 2160311.779166667 │ 8388604 │
│ 2017-01-02 │ 21 │ 0.11698249988257885 │ 0.10602944443623225 │ 0.10571166708299036 │ 2160373.0708333333 │ 8388604 │
│ 2017-01-02 │ 22 │ 0.08861805548270543 │ 0.08006027803445856 │ 0.09200138785333062 │ 2160149.3916666666 │ 8388604 │
│ 2017-01-02 │ 23 │ 0.09397055606047312 │ 0.09536361179004113 │ 0.08649277756145844 │ 2160389.875 │ 8388604 │
│ 2017-01-03 │ 0 │ 0.0848027781272928 │ 0.08811111074561874 │ 0.10022500015717621 │ 2160202.345833333 │ 8388604 │
│ 2017-01-03 │ 1 │ 0.08412027824670076 │ 0.0896069444095095 │ 0.08068527737535382 │ 2160146.9291666667 │ 8388604 │
│ 2017-01-03 │ 2 │ 0.07765083375076452 │ 0.09546111083279053 │ 0.09552611192145075 │ 2160108.675 │ 8388604 │
│ 2017-01-03 │ 3 │ 0.1192725001523892 │ 0.09875500040749709 │ 0.09649555550956089 │ 2160172.0083333333 │ 8388604 │
│ 2017-01-03 │ 4 │ 0.14164750104149182 │ 0.14051166748007138 │ 0.13798694227201244 │ 2158355.8875 │ 8388604 │
│ 2017-01-03 │ 5 │ 0.11167611181735992 │ 0.09206805576880774 │ 0.1009994447696954 │ 2160156.745833333 │ 8388604 │
│ 2017-01-03 │ 6 │ 0.1218663889914751 │ 0.11450888973971209 │ 0.10943611049248526 │ 2160072.9791666665 │ 8388604 │
│ 2017-01-03 │ 7 │ 0.12645916653176148 │ 0.11633722161253293 │ 0.12031111149893453 │ 2159854.4583333335 │ 8388604 │
│ 2017-01-03 │ 8 │ 0.12138416667779287 │ 0.10111111160367728 │ 0.10423249991921087 │ 2159847.1458333335 │ 8388604 │
│ 2017-01-03 │ 9 │ 0.09084388917932908 │ 0.09078694464017947 │ 0.08977777811232954 │ 2159932.279166667 │ 8388604 │
│ 2017-01-03 │ 10 │ 0.11166750006377697 │ 0.09926222264766693 │ 0.10057527695704872 │ 2159912.3875 │ 8388604 │
│ 2017-01-03 │ 11 │ 0.10902583313484987 │ 0.10831499888251225 │ 0.10890444433316589 │ 2159957.9375 │ 8388604 │
│ 2017-01-03 │ 12 │ 0.11022166566302379 │ 0.08308555517966548 │ 0.07808333377276237 │ 2159977.8333333335 │ 8388604 │
│ 2017-01-03 │ 13 │ 0.09131916637221972 │ 0.10051250029355288 │ 0.09484805540802578 │ 2159764.158333333 │ 8388604 │
│ 2017-01-03 │ 14 │ 0.10627000083525975 │ 0.10664000095178684 │ 0.12761138786639398 │ 2159925.575 │ 8388604 │
│ 2017-01-03 │ 15 │ 0.11860277764499187 │ 0.09735888962944349 │ 0.09518999981228263 │ 2160024.1708333334 │ 8388604 │
│ 2017-01-03 │ 16 │ 0.09364305554578702 │ 0.09928194371362527 │ 0.10330889023995647 │ 2159767.8208333333 │ 8388604 │
│ 2017-01-03 │ 17 │ 0.08179833441972732 │ 0.07355138867472609 │ 0.06907388898544013 │ 2159958.658333333 │ 8388604 │
│ 2017-01-03 │ 18 │ 0.11627833272020022 │ 0.11544333280374607 │ 0.11665305603916447 │ 2159795.4 │ 8388604 │
│ 2017-01-03 │ 19 │ 0.08969527805844943 │ 0.084594166961809 │ 0.09310777788826576 │ 2159725.2708333335 │ 8388604 │
│ 2017-01-03 │ 20 │ 0.11439222296079 │ 0.11282916615406673 │ 0.11416083509878566 │ 2159704.8041666667 │ 8388604 │
│ 2017-01-03 │ 21 │ 0.11115861212213834 │ 0.09447166739652554 │ 0.10062499980752666 │ 2159820.345833333 │ 8388604 │
│ 2017-01-03 │ 22 │ 0.07892972175031901 │ 0.09867638902117809 │ 0.11645444415432091 │ 2159670.7 │ 8388604 │
│ 2017-01-03 │ 23 │ 0.09039777840177218 │ 0.07829000012328227 │ 0.0749988881793494 │ 2159820.466666667 │ 8388604 │
│ 2017-01-04 │ 0 │ 0.09144805613905191 │ 0.1059711106431981 │ 0.09972944596859937 │ 2159546.975 │ 8388604 │
│ 2017-01-04 │ 1 │ 0.14011889037986597 │ 0.13926944453269244 │ 0.14483611225926626 │ 2159838.154166667 │ 8388604 │
│ 2017-01-04 │ 2 │ 0.1226708333939314 │ 0.09988777805119753 │ 0.10562166693465164 │ 2159638.379166667 │ 8388604 │
│ 2017-01-04 │ 3 │ 0.08219250074277322 │ 0.08414944401010871 │ 0.08401111047714949 │ 2159485.908333333 │ 8388604 │
│ 2017-01-04 │ 4 │ 0.10787166704734166 │ 0.1202719441925486 │ 0.11356499992931883 │ 2158027.8333333335 │ 8388604 │
│ 2017-01-04 │ 5 │ 0.11913638859987259 │ 0.09875972177833318 │ 0.10850583316059784 │ 2159512.0458333334 │ 8388604 │
│ 2017-01-04 │ 6 │ 0.09163138928512732 │ 0.09442499922588468 │ 0.09266166596013742 │ 2159091.220833333 │ 8388604 │
│ 2017-01-04 │ 7 │ 0.09982722202936808 │ 0.08437194479629398 │ 0.08015055582800415 │ 2158777.3875 │ 8388604 │
│ 2017-01-04 │ 8 │ 0.10133777807156245 │ 0.09118944487224022 │ 0.08264638876426034 │ 2158855.5083333333 │ 8388604 │
│ 2017-01-04 │ 9 │ 0.08981722264240186 │ 0.10164055557300647 │ 0.09779472231748514 │ 2158757.125 │ 8388604 │
│ 2017-01-04 │ 10 │ 0.11853444501757622 │ 0.09684166566779216 │ 0.09657833366654814 │ 2158859.191666667 │ 8388604 │
│ 2017-01-04 │ 11 │ 0.11727194525301457 │ 0.09830833369245132 │ 0.09789777666640778 │ 2158554.4833333334 │ 8388604 │
│ 2017-01-04 │ 12 │ 0.10132972213129203 │ 0.09658222272992134 │ 0.0999491668442109 │ 2158896.575 │ 8388604 │
│ 2017-01-04 │ 13 │ 0.11128361138204733 │ 0.10659083357701699 │ 0.1054338880504171 │ 2158737.8541666665 │ 8388604 │
│ 2017-01-04 │ 14 │ 0.09594555596510569 │ 0.08428166660790642 │ 0.08276361165917479 │ 2158608.025 │ 8388604 │
│ 2017-01-04 │ 15 │ 0.10805555544793606 │ 0.105753610872974 │ 0.10401194337561416 │ 2158767.066666667 │ 8388604 │
│ 2017-01-04 │ 16 │ 0.11955472193658352 │ 0.11093000040079157 │ 0.10593249965071057 │ 2158746.0416666665 │ 8388604 │
│ 2017-01-04 │ 17 │ 0.11997083326180775 │ 0.10015722240010898 │ 0.09378666628229743 │ 2158559.3541666665 │ 8388604 │
│ 2017-01-04 │ 18 │ 0.1046652772774299 │ 0.1116363880224526 │ 0.11287583462544717 │ 2158610.9833333334 │ 8388604 │
│ 2017-01-04 │ 19 │ 0.10411527703205745 │ 0.08298000047604243 │ 0.0721408330030196 │ 2158338.6083333334 │ 8388604 │
│ 2017-01-04 │ 20 │ 0.08938333342472712 │ 0.11257583368569613 │ 0.1211222229215006 │ 2158588.6708333334 │ 8388604 │
│ 2017-01-04 │ 21 │ 0.0880608340104421 │ 0.08018611095224817 │ 0.06615805641389064 │ 2158472.1791666667 │ 8388604 │
│ 2017-01-04 │ 22 │ 0.09270361171414455 │ 0.09680277736236652 │ 0.10423916775228766 │ 2158592.870833333 │ 8388604 │
│ 2017-01-04 │ 23 │ 0.08201500022162994 │ 0.08325611154238383 │ 0.08631694565168194 │ 2158481.45 │ 8388604 │
│ 2017-01-05 │ 0 │ 0.07171250047783057 │ 0.08324750009924173 │ 0.08501305530323104 │ 2158695.9541666666 │ 8388604 │
│ 2017-01-05 │ 1 │ 0.1130638894935449 │ 0.10219722241163254 │ 0.08725333320132146 │ 2158628.4 │ 8388604 │
│ 2017-01-05 │ 2 │ 0.13312555539111295 │ 0.12725722181300322 │ 0.12862388933232674 │ 2158665.625 │ 8388604 │
│ 2017-01-05 │ 3 │ 0.1393444440017144 │ 0.13247888945043088 │ 0.1365322204461942 │ 2157396.720833333 │ 8388604 │
│ 2017-01-05 │ 4 │ 0.14370694470902284 │ 0.12981777669241032 │ 0.14072833495544426 │ 2155651.220833333 │ 8388604 │
│ 2017-01-05 │ 5 │ 0.14033194382985434 │ 0.13922999954471985 │ 0.14209944419562817 │ 2156797.2916666665 │ 8388604 │
│ 2017-01-05 │ 6 │ 0.12512750079234441 │ 0.1053475009277463 │ 0.10296472442132654 │ 2157300.941666667 │ 8388604 │
│ 2017-01-05 │ 7 │ 0.1347247213125229 │ 0.12085194500784079 │ 0.11856944326524778 │ 2157813.6375 │ 8388604 │
│ 2017-01-05 │ 8 │ 0.10596500175694624 │ 0.08730222166826328 │ 0.09061055597073088 │ 2157630.7958333334 │ 8388604 │
│ 2017-01-05 │ 9 │ 0.0983919454117616 │ 0.08596749982486168 │ 0.07934388876504575 │ 2157551.475 │ 8388604 │
│ 2017-01-05 │ 10 │ 0.09058222249150276 │ 0.10841277794291576 │ 0.11727638983090098 │ 2157664.9458333333 │ 8388604 │
│ 2017-01-05 │ 11 │ 0.11202916651964187 │ 0.09312694392477473 │ 0.0871952785916316 │ 2157377.9125 │ 8388604 │
│ 2017-01-05 │ 12 │ 0.12679055494566757 │ 0.1145772211564084 │ 0.12212222339973475 │ 2157558.154166667 │ 8388604 │
│ 2017-01-05 │ 13 │ 0.07202888950705529 │ 0.07222666734208663 │ 0.08037555544869975 │ 2157626.9625 │ 8388604 │
│ 2017-01-05 │ 14 │ 0.10506222241868575 │ 0.11781222249070804 │ 0.12231833242064265 │ 2157781.8 │ 8388604 │
│ 2017-01-05 │ 15 │ 0.1037805562838912 │ 0.09676000000908971 │ 0.10480055649143954 │ 2157442.65 │ 8388604 │
│ 2017-01-05 │ 16 │ 0.12242499962449074 │ 0.10287333329518636 │ 0.10425749932571003 │ 2157471.3333333335 │ 8388604 │
│ 2017-01-05 │ 17 │ 0.11276777796447277 │ 0.09595166655878226 │ 0.09540694419605036 │ 2157592.2 │ 8388604 │
│ 2017-01-05 │ 18 │ 0.10727083335320155 │ 0.09358222189669808 │ 0.09164000034409886 │ 2157496.5416666665 │ 8388604 │
│ 2017-01-05 │ 19 │ 0.12457777845362822 │ 0.11393722246090571 │ 0.09907527814308802 │ 2157470.245833333 │ 8388604 │
│ 2017-01-05 │ 20 │ 0.12004750122626623 │ 0.1025083323319753 │ 0.09964777708131199 │ 2157689.175 │ 8388604 │
│ 2017-01-05 │ 21 │ 0.09280749994019667 │ 0.08252055579796433 │ 0.0738483333378099 │ 2157615.9541666666 │ 8388604 │
│ 2017-01-05 │ 22 │ 0.12193222238371769 │ 0.11657722188780704 │ 0.13440000043871503 │ 2157412.3291666666 │ 8388604 │
│ 2017-01-05 │ 23 │ 0.12363944488267103 │ 0.10315083339810371 │ 0.09123750050396968 │ 2157480.4291666667 │ 8388604 │
│ 2017-01-06 │ 0 │ 0.1460688884059588 │ 0.14045694451779128 │ 0.1415188893957141 │ 2157526.8833333333 │ 8388604 │
│ 2017-01-06 │ 1 │ 0.11137166693806648 │ 0.08777305607994397 │ 0.07703472241216029 │ 2157528.0625 │ 8388604 │
│ 2017-01-06 │ 2 │ 0.0918919451534748 │ 0.08420805583397548 │ 0.0894538877531886 │ 2157556.9916666667 │ 8388604 │
│ 2017-01-06 │ 3 │ 0.11062361126144728 │ 0.11747638809805115 │ 0.12368166759066905 │ 2156442.595833333 │ 8388604 │
│ 2017-01-06 │ 4 │ 0.15077444488803546 │ 0.13361222185194493 │ 0.1423124995548278 │ 2154126.620833333 │ 8388604 │
│ 2017-01-06 │ 5 │ 0.08335222254196803 │ 0.08269611143817504 │ 0.09117472195842614 │ 2155150.825 │ 8388604 │
│ 2017-01-06 │ 6 │ 0.08438222147524357 │ 0.08668333385139704 │ 0.0739586115659525 │ 2155560.6625 │ 8388604 │
│ 2017-01-06 │ 7 │ 0.10765250064432622 │ 0.10485277858873208 │ 0.10753750022655974 │ 2156069.004166667 │ 8388604 │
│ 2017-01-06 │ 8 │ 0.11446972278257211 │ 0.09703583400696517 │ 0.08741638920425127 │ 2156031.0791666666 │ 8388604 │
│ 2017-01-06 │ 9 │ 0.06034638863056898 │ 0.07753250027696292 │ 0.08067666680726689 │ 2155965.3875 │ 8388604 │
│ 2017-01-06 │ 10 │ 0.09297333297630152 │ 0.10178500022739172 │ 0.10280888875325521 │ 2155782.5083333333 │ 8388604 │
│ 2017-01-06 │ 11 │ 0.09418888905396064 │ 0.07414861141393582 │ 0.06712055607931688 │ 2155963.325 │ 8388604 │
│ 2017-01-06 │ 12 │ 0.08447166724751393 │ 0.08855999993781248 │ 0.08750777811316462 │ 2155933.2375 │ 8388604 │
│ 2017-01-06 │ 13 │ 0.1003383339693149 │ 0.09205083325505256 │ 0.10012944447613942 │ 2156056.5125 │ 8388604 │
│ 2017-01-06 │ 14 │ 0.12480916691323121 │ 0.11884888891751567 │ 0.11095666630038371 │ 2155958.2 │ 8388604 │
│ 2017-01-06 │ 15 │ 0.10974416633447011 │ 0.09111055539300045 │ 0.09935527682537212 │ 2156070.279166667 │ 8388604 │
│ 2017-01-06 │ 16 │ 0.09751916639506816 │ 0.08861972220862906 │ 0.08550722183814893 │ 2155921.029166667 │ 8388604 │
│ 2017-01-06 │ 17 │ 0.07831722224752108 │ 0.08212027767052253 │ 0.0777961109939497 │ 2155709.745833333 │ 8388604 │
│ 2017-01-06 │ 18 │ 0.058498055984576545 │ 0.07520055587713917 │ 0.0694680558789211 │ 2155935.3125 │ 8388604 │
│ 2017-01-06 │ 19 │ 0.10868555568158626 │ 0.11651527794698874 │ 0.12083499953150749 │ 2155951.1333333333 │ 8388604 │
│ 2017-01-06 │ 20 │ 0.12161388881504535 │ 0.10482305480788151 │ 0.09871472195178892 │ 2155670.183333333 │ 8388604 │
│ 2017-01-06 │ 21 │ 0.12383972145617009 │ 0.10861527721087137 │ 0.09750805621539864 │ 2155606.875 │ 8388604 │
│ 2017-01-06 │ 22 │ 0.11690166629850865 │ 0.10696944380179048 │ 0.11853694478825977 │ 2155773.7583333333 │ 8388604 │
│ 2017-01-06 │ 23 │ 0.12414777763187886 │ 0.10161472180237373 │ 0.10429055502754636 │ 2155512.1666666665 │ 8388604 │
│ 2017-01-07 │ 0 │ 0.1343536118666331 │ 0.12769444473087788 │ 0.1332177777464191 │ 2155834.0875 │ 8388604 │
│ 2017-01-07 │ 1 │ 0.11266194470226765 │ 0.0973586113502582 │ 0.09657888914225624 │ 2155860.3208333333 │ 8388604 │
│ 2017-01-07 │ 2 │ 0.11044750002523264 │ 0.09410805500422915 │ 0.09852555442485027 │ 2155723.0208333335 │ 8388604 │
│ 2017-01-07 │ 3 │ 0.08386305595437686 │ 0.08519916633764903 │ 0.0883752778211298 │ 2154415.558333333 │ 8388604 │
│ 2017-01-07 │ 4 │ 0.1288394452383121 │ 0.12242389023303986 │ 0.12212777933067022 │ 2153784.058333333 │ 8388604 │
│ 2017-01-07 │ 5 │ 0.11153138938049476 │ 0.09476888937254747 │ 0.10457305488331864 │ 2155218.7666666666 │ 8388604 │
│ 2017-01-07 │ 6 │ 0.13171166566510994 │ 0.12011250015348196 │ 0.12435944544461866 │ 2154716.65 │ 8388604 │
│ 2017-01-07 │ 7 │ 0.12509916710356872 │ 0.10306833442300559 │ 0.0945552766827556 │ 2154865.1708333334 │ 8388604 │
│ 2017-01-07 │ 8 │ 0.08763083387166262 │ 0.0740758336459597 │ 0.07545194388755287 │ 2154932.495833333 │ 8388604 │
│ 2017-01-07 │ 9 │ 0.10922750098009905 │ 0.11359805508206287 │ 0.11970138938825887 │ 2154903.9541666666 │ 8388604 │
│ 2017-01-07 │ 10 │ 0.10072972240547338 │ 0.08804055533061425 │ 0.07780888800819714 │ 2154806.4541666666 │ 8388604 │
│ 2017-01-07 │ 11 │ 0.10521750077605248 │ 0.10553583341340224 │ 0.11668888970743865 │ 2154747.3958333335 │ 8388604 │
│ 2017-01-07 │ 12 │ 0.12571166642010212 │ 0.10943888972202936 │ 0.10949555528350174 │ 2154755.6 │ 8388604 │
│ 2017-01-07 │ 13 │ 0.12495444479087989 │ 0.10555500049764911 │ 0.09935222238515659 │ 2154928.175 │ 8388604 │
│ 2017-01-07 │ 14 │ 0.1259083333114783 │ 0.11717999974886577 │ 0.11581166738566633 │ 2154793.316666667 │ 8388604 │
│ 2017-01-07 │ 15 │ 0.0926530558615923 │ 0.09002861107389132 │ 0.10350138832533655 │ 2154600.5083333333 │ 8388604 │
│ 2017-01-07 │ 16 │ 0.09222888803730409 │ 0.08801750012983879 │ 0.09472444429993629 │ 2154554.8 │ 8388604 │
│ 2017-01-07 │ 17 │ 0.10075027806063493 │ 0.08458694458628695 │ 0.0766372217942262 │ 2154598.1166666667 │ 8388604 │
│ 2017-01-07 │ 18 │ 0.10147361171742281 │ 0.10084583293646573 │ 0.10167166808969341 │ 2154738.033333333 │ 8388604 │
│ 2017-01-07 │ 19 │ 0.09666888838013013 │ 0.10691555536662539 │ 0.11004722107512256 │ 2154661.095833333 │ 8388604 │
│ 2017-01-07 │ 20 │ 0.107993333786726 │ 0.08293166632453601 │ 0.07982333325538396 │ 2154369.433333333 │ 8388604 │
│ 2017-01-07 │ 21 │ 0.10198527822891872 │ 0.0947680551558733 │ 0.09041472167397539 │ 2154595.5708333333 │ 8388604 │
│ 2017-01-07 │ 22 │ 0.06630444427331289 │ 0.07041638835022847 │ 0.06661194364035812 │ 2154370.2666666666 │ 8388604 │
│ 2017-01-07 │ 23 │ 0.10210361058513323 │ 0.11088194486995538 │ 0.10942583356906349 │ 2150071.5416666665 │ 8388604 │
│ 2017-01-08 │ 0 │ 0.07928777815153201 │ 0.069459444253395 │ 0.06329444553897096 │ 2146417.0708333333 │ 8388604 │
│ 2017-01-08 │ 1 │ 0.07957833328594764 │ 0.09387944443151355 │ 0.10099555618071462 │ 2146481.6625 │ 8388604 │
│ 2017-01-08 │ 2 │ 0.10864666737616062 │ 0.08729694454620282 │ 0.07111861116718501 │ 2146330.308333333 │ 8388604 │
│ 2017-01-08 │ 3 │ 0.11374444464842479 │ 0.10025749964018663 │ 0.10012444596504792 │ 2145088.8375 │ 8388604 │
│ 2017-01-08 │ 4 │ 0.11595472134649754 │ 0.12547527806212505 │ 0.13294138964944674 │ 2144110.8 │ 8388604 │
│ 2017-01-08 │ 5 │ 0.11365055628120899 │ 0.0986847228060166 │ 0.09432444444973953 │ 2146392.845833333 │ 8388604 │
│ 2017-01-08 │ 6 │ 0.12372361148397128 │ 0.10569250012437502 │ 0.09282444390846649 │ 2145716.629166667 │ 8388604 │
│ 2017-01-08 │ 7 │ 0.12734777753551801 │ 0.11354888888696829 │ 0.11857166595291346 │ 2145076.004166667 │ 8388604 │
│ 2017-01-08 │ 8 │ 0.13654638901352883 │ 0.1237405551597476 │ 0.11750666750546467 │ 2145025.9 │ 8388604 │
│ 2017-01-08 │ 9 │ 0.1006108338634173 │ 0.08410111075888077 │ 0.08406888853836184 │ 2145091.3958333335 │ 8388604 │
│ 2017-01-08 │ 10 │ 0.0749416667347153 │ 0.0857633337378502 │ 0.08346583395614289 │ 2145428.220833333 │ 8388604 │
│ 2017-01-08 │ 11 │ 0.1148574996739626 │ 0.10503555492808421 │ 0.1168052781043419 │ 2145330.1625 │ 8388604 │
│ 2017-01-08 │ 12 │ 0.12831333316862584 │ 0.11136361037691435 │ 0.10050805537030101 │ 2145388.3125 │ 8388604 │
│ 2017-01-08 │ 13 │ 0.10609694433708985 │ 0.08837166695545116 │ 0.09538111038564238 │ 2145153.2291666665 │ 8388604 │
│ 2017-01-08 │ 14 │ 0.11839194471637408 │ 0.10522361136972905 │ 0.10144666695074799 │ 2145024.7125 │ 8388604 │
│ 2017-01-08 │ 15 │ 0.09263500031083823 │ 0.0926441668222348 │ 0.09044222231799116 │ 2145151.970833333 │ 8388604 │
│ 2017-01-08 │ 16 │ 0.09664055580894153 │ 0.09300361114243666 │ 0.09533194500836543 │ 2145103.2375 │ 8388604 │
│ 2017-01-08 │ 17 │ 0.06105750029285749 │ 0.06733222221955656 │ 0.0654650003251542 │ 2144911.625 │ 8388604 │
│ 2017-01-08 │ 18 │ 0.07457361134390036 │ 0.09279388847450415 │ 0.08531583315537622 │ 2145046.220833333 │ 8388604 │
│ 2017-01-08 │ 19 │ 0.11674750012656053 │ 0.09787583397701383 │ 0.08649083399116837 │ 2144829.995833333 │ 8388604 │
│ 2017-01-08 │ 20 │ 0.08550277842829625 │ 0.08478749987358848 │ 0.08639666609184739 │ 2144794.441666667 │ 8388604 │
│ 2017-01-08 │ 21 │ 0.07088916630794605 │ 0.08920222179343303 │ 0.09048222226944441 │ 2144966.2375 │ 8388604 │
│ 2017-01-08 │ 22 │ 0.0674727775156498 │ 0.07679611099883914 │ 0.06936250114813447 │ 2145056.45 │ 8388604 │
│ 2017-01-08 │ 23 │ 0.1028036113207539 │ 0.1048719447106123 │ 0.09972472208367739 │ 2144932.3958333335 │ 8388604 │
│ 2017-01-09 │ 0 │ 0.08686416670680046 │ 0.07989499901110927 │ 0.07972916645812803 │ 2144815.9541666666 │ 8388604 │
│ 2017-01-09 │ 1 │ 0.09337972228725751 │ 0.10032694448406497 │ 0.09999194458650891 │ 2144916.1083333334 │ 8388604 │
│ 2017-01-09 │ 2 │ 0.14297388829290866 │ 0.143939723012348 │ 0.14694749937286058 │ 2144887.2666666666 │ 8388604 │
│ 2017-01-09 │ 3 │ 0.1277505561709404 │ 0.10705388930315772 │ 0.09926499982442086 │ 2144506.2958333334 │ 8388604 │
│ 2017-01-09 │ 4 │ 0.11586888941625755 │ 0.10868083368986844 │ 0.10546805568446871 │ 2142843.5416666665 │ 8388604 │
│ 2017-01-09 │ 5 │ 0.12128638873497645 │ 0.10324638914316893 │ 0.09664888828750312 │ 2144009.7333333334 │ 8388604 │
│ 2017-01-09 │ 6 │ 0.1040000006556511 │ 0.09181944454709688 │ 0.10551666697962597 │ 2144019.1125 │ 8388604 │
│ 2017-01-09 │ 7 │ 0.10575000122189522 │ 0.08806888945400715 │ 0.08677805510039131 │ 2144813.45 │ 8388604 │
│ 2017-01-09 │ 8 │ 0.08093388887743155 │ 0.08687000001470248 │ 0.08736222303268733 │ 2144926.2416666667 │ 8388604 │
│ 2017-01-09 │ 9 │ 0.07157888871928056 │ 0.08808944451933105 │ 0.08809166823048145 │ 2144657.216666667 │ 8388604 │
│ 2017-01-09 │ 10 │ 0.08930805555234353 │ 0.10994416661560535 │ 0.11131361028334746 │ 2144612.620833333 │ 8388604 │
│ 2017-01-09 │ 11 │ 0.13514194538195928 │ 0.12601555610696474 │ 0.11910888763377443 │ 2144762.529166667 │ 8388604 │
│ 2017-01-09 │ 12 │ 0.1126061109205087 │ 0.10002361107617616 │ 0.08598333329039937 │ 2144622.725 │ 8388604 │
│ 2017-01-09 │ 13 │ 0.12375416643917561 │ 0.11025777785107493 │ 0.10604361165896989 │ 2144595.8375 │ 8388604 │
│ 2017-01-09 │ 14 │ 0.1211983340481917 │ 0.10637722257524729 │ 0.09917222327397515 │ 2144751.6875 │ 8388604 │
│ 2017-01-09 │ 15 │ 0.112248611698548 │ 0.10060722207029661 │ 0.1028222230951845 │ 2144647.9541666666 │ 8388604 │
│ 2017-01-09 │ 16 │ 0.0782936116680503 │ 0.07683305526152254 │ 0.0706611104561792 │ 2144523.591666667 │ 8388604 │
│ 2017-01-09 │ 17 │ 0.09476111065596342 │ 0.10311888841291268 │ 0.09811972207971849 │ 2144521.995833333 │ 8388604 │
│ 2017-01-09 │ 18 │ 0.11918777823448182 │ 0.10715472195297479 │ 0.10485555567623427 │ 2144547.341666667 │ 8388604 │
│ 2017-01-09 │ 19 │ 0.13063638930519422 │ 0.11541194363186756 │ 0.12053555605234578 │ 2144669.3291666666 │ 8388604 │
│ 2017-01-09 │ 20 │ 0.10678861166040103 │ 0.09699916634708643 │ 0.09557527691746752 │ 2144548.1458333335 │ 8388604 │
│ 2017-01-09 │ 21 │ 0.13011000044643878 │ 0.12142499896387259 │ 0.12543805547757075 │ 2144554.675 │ 8388604 │
│ 2017-01-09 │ 22 │ 0.11708944439888 │ 0.10152277760207654 │ 0.08944388896537324 │ 2144577.0875 │ 8388604 │
│ 2017-01-09 │ 23 │ 0.09211972169578075 │ 0.08930694454660018 │ 0.09782361131316672 │ 2144540.654166667 │ 8388604 │
│ 2017-01-10 │ 0 │ 0.09406444448977709 │ 0.09819472224141161 │ 0.09566750023514033 │ 2144636.475 │ 8388604 │
│ 2017-01-10 │ 1 │ 0.1206702783703804 │ 0.10580361125369867 │ 0.09652722185710445 │ 2144368.9916666667 │ 8388604 │
│ 2017-01-10 │ 2 │ 0.1132600004474322 │ 0.09469277746975421 │ 0.10304555604234338 │ 2144448.5083333333 │ 8388604 │
│ 2017-01-10 │ 3 │ 0.12430611203114192 │ 0.12068416606634855 │ 0.13231194442293295 │ 2143543.3958333335 │ 8388604 │
│ 2017-01-10 │ 4 │ 0.16184916657706103 │ 0.1538602779308955 │ 0.16069972179053973 │ 2142588.7395833335 │ 8388604 │
│ 2017-01-10 │ 5 │ 0.11823139016826947 │ 0.09743527844548225 │ 0.08917388814346244 │ 2144151.2875 │ 8388604 │
│ 2017-01-10 │ 6 │ 0.1115133340160052 │ 0.09798611141741276 │ 0.08998027679044754 │ 2144240.925 │ 8388604 │
│ 2017-01-10 │ 7 │ 0.11213833404084046 │ 0.09176611043512821 │ 0.07756277751953651 │ 2144293.5708333333 │ 8388604 │
│ 2017-01-10 │ 8 │ 0.10404833381374677 │ 0.08997944379225373 │ 0.10099055523363253 │ 2144211.2083333335 │ 8388604 │
│ 2017-01-10 │ 9 │ 0.12564666618903478 │ 0.12132472218945622 │ 0.11302694405894727 │ 2144566.183333333 │ 8388604 │
│ 2017-01-10 │ 10 │ 0.11986666619777679 │ 0.10056250027070443 │ 0.10170555565467415 │ 2144418.9583333335 │ 8388604 │
│ 2017-01-10 │ 11 │ 0.09797444405655066 │ 0.08230277722080549 │ 0.08632638885513491 │ 2144463.3291666666 │ 8388604 │
│ 2017-01-10 │ 12 │ 0.08722222267339627 │ 0.09509111108879248 │ 0.09908944442140637 │ 2144219.095833333 │ 8388604 │
│ 2017-01-10 │ 13 │ 0.0932422220086058 │ 0.0889163884955148 │ 0.09115111093463686 │ 2144258.7666666666 │ 8388604 │
│ 2017-01-10 │ 14 │ 0.10688805590487188 │ 0.10064546252704329 │ 0.09340509267252249 │ 2144284.188888889 │ 8388604 │
│ 2017-01-10 │ 15 │ 0.11199437542818487 │ 0.11553569473326206 │ 0.11783152662186087 │ 2144295.9739583335 │ 8388604 │
│ 2017-01-10 │ 16 │ 0.135527638780574 │ 0.1284138893398146 │ 0.12610298461925898 │ 2144386.622916667 │ 8388604 │
│ 2017-01-10 │ 17 │ 0.10659680630390843 │ 0.0840172916961213 │ 0.09128215256181041 │ 2144285.459375 │ 8388604 │
│ 2017-01-10 │ 18 │ 0.11573840339357654 │ 0.10678500034846365 │ 0.11853152723051608 │ 2144309.7645833334 │ 8388604 │
│ 2017-01-10 │ 19 │ 0.1120402783776323 │ 0.09901951387679825 │ 0.095877779443011 │ 2144150.7552083335 │ 8388604 │
│ 2017-01-10 │ 20 │ 0.09373222255768875 │ 0.08531534704379737 │ 0.08557562401329051 │ 2144084.2416666667 │ 8388604 │
│ 2017-01-10 │ 21 │ 0.11311305603012442 │ 0.09948694446745018 │ 0.09770791646975946 │ 2144066.480208333 │ 8388604 │
│ 2017-01-10 │ 22 │ 0.07009770829851429 │ 0.07559298583461592 │ 0.07958479175916484 │ 2144082.529166667 │ 8388604 │
│ 2017-01-10 │ 23 │ 0.1055661811803778 │ 0.10134375019309422 │ 0.0974520837708648 │ 2144062.0364583335 │ 8388604 │
│ 2017-01-11 │ 0 │ 0.14252777760848404 │ 0.13635736093856393 │ 0.13698284624357862 │ 2144168.9166666665 │ 8388604 │
│ 2017-01-11 │ 1 │ 0.10343902801784376 │ 0.09217958241545905 │ 0.09731041762861423 │ 2144060.441666667 │ 8388604 │
│ 2017-01-11 │ 2 │ 0.07343770835238199 │ 0.07530659756157547 │ 0.06305951445441073 │ 2144201.105208333 │ 8388604 │
│ 2017-01-11 │ 3 │ 0.07335465311383207 │ 0.08935868081947168 │ 0.10009062507839796 │ 2143860.6395833334 │ 8388604 │
│ 2017-01-11 │ 4 │ 0.12063319481288394 │ 0.11820798604749143 │ 0.11340687503955754 │ 2141860.2494791667 │ 8388604 │
│ 2017-01-11 │ 5 │ 0.11260131935899456 │ 0.09816305538018545 │ 0.10159437547493629 │ 2143301.3958333335 │ 8388604 │
│ 2017-01-11 │ 6 │ 0.08494430601907273 │ 0.09440993048871557 │ 0.10841117981714585 │ 2143227.73125 │ 8388604 │
│ 2017-01-11 │ 7 │ 0.09903027890250087 │ 0.09638819451599072 │ 0.08026854043224982 │ 2143370.439583333 │ 8388604 │
│ 2017-01-11 │ 8 │ 0.12056861169015368 │ 0.10728958297210435 │ 0.10302326265785572 │ 2143393.1177083333 │ 8388604 │
│ 2017-01-11 │ 9 │ 0.12784069459885358 │ 0.11582201292427878 │ 0.12336562498749118 │ 2143220.684375 │ 8388604 │
│ 2017-01-11 │ 10 │ 0.12128131960829099 │ 0.10299875030759723 │ 0.09292083507631711 │ 2143246.673958333 │ 8388604 │
│ 2017-01-11 │ 11 │ 0.11190687526638309 │ 0.09209750055645903 │ 0.09161652805147848 │ 2143272.565625 │ 8388604 │
│ 2017-01-11 │ 12 │ 0.12043069417898854 │ 0.09808347170862058 │ 0.09894513908172181 │ 2143276.6302083335 │ 8388604 │
│ 2017-01-11 │ 13 │ 0.1019771532776455 │ 0.09134451388381422 │ 0.0860342363230302 │ 2143382.409375 │ 8388604 │
│ 2017-01-11 │ 14 │ 0.11532607930712402 │ 0.08677697167149745 │ 0.0793671875326254 │ 2143348.353515625 │ 8388604 │
└────────────┴────┴──────────────────────┴─────────────────────┴─────────────────────┴────────────────────┴───────────────┘
255 rows in set. Elapsed: 0.072 sec. Processed 189.66 thousand rows, 2.01 MB (2.63 million rows/s., 27.96 MB/s.)
– Q1.4: Over 1 month, how often was each server blocked on disk I/O?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
hadoop003 :) SELECT machine_name,
COUNT(*) AS spikes
FROM logs1
WHERE machine_group = 'Servers'
AND cpu_wio > 0.99
AND log_time >= TIMESTAMP '2016-12-01 00:00:00'
AND log_time < TIMESTAMP '2017-01-01 00:00:00'
GROUP BY machine_name
ORDER BY spikes DESC
LIMIT 10;
SELECT
machine_name,
COUNT(*) AS spikes
FROM logs1
WHERE (machine_group = 'Servers') AND (cpu_wio > 0.99) AND (log_time >= toDateTime('2016-12-01 00:00:00')) AND (log_time < toDateTime('2017-01-01 00:00:00'))
GROUP BY machine_name
ORDER BY spikes DESC
LIMIT 10
Query id: e80beb84-024f-4bf5-8d2f-2fc4a2f04f76
┌─machine_name─┬─spikes─┐
│ sourpatch │ 8861 │
│ louie │ 581 │
│ zotz │ 550 │
│ pieces │ 495 │
│ reeces │ 453 │
│ thing │ 438 │
│ adminhost │ 366 │
│ cadbury │ 357 │
│ york │ 193 │
│ reflect │ 156 │
└──────────────┴────────┘
10 rows in set. Elapsed: 0.130 sec. Processed 1.70 million rows, 17.21 MB (13.03 million rows/s., 131.94 MB/s.)
– Q1.5: Which externally reachable VMs have run low on memory?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
hadoop003 :) SELECT machine_name,
dt,
MIN(mem_free) AS mem_free_min
FROM (
SELECT machine_name,
CAST(log_time AS DATE) AS dt,
mem_free
FROM logs1
WHERE machine_group = 'DMZ'
AND mem_free IS NOT NULL
) AS r
GROUP BY machine_name,
dt
HAVING MIN(mem_free) < 10000
ORDER BY machine_name,
dt;
SELECT
machine_name,
dt,
MIN(mem_free) AS mem_free_min
FROM
(
SELECT
machine_name,
CAST(log_time, 'DATE') AS dt,
mem_free
FROM logs1
WHERE (machine_group = 'DMZ') AND (mem_free IS NOT NULL)
) AS r
GROUP BY
machine_name,
dt
HAVING MIN(mem_free) < 10000
ORDER BY
machine_name ASC,
dt ASC
Query id: 46da1a2a-e529-41c8-87ae-27425b034e07
┌─machine_name─┬─────────dt─┬─mem_free_min─┐
│ chimera │ 2016-11-04 │ 6135.48 │
│ chimera │ 2016-11-26 │ 6045.8667 │
│ chimera │ 2016-11-27 │ 9374.527 │
│ chimera │ 2016-11-30 │ 6933.56 │
│ chimera │ 2016-12-01 │ 6535.8135 │
│ chimera │ 2016-12-02 │ 6393.74 │
│ chimera │ 2016-12-11 │ 8010.5 │
│ chimera │ 2016-12-30 │ 8167.6665 │
│ chimera │ 2016-12-31 │ 6324.6665 │
│ chimera │ 2017-01-01 │ 5972 │
│ chimera │ 2017-01-03 │ 5948 │
│ chimera │ 2017-01-04 │ 7320 │
│ chimera │ 2017-01-05 │ 8144 │
│ chimera │ 2017-01-07 │ 6308.4663 │
│ chimera │ 2017-01-09 │ 9370 │
│ chimera │ 2017-01-10 │ 7147.467 │
└──────────────┴────────────┴──────────────┘
16 rows in set. Elapsed: 0.165 sec. Processed 3.54 million rows, 39.11 MB (21.45 million rows/s., 236.91 MB/s.)
– Q1.6: What is the total hourly network traffic across all file servers?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
hadoop003 :) SELECT dt,
hr,
SUM(net_in) AS net_in_sum,
SUM(net_out) AS net_out_sum,
SUM(net_in) + SUM(net_out) AS both_sum
FROM (
SELECT CAST(log_time AS DATE) AS dt,
EXTRACT(HOUR FROM log_time) AS hr,
COALESCE(bytes_in, 0.0) / 1000000000.0 AS net_in,
COALESCE(bytes_out, 0.0) / 1000000000.0 AS net_out
FROM logs1
WHERE machine_name IN ('allsorts','andes','bigred','blackjack','bonbon',
'cadbury','chiclets','cotton','crows','dove','fireball','hearts','huey',
'lindt','milkduds','milkyway','mnm','necco','nerds','orbit','peeps',
'poprocks','razzles','runts','smarties','smuggler','spree','stride',
'tootsie','trident','wrigley','york')
) AS r
GROUP BY dt,
hr
ORDER BY both_sum DESC
LIMIT 10;
SELECT
dt,
hr,
SUM(net_in) AS net_in_sum,
SUM(net_out) AS net_out_sum,
SUM(net_in) + SUM(net_out) AS both_sum
FROM
(
SELECT
CAST(log_time, 'DATE') AS dt,
toHour(log_time) AS hr,
COALESCE(bytes_in, 0.) / 1000000000. AS net_in,
COALESCE(bytes_out, 0.) / 1000000000. AS net_out
FROM logs1
WHERE machine_name IN ('allsorts', 'andes', 'bigred', 'blackjack', 'bonbon', 'cadbury', 'chiclets', 'cotton', 'crows', 'dove', 'fireball', 'hearts', 'huey', 'lindt', 'milkduds', 'milkyway', 'mnm', 'necco', 'nerds', 'orbit', 'peeps', 'poprocks', 'razzles', 'runts', 'smarties', 'smuggler', 'spree', 'stride', 'tootsie', 'trident', 'wrigley', 'york')
) AS r
GROUP BY
dt,
hr
ORDER BY both_sum DESC
LIMIT 10
Query id: f7bea703-80fe-4289-ab56-5bf1110badf2
┌─────────dt─┬─hr─┬─────────net_in_sum─┬───────net_out_sum─┬───────────both_sum─┐
│ 2017-01-10 │ 17 │ 87.94858539170784 │ 71.38525362068188 │ 159.33383901238972 │
│ 2017-01-10 │ 18 │ 89.64930670570381 │ 69.15606039811598 │ 158.80536710381978 │
│ 2017-01-10 │ 19 │ 73.60724927620797 │ 81.40722209611718 │ 155.01447137232515 │
│ 2017-01-10 │ 20 │ 68.54794632249266 │ 85.50667667204652 │ 154.0546229945392 │
│ 2017-01-11 │ 13 │ 42.000690029221666 │ 69.61384714089117 │ 111.61453717011284 │
│ 2017-01-10 │ 16 │ 52.18435269713253 │ 56.94834975170341 │ 109.13270244883594 │
│ 2017-01-10 │ 15 │ 58.92187069434715 │ 47.70097635240874 │ 106.62284704675588 │
│ 2017-01-10 │ 14 │ 60.980273885919715 │ 44.44881952093317 │ 105.42909340685289 │
│ 2017-01-10 │ 21 │ 45.797732422988794 │ 54.04821061966015 │ 99.84594304264894 │
│ 2017-01-11 │ 4 │ 48.96217601967555 │ 48.37625873783827 │ 97.33843475751382 │
└────────────┴────┴────────────────────┴───────────────────┴────────────────────┘
10 rows in set. Elapsed: 0.202 sec. Processed 1.81 million rows, 20.89 MB (8.95 million rows/s., 103.26 MB/s.)
2.1 Benchmarks on logs2
– Q2.1: Which requests have caused server errors within the past 2 weeks?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
hadoop003 :) select * from logs2 where status_code >= 500
and log_time >= TIMESTAMP '2012-12-18 00:00:00'
order by log_time;
SELECT *
FROM logs2
WHERE (status_code >= 500) AND (log_time >= toDateTime('2012-12-18 00:00:00'))
ORDER BY log_time ASC
Query id: 7360e4e4-d1e7-426c-8593-681405aafe21
┌────────────log_time─┬─client_ip──────┬─request─────────────┬─status_code─┬─object_size─┐
│ 2012-12-21 16:58:00 │ 229.50.247.232 │ //cgi-bin/feedback/ │ 500 │ 410 │
└─────────────────────┴────────────────┴─────────────────────┴─────────────┴─────────────┘
┌────────────log_time─┬─client_ip──────┬─request─────────────┬─status_code─┬─object_size─┐
│ 2012-12-23 23:28:02 │ 229.50.247.232 │ //cgi-bin/feedback/ │ 500 │ 410 │
└─────────────────────┴────────────────┴─────────────────────┴─────────────┴─────────────┘
2 rows in set. Elapsed: 0.017 sec. Processed 2.00 million rows, 4.52 MB (119.00 million rows/s., 269.05 MB/s.)
– Q2.2: During a specific 2-week period, was the user password file leaked?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
hadoop003 :) select * from logs2
where status_code >=200 and status_code< 300
and request like '%/etc/passwd%'
and log_time >= TIMESTAMP '2012-05-06 00:00:00'
and log_time < TIMESTAMP '2012-05-20 00:00:00'
;
SELECT *
FROM logs2
WHERE (status_code >= 200) AND (status_code < 300) AND (request LIKE '%/etc/passwd%') AND (log_time >= toDateTime('2012-05-06 00:00:00')) AND (log_time < toDateTime('2012-05-20 00:00:00'))
Query id: 72302fa3-bc9f-452e-b052-5b01d46651c7
┌────────────log_time─┬─client_ip──────┬─request───────────────────────────────┬─status_code─┬─object_size─┐
│ 2012-05-13 20:17:05 │ 201.183.185.11 │ /?-nd+auto_prepend_file%3D/etc/passwd │ 200 │ 21809 │
└─────────────────────┴────────────────┴───────────────────────────────────────┴─────────────┴─────────────┘
┌────────────log_time─┬─client_ip──────┬─request───────────────────────────────┬─status_code─┬─object_size─┐
│ 2012-05-09 14:46:58 │ 201.183.185.11 │ /?-nd+auto_prepend_file%3D/etc/passwd │ 200 │ 21173 │
└─────────────────────┴────────────────┴───────────────────────────────────────┴─────────────┴─────────────┘
2 rows in set. Elapsed: 0.232 sec. Processed 1.83 million rows, 120.13 MB (7.88 million rows/s., 518.50 MB/s.)
Let search for the whole year 2012.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
hadoop003 :) select * from logs2
where status_code >=200 and status_code< 300
and request like '%/etc/passwd%'
and log_time >= TIMESTAMP '2012-01-01 00:00:00'
and log_time < TIMESTAMP '2013-01-01 00:00:00';
SELECT *
FROM logs2
WHERE (status_code >= 200) AND (status_code < 300) AND (request LIKE '%/etc/passwd%') AND (log_time >= toDateTime('2012-01-01 00:00:00')) AND (log_time < toDateTime('2013-01-01 00:00:00'))
Query id: 12dbfeaa-b8b5-42f5-8dab-bb7b12bdeea9
┌────────────log_time─┬─client_ip──────┬─request───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─status_code─┬─object_size─┐
│ 2012-03-22 21:52:07 │ 157.38.199.221 │ /cgi-bin/events?response_page=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd%00 │ 200 │ 716 │
│ 2012-03-22 21:52:07 │ 157.38.199.221 │ /cgi-bin/events?response_page=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd │ 200 │ 715 │
│ 2012-03-22 21:52:11 │ 157.38.199.221 │ /gcs.html?q=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd%00 │ 200 │ 5935 │
│ 2012-03-22 21:52:11 │ 157.38.199.221 │ /gcs.html?q=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd │ 200 │ 5935 │
│ 2012-03-22 21:52:12 │ 157.38.199.221 │ /cgi-bin/events?response_page=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd%00&month=2&year=2012 │ 200 │ 716 │
│ 2012-03-22 21:52:12 │ 157.38.199.221 │ /cgi-bin/events?response_page=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd&month=2&year=2012 │ 200 │ 715 │
│ 2012-03-22 21:52:12 │ 157.38.199.221 │ /cgi-bin/events?response_page=/events/resp.html&month=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd%00&year=2012 │ 200 │ 5290 │
│ 2012-03-22 21:52:12 │ 157.38.199.221 │ /cgi-bin/events?response_page=/events/resp.html&month=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd&year=2012 │ 200 │ 5290 │
│ 2012-03-22 21:52:12 │ 157.38.199.221 │ /cgi-bin/events?response_page=/events/resp.html&month=2&year=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd%00 │ 200 │ 5425 │
│ 2012-03-22 21:52:12 │ 157.38.199.221 │ /cgi-bin/events?response_page=/events/resp.html&month=2&year=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd │ 200 │ 5422 │
│ 2012-03-22 21:52:24 │ 157.38.199.221 │ /cgi-bin/events?response_page=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd%00&month=4&year=2012 │ 200 │ 716 │
│ 2012-03-22 21:52:24 │ 157.38.199.221 │ /cgi-bin/events?response_page=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd&month=4&year=2012 │ 200 │ 715 │
│ 2012-03-22 21:52:24 │ 157.38.199.221 │ /cgi-bin/events?response_page=/events/resp.html&month=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd%00&year=2012 │ 200 │ 5296 │
│ 2012-03-22 21:52:24 │ 157.38.199.221 │ /cgi-bin/events?response_page=/events/resp.html&month=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd&year=2012 │ 200 │ 5290 │
│ 2012-03-22 21:52:24 │ 157.38.199.221 │ /cgi-bin/events?response_page=/events/resp.html&month=4&year=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd%00 │ 200 │ 5422 │
│ 2012-03-22 21:52:25 │ 157.38.199.221 │ /cgi-bin/events?response_page=/events/resp.html&month=4&year=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd │ 200 │ 5419 │
│ 2012-03-22 21:52:39 │ 157.38.199.221 │ /cgi-bin/events?response_page=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd%00&month=1&year=2012 │ 200 │ 716 │
│ 2012-03-22 21:52:39 │ 157.38.199.221 │ /cgi-bin/events?response_page=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd&month=1&year=2012 │ 200 │ 715 │
│ 2012-03-22 21:52:40 │ 157.38.199.221 │ /cgi-bin/events?response_page=/events/resp.html&month=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd%00&year=2012 │ 200 │ 5296 │
│ 2012-03-22 21:52:40 │ 157.38.199.221 │ /cgi-bin/events?response_page=/events/resp.html&month=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd&year=2012 │ 200 │ 5290 │
│ 2012-03-22 21:52:40 │ 157.38.199.221 │ /cgi-bin/events?response_page=/events/resp.html&month=1&year=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd%00 │ 200 │ 5380 │
│ 2012-03-22 21:52:40 │ 157.38.199.221 │ /cgi-bin/events?response_page=/events/resp.html&month=1&year=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd │ 200 │ 5378 │
│ 2012-03-22 21:52:52 │ 157.38.199.221 │ /cgi-bin/events?response_page=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd%00&month=3&year=2012 │ 200 │ 716 │
│ 2012-03-22 21:52:52 │ 157.38.199.221 │ /cgi-bin/events?response_page=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd&month=3&year=2012 │ 200 │ 715 │
│ 2012-03-22 21:52:52 │ 157.38.199.221 │ /cgi-bin/events?response_page=/events/resp.html&month=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd%00&year=2012 │ 200 │ 5290 │
│ 2012-03-22 21:52:52 │ 157.38.199.221 │ /cgi-bin/events?response_page=/events/resp.html&month=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd&year=2012 │ 200 │ 5296 │
│ 2012-03-22 21:52:52 │ 157.38.199.221 │ /cgi-bin/events?response_page=/events/resp.html&month=3&year=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd%00 │ 200 │ 5427 │
│ 2012-03-22 21:52:52 │ 157.38.199.221 │ /cgi-bin/events?response_page=/events/resp.html&month=3&year=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd │ 200 │ 5418 │
│ 2012-03-22 21:53:04 │ 157.38.199.221 │ /cgi-bin/events?response_page=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd%00&month=5&year=2012 │ 200 │ 716 │
│ 2012-03-22 21:53:04 │ 157.38.199.221 │ /cgi-bin/events?response_page=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd&month=5&year=2012 │ 200 │ 715 │
│ 2012-03-22 21:53:04 │ 157.38.199.221 │ /cgi-bin/events?response_page=/events/resp.html&month=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd%00&year=2012 │ 200 │ 5296 │
│ 2012-03-22 21:53:04 │ 157.38.199.221 │ /cgi-bin/events?response_page=/events/resp.html&month=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd&year=2012 │ 200 │ 5296 │
│ 2012-03-22 21:53:04 │ 157.38.199.221 │ /cgi-bin/events?response_page=/events/resp.html&month=5&year=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd%00 │ 200 │ 5426 │
│ 2012-03-22 21:53:04 │ 157.38.199.221 │ /cgi-bin/events?response_page=/events/resp.html&month=5&year=%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/%2e%2e/etc/passwd │ 200 │ 5423 │
└─────────────────────┴────────────────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴─────────────┴─────────────┘
┌────────────log_time─┬─client_ip──────┬─request───────────────────────────────┬─status_code─┬─object_size─┐
│ 2012-05-09 14:46:58 │ 201.183.185.11 │ /?-nd+auto_prepend_file%3D/etc/passwd │ 200 │ 21173 │
└─────────────────────┴────────────────┴───────────────────────────────────────┴─────────────┴─────────────┘
┌────────────log_time─┬─client_ip──────┬─request───────────────────────────────┬─status_code─┬─object_size─┐
│ 2012-05-13 20:17:05 │ 201.183.185.11 │ /?-nd+auto_prepend_file%3D/etc/passwd │ 200 │ 21809 │
└─────────────────────┴────────────────┴───────────────────────────────────────┴─────────────┴─────────────┘
┌────────────log_time─┬─client_ip───────┬─request────────────────────────────────────────────────────────────────────────────────────────────────────┬─status_code─┬─object_size─┐
│ 2012-10-25 04:19:47 │ 207.173.231.236 │ /?-d%20allow_url_include%3DOn+-d%20auto_prepend_file%3D../../../../../../../../../../../etc/passwd%00%20-n │ 200 │ 7178 │
└─────────────────────┴─────────────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────┴─────────────┴─────────────┘
┌────────────log_time─┬─client_ip───────┬─request────────────────────────────────────────────────────────────────────────────────────────────────────┬─status_code─┬─object_size─┐
│ 2012-10-25 10:33:27 │ 207.173.231.236 │ /?-d%20allow_url_include%3DOn+-d%20auto_prepend_file%3D../../../../../../../../../../../etc/passwd%00%20-n │ 200 │ 4611 │
│ 2012-10-25 10:56:04 │ 207.173.231.236 │ /?-d%20allow_url_include%3DOn+-d%20auto_prepend_file%3D../../../../../../../../../../../etc/passwd%00%20-n │ 200 │ 7178 │
└─────────────────────┴─────────────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────┴─────────────┴─────────────┘
┌────────────log_time─┬─client_ip───────┬─request────────────────────────────────────────────────────────────────────────────────────────────────────┬─status_code─┬─object_size─┐
│ 2012-10-25 16:15:52 │ 207.173.231.236 │ /?-d%20allow_url_include%3DOn+-d%20auto_prepend_file%3D../../../../../../../../../../../etc/passwd%00%20-n │ 200 │ 16109 │
└─────────────────────┴─────────────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────┴─────────────┴─────────────┘
┌────────────log_time─┬─client_ip───────┬─request────────────────────────────────────────────────────────────────────────────────────────────────────┬─status_code─┬─object_size─┐
│ 2012-10-26 06:12:38 │ 207.173.231.236 │ /?-d%20allow_url_include%3DOn+-d%20auto_prepend_file%3D../../../../../../../../../../../etc/passwd%00%20-n │ 200 │ 7307 │
│ 2012-10-26 06:59:33 │ 207.173.231.236 │ /?-d%20allow_url_include%3DOn+-d%20auto_prepend_file%3D../../../../../../../../../../../etc/passwd%00%20-n │ 200 │ 3300 │
│ 2012-10-26 06:59:33 │ 207.173.231.236 │ /?-d%20allow_url_include%3DOn+-d%20auto_prepend_file%3D../../../../../../../../../../../etc/passwd%00%20-n │ 200 │ 2363 │
│ 2012-10-26 06:59:34 │ 207.173.231.236 │ /?-d%20allow_url_include%3DOn+-d%20auto_prepend_file%3D../../../../../../../../../../../etc/passwd%00%20-n │ 200 │ 3819 │
└─────────────────────┴─────────────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────┴─────────────┴─────────────┘
┌────────────log_time─┬─client_ip───────┬─request────────────────────────────────────────────────────────────────────────────────────────────────────┬─status_code─┬─object_size─┐
│ 2012-10-26 11:37:04 │ 207.173.231.236 │ /?-d%20allow_url_include%3DOn+-d%20auto_prepend_file%3D../../../../../../../../../../../etc/passwd%00%20-n │ 200 │ 13500 │
└─────────────────────┴─────────────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────┴─────────────┴─────────────┘
┌────────────log_time─┬─client_ip───────┬─request────────────────────────────────────────────────────────────────────────────────────────────────────┬─status_code─┬─object_size─┐
│ 2012-10-26 15:26:48 │ 207.173.231.236 │ /?-d%20allow_url_include%3DOn+-d%20auto_prepend_file%3D../../../../../../../../../../../etc/passwd%00%20-n │ 200 │ 7174 │
└─────────────────────┴─────────────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────┴─────────────┴─────────────┘
46 rows in set. Elapsed: 4.227 sec. Processed 75.75 million rows, 4.80 GB (17.92 million rows/s., 1.13 GB/s.)
– Q2.3: What was the average path depth for top-level requests in the past month?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
hadoop003 :) SELECT top_level,
AVG(LENGTH(request) - LENGTH(REPLACE(request, '/', ''))) AS depth_avg
FROM (
SELECT SUBSTRING(request FROM 1 FOR len) AS top_level,
request
FROM (
SELECT POSITION(SUBSTRING(request FROM 2), '/') AS len,
request
FROM logs2
WHERE status_code >= 200
AND status_code < 300
AND log_time >= TIMESTAMP '2012-12-01 00:00:00'
) AS r
WHERE len > 0
) AS s
WHERE top_level IN ('/about','/courses','/degrees','/events',
'/grad','/industry','/news','/people',
'/publications','/research','/teaching','/ugrad')
GROUP BY top_level
ORDER BY top_level;
SELECT
top_level,
AVG(LENGTH(request) - LENGTH(REPLACE(request, '/', ''))) AS depth_avg
FROM
(
SELECT
substring(request, 1, len) AS top_level,
request
FROM
(
SELECT
position(substring(request, 2), '/') AS len,
request
FROM logs2
WHERE (status_code >= 200) AND (status_code < 300) AND (log_time >= toDateTime('2012-12-01 00:00:00'))
) AS r
WHERE len > 0
) AS s
WHERE top_level IN ('/about', '/courses', '/degrees', '/events', '/grad', '/industry', '/news', '/people', '/publications', '/research', '/teaching', '/ugrad')
GROUP BY top_level
ORDER BY top_level ASC
Query id: 2cbd0d10-6306-43b6-8bba-dd98fdcc6eb4
┌─top_level─────┬──────────depth_avg─┐
│ /about │ 4.180225643663013 │
│ /courses │ 5.138040610251345 │
│ /degrees │ 3.385165907612232 │
│ /events │ 3.105441792057925 │
│ /grad │ 3.6942030710629687 │
│ /industry │ 4.094312105007292 │
│ /news │ 2.963451710977424 │
│ /people │ 4.485873513820637 │
│ /publications │ 2.245890410958904 │
│ /research │ 5.040612211618354 │
│ /teaching │ 2 │
│ /ugrad │ 2.4527363184079602 │
└───────────────┴────────────────────┘
12 rows in set. Elapsed: 0.855 sec. Processed 4.79 million rows, 255.12 MB (5.61 million rows/s., 298.53 MB/s.)
– Q2.4: During the last 3 months, which clients have made an excessive number of requests?
Get the top 10 request clients;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
hadoop003 :) SELECT client_ip,
COUNT(*) AS num_requests
FROM logs2
WHERE log_time >= TIMESTAMP '2012-10-01 00:00:00'
GROUP BY client_ip
ORDER BY num_requests
DESC limit 10;
SELECT
client_ip,
COUNT(*) AS num_requests
FROM logs2
WHERE log_time >= toDateTime('2012-10-01 00:00:00')
GROUP BY client_ip
ORDER BY num_requests DESC
LIMIT 10
Query id: 1ff484b9-5f4a-4776-b4fa-aff83a8e40ff
┌─client_ip───────┬─num_requests─┐
│ 219.63.173.93 │ 1540391 │
│ 229.50.247.232 │ 743801 │
│ 97.211.80.244 │ 733261 │
│ 152.149.228.251 │ 492221 │
│ 198.156.249.133 │ 370834 │
│ 70.86.124.37 │ 273057 │
│ 67.153.111.239 │ 167287 │
│ 249.92.17.134 │ 112909 │
│ 39.47.154.162 │ 87506 │
│ 121.123.229.213 │ 87490 │
└─────────────────┴──────────────┘
10 rows in set. Elapsed: 0.416 sec. Processed 17.98 million rows, 143.84 MB (43.20 million rows/s., 345.57 MB/s.)
– Q2.5: What are the daily unique visitors (in December)?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
hadoop003 :) select dt, count(distinct client_ip) as uv
from (
select cast(log_time AS DATE) as dt, client_ip
from logs2
where log_time >= TIMESTAMP '2012-12-01 00:00:00'
and log_time < TIMESTAMP '2013-01-01 00:00:00'
) t1
group by dt
order by dt;
SELECT
dt,
countDistinct(client_ip) AS uv
FROM
(
SELECT
CAST(log_time, 'DATE') AS dt,
client_ip
FROM logs2
WHERE (log_time >= toDateTime('2012-12-01 00:00:00')) AND (log_time < toDateTime('2013-01-01 00:00:00'))
) AS t1
GROUP BY dt
ORDER BY dt ASC
Query id: 6fb1e57e-ee48-4f96-bbcc-9fa21e084017
┌─────────dt─┬────uv─┐
│ 2012-12-01 │ 11266 │
│ 2012-12-02 │ 12816 │
│ 2012-12-03 │ 17287 │
│ 2012-12-04 │ 17340 │
│ 2012-12-05 │ 15726 │
│ 2012-12-06 │ 15408 │
│ 2012-12-07 │ 508 │
│ 2012-12-08 │ 11204 │
│ 2012-12-09 │ 12924 │
│ 2012-12-10 │ 15325 │
│ 2012-12-11 │ 15117 │
│ 2012-12-12 │ 14005 │
│ 2012-12-13 │ 13310 │
│ 2012-12-14 │ 11731 │
│ 2012-12-15 │ 10734 │
│ 2012-12-16 │ 11355 │
│ 2012-12-17 │ 13305 │
│ 2012-12-18 │ 13160 │
│ 2012-12-19 │ 12329 │
│ 2012-12-20 │ 9592 │
│ 2012-12-21 │ 863 │
│ 2012-12-22 │ 816 │
│ 2012-12-23 │ 794 │
│ 2012-12-24 │ 727 │
│ 2012-12-25 │ 8062 │
│ 2012-12-26 │ 8799 │
│ 2012-12-27 │ 9435 │
│ 2012-12-28 │ 9185 │
│ 2012-12-29 │ 8247 │
│ 2012-12-30 │ 8328 │
│ 2012-12-31 │ 7824 │
└────────────┴───────┘
31 rows in set. Elapsed: 0.098 sec. Processed 4.79 million rows, 38.33 MB (48.81 million rows/s., 390.48 MB/s.)
– Q2.6: What are the average and maximum data transfer rates (Gbps)?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
hadoop003 :) SELECT AVG(transfer) / 125000000.0 AS transfer_avg,
MAX(transfer) / 125000000.0 AS transfer_max
FROM (
SELECT log_time,
SUM(object_size) AS transfer
FROM logs2
GROUP BY log_time
) AS r;
SELECT
AVG(transfer) / 125000000. AS transfer_avg,
MAX(transfer) / 125000000. AS transfer_max
FROM
(
SELECT
log_time,
SUM(object_size) AS transfer
FROM logs2
GROUP BY log_time
) AS r
Query id: a548aeea-3e71-4b8a-b404-d58b0d6b6eb8
┌──────────transfer_avg─┬──transfer_max─┐
│ 0.0046296999419207785 │ 295.028835936 │
└───────────────────────┴───────────────┘
1 rows in set. Elapsed: 5.484 sec. Processed 75.75 million rows, 908.98 MB (13.81 million rows/s., 165.74 MB/s.)
2.2 Benchmarks on logs3
– Q3.1: Did the indoor temperature reach freezing over the weekend?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
hadoop003 :) SELECT *
FROM logs3
WHERE event_type = 'temperature'
AND event_value <= 32.0
AND log_time >= '2019-11-29 17:00:00.000';
SELECT *
FROM logs3
WHERE (event_type = 'temperature') AND (event_value <= 32.) AND (log_time >= '2019-11-29 17:00:00.000')
Query id: 351db5ca-1c0b-4bfd-9fd0-a8c3795f7748
┌────────────────log_time─┬─device_id───────┬─device_name─┬─device_type─┬─device_floor─┬─event_type──┬─event_unit─┬─event_value─┐
│ 2019-12-01 09:30:13.417 │ 16C448031108ABA │ balcony_5 │ door │ 5 │ temperature │ F │ 32 │
│ 2019-12-01 09:59:58.874 │ 16C448031108ABA │ balcony_5 │ door │ 5 │ temperature │ F │ 32 │
└─────────────────────────┴─────────────────┴─────────────┴─────────────┴──────────────┴─────────────┴────────────┴─────────────┘
2 rows in set. Elapsed: 0.081 sec. Processed 22.47 thousand rows, 313.72 KB (279.00 thousand rows/s., 3.90 MB/s.)
– Q3.4: Over the past 6 months, how frequently were each door opened?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
hadoop003 :) SELECT device_name,
device_floor,
COUNT(*) AS ct
FROM logs3
WHERE event_type = 'door_open'
AND log_time >= '2019-06-01 00:00:00.000'
GROUP BY device_name,
device_floor
ORDER BY ct DESC;
SELECT
device_name,
device_floor,
COUNT(*) AS ct
FROM logs3
WHERE (event_type = 'door_open') AND (log_time >= '2019-06-01 00:00:00.000')
GROUP BY
device_name,
device_floor
ORDER BY ct DESC
Query id: 23981d37-287d-4a2f-90fb-a0477f1ab281
┌─device_name─────────┬─device_floor─┬────ct─┐
│ front_right_1 │ 1 │ 64269 │
│ front_center_1 │ 1 │ 37054 │
│ loading_dock_1 │ 1 │ 27284 │
│ lobby_center_2 │ 2 │ 19123 │
│ front_left_1 │ 1 │ 14679 │
│ stairs_east_3 │ 3 │ 12773 │
│ stairs_north_2 │ 2 │ 11208 │
│ stairs_north_1 │ 1 │ 9874 │
│ balcony_5 │ 5 │ 5284 │
│ stairs_east_left_2 │ 2 │ 4646 │
│ stairs_north_4 │ 4 │ 4086 │
│ lobby_left_2 │ 2 │ 4029 │
│ stairs_east_right_2 │ 2 │ 3140 │
│ lobby_right_2 │ 2 │ 3108 │
│ lobby_left_1 │ 1 │ 2944 │
│ stairs_north_5 │ 5 │ 1731 │
│ lobby_right_1 │ 1 │ 1641 │
│ stairs_east_5 │ 5 │ 1637 │
│ balcony_3 │ 3 │ 1619 │
│ stairs_east_4 │ 4 │ 1093 │
└─────────────────────┴──────────────┴───────┘
20 rows in set. Elapsed: 0.042 sec. Processed 245.76 thousand rows, 2.71 MB (5.83 million rows/s., 64.21 MB/s.)
– Q3.5: Where in the building do large temperature variations occur in winter and summer?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
hadoop003 :)
WITH temperature AS (
SELECT dt,
device_name,
device_type,
device_floor
WITH temperature AS (
SELECT dt,
device_name,
device_type,
device_floor
FROM (
SELECT dt,
hr,
device_name,
device_type,
device_floor,
AVG(event_value) AS temperature_hourly_avg
FROM (
SELECT CAST(log_time AS DATE) AS dt,
EXTRACT(HOUR FROM log_time) AS hr,
device_name,
device_type,
device_floor,
event_value
FROM logs3
WHERE event_type = 'temperature'
) AS r
GROUP BY dt,
hr,
device_name,
device_type,
device_floor
) AS s
GROUP BY dt,
device_name,
device_type,
device_floor
HAVING MAX(temperature_hourly_avg) - MIN(temperature_hourly_avg) >= 25.0
)
SELECT DISTINCT device_name,
device_type,
device_floor,
'WINTER'
FROM temperature
WHERE dt >= DATE '2018-12-01'
AND dt < DATE '2019-03-01'
UNION
SELECT DISTINCT device_name,
device_type,
device_floor,
'SUMMER'
FROM temperature
WHERE dt >= DATE '2019-06-01'
AND dt < DATE '2019-09-01';
WITH temperature AS
(
SELECT
dt,
device_name,
device_type,
device_floor
FROM
(
SELECT
dt,
hr,
device_name,
device_type,
device_floor,
AVG(event_value) AS temperature_hourly_avg
FROM
(
SELECT
CAST(log_time, 'DATE') AS dt,
toHour(log_time) AS hr,
device_name,
device_type,
device_floor,
event_value
FROM logs3
WHERE event_type = 'temperature'
) AS r
GROUP BY
dt,
hr,
device_name,
device_type,
device_floor
) AS s
GROUP BY
dt,
device_name,
device_type,
device_floor
HAVING (MAX(temperature_hourly_avg) - MIN(temperature_hourly_avg)) >= 25.
)
SELECT DISTINCT
device_name,
device_type,
device_floor,
'WINTER'
FROM temperature
WHERE (dt >= toDate('2018-12-01')) AND (dt < toDate('2019-03-01'))
SELECT DISTINCT
device_name,
device_type,
device_floor,
'SUMMER'
FROM temperature
WHERE (dt >= toDate('2019-06-01')) AND (dt < toDate('2019-09-01'))
Query id: cf696408-06cc-4d54-8328-f6ef229b3a38
┌─device_name────┬─device_type─┬─device_floor─┬─'WINTER'─┐
│ balcony_5 │ door │ 5 │ SUMMER │
│ front_center_1 │ door │ 1 │ SUMMER │
│ balcony_3 │ door │ 3 │ SUMMER │
│ front_left_1 │ door │ 1 │ SUMMER │
│ front_right_1 │ door │ 1 │ SUMMER │
└────────────────┴─────────────┴──────────────┴──────────┘
┌─device_name────┬─device_type─┬─device_floor─┬─'WINTER'─┐
│ loading_dock_1 │ door │ 1 │ WINTER │
│ balcony_3 │ door │ 3 │ WINTER │
│ balcony_5 │ door │ 5 │ WINTER │
│ front_right_1 │ door │ 1 │ WINTER │
│ front_left_1 │ door │ 1 │ WINTER │
│ front_center_1 │ door │ 1 │ WINTER │
└────────────────┴─────────────┴──────────────┴──────────┘
11 rows in set. Elapsed: 0.108 sec. Processed 138.37 thousand rows, 2.36 MB (1.28 million rows/s., 21.79 MB/s.)
– Q3.6: For each device category, what are the monthly power consumption metrics?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
hadoop003 :)
SELECT yr,
mo,
SUM(coffee_hourly_avg) AS coffee_monthly_sum,
AVG(coffee_hourly_avg) AS coffee_monthly_avg,
SUM(printer_hourly_avg) AS printer_monthly_sum,
AVG(printer_hourly_avg) AS printer_monthly_avg,
SUM(projector_hourly_avg) AS projector_monthly_sum,
AVG(projector_hourly_avg) AS projector_monthly_avg,
SUM(vending_hourly_avg) AS vending_monthly_sum,
AVG(vending_hourly_avg) AS vending_monthly_avg
FROM (
SELECT dt,
yr,
mo,
hr,
AVG(coffee) AS coffee_hourly_avg,
AVG(printer) AS printer_hourly_avg,
AVG(projector) AS projector_hourly_avg,
AVG(vending) AS vending_hourly_avg
FROM (
SELECT CAST(log_time AS DATE) AS dt,
EXTRACT(YEAR FROM log_time) AS yr,
EXTRACT(MONTH FROM log_time) AS mo,
EXTRACT(HOUR FROM log_time) AS hr,
CASE WHEN device_name LIKE 'coffee%' THEN event_value END AS coffee,
CASE WHEN device_name LIKE 'printer%' THEN event_value END AS printer,
CASE WHEN device_name LIKE 'projector%' THEN event_value END AS projector,
CASE WHEN device_name LIKE 'vending%' THEN event_value END AS vending
FROM logs3
WHERE device_type = 'meter'
) AS r
GROUP BY dt,
yr,
mo,
hr
) AS s
GROUP BY yr,
mo
ORDER BY yr,
mo;
SELECT
yr,
mo,
SUM(coffee_hourly_avg) AS coffee_monthly_sum,
AVG(coffee_hourly_avg) AS coffee_monthly_avg,
SUM(printer_hourly_avg) AS printer_monthly_sum,
AVG(printer_hourly_avg) AS printer_monthly_avg,
SUM(projector_hourly_avg) AS projector_monthly_sum,
AVG(projector_hourly_avg) AS projector_monthly_avg,
SUM(vending_hourly_avg) AS vending_monthly_sum,
AVG(vending_hourly_avg) AS vending_monthly_avg
FROM
(
SELECT
dt,
yr,
mo,
hr,
AVG(coffee) AS coffee_hourly_avg,
AVG(printer) AS printer_hourly_avg,
AVG(projector) AS projector_hourly_avg,
AVG(vending) AS vending_hourly_avg
FROM
(
SELECT
CAST(log_time, 'DATE') AS dt,
toYear(log_time) AS yr,
toMonth(log_time) AS mo,
toHour(log_time) AS hr,
multiIf(device_name LIKE 'coffee%', event_value, NULL) AS coffee,
multiIf(device_name LIKE 'printer%', event_value, NULL) AS printer,
multiIf(device_name LIKE 'projector%', event_value, NULL) AS projector,
multiIf(device_name LIKE 'vending%', event_value, NULL) AS vending
FROM logs3
WHERE device_type = 'meter'
) AS r
GROUP BY
dt,
yr,
mo,
hr
) AS s
GROUP BY
yr,
mo
ORDER BY
yr ASC,
mo ASC
Query id: 2b6b7569-88fb-4281-8243-d3352bf3d6f3
┌───yr─┬─mo─┬─coffee_monthly_sum─┬─coffee_monthly_avg─┬─printer_monthly_sum─┬─printer_monthly_avg─┬─projector_monthly_sum─┬─projector_monthly_avg─┬─vending_monthly_sum─┬─vending_monthly_avg─┐
│ 2017 │ 9 │ 721638.3414064788 │ 1252.8443427195812 │ 65268.73919188456 │ 113.31378331924402 │ 174895.77151746233 │ 330.615825174787 │ 176421.94104524463 │ 388.5945838000983 │
│ 2017 │ 10 │ 702508.0664338224 │ 1256.722837985371 │ 66960.70685831294 │ 120.00126677117015 │ 131314.92843738297 │ 270.7524297677999 │ 212072.42540618268 │ 379.37822076240195 │
│ 2017 │ 11 │ 903660.59565171 │ 1258.5802167851116 │ 79376.14433888876 │ 110.55173306251916 │ 178488.04901201397 │ 304.0682265962759 │ 257457.22712476418 │ 358.5755252434042 │
│ 2017 │ 12 │ 931357.6830229153 │ 1251.8248427727356 │ 76585.03687197338 │ 102.93687751609326 │ 112836.92815090147 │ 198.6565636459533 │ 241257.5071419921 │ 324.27084293278506 │
│ 2018 │ 1 │ 800695.0232504867 │ 1268.9303062606762 │ 52913.86179851606 │ 83.85715023536618 │ 36392.4805845717 │ 83.46899216644884 │ 191628.70119383498 │ 303.69049317564975 │
│ 2018 │ 2 │ 788872.0573169806 │ 1188.0603272846092 │ 85621.32358955598 │ 128.94777649029515 │ 83922.79400835957 │ 176.67956633338858 │ 230227.25133465911 │ 346.7277881546071 │
│ 2018 │ 3 │ 896816.2190436663 │ 1205.3981438758956 │ 88325.94803945154 │ 118.71767209603702 │ 114322.79839365483 │ 193.76745490449971 │ 269070.60609120055 │ 361.654040445162 │
│ 2018 │ 4 │ 581954.6102904766 │ 1199.9064129700548 │ 55146.138460788185 │ 113.70337826966636 │ 107909.634197731 │ 260.6512903326836 │ 172977.5683584701 │ 356.6547801205569 │
│ 2018 │ 5 │ 810269.3225697604 │ 1246.568188568862 │ 69687.03583794403 │ 107.21082436606774 │ 83644.7815917367 │ 143.4730387508348 │ 248284.77976738202 │ 381.9765842575108 │
│ 2018 │ 6 │ 824150.058894909 │ 1252.5076882901353 │ 52047.11873700281 │ 79.09896464590094 │ 61491.79513703262 │ 113.24455826341182 │ 242561.98528653936 │ 368.63523599778017 │
│ 2018 │ 7 │ 920527.341169839 │ 1237.2679316798913 │ 54001.80526436985 │ 72.58307159189495 │ 110136.29401019623 │ 175.65597130812796 │ 284977.68054289336 │ 383.0345168587276 │
│ 2018 │ 8 │ 934028.5503494722 │ 1255.4147182116562 │ 56534.79015953166 │ 75.98762118216621 │ 18440.748190359893 │ 37.254036748201806 │ 281409.0307800981 │ 378.23794459690606 │
│ 2018 │ 9 │ 796294.7250120321 │ 1201.047850696881 │ 59750.62790355883 │ 90.12161071426671 │ 123357.51466599092 │ 219.10748608524142 │ 251368.5907741442 │ 379.1381459640184 │
│ 2018 │ 10 │ 876930.348378458 │ 1178.6698230893253 │ 68192.54789305256 │ 91.65665039388784 │ 161682.95582566145 │ 259.9404434496165 │ 285017.38156064023 │ 383.08787844172076 │
│ 2018 │ 11 │ 220190.80577936105 │ 1177.4909399965832 │ 17258.738257541885 │ 92.29271795476944 │ 31282.504059750238 │ 215.7414073086223 │ 74832.72439675417 │ 400.1749967740865 │
│ 2018 │ 12 │ 600604.6369429254 │ 1213.3427008947988 │ 40572.284658587625 │ 81.96421143149016 │ 45621.9010882252 │ 103.21697078783981 │ 174784.2060631482 │ 353.09940618817814 │
│ 2019 │ 1 │ 875861.8015196376 │ 1177.2336041930612 │ 62356.908033973996 │ 83.81304843276075 │ 38427.20028916452 │ 68.37580122627138 │ 266395.02450662036 │ 358.0578286379306 │
│ 2019 │ 2 │ 766839.5051314125 │ 1141.130215969364 │ 58769.95527064847 │ 87.45529058132213 │ 98888.7672055376 │ 174.4069968351633 │ 263572.6687568532 │ 392.2212332691268 │
│ 2019 │ 3 │ 875531.8723175356 │ 1176.7901509644296 │ 64134.018569618085 │ 86.2016378623899 │ 137670.7954831731 │ 220.9804100853501 │ 307147.87856089295 │ 412.8331701087271 │
│ 2019 │ 4 │ 882405.7822994066 │ 1225.5635865269537 │ 62498.886241078144 │ 86.80400866816409 │ 118655.91830720668 │ 190.7651419730011 │ 310192.12705153367 │ 430.8223986826857 │
│ 2019 │ 5 │ 915804.9453381413 │ 1230.920625454491 │ 62733.50787805458 │ 84.31923101889056 │ 51908.47672565359 │ 92.36383759013094 │ 333347.07849929924 │ 448.0471485205635 │
│ 2019 │ 6 │ 743714.4735556262 │ 1264.8205332578677 │ 47914.37351050592 │ 81.48702977977197 │ 33701.157666174215 │ 99.12105195933593 │ 258663.9435447938 │ 439.90466589250644 │
│ 2019 │ 7 │ 936697.9580959905 │ 1259.0026318494495 │ 60426.117893187904 │ 81.21790039406976 │ 10876.808554053554 │ 30.213357094593206 │ 322797.2259733416 │ 433.86723921148064 │
│ 2019 │ 8 │ 930640.755040978 │ 1250.8612298937876 │ 60639.08568100216 │ 81.50414742070183 │ 13063.088331067776 │ 44.58391921866135 │ 336075.45302452653 │ 451.71431858135287 │
│ 2019 │ 9 │ 862784.4318728528 │ 1198.3117109345178 │ 63348.6489027506 │ 87.98423458715362 │ 56973.39566326607 │ 126.32681965247465 │ 322162.84531000565 │ 447.4483962638967 │
│ 2019 │ 10 │ 900170.2429565514 │ 1209.9062405329992 │ 64953.121362668586 │ 87.30258247670508 │ 98168.02805722524 │ 218.6370335350228 │ 310577.09491537174 │ 417.44233187549963 │
│ 2019 │ 11 │ 881836.9061517064 │ 1224.773480766259 │ 61941.82869595774 │ 86.03031763327463 │ 181716.37704881537 │ 294.9941185857392 │ 258322.18736711267 │ 358.7808157876565 │
│ 2019 │ 12 │ 45118.35452979575 │ 1253.28762582766 │ 2884.8668441554055 │ 80.13519011542793 │ 13576.481448950077 │ 377.1244846930577 │ 12249.124855753844 │ 340.25346821538454 │
└──────┴────┴────────────────────┴────────────────────┴─────────────────────┴─────────────────────┴───────────────────────┴───────────────────────┴─────────────────────┴─────────────────────┘
28 rows in set. Elapsed: 12.469 sec. Processed 108.96 million rows, 1.52 GB (8.74 million rows/s., 121.86 MB/s.)
