Amazon QuickSight チュートリアル実践：データベースデータ(MySQL/MariaDB)を使って分析を作成

#Amazon QuickSight

#AWS

石川覚

2016.11.28

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

Amazon QuickSightからMySQL/MariaDBのデータを使って分析を作成する方法をご紹介します。データセットを作成する方法は、同じAWS環境下のRDSの自動検知（Auto discoverd）とデータベースサーバーやポート番号などを指定する手動設定（Manual Connect）があります。 AWS環境下では、以下の組み合わせが存在します。

RDS（MySQL）=> RDS(自動検知)
RDS（MariaDB）=> RDS(自動検知)
MySQL on EC2 => MySQL(手動設定)
MariaDB on EC2 => MariaDB(手動設定)

MySQLとMariaDBは基本的に同じ設定となりますので、MySQLの接続方法をメインに紹介します。MySQLはバージョンは5.6系、MariaDBは10.0としています。 Redshiftのチュートリアルと同じく、Tableauのサンプルデータ、superstoreのデータを利用します。

アクセス制限周りの注意点

アクセス制限周りの設定については下記エントリをご参照ください。

Amazon QuickSight: DBデータソースからデータセットを作成する際の諸注意(前提条件まとめ) ｜ Developers.IO

注意：Amazon QuickSightのIPアドレス範囲

QuickSightからのインバウンド接続の許可設定が必要です。AWS側のセキュリティグループやNetworkACLをご確認ください。

現在サポートされているリージョンにおけるAmazon QuickSightサーバのIPアドレス範囲の情報は以下となります。

リージョン	IPアドレス範囲
US East (N. Virginia) (us-east-1)	52.23.63.224/27
US West (Oregon) (us-west-2)	54.70.204.128/27
EU (Ireland) (eu-west-1)	52.210.255.224/27

RDS（MySQL）環境

以下の様にRDS/MySQLを作成します。

より本番環境のユースケースに合わせるため、DBシステム管理者（root）以外にアプリケーションユーザー（cmuser）を追加して、そのユーザのテーブルをデータ分析の対象とします。

[ec2-user@ip-10-0-0-144 ~]$ mysql -h cmmysql.xxxx.us-east-1.rds.amazonaws.com -u cmuser -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.6.27-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use cmuser;
Database changed
mysql> CREATE TABLE orders(
    -> order_id int,
    -> order_date date,
    -> proprity varchar(32),
    -> quantity smallint,
    -> sales int,
    -> discount decimal(3,2),
    -> ship_mode varchar(32),
    -> profit int,
    -> unit_price int,
    -> advertising int,
    -> shipping_cost int,
    -> customer varchar(64),
    -> prefecture varchar(32),
    -> city varchar(32),
    -> region varchar(32),
    -> store_name varchar(64),
    -> segment varchar(64),
    -> category varchar(64),
    -> sub_category varchar(64),
    -> product_id varchar(16),
    -> product_name  varchar(256),
    -> product_description varchar(1024),
    -> product_continer varchar(32),
    -> base_margin decimal(3,2),
    -> suppryer varchar(32),
    -> estimated_delivery_date date,
    -> ship_date date
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> LOAD DATA LOCAL INFILE '/tmp/orders.csv' INTO TABLE orders FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
Query OK, 8369 rows affected (0.32 sec)
Records: 8369  Deleted: 0  Skipped: 0  Warnings: 0

MySQL on EC2 環境

以下の様にMySQL on EC2環境を作成します。 Amazon Linux にmysql56-server.x86_64をインストールします。mysql_secure_installationは「Disallow root login remotely」以外は、デフォルトで設定しています。

[ec2-user@ip-10-0-0-144 ~]$ sudo yum -y install mysql56-server.x8664
読み込んだプラグイン:priorities, update-motd, upgrade-helper
amzn-main/latest | 2.1 kB 00:00
amzn-main/latest/group | 35 kB 00:00
:
完了しました!
[ec2-user@ip-10-0-0-144 ~]$ sudo /etc/rc.d/init.d/mysqld start
Initializing MySQL database
:
Starting mysqld: [ OK ]
[ec2-user@ip-10-0-0-144 ~]$ /usr/libexec/mysql56/mysqlsecure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
:
Disallow root login remotely? [Y/n] n
... skipping.
:
installation should now be secure.
Thanks for using MySQL!
Cleaning up...

RDS（MySQL）環境と同様に、より本番環境のユースケースに合わせるため、DBシステム管理者（root）以外にアプリケーションユーザー（cmuser）を追加して、そのユーザのテーブルをデータ分析の対象とします。

自動検知機能を用いたデータセットの作成(RDS/MySQL, RDS/MariaDB)

Amazon QuickSightにサインインした直後の画面に自動検知機能されたRDSインスタンスから選択してデータセットを作成することができます。

20161128-auto-discoverd

今回はデータセット作成のメニューで[RDS]を選択します。

20161128-auto-discoverd-rds-mysql

インスタンスIDの部分でプルダウンを展開してみます。すると、自動検知してくれたクラスタのIDが表示されました。ここでは「cmmysql」というRDSインスタンスを選択します。

その他の設定事項についても必要事項を記入。左下のボタンで接続を確認した後、[Create data source]を押下。

次にテーブルを選択。

ここはデータセットを作る最終段階です。・Import to SPICE for quicker analytics ・Directory query your data 前者が指定したテーブルデータをSPICEにロード（デフォルト）します。後者は指定したテーブルに対する直接クエリをアドホックに投げてデータを得ます。では、どのようにテーブルデータが格納されたかを確認してみましょう。SPICE取込を選択したままで[Edit/Preview data]を選択します。

以下の様に、取り込んだデータを確認出来ます。

20161128-auto-discoverd-rds-list

可視化も左のディメンジョンとメジャを選択して、VisualTypes を切り替えるのみで、いい感じで出来ました。

20161128-auto-discoverd-rds-view

自動検知機能を使わないデータセットの作成(MySQL, MariaDB on EC2)

自動検知を用いない場合はそれぞれ『MySQL』や『MariaDB』を選択します。こちらの接続方法を用いることで、別アカウントのAmazon RDSインスタンスの接続やEC2にインストールした MySQLやMariaDBに接続することができます。ここでは新規データセット作成の際の接続種別で『MySQL』を選択し、

20161128-manual-connect

必要事項を全て手入力で埋めるだけです。接続確立後の操作は自動検知機能利用時のものと変わらないので割愛します。

まとめ

同じAWSアカウント上のRDSのMySQL/MariaDBインスタンスに接続する場合は、『RDS』から自動検知機能を利用することをおすすめします。一方、異なるAWSアカウント上のRDSのMySQL/MariaDBインスタンスやEC2インスタンスで動作しているMySQL/MariaDBは、『MySQL』や『MariaDB』から接続することができます。