はじめに
Amazon QuickSightとは、様々なソースからのデータを結合し、
人々にわかりやすい洞察を提供するために使用できるAWS提供のBIツールです。
本記事では、QuickSightによる可視化の基礎をハンズオン形式で学習していきます。
QuickSight ハンズオン アーキテクチャ
大規模データの分析に必要な基盤は以下の4つに分類され、
それぞれ活用するAWSサービスが違います。
・収集 ・・・Amazon Kinesis, Direct Connect など
・保存 ・・・S3, EBS など
・分析 ・・・Amazon EMR, Redshift, Amazon Athena など
・可視化・・・QuickSight, OpenSearch, EC2 など
今回利用するサービスを、各データ分析基盤に当てはめた図は下記の通り。
データはAWSから提供されるものを使います。
VPCやサブネットなどを考慮して、AWSの構成図に落とし込むと以下のようになります。
これが今回作成するアーキテクチャ図です。
QuickSight ハンズオン
それではQuickSightを用いてBIダッシュボードを作成していきます。
大まかな手順は下記の通り。
1. Redshiftクラスターのセットアップ
2. Redshiftクラスターへのデータロード
3. QuickSightのセットアップ
4. QuickSightを用いてデータを可視化する
VPC, サブネット, ハンズオンで使うデータを事前に用意した状態からスタートです。
データはこちらからダウンロードしてください
データをダウンロードするためには、AWS提供の無料セミナーに登録後、
[01 今回のハンズオンで構成する構成等の紹介]タブを開いてください。
現在の状態
1. Redshiftクラスターのセットアップ
①Redshift用IAMロールの作成
まずは、この後作成するRedshiftクラスター用のIAMロールを作成する。
| パラメータ | 値 | 備考 |
|---|---|---|
| サービス | Redshift | |
| ユースケース | Redshift - Customizable | |
| 許可ポリシー | AmazonS3ReadOnlyAccess | |
| ロール名 | Redshift-Role | なんでも可 |
| 信頼ポリシー | Redshiftを信頼します | 詳しくは[画面キャプチャ]を参照 |
画面キャプチャ
②クラスターサブネットグループの作成
払い出しの前にRedshiftクラスターを所属させるための、
クラスターサブネットグループを作成する。
検索窓から「Redshift」と検索して、Redshiftのサービス画面に移動する。
左のタブから「サブネットグループ」を選択し、
「クラスターサブネットグループの作成」を選択する。
| パラメータ | 値 | 備考 |
|---|---|---|
| 名前 | cluster-subnet-group-forhandson | なんでも可 |
| VPC | DataAnalytics-VPC | ARN指定なので事前にメモ用意 |
| アベイラビリティゾーン | ap-northeast-1a | ↓サブネットが存在するAZを指定する |
| サブネット | Redshift-subnet | ARN指定なので事前にメモ用意 |
画面キャプチャ
③Redshiftクラスターの払い出し
今回は通常のRedshiftクラスターを払い出しますが、
Redshift Serverlessクラスターでも、同様のハンズオンが可能です。
Redshift Serverlessで実施するときは、異なるAZに所属する3つのサブネットに、それぞれ37個以上の利用できるIPアドレスが必要です。
Redshiftのトップ画面から、画面中央の「クラスターを作成」を選択する。
下記パラメータを指定。そのほかはデフォルトのまま作成。
| パラメータ | 値 | 備考 |
|---|---|---|
| クラスター識別子 | redshift-cluster-forhandson | なんでも可 |
| クラスターのサイズ | 自分で選択 | |
| ノードの種類 | dc2.large | 節約!! |
| ノードの数 | 1 | 節約!! |
| パラメータ | 値 | 備考 |
|---|---|---|
| 管理者ユーザー名 | awsuser | |
| 管理者パスワード | 管理者パスワードを手動で追加する | |
| パスワード | *************** | ご自由に |
| パラメータ | 値 | 備考 |
|---|---|---|
| IAMロール | Redshift-Role | ①で作成したロールを指定 |
| パラメータ | 値 | 備考 |
|---|---|---|
| デフォルトを使用 | オフ | オフにすると↓が設定できる |
| VPC | DataAnalytics-VPC | |
| VPCセキュリティグループ | redshift-group | この後の手順で変更が発生するので、今はなんでもOK |
| クラスターサブネットグループ | cluster-subnet-group-forhandson | ②で作成したサブネットグループを指定 |
| 拡張されたVPCのルーティング | オンにする | 重要!! |
| パブリックにアクセス可能 | チェックを外す(オフ) | 重要!! |
※拡張VPCルーティングによりS3とRedshiftのデータ連携をAWS内部ネットワークに留める。
| パラメータ | 値 | 備考 |
|---|---|---|
| データベース名 | dev | |
| データベースポート | 5439 |
| パラメータ | 値 | 備考 |
|---|---|---|
| 自動スナップショット保持期間 | 0 | 節約!! |
画面キャプチャ
現在の状態
2. Redshiftクラスターへのデータロード
①S3バケットの作成
RedshiftのデータソースとなるS3バケットを作成していく。
検索窓から「S3」と検索して、S3のサービス画面に移動する。
「バケットを作成」を選択する。
下記パラメータ以外はデフォルトで作成する。
| パラメータ | 値 | 備考 |
|---|---|---|
| バケット名 | s3bucket-redshift-forhandson- | グローバルで一意の名前にする |
| パブリックアクセスをすべてブロック | オン | |
| バケットのバージョニング | 無効にする |
画面キャプチャ
②S3へのデータアップロード
先ほど作成したバケットを選択し、事前にダウンロードしたデータをアップロードする。
画面キャプチャ
②S3のゲートウェイエンドポイントの作成
Redshiftとの連携用にS3のゲートウェイエンドポイントを作成する。
検索窓から「VPC」と検索して、VPCのサービス画面に移動する。
左タブから「エンドポイント」に遷移し、「エンドポイントを作成」を選択する。
下記パラメータ以外はデフォルトで作成する。
| パラメータ | 値 | 備考 |
|---|---|---|
| 名前タグ | s3-redshift-endpoint | なんでも可 |
| サービスカテゴリ | AWSのサービス | |
| サービス | com.amazonaws.ap-northeast-1.s3 | |
| タイプ | Gateway | Interfaceではないので注意!! |
| パラメータ | 値 | 備考 |
|---|---|---|
| VPC | DataAnalytics-VPC | |
| ルートテーブル | Redshift-subnetのルートテーブル |
画面キャプチャ
③Redshiftクラスターのセキュリティグループ編集
Redshiftに付与されたセキュリティグループを編集、
もしくは新たにセキュリティグループを作成する。
検索窓から「EC2」と検索して、EC2のサービス画面に移動する。
左タブから「セキュリティグループ」に遷移し、
「セキュリティグループを作成」を選択する。
| パラメータ | 値 | 備考 |
|---|---|---|
| セキュリティグループ名 | redshift-group | なんでも可 |
| VPC | DataAnalytics-VPC |
| インバウンドルール | タイプ | ソース | 備考 |
|---|---|---|---|
| すべてのトラフィック | S3ゲートウェイエンドポイント | 「com.amazonaws.ap-northeast-1.s3」を検索欄に入力して選択 |
| アウトバウンドルール | タイプ | 送信先 | 備考 |
|---|---|---|---|
| すべてのトラフィック | 0.0.0.0/0 |
画面キャプチャ
手順２で作成したRedshiftクラスターの「ネットワークとセキュリティ」タブから、
セキュリティグループを付け替える。
画面キャプチャ
④Redshiftクラスター上にテーブル作成
作成したRedshiftクラスターから「クエリエディタでクエリ」から
クエリエディタを起動し、
「データベースに接続」で、まずはRedshiftクラスターに接続する。
接続情報は③Redshiftクラスターの払い出しで指定した認証情報を使いましょう
画面キャプチャ
下記3種類のcreate tableコマンドにより、Redshiftクラスター上にテーブルを作成する。
create table users(
userid integer not null,
username char(8),
firstname varchar(30),
lastname varchar(30),
city varchar(30),
state char(2),
email varchar(100),
phone char(14),
likesports boolean,
liketheatre boolean,
likeconcerts boolean,
likejazz boolean,
likeclassical boolean,
likeopera boolean,
likerock boolean,
likevegas boolean,
likebroadway boolean,
likemusicals boolean);
create table date(
dateid smallint not null,
caldate date not null,
day character(3) not null,
week smallint not null,
month character(5) not null,
qtr character(5) not null,
year smallint not null,
holiday boolean default('N'));
create table sales(
salesid integer not null,
listid integer not null,
sellerid integer not null,
buyerid integer not null,
eventid integer not null,
dateid smallint not null,
qtysold smallint not null,
pricepaid decimal(8,2),
commission decimal(8,2),
saletime timestamp);
画面キャプチャ
⑤COPYコマンドによるデータロード
続けてCOPYコマンドを用いて、S3からRedshiftにデータをコピーする。
COPYコマンドが1分以上完了しない場合は、
NW設定が適切でない場合があります。
COPY users from 's3://<s3バケットのARN>/allusers_pipe.txt' iam_role '<Redshiftに割り当てたIAMロールのARN>' delimiter '|' ;
COPY date from 's3://<s3バケットのARN>/date2008_pipe.txt' iam_role '<Redshiftに割り当てたIAMロールのARN>' delimiter '|' ;
COPY sales from 's3://<s3バケットのARN>/sales_tab.txt' iam_role '<Redshiftに割り当てたIAMロールのARN>' delimiter '\t' timeformat 'MM/DD/YYYY HH:MI:SS';
画面キャプチャ
現在の状態
3. QuickSightのセットアップ
①QuickSightへのサインアップ
検索窓から「QuickSight」と検索して、QuickSightのサービス画面に移動する。
「QUICKSIGHTにサインアップ」を選択する。
チュートリアルは「次へ」で飛ばして「分析」画面を表示させる。
下記パラメータ以外はデフォルトで作成する。
| パラメータ | 値 | 備考 |
|---|---|---|
| アカウント通知用メールアドレス | ******* | 有効なメールアドレスならなんでも可 |
| QuickSightリージョン | Asia Pacific (Tokyo) | |
| QuickSightアカウント名 | quicksight-handson-yyyymmdd | |
| IAMロール | QuickSightで管理されるロールを使用する | |
| オプションのアドオン | オフ | 節約!! |
画面キャプチャ
②QuickSightからVPC接続用IAMロールの作成
ここからはQuickSightからVPCに接続するための設定を作成していく。
・ポリシー作成
検索窓から「IAM」と検索して、IAMのサービス画面に移動する。
左タブから「ポリシー」に遷移し、「ポリシーの作成」を選択する。
| パラメータ | 値 | 備考 |
|---|---|---|
| ポリシー名 | Quicksight-policy | なんでも可 |
| ポリシー | 下記のjsonを付与する |
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"ec2:CreateNetworkInterface",
"ec2:ModifyNetworkInterfaceAttribute",
"ec2:DeleteNetworkInterface",
"ec2:DescribeSubnets",
"ec2:DescribeSecurityGroups"
],
"Resource": "*"
}
]
}
画面キャプチャ
・ロール作成
左タブから「ロール」に遷移し、「ロールを作成」を選択する。
| パラメータ | 値 | 備考 |
|---|---|---|
| サービス | カスタム信頼ポリシー | |
| カスタム信頼ポリシー | QuickSightを信頼します | 詳しくは[画面キャプチャ]を参照 |
| 許可ポリシー | Quicksight-policy | 直前に作成したポリシーを付与。 |
| ロール名 | Quicksight-Role | なんでも可 |
画面キャプチャ
③QuickSightとRedshift用セキュリティグループの編集
・QuickSight用セキュリティグループの作成
EC2のサービス画面に移動し、左タブから「セキュリティグループ」に遷移し、
「セキュリティグループを作成」を選択する。
| パラメータ | 値 | 備考 |
|---|---|---|
| セキュリティグループ名 | quicksight-group | なんでも可 |
| VPC | DataAnalytics-VPC |
| インバウンドルール | タイプ | ソース | 備考 |
|---|---|---|---|
| すべてのトラフィック | Redshiftに付与されているセキュリティグループ |
| アウトバウンドルール | タイプ | 送信先 | 備考 |
|---|---|---|---|
| すべてのトラフィック | 0.0.0.0/0 |
画面キャプチャ
・Redshift用セキュリティグループの編集
Redshiftに付与したセキュリティグループを指定し、
「インバウンドルール」タブから「インバウンドのルールを編集」を選択する。
下記のルールを追加で付与する。
| インバウンドルール | タイプ | ソース | 備考 |
|---|---|---|---|
| Redshift | QuickSight用に作成したセキュリティグループ |
画面キャプチャ
④QuickSightとVPCを接続
QuickSightとVPCを接続していく。
QuickSight画面に戻り、画面右上のユーザーアイコンをクリックし、
「QuickSightを管理」を選択する。
左タブから「VPC接続の管理」に遷移し、「VPC接続の追加」を選択する。
| パラメータ | 値 | 備考 |
|---|---|---|
| VPC接続名 | redshift-connection | なんでも可 |
| VPC ID | DataAnalytics-VPC | ARN指定なので事前にメモ用意 |
| 実行ロール | Quicksight-Role | 直前に作成したロールを付与。 |
| サブネット | Redshift-subnetとQuickSight-subnet | ARN指定なので事前にメモ用意 |
| セキュリティグループID | QuickSight用に作成したセキュリティグループ | ARN指定なので事前にメモ用意 |
ネットワークインターフェースが各サブネットに1枚ずつ追加され、
ステータスが「AVAILABLE」になればデータ連携可能になる。
画面キャプチャ
現在の状態（完成!!!）
4. QuickSightを用いてデータを可視化する
これまでの手順でデータ分析環境の構築は完了しているため、
Redshiftからデータを連携し、QuickSightで可視化していく。
①分析用データをQuickSightにロードする
QuickSightのトップ画面に戻り、左タブから「分析」に遷移し、
「新しい分析」を選択する。
「新しいデータセット」から「Redshift(自動検出)」を選び、
作成したRedshiftクラスターと接続する。
下記パラメータを入力した後、「データソースを作成」を押下する。
| パラメータ | 値 | 備考 |
|---|---|---|
| データソース名 | private-redshift | なんでも可 |
| インスタンスID | redshift-cluster-frohandson | 1. Redshiftクラスターのセットアップで入力したパラメータ |
| 接続タイプ | redshift-connection | 直前に作成したVPC接続を指定。 |
| データベース名 | dev | |
| ユーザ名 | awsuser | |
| パスワード | *********** |
接続情報は1. Redshiftクラスターのセットアップの③Redshiftクラスターの払い出しで指定した認証情報を使いましょう
スキーマに「public」、テーブルに「sales」を選択し、
左下の「データの編集/プレビュー」を押下する。(「選択」ではないので注意)
画面キャプチャ
②データを結合する
画面右上の「データを追加」から「データソース」→
「<先ほど追加したデータソース名>」を選び、「date」テーブルを追加する。
「date」テーブルを追加したら、画面下の結合句の両方に「#dateid」を指定し、
結合タイプに「Inner」を指定し、「適用」を押下する。
画面右上の「保存して視覚化」を押下して、分析ページに遷移する。
画面キャプチャ
③2008年1月5日の売上量(qtysold)を表示する
左の「データ」から「caldate」と「qtysold」を選択することで、
売上量の時系列グラフが作成される。
画面キャプチャ
画面左上の「フィルター」アイコンから「caldate」を選択し、
下記のフィルター条件を設定する。
| パラメータ | 値 | 備考 |
|---|---|---|
| フィルタータイプ | 日付と時刻の範囲 | |
| 条件 | 次と等しい | |
| 時間の詳細度 | 日 | |
| 日付 | 2008/01/05 |
画面キャプチャ
グラフのままでは見づらいので、テーブル形式に変換する。
画面左上の「ビジュアル」アイコンを選択し、
ビジュアルタイプの中から「テーブル」を選択する。
画面キャプチャ
2008年1月5日の売上量(qtysold)が210であることが表示されました。
④売上量(qtysold)TOP10のバイヤーを表示する
視覚化に用いるデータセットを「編集」し、②データを結合すると同様の手順で
「users」テーブルをInner joinする。
結合句には「salesテーブルの#buyerid」と「usersテーブルの#userid」を指定する。
画面キャプチャ
分析画面に戻り、画面左上の「ビジュアル」アイコンから
テーブルのビジュアルを追加する。
左の「データ」タブからbuyerid,firstname,lastname,qtysoldを追加する。
さらに画面左上の「フィルター」アイコンからbuyeridにフィルター条件を設定する。
下記のフィルター条件を設定する。
| パラメータ | 値 | 備考 |
|---|---|---|
| フィルタータイプ | 上位と下位のフィルター 上 | |
| Integer | 10 | |
| 集計 | 合計 | |
| 日付 | 2008/01/05 |
画面キャプチャ
TOP10は表示されたが、並びがバラバラなので降順表示に変更する。
表示されたテーブルの「qtysold」を選択し、並び替えを降順にする。
画面キャプチャ
売上量(qtysold)TOP10のバイヤーランキングが表示されました。
所感
拡張VPCルーティングとエンドポイントの設定をミスって、Redshiftクラスターへのデータロードで見事にハマりました。。
NW設定が不適切だとクエリがすぐにエラーを返さず延々と動き続けるのでかなり時間を無駄にしてしまいました。
QuickSightを操作できるようになるまでが長い！！
本当はもう少しデータ可視化をじっくりやりたかったのですが、
途中で力尽きましたね。
引用元
[1]AWS Hands-on for Beginners 手を動かしなら学ぶ Analytics サービス入門
https://pages.awscloud.com/JAPAN-event-OE-Hands-on-for-Beginners-Analytics-2022-confirmation_790.html
[2][AWS Black Belt Online Seminar] Amazon QuickSight 資料及びQA公開
https://aws.amazon.com/jp/blogs/news/aws-black-belt-online-seminar-amazon-quicksight/
[3]AWSでのデータ収集、分析、そして機械学習
https://pages.awscloud.com/rs/112-TZM-766/images/C2-05.pdf