Helve’s Python memo

Pythonを使った機械学習や最適化の備忘録

時系列データベースInfluxDBのクエリ文

時系列データベースInfluxDBのクエリ文について、 データの期間の指定や集約処理など、基本的な構文をまとめた。

目次

はじめに

InfluxDBは時系列データの扱いに特化したデータベースである。 概要は以下の記事を参照。

時系列データベースInfluxDB入門 - Helve’s Python memo

InfluxDBではデータを取得するために、SQLに似た独自のクエリ文を採用している。 クエリ文では、取得するデータの期間を指定したり、 移動平均や最大値を計算する処理などの処理を行うことができる。 本記事では、クエリ文の基本的な構文をまとめた。

また、本記事ではPythonAPIを使っているが、 他の言語やCLIで扱う場合もクエリの構文自体は変わらない。

本記事の環境は以下の通り。

また、Pythonのバージョンは以下の通り。

バージョン
Python 3.7.6
NumPy 1.18.1
Pandas 1.0.1
Influxdb-python 5.2.2

以降では、ライブラリを以下のようにインポートすることを前提とする。

import numpy as np
import pandas as pd
import influxdb

使用するデータ

まず、以下のPythonコードを実行し、テスト用のデータをInfluxDBに書き込む。

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"である。

>>> 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つからなる。

SELECT <field名> FROM <measurement名>

公式リファレンスでは、それぞれSELECT節 (clause), FROM節と呼んでいる。 上記のクエリによって、指定したmeasurementのfieldのデータを全て取得する。

例:meas1A のデータを全て取得する。

>>> 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]としたのは、 PythonAPIの仕様(queryメソッドの戻り値が辞書)のためである。

また、複数のfield名を得るには、カンマ,で区切って指定する。

SELECT <field名>, <field名> FROM <measurement名>

全てのfield名を得るには、アスタリスク*を用いる。

SELECT * FROM <measurement名>

集約処理をする

SELECT節のfield名に関数を追加することによって、 移動平均や最大値をとるなどの集約処理を行える。 集約処理を行う時間幅は、GROUP BY節で指定する。

SELECT <関数名>(<field名>) FROM <measurement名> GROUP BY time(<時間幅>)

主な関数を下表に示す。

関数名 意味
MEAN 平均
MEDIAN 中央値
SUM 合計
FIRST 最初の値
LAST 最後の値
MAX 最大値
MIN 最小値
MOVING_AVERAGE 移動平均

ただし、MOVING_AVERAGEは<field名>の後に、第2引数として移動平均をとるデータ数が必須。

その他の関数は以下を参照。

InfluxQL functions | InfluxData 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になっていることに注意)。

>>> 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節を追加する。

SELECT <field名> FROM <measurement名> WHERE <条件>

例えば、2020年5月1日以降のデータを指定する場合は、 以下のようにtimeと等号・不等号を用いる。

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 | InfluxData Documentation

Support disparate time intervals and more advanced time in WHERE clauses · Issue #7530 · influxdata/influxdb · GitHub

例:field Aの2020年5月2日のデータだけを取得する場合

>>> 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日から現在までのデータを取得する場合

WHERE time >= '2020-04-01T00:00:00Z' AND time <= now()

また、以下のようにクエリ文中で時刻の計算もできる。

例:10分前から現在までのデータを取得する場合

WHERE time > now() - 10m

タイムゾーンを指定する

取得データのタイムスタンプにタイムゾーンを設定するためには、 tz節を追加する。

tz節では、タイムゾーンをシングルクォーテーション'で囲んで指定する。 得られるデータの時刻は、WHERE節の時刻 (UTC) と同じであり、 あくまでもタイムスタンプのタイムゾーンが変換されているだけである。

例:UTC時刻で5月2日のデータを取得し、日本標準時のタイムスタンプに変換する。 タイムスタンプが+09:00となっている。

>>> 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

複数のクエリ文を同時に与える

複数のクエリ文を同時に処理するには、次のようにセミコロン;でつなぐ。

SELECT A FROM meas1; SELECT B FROM meas1

参考

Explore data using InfluxQL | InfluxData Documentation

InfluxDB インストール - 簡単な使い方 - Qiita

時系列データベースInfluxDB入門 - Helve’s Python memo

PythonとPandasでInfluxDBを操作する - Helve’s Python memo