QuickSightのデータソースをMySQLからAthena+Amazon S3に変更してみた

前回の記事でQuickSightを使って住所コード一覧の情報をグラフ化してみました。

前回の記事→社内データのBIツールとして、AMAZON QUICKSIGHTを使ってみた話。

極力既存のナレッジが利用できるように、データソースにはMySQLを指定していました。が、しかし、BIのためにわざわざEC2インスタンスをたてて運用するのもなぁと思ってきたので、データソースをMySQLからAthena＋Amazon S3へ移行してみました。図のようなイメージです。

今回は、QuickSightのデータソースをAthena+S3にする方法を紹介します。Athenaは、Amazon S3内のデータを標準的なSQLを使用して検索できるサービスです。SQLが利用できるので、既存ナレッジの活用ができつつ、データ置き場をS3とするためEC2インスタンスの運用が不要になる素敵な構成です。

S3の設定

S3のバケットを作成します。

aws s3 mb s3://denet-blog-zip

そのうえで住所コード一覧のcsvファイルをアップロードします。ファイルは下記よりダウンロードしており、解凍後zip.csvとリネイムしています。

全国一括のデータファイル:https://www.post.japanpost.jp/zipcode/dl/roman-zip.html

aws s3 cp zip.csv s3://denet-blog-zip/zip/
aws s3 ls s3://denet-blog-zip/zip/

念のためマネジメントコンソールからも確認してみます。

無事にアップロードされていそうです。

のちほど、Athenaでアップロードしたファイルをテーブルに見立てて利用することになるのですが、「テーブル」＝「フォルダ」として扱うことになります。そのため、「zip」フォルダを作成し、その中にファイルをアップロードしました。フォルダ内に複数ファイルが存在している場合は、自動でマージしてくれるので、定期的にファイルアップロードするだけでデータの追加が可能です。

Athenaの設定

テーブル定義

次にAthenaの設定を行っていきます。まずはじめにテーブル定義を行う必要があります。

「Data Sources」の「connect data sources」から作成していきます。図のように進めていきます。（見にくいので別タブで画像を開いてもらえると良いかと思います）

データソースにS3を設定して、データベース名、テーブル名は適当に設定します。データソースのロケーションにS3の対象フォルダを設定します。今回は、「s3://denet-blog-zip/zip/」を設定しました。

DataFormatにcsvを設定します。列定義の設定を適宜行います。今回は「Bulk add columns」から一括登録を行います。

zip string,pref string,city string,town string,pref_r string,city_r string,town_r string

以下クエリの実行でも作成が可能です。

CREATE EXTERNAL TABLE IF NOT EXISTS zip.zip (
  `zip` string,
  `pref` string,
  `city` string,
  `town` string,
  `pref_r` string,
  `city_r` string,
  `town_r` string 
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = ',',
  'field.delim' = ','
) LOCATION 's3://denet-blog-zip/zip/'
TBLPROPERTIES ('has_encrypted_data'='true');

クエリ時の実行

試しにクエリを実行してみましょう。クエリの実行は「QueryEditor」から行います。Reusltsに「124433」が表示されれば成功です。

select count(*) from zip;

Athenaでは、SQL文中にダブルクォーテーションは利用できないようです。固定文字列を使う場合はシングルクォーテーションを利用しましょう。

複数テーブル用意して結合もできるので、普通のデータベース感覚で扱えます。

QuickSightの設定

Amazon S3とAthenaの設定ができたので、QuickSightの設定を行っていきます。

データセットの設定

新規データソースに「Athena」を選択します。Athenaで作成したデータベース「zip」を選択して「カスタムSQLを使用」します。QuickSightから実行する場合は、テーブル名の前にデータベース名を指定してあげる必要があるようです。

select pref,city from zip.zip group by pref,city;

おそらく最初はSQL例外のエラーが発生します。

Athenaへの接続許可設定

QuickSightからAthenaへの接続許可を入れる必要があります。右上の「QuickSightの管理」を選択し、「セキュリティとアクセス権限」の設定を行います。「QuickSight の AWS のサービスへのアクセス」には、すでにAthenaが追加されていますが、一度削除を行います。削除後に再設定すると、データベースを選ぶことができるので、今回作成したデータベースを選択します。

データセットの設定　その２

データソースの設定を実行してみると、データ取得が成功するはずです。

分析の実施

データの取得ができたら、通常通り分析の実施が可能です。

まとめ

QuickSightのデータソースをMySQLからAthena＋Amazon S3へ変更してみました。S3に対してSQLが利用できるので、意外と敷居は低いと感じました。

BI用途のためにEC2インスタンスの管理をするのは手間なので、S3をデータソースにできることはすごくメリットがありそうです。