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

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

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

https://www.monotaro.com/s/?c=&q=%E3%82%B7%E3%82%B0%E3%83%8A%E3%83%AB+%E3%82%BF%E3%83%AF%E3%83%BC


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

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

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

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

https://www.switch-science.com/products/8304


## 必要なテーブル
この条件で必要なテーブルを作成してみましょう。
下記の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設備にセンサーを設置している場合を考えてみます。
このケースでは、一年間に貯まるデータ量は下記になります。

```math
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秒毎やもっと短い間隔の場合、ぞっとしますね。

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


:::note alert
整理すると下記の問題があります。
- 小さなデータが大量に存在してDBを圧迫する。
- 細かい粒度のデータが存在して取り扱い性が悪い。
- 細かい粒度で持っておく必要がない。
- DB側のアクセスに時間を取られる可能性がある。
:::

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

:::note info
筆者が考える理想の状態
1. センサーデータテーブルをまとめた集約テーブルが存在する
1. 一定期間経過後のセンサーデータテーブルのレコードは削除される
:::



# 私が考えた理想の構成

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

### ステータスマスタテーブル
このケースですと、下記の4つの状態が登録されているマスタを準備しておきたいです。
- 正常 (緑)
- 停止 (黄)
- 異常 (赤)
- 電源OFF

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

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

集約の効果を数で確認してみましょう。
例えば一日のステータス変化が平均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の一部を簡単なケースで思考実験したものです)