センサの値をRDBMSに記録する
第1回：Webでデバイスを操作してみよう
第2回：Webサービスでデバイスを操作する
loTという言葉が一般的なものとなって久しい昨今、「loT」関連の記事も多くインターネット上に流れていると思います。そんな「loT」を独自の切り口で楽しく紹介してくれる、うすだひさしさんの連載も今回で3回目。今回はセンサの値をRDBMSに記録する方法を見ていきたいと思います。
目次
1. はじめに
「ラズパイとセンサで楽しむお手軽IoT」と称して、ラズパイにつないだデバイスを外部から操作したり、センサの情報を外部から読み取ったりする連載の第3回目です。
第1回と第2回では、WebやGoogleアシスタントからラズパイにつないだLEDを操作する方法をご紹介しました。ですが、IoTでは、モノを操作するだけでなく、モノから情報を得て活用することも重要です。そこで今回は、センサからデータを読み取り、データベースに記録する方法を紹介します。
センサには、本サイトではおなじみの「センサメダル(SensorMedal-EVK-002)」を使います。Bluetoothを使って、部屋に置いてあるセンサメダルから温度や湿度などのデータを定期的に読み取り、RDBMS(Relational DataBase Management System、リレーショナルデータベース管理システム)である「PostgreSQL」にデータを記録します。
2. 準備
今回必要となるものは、下記です。センサメダルがなくても(たとえばロードアベレージやプロセス数といったシステムの状態など)何かしらデータを読み取れるものがあれば、同等のことがおこなえます。
- Raspberry Pi OSがインストールされたラズパイ本体
- センサメダル(SensorMedal-EVK-002)
3. センサメダルからデータを読み取る
ロームのセンサメダル(SensorMedal-EVK-002)は、温湿度や加速度など6種類のセンサを搭載した評価キットです。Bluetoothに対応し、コイン電池で数ヶ月動作し続けます。iOSアプリが公開されていて、アプリを使ってデータを読み取ることができますが、ラズパイでも可能です。
本サイトでも、「ラズパイとセンサで作る小型健康管理ウェアラブルデバイス！」や「ラズパイとセンサメダルで作るIoT縄跳びデバイス」などで、センサメダルを活用しています。センサメダルをラズパイで使う方法自体は、これらと同等です。
ただ、今回は、身につけるのではなく、部屋に設置して使います。そのため、読み取るデータは、温度、湿度、気圧、照度、RSSI(Received Signal Strength Indicator、受信信号の強度)およびバッテリ残量としています(もちろん、加速度や地磁気、歩数も読み取れます。必要に応じて追加してください)。
データの読み取りやデータベースへの記録をおこなう処理は、Pythonスクリプトでします。そこでまず、Bluetoothの通信をおこなうためのPythonパッケージ「bluepy」をインストールします（下記の「$」はシェルのプロンプトです。また、もし「pip3」コマンドがなければ、「sudo apt install python3-pip」を実行してインストールしてください）。
$ sudo pip3 install bluepy
次に、本サイトの他の連載と同様、Githubで公開されている「BLE Logger for Rohm SensorMedal-EVK-002」を拝借して、動作確認をおこないます。下記のように「git」コマンドを実行して、各種Pythonスクリプトを取得します（もし「git」コマンドがなければ、「sudo apt install git」を実行してください）。
$ git clone https://github.com/bokunimowakaru/SensorMedal2.git $ cd SensorMedal2/
データの表示をおこなうPythonスクリプト「bleloggerSensorMedal2.py」を管理者権限で実行し、「Temperature(温度)」や「Humidity(湿度)」などが出力されることを確認します。
$ sudo ./ble_logger_SensorMedal2.py (中略) Temperature = 18.13 ℃ Humidity = 64.9 % Pressure = 1012.828 hPa Illuminance = 180.0 lx Accelerometer = 1.037 g ( 0.064 0.161 1.022 g) Geomagnetic = 103.8 uT ( 6.8 -35.3 -97.4 uT) Magnetic = 0x3 Steps = 0 歩 Battery Level = 100 % RSSI = -51 dB (後略)
センサメダルのサイトからダウンロードできるマニュアルにデータフォーマットの記載があります。実行したPythonスクリプトでは、payval関数で16進数の文字を数値に変換した後、記載されている式の通りに数値を変換して、各値を算出しています。
4. RDBMSの設定をおこなう
データの記録や利用に使うRDBMSには、「PostgreSQL」を使います。Raspberry Pi OSにはパッケージがあり、下記のようにインストールすればすぐ使えます。
$ sudo apt install postgresql
インストールすると、PostgreSQLのサービスが自動的に起動されます。ただ、データベースを管理するサービスが動作しているだけの状態です。実際に使えるようにするには、（OSではなくPostgreSQLの）ユーザとデータベース、そしてデータを格納するテーブルを作成する必要があります。
PostgreSQLのユーザを作成するには、「createuser」コマンドを実行します。PostgreSQLは「postgres」というユーザの権限で動作しているため、下記のようにpostgresの権限でコマンドを実行します。
$ sudo -u postgres createuser -P -s pi 新しいロールのためのパスワード: (パスワードを入力) もう一度入力してください： (パスワードを再入力)
上記では、「pi」ユーザを作成しています。「-P」オプションでパスワードの設定をおこないます。2回聞かれるので、設定したいパスワードを入力します。また、「-s」オプションで管理者権限を付与しています。
次に、「createdb」コマンドでデータベースを作成します。前述で作成した通り、piユーザはPostgreSQLの管理者権限を持っているため、そのまま実行できます。引数なしで実行すると、ユーザ名と同じ名前のデータベースを作成します（PostgreSQLのコマンドをローカルで実行する場合、OSのユーザ名とPostgreSQLのユーザ名が同じなら、パスワードの入力は不要です（pg_hba.confで設定されています）。他のコマンドも同様です)。
$ createdb
PostgreSQLの操作を行う対話形式のコマンドが「psql」です。引数なしで実行すると、ユーザ名と同じ名前のデータベースを対象として動作します。
$ psql pi=#
「pi=#」がプロンプトです。データベースへの操作や定義をおこなう言語「SQL」のコマンドを入力したり、「\」で始まるpsqlのコマンドを入力できます。ここでは、センサメダルのデータを記録するための、下記に示すテーブル「sensormedaldata」を作成したいと思います（PostgreSQLのデータ型の詳細については、「PostgreSQL文書の8.データ型」を参照してください）。
| 列名 | データ型 | 概要 |
| temperature | double precision | 温度(℃) |
| humidity | double precision | 湿度(%) |
| pressure | double precision | 気圧(hPa) |
| illuminance | double precision | 照度(lx) |
| rssi | double precision | RSSI(dB) |
| battery | double precision | バッテリ残量(%) |
| date | timestamp with time zone | 日時 |
下記のように「CREATE TABLE」というコマンドを入力してテーブルを作成します。
pi=# CREATE TABLE sensormedaldata (temperature double precision, humidity double precision, pressure double precision, illuminance double precision, rssi double precision, battery double precision, date timestamp with time zone); CREATE TABLE
テーブルの一覧を出力するコマンド「\dt」を実行して、テーブルが作成されたことを確認します。
pi=# \dt リレーションの一覧 スキーマ | 名前 | 型 | 所有者 ----------+-----------------+----------+-------- public | sensormedaldata | テーブル | pi (1 行)
問題なければ、「\q」を入力してpsqlコマンドを終了します。
pi=# \q $
なお、psqlコマンドでは、「\h」と入力するとSQLのコマンド、「\?」と入力するとpsqlのコマンドのヘルプを出力してくれます。何か困ったら実行してみてください。
5. センサデータをRDBMSに記録する
作成したテーブルに、センサメダルのデータを記録します。まず、PythonからPostgreSQLへアクセスするため、「psycopg2」というPythonパッケージをインストールします。
$ sudo pip3 install psycopg2
前述の「bleloggerSensorMedal2.py」を参考に、センサメダルからデータを読み取ってPostgreSQLのテーブルへ記録するPythonスクリプトを作りました。下記をテキストエディタで入力(コピペ)し、「/home/pi/sm2pg.py」というファイル名で保存してください（「パスワード」には実際のパスワードを設定してください）。
#!/usr/bin/env python3
# based on: https://github.com/bokunimowakaru/SensorMedal2
from bluepy import btle
import psycopg2
import datetime
timeout = 5
def payval(val, num, bytes=1, sign=False):
a = 0
for i in range(0, bytes):
a += (256 ** i) * int(val[(num - 2 + i) * 2 : (num - 1 + i) * 2], 16)
if sign:
if a >= 2 ** (bytes * 8 - 1):
a -= 2 ** (bytes * 8)
return a
def insertdb(t, h, p, i, r, b):
try:
conn = psycopg2.connect(host='localhost', dbname='pi', user='pi', password='パスワード')
except Exception as e:
print('Error: ', e)
return
sql = 'INSERT INTO sensormedaldata VALUES (%s,%s,%s,%s,%s,%s,%s)'
try:
with conn.cursor() as cur:
cur.execute(sql, (t, h, p, i, r, b, 'NOW'))
conn.commit()
except Exception as e:
print('Error: ', e)
conn.close()
if __name__ == '__main__':
print('Start: ', datetime.datetime.now())
scanner = btle.Scanner()
try:
devices = scanner.scan(timeout)
except Exception as e:
print('Error: ', e)
exit(2)
for dev in devices:
isRohmMedal = False
for (adtype, desc, val) in dev.getScanData():
if desc == 'Short Local Name' and val[0:10] == 'ROHMMedal2':
isRohmMedal = True
if isRohmMedal and desc == 'Manufacturer':
temperature = -45 + 175 * payval(val, 4, 2) / 65536
humidity = 100 * payval(val, 6, 2) / 65536
pressure = payval(val, 22, 3) / 2048
illuminance = payval(val, 25, 2) / 1.2
rssi = dev.rssi
battery = payval(val, 30)
print('%f,%f,%f,%f,%f,%f' % (temperature, humidity, pressure, illuminance, rssi, battery))
insertdb(temperature, humidity, pressure, illuminance, rssi, battery)
print('End: ', datetime.datetime.now())
そして、「chmod」コマンドで実行権を付与します。
$ chmod +x ~/sm2pg.py
それでは実際に動かしてみましょう。
下記のように、「Start」と「End」が日時とともに出力され、それらの間にカンマ区切りのデータが出力されていれば、動作しています。念のため5秒待つようにしていますが、まれにデータを読み取れないことがあります。StartとEndしか出力されなかったときは、再度実行してみてください。
$ sudo ~/sm2pg.py Start: 2021-03-21 10:07:05.526701 20.114975,75.114441,998.004395,126.666667,-69.000000,90.000000 End: 2021-03-21 10:07:10.654526 $
psqlコマンドで、テーブルに記録されていることを確認します。下記のように「-c」オプションとコマンドを指定して実行すると、対話形式ではなくコマンドを実行します。
$ psql -c 'SELECT * FROM sensormedaldata' temperature | humidity | pressure | illuminance | rssi | battery | date ------------------+------------------+------------------+------------------+------+---------+------------------------------- 20.1149749755859 | 75.1144409179688 | 998.00439453125 | 126.666666666667 | -69 | 90 | 2021-03-21 10:07:10.63709+09 (1行)
Pythonスクリプト(sm2pg.py)を実行する度に、データが1行ずつテーブルへ追加されます。
6. センサデータを定期的に記録する
最後に、Unix系OSで定期的に処理を行うサービス「cron」を使って、センサメダルのデータを定期的に記録しましょう。
通常は「crontab」コマンドで設定や確認をおこないますが、管理者権限で実行する必要があるため、今回は「/etc/crontab」ファイルに直接設定します。
/etc/crontabでは、1行に1つの設定を記述します。形式は下記の通りです。
分 時 日 月 曜日 ユーザ コマンド...
最初の5つのフィールドで、実行したい日時を指定します。たとえば、「0 * * * *」と指定した場合、1時間毎に(毎時0分に)実行されます。「30 12 * * 0」と指定すると、毎週日曜日の12時半に実行されます。
「ユーザ」には、実行する際のユーザを指定します。「コマンド…」には、実行したいコマンドとその引数を指定します。今回は、5分おきにPythonスクリプトを実行しようと思います。/etc/crontabの末尾に下記の1行を追記します（管理者権限でテキストエディタを起動し、/etc/crontabを開いて下記を追記します）。
*/5 * * * root /home/pi/sm2pg.py >> /home/pi/log_sm2pg.txt 2>&1
追記したら、少し（5分以上）待って、テーブルにデータが記録されていることを確認します。何か問題がある場合は、スクリプトの出力を「/home/pi/log_sm2pg.txt」に追記して残すようにしていますので、このファイルを確認してください。
ただ、定期的に実行していると、「Error: Failed to execute management command ‘scanend’」というエラーが発生することがあります。bluepyのissueによると、「hciconfig hci0 down」と「hciconfig hci0 up」を実行すれば直るようです。そのため、上記の代わりに下記を/etc/crontabに設定すると、エラーが発生しても自動で復帰します。上記のhciconfigを実行しないと、ずっとエラーが出続けます。
*/5 * * * root /home/pi/sm2pg.py >> /home/pi/log_sm2pg.txt 2>&1 || (hciconfig hci0 down >> /home/pi/log_sm2pg.txt 2>&1 && hciconfig hci0 up >> /home/pi/log_sm2pg.txt 2>&1 && /home/pi/SensorMedal2/sm2pg.py >> /home/pi/log_sm2pg.txt 2>&1)
7. まとめ
以上、センサメダルのデータを定期的に読み取り、PostgreSQLに記録する方法を紹介しました。
なお、大事なデータは、「pg_dump」コマンドで定期的にバックアップしておくことをオススメします。下記に例を示します。
$ pg_dump -t sensormedaldata | gzip -9 > sensormedaldata-$(date +%Y%m%d%H%M).txt.gz
また、別のマシンのPostgreSQLに記録したり、PostgreSQLのレプリケーション機能を使って別のマシンに複製しておくと、さらに安心です。
次回は、記録したデータの可視化など、活用する方法を紹介したいと思います。お楽しみに！
今回の連載の流れ
第1回：Webでデバイスを操作してみよう
第2回：Webサービスでデバイスを操作する
第3回：センサの値をRDBMSに記録する（今回）
第4回：RDBMSの情報をWebで可視化する
