長期間の統計計算と可視化にロールアップ（Rollup）を活用する
はじめに
時系列データベースに期待される大きな役割の一つが、長期間にわたって収集した大量のデータに対する、さまざまな統計計算、データ抽出、可視化です。他の種類のデータベースで利用できる処理技術だけでは、求める性能に到達しにくいためです。そうでなければ、時系列データベースを使う必要はないでしょう。ただし、時系列として蓄積された大量のデータも、無制限のリソースで並列処理しない限り、データ量に応じた処理性能の限界があります。
例を考えてみましょう。
ある目的でセンサーデータを12か月間収集し、総件数が1,000億件、圧縮後の保存容量が約700GBになったとします。センサー（タグ）が50個なら、センサーごとに少なくとも20億件のデータが時系列に並んでいることになります。
任意のタグについて、6か月間の平均値などの統計計算を求められたとします。この種の処理では、対象期間に含まれるデータをすべて読み、合計を件数で割る必要があります。最大値ならどうでしょうか。最小値はどうでしょう。特定の値を検索するのとは異なり、全体や広い範囲の統計計算は、非常にコストの高い処理です。
実際の現場では、次のような要求に対応しなければなりません。
この設備の過去3か月間の平均温度は、どのように推移したか。
この設備の過去1年間の最高圧力はいくつだったか。
この設備の過去6か月間の最小電流はいくつで、いつ記録されたか。
過去12時間のデータから、1秒ごとの平均温度グラフを作って報告してほしい。明日からは1秒単位ではなく、15秒単位で描き直してほしい。
過去1時間の平均圧力を1秒単位でグラフにし、リアルタイムに更新してほしい。
過去6時間のうち、15分間の平均温度が33度以下になった場合は、アラートを通知してほしい。
過去1年のうち、1時間の平均温度が98度を超えた時刻を特定し、その前後15分、計30分間のRAWデータをCSVにしてメールで送ってほしい。
すべてのデータがデータベースに入っていても、こうした要求に素早く応えることは簡単ではありません。
従来の方法
そこで業界では、データベース全体を読み込まなくても統計値を得られるように、あらかじめ定めた時間単位の「統計テーブル」を作成します。要求が来たら、この事前作成済みのテーブルからデータを素早く取り出し、目的を達成する方法です。
ただし、この方式には次のような制約があります。
集計の開始時刻が変わると、統計テーブルを利用できない。
毎時0分0秒を基準に作った統計テーブルを、2分30秒開始に変更することはできません。
集計する時間単位が変わっても、統計テーブルを利用できない。
例えば、1分単位の統計データから30秒単位のデータを得ることはできません。
統計テーブルの管理が難しい。
要求に応じて、タグ、対応する統計関数、時間単位などの組み合わせごとに多くのテーブルを作り、個別にファイルを管理する必要があります。
統計テーブルの削除や、継続的なデータ追加も難しい。
つまり、統計テーブル方式は、顧客の要求に対して限られた範囲でしか活用できない技術です。
ロールアップ（Rollup）とは
本記事でいうロールアップとは、Machbaseのタグテーブルを元データとして、上記のような統計テーブルを自動生成する処理です。その処理をロールアップと呼び、結果を保存するテーブルをロールアップテーブルと呼びます。では、従来の統計テーブルとは何が違うのでしょうか。
ロールアップテーブルは、従来の統計テーブルの弱点を補い、より簡単に利用できるようにしたものです。具体的な違いを下表に示します。
比較項目 | ロールアップテーブル | 統計テーブル |
集計開始時刻の変更 | 開始時刻を自由に変更可能 | 変更不可 |
ロールアップの種類 | 秒・分・時を指定可能 | 固定 |
集計時間単位の変更 | 自由に変更可能 | 変更不可 |
作成・管理方法 | SQLで作成・追加・削除 | 手動で作成 |
集計データの生成方法 | 内部で自動生成 | 外部アプリケーションの実行・管理が必要 |
対応する統計値 | 最小値、最大値、件数、合計、平均、二乗和。拡張オプション：最初の値・時刻、最後の値・時刻 | 要件によって異なる |
例えば、数千のセンサーから1年分、数千億件のデータをタグテーブルに収集し、ロールアップを作成しておけば、任意のセンサー、任意の時間範囲（数年にわたる範囲も含む）、任意の集計単位（15秒、3秒、100分、12時間、3週間など）について、最小値、最大値、件数、合計、平均、二乗和をすぐに取得できます。
ロールアップの限界
あらゆる面で優れたものはありません。ロールアップも同様です。利用前に、次の基本的な制約を理解しておくと役立ちます。
新しい統計関数が必要な場合
前述の10種類以外の統計値が必要な場合、ロールアップは利用できません。多くの産業で必要な統計値を備えてはいますが、要求は多岐にわたります。
元データに誤りがある場合
時系列データベースに誤ったデータを取り込むと、その値は当然ロールアップテーブルにも反映されます。
不要なノイズを入力しないようにしましょう。
大量の元データの取り込みによってリソースが不足する場合
ロールアップは、タグテーブルからデータを読み、計算し、その結果をロールアップテーブルに書き込む処理を繰り返します。
このため、ロールアップテーブルへの反映には、数ミリ秒から数十秒の遅延が発生し得ます。
特に、取り込み量が非常に多く、ロールアップに十分なシステムリソースを割り当てられない場合、元データの最新時刻とロールアップテーブルの最新時刻の差が広がることがあります。
この差は、Machbase NeoのSHELLで「show rollupgap」を実行すると確認できます。「GAP」列の値が0に近いほど、リアルタイムに近い状態で集計データが生成されていると考えられます。
GAPが継続的に増える場合は、CPU、メモリ、ディスク性能を見直し、ロールアップに使えるリソースを確保しましょう。
ロールアップで長期間の統計データを活用する
1. Neoのインストールとneo-appsの入手
インストール手順は以前の記事と同じです。リンク先の手順1（Neoのインストール）と手順2（neo-appsのダウンロード）だけを参照してから、本記事に戻ってください。以降は、下記の手順2から進めます。
Machbase Neoは、可能であればバージョン8.0.19以降を使用してください。week、monthの対応と構文の改善が含まれています。
2. fast-rollupディレクトリの確認
neo-appsを入手すると、fast-rollupディレクトリが見つかります。番号の順番にテストを進めます。
3. スキーマの作成とデータの取り込み
fast-rollupデモの「0-Schema and Data Population.wrk」を開き、順番にスキーマを作成してデータを取り込みます。あらかじめTQLでデータを投入できるように準備してあります。
ロールアップの作成方法は、大きく分けて次の2つです。
以下の例では、WITH ROLLUPを指定してデフォルトのロールアップを作成します。
図の1番のボタンを押して、スキーマを作成します。
2番は、1つのタグに対して1秒に1件、1年分のデータをまとめて取り込む処理です。約2〜3分かかるため、完了するまでお待ちください。ロールアップの特徴を長期間のデータで確認するため、1年間という期間を選んでいます。
4. Rollup Gapの確認と解消
取り込みが完了したら、SHELLで「show rollupgap」を実行して進捗を確認します。図では、大量のデータを一度に投入した直後のためGAPが発生しています。約2〜3秒間隔で確認すると、約10秒後にはHOURのロールアップだけにGAPが残ります。HOURの実行周期は1時間で、該当スレッドがスリープしているためです。
このようなGAPは通常の運用では発生せず、このデモのように全リソースを使って大量のデータを投入した場合に見られる例外的な状況です。一般に、リソースが十分であればGAPは1秒以内にすべて0に収束し、リアルタイム性を保てます。
そこで、図のコマンドでHOURを担当するスレッドを強制的に起こしてロールアップを実行すると、すべてのGAPが0になります。
これでロールアップのテストに必要な準備が整いました。
図の左側では、1つのセンサーについて1Hzで1年分、合計31,536,000件のデータが入っていることを確認できます。
5. ロールアップデータを取得するクエリ
以下は、8.0.19以降で利用できる代表的なROLLUPクエリです。
select ROLLUP('sec', 15, time) as time, AVG(value)
FROM mybigdata
WHERE name = 'pressure' and
time BETWEEN TO_DATE('2023-05-15') AND TO_DATE('2023-05-16')
GROUP BY time
ORDER BY time;このクエリは、指定した期間のpressureセンサーについて、15秒ごとの平均値を取得します。
第1引数にはsec、min、hour、day、week、month、yearを指定できます。第2引数には、その単位で使用できる範囲の数値を指定します。第3引数は、basetimeとして指定したtime列の名前です。省略可能な第4引数はロールアップの開始時刻で、後の例で説明します。
6. 秒・分・時・日単位のROLLUPを試す
「1-Rollup-min-day.wrk」を開きます。to_char()はTQL内で文字列に変換するための関数で、クエリの実行自体には必須ではありません。
1時間のデータから15秒ごとの平均値を取得する
select to_char(rollup('sec', 15, time, '2023-01-01')) as time, avg(value), max(value) from mybigdata where name = 'pressure' and time between TO_DATE('2023-05-15 0:0:0') AND TO_DATE('2023-05-15 23:59:59') group by time order by time;1日のデータから30分ごとの最小値を取得する
select to_char(rollup('min', 30, time, '2023-01-01')) as time, min(value), sumsq(value) from mybigdata where name = 'pressure' and time between TO_DATE('2023-09-11') AND TO_DATE('2023-09-11 23:59:59') group by time order by time;半年間のデータから1日ごとの平均値を取得する
select to_char(rollup('day', 1, time, '2023-01-01')) as time, avg(value) from mybigdata where name = 'pressure' and time between TO_DATE('2023-01-1') AND TO_DATE('2023-12-31 23:59:59') group by time order by time;結果を可視化するダッシュボード「2-DASHBOARD-MIN-HOUR.dsh」は次のとおりです。
3番目のグラフは、6か月間のデータから12時間ごとの平均値を算出したものです。このような長期間の統計でも、データの抽出と可視化を非常に高速に実行できます。
7. 週・月単位のROLLUPを試す
「3-Rollup-week-month.wrk」を開きます。
週単位に加えて、月単位の集計にも難しさがあります。月によって日数が異なり、うるう年の影響もあるためです。Machbase Neoはこうした複雑さを内部で処理するので、利用者はweekまたはmonthを指定するだけで結果を得られます。ただし、週単位では開始する曜日を指定する必要があるため、ROLLUPの第4引数である開始時刻を使います。
6か月間のデータから毎週の平均値を取得する
2023年1月2日は月曜日です。月曜日から日曜日までの統計を得るため、開始日を2日に指定しています。
select to_char(rollup('week', 1, time, '2023-01-02'), 'YYYY-MM-DD') as time, avg(value) from mybigdata where name = 'pressure' and time between TO_DATE('2023-01-2 0:0:0') AND TO_DATE('2023-06-30 23:59:59') group by time order by time;6か月間のデータから2週間ごとの平均値を取得する
次の例の7月3日も月曜日です。
select to_char(rollup('week', 2, time, '2023-07-03'), 'YYYY-MM-DD') as time, avg(value) from mybigdata where name = 'pressure' and time between TO_DATE('2023-07-03 0:0:0') AND TO_DATE('2023-12-31 23:59:59') group by time order by time;1年間のデータから月ごとの平均値を取得する
select to_char(rollup('month', 1, time, '2023-01-01'), 'YYYY-MM') as time, avg(value) from mybigdata where name = 'pressure' and time between TO_DATE('2023-01-01') AND TO_DATE('2023-12-31 23:59:59') group by time order by time;結果を可視化するダッシュボード「4-DASHBOARD-WEEK-MONTH.dsh」は次のとおりです。
