時系列データベースInfluxDBのクエリ文について、データの期間の指定や集約処理など、基本的な構文をまとめた。
はじめに
InfluxDBは時系列データの扱いに特化したデータベースである。概要は以下の記事を参照。
時系列データベースInfluxDB入門
InfluxDBではデータを取得するために、SQLに似た独自のクエリ文を採用している。クエリ文では、取得するデータの期間を指定したり、移動平均や最大値を計算する処理などの処理を行うことができる。本記事では、クエリ文の基本的な構文をまとめた。
また、本記事ではPythonのAPIを使っているが、他の言語やCLIで扱う場合もクエリの構文自体は変わらない。
本記事の環境は以下の通り。
- Linux Mint 19.3 (Cinnamon)
- InfluxDB 1.8.0
また、Pythonのバージョンは以下の通り。
|
バージョン |
| Python |
3.7.6 |
| NumPy |
1.18.1 |
| Pandas |
1.0.1 |
| Influxdb-python |
5.2.2 |
以降では、ライブラリを以下のようにインポートすることを前提とする。
1
2
3
|
import numpy as np
import pandas as pd
import influxdb
|
使用するデータ
まず、以下のPythonコードを実行し、テスト用のデータをInfluxDBに書き込む。
1
2
3
4
5
6
7
|
client = influxdb.DataFrameClient()
client.create_database("test")
array = np.arange(240).reshape(-1, 2)
index = pd.date_range(pd.Timestamp("2020-05-01 00:00:00"),
pd.Timestamp("2020-05-05 23:00:00"), freq="1H")
df = pd.DataFrame(array, index=index, columns=["A", "B"])
client.write_points(df, "meas1", database="test")
|
このコードでは、testという名前のデータベースを作成し、meas1という名前のmeasurementでデータを書き込む。
データdfは以下に示すように、120行×2列の配列で、時刻は2020年5月1日0時〜5月5日23時まで1時間周期（120時間）とする。field keyは、"A", "B"である。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
>>> print(df)
A B
2020-05-01 00:00:00 0 1
2020-05-01 01:00:00 2 3
2020-05-01 02:00:00 4 5
2020-05-01 03:00:00 6 7
2020-05-01 04:00:00 8 9
... ... ...
2020-05-05 19:00:00 230 231
2020-05-05 20:00:00 232 233
2020-05-05 21:00:00 234 235
2020-05-05 22:00:00 236 237
2020-05-05 23:00:00 238 239
[120 rows x 2 columns]
|
基本構文
最も基本となるクエリ文は、以下のようにSELECT <field名>とFROM <measurement名>の2つからなる。
1
|
SELECT <field名> FROM <measurement名>
|
公式リファレンスでは、それぞれSELECT節 (clause), FROM節と呼んでいる。上記のクエリによって、指定したmeasurementのfieldのデータを全て取得する。
例：meas1のA のデータを全て取得する。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
>>> res = client.query("SELECT A FROM meas1", database="test")
>>> list(res.values())[0]
A
2020-05-01 00:00:00+00:00 0
2020-05-01 01:00:00+00:00 2
2020-05-01 02:00:00+00:00 4
2020-05-01 03:00:00+00:00 6
2020-05-01 04:00:00+00:00 8
... ...
2020-05-05 19:00:00+00:00 230
2020-05-05 20:00:00+00:00 232
2020-05-05 21:00:00+00:00 234
2020-05-05 22:00:00+00:00 236
2020-05-05 23:00:00+00:00 238
[120 rows x 1 columns]
|
ここで、list(res.values())[0]としたのは、PythonのAPIの仕様（queryメソッドの戻り値が辞書）のためである。
また、複数のfield名を得るには、カンマ,で区切って指定する。
1
|
SELECT <field名>, <field名> FROM <measurement名>
|
全てのfield名を得るには、アスタリスク*を用いる。
1
|
SELECT * FROM <measurement名>
|
集約処理をする
SELECT節のfield名に関数を追加することによって、移動平均や最大値をとるなどの集約処理を行える。集約処理を行う時間幅は、GROUP BY節で指定する。
1
|
SELECT <関数名>(<field名>) FROM <measurement名> GROUP BY time(<時間幅>)
|
主な関数を下表に示す。
| 関数名 |
意味 |
MEAN |
平均 |
MEDIAN |
中央値 |
SUM |
合計 |
FIRST |
最初の値 |
LAST |
最後の値 |
MAX |
最大値 |
MIN |
最小値 |
MOVING_AVERAGE |
移動平均 |
ただし、MOVING_AVERAGEは<field名>の後に、第2引数として移動平均をとるデータ数が必須。
その他の関数は以下を参照。
InfluxQL functions InfluxDB OSS 1.8 Documentation
また、時間幅は5分ならば5m, 3日ならば3dのように指定する。時刻を表す記号は下表の通り。
| 記号 |
意味 |
u, µ |
microseconds |
ms |
milliseconds |
s |
seconds |
m |
minutes |
h |
hours |
d |
days |
w |
weeks |
例：field A, Bのデータを6時間ごとに平均をとる（データフレームのカラムがmean, mean_1になっていることに注意）。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
>>> q1 = "SELECT MEAN(A), MEAN(B) FROM meas1 GROUP BY time(6h)"
>>> res = client.query(q1, database="test")
>>> list(res.values())[0]
mean mean_1
2020-05-01 00:00:00+00:00 5 6
2020-05-01 06:00:00+00:00 17 18
2020-05-01 12:00:00+00:00 29 30
2020-05-01 18:00:00+00:00 41 42
2020-05-02 00:00:00+00:00 53 54
2020-05-02 06:00:00+00:00 65 66
2020-05-02 12:00:00+00:00 77 78
2020-05-02 18:00:00+00:00 89 90
2020-05-03 00:00:00+00:00 101 102
2020-05-03 06:00:00+00:00 110 111
|
時間範囲を指定する
データの時間範囲を指定するには、FROM節の後に条件を指定するWHERE節を追加する。
1
|
SELECT <field名> FROM <measurement名> WHERE <条件>
|
例えば、2020年5月1日以降のデータを指定する場合は、以下のようにtimeと等号・不等号を用いる。
1
|
WHERE time >= '2020-05-01T00:00:00Z'
|
InfluxDBが対応する時刻の形式は、RFC3339またはUNIX時刻である。
RFC3339は、次のような時刻の表示形式である。
'YYYY-MM-DDTHH:MM:SS.nnnnnnnnnZ'
ただし、秒未満の部分 (.nnnnnnnnn) は任意。
また、UNIX時刻とは1970年1月1日午前0時0分0秒からの経過時刻である。InfluxDBのデフォルトの設定では、UNIX時刻をナノ秒単位で指定する。
例：UTC時刻で2020年5月1日0時0分0秒000000000をナノ秒単位のUNIX時刻で表すと、1588291200000000000である。
また、>=の他に下表の演算子が使える。
| 演算子 |
意味 |
= |
等しい |
<>, != |
等しくない |
> |
より大きい（後の） |
>= |
以上の（以降の） |
< |
より小さい（前の） |
<= |
以下の（以前の） |
さらに、複数の条件を指定する場合はAND, ORを用いる。
ただし、現在のバージョン(v1.8)では絶対時刻のORはサポートされていない（相対時刻については後述）。
InfluxDB frequently asked questions InfluxDB OSS 1.8 Documentation
Support disparate time intervals and more advanced time in WHERE clauses · Issue #7530 · influxdata_influxdb
例：field Aの2020年5月2日のデータだけを取得する場合
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
|
>>> query = "SELECT A, B FROM meas1"
>>> query += " WHERE time >= '2020-05-02T00:00:00Z'"
>>> query += " AND time < '2020-05-03T00:00:00Z'"
>>> res = client.query(query, database="test")
>>> list(res.values())[0]
A B
2020-05-02 00:00:00+00:00 48 49
2020-05-02 01:00:00+00:00 50 51
2020-05-02 02:00:00+00:00 52 53
2020-05-02 03:00:00+00:00 54 55
2020-05-02 04:00:00+00:00 56 57
2020-05-02 05:00:00+00:00 58 59
2020-05-02 06:00:00+00:00 60 61
2020-05-02 07:00:00+00:00 62 63
2020-05-02 08:00:00+00:00 64 65
2020-05-02 09:00:00+00:00 66 67
2020-05-02 10:00:00+00:00 68 69
2020-05-02 11:00:00+00:00 70 71
2020-05-02 12:00:00+00:00 72 73
2020-05-02 13:00:00+00:00 74 75
2020-05-02 14:00:00+00:00 76 77
2020-05-02 15:00:00+00:00 78 79
2020-05-02 16:00:00+00:00 80 81
2020-05-02 17:00:00+00:00 82 83
2020-05-02 18:00:00+00:00 84 85
2020-05-02 19:00:00+00:00 86 87
2020-05-02 20:00:00+00:00 88 89
2020-05-02 21:00:00+00:00 90 91
2020-05-02 22:00:00+00:00 92 93
2020-05-02 23:00:00+00:00 94 95
|
なお、現在時刻（クエリ文を処理した時刻）を指定するには、now()とする。これを相対時刻という。
例：2020年4月1日から現在までのデータを取得する場合
1
|
WHERE time >= '2020-04-01T00:00:00Z' AND time <= now()
|
また、以下のようにクエリ文中で時刻の計算もできる。
例：10分前から現在までのデータを取得する場合
1
|
WHERE time > now() - 10m
|
タイムゾーンを指定する
取得データのタイムスタンプにタイムゾーンを設定するためには、tz節を追加する。
tz節では、タイムゾーンをシングルクォーテーション'で囲んで指定する。得られるデータの時刻は、WHERE節の時刻 (UTC) と同じであり、あくまでもタイムスタンプのタイムゾーンが変換されているだけである。
例：UTC時刻で5月2日のデータを取得し、日本標準時のタイムスタンプに変換する。
タイムスタンプが+09:00となっている。
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
|
>>> query = "SELECT A, B FROM meas1 "
>>> query += " WHERE time >= '2020-05-02T00:00:00Z'"
>>> query += " AND time < '2020-05-03T00:00:00Z'"
>>> query += " tz('Asia/Tokyo')"
>>> res = client.query(query, database="test")
>>> list(res.values())[0]
A B
2020-05-02 09:00:00+09:00 48 49
2020-05-02 10:00:00+09:00 50 51
2020-05-02 11:00:00+09:00 52 53
2020-05-02 12:00:00+09:00 54 55
2020-05-02 13:00:00+09:00 56 57
2020-05-02 14:00:00+09:00 58 59
2020-05-02 15:00:00+09:00 60 61
2020-05-02 16:00:00+09:00 62 63
2020-05-02 17:00:00+09:00 64 65
2020-05-02 18:00:00+09:00 66 67
2020-05-02 19:00:00+09:00 68 69
2020-05-02 20:00:00+09:00 70 71
2020-05-02 21:00:00+09:00 72 73
2020-05-02 22:00:00+09:00 74 75
2020-05-02 23:00:00+09:00 76 77
2020-05-03 00:00:00+09:00 78 79
2020-05-03 01:00:00+09:00 80 81
2020-05-03 02:00:00+09:00 82 83
2020-05-03 03:00:00+09:00 84 85
2020-05-03 04:00:00+09:00 86 87
2020-05-03 05:00:00+09:00 88 89
2020-05-03 06:00:00+09:00 90 91
2020-05-03 07:00:00+09:00 92 93
2020-05-03 08:00:00+09:00 94 95
|
複数のクエリ文を同時に与える
複数のクエリ文を同時に処理するには、次のようにセミコロン;でつなぐ。
1
|
SELECT A FROM meas1; SELECT B FROM meas1
|
参考
Explore data using InfluxQL | InfluxData Documentation
InfluxDB インストール - 簡単な使い方 - Qiita
時系列データベースInfluxDB入門
PythonとPandasでInfluxDBを操作する
