Amazon AthenaでCloudFrontログを解析しようとしたらエラく苦労した話

　この2日間、さんざん悩んだので、メモメモ。

　Amazon AthenaはS3に格納したログファイルとかを、まるでRDBのようにSQLで検索・分析できる、いわゆるサーバレスなサービスです。
このAmazon Athenaで、S3→CloudFront経由でダウンロードしたファイルのアクセス数をカウントするような簡単な分析を行いたい、というのが今回の要件。CloudFrontはS3にgzip形式(拡張子:「.gz」)でアクセスログを吐くので、こいつを無加工(gzipを回答したりファイル群を結合したりせずに)で検索できるあたりがAmazon Athenaとても便利なところです。

　Amazon Athenaは割と最近リリースされたばかり(「AWS re:Invent 2016」で発表)なので、社内に知見もなく、Webの記事を参考に構築を試みました。……がこれがどエラい苦労しました。

　参考にすべきものがあって何故苦労したか？これじゃまともに動かなかったからです。各記事のテーブル作成SQLでテーブルを作ると、なぜかログファイルのレコード行が空白で取り込まれ、ヘッダー行が1カラム目がやはり取り込まれない状況でした。

　結論から言うと、以下のSQLで正しくテーブル作成ができました。カラム名を「` (アポストロフィ)」で囲む必要があったのです。今回はSQLを直接エディタに書くのではなく、コンソールからチマチマ入力してテーブルを作った際、このSQL文が生成されたことで違いに気づけました。

CREATE EXTERNAL TABLE IF NOT EXISTS cloudfront_log (
  `date` date,
  `time` string,
  `x_edge_location` string,
  `sc_bytes` int,
  `c_ip` string,
  `cs_method` string,
  `cs_host` string,
  `cs_uri_stem` string,
  `sc_status` int,
  `cs_referer` string,
  `cs_user_agent` string,
  `cs_uri_query` string,
  `cs_cookie` string,
  `x_edge_result_type` string,
  `x_edge_request_id` string,
  `x_host_header` string,
  `cs_protocol` string,
  `cs_bytes` int,
  `time_taken` int,
  `x_forwarded_for` string,
  `ssl_protocol` string,
  `ssl_cipher` string,
  `x_edge_response_result_type` string,
  `cs_protocol_version` string 
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '	',
  'field.delim' = '	'
) LOCATION 's3://sample-cloudfront-log/logs/'
TBLPROPERTIES ('has_encrypted_data'='false')

※S3バケット名は記事記述用のでたらめなものです。ご注意あれ。

　参考にした記事らは以下。参照した記事のうちのほんの一部です。

　これらの記事の内容が間違っていた、とはオレは思いません。何故なら、同時期に書かれた記事で同じようなSQL文が書かれており、実際に検証した結果の画面スクリーンショットも張り付けられていたからです。
　おそらくは、かなり直近……おそらく昨日今日のレベルで、Amazon Athenaで仕様変更があったんじゃないでしょうか？詳細は分かりませんが、今後突然CloudFrontログ分析がAmazon Athenaで行えないケースが増えそうな気がしたので、自分用のメモを公開しておきます。

※何か誤りあればご指摘頂けますと大変助かります。

ミッションたぶんPossible

どこにでもいるシステムエンジニアのなんでもない日記です。たぶん。

Amazon AthenaでCloudFrontログを解析しようとしたらエラく苦労した話