2021 年度 OSS リテラシ 3 : 代表値・欠損値処理
はじめに
センサーから得られたデータの代表値 (平均値・最大・最小・標準偏差) を計算し, それを MariaDB のテーブルに登録し, grafana で可視化する.
代表値の計算を Ruby などにやらせても良いが, ここでは MySQL の関数を用いて計算することにする.
今回の実習では, ラズパイから送られてきた 1 分毎の瞬間値から 10 分平均値, 1 時間毎の値の抽出を作成し, それらのデータを grafana で表示できるようにする.
- 1 分間隔のデータ => 10 分平均値
- 10 分間の中にデータが一定数以上無い場合には欠損値に.
- 10 分間隔の平均値 => 1 時間間隔の平均値 (10 分間隔のデータから, 毎正時の値を抽出)
統計量の作り方は気象庁のやり方を真似ている (全く同じではないが). 詳細は気象観測統計の解説 を参照のこと.
以下の作業はサーバ (VM) で行うべきものである. ラズパイで行うことはない (きちんとデータが送られている限りは).
準備
データの確認
前回までの内容で, ラズパイからデータが送られてきたデータが grafana で可視化されているはずである. 各自の管理するサーバ上で動いている grafana において, 以下のような画面が表示されているか確認すること.
また, MySQL を直接確認すると, 1 分間隔でデータがテーブルに入力されていることがわかる. 以下ではユーザ名は hogehoge としているが，自分のユーザ名で適宜読み替えること．
$ mysql -u hogehoge -p
Enter password: (適宜入力)
...(略)...
MariaDB [(none)]> use iotex
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [iotex]> select hostname,time,temp,humi,lux from monitoring order by time;
...(略)...
| matsue28 | 2021-12-15 12:21:00 | 23.25 | 56.63 | NULL |
| kawase | 2021-12-15 12:21:00 | 23.01 | 54.86 | NULL |
| kawase | 2021-12-15 12:22:00 | 22.98 | 54.79 | NULL |
| kawase | 2021-12-15 12:23:00 | 22.92 | 54.81 | NULL |
なお, MySQL のデータベース iotex のテーブル monitoring のカラムの内, 2021 年度の演習で値が入っているものは以下の 5 つである. それ以外のカラムの値は NULL になっている.
- hostname (ホスト名)
- time (時刻)
- temp (温度)
- humi (湿度)
- lux (照度)
なお，以下で使うテーブルもここで作っておくとよい
MariaDB [iotex]> CREATE TABLE monitoring_10min LIKE monitoring; MariaDB [iotex]> CREATE TABLE monitoring_1hour LIKE monitoring; MariaDB [iotex]> CREATE TABLE monitoring_1day_avg LIKE monitoring; MariaDB [iotex]> CREATE TABLE monitoring_1day_min LIKE monitoring; MariaDB [iotex]> CREATE TABLE monitoring_1day_max LIKE monitoring; MariaDB [iotex]> CREATE TABLE monitoring_1day_stddev LIKE monitoring;
必要なパッケージのインストール
Ruby からデータベースを操作するために必要なパッケージをインストールする.
# apt-get update # apt-get install ruby-mysql2 ruby-activesupport
MySQL の接続情報の用意
PHP のセットアップ で PHP スクリプトから MySQL に接続するための情報を db_info.php というファイルにまとめたが， 今回も同様に Ruby からデータベースに接続するための情報を db_info.yml という ファイルにまとめることにする．
db_info.yml は YAML 形式で書かれたファイルである．YAML は設定などを外部ファイルに まとめるときによく用いるファイル形式である．YAML の詳細は Google 検索などしてみて欲しい．
PHP のセットアップ, 動作確認: PHP 経由でのデータベースの利用 で GitHub からリポジトリ iotex-server を git clone したが，そこに db_info.yml のサンプルが含まれている．今回はそれを利用すると良い．
なお，データベース接続情報 (db_info.yml) は絶対に ~/public_html 以下には置かないこと. また，YAML 形式のファイルでは, コロン (:) の後に空白を 1 つ入れる必要があることに注意せよ.
$ cd $ cd iotex-server/conf $ vi db_info.yml ADPT: "mysql2" SERV: "localhost" USER: "<username>" (適宜修正すること. "<" と ">" は削除すること) PASS: "<password>" (適宜修正すること. "<" と ">" は削除すること) DBNM: "iotex"
db_info.php と同様に，db_info.yml のグループも WWW サーバ (apache) の実行されるグループである www-data にしておくべきである． また，others (所有者でもグループでもない人) からは読めないようにパーミッションを 640 にしておくべきである．
$ chmod 640 db_info.yml $ sudo chgrp www-data db_info.yml $ ls -l -rw-r----- 1 hogehoge www-data 84 8月 28 12:24 db_info.yml
10 分平均値を作成する
はじめの一歩：MySQL のターミナル上での統計操作
SQL 文で代表値を計算するやり方を確認する．ここでは， テーブル monitoring に保存されている温度データを 10 分平均して別テーブルに入力する SQL 文を順を追って考えてみる.
SQL のターミナルを立ち上げる.
$ mysql -u hogehoge -p
Enter password: (適宜入力)
...(略)...
MariaDB [(none)]> use iotex
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
テストデータが入っていることを確認する. 簡単のために温度のみ表示する. まずは以下のように，自分のラズパイのホスト名を指定し，自分のデータが含まれる時刻 10 分間を表示してみよ (hogehoge や時刻を読み替えること)．
MariaDB [iotex]> SELECT hostname,time,temp FROM monitoring WHERE hostname = 'hogehoge' AND time > '2021-12-10 15:10:00' AND time <= '2021-12-10 15:20:00'; +----------+---------------------+-------+ | hostname | time | temp | +----------+---------------------+-------+ | hogehoge | 2021-12-10 15:11:00 | 22.48 | | hogehoge | 2021-12-10 15:12:00 | 22.47 | | hogehoge | 2021-12-10 15:13:00 | 22.46 | | hogehoge | 2021-12-10 15:14:00 | 22.46 | | hogehoge | 2021-12-10 15:15:00 | 22.43 | | hogehoge | 2021-12-10 15:16:00 | 22.41 | | hogehoge | 2021-12-10 15:17:00 | 22.39 | | hogehoge | 2021-12-10 15:18:00 | 22.37 | | hogehoge | 2021-12-10 15:19:00 | 22.36 | | hogehoge | 2021-12-10 15:20:00 | 22.36 | +----------+---------------------+-------+ 10 rows in set (0.033 sec)
上記時間帯について平均操作を行う. 平均を行うためには AVG() を使えば良い．
MariaDB [iotex]> SELECT hostname,time,AVG(temp) FROM monitoring WHERE hostname = 'hogehoge' AND time > '2021-12-10 15:10:00' AND time <= '2021-12-10 15:20:00'; +----------+---------------------+--------------------+ | hostname | time | AVG(temp) | +----------+---------------------+--------------------+ | hogehoge | 2021-12-10 15:11:00 | 22.419000000000004 | +----------+---------------------+--------------------+ 1 row in set (0.039 sec)
上記で得た時刻と平均値の組みはちょっと都合が悪い． 気象のデータは前 10 分で平均するのが普通であるためである (15:11 ~ 15:20 の平均値を 15:11 ではなく 15:20 の値として扱う)．
時刻の列は時系列データなので，時刻に対して MAX() を適用すれば，前 10 分平均の値として数値が求まる．
MariaDB [iotex]> SELECT hostname,MAX(time),AVG(temp) FROM monitoring WHERE hostname = 'hogehoge' AND time > '2021-12-10 15:10:00' AND time <= '2021-12-10 15:20:00'; +----------+---------------------+--------------------+ | hostname | MAX(time) | AVG(temp) | +----------+---------------------+--------------------+ | hogehoge | 2021-12-10 15:20:00 | 22.419000000000004 | +----------+---------------------+--------------------+
このようにして平均操作を行った結果を別のテーブル (monitoring_10min) にそのまま入力することができる (INSERT INTO ~ SELECT ~ を使う)．すなわち，上記の SQL 文の先頭に "INSERT INTO monitoring_10min (hostname,time,temp)" を追加すれば良い．なお，hostname, time は主キーであることに注意すること．insert 文を実行し直す場合にはデータを消す必要がある．(delete from monitoring_10min where hostname like 'hogehoge' and time ...." というように)
MariaDB [iotex]> INSERT INTO monitoring_10min (hostname,time,temp) SELECT hostname,MAX(time),AVG(temp) FROM monitoring WHERE hostname = 'hogehoge' AND time > '2021-12-10 15:10:00' AND time <= '2021-12-10 15:20:00'; Query OK, 1 row affected (0.001 sec) Records: 1 Duplicates: 0 Warnings: 0 MariaDB [iotex]> select hostname,time,temp from monitoring_10min; +----------+---------------------+--------------------+ | hostname | time | temp | +----------+---------------------+--------------------+ | hogehoge | 2021-12-10 15:20:00 | 22.419000000000004 | +----------+---------------------+--------------------+
次の操作のために，テーブル monitoring_10min のデータを消去しておく．
MariaDB [iotex]> DELETE FROM monitoring_10min; Query OK, 1 row affected (0.011 sec)
次の一歩：MySQL のターミナル上での統計操作 (欠損値処理あり)
平均を作成する際に, 指定された時間帯に一定以上のデータ数が含まるか調べることをよく行う. 例えば, 指定された時間帯に 5 点以上のデータがあれば平均し, そうでなければ null (欠損値) を入れる場合には 以下のような SQL 文を書けば良い．データ数が十分にある場合には以前と同じ結果が得られるはずである．
MariaDB [iotex]> INSERT INTO monitoring_10min (hostname,time,temp) SELECT hostname,MAX(time),(CASE WHEN COUNT(temp) >= 5 then AVG(temp) else NULL end) FROM monitoring WHERE hostname = 'hogehoge' AND time > '2021-12-10 15:10:00' AND time <= '2021-12-10 15:20:00'; Query OK, 1 row affected (0.002 sec) Records: 1 Duplicates: 0 Warnings: 0 MariaDB [iotex]> select hostname,time,temp from monitoring_10min; +----------+---------------------+--------------------+ | hostname | time | temp | +----------+---------------------+--------------------+ | hogehoge | 2021-12-10 15:20:00 | 22.419000000000004 | +----------+---------------------+--------------------+ 1 row in set (0.028 sec)
欠損値のようなデータの存在しない時刻でも主キーの hostname, time には何らかの値を入力しないといけない． このような時には，hostname と time に値を決め打ちする必要がある． 以下の例では，hostname として 'hogehoge' を，'time' として平均に使う最終時刻 (2020-12-10 15:20:00) を入力している．
MariaDB [iotex]> INSERT INTO monitoring_10min (hostname,time,temp) SELECT 'hogehoge','2020-12-10 15:20:00',(CASE WHEN COUNT(temp) >= 5 then AVG(temp) else NULL end) FROM monitoring WHERE hostname = 'hogehoge' AND time > '2020-12-10 15:10:00' AND time <= '2020-12-10 15:20:00'; Query OK, 1 row affected (0.002 sec) Records: 1 Duplicates: 0 Warnings: 0
確認してみる．2020/12/10 には欠損値 NULL が，2021/12/10 には平均値が入っていることがわかる．
MariaDB [iotex]> select hostname,time,temp from monitoring_10min; +----------+---------------------+--------------------+ | hostname | time | temp | +----------+---------------------+--------------------+ | hogehoge | 2020-12-10 15:20:00 | NULL | | hogehoge | 2021-12-10 15:20:00 | 22.419000000000004 | +----------+---------------------+--------------------+ 1 row in set (0.028 sec)
次の操作のために，テーブル monitoring_10min のデータを消去しておく．
MariaDB [iotex]> DELETE FROM monitoring_10min; Query OK, 1 row affected (0.011 sec)
最後の一歩：Ruby スクリプトからの実行
上記の SQL 文を踏まえて, Ruby スクリプトを作成する. 以下に例を示すので, プログラム中のコメントを参考にやっていることを理解して欲しい. サンプルファイルはmake-10min.rbからダウンロードできるので， 空欄を自分で埋めて実行してみて欲しい．
$ vi ~/make-10min.rb
#!/usr/bin/env ruby
# coding: utf-8
require 'yaml'
require 'mysql2'
require 'active_support/time'
###
### 変数宣言
###
# ホスト名
hosts = ["sugiyama", "hogehoge", ....] #自分のラズパイやチームメンバーのラズパイのホスト名を配列として列挙する．
# データベースへの接続情報の置き場.
# ~/public_html 以下には置かないこと.
conf = "/home/hogehoge/iotex-server/conf/db_info.yml" # <= 自分の環境に合わせて書き換えること!!
# 設定ファイルの読み込み
mydb = YAML.load_file( conf )
# データベースへの接続
client = Mysql2::Client.new(
:host => "#{mydb["SERV"]}",
:username => "#{mydb["USER"]}",
:password => "#{mydb["PASS"]}",
:database => "#{mydb["DBNM"]}"
)
###
### 時刻の設定
###
# 平均を開始する時間 (デフォルト値は 2021/12/21)
time_from = DateTime.new( 2021, 12, 21, 0, 0, 0, "JST")
# テーブル monitoring_10min に値が入っている場合は, 開始時刻 time_from の値を更新する.
sql = ".......................... " # データベースに入っている最後の時刻を取得. DESC か ASC を使い, "LIMIT 1" で取り出すレコードの数を 1 つに限定すると良い.
client.query(sql).each do |item|
if item["time"].present?
time_from = Time.parse( item["time"].to_s ) # 時刻のフォーマットに変換するために Time.parse を用いる.
end
end
# 平均操作を終了する時間. 現在時刻にセット.
time_end = DateTime.now
p "+++++ START : #{time_from}, END : #{time_end} +++++"
###
### 前 10 分平均値の計算とテーブルへの代入
###
# 変数の初期化
time0 = time_from #平均開始時刻.
time1 = time_from + 10.minutes #平均終了時刻.
# ループを回しながら 10 分平均をとる.
while ( time1 < time_end ) do
p "#{time0} ... #{time1}"
# ホスト名のループを回す. 欠損値処理のため (データが存在しないホストに対して, 各カラムに NULL が入るようにするため).
hosts.each do |host|
# SQL 文作成.
# SELECT で指定する時刻はシングルクォートで囲むこと. また, strftime でフォーマットを指定する ('#{time1.strftime('%Y-%m-%d %H:%M:%S')}')
# temp, humi, lux の値を平均すること (5 点以上のデータが存在する場合に).
# 前 10 分平均なので, 平均する時間帯に time0 は含めず, time1 は含めること.
# 欠損値処理のため，SELECT 文中で hostname と time は決め打ちしている．
sql = "INSERT INTO monitoring_10min (hostname,time,temp,humi,lux)
SELECT '#{host}', '#{time1.strftime('%Y-%m-%d %H:%M:%S')}',
.... temp の平均値 ....,
.... temp の平均値 ....,
.... humi の平均値 ....,
FROM monitoring
WHERE hostname = '#{host}'
AND time > '#{time0.strftime('%Y-%m-%d %H:%M:%S')}'
AND time <= '#{time1.strftime('%Y-%m-%d %H:%M:%S')}' "
# p sql
# SQL 文実行
client.query(sql)
end
# 時刻の更新
time0 = time1
time1 = time1 + 10.minutes
end
実際に動かしてみる. 標準出力に平均をとるのに用いた時間帯が表示される. 最初の 1 回目はプログラムの実行にそこそこ時間がかかる.
$ ruby ~/make-10min.rb "2021-12-21 19:30:00 +0900 ... 2021-12-21 19:40:00 +0900" "2021-12-21 19:40:00 +0900 ... 2021-12-21 19:50:00 +0900" "2021-12-21 19:50:00 +0900 ... 2021-12-21 20:00:00 +0900" "2021-12-21 20:00:00 +0900 ... 2021-12-21 20:10:00 +0900" "2021-12-21 20:10:00 +0900 ... 2021-12-21 20:20:00 +0900" "2021-12-21 20:20:00 +0900 ... 2021-12-21 20:30:00 +0900" "2021-12-21 20:30:00 +0900 ... 2021-12-21 20:40:00 +0900"
MySQL に接続して, データがテーブル monitoring_10min に保管されているか確かめること.
$ mysql -u hogehoge -p
Enter password: (適宜入力)
...(略)...
MariaDB [(none)]> use iotex
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [iotex]> select hostname,time,temp,humi,lux from monitoring_10min;
...(略)...
| sugiyama | 2021-12-21 18:40:00 | NULL | NULL | NULL |
| sugiyama | 2021-12-21 18:50:00 | 17.28375 | 54.0875 | NULL |
| sugiyama | 2021-12-21 19:00:00 | 17.273 | 54.111000000000004 | NULL |
| sugiyama | 2021-12-21 19:10:00 | 17.248 | 54.163 | NULL |
| sugiyama | 2021-12-21 19:20:00 | 17.233 | 54.174 | NULL |
crontab を設定して定期に実行させるようにする. ラズパイから送られてくるまでのタイムラグを考慮して 30 秒ほど sleep で調整している.
$ crontab -e MAILTO="" PATH=/usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin */10 * * * * sleep 30; ruby make-10min.rb
1 時間毎の気温の代表値 (毎時 00 分の値) を抽出する.
10 分間隔のデータから毎時 0 分のデータを SQL の命令を用いて取り出す. 気象分野ではこのように取り出した 1 時間おきの値を, 1 時間ごとの代表値とみなす.
先の 10 分間隔の平均値を作るスクリプト (make-10min.rb) を参照して新たなスクリプト (make-1hour.rb) を作成する. 略されている部分は自分で補完して動くようにせよ.
$ vi ~/make-1hour.rb
...(前略)...
###
### 変数宣言
###
...(略)...
###
### 時刻の設定
###
# 平均を開始する時間 (デフォルト値は 2021/12/21)
time_from = DateTime.new( 2021, 12, 21, 0, 0, 0, "JST")
# テーブル monitoring_1hour に値が入っている場合は, 開始時刻 time_from の値を更新する.
sql = "SELECT time FROM monitoring_1hour ........ " # データベースに入っている最後の時刻を取得. DESC を使い, "LIMIT 1" で取り出すレコードの数を 1 つに限定すると良い.
client.query(sql).each do |item|
if item["time"].present?
time_from = Time.parse( item["time"].to_s ) # 時刻のフォーマットに変換するために Time.parse を用いる.
end
end
###
### 毎正時の値を抽出
###
# SQL 文作成.
# 時刻 time_from より後の時刻について, 時刻の末尾が '00:00' な時刻を取り出し，それを monitoring_1hour に入力すれば良い．
sql = "INSERT INTO monitoring_1hour
SELECT * from monitoring_10min
WHERE time LIKE .....
AND time > '#{time_from.strftime('%Y-%m-%d %H:%M:%S')}' "
p sql
# SQL 文実行
client.query(sql)
実際に動かしてみる.
$ ruby ~/make-1hour.rb
MySQL に接続して, データがテーブル monitoring_1hour に保管されているか確かめること.
$ mysql -u hogehoge -p
Enter password: (適宜入力)
...(略)...
MariaDB [(none)]> use iotex
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [iotex]> select hostname,time,temp,humi,lux from monitoring_1hour;
...(略)...
| sugiyama | 2021-12-21 18:00:00 | NULL | NULL | NULL |
| sugiyama | 2021-12-21 19:00:00 | 17.273 | 54.111000000000004 | NULL |
| sugiyama | 2021-12-21 20:00:00 | 17.184 | 54.278999999999996 | NULL |
| sugiyama | 2021-12-21 21:00:00 | 17.131 | 54.55800000000001 | NULL |
crontab を設定して定期に実行させるようにする. スクリプトを実行する時刻は毎時 11 分としている.
$ crontab -e MAILTO="" PATH=/usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin */10 * * * * sleep 30; ruby make-10min.rb 11 * * * * ruby make-1hour.rb <= 追加
データ処理で注意すること : 欠損値
何らかの理由でセンサー (ラズパイ) のネットワークが切れると, その時間帯のデータは取得できない = データが欠損する, ということが生じる. grafana に限らず dcl や gnuplot でも同じであるが, データ欠損があることを教えてあげないと 描画ツールは以下のように欠損部分をつないだ不自然な線グラフを作ってしまう. 本来はデータが無いはずなのに変なデータが存在しているように見えてしまい, これはデータ処理を行う上で大変まずい.
データに欠損値がある場合には, そこに NULL や取り得ない数字 (999 など)を入れておく. そして, 描画ツールに欠損値はどのように表されているか教える必要がある.
grafana はデフォルトで NULL は欠損値として扱うので, 以下では欠損値に NULL を入れることにする. NULL を欠損値として扱っていることを確かめるには, グラフの編集 (edit) ページを開き, 右カラムの "Graph styles" の "Connect null values" が "Never" になっていれば良い．
欠損値を正しく設定すれば, 以下の図のように欠損値の部分は折れ線でつながれなくなる.
課題
[1] テーブル monitoring には，サンプルデータが含まれている．テーブル monitoring に含まれているホスト 892cd2df (情報処理演習室) の 10 分毎のデータを用いて，2021/10/01 ~ 2021/10/04 の各日の平均値・最大値・最小値・標準偏差を求め，それらの統計量をテーブル monitoring_1day_avg, monitoring_1day_max, monitoring_1day_min, monitoring_1day_stddev, にそれぞれ入力せよ．
- テーブル monitoring_1day_avg, monitoring_1day_max, monitoring_1day_min, monitoring_1day_stddev, は既に作られているはずである．
- 平均値・最大値・最小値・標準偏差の計算には SQL の AVG, MAX, MIN, STDDEV 関数を用いること.
- 十分なデータ数がない場合には欠損値として扱うこと．その際の閾値は各自で決めて良い．
- MySQL のターミナルから SQL 命令を実行しても，Ruby スクリプトから実行しても，どちらでも良い．
- MySQL のターミナルで実行した場合は，行った命令やその戻り値を WBT のオンラインテキストにコピペすること．
- Ruby スクリプトを作成した場合は，そのスクリプトを WBT に提出すること．
なお，以下の SQL の実行結果は必ず提出物に含めること．
SELECT * FROM monitoring_1day_avg; SELECT * FROM monitoring_1day_max; SELECT * FROM monitoring_1day_min; SELECT * FROM monitoring_1day_stddev;
[2] grafana のダッシュボードを作成する．気温・湿度・照度のそれぞれのグラフに 1 分毎の値 (生データ), 10 分平均値, 1 時間毎の代表値の 3 本の線を引くこと.
- 自分のラズパイのデータに対して行うこと．
- 欠損値処理を行うこと.
- 72 時間以上のデータを表示した grafana のダッシュボードのスナップショットを提出すること (ブラウザの URL バーが含まれるようにスナップショットを撮ること)．
[3] チームメンバー全員のデータが表示された grafana のダッシュボードを作成する．気温・湿度・照度のそれぞれの変数に対して 10 分平均値と 1 時間毎の代表値のグラフを作成し, それぞれにメンバー全員分のデータを表示すること.
- 72 時間以上のデータを表示した grafana のダッシュボードのスナップショットを提出すること (ブラウザの URL バーが含まれるようにスナップショットを撮ること)．
- チーム代表者のダッシュボードを用いることになるが, メンバー全員がスクリーンショットを提出すること (すなわち, 同じチームのメンバーは同じファイルを提出することになる)
[4] [3] で作成した各チームの grafana ダッシュボードの URL の提出．
- cron がちゃんと動いていて，10 分平均値や 1 時間ごとの値が更新され続けるか教員の方で確認します．
[5 (Extra)] 自分のラズパイのデータについて，1 時間ごとの値から「1 日ごと」の平均値・最大値・最小値・標準偏差を求め，それを grafana で表示させてみよ．cron で定期的にスクリプトを実行し，グラフが定期的に更新されるようにすること．
- grafana ダッシュボードの URL およびスナップショットを提出すること．