Zenn
🗂

SQLiteとGrafanaでチャート表示

2024/10/13に公開
Raspberry Pi
sqlite3
Grafana
tech

Yahoo Financeで取得した銘柄の終値をSQLiteに保存した時系列データを格納を可視化するためGrafanaで折れ線グラフを表示させることにしますた。
SQLiteとGrafanaはRaspberry Pi ZERO2上で実装しました。

SQLite3インストール

パッケージを利用

sudo apt update
sudo apt install sqlite3

時系列テーブル

CREATE TABLE IF NOT EXISTS "hist_stock" (
"datetime"	TEXT,　// 日付
"symbol"	TEXT, // 銘柄コード
"close"	REAL, // 終値
PRIMARY KEY("datetime","symbol")
);

Grafanaインストール

公式サイト手順通りインストールしました。
Install Grafana on Raspberry Pi

sudo mkdir -p /etc/apt/keyrings/
wget -q -O - https://apt.grafana.com/gpg.key | gpg --dearmor | sudo tee /etc/apt/keyrings/grafana.gpg > /dev/null

echo "deb [signed-by=/etc/apt/keyrings/grafana.gpg] https://apt.grafana.com stable main" | sudo tee /etc/apt/sources.list.d/grafana.list

sudo apt-get update
sudo apt-get install -y grafana

sudo /bin/systemctl enable grafana-server
sudo /bin/systemctl start grafana-server

日付データ

SQLite3にはdate型がなく終値は日毎でよいため日付カラムは文字列型で"yyyy-mm-dd"形式で日付を格納したけれど上手くチャートが描画されずyyyy-mm-ddT00:00:00+09:00とすることで表示されました。Grafanaの日時はデフォルトUTCらしいので時差も設定しました。

データの取得＆格納

Yahoo financeからデータ取得しSQLite格納する手段として今回はRustでツールを作成しましたがここでは割愛します。

ログイン

デフォルトはhttp://<ip address>:3000にアクセスしID：admin、Pass：adminでログイン

SQLiteプラグインインストール

デフォルトではデータソースにSQLiteがないためプラグインからSQLiteを追加しました。
左上のメニューアイコン→Administration→Plugins and data→Plugins→SQLiteを選択し、右上のインストールアイコンをクリックしてインストール

Data sources登録

SQLiteプラグインを使用してデータソースを追加します。データソース設定でDBファイルを設定します。
/varや/homeにはデフォルトアクセスできないようです。QA

チャート作成

左上のメニューアイコン→Dashboards→右上のNewアイコンクリック→New dashboard
画面真ん中の+ Add visualizationをクリック
チャートの種類はTime seriesを選択
Data sourcesで登録したデータソースを選択
Query AにSQLを設定

SELECT datetime,symbol,close from hist_stock
order by datetime

FORMAT asはTime seriasを指定

日付のカラム名が今回のようにtimeもしくはtsではない場合はTime formatted columnsで明示的にカラム名を指定しました。

チャートを作成したらApplyだけではなくSAVEアイコンをクリックしないとチャートとダッシュボードが保存されず作成し直すことがありました。

概ね完成したチャート

その他設定メモ

  • 表示間隔によって自動的に表示間隔は日単位になる。※Query optionsで明示的に固定することも可能
  • 値を取得できていない日はGraph styleのconnect null valuesで"Always"を選択することで前のデータポイントと直線で補間される
  • 数値の差が大きい時系列データを同時に表示させる場合、AxisのScaleで"Symlog"を選択するとY軸程よい値にを表示してくれる
  • Y軸の単位はStandard optionsで設定、リストボックスから選択することも単位を明示的に入力することも可能
  • 2軸で単位の異なるグラフを同時に表示させる場合、OverridesからFields with namaを選んで対象の値を選び、それに適用させるスタイル（今回ならDollars($)）を選択する
  • 固定値のラインを表示させる場合ThresholdsでAdd thresholdで追加に任意の固定値を追加し、- Show thresholdsでAs linesを選択、色を指定する場合は値の脇に表示している色の●をクリックすることで色指定が可能

Discussion