A SQLite extension which loads data from Soracom Harvest Data as a virtual table.
- SQLite 3.40.0
- Rust 1.65.0 (stable-aarch64-apple-darwin)
- macOS 12.6 (Monterey) on Apple M1 MAX
- SORACOM Harvest (English/Japanese)
$ git clone github.com/0x6b/libshsqlite
$ cd libshsqlite
- Sign-up SORACOM to get a (virtual) SIM (English/Japanese)
- Enable Soracom Harvest Data (English/Japanese)
- Create a SAM user with following permission:
{ "statements": [ { "api": [ "Sim:getDataFromSim", "Subscriber:getDataFromSubscriber", "DataEntry:getDataEntries", "DataEntry:getDataEntry" ], "effect": "allow" } ] }
- Generate and authentication information for the user and save it for future reference
See documentation (English/Japanese) for detail, or use deadly simple client in this repository (soracom_harvest_client
), from your SIM or virtual SIM connected machine as follows:
$ cargo run -p soracom_harvest_client -- --udp hey # Say hello, via UDP
$ cargo build --release
- Export required environment variables with the credential:
$ export LIBSHSQLITE_AUTH_KEY_ID=keyId-.. # authKeyId $ export LIBSHSQLITE_AUTH_KEY_SECRET=secret-... # authKey
- Launch SQLite:
$ sqlite3
- Load the extension (you have to use "shsqlite" on Windows):
If you get
.load target/release/libshsqlite
Error: unknown command or invalid arguments: "load". Enter ".help" for help
, your SQLite is not capable for loading an extension. For macOS, install it withbrew install sqlite3
, and use it.
- Create a virtual table for your Soracom Harvest Data by providing
IMSI
(IMSI of target SIM) as module argument. See reference below for available arguments other thanIMSI
:CREATE VIRTUAL TABLE harvest_data USING shsqlite(IMSI 'imsi-of-your-sim', COVERAGE 'japan');
- Run queries as usual:
SELECT * FROM harvest_data; SELECT * FROM harvest_data WHERE value ->>'$.temperature' > 10;
Argument | Description | Default | Required |
---|---|---|---|
IMSI |
Your IMSI | None | x |
FROM |
Start time for the data entries search range (unix time in milliseconds). | 1 days ago from now | |
TO |
End time for the data entries search range (unix time in milliseconds). | now | |
COVERAGE |
Your SIM's coverage (global or japan ) |
global |
|
LIMIT |
Maximum number of data entries to retrieve. Should be between 1 and 1000. | 100 |
CREATE VIRTUAL TABLE harvest_data USING shsqlite(
IMSI '...',
FROM '...',
TO '...',
COVERAGE 'japan|global',
LIMIT '...'
);
Please read CONTRIBUTING for more detail.
An article, Extending SQLite with Rust to support Excel files as virtual tables | Sergey Khabibullin , and its companion repository x2bool/xlite, for great write up and inspiration.
- The extension will load the data only once while creating a virtual table. If you want to pick up recent data, drop the table and create it again. Dropping the table won't erase your data on Soracom Harvest.
INSERT
,UPDATE
andDELETE
statements won't be implemented.
The extension never send your data to any server.
This extension is released under the MIT License. See LICENSE for details.