2018 年度 OSS リテラシー 3 : 第 12 回データ解析と可視化 (1)

準備

データの確認

前回までの内容で, ラズパイからデータが送られてきたデータが grafana で可視化されているはずである. 各自の管理するサーバ上で動いている grafana において, 以下のような画面が表示されているか確認すること.

また, MySQL を直接確認すると, 1 分間隔でデータがテーブルに入力されていることがわかる.

vm$ 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 iotex2018 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 のテーブルには以下のカラムが存在するが, 実習では温度湿度センサー SHT75 を 1 台ラズパイに接続しているだけなので, hostname, time, temp, humi, dp 以外は NULL になっている.

hostname (ホスト名)
time (時刻)
temp (温度, SENSIRION SHT75 (1 台目))
temp2 (温度, SENSIRION SHT75 (2 台目))
temp3 (温度, SENSIRION SHT75 (3 台目))
humi (湿度, SENSIRION SHT75 (1 台目))
humi2 (湿度, SENSIRION SHT75 (2 台目))
humi3 (湿度, SENSIRION SHT75 (3 台目))
dp (露点温度, SENSIRION SHT75 (1 台目))
dp2 (露点温度, SENSIRION SHT75 (2 台目))
dp3 (露点温度, SENSIRION SHT75 (3 台目))
pres (圧力, BMP180)
bmptemp (温度, BMP180)
dietemp (基板の温度, TMP007)
objtemp (放射温度 (壁の温度), TMP007)
lux (照度, TSL2561)
winddir (風向)
windvel (風速)

Ruby スクリプトのサンプル取得

実習用サンプルスクリプト (3 種類) とデータベース接続情報を書くファイルのサンプルを入手する.

vm$ mkdir ~/bin

vm$ cd ~/bin

vm$ wget http://www.gfd-dennou.org/arch/sugiyama/IoTeX_2018/sample/db_1min-10min.rb

vm$ wget http://www.gfd-dennou.org/arch/sugiyama/IoTeX_2018/sample/db_10min-1hour.rb

vm$ wget http://www.gfd-dennou.org/arch/sugiyama/IoTeX_2018/sample/db_1hour-1day.rb

vm$ wget http://www.gfd-dennou.org/arch/sugiyama/IoTeX_2018/sample/db_info.yml

$ ls ~/bin/

  db_10min-1hour.rb  db_1hour-1day.rb  db_1min-10min.rb  db_info.yml

必要なパッケージのインストール

vm# apt-get update; apt-get install ruby-mysql2

データの解析 & 可視化

本日の作業手順は「仮想サーバ」で行うべき作業である. ラズパイで行うことはない (きちんとデータが送られている限りは).

今回の実習では, ラズパイから送られてきた 1 分平均値から 10 分平均値, 1 時間毎の値の抽出, 1 日の最低・最大・平均値を作成し, それらのデータを grafana で表示できるようにする.

完成予想図は以下の図の通りである.

サーバ情報 (ホスト名, IP, SSID),
気温 (1 分平均, 10 分平均, 1 時間おきの値)
気温 (1 日平均の最小・最大・平均). 気象分野では 1 時間おきの値をつかってこれらを計算する.
気温の標準偏差
湿度 (1 分平均, 10 分平均, 1時間おきの値)
露点温度 (1 分平均)
不快指数 (10 分平均)

なお, まだラズパイからうまくデータが送られてきていない, もしくはデータが少なくて困っている場合は, サンプルとして配ったデータに含まれるホスト名が j1511 or j1526 のデータを使うと良い. 以下のようにサンプルデータを可視化してみると, j1511 と j1526 が 12/6 ~ 12/11 の期間にデータが入っていることがわかる. grafana に以下のように SQL 文を入力してみよ.

SELECT
  UNIX_TIMESTAMP(time) AS "time",
  temp,
  hostname as metric
FROM iotex2018
WHERE
  $__timeFilter(time)
ORDER BY time

1 分平均値から 10 分平均値を作成する

以下のサンプルスクリプト (db_1min-10min.rb) において, ホスト名や MySQL サーバ接続情報を自分のものに変更せよ. データベース接続情報 (db_info.yml) は絶対に ~/public_html 以下には置かないこと.

このプログラム中においては SQL の AVG 関数で平均値を計算している.

