仕事でALBのログを解析する必要が出てきました。
その時にAthenaとBigQueryの両方でログ解析をしてみて使い勝手の比較をしたので、そのまとめです。

解析対象のログ

S3に保存されている、2019年4月分のALBアクセスログ
gz圧縮済みのデータ量: 239GB

参考: https://docs.aws.amazon.com/elasticloadbalancing/latest/application/load-balancer-access-logs.html

解析内容

特定のエンドポイントに対して、1秒あたりのアクセス回数が最も多かった時刻とその時の回数を求める。

比較項目

今回は以下の3つの環境でクエリを投げて比較しました。

• Athena(ALBが吐き出したままの形式のログ)
• Athena(ログをParquet形式に変換)
• BigQuery

以下のドキュメントによると、データ形式を列指向のものに変換するとパフォーマンスが上がるようです。
Athenaで使うことが出来る列指向フォーマットにはParquetとORCがあるようですが、Parquetの方がオススメされていたので、Parquet形式に変換した場合の性能を検証しました。

また、以下の項目を比較しました。
クエリの実行そのもの時間・費用だけではなく、準備(SELECT文を実行できるようにするまで）の手間や費用も比較したいと思います。

• 準備にかかる手間
• 準備にかかる費用
• クエリ実行時間
• クエリ実行費用

手順

Athena(ALBが吐き出したままの形式のログ)

準備

以下のサンプルのCREATE TABLE文を参考にAthenaにテーブルを作成します。
自分で一から書けと言われたらそこそこ時間がかかりそうな正規表現をawsが提供してくれているので楽です。
最終行のLOCATIONだけは各自で書き換える必要があります。
さらに、今回はLOCATIONの末尾に2019/04を付与することによって2019年4月のみを対象にしたテーブルを作成しました。

参考: Querying Application Load Balancer Logs

クエリ投げてみる

以下のクエリを投げることでログ分析を行いました。

select time, count(*) as count from (
  select substr(time, 1, 19) as time from alb_logs_201904 where request_url like '%調査対象のURL%' 
) group by time order by count desc

238.9GBのデータをスキャンして、2:14の時間がかかりました。
Athenaの料金は1TBあたり5USDなので、1.19USDの費用がかかりました。

Athena(ログをParquet形式に変換)

準備

昔はEMRクラスターを立ち上げる必要があったようですが、create table as文がサポートされたことにより、Parquet形式への変換を簡単に行うことが出来るようになりました。
参考: Amazon Athena が SELECT クエリの結果を使用して CREATE TABLE (CTAS) のサポートを追加

以下のクエリを実行するだけで、Parquet形式でのテーブルを作成することが出来ます。

create table alb_logs_parquet_201904 as
select * from alb_logs_201904

参考: CREATE TABLE AS

238.9GBのデータをスキャンして、23:41の時間がかかりました。

この方法で作成されたテーブルに対してshow create tableを実行したところ、以下のような結果が返ってきました。
create table as文はクエリの実行結果をS3に保存し、そのデータを対象としたテーブルを作成しているのだろうと予測できます。

CREATE EXTERNAL TABLE `alb_logs_perquet_201904`(
  `type` string COMMENT '', 
  <略>
  `new_field` string COMMENT '')
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  's3://略'
TBLPROPERTIES (
  'has_encrypted_data'='false')

クエリ投げてみる

クエリは先程のものとほぼ同じです。
テーブル名だけを変えました。

select time, count(*) as count from (
  select substr(time, 1, 19) as time from alb_logs_parquet_201904 where request_url like '%調査対象のURL%' 
) group by time order by count desc

98.33GBのデータをスキャンして、0:38の時間がかかりました。
Parquetに変換せずにクエリを投げた場合と比較すると、59%のデータ量を削減でき、72%の処理時間を削減出来ました。
利用料金も削減され、0.49USDになりました。

BigQuery

準備

BigQueryで解析を行うためには、ログデータをawsからGCPに転送する必要があります。
手元のMacBookProでembulkを使って転送できないか試してみたところ、ログの量が数百GBにもなるため、筐体が熱々になったので、途中で諦めました。

なんか他の解決策がないかと思い探していたら、2019年4月からBigQuery Data Transfer ServiceがS3に対応していたので、これを使うことにしました。
※ 2019/05/16時点ではまだベータ版なので注意。

参考: Overview of Amazon S3 transfers

この機能を使うためには先に空のテーブルを作成する必要があります。
以下のドキュメントを参考にして、テーブルのスキーマを決定します。

Access Logs for Your Application Load Balancer

以下のスキーマで空テーブルを作成しました。

type:STRING,
time:STRING,
elb:STRING,
client_ip_port:STRING,
backend_ip_port:STRING,
request_processing_time:FLOAT,
target_processing_time:FLOAT,
response_processing_time:FLOAT,
elb_status:STRING,
target_status_code:STRING,
received_bytes:INTEGER,
send_bytes:INTEGER,
request:STRING,
user_agent:STRING,
ssl_cipher:STRING,
ssl_protocol:STRING,
target_group_arn:STRING,
trace_id:STRING,
domain_name:STRING,
chosen_cert_arn:STRING,
matched_rule_priority:STRING,
request_creation_time:STRING,
actions_executes:STRING,
redirect_url:STRING,
lambda_error_reason:STRING

また、ログが置かれているS3バケットに対する読み取り権限のあるIAMユーザーも必要なので、それを作成しアクセスキーとシークレットアクセスキーをメモっておきます。

次にBigQuery Data Transfer Serviceを利用したデータの転送を設定します。
入力にあたって基本的に迷う部分はありません。
File formatはCSV、Field delimiterは(半角空白)を指定します。

select time, count(1) as count from (
  select substr(time, 1, 19) as time from `テーブル名` where request like '%調査したいURL%'
) group by time order by count desc