2020 年度 OSS リテラシ 3 : 代表値・欠損値処理
はじめに
センサーから得られたデータの代表値 (平均値・最大・最小・標準偏差) を計算し, それを MariaDB のテーブルに登録し, grafana で可視化する.
代表値の計算を Ruby などにやらせても良いが, ここでは MySQL の関数を用いて計算することにする.
今回の実習では, ラズパイから送られてきた 1 分毎の瞬間値から 10 分平均値, 1 時間毎の値の抽出を作成し, それらのデータを grafana で表示できるようにする.
- 1 分間隔のデータ => 10 分平均値
- 10 分間の中にデータが一定数以上無い場合には欠損値に.
- 10 分間隔の平均値 => 1 時間間隔の平均値 (10 分間隔のデータから, 毎正時の値を抽出)
- 10 分間隔の平均値 => 1 時間毎の標準偏差 (課題)
統計量の作り方は気象庁のやり方を真似ている (全く同じではないが). 詳細は気象観測統計の解説 を参照のこと.
以下の作業はサーバ (VM) で行うべきものである. ラズパイで行うことはない (きちんとデータが送られている限りは).
準備
データの確認
前回までの内容で, ラズパイからデータが送られてきたデータが grafana で可視化されているはずである. 各自の管理するサーバ上で動いている grafana において, 以下のような画面が表示されているか確認すること.
また, MySQL を直接確認すると, 1 分間隔でデータがテーブルに入力されていることがわかる.
$ 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 time,temp from monitoring order by time;
...(略)...
| 2019-01-15 09:32:00 | 19.68 |
| 2019-01-15 09:33:00 | 19.14 |
| 2019-01-15 09:34:00 | 18.84 |
| 2019-01-15 09:35:00 | 18.62 |
| 2019-01-15 09:36:00 | 18.49 |
| 2019-01-15 09:37:00 | 18.35 |
| 2019-01-15 09:38:00 | 18.3 |
| 2019-01-15 09:39:00 | 18.95 |
| 2019-01-15 09:40:00 | 19.85 |
| 2019-01-15 09:41:00 | 19.8 |
| 2019-01-15 09:42:00 | 19.85 |
なお, MySQL のデータベース iotex のテーブル monitoring のカラムの内, 2020年度の演習で値が入っているものは以下の 5 つである. それ以外のカラムの値は NULL になっている.
- hostname (ホスト名)
- time (時刻)
- temp (温度)
- humi (湿度)
- lux (照度)
必要なパッケージのインストール
Ruby からデータベースを操作するために必要なパッケージをインストールする.
# apt-get update # apt-get install ruby-mysql2 ruby-activesupport
MySQL の接続情報の用意
Ruby からデータベースに接続するための情報をまとめた YAML 形式のファイル (db_info.yml) を用意する. すでに GitHub から clone した iotex-server にサンプルが含まれているので, それを利用する. データベース接続情報 (db_info.yml) は絶対に ~/public_html 以下には置かないこと. なお, yml 形式のファイルでは, コロン (:) の後に空白を 1 つ入れる必要があることに注意せよ.
$ cd $ cd iotex-server/conf $ 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 $ vi db_info.yml ADPT: "mysql2" SERV: "localhost" USER: "<username>" (適宜修正すること. "<" と ">" は削除すること) PASS: "<password>" (適宜修正すること. "<" と ">" は削除すること) DBNM: "iotex"
データ処理で注意すること : 欠損値
何らかの理由でセンサー (ラズパイ) のネットワークが切れると, その時間帯のデータは取得できない = データが欠損する, ということが生じる. grafana に限らず dcl や gnuplot でも同じであるが, データ欠損があることを教えてあげないと 描画ツールは以下のように欠損部分をつないだ不自然な線グラフを作ってしまう. 本来はデータが無いはずなのに変なデータが存在しているように見えてしまい, これはデータ処理を行う上で大変まずい.
データに欠損値がある場合には, そこに NULL や取り得ない数字 (999 など)を入れておく. そして, 描画ツールに欠損値はどのように表されているか教える必要がある.
grafana はデフォルトで NULL は欠損値として扱うので, 以下では欠損値に NULL を入れることにする. NULL を欠損値として扱っていることを確かめるには, グラフの編集 (edit) ページを開き, "Display" タグを開くと良い.
欠損値を正しく設定すれば, 以下の図のように欠損値の部分は折れ線でつながれなくなる.
10 分平均値を作成する
まずはテーブル 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
テストデータが入っていることを確認する. 簡単のために温度のみ表示する.
MariaDB [iotex]> select hostname,time,temp from monitoring where hostname = 'j55' and time > '2019-08-01 00:00:00' and time <= '2019-08-01 00:10:00'; +----------+---------------------+--------------------+ | hostname | time | temp | +----------+---------------------+--------------------+ | j55 | 2019-08-01 00:01:00 | 29.586666666666666 | | j55 | 2019-08-01 00:02:00 | 29.578333333333333 | | j55 | 2019-08-01 00:03:00 | 29.578333333333333 | | j55 | 2019-08-01 00:04:00 | 29.581666666666663 | | j55 | 2019-08-01 00:05:00 | 29.58 | | j55 | 2019-08-01 00:06:00 | 29.578333333333333 | | j55 | 2019-08-01 00:07:00 | 29.575000000000003 | | j55 | 2019-08-01 00:08:00 | 29.575 | | j55 | 2019-08-01 00:09:00 | 29.573333333333334 | | j55 | 2019-08-01 00:10:00 | 29.576666666666668 | +----------+---------------------+--------------------+ 10 rows in set (0.001 sec)
上記時間帯について平均操作を行う. 気象のデータは前 10 分で平均するのが普通であるが, 何も指定しないと出力では平均開始時刻に対する温度の平均値という形で表示されてしまう (前 10 分平均でなく後 10 分平均の形で表示されてしまう).
MariaDB [iotex]> select hostname,time,avg(temp) from monitoring where hostname = 'j55' and time > '2019-08-01 00:00:00' and time <= '2019-08-01 00:10:00'; +----------+---------------------+-------------------+ | hostname | time | avg(temp) | +----------+---------------------+-------------------+ | j55 | 2019-08-01 00:01:00 | 29.57833333333333 | +----------+---------------------+-------------------+ 1 row in set (0.001 sec)
明示的に表示する時刻を変える場合は, 例にあるように時刻を手動で書き入れてしまえ良い.
MariaDB [iotex]> select hostname,'2019-08-01 00:10:00',avg(temp) from monitoring where hostname = 'j55' and time > '2019-08-01 00:00:00' and time <= '2019-08-01 00:10:00'; +----------+---------------------+--------------------+ | hostname | 2019-08-01 00:10:00 | avg(temp) | +----------+---------------------+--------------------+ | j55 | 2019-08-01 00:01:00 | 29.57833333333333 | +----------+---------------------+--------------------+ 1 row in set (0.001 sec)
同様にしてホスト名も例にあるように手動で書き入れてしまえば, そのように表示される.
MariaDB [iotex]> select 'hoge','2019-08-01 00:10:00',avg(temp) from monitoring where hostname = 'j55' and time > '2019-08-01 00:00:00' and time < '2019-08-01 00:10:00'; +------+---------------------+--------------------+ | hoge | 2019-08-01 00:10:00 | avg(temp) | +------+---------------------+--------------------+ | hoge | 2019-08-01 00:10:00 | 29.57833333333333 | +------+---------------------+--------------------+ 1 row in set (0.001 sec)
このようにして平均操作を行った結果を別のテーブルにそのまま入力することができる.
MariaDB [iotex]> insert into monitoring_10min (hostname,time,temp) select 'hoge','2019-08-01 00:10:00',avg(temp) from monitoring where hostname = 'j55' and time > '2019-08-01 00:00:00' and time < '2019-08-01 00:10:00'; Query OK, 1 row affected (0.001 sec) Records: 1 Duplicates: 0 Warnings: 0 MariaDB [iotex]> select hostname,time,temp from monitoring_10min where time = '2019-08-01 00:10:00'; +----------+---------------------+--------------------+ | hostname | time | temp | +----------+---------------------+--------------------+ | hoge | 2019-08-01 00:10:00 | 29.57833333333333 | +----------+---------------------+--------------------+ 1 row in set (0.032 sec)
平均を作成する際に, 指定された時間帯に一定以上のデータ数が含まるか調べることをよく行う. 例えば, 指定された時間帯に 5 点以上のデータがあれば平均し, そうでなければ null (欠損値) を入れる場合には 以下のような SQL 文を書けば良い.
MariaDB [iotex]> insert into monitoring_10min (time,hostname,temp) select '2019-08-01 00:20:00',hostname,(case when count(temp) >= 5 then avg(temp) else null end) from monitoring where time > '2019-08-01 00:10:00' and time <= '2019-08-01 00:20:00' and hostname = 'j55'; Query OK, 1 row affected (0.002 sec) Records: 1 Duplicates: 0 Warnings: 0 MariaDB [iotex]> select hostname,time,temp from monitoring_10min where time = '2019-08-01 00:20:00'; +----------+---------------------+-------------------+ | hostname | time | temp | +----------+---------------------+-------------------+ | j55 | 2019-08-01 00:20:00 | 29.57133333333333 | +----------+---------------------+-------------------+ 1 row in set (0.028 sec)
上記の SQL 文を踏まえて, Ruby スクリプトを作成する. 以下に例を示すので, プログラム中のコメントを参考にやっていることを理解して欲しい. プログラムに SQL 文を書く前に, 上記のように SQL のターミナルで実行してみて期待通り動作するか確かめると良い. なお, group by hostname を使うと欠損値処理ができない (その時刻にデータを持つホストに対してしか平均操作がなされない) ので, 存在するホスト名を調べておいて, そのホスト名でループを回すべきである.
$ vi ~/make-10min.rb
#!/usr/bin/env ruby
# coding: utf-8
require 'yaml'
require 'mysql2'
require 'active_support/time'
###
### 変数宣言
###
# データベースへの接続情報の置き場.
# ~/public_html 以下には置かないこと.
conf = "/home/hogehoge/iotex-server/conf/db_info.yml" # <= 書き換えること!!
# データベースのテーブル名 (既に定義済み)
mytable_from = "monitoring"
mytable_to = "monitoring_10min"
# 設定ファイルの読み込み
mydb = YAML.load_file( conf )
# データベースへの接続
client = Mysql2::Client.new(
:host => "#{mydb["SERV"]}",
:username => "#{mydb["USER"]}",
:password => "#{mydb["PASS"]}",
:database => "#{mydb["DBNM"]}"
)
###
### 時刻の設定
###
# 平均を開始する時間 (デフォルト値)
time_from = DateTime.new( 2020, 10, 26, 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} +++++"
###
### ホスト名の取得
###
# テーブル monitoring のカラム hostname の値を取得.
hosts = Array.new #配列 hosts の定義
sql = "..........................." #ホスト名の取得. 重複を切る (DISTINCT) を使うと良い
client.query(sql).each do |item|
hosts.push( item["hostname"] ) unless item["hostname"] == '' #配列 hosts に値を追加.
end
p hosts
###
### 前 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 は含めること.
sql = "................................" #長い SQL 文になるので, 改行を含めて良い
# p sql
# SQL 文実行
client.query(sql)
end
# 時刻の更新
time0 = time1
time1 = time1 + 10.minutes
end
実際に動かしてみる. 標準出力に平均をとるのに用いた時間帯が表示される. 最初の 1 回目はプログラムの実行にそこそこ時間がかかる.
$ ruby ~/make-10min.rb "2019-01-15 22:00:00 +0900 ... 2019-01-15 22:10:00 +0900" "2019-01-15 22:10:00 +0900 ... 2019-01-15 22:20:00 +0900" "2019-01-15 22:20:00 +0900 ... 2019-01-15 22:30:00 +0900" "2019-01-15 22:30:00 +0900 ... 2019-01-15 22:40:00 +0900" "2019-01-15 22:40:00 +0900 ... 2019-01-15 22:50:00 +0900" "2019-01-15 22:50:00 +0900 ... 2019-01-15 23:00:00 +0900" "2019-01-15 23:00:00 +0900 ... 2019-01-15 23:10:00 +0900" "2019-01-15 23:10:00 +0900 ... 2019-01-15 23:20:00 +0900" "2019-01-15 23:20:00 +0900 ... 2019-01-15 23:30: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 time,temp from monitoring_10min order by time;
...(略)...
| 2019-01-15 09:30:00 | 19.68 |
| 2019-01-15 09:40:00 | 19.14 |
crontab を設定して定期に実行させるようにする. ラズパイから送られてくる時間が毎分 0 秒とはならないので, 30 秒ほど sleep で調整している.
$ crontab -e MAILTO="" PATH=/usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin */10 * * * * sleep 30; ruby make-10min.rb
1 時間おきの値を抽出する.
10 分間隔のデータから毎時 0 分のデータを SQL の命令を用いて取り出す. 気象分野ではこのように取り出した 1 時間おきの値を, 1 時間ごとの代表値とみなす.
先の 10 分間隔の平均値を作るスクリプト (make-10min.rb) を参照して新たなスクリプト (make-1hour.rb) を作成する. 略されている部分は自分で補完して動くようにせよ.
$ vi ~/make-1hour.rb ...(前略)... ### ### 変数宣言 ### ...(略)... ### ### 時刻の設定 ### ...(略)... ### ### 毎正時の値を抽出 ### # SQL 文作成. # 時刻 time_from より後の時刻について, 時刻の末尾が '00:00' となる行を mytable_from より取り出し, # その行を mytable_to に代入すれば良い. sql = # .... 自分で考えよ .... #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 time,temp from monitoring_1hour order by time;
...(略)...
| 2019-01-15 09:30:00 | 19.68 |
| 2019-01-15 09:40:00 | 19.14 |
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 <= 追加
課題
[1] 以下の手順で monitoring_10min, monitoring_1hour に入れた値が正しく計算されたものであることを示せ. WBT のオンラインテキストもしくは電子ファイル (pdf) で提出すること.
a) テーブル monitoring から, とあるホストに対して, 1 時間分の temp, humi, lux を表示する. 1 分おきの値を使うので 60 個のレコードが並ぶことになる. ターミナルの表示もしくは csv ファイルの中身をコピペして, オンラインテキストもしくは電子ファイルで示すこと.
例) SELECT time,temp,humi,lux FROM monitoring WHERE hostname LIKE 'hogehoge' AND time > 'xxxx' AND time <= 'xxxx' (ターミナルへ表示) 例) $ mysqldump -u hogehoge -p --tab=/tmp --fields-terminated-by=, iotex monitoring (CSV ファイルへ出力)
- b) a) の出力を用いて, 何らかの方法で (C 言語や Ruby で計算, Excel で計算, 手計算,...) で前 10 分平均値を計算する. プログラムの出力, もしくは, 手計算の途中式と結果, を示すこと. て計算の場合は計算用紙の写真を撮って, それを貼り付けて良い.
- c) テーブル monitoring_10min から, とあるホストに対して, 1 時間分の temp, humi, lux を表示する. その表示が b) の結果と一致することを示すこと.
- d) テーブル monitoring_10min から, とあるホストに対して, 毎 0 分の temp, humi, lux を数時間分表示する. さらに, テーブル monitoring_1hour から, とあるホストに対して, 数時間分の temp, humi, lux を数時間分表示する. その 2 つを並べて値が一致することを示すこと (表示に用いた SQL 文も示すこと).
[2] kadai-4 で作成した grafana のダッシュボードを元に, 気温・湿度・照度のそれぞれのグラフに 1 分毎の値 (生データ), 10 分平均値, 1 時間毎の代表値の 3 本の線を引くこと.
- 自分のラズパイのデータに対して行えば良い.
- 欠損値処理を行うこと.
- 24 時間以上のデータを表示した grafana のダッシュボードのスナップショットを提出すること. また, grafana のダッシュボードの URL も提出すること.
[3] kadai-5 で作成した grafana のダッシュボードにグラフを追加する. 気温・湿度・照度のそれぞれの変数に対して 10 分平均値と 1 時間毎の代表値のグラフを作成し, それぞれにメンバー全員のデータを表示すること.
- 24 時間以上のデータを表示した grafana のダッシュボードのスナップショットを提出すること. また, grafana のダッシュボードの URL も提出すること.
- チーム代表者のダッシュボードを用いることになるが, メンバー全員がスクリーンショットを提出すること (すなわち, 同じチームのメンバーは同じファイルを提出することになる)
[4] 気温・湿度・照度のそれぞれについて, 10 分平均値から 1 時間毎の標準偏差を計算してテーブル monitoring_1hour_stddev に値を入力せよ. [2] で作成したダッシュボードに気温の標準偏差のグラフを追加し, そのスナップショットを提出すること.
- テーブル monitoring_1hour_stddev とテーブル monitoring_1hour のスキーマは同じとする. "> CREATE TABLE monitoring_1hour_stddev LIKE monitoring_1hour;"
- 標準偏差の計算には SQL の STDDEV 関数を用いること.
- crontab に登録し, データが自動的に更新されるようにしておくこと.