Amazon Athena のクエリ結果を SPICE にインポートして、 QuickSight で可視化する

藤本真司

2017.01.07

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

こんにちは、藤本です。

先日、QuickSight のデータセットに Amazon Athena の対応が追加されました。弊社ブログでもせーのが紹介しました。

この機能を使って、昨日、ELB のアクセスログをいくつかの視点から可視化して、解析した時の知見です。

Amazon Athena と QuickSight の連携

まず、前提として、ELB のアクセスログのような非構造化データを QuickSight で可視化したい場合、一度、構造化データにした上で、QuickSight のデータセットに取り込む必要があります。そこで AWS re:Invent 2016 でリリースされた Amazon Athena を利用することで、S3 にある非構造化データを SQL っぽくクエリでき、構造化データ（CSV）として取得することができます。この 2つを組み合わせることで非構造化データを可視化することができます。

AthenaQuickSight

ただし、Amazon Athena を可視化する場合、SPICE へのインポートがサポートされていません。QuickSight の可視化を変更する都度、Amazon Athena へのクエリが発生します。

You can improve the performance of database data sets by importing the data into SPICE instead of using a direct query to the database (with the exception of Amazon Athena data sets, which currently only support direct query) Importing Data into SPICE

昨日、ELB のアクセスログを Athena → QuickSight で可視化していた際に気になったことがいくつかありました。

前提
ELB のアクセスログ量が約5GB〜10GB/日
Athena はパーティション設定済み
クエリしたいのは一定時間内だった
色々な観点で可視化したかった
気になったこと
Amazon Athena のクエリ速度が高速とはいえ、データを変更する都度、データロードが走る QuickSight だと、若干ストレスな速度
それと同時に都度、Athena へクエリが発生し、コストが気になって、色々な可視化を試しづらい
Filter で Athena のパーティション設定を忘れると、高コスト、かなり待つ

そこで今回お伝えしたいことは S3 (非構造化) → Athena → QuickSight (可視化) よりも、S3 (非構造化) → Athena → S3 (CSVファイル) → QuickSight (SPICE) → QuickSight (可視化) が適しているケースがあるという話です。

Untitled

簡単に 2つの方式を比較してみましょう。

	Athena直接	SPICE経由
メリット	設定が簡単リアルタイムのデータを可視化	高速
デメリット	グラフを更新する度にAthenaのクエリ課金が発生	SPICE のキャパシティコストリアルタイムの可視化ができない

うーん、SPICE 経由の用途の方が少ないかな。ユースケースとしては今回のように決まった期間の可視化、パーティションを設定してもデータソースが大容量、可視化の方法が具体的に決まってなく、色々な可視化を実施したい、という障害の解析ぐらいかなー。月次のレポーティングとかも向いてそうだけど、そういう場合は決まった可視化しかしなそうなので、Athena のデータセットでも問題なさそう。

SPICE経由で可視化する

それでは S3 (非構造化) → Athena → S3 (CSVファイル) → QuickSight (SPICE) → QuickSight (可視化) の手順を試してみましょう。可視化する対象は ELB のアクセスログです。

Amazon Athena を設定する
可視化に必要なデータをクエリにより取り出す
取り出したデータを SPICE にインポートする
QuickSight で可視化する

ほとんどの単品が既にブログ化されているのでリンク集みたいになりました。

Amazon Athena を設定する

ELB のアクセスログを Athena でクエリするには下記ブログにあるCREATE TABLEでテーブルを作成できます。

Amazon AthenaでELBログをSQLで解析する

パーティション設定する場合は、PARTITIONED BY(year string, month string, day string)を加えてください。

可視化に必要なデータをクエリにより取り出す

今回、インポートしたかったデータが 10GB を超え、SPICE の単一 S3データセット 10GBまでの制限に引っかかったため、取り出すカラムを絞りました。そんなにデータ数が多くなければ、全カラムの取り出しでもいいと思います。そもそもデータ数が多くなければ、Athena をデータセットに設定してもいいかと思います。

If you are retrieving multiple files from Amazon S3, the total size of the files specified in the manifest file cannot exceed 10 GB, and the total number of files specified in the manifest file cannot exceed 100. Data Source Limits

SELECT
request_timestamp,
elb_name,
request_ip,
backend_processing_time,
elb_response_code,
received_bytes,
sent_bytes,
request_verb,
url,
protocol
FROM
xxxx_elb_log
WHERE
year='2017'
AND month='01'
AND day='01'

パーティション単位で上記クエリを 1/1、1/2、1/3 のデータに絞って取得しました。

クエリ結果の構造化データはCSVファイルとして、Query Result Location で設定した S3 のパスへ出力されます。

S3_Management_Console