InfluxDB v1.2のサブクエリを試す

先日、InfluxDB v1.2がリリースされ、サブクエリが使えるようになったので、早速試してみました。

InfluxDB 1.2 released with subqueries and 50% better write throughput on larger hardware

サブクエリとは

v1.1までのInfluxDBでは、SQLライクに複数のフィールドの値を計算できるものの、集計関数の中には単一の値しか書くことが出来ませんでした。

o select value1 * 100 from ... # OK

x select mean(value1 * 100) from ... 　   # 関数の中には計算式を書けない
o select mean(value1) * 100 from ... 　   # これはOK

x select mean(value1 * value2) from ...
o select mean(value1) * mean(value2) from ... # 関数の結果を計算はできるがやりたいのはコレジャナイ

上記のような計算を行う場合、従来は(value1 * value2)のクエリの計算結果を一旦measurementとして保存し、その結果に対して再度クエリをかけて平均を取る、ということを行う必要がありました。しかしv1.2でサポートされたサブクエリを使うと、同等のことが1回のクエリで可能になります。

select mean(value) from (select value1 * value2 from ...)

使用例その1: 加重平均

まずは上記で例に挙げたような加重平均を試してみます。サンプルデータは下記のとおり。(表示を見やすくするため、precision rfc3339としています)

> select * from sample_data
name: sample_data
time       count host  value
----       ----- ----  -----
2017-01-26T00:00:00Z 33    host1 0.1686618
2017-01-26T00:00:00Z 10    host2 0.772844364
2017-01-26T00:01:00Z 60    host1 0.025078533
2017-01-26T00:01:00Z 86    host2 0.589876001
2017-01-26T00:02:00Z 28    host1 0.795133127
2017-01-26T00:02:00Z 41    host2 0.801407917
2017-01-26T00:03:00Z 91    host1 0.081822447
2017-01-26T00:03:00Z 90    host2 0.765902936
2017-01-26T00:04:00Z 61    host1 0.51287518
2017-01-26T00:04:00Z 38    host2 0.53270741
2017-01-26T00:05:00Z 51    host1 0.060094054
2017-01-26T00:05:00Z 79    host2 0.128055132
2017-01-26T00:06:00Z 72    host1 0.190071427
2017-01-26T00:06:00Z 35    host2 0.005363927
2017-01-26T00:07:00Z 25    host1 0.211173586
2017-01-26T00:07:00Z 61    host2 0.194961101
2017-01-26T00:08:00Z 41    host1 0.211522929
2017-01-26T00:08:00Z 16    host2 0.022634187
2017-01-26T00:09:00Z 75    host1 0.131138756
2017-01-26T00:09:00Z 89    host2 0.819108491

countを、各valueに対する重み値とすると、重み付けした値(value * count)は問題なく計算できます。

> select host, value * count as weighed_value from sample_data
name: sample_data
time                 host  weighed_value
----                 ----  -------------
2017-01-26T00:00:00Z host1 5.5658394
2017-01-26T00:00:00Z host2 7.72844364
2017-01-26T00:01:00Z host1 1.50471198
2017-01-26T00:01:00Z host2 50.729336086
2017-01-26T00:02:00Z host1 22.263727556000003
2017-01-26T00:02:00Z host2 32.857724597
2017-01-26T00:03:00Z host1 7.445842677000001
2017-01-26T00:03:00Z host2 68.93126424
2017-01-26T00:04:00Z host1 31.28538598
2017-01-26T00:04:00Z host2 20.24288158
2017-01-26T00:05:00Z host1 3.064796754
2017-01-26T00:05:00Z host2 10.116355427999999
2017-01-26T00:06:00Z host1 13.685142743999998
2017-01-26T00:06:00Z host2 0.187737445
2017-01-26T00:07:00Z host1 5.27933965
2017-01-26T00:07:00Z host2 11.892627161
2017-01-26T00:08:00Z host1 8.672440089
2017-01-26T00:08:00Z host2 0.362146992
2017-01-26T00:09:00Z host1 9.8354067
2017-01-26T00:09:00Z host2 72.900655699

ここから、各時刻における全ホスト間の平均値は、元のクエリをfrom句にいれて、以下のように計算できます。

> select mean(*) from (select host, value * count as weighed_value from sample_data) where time >= '2017-01-26T00:00:00Z' and time < '2017-01-26T00:10:00Z' group by time(1m)
name: sample_data
time                 mean_weighed_value
----                 ------------------
2017-01-26T00:00:00Z 6.64714152
2017-01-26T00:01:00Z 26.117024033000003
2017-01-26T00:02:00Z 27.560726076500004
2017-01-26T00:03:00Z 38.1885534585
2017-01-26T00:04:00Z 25.76413378
2017-01-26T00:05:00Z 6.590576090999999
2017-01-26T00:06:00Z 6.936440094499999
2017-01-26T00:07:00Z 8.5859834055
2017-01-26T00:08:00Z 4.5172935405
2017-01-26T00:09:00Z 41.368031199499995

それぞれの時刻において、host1とhost2の平均値となっています。
また、各ホスト毎に10分間の平均値も、下記のようにして計算できます。

