Redshift+QuickSightでS3のデータを分析してみた
こんにちは、保険システム部の照屋です。
今回は、Redshiftを構築し、クエリを実行して、S3にあるCSVファイルを分析してみます。
さらに、QuickSightとの連携を行って、データの可視化も行います。
Amazon Redshiftとは
RedshiftとはAWSが提供するフルマネージド型のデータウェアハウスサービスです。
Redshiftは、PostgreSQLをベースに構築されており、PostgreSQLの構文を使用してアクセスすることが可能です。
また、データを並列で処理しているため、高速にデータの読み出しや分析を行うことができます。
そのため、大規模なデータを処理することを得意としています。
Redshiftの構築
Redshiftの構築には、VPCやサブネットの作成を行う必要があります。
最初に、VPCとサブネットの作成から行っていきます。
構成図
今回のゴールとなる構成図は以下のようになります。
Redshiftを構築して、QuickSightと連携することでデータを可視化します。
しかし、Redshiftはプライベートサブネットにあり、QuickSightから直接アクセスすることができません。
そのため、RedshiftとQuickSightの間に踏み台となるプライベートサブネットを構築し、QuickSightとのVPC接続を設定します。
VPCの作成
VPCのコンソール画面を開き、「VPC」タブから「VPCを作成」をクリックします。
VPCの設定画面で、VPCの名前やIPv4CIDRを設定します。
入力が完了したら、「VPCを作成」をクリックします。
Redshift用のサブネットを作成
作成したVPC内に、サブネットを作成します。
下図のオレンジの枠で囲われている部分を構築していきます。
VPCのコンソール画面で、「サブネットを作成」をクリックします。
サブネットを作成するVPCとして、先ほど作成したVPCを選択します。
サブネットの設定欄で、サブネット名、アベイラビリティ－ゾーン、CIDRブロックを設定します。
入力が完了したら「サブネットを作成」をクリックします。
クラスターサブネットグループの作成
Redshiftのコンソール画面で、「設定」タブの「サブネットグループ」を選択します。
「クラスターサブネットグループの作成」をクリックします。
クラスターサブネットグループの詳細欄で、クラスターサブネットグループの名前と説明を入力します。
サブネットの追加欄で、作成したVPCとサブネットを選択し、「サブネットを追加」をクリックします。
サブネットの追加まで完了したら、「クラスターサブネットグループの作成」をクリックします。
クラスターの作成
Redshiftのコンソール画面で、「クラスター」タブの「クラスターを作成」をクリックします。
次の画面で、クラスターを作成していきます。
クラスター設定で、クラスター識別子とノードの種類・数を入力します。
今回はノードの種類として、最も安価で利用できる「dc2.large」を選択し、ノードの数は1つで設定します。
続いて、データベース設定で、管理者ユーザー名とパスワードを設定します。
クラスターの許可欄では、RedshiftやS3へのアクセス権限が付与されたIAMロールを関連付けます。
設定が完了したら、画面下部にある「クラスターを作成」をクリックします。
S3のデータをRedshiftへロード
クラスター作成後に「クラスター」タブをクリックすると、作成したクラスターが表示されています。
作成したクラスターをチェックし、「Query in query editor」か「Query in query editor v2」のどちらかをクリックしてエディタを開きます。（今回は「Query in query editor v2」をクリックします。）
S3のデータをロードする前に、Redshiftクラスターにテーブルを作成します。
S3のデータをRedshiftにロードするには、COPYコマンドを使用します。
FROMには、ロードするファイルのパスを入力します。
iam_roleには、クラスター作成時に紐づけたIAMロールのARNを指定します。
今回はCSVファイルをロードするため、delimiterに’ , 'を指定します。
そして、IGNOREHEADERに1を指定することで、1行目をヘッダーとして扱い、ロードしないという設定が可能になります。
データのロードが完了したら、SELECT文を実行し、正しくロードできているか確認します。
正しくロードできていることが確認出来たら、Redshiftの構築は完了です！
QuickSightへの連携
Redshiftにロードしたデータを可視化して分析するために、QuickSightへの連携を行います。
しかし、今回構築したRedshiftクラスターはプライベートサブネット内にあるため、現在の状態ではQuickSightからアクセスすることができません。
そのため、QuickSightからRedshiftクラスターへのVPC接続を行います。
VPC接続を行うためには、新しくサブネットとセキュリティグループを作成する必要があります。
QuickSightアクセス用のサブネットを作成
下図のオレンジの枠で囲われている部分を構築していきます。
今回作成したVPC内に、QuickSightアクセス用のサブネットを作成します。
QuickSightアクセス用のセキュリティグループの作成
セキュリティグループ（Security group1）を作成して、下図のオレンジの枠で囲われた部分で通信を行えるようにします。
VPC内にセキュリティグループ（Security group1）を作成していきます。
Redshiftクラスターからの通信を許可するインバウンドルールを追加します。
「タイプ」は「すべてのTCP」を選択し、「ソース」にはVPCを作成した際に自動で作成された、Redshift用のセキュリティグループ（Security group2）のIDを入力します。
Redshiftクラスターへのトラフィックを許可するアウトバウンドルールも追加します。
「タイプ」は「カスタムTCP」を選択し、「ポート範囲」にはRedshiftポートである「5439」を入力します。
「ソース」にはインバウンドルールで設定したセキュリティグループIDを入力します。
以上で、QuickSightアクセス用のセキュリティグループの作成が完了しました。
Redshift用のセキュリティグループを編集
VPCを作成した際に、自動的にデフォルトで作成されるRedshift用のセキュリティグループ（Security group2）を編集していきます。
QuickSightアクセス用のセキュリティグループ（Security group1）からのトラフィックを許可するインバウンドルールを追加します。
「タイプ」は「Redshift」を選択し、「ソース」にはQuickSightアクセス用のセキュリティグループIDを入力します。
QuickSightアクセス用のセキュリティグループ（Security group1）へのトラフィックを許可するアウトバウンドルールを追加します。
「タイプ」は「すべてのTCP」を選択し、ソースにインバンドルールで設定したセキュリティグループIDを入力します。
以上でデフォルトのセキュリティグループの編集が完了しました。
VPC接続の追加
下図のオレンジの枠で囲われた部分を構築していきます。
QuickSightのVPC接続を追加します。
QuickSightコンソール画面右上の、ユーザー名をクリックし、「QuickSightの管理」を選択します。
次の画面で「VPC接続の管理」をクリックし、「VPC接続の追加」をクリックします。
VPC接続の追加画面で、VPC接続名、VPC ID、サブネットID、セキュリティグループIDを入力します。
入力が完了したら、「作成」をクリックします。
以上でVPC接続の追加が完了しました。
データセットの作成と分析
QuickSightコンソール画面の「データセット」タブの「新しいデータセット」をクリックし、データセットを作成します。
データセットを作成する画面で、「Redshift」クリックします。
次の画面で、データソース名を入力し、インスタンスIDで今回作成したRedshiftクラスターを選択します。
接続タイプでは、先ほど作成したVPC接続を選択し、クラスター作成時に設定したしたユーザ名とパスワードを入力します。
入力が完了したら「データソースを作成」をクリックします。
次のテーブル選択画面で今回作成したテーブルを選択します。
データセット作成の完了画面では、SPICEへインポートをチェックし、「Visualize」をクリックします。
次の画面で、実際にデータを可視化して分析を行うことができます。
以上でQuickSightへの連携が完了しました！
さいごに
今回は、Redshiftの構築からQuickSightへの連携まで行いました。
QuickSightへの連携では、プライベートサブネットに作成したRedshiftクラスターに対してVPC接続を行うため、複雑な手順が必要でした。
しかし、VPCやサブネット、セキュリティグループなど様々なサービスを触る機会となりました！