#!/usr/bin/env ruby
# coding: utf-8
#
# 表題: DB から取り出した 1 分毎のデータから 10 分平均データを作る.
# 

require 'yaml'
require 'mysql2'
require 'fileutils'
require 'active_support/time'

###
### 変数宣言
###

# ホスト名
myhost = "j52"                   # <<< 変更

# データベースのテーブル名
mytable_from = "iotex2018"
mytable_to   = "iotex2018_10min"

# データベースへの接続情報の置き場.
# ~/public_html 以下には置かないこと.
conf = "/home/hogehoge/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"]}"
)

###
### 新たなテーブルの作成
###

# テーブルの存在の確認
flag = false
sql  = "SHOW TABLES"
client.query(sql).each do |item|
  if item["Tables_in_#{mydb["DBNM"]}"] == mytable_to
     flag = true
  end
end
# テーブルが存在しない場合は新規作成. 不快指数 didx をカラムに追加. 
unless (flag)
  sql  = "CREATE TABLE #{mytable_to} like #{mytable_from}"
  client.query(sql)
  sql  = "ALTER  TABLE #{mytable_to} ADD didx double"
  client.query(sql)
end

###
### 時刻の設定
###

# 平均を開始する時間 (デフォルト値)
time_from = DateTime.new( 2018, 11, 1, 0, 0, 0, "JST")

# テーブルに既に値が入っている場合は平均開始時刻 time_from の値を更新する. 
sql = "SELECT time FROM #{mytable_to} WHERE hostname LIKE '#{myhost}' 
       ORDER BY time DESC LIMIT 1"
client.query(sql).each do |item|
  if item["time"].present?
    time_from = Time.parse( item["time"].to_s )
  end
end

# 平均を終了する時間
time_end = DateTime.now

###
### 前 10 分平均値の計算とテーブルへの代入
###

# 時刻の初期化
time0 = time_from
time1 = time_from + 10.minutes 

# ループを回しながら 10 分平均をとる. 
while ( time1 < time_end ) do
  p "#{time0} ... #{time1}"

  # 温度湿度の平均値と, 温度が 10 分間に何回記録されているか調べる. 
  sql = "SELECT count(temp) as count, AVG(temp) as temp, AVG(humi) as humi
         FROM #{mytable_from} 
         WHERE time > '#{time0}' AND time <= '#{time1}' 
         AND hostname LIKE '#{myhost}' ORDER BY time"

  # SQL の出力を用いて不快指数を計算し, 新たなテーブルに入れる. 
  client.query(sql).each do |item|

    # 10 分のうち, 5 点以上のデータがあるなら平均操作を行う
    if item["count"] > 5                                     

      # 平均値を変数に代入
      temp = item["temp"]
      humi = item["humi"]

      # 不快指数の計算
      didx = 0.81 * temp + 0.01 * humi * (0.99 * temp - 14.3) + 46.3

      # 平均値と不快指数を新たなテーブルへ入力
      sql = "INSERT INTO #{mytable_to} (hostname,time,temp,humi,didx) 
             VALUES ('#{myhost}', '#{time1}', #{temp}, #{humi}, #{didx})"
      client.query(sql)      
    end
  end

  time0 = time1
  time1 = time1 + 10.minutes
end

exit

データベースの接続情報が書かれたファイル (db_info.yml) は適切に修正する.

vm$ vi db_info.yml

実際に動かしてみる. 標準出力に平均をとるのに用いた時間帯が表示される.

vm$ ruby ~/bin/db_1min-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 に接続して, データがテーブル iotex2018_10min に保管されているか確かめること.

vm$ 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 iotex2018_10min order by time;

       ...(略)...

    | 2019-01-15 09:30:00 | 19.68 |
    | 2019-01-15 09:40:00 | 19.14 |

crontab を設定して定期に実行させるようにする. ラズパイから送られてくる時間が毎分 0 秒とはならないので, 30 秒ほど sleep で調整している.

vm$ crontab -e

  MAILTO=""
  PATH=/usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin
  */10 * * * * sleep 30; ruby bin/db_1min-10min.rb

1 時間おきの値を抽出する.

10 分間隔のデータから毎時 0 分のデータを SQL の命令を用いて取り出す. 気象分野ではこのように取り出した 1 時間おきの値を, 1 時間ごとの代表値とみなす. 次に行う 1 日の平均・最小・最大・標準偏差は, 1 時間おき (00 分) のデータを使って計算する.

以下のようなサンプルスクリプト (db_10min-1hour.rb) において, ホスト名や MySQL サーバ接続情報を自分のものに更新せよ. データベース接続情報 (db_info.yml) は絶対に ~/public_html 以下には置かないこと.

#!/usr/bin/env ruby
# coding: utf-8
#
# 表題: データ解析スクリプト. DB から 1 時間おきの値を抽出する.
# 

require 'yaml'
require 'mysql2'
require 'fileutils'
require 'active_support/time'

###
### 変数宣言
###

# ホスト名
myhost = "j52"

# データベースのテーブル名
mytable_from = "iotex2018_10min"
mytable_to   = "iotex2018_1hour"

# データベースへの接続情報の置き場.
# ~/public_html 以下には置かないこと.
conf = "/home/hogehoge/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"]}"
)

