SORACOM Harvest Data 用の SQLite 拡張を作りました
「一般消費者が事業者の表示であることを判別することが困難である表示」の運用基準に基づく開示: この記事は記載の日付時点で株式会社ソラコムのエンジニアリングチームに所属する社員が執筆しました。ただし、個人としての投稿であり、株式会社ソラコムとしての正式な発言や見解ではありません。
はじめに
SORACOM Advent Calendar 2022 7 日目の記事です。SORACOM Harvest と SQLite のお話です。
SORACOM Harvest とは
SORACOM Harvest はサーバーやストレージ、アプリケーションを構築することなく SORACOM Air を使っている IoT デバイスからのセンサー情報や位置情報など任意の情報を簡単に SORACOM プラットフォームに蓄積し可視化できるサービスです。IoT プロジェクト開始の早い段階で、まずはデバイスからどんなデータが送られているかを確認する際に簡単に利用できます。
以下の 2 つの機能を提供しています。今回はひとつめの SORACOM Harvest Data ネタです。
- SORACOM Harvest Data: テキスト、JSON、バイナリなどデータを扱います。データのフォーマットに制限はありません。
- SORACOM Harvest Files: 画像やログなどのファイルを扱います。
SQLite とは
SQLite とは C で実装された組み込み型のデータベースエンジンです。簡潔な説明ですので 公式サイト から引用します。
SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is the most used database engine in the world. SQLite is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day. More Information...
ふだん意識する機会は少ないかもしれませんが、みなさんがお使いの Android や iPhone、そして主要ブラウザ (Firefox, Chrome, Safari) に搭載されており、世界でもっとも使用されているデータベースエンジンと言われています。
SORACOM Harvest Data と SQLite
SQLite は実行時に機能を追加する Run-Time Loadable Extensions というメカニズムがあります。SQL 関数を追加したり、照合順序 (collating sequence) を定義したり、仮想テーブルを実装したりといろいろできます。公式サイトの Contributed Files セクションでは SQL に数学 (acos, asin, atan など) や文字列操作 (replicate, charindex, lftstr など) 関係の関数を追加する拡張 extension-functions.c が紹介されています。
今回はこのメカニズムを利用して SORACOM Harvest Data に蓄積されているデータを SQLite の 仮想テーブル として取り扱える拡張を作ってみました。蓄積されたデータをクイックに手元で確認する際に便利に使えると思います。
0x6b/libshsqlite: A SQLite extension which loads data from Soracom Harvest Data as a virtual table.
以下の環境で開発しました。プラットフォームに依存しないはずですが、他の OS ではうまく動かないかもしれません。
使い方
Rust をインストールする
拡張本体 (.so や .dll ファイル) を GitHub から配布する準備がまにあわなかったため、お手元でビルドが必要です。そのため、だいぶ面倒ですが Rust のインストールから。Rust をインストール - Rust プログラミング言語 にしたがってください。
ソースコードを入手する
拡張のソースコードを GitHub リポジトリから入手します。
$ git clone github.com/0x6b/libshsqlite
$ cd libshsqlite
SORACOM と Soracom Harvest Data を使い始める
SORACOM Air for セルラーの SIM あるいは SORACOM Arc によるバーチャル SIM/Subscriber を用意します。私は開発・テスト時に Arc を便利に使いました。
- SORACOM の利用を始める にしたがって SIM カードをゲットし通信可能にします。バーチャル SIM も使用できます。SORACOM Arc の 公式ドキュメント を参照しセットアップしてください。
- SORACOM Harvest Data を有効化 します。
- 以下の権限を持つ SAM ユーザーを作成します。セキュリティの観点から専用のユーザーを作成することをおすすめしますが、すでに SORACOM CLI などを利用中で、必要な権限を持っているユーザーを使っている場合はその認証情報を使っても OK です。SAM は、管理ユーザー毎にアクセス権限を設定できるアクセス管理機能で、不要なアクセスや操作ミスを未然に防げます。
{ "statements": [ { "api": [ "Sim:getDataFromSim", "Subscriber:getDataFromSubscriber", "DataEntry:getDataEntries", "DataEntry:getDataEntry" ], "effect": "allow" } ] } - 作成した SAM ユーザーの認証キーを生成し
authKeyIdとauthKeyをメモっておきます。
SORACOM プラットフォームへデータを送信する
Harvest Data でデバイスのデータをクラウドで収集・取得・可視化する を参考にしながら適当なデータを送信します。プロトコルは HTTP、TCP、UDP いずれでも構いません。
同じリポジトリに拡張のテストのために UDP と HTTP でデータを送信する soracom_harvest_client crate を作っており、簡易な CLI もありますのでそちらでも大丈夫です。
UDP で送信する
$ cargo run -p soracom_harvest_client --quiet -- --udp hey
2022-12-03T15:06:18.724035+09:00 hey
HTTP で送信する
$ cargo run -p soracom_harvest_client --quiet -- --http '{"temperature":20}'
2022-12-04T01:17:10.924355+09:00 {"temperature":20}
拡張をビルドする
cargo コマンドでビルドします。
$ cargo build --release
Compiling autocfg v1.1.0
Compiling libc v0.2.137
Compiling cfg-if v1.0.0
...
Compiling soracom_harvest_client v0.1.0 (/.../libshsqlite/soracom_harvest_client)
Compiling soracom_harvest_api_client v0.1.0 (/.../libshsqlite/soracom_harvest_api_client)
Compiling soracom_harvest_sqlite_extension v0.1.0 (/.../libshsqlite/soracom_harvest_sqlite_extension)
Finished release [optimized] target(s) in 22.68s
拡張を SQLite にロードする
- SAM ユーザーの認証情報を環境変数としてエクスポートします。
$ export LIBSHSQLITE_AUTH_KEY_ID=keyId-.. # authKeyId $ export LIBSHSQLITE_AUTH_KEY_SECRET=secret-... # authKey - SQLite を起動します。
$ sqlite3 - 拡張をロードします。Windows の場合は
libshsqliteではなくおそらくshsqliteと指定します。sqlite> .load target/release/libshsqliteError: unknown command or invalid arguments: "load". Enter ".help" for helpというエラーが出てしまったら、お使いの SQLite が拡張のロードをサポートしていません。macOS の場合はbrew install sqlite3などでよしなにインストールしてそちらを使用してください。
SORACOM Harvest Data のデータから仮想テーブルを作成する
CREATE VIRTUAL TABLE 文を使って SORACOM Harvest Data のデータを元に仮想テーブルを作成します。データはこのタイミングでのみ取得しますので、仮想テーブル作成後に送信されたデータが必要な場合は作り直してください。仮想テーブルを DROP しても SORACOM Harvest Data に蓄積されているデータは削除されません。
CREATE VIRTUAL TABLE harvest_data USING shsqlite(
IMSI 'imsi-of-your-sim',
COVERAGE 'japan'
);
以下のパラメータをモジュールの引数として指定できます。
| 引数 | 説明 | デフォルト値 | 必須 |
|---|---|---|---|
IMSI | SIM カードの IMSI | なし | x |
FROM | 取得するデータの開始時刻 (UNIX 時刻ミリ秒) | 現在から 1 日前 | |
TO | 取得するデータの終了時刻 (UNIX 時刻ミリ秒) | 現在時刻 | |
COVERAGE | SIM のカバレッジ (global または japan) | global | |
LIMIT | 取得するデータの数。1 から 1000 まで。 | 100 |
CREATE VIRTUAL TABLE harvest_data USING shsqlite(
IMSI '...',
FROM '...',
TO '...',
COVERAGE 'japan|global',
LIMIT '...'
);
SORACOM Harvest Data に蓄積されているデータをクエリする
ここまでくるといつものように SQL が使用できます。ちなみに INSERT や UPDATE はできません。
SELECT * FROM harvest_data;
-- time content_type value
-- ------------- ---------------- ------------------
-- 1670084230984 application/json {"temperature":20}
-- 1670083937548 application/json {"value":"hey"}
SORACOM Harvest Data のタイムスタンプは UNIX 時刻ミリ秒ですので datetime 関数で変換できます。
SELECT datetime(time/1000, "unixepoch") AS datetime, value FROM harvest_data;
-- datetime value
-- ------------------- ------------------
-- 2022-12-03 16:17:10 {"temperature":20}
-- 2022-12-03 16:12:17 {"value":"hey"}
UDP で送信した文字列 hey が {"value":"hey"} となっている部分はポイントです。公式ドキュメント に記載のとおり、TCP または UDP で Harvest Data にデータを送信すると、データが Base64 形式でエンコードされた上で payload プロパティに設定された JSON 形式で蓄積されます。hey という文字列を UDP で送信すると、SORACOM CLI や SORACOM API では以下のように取得できます。
[
{
"content": "{\"payload\":\"aGV5\"}",
"contentType": "application/json",
"time": 1670047578752
}
]
SORACOM ユーザーコンソールには、上記のデータ(content 部分)を「一次処理済みデータ」として自動的にデコードして表示してくれる気の利いた機能があります。今回作った拡張も同様の変換を実装しました。
UDP でデータを送信すると、
$ cargo run -p soracom_harvest_client --quiet -- --udp hey
2022-12-03T15:11:15.373075+09:00 hey
SORACOM プラットフォームには以下のような形で蓄積され、
{ "payload": "aGV5" }
SORACOM ユーザーコンソールでは 一次処理済みデータ として以下のように表示されます。
{ "value": "hey" }
同様に SQLite でも以下のように変換されて表示されます。
SELECT * FROM harvest_data;
-- time content_type value
-- ------------- ---------------- ---------------
-- 1670083937548 application/json {"value":"hey"}
value 列に {"value":"..."} とあるのは冗長で少し気になりますが、整合性を考えてすべて JSON 形式にしました。SQLite は JSON 関数がありますので、以下のように値だけ取り出せます。
SELECT datetime(time/1000, 'unixepoch') as datetime,
value->>'$.value' AS value
FROM harvest_data;
-- datetime value
-- ------------------- -----
-- 2022-12-03 16:12:17 hey
JSON 関数は WHERE 句にも使えて便利ですね。
SELECT datetime(time/1000, "unixepoch") AS datetime,
value->>'$.temperature' AS temperature
FROM harvest_data
WHERE temperature > 10;
-- datetime temperature
-- ------------------- -----------
-- 2022-12-03 16:17:10 20
SORACOM CLI と jq コマンドを組み合わせ、base64 をデコードしつつ CSV に変換する場合は以下のような感じでしょうか。もう少しうまく書けそうですが jq 難しいです。
$ soracom subscribers get-data --imsi xxxxxxxxxxxxxxx | jq -r '.[]
| {
time: .time,
contentType: .contentType,
value: .content | fromjson | .payload | @base64d
}
| [.time, .contentType, .value]
| @csv'
1670048590499,"application/json","hey"
実装の話を少し
SQLite 拡張の作り方
Rust による SQLite 拡張の作り方は分かりやすい以下のブログ記事および関連する GitHub リポジトリ、そして公式ドキュメントを参照しました。
- Extending SQLite with Rust to support Excel files as virtual tables | Sergey Khabibullin - blog
- x2bool/xlite: SQLite extension for querying Excel (.xlsx, .xls, .ods) files as virtual tables
- Run-Time Loadable Extensions
まずは手元の環境にあわせて rust-bindgen を使用して SQLite 拡張の FFI バインディングを生成しました。SQLite のバージョンに依存しない(後方互換性がある)はずですので上記リポジトリのバインディングをそのまま使用しても動くと思いましたが念のため。
$ bindgen --default-macro-constant-type signed sqlite3ext.h -o sqlite3ext.rs
あとは既存の実装を参考に拡張の動作に必要な関数を実装していくだけです。ポイントは以下のとおりです。
shsqlite_create: 仮想テーブルを作成する際に呼ばれます。CREATE VIRTUAL TABLE文に渡した IMSI やカバレッジなどの引数をパースし、SORACOM プラットフォームから Harvest Data に蓄積されたデータを取得し、仮想テーブルとして宣言します。データの取得はsoracom_harvest_api_clientcrate として外出ししています。API 呼び出しの認証、データの取得、データの削除(テストに使用)のみを実装したシンプルな API クライアントです。shsqlite_open: 仮想カーソル(内部で保持しているデータにアクセスするカーソル)を初期化します。shsqlite_next: 仮想カーソルをひとつ進めます。shsqlite_column: 各列の値を返します。std::vec::Vecに保存したデータを返しているだけです。shsqlite_rowid: 各列のインデックスを返します。
Rust のよかったところ
Option 型 (Rust における std::option::Option) とパターンマッチをまともに使ったのは Rust が初めてだったのですが、エラーを含むすべてのパターンをコンパイラに矯正・強制されつつ自然な形でカバーできるので実装していて安心感がありました (これまでは JavaScript/TypeScript か Go が多かった)。TCP または UDP で送信されたデータをデコードする処理の実装のあたりです。
fn try_decode(content: String) -> String {
// API からゲットしたデータの content プロパティが {"payload": "value"} の形になっている場合、
// それはきっと base64 でエンコードされたデータのはず
if let Ok(base64_encoded_payload) =
serde_json::from_str::<Base64EncodedPayload>(content.as_str())
{
// payload プロパティの中身が base64 でデコードできて、
if let Ok(decoded) = base64::decode(base64_encoded_payload.payload) {
// かつ、UTF-8 としてデコードできて、
if let Ok(str) = String::from_utf8(decoded) {
// かつ、ASCII で表示可能な文字のみであった場合は、
if str.chars().all(|c| matches!(c as u8, 0x20..=0x7E)) {
// {"value": "デコードした文字列"} という形で返却する
return format!(r#"{{"value":"{str}"}}"#);
}
}
}
}
// 上記の条件のどれにも引っかからなかった場合は、元の content をそのまま返す
content
}
また、Arc<Mutex<...>> によって参照を共有しつつ値を変更するというパターンも、シグネチャは見づらいものの慣れてくると快適でした。
crates.io を使えること、cargo、clippy といったツールチェインを使えること、monorepo (ワークスペース) が標準でサポートされていること、テストの仕組みも用意されていることなど、全体を通してよい開発体験でした。
まとめ
SORACOM Harvest Data に蓄積されたデータを SQLite で取り扱える拡張を、最近(というかずっと)勉強中の Rust を使って開発しました。最近コードを書く機会がめっきり減っているのでとても楽しめました。SORACOM Advent Calendar 2022 をセットアップしてくださった SORACOM ユーザーグループ (SORACOM UG) のみなさまに感謝します。
2022 年 12 月現在、SORACOM Harvest Data は 1 アカウントあたり毎月 31 日分(1 日 2,000 書き込みリクエストまで)の無料利用枠があります。ぜひご活用ください。