AthenaとQuicksightでログ分析する時のつまづきと解消 #AWS

Apache HTTPサーバーのアクセスログをS3に配置してAthenaでテーブル作成、QuickSightで可視化をしようと試み、つまづいたポイントと解消方法を恥ずかしげもなくメモとして残します。

サンプルのログ

下記のようなログをサンプルに試みました。

xx.xx.xx.xx - - [dd/Sep/202x:08:00:31] "POST /category.screen?categoryId=STRATEGY&JSESSIONID=xxxxx HTTP 1.1" 200 2571 "http://www.samplesample.com" "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.28) Gecko/20120306 YFF3 Firefox/3.6.28 ( .NET CLR 3.5.30729; .NET4.0C)" 830

データの構造が知りたい

まずApacheのログには何が書かれているのだろうか、データを分析しようとすると対象となるデータの構造を抑えなければなりません。何が書かれているかは最終的にはmod_log_configを確認するのが確実です。
https://httpd.apache.org/docs/2.2/ja/mod/mod_log_config.html
下記のサイトでもやさしく勉強になります。
https://beyondjapan.com/blog/2020/02/access-log/
最終的にはLogstashのGrokのパターンも理解に役立ちます。
https://github.com/elastic/logstash/blob/v1.4.2/patterns/grok-patterns
今回のケースでは一番最後のこちらです。

COMMONAPACHELOG %{IPORHOST:clientip} %{USER:ident} %{USER:auth} \[%{HTTPDATE:timestamp}\] "(?:%{WORD:verb} %{NOTSPACE:request}(?: HTTP/%{NUMBER:httpversion})?|%{DATA:rawrequest})" %{NUMBER:response} (?:%{NUMBER:bytes}|-)

SerDeとは何だ

シリアライザー、デジタライザーをSerDeと訳しています。ログがどういうフォーマット、区切りで作られているのかを識別させるために使います。
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/serde-about.html
今回は構造化されていないログの分析なので、Grok SerDeというものを使いました。
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/grok-serde.html
上記リンク先にもありますが、Grokで定義されているパターンは下記です。パターンが定義されているので便利という代物です。区切り文字だけでしたら、その他の認識すべき文字列をカスタムで定義しなければなりません。
https://github.com/elastic/logstash/blob/v1.4.2/patterns/grok-patterns

Athenaで認識したログの区切り位置が違う

Grokでの定義を見ながら、SERDEPROPERTIESをDATAのパターンを多用しながら、定義してみると悲しいかな区切り位置が違って、思っていたログの見え方がされません。""のような区切り部分は、QUOTEDSTRINGを使います。

Athenaで日時を抽出したい

AWSのApacheログをクエリするというページで、アクセス日時をStringでガツッと宣言しています。
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/querying-apache-logs.html

(略)
CREATE EXTERNAL TABLE apache_logs(
    request_received_time string,
(略)
ROW FORMAT SERDE
   'com.amazonaws.glue.serde.GrokSerDe'
WITH SERDEPROPERTIES (
  'input.format'=' (略) {GREEDYDATA:request_received_time}

これではQuickSightで日毎にデータを見たいというときに不便です。本当はログ出力をtimestamp型で認識できるフォーマットにして欲しいところではありますが、そんなときには下記でデータを分割してしまおうというアプローチもあります。無理矢理です。

CREATE EXTERNAL TABLE apache_logs_sample(
    client_ip string,
    client_id string,
    user_id string,
    request_date string,
    request_month string,
    request_year string,
    request_time string,
    client_request string,
    server_status string,
    returned_obj_size string,
    referer string,
    user_agent string,
    process_id string
) ROW FORMAT SERDE 'com.amazonaws.glue.serde.GrokSerDe' WITH SERDEPROPERTIES (
    'input.format' = '^%{IPV4:client_ip} %{DATA:client_id} %{USERNAME:user_id} \\[%{MONTHDAY:request_day}/%{MONTH:request_month}/%{YEAR:request_year}:%{TIME:request_time}\\] %{QUOTEDSTRING:client_request} %{DATA:server_status} %{DATA: returned_obj_size} %{DATA: referer} %{QUOTEDSTRING: user_agent} %{DATA: process_id}$'
) STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 's3://location/';

QuickSightで「テーブルが見つかりません」

QuickSightでデータソースとしてAthenaを選択します。しかしながらお望みのテーブルが表示されず「テーブルが見つかりません（Table not found）」なんて悲しい表示に出会ってしまうと、「なんでや」と心の呟きが止まりません。しかし忘れないで。QuickSightでも自分がどこのRegionにいるかを。S3にデータを配置したRegionにいることを確認しましょう。
参考：https://stackoverflow.com/questions/53272205/aws-quicksight-cant-see-athena-db

私の場合には初期にQuickSightを米国バージニア北部で作っていたため下記となっていました。

ここを適切なRegionを選択するとテーブルが見つかります。