Zenn
🔬

BIツールでMySQLのデータを可視化する

2021/01/25に公開
Google Analytics
MySQL
可視化
idea

本ドキュメントは次のミラーサイトです。
https://qiita.com/mindwood/items/dbfb9875f7f4d81a03bf

とある案件で、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で予測し、リアルタイムで表示する下記サイトも Data Studio のダッシュボードを利用している。
http://g.co/covid19/japanforecast

手順

MySQL

まず、MySQLのconfファイルの場所をmysql --helpなどで確認し、MySQL側で外部からの接続を許可しておこう。

/etc/mysql/mysql.conf.d/mysqld.cnf
bind-address = 127.0.0.1

上記の行をコメントアウトし、MySQLを再起動する。

ちなみに、このbind-addressの誤った解説がネットで散見されるため、念の為に補足しておく。
これはリモートホストのIPアドレスを制限するものではなく、どのローカルアドレスでMySQLがlistenするかの指定である。
コメントアウトするとデフォルトの0.0.0.0になり、すべてのインターフェースで待ち受けることになる。
127.0.0.1が指定されていると自分自身しか接続できない。

続いて、mysql -u rootでMySQLに管理者ログインし、すべてのリモートホストから接続できるユーザを作成する。

grant all privileges on DB名.* to ユーザ名@'%';

もし、Can't find any matching row in the user tableのエラーになったら、select user,host from mysql.user でユーザテーブルの内容を確認し、CREATE USERしてからGRANT ALLする。

create user DB名@'%' identified by 'パスワード';

最終的に許可するリモートIPアドレスのリストは https://support.google.com/datastudio/answer/7088031?hl=ja に記載されているので、ポート番号3306とともにファイアウォールに追加する。

Data Studio

https://datastudio.google.com
にGoogleアカウントでログインし、[作成] から [データソース] をクリックする。

MySQLを選択する。

接続情報を入力し、[認証] をクリックする。ポート番号はデフォルト通りなら入れなくて良い。

テーブルを選択するか、カスタムクエリにSQLを記述し、[接続] をクリックする。

テーブルまたはSQLの出力項目が展開されるので、必要に応じて修正し、[レポートを作成] をクリックする。

レポート編集画面になる。

ディメンションとは分析軸のことで、指標とは集計項目（合計値、平均値、中央値、最大値など）のこと。
例えば、時間帯ごとの消費エネルギーを見たければ、ディメンションに時間、指標にエネルギーを指定するといった具合。

実データをここに載せる訳にはいかないので、ここではデータソースに「Google Analytics」を設定して説明する。データソースが違くても使い方は変わらない。

Zennで埋め込んだトラッキングコードを持つ Google Analytics と接続し、

  • ディメンションに、ページタイトル、イベント名
  • 指標に、視聴回数、ユーザーの合計数
  • 並び替えに、視聴回数（降順）

を指定すると、自分が投稿したZenn記事のランキング表を簡単に作れた。

Google Analytics には、UAプロパティとGA4プロパティがあるが、イベントを収集できるのはGA4プロパティである。

レポートが完成したら、共有ボタンから他のユーザと共有しよう。

Googleアカウント名やメールアドレス、Googleグループで共有できる他、[共有可能なリンクを取得] すれば不特定多数とレポートを共有できる。

無料版の Data Studio では１レポート１データソースに制限されるが、ダッシュボード・可視化ツールとして使うなら十分活用できると思う。
API と Google Apps Script を使えばコネクタも自作できそうだ。

Discussion