はじめに

SORACOM Advent Calendar 2022 7 日目の記事です。SORACOM HarvestSQLite のお話です。

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 ではうまく動かないかもしれません。

  • SQLite 3.40.0
  • Rust 1.65.0 (stable-aarch64-apple-darwin)
  • macOS 12.6 (Monterey) on Apple M1 MAX

使い方

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 を便利に使いました。

  1. SORACOM の利用を始める にしたがって SIM カードをゲットし通信可能にします。バーチャル SIM も使用できます。SORACOM Arc の 公式ドキュメント を参照しセットアップしてください。
  2. SORACOM Harvest Data を有効化 します。
  3. 以下の権限を持つ SAM ユーザーを作成します。セキュリティの観点から専用のユーザーを作成することをおすすめしますが、すでに SORACOM CLI などを利用中で、必要な権限を持っているユーザーを使っている場合はその認証情報を使っても OK です。SAM は、管理ユーザー毎にアクセス権限を設定できるアクセス管理機能で、不要なアクセスや操作ミスを未然に防げます。
    {
      "statements": [
        {
          "api": [
            "Sim:getDataFromSim",
            "Subscriber:getDataFromSubscriber",
            "DataEntry:getDataEntries",
            "DataEntry:getDataEntry"
          ],
          "effect": "allow"
        }
      ]
    }
    
  4. 作成した SAM ユーザーの認証キーを生成し authKeyIdauthKey をメモっておきます。

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 にロードする

  1. SAM ユーザーの認証情報を環境変数としてエクスポートします。
    $ export LIBSHSQLITE_AUTH_KEY_ID=keyId-.. # authKeyId
    $ export LIBSHSQLITE_AUTH_KEY_SECRET=secret-... # authKey
    
  2. SQLite を起動します。
    $ sqlite3
    
  3. 拡張をロードします。Windows の場合は libshsqlite ではなくおそらく shsqlite と指定します。
    sqlite> .load target/release/libshsqlite
    
    Error: 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'
);

以下のパラメータをモジュールの引数として指定できます。

引数説明デフォルト値必須
IMSISIM カードの IMSIなしx
FROM取得するデータの開始時刻 (UNIX 時刻ミリ秒)現在から 1 日前
TO取得するデータの終了時刻 (UNIX 時刻ミリ秒)現在時刻
COVERAGESIM のカバレッジ (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 が使用できます。ちなみに INSERTUPDATE はできません。

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 CLISORACOM 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 リポジトリ、そして公式ドキュメントを参照しました。

まずは手元の環境にあわせて 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_client crate として外出ししています。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 を使えること、cargoclippy といったツールチェインを使えること、monorepo (ワークスペース) が標準でサポートされていること、テストの仕組みも用意されていることなど、全体を通してよい開発体験でした。

まとめ

SORACOM Harvest Data に蓄積されたデータを SQLite で取り扱える拡張を、最近(というかずっと)勉強中の Rust を使って開発しました。最近コードを書く機会がめっきり減っているのでとても楽しめました。SORACOM Advent Calendar 2022 をセットアップしてくださった SORACOM ユーザーグループ (SORACOM UG) のみなさまに感謝します。

2022 年 12 月現在、SORACOM Harvest Data は 1 アカウントあたり毎月 31 日分(1 日 2,000 書き込みリクエストまで)の無料利用枠があります。ぜひご活用ください。