先日、Workbenchを導入してGUIでMySQLを操作できるようにしました。またGrafanaもインストールしました。本日は、これら2つの環境が整っているのでGrafanaからMySQLに接続して株価チャートを表示します。
まずは株価データを楽天証券のマーケットスピードから取得してきます。CSV形式で取得できます。CSVファイルをエクセルで開き編集します。日付の項目で昇順に並び替え、日付、始値、高値、安値、終値以外の項目を削除して上書き保存します。このCSVファイルをWorkbenchで新規に作成したスキーマにインポートします。Table Data Import Wizardでインポートしますがフィールドタイプは初期値になっていたTEXTのままインポートしました。
ここで、スキーマ（schema）とは簡単に言うと、データベースの構造であり、データベースに含まれるものということです。ここら辺を曖昧に理解していたせいで後から苦労することになる。WorkbenchではAWSにあるRDSに接続済みです。
Grafanaでは設定にあるデータソースからMySQLを選択します。ホスト名、データベース名、およびログインするユーザー名とパスワードを入力します。
SAVE＆TESTボタンをクリックします。正しく入力していれば接続に成功します。エクスプローラでデータソースの情報が正しく表示できるか確認します。しかし、FROMの部分が選択できるだけで他にはなにもありません。FROMの部分はWorkbenchでスキーマを展開して表示されているtablesの中にある名前のことです。
さらにエラーもでています。
db query error: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘9984 ORDER BY time’ at line 4
そこでWorkbenchでDatatypeをTEXTから変更することにしました。日付をDATETIME、残りをINTにして更新しました。
データ型については次のページで調べてきました。
Grafanaで再度確認します。Workbenchで表示されているTablesの中にあるColmunsの内容は選択できるようになったがエラー内容が変わりグラフが表示できない状態です。
その後なんどか修正しましたがエラーはなくなりません。
db query error: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘9984 WHERE 日付 BETWEEN FROM_UNIXTIME(1640867471) AND FROM_UNIXTIME(16486434’ at line 4
初心に戻ることにした。
grafanaで時系列データを表示するためには、少なくともtime、metric、数値が必要ということです。この場合、timeは日付、数値は株価なのですがmetricがありません。metricがあると全く違うデータが含まれていても、metricごとに複数のグラフが描画できるみたいです。
なにで分類するか考えましたが、株価コードをmetricに指定することが最善だと思いました。csvファイルを編集します。記述していない株価コードを追加します。ここで一番重要なのがフィールド名の設定をWorkbenchで行うということです。よくわかりませんがCSVファイルの内容を正しく設定してインポートしてもエラーが出ました。また、株価の表示形式をみると通貨になっているので数値に変更します。これらに気が付くのに苦労しました。
下記のスクリプトをWorkbenchで実行します。
CREATE TABLE IF NOT EXISTS test.table9984 (
code VARCHAR(10),
日付 DATETIME,
始値 int(10),
高値 int(10),
安値 int(10),
終値 int(10),
PRIMARY KEY( code, 日付)
);あとはデータだけをTable Data Import Wizardを選択して書き込みます。この時、新しいテーブルを作成してインポートすることはしないで、すでに存在するテーブルにインポートします。CSVファイルのフィールド名はあってもなくてもWorkbenchがうまく処理をしてくれました。
この作業でようやくgrafanaにグラフを表示することができました。テーブルにあるデータはMySQLの時間と9時間ずれていますが日足なのでここでは気にしません。時間軸が短い場合はずれると困ります。UNIX_TIMESTAMP（日付）とすれば意図した通り表示されるようです。
エクスプローラで動作確認ができたので、新しいパネルにチャートを表示します。
データソースが最初はPrometheusになっているのでMySQLに変更し、必要な項目を選択します。
データが日足なので時間軸が初期値の６hoursではグラフが表示されませんので、もっと長いものに設定します。グラフの種類は画面右から「candlestick」に変更するとローソク足が表示されます。
次はデータに他のコードが混在している状態で確認します。日経平均のデータを追加してcodeは先のデータとは違うもので統一します。さらに日付が昇順になっていないデータです。
日付が昇順になっていなくてもGrafanaで並び替えてくれているようです。今度のデータは同じ日付があり、それぞれに値があるのでグラフが変ですが、メトリックの記述を追加すると分類されて意図したグラフが表示されます。
code as metric,
記述した内容
SELECT
日付 AS "time",
code as metric,
始値,
高値,
安値,
終値
FROM table9984
WHERE
$__timeFilter(日付)
ORDER BY 日付