> select mean(*) from (select host, value * count as weighed_value from sample_data) where time >= '2017-01-26T00:00:00Z' and time < '2017-01-26T00:10:00Z' group by time(10m),host
name: sample_data
tags: host=host1
time                 mean_new_value
----                 --------------
2017-01-26T00:00:00Z 10.860263353000002

name: sample_data
tags: host=host2
time                 mean_new_value
----                 --------------
2017-01-26T00:00:00Z 27.5949172868

使用例その2: 累積値

ネットワークのトラフィック等をSNMPで取得すると累積値として結果が得られますが、これもInfluxDBでは扱いづらいデータでした。

> select * from sample_data
name: sample_data
time                 host  value
----                 ----  -----
2017-01-25T23:59:00Z host1 0
2017-01-25T23:59:00Z host2 0
2017-01-26T00:00:00Z host1 0.380175335
2017-01-26T00:00:00Z host2 0.654674526
2017-01-26T00:01:00Z host1 0.548469287
2017-01-26T00:01:00Z host2 1.518483451
2017-01-26T00:02:00Z host1 0.912088251
2017-01-26T00:02:00Z host2 1.618768565
2017-01-26T00:03:00Z host1 1.024910481
2017-01-26T00:03:00Z host2 1.661952017
2017-01-26T00:04:00Z host1 1.194558728
2017-01-26T00:04:00Z host2 2.532767878
2017-01-26T00:05:00Z host1 1.518409392
2017-01-26T00:05:00Z host2 3.402324839
2017-01-26T00:06:00Z host1 1.603926464
2017-01-26T00:06:00Z host2 4.058450398
2017-01-26T00:07:00Z host1 2.06296154
2017-01-26T00:07:00Z host2 4.794882977
2017-01-26T00:08:00Z host1 2.408185365
2017-01-26T00:08:00Z host2 4.902904745
2017-01-26T00:09:00Z host1 3.351145834
2017-01-26T00:09:00Z host2 4.915808337

derivative関数を使うと差分を計算できますが、その結果をさらに集計する、という事が従来は出来ませんでした。

> select derivative(value,1m) from sample_data group by host
name: sample_data
tags: host=host1
time                 derivative
----                 ----------
2017-01-26T00:00:00Z 0.380175335
2017-01-26T00:01:00Z 0.16829395199999997
2017-01-26T00:02:00Z 0.36361896400000004
2017-01-26T00:03:00Z 0.11282223000000002
2017-01-26T00:04:00Z 0.16964824700000003
2017-01-26T00:05:00Z 0.32385066399999984
2017-01-26T00:06:00Z 0.08551707200000003
2017-01-26T00:07:00Z 0.45903507599999993
2017-01-26T00:08:00Z 0.34522382500000015
2017-01-26T00:09:00Z 0.942960469

name: sample_data
tags: host=host2
time                 derivative
----                 ----------
2017-01-26T00:00:00Z 0.654674526
2017-01-26T00:01:00Z 0.8638089250000001
2017-01-26T00:02:00Z 0.10028511400000006
2017-01-26T00:03:00Z 0.043183451999999845
2017-01-26T00:04:00Z 0.8708158610000001
2017-01-26T00:05:00Z 0.8695569609999998
2017-01-26T00:06:00Z 0.6561255589999999
2017-01-26T00:07:00Z 0.7364325790000006
2017-01-26T00:08:00Z 0.10802176799999952
2017-01-26T00:09:00Z 0.012903591999999797

複数ホストがある中で、最大、最小の値を取得したい場合、サブクエリを使うと以下のように書くことが出来ます。

> select max(derivative) as max, min(derivative) as min from (select derivative(value,1m) from sample_data group by host) where time >= '2017-01-26T00:00:00Z' and time < '2017-01-27T00:00:00Z' group by time(1m) fill(none)
name: sample_data
time                 max                 min
----                 ---                 ---
2017-01-26T00:00:00Z 0.654674526         0.380175335
2017-01-26T00:01:00Z 0.8638089250000001  0.16829395199999997
2017-01-26T00:02:00Z 0.36361896400000004 0.10028511400000006
2017-01-26T00:03:00Z 0.11282223000000002 0.043183451999999845
2017-01-26T00:04:00Z 0.8708158610000001  0.16964824700000003
2017-01-26T00:05:00Z 0.8695569609999998  0.32385066399999984
2017-01-26T00:06:00Z 0.6561255589999999  0.08551707200000003
2017-01-26T00:07:00Z 0.7364325790000006  0.45903507599999993
2017-01-26T00:08:00Z 0.34522382500000015 0.10802176799999952
2017-01-26T00:09:00Z 0.942960469         0.012903591999999797

また、各ホスト毎に、10分間の最大値、最小値も取得可能です。

> select max(derivative) as max, min(derivative) as min from (select derivative(value,1m) from sample_data group by host) where time >= '2017-01-26T00:00:00Z' and time < '2017-01-27T00:00:00Z' group by time(10m),host fill(none)
name: sample_data
tags: host=host1
time                 max         min
----                 ---         ---
2017-01-26T00:00:00Z 0.942960469 0.08551707200000003

name: sample_data
tags: host=host2
time                 max                min
----                 ---                ---
2017-01-26T00:00:00Z 0.8708158610000001 0.012903591999999797