IoTデータベースの設計を考えてみた

はじめに

以前の記事でIoT用のテーブル作成を行いました。
少し発展させ、IoT用データベースを設計してみようと思います。
今回は、シグナルタワーを用いた稼働状態測定後の稼働状態履歴の保存について考えてみましょう。
シグナルタワーは製造設備に付いている緑,黄,赤の信号灯です。灯っている信号灯の色で設備の稼働状態を確認することができるというものです。

言葉では伝わらない部分があると思うので、モノタロウさんのシグナルタワー検索結果を貼っておきます。

Agenda

1. やりたいこと
2. IoTデータベースの課題について
3. 私が考えた理想の構成

やりたいこと

IoT用データベースの設計。
設備稼働履歴を適切に保管する仕組みを構築したい。

前提

下記の前提で考えてみます。

• 制御器はM5 Stick C+
• 照度センサーを使用する
• シグナルタワーの各色に一つの照度センサーを設置
• 稼働状態は4種類 (緑：正常、黄：停止、赤：異常、無：電源OFF)
  (状態は設備によって細かな違いがあるようですので今回は上記のものと仮定します)

なお、M5には専用のハブが用意されており、それを使用することで一つのポートから複数のセンサーを接続することができます。
(AタイプとBタイプが存在するので間違って購入すると悲しいことが起きます)

必要なテーブル

この条件で必要なテーブルを作成してみましょう。
下記の2つを作成して、BIツール等をうまく使用すれば測定と可視化が始められます。

設備テーブル

カラム名	データ型	オプション	備考
id	INTEGER	unique, null false	-
machine_name	VARCHAR(100)	-	設備名
purchase_date	DATE	-	購入日
create_timestamp	TIMESTAMP	-	レコード作成日時

シグナルライト保管テーブル

カラム名	データ型	オプション	備考
id	INT	unique, null false	-
machine_id	INTEGER	-	設備ID
green_raw	INTEGER	-	緑信号アナログ値
yellow_raw	INTEGER	-	黄信号アナログ値
red_raw	INTEGER	-	赤信号アナログ値
create_timestamp	TIMESTAMP	-	レコード作成日時

IoTデータベースの課題について

何を考慮しなければいけないか

初期の取り組みでは上記のもので問題ないと思われます。
しかしながら、ここで一歩立ち止まって考えてみましょう。
IoTで送付するデータは非常に小さいものです。しかしながら、送付する量が多いという特徴があります。

例えば、1つのデータが70byteだったとします。さらに1分定期の測定を行い、年間稼働日が250日、早番遅番で16h稼働している工場で、20設備にセンサーを設置している場合を考えてみます。
このケースでは、一年間に貯まるデータ量は下記になります。

320 \verb|(MB/year)| \fallingdotseq
 70 \verb|(byte/min/machine)|
 \times 16 \verb|(h/day)|
 \times 60 \verb|(min/h)|
 \times 250  \verb|(days)|
 \times 20 \verb|(machines)|

今回は比較的取り込み時間が1分と長いので比較的少量に思えますが、これが1秒毎やもっと短い間隔の場合、ぞっとしますね。

また、下記の内容も考えられます。

• データ数(レコード数)が多いため、ラベル付等を行いたい場合取り扱い性が悪い。
• 細かい粒度のデータを持っておく必要がない。
• データ数が多いため、検索等のクエリで余計な時間が発生する可能性がある。

整理すると下記の問題があります。

• 小さなデータが大量に存在してDBを圧迫する。
• 細かい粒度のデータが存在して取り扱い性が悪い。
• 細かい粒度で持っておく必要がない。
• DB側のアクセスに時間を取られる可能性がある。

理想な状態とは何か

このケースで私が考える理想の状態は何か、という点について記載してみます。

筆者が考える理想の状態

1. センサーデータテーブルをまとめた集約テーブルが存在する
2. 一定期間経過後のセンサーデータテーブルのレコードは削除される

私が考えた理想の構成

概要

先程のテーブルに加え2つのテーブルを追加で作成すると良いと考えます。
一つはステータスを登録するマスタのテーブル。もう一つは、センサーデータを集約したテーブルです。

ステータスマスタテーブル

このケースですと、下記の4つの状態が登録されているマスタを準備しておきたいです。

• 正常 (緑)
• 停止 (黄)
• 異常 (赤)
• 電源OFF

集約テーブル

集約を行うといっても、いくつか考慮すべきことがあると思います。
今回の条件で考えられる内容を下記に記載してみようと思います。

集約する上で考慮すべきこと

• 集約する際に時系列にデータが並んでいることが重要
• ステータスをアナログ値から閾値を使用して取得しておく
• ステータスが同じ連続したデータはグルーピングできる
• ステータスの変化した時間のみが必要

集約の効果を数で確認してみましょう。
例えば一日のステータス変化が平均10回で、先程と同じ頻度でデータ取得を行っている場合のデータ数を比較してみます。

＜集約しない場合のデータ数＞
$4,800,000 \verb|(records/year)| = 16 \verb|(h/day)|
\times 60 \verb|(min/h)|
\times 250 \verb|(days)|
\times 20 \verb|(machines)|$

＜集約後のデータ数＞
$50,000 \verb|(records/year)| = 10 \verb|(statuses/day)| \times 250 \verb|(days)|
\times 20 \verb|(machines)|$

いかがでしょうか。大幅にデータを削減することができています。(自分でも驚きです)

また、正常以外のステータスの場合に、stop_reasonというカラムを設けて停止理由を入れておくと、改善までセットで考えられそうなのでgoodです。(アプリ側でそういう機能を設ける必要はありますが)
これが集約していない場合、一つ一つのレコード全てに停止理由をつけていく必要がありますので、この意味でも効率的なテーブルとなっています。

テーブル定義

考えてみたテーブル定義を書いてみます。

ステータステーブル

カラム名	データ型	オプション	備考
id	INT	unique, null false	-
name	VARCHAR(10)	unique, null false	ステータス名
last_edit_account	TIMESTAMP	-	最終更新者
last_edit_timestamp	TIMESTAMP	-	最終更新日時
create_timestamp	TIMESTAMP	-	レコード作成日時

センサーデータ集約テーブル

カラム名	データ型	オプション	備考
id	INT	unique, null false	-
mesured_date	DATE	-	測定日
machine_id	INTEGER	foreign_key	設備ID
start_timestamp	TIMESTAMP	-	状態の始まり
end_timestamp	TIMESTAMP	-	状態の終わり
status_id	INT	foreign_key	ステータスID
stop_reason	VARCHAR(100)	-	停止理由
last_edit_account	TIMESTAMP	-	最終更新者
last_edit_timestamp	TIMESTAMP	-	最終更新日時
create_timestamp	TIMESTAMP	-	レコード作成日時

・・・本当ならstop_reasonはマスタ持ってforegin_keyにしておくのがベストだが、本題と外れるので割愛。

終わりに

思考実験的な部分があり、最適なものであるとは言い切れませんがIoTのデータベース設計を考えてみました。
いかがでしたでしょうか？
この例が適用できるケースがあるかどうかはわかりかねますが、参考になれば幸いです。
知見がある方からコメント等でご意見いただけますとありがたいです。よろしくお願いいたします。

面白そうな資料のリンク

• https://www.postgresql.jp/sites/default/files/2019-12/PGconf2019_SMMr1.pdf
  (本記事はこのサイトのP19の一部を簡単なケースで思考実験したものです)