仮定の課題と解決策を考えてみます。
課題
PLCあるいはセンサーから取得されるデータの変化点（変化が発生した時刻）と、変化した際のデータの差（前のデータとの差）を取得する
取得するデータ
- データに変化が生じた時刻
- 前のデータとの差（対象のデータは数値で数は1つ）
条件
- データは1秒間隔でソースから取得され、テーブルAに記録される
- 対象のデータは数値、1回に取得される数は1つ
- 1回に取得されるデータは、テーブルAにレコードとして追加される
解説
さて、データ・ソースから取得されたデータが無秩序にデータベース中のテーブルに書き込まれます。これはありがちなケースです。PLCやセンサーからデータを取得するゲートウェイの設計担当者が、出来る努力をしなかったケースですね。（もちろん、全てのデータがほしい、というオーダーがあった可能性は否定できませんが）
このようなお粗末を許すプロジェクトですから、他の失敗も考えられます。
簡単に想像できる失敗は、目的とするデータを計算するバッチ・プログラムを作り出すことです。このプログラムはスケジューラで定期的に実行されます。良さそうな設計ですが、はたしてこのプログラムの処理はデータの発生に追いつくのでしょうか。もし追いつかない場合には、対症療法的な対策が取られるでしょう。それは、チューニングと称して行われる、データベースに対する様々な工夫です。それでも解決できない場合には、ハードウェアの設計担当者のミスが指摘されるかもしれません。こうしてプロジェクトは「計画になかった役に立たない不要な作業」のために大きく複雑になっていきます。
想像したくもない状況ですが、情報システム開発のプロジェクトでは、今でもこれに類する失敗が繰り返されています。
経験からではなく歴史（学問）から学ぶ賢者はこう考えます「データへの処理は、そのデータが発生したときにしろ」。
データ・ドリブン型システムとは、本来はこのようなことを指し、大昔からソフトウェアの工学分野で有効性が唱えられていました。ただし、今回のケースでは、データが発生した時には何も手出しができません。条件から読み取ると、発生したデータはもれなくテーブルAに書き込まれ、テーブルAに書き込まれる前になにかの工夫はできなさそうです。
となると、可能で有効な細工は、データがテーブルAに追加される部分にすべきのようです。そして、もう一歩考えを進めると、データベースの基礎的な知識さえあれば、使うべきはトリガーだということに簡単に気づくことができます。
後述の解決策の一例でもトリガーを活用しています。
解決策の一例
テーブルA
解決策を具体的に説明するために、テーブルAを以下のように仮定します。これは、かつての僕の記事で何度か登場した"from_plc"ですね。
mysql> desc from_plc;
+-------------+-----------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------+------+-----+-------------------+-------------------+
| body | json | YES | | NULL | |
| time_insert | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-------------+-----------+------+-----+-------------------+-------------------+
2 rows in set (0.02 sec)
mysql>
見てのとおり、モデルはMySQLで実装されています。
トリガー
計算と目的のデータをピックアップするために、トリガーを使います。トリガーは以下のとおりです。
DROP TRIGGER IF EXISTS from_plc_proc;
DELIMITER //
CREATE TRIGGER from_plc_proc AFTER INSERT ON from_plc FOR EACH ROW
BEGIN
DECLARE count_new INTEGER;
DECLARE count_old INTEGER;
DECLARE count_rec INTEGER;
-- from_plc_last の参照
SELECT COUNT(*) INTO count_rec FROM from_plc_last;
IF count_rec > 0 THEN
SELECT JSON_EXTRACT(body, '$.value') INTO count_old FROM from_plc_last;
ELSE
INSERT INTO from_plc_last (time_insert) VALUES (NOW());
SET count_old = 0;
END IF;
-- from_plc_last の更新
UPDATE from_plc_last SET body = NEW.body, time_insert = NEW.time_insert;
SET count_new = JSON_EXTRACT(NEW.body, '$.value');
-- count_status の更新
IF count_new <> count_old THEN
INSERT INTO count_status (count_old, count_new, time_change) VALUES (count_old, count_new, NEW.time_insert);
END IF;
END;
//
DELIMITER ;
3つのテーブルが登場します。
- from_plc : 前述のテーブルAの実態
- from_plc_last : from_plc中の最新のレコードのコピーが置かれる
- count_status : 目的のデータの導出が可能なデータが記録される
各テーブルの属性は以下のとおりです。from_plcの属性は既出なので、あらためて示すことはしません。
mysql> desc from_plc_last;
+-------------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------+------+-----+---------+-------+
| body | json | YES | | NULL | |
| time_insert | timestamp | YES | | NULL | |
+-------------+-----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> desc count_status;
+-------------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------+------+-----+---------+-------+
| count_old | int | YES | | NULL | |
| count_new | int | YES | | NULL | |
| time_change | timestamp | YES | | NULL | |
+-------------+-----------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql>
このシステムでは、from_plc_last はヒット率100%のキャッシュとして機能し、前後のデータの差を計算する処理の効率化に寄与します。
テスト
上記の解決策をテストしてみました。
データ
以前の僕の記事「サーバやPC上のプログラムでModbus機器からデータを取得 (2)」で登場したPerlスクリプトの劣化版とModbusシミュレーターでデータを発生させました。
サーバやPC上のプログラムでModbus機器からデータを取得 (2)
Perlスクリプトの46, 68行目をコメント・アウトしただけです。
結果
from_plc
mysql> select * from from_plc;
+----------------------------------------------------+---------------------+
| body | time_insert |
+----------------------------------------------------+---------------------+
| {"ts": "2022-08-07T12:31:52.445466", "value": 0} | 2022-08-07 12:31:52 |
| {"ts": "2022-08-07T12:31:53.492522", "value": 0} | 2022-08-07 12:31:53 |
| {"ts": "2022-08-07T12:31:54.547653", "value": 0} | 2022-08-07 12:31:54 |
| {"ts": "2022-08-07T12:31:55.563391", "value": 0} | 2022-08-07 12:31:55 |
| {"ts": "2022-08-07T12:31:56.610040", "value": 0} | 2022-08-07 12:31:56 |
| {"ts": "2022-08-07T12:31:57.672496", "value": 1} | 2022-08-07 12:31:57 |
| {"ts": "2022-08-07T12:31:58.703830", "value": 1} | 2022-08-07 12:31:58 |
| {"ts": "2022-08-07T12:31:59.734885", "value": 1} | 2022-08-07 12:31:59 |
| {"ts": "2022-08-07T12:32:00.766081", "value": 1} | 2022-08-07 12:32:00 |
| {"ts": "2022-08-07T12:32:01.797569", "value": 1} | 2022-08-07 12:32:01 |
| {"ts": "2022-08-07T12:32:02.859724", "value": 2} | 2022-08-07 12:32:02 |
| {"ts": "2022-08-07T12:32:03.891062", "value": 2} | 2022-08-07 12:32:03 |
| {"ts": "2022-08-07T12:32:04.958819", "value": 2} | 2022-08-07 12:32:04 |
| {"ts": "2022-08-07T12:32:06.5521", "value": 2} | 2022-08-07 12:32:06 |
+----------------------------------------------------+---------------------+
14 rows in set (0.01 sec)
mysql>
from_plc_last
mysql> select * from from_plc_last;
+------------------------------------------------+---------------------+
| body | time_insert |
+------------------------------------------------+---------------------+
| {"ts": "2022-08-07T12:32:06.5521", "value": 2} | 2022-08-07 12:32:06 |
+------------------------------------------------+---------------------+
1 row in set (0.00 sec)
mysql>
count_status
mysql> select * from count_status;
+-----------+-----------+---------------------+
| count_old | count_new | time_change |
+-----------+-----------+---------------------+
| 0 | 1 | 2022-08-07 12:31:57 |
| 1 | 2 | 2022-08-07 12:32:02 |
+-----------+-----------+---------------------+
2 rows in set (0.00 sec)
mysql>
まとめ
一旦まとめを記述せずに投稿しました。意識的にです。
この記事の記載、多くの読者が「こんなの記事にするまでもなく、あたりまえのこと」ととらえるでしょう。さて本当にそうでしょうか。これが「まとめ」です。