BIツールでMySQLのデータを可視化した話

とある案件で、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 感染予測](http://g.co/covid19/japanforecast) も Data Studio のダッシュボードを利用している。 

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

```ini:/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に管理者ログインし、リモートホストから接続できるユーザを作成する。
`%`はすべてのホストを表すが、ホスト名やIPアドレスでも指定できる。

```sql
grant all privileges on DB名.* to ユーザ名@'%' identified by 'パスワード';
```

MySQL5.7では privileges を省略できる。
MySQLではホスト名（アドレス）が変わると同じユーザー名でも別人として扱われるため、パスワードを指定する。

アドレスはCIDR表記を受け付けないものの、次のようにするとサブネット単位に制限も可能。

```sql
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   |
+------------------+-------------+
```

```sql
create user ユーザ名@'%' identified by 'パスワード';
```

取り消す場合は次の通り。

```sql
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のセキュリティグループの例
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/236222/585ff932-c70f-d8b0-5bd7-912fa4043644.png)

## Data Studio
https://datastudio.google.com にGoogleアカウントでログインし、[作成] から [データソース] をクリックする。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/236222/c7d6bdee-6938-bc73-d822-04b8aa100385.png)

MySQLを選択する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/236222/781342c6-93c9-a34f-ea0e-2100de2cd87a.png)

接続情報を入力し、[認証] をクリックする。ポート番号はデフォルト通りなら入れなくて良い。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/236222/759b9c14-0289-88a7-cfb3-318695e6f41e.png)

テーブルを選択するか、カスタムクエリにSQLを記述し、[接続] をクリックする。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/236222/9ad40bd7-20e0-a34d-fede-5ce9fe5fee86.png)

テーブルまたはSQLの出力項目が展開されるので、必要に応じて修正し、[レポートを作成] をクリックする。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/236222/b3da9cb8-68fa-8f64-06ec-9caa52a1b546.png)

レポート編集画面になる。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/236222/112abdfa-0deb-e60f-d1a8-671f36e446c5.png)
**ディメンション**とは分析軸のことで、**指標**とは集計項目（合計値、平均値、中央値、最大値など）のこと。
例えば、時間帯ごとの消費エネルギーを見たければ、ディメンションに時間、指標にエネルギーを指定するといった具合。

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

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

- グラフに「棒付きデータ表」
- ディメンションに「ページタイトル」
- 指標に「ページビュー数」

を指定すると、人気投稿ランキングが簡単に作れた。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/236222/3df4a695-aace-8a57-dc35-647905376b68.png)

レポートが完成したら、共有ボタンから他のユーザと共有する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/236222/4714c0ef-fb9e-bff1-3811-73ad653adef7.png)
Googleアカウント名やメールアドレス、Googleグループで共有できる他、[共有可能なリンクを取得] すれば不特定多数とレポートを共有できる。

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