###
### 新たなテーブルの作成
###

# テーブルの存在の確認
flag = false
sql  = "SHOW TABLES"
client.query(sql).each do |item|
  if item["Tables_in_#{mydb["DBNM"]}"] == mytable_to
    flag = true
  end
end
# テーブルが存在しない場合は新規作成. 
unless (flag)
  sql  = "CREATE TABLE #{mytable_to} like #{mytable_from}"
  client.query(sql)
end

###
### 時刻の設定
###

# 平均を開始する時間 (デフォルト値)
time_from = DateTime.new( 2018, 11, 1, 0, 0, 0, "JST")

# テーブルに既に値が入っている場合は平均開始時刻 time_from の値を更新する. 
sql = "SELECT time FROM #{mytable_to} WHERE hostname LIKE '#{myhost}' 
       ORDER BY time DESC LIMIT 1"
client.query(sql).each do |item|
  if item["time"].present?
    time_from = Time.parse( item["time"].to_s )
  end
end

###
### 毎正時の値を抽出
###

sql = "INSERT INTO #{mytable_to} 
       SELECT * FROM #{mytable_from} 
       WHERE TIME(time) LIKE '%00:00' AND time > '#{time_from}' "
p sql
client.query(sql)

実際に動かしてみる.

vm$ ruby ~/bin/db_10min-1hour.rb

MySQL に接続して, データがテーブル iotex2018_1hour に保管されているか確かめること.

vm$ 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 iotex2018_1hour order by time;

       ...(略)...

    | 2019-01-15 09:30:00 | 19.68 |
    | 2019-01-15 09:40:00 | 19.14 |

crontab を設定して定期に実行させるようにする. スクリプトを実行する時刻は毎時 10 分としている.

vm$ crontab -e

  MAILTO=""
  PATH=/usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin
  */10 * * * *  sleep 30; ruby bin/db_1min-10min.rb
  10   * * * *  ruby bin/db_10min-1hour.rb

1 日単位での統計量の計算

1 日ごとに統計量を計算する. 以下のサンプルスクリプト (db_1hour-1day.rb) を完成させよ. 平均・最大・最小・標準偏差には, SQL の AVG, MAX, MIN, STDDEV 関数を用いることができる. 1 日の平均・最小・最大・標準偏差は 1 時間おき (00 分) のデータを使って計算するため, 1 日 24 点分のデータが有るかないかをチェックし, 有る場合のみ計算を行なっている.

-> db_1hour-1day.rb を完成させないと本日の課題ができあがらないので注意.

#!/usr/bin/env ruby
# coding: utf-8
#
# 表題: データ解析スクリプト. 1 日平均値を作る
# 

require 'yaml'
require 'mysql2'
require 'fileutils'
require 'active_support/time'

###
### 変数宣言
###

# ホスト名
myhost = "j52"

# データベースのテーブル名
mytable_from = "iotex2018_1hour"
mytable_to   = {
  "min"  => "iotex2018_1day_min",
  "max"  => "iotex2018_1day_max",
  "avg"  => "iotex2018_1day_avg",
  "stddev"  => "iotex2018_1day_stddev"
}

# データベースへの接続情報の置き場.
# ~/public_html 以下には置かないこと.
conf = "/home/hogehoge/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"]}"
)

###
### 新たなテーブルの作成
###

