とある案件で、MySQLのデータを手っ取り早く可視化して関係者にお披露目する機会があった。
Google Data Studio ではそれが簡単にできるので、手順を紹介する。
Google Data Studio について
BIツール（Business Intelligence ツール）のひとつ。
BIツールといえば、Tableau（タブローと読む。Salesforceアナリティクス製品との統合を加速中）が老舗で、名前だけは良く聞くがライセンス費用は高め。
マイクロソフトの PowerBI はある程度まで無料で試すことができ、それなりに情報量も豊富だが、今回は Google Analytics で使用実績があった Google Data Studio を採用した。無料で使える。
接続できるデータソースは、
- Googleが展開するサービス（Google Analytics、スプレッドシート、Cloud Storage、Search Console等）
- BigQuery
- MySQL
- PostgreSQL
- その他アップロードファイル
など。
新型コロナウイルス感染症の陽性者数などをAIで予測し、リアルタイムで表示するサイト COVID-19 感染予測 も Data Studio のダッシュボードを利用している。
手順
MySQL
まず、MySQLのconfファイルの場所をmysql --helpなどで確認し、MySQL側で外部からの接続を許可しておこう。
bind-address = 127.0.0.1
上記の行をコメントアウトし、MySQLを再起動する。
ちなみに、このbind-addressの誤った解説がネットで散見されるため、念の為に補足しておく。
これはリモートホストのIPアドレスを制限するものではなく、どのローカルアドレスでMySQLがlistenするかの指定である。
コメントアウトするとデフォルトの0.0.0.0になり、すべてのインターフェースで待ち受けることになる。
127.0.0.1が指定されていると自分自身しか接続できない。
続いて、mysql -u rootでMySQLに管理者ログインし、リモートホストから接続できるユーザを作成する。
%はすべてのホストを表すが、ホスト名やIPアドレスでも指定できる。
grant all privileges on DB名.* to ユーザ名@'%' identified by 'パスワード';
MySQL5.7では privileges を省略できる。
MySQLではホスト名（アドレス）が変わると同じユーザー名でも別人として扱われるため、パスワードを指定する。
アドレスはCIDR表記を受け付けないものの、次のようにするとサブネット単位に制限も可能。
grant all on DB名.* to ユーザ名@'223.132.%.%' identified by 'パスワード';
もし、Can't find any matching row in the user tableのエラーになったら、次のようにユーザテーブルの内容を確認し、存在しなければCREATE USERしてからGRANT ALLする。
mysql> select user,host from mysql.user;
+------------------+-------------+
| user | host |
+------------------+-------------+
| user1 | 223.132.%.% |
| user1 | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-------------+
create user ユーザ名@'%' identified by 'パスワード';
取り消す場合は次の通り。
revoke all on DB名.* from ユーザ名@'%';
delete from mysql.user where user = ユーザ名 and host = '%';
flush privileges;
最終的に許可するリモートIPアドレスのリストは https://support.google.com/datastudio/answer/7088031?hl=ja に記載されているので、ポート番号3306とともにファイアウォールに追加する。
▼ AWSのセキュリティグループの例
Data Studio
https://datastudio.google.com にGoogleアカウントでログインし、[作成] から [データソース] をクリックする。
MySQLを選択する。
接続情報を入力し、[認証] をクリックする。ポート番号はデフォルト通りなら入れなくて良い。
テーブルを選択するか、カスタムクエリにSQLを記述し、[接続] をクリックする。
テーブルまたはSQLの出力項目が展開されるので、必要に応じて修正し、[レポートを作成] をクリックする。
レポート編集画面になる。
ディメンションとは分析軸のことで、指標とは集計項目（合計値、平均値、中央値、最大値など）のこと。
例えば、時間帯ごとの消費エネルギーを見たければ、ディメンションに時間、指標にエネルギーを指定するといった具合。
実データをそのままここに載せる訳にはいかないので、ここではデータソースに「Google Analytics」を設定して説明する。データソースが違くても使い方は変わらないので。
Qiitaで埋め込んだトラッキングコードを持つ Google Analytics と接続し、
- グラフに「棒付きデータ表」
- ディメンションに「ページタイトル」
- 指標に「ページビュー数」
を指定すると、人気投稿ランキングが簡単に作れた。
レポートが完成したら、共有ボタンから他のユーザと共有する。
Googleアカウント名やメールアドレス、Googleグループで共有できる他、[共有可能なリンクを取得] すれば不特定多数とレポートを共有できる。
無料版の Data Studio では１レポート１データソースに制限されるが、ダッシュボード・可視化ツールとして使うなら十分活用できると思う。
API と Google Apps Script を使えばコネクタも自作できそうだ。