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

#Amazon QuickSight

#AWS

石川覚

2016.11.29

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

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

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

PostgreSQLはバージョンは9.5系としています。 Redshiftのチュートリアルと同じく、Tableauのサンプルデータ、superstoreのデータを利用します。

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

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

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

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

QuickSightからのインバウンド接続の許可設定が必要です。AWS側のセキュリティグループやNetworkACLをご確認ください。さらに PostgreSQL on EC2の場合は、pg_hba.confに許可設定をお忘れにならないようにご注意ください。

現在サポートされているリージョンにおける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（PostgreSQL）環境

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

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

-bash-4.2$ psql -U cmuser -d postgres
Password for user cmuser:
psql (9.5.4)
Type "help" for help.

postgres=> CREATE TABLE cmuser.orders(
postgres(> order_id int,
postgres(> order_date date,
postgres(> proprity varchar(32),
postgres(> quantity smallint,
postgres(> sales int,
postgres(> discount decimal(3,2),
postgres(> ship_mode varchar(32),
postgres(> profit int,
postgres(> unit_price int,
postgres(> advertising int,
postgres(> shipping_cost int,
postgres(> customer varchar(64),
postgres(> prefecture varchar(32),
postgres(> city varchar(32),
postgres(> region varchar(32),
postgres(> store_name varchar(64),
postgres(> segment varchar(64),
postgres(> category varchar(64),
postgres(> sub_category varchar(64),
postgres(> product_id varchar(16),
postgres(> product_name  varchar(256),
postgres(> product_description varchar(1024),
postgres(> product_continer varchar(32),
postgres(> base_margin decimal(3,2),
postgres(> suppryer varchar(32),
postgres(> estimated_delivery_date date,
postgres(> ship_date date
postgres(> );
CREATE TABLE
postgres=> \d
        List of relations
 Schema |  Name  | Type  | Owner
--------+--------+-------+--------
 cmuser | orders | table | cmuser
(1 row)


postgres=> \COPY cmuser.orders FROM '/tmp/orders.csv' WITH CSV;
COPY 8369

PostgreSQL on EC2 環境

以下の様にPostgreSQL on EC2環境を作成します。 Amazon Linux にpostgresql95-server.x86_64をインストールします。

[ec2-user@ip-10-0-0-144 ~]$ sudo yum -y install postgresql95-server.x86_64
読み込んだプラグイン:priorities, update-motd, upgrade-helper
amzn-main/latest                                                                              | 2.1 kB     00:00
amzn-updates/latest                                                                           | 2.3 kB     00:00
:
完了しました!

[ec2-user@ip-10-0-0-144 ~]$ sudo /etc/rc.d/init.d/postgresql95 initdb
データベースを初期化中:                                    [  OK  ]

[ec2-user@ip-10-0-0-144 ~]$ sudo /etc/rc.d/init.d/postgresql95 start
postgresql95 サービスを開始中:                             [  OK  ]

[ec2-user@ip-10-0-0-144 ~]$ sudo su - postgres
-bash-4.2$ psql
psql (9.5.4)
Type "help" for help.

postgres=# \password postgres
Enter new password:
Enter it again:
postgres=# ^C
postgres=# \q
-bash-4.2$
-bash-4.2$ postgresql_conf=/var/lib/pgsql95/data/postgresql.conf
-bash-4.2$ pg_hba_conf=/var/lib/pgsql95/data/pg_hba.conf
-bash-4.2$ now=`date '+%Y%m%H%M%S'`
-bash-4.2$
-bash-4.2$ mv ${postgresql_conf} ${postgresql_conf}.${now}
-bash-4.2$ cat ${postgresql_conf}.${now} \
> | sed -e 's/#listen_addresses = '\''localhost'\''/listen_addresses = '\''*'\''/g' \
> | sed -e 's/#port = 5432/port = 5432 /g' \
> | sed -e 's/#log_line_prefix = '\'''\''/log_line_prefix = '\''<%t %u %d>'\''/g' \
> > ${postgresql_conf}
-bash-4.2$
-bash-4.2$ mv ${pg_hba_conf} ${pg_hba_conf}.${now}
-bash-4.2$ cat ${pg_hba_conf}.${now} \
> | sed -e 's/local   all             all                                     peer/local   all             all                                     md5/g' \
> > ${pg_hba_conf}
-bash-4.2$ logout

[ec2-user@ip-10-0-0-144 ~]$ sudo /etc/rc.d/init.d/postgresql95 restart
postgresql95 サービスを停止中:                             [  OK  ]
postgresql95 サービスを開始中:                             [  OK  ]

[ec2-user@ip-10-0-0-144 ~]$ sudo su - postgres
最終ログイン: 2016/11/27 (日) 09:38:47 UTC日時 pts/0
-bash-4.2$ sudo su - postgres
ユーザー postgres のパスワードを変更。
新しいパスワード:
新しいパスワードを再入力してください:
passwd: すべての認証トークンが正しく更新できました。

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

上記の通り、PostgreSQL on EC2の場合は、pg_hba.confに許可設定を追加したり、postgres.confのリスンアドレスやポート番号の設定を追加しています。

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

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

20161128-auto-discoverd

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

20161128-auto-discoverd-rds-mysql

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

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

次にテーブルを選択。

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

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

20161128-auto-discoverd-rds-postgresql-list

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

20161128-auto-discoverd-rds-postgresql-view

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

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

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

まとめ

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