# テーブルの存在の確認
mytable_to.each_key do |key|
  flag = false
  sql  = "SHOW TABLES"
  client.query(sql).each do |item|
    if item["Tables_in_#{mydb["DBNM"]}"] == mytable_to[ key ]
      flag = true
    end
  end

  # テーブルが存在しない場合は新規作成. 
  unless (flag)
    sql  = "CREATE TABLE #{mytable_to[key]} like #{mytable_from}"
    client.query(sql)
  end
end

###
### 時刻の設定
###

# 平均を開始する時間 (デフォルト値)
time_from = DateTime.new( 2018, 11, 1, 0, 0, 0, "JST")

# テーブルに既に値が入っている場合は平均開始時刻 time_from の値を更新する. 
sql = "SELECT time FROM #{mytable_to['min']} WHERE hostname LIKE '#{myhost}' 
       ORDER BY time DESC LIMIT 1"
client.query(sql).each do |item|
  if item["time"].present?
    time_from = Time.parse( item["time"].to_s )
  end
end

# 平均を終了する時間
time_end = DateTime.now


###
### 前 1 日のデータから最大・最小・平均・標準偏差を計算
###

# 時刻の初期化
time0 = time_from
time1 = time_from + 1.days

# ループを回しながら最大・最小・平均・標準偏差をとる. 
while ( time1 < time_end ) do
  p "#{time0} ... #{time1}"

  ####
  #### 最小値
  ####
  sql = "SELECT count(temp) as count, 
         MIN(temp) as temp, MIN(humi) as humi, MIN(didx) as didx
         FROM #{mytable_from} 
         WHERE time > '#{time0}' AND time <= '#{time1}' 
         AND hostname LIKE '#{myhost}' ORDER BY time"

  # SQL の出力を用いて不快指数を計算し, 新たなテーブルに入れる. 
  client.query(sql).each do |item|

    # 24 時間分のデータが揃っているか確認
    if item["count"] == 24    

      # 変数に代入
      temp = item["temp"]
      humi = item["humi"]
      didx = item["didx"]

      # 新たなテーブルへ入力
      sql = "INSERT INTO #{mytable_to["min"]} (hostname,time,temp,humi,didx) 
             VALUES ('#{myhost}', '#{time1}', #{temp}, #{humi}, #{didx})"
      client.query(sql)

    end
  end

  ####
  #### 最大値
  ####

     #### ここに自分でスクリプトを書く ####


  ####
  #### 平均値
  ####

     #### ここに自分でスクリプトを書く ####

  ####
  #### 標準偏差
  ####

     #### ここに自分でスクリプトを書く ####


  time0 = time1
  time1 = time1 + 1.days
end

スクリプトが完成したら実際に動かしてみる.

vm$ ruby ~/bin/db_1hour-1day.rb

MySQL に接続して, データがテーブル iotex2018_1day_min, iotex2018_1day_max, iotex2018_1day_avg, iotex2018_1day_stddev に保管されているか確かめること.

vm$ 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 iotex2018_1day_min order by time;

       ...(略)...

    | 2019-01-15 09:30:00 | 19.68 |
    | 2019-01-15 09:40:00 | 19.14 |

  その他の iotex2018_1day_max, iotex2018_1day_avg, iotex2018_1day_stddev に
  ついても必ず確認すること.

さらに, crontab に設定し, 定期的に実行できるようにする.

vm$ crontab -e

  MAILTO=""
  PATH=/usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin
  */10 * * * * sleep 30; ruby bin/db_1min-10min.rb
  10   * * * * ruby bin/db_10min-1hour.rb
  20   0 * * * ruby bin/db_1hour-1day.rb

grafana での可視化

作成した統計量を可視化する. -> 本日の課題

これまでに作成した grafana のダッシュボードを改良し, 以下のデータをダッシュボード上で表示せよ.

サーバ情報 (ホスト名, IP, SSID),
気温 (1 分平均, 10 分平均, 1 時間おきの値)
気温 (1 日平均の最小・最大・平均). 気象分野では 1 時間おきの値をつかってこれらを計算する.
気温の標準偏差
湿度 (1 分平均, 10 分平均, 1時間おきの値)
露点温度 (1 分平均)
不快指数 (10 分平均)

気温の 1 分平均値, 10 分平均値・1 時間おきの値を重ね書きする場合は, 以下のようにSQL を書くと良い. ホスト名は適宜書き換えること.

SELECT
  UNIX_TIMESTAMP(time) as time_sec,
  temp as "1min"
FROM iotex2018
WHERE
  $__timeFilter(time) AND
  hostname = 'j52'
ORDER BY time

SELECT
  UNIX_TIMESTAMP(time) as time_sec,
  temp as "10min"
FROM iotex2018_10min
WHERE
  $__timeFilter(time) AND
  hostname = 'j52'
ORDER BY time

SELECT
  UNIX_TIMESTAMP(time) as time_sec,
  temp as "1hour"
FROM iotex2018_1hour
WHERE
  $__timeFilter(time) AND
  hostname = 'j52'
ORDER BY time

gnuplot を用いた可視化

gnuplot は伝統的かつ標準的な UNIX 系グラフ作成ツールであり, 覚えておいて損はない. 将来的に grafana は消えることはあっても gnuplot が消えることはほぼ無いであろう. 今回は gnuplot の ruby ラッパー (numo/gnuplot) を使って図を作ることにする.

grafana でグラフのタイトル部分を右クリックすると, データを csv に出力することができる (下図). この機能を用いて温度などの csv ファイルから gnuplot を用いてグラフを作成する. なお, CSV に変換する際は "Mode : Series as column" を選ぶこと. また Windows PC で "CSV export" を行うとファイルが Windows 上に保存される. FileZilla や scp で自分の仮想マシンにコピーするか (sky に一旦保存して, さらに sky から仮想サーバに転送する必要がある), Windows 上でファイルを開いてそれを仮想マシンのファイルにコピペする, などしないといけない.

gnuplot のインストール

$ sudo apt-get update 

  ... (略) ...

$ sudo apt-get install gnuplot-x11 fonts-noto-cjk

  ... (略) ...

$ sudo gem install numo-gnuplot

  Fetching: numo-gnuplot-0.2.4.gem (100%)
  Successfully installed numo-gnuplot-0.2.4
  Parsing documentation for numo-gnuplot-0.2.4
  Installing ri documentation for numo-gnuplot-0.2.4
  Done installing documentation for numo-gnuplot after 2 seconds
  1 gem installed

~/public_html において以下のようなスクリプトを作成・実行すると, ブラウザ上 (http://10.176.0.XXX/~hogehoge/, IP とユーザ名は適宜修正すること) から png ファイルを確認することができる.

#!/usr/bin/env ruby
# coding: utf-8
#

require 'csv'
require 'date'
require 'fileutils'
require 'numo/gnuplot'

# 初期化
csvfile = "temp.csv"
output  = "/home/hogehoge/public_html/output.png"
time_list = []
data_list = []

# csv ファイルの読み込み
CSV.foreach( csvfile, {:encoding => "UTF-8", :col_sep => ";" }) do |row|
  next if row[0] == "Time"  # 1 行目無視
  p row
  time_list.push( row[0] )
  data_list.push( row[1].to_f )
end

# 作図
Numo.gnuplot do
  debug_on
  set title:    "温度"
  set ylabel:   "temperature (C)"
  set xlabel:   "time"
  set xdata:    "time"
  set timefmt:  "%Y-%m-%dT%H:%M:%S+09:00"
  set format_x: "%m/%d %H:%M"
  set xtics:    "rotate by -60"
  set terminal: "png"
  set output:   "#{output}"

  plot time_list, data_list, using:'1:($2)', with:"linespoints", lc_rgb:"red", lw:2, title:"ほげ"
end

注意事項

スクリプトを実行した時に以下のようなメッセージが表示された場合は, csv ファイルの改行コードを変更する必要がある. Windows と Unix では改行コードの表し方が違う.

vm$ ruby gnuplot.rb

  /usr/lib/ruby/2.3.0/csv.rb:1883:in `block (2 levels) in shift': Illegal quoting in line 1. (CSV::MalformedCSVError)

nkf コマンドを使うと簡単に変換することができる.

vm$ nkf -Lu grafana_data_export.csv > temp.csv

課題

grafana のダッシュボードのスクリーンショットを提出せよ.
- 作成した全てのグラフが含まれるようにスナップショットをとること. スナップショットが複数枚にわたっても構わない.
gnuplot で温度・湿度・不快指数の 3 種類のグラフを作成し, その png ファイルを提出せよ.