Amazon Athena Webアクセスログ、CloudFrontのログ、CloudTrailのログのパーティション設定を自動化する

AWS CloudTrail Amazon CloudFront Amazon Athena AWS

2018.04.03

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

はじめに

Webアクセスログ（ELB）、CloudFrontのログ、CloudTrailのログは、YYYY/MM/DD形式のフォルダの下に自動的に保存されます。Amazon Athenaは、S3上のログファイルに対してテーブル定義して、クエリを実行できます。しかし、大量のログファイルを高速かつコスト効率よくクエリするにはパーティションを設定する必要があります。今回はこの煩雑なパーティション設定を自動化する方法をご紹介します。

カラム名あり（Hive互換）とカラム名なしのパーティション

パーティションは、カラム名あり（Hive互換）パーティションとカラム名なしパーティションの２つに分類されます。

AWS Black Belt Online Seminar 2017 Amazon Athena fromAmazon Web Services Japan

カラム名あり（Hive互換）パーティションは、フォルダ名がキーバリュー形式になっており、例えばyear=YYYY/month=MM/day=DD形式で保存されています。この形式はMSCK REPIRE TABLEを実行するとフォルダを再帰的にスキャンして、パーティションを自動設定できます。

一方、カラム名なしパーティションは、YYYY/MM/DD形式のフォルダの下に自動的に保存されます。

$ aws s3 ls s3://mybucket/devio2017-handson/07-elblogs/ --recursive2017-08-21 12:42:42 11789573 devio2017-handson/07-elblogs/2015/01/01/part-r-00000-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt2017-08-21 12:42:45 9012723 devio2017-handson/07-elblogs/2015/01/02/part-r-00006-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt2017-08-21 12:42:48 11360522 devio2017-handson/07-elblogs/2015/01/03/part-r-00012-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt2017-08-21 12:42:50 11899582 devio2017-handson/07-elblogs/2015/01/03/part-r-00017-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt2017-08-21 12:42:53 8321364 devio2017-handson/07-elblogs/2015/01/04/part-r-00023-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt2017-08-21 12:42:55 7864475 devio2017-handson/07-elblogs/2015/01/05/part-r-00029-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt2017-08-21 12:42:56 9148117 devio2017-handson/07-elblogs/2015/01/06/part-r-00035-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt2017-08-21 12:43:00 10019678 devio2017-handson/07-elblogs/2015/01/07/part-r-00041-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt：

Webアクセスログ（ELB）、CloudFrontのログ、CloudFrontのログ、Amazon Kinesis Data Firehoseなどはこの形式で保存されます。この形式はALTER TABLE ADD PARTITIONをパーティションごとに設定しなければなりません。この数が数百〜数万となると手作業では不可能です。以降では、ApacheのWebアクセスログを例に自動化の手順を解説します。なお、awscliとjqについては事前にインストールしてください。

例.ApacheのWebアクセスログのテーブルに対してパーティション設定する

テーブル定義は、以前紹介したAmazon Athena RegexSerDe を利用して CLB ログ / Apache Web のアクセスログを探索するのとおりです。年月日（year, month, day）にてパーティション設定しています。

CREATE EXTERNAL TABLE IF NOT EXISTS access_logs (request_timestamp string,elb_name string,client_addrport string,client_ip string,client_port int,backend_addrport string,backend_ip string,backend_port int,request_processing_time decimal(8,6),backend_processing_time decimal(8,6),response_processing_time decimal(8,6),elb_status_code string,backend_status_code string,received_bytes int,sent_bytes int,request string,user_agent string,ssl_cipher string,ssl_protocol string)PARTITIONED BY (year string,month string,day string)ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'WITH SERDEPROPERTIES ('serialization.format' = '1','input.regex' = '([^ ]*) ([^ ]*) (([0-9.]*):([0-9]*)|-) (([0-9.]*):([0-9]*)|-) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\\"[^\\"]*\\") (\\"[^\\"]*\\") ([^ ]*) ([^ ]*)[ ]*$')LOCATION 's3://mybucket/devio2017-handson/07-clblogs'TBLPROPERTIES ('has_encrypted_data'='false');

パーティション設定を自動生成する

では、どうやってパーティション設定を自動生成するかというと、MSCK REPIRE TABLEと同様にS3をスキャンして、ALTER TABLE ADD PARTITIONを自動生成します。自動生成した結果からピンポイントでパーティション設定したい場合などは、こちらのほうが使いやすいと思います。

dbname: dbnameを指定します
tablename: tablenameを指定します
s3bucket: データが保存されているバケットを指定します
s3prefix: フォルダのパスを指定します
profile: プロファイルを指定します（オプション）

#!/bin/sh# Settingdbname=defaulttablename=access_logss3bucket=mybuckets3prefix=devio2017-handson/07-elblogs#profile="--profile=example_profile"# get pathspaths=$(aws s3api list-objects --bucket ${s3bucket} --prefix ${s3prefix} ${profile} | grep -v '\$folder\$' | jq ".Contents[].Key" | awk -v FS='/' -v OFS='/' '{print $(NF-4), $(NF-3), $(NF-2), $(NF-1)}' | awk '!a[$0]++' | sort)# generate ddlfor path in ${paths}doyear=$(echo ${path} | awk -v FS='/' -v OFS='/' '{print $2}')month=$(echo ${path} | awk -v FS='/' -v OFS='/' '{print $3}')day=$(echo ${path} | awk -v FS='/' -v OFS='/' '{print $4}')echo "ALTER TABLE ${dbname}.${tablename} ADD PARTITION (year='${year}',month='${month}',day='${day}') LOCATION 's3://${s3bucket}/${s3prefix}/${year}/${month}/${day}/';"doneexit 0

上記のスクリプトを実行すると、以下のように自動生成されます。Athenaのコンソールは、複数のクエリをまとめて実行できないので、SQL WorkbenchなどのツールからSQL実行することになります。

$ ./accesslog_partitioner.shALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='01') LOCATION 's3://mybucket/07-elblogs/2015/01/01/';ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='02') LOCATION 's3://mybucket/07-elblogs/2015/01/02/';ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='03') LOCATION 's3://mybucket/07-elblogs/2015/01/03/';ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='04') LOCATION 's3://mybucket/07-elblogs/2015/01/04/';ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='05') LOCATION 's3://mybucket/07-elblogs/2015/01/05/';ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='06') LOCATION 's3://mybucket/07-elblogs/2015/01/06/';ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='07') LOCATION 's3://mybucket/07-elblogs/2015/01/07/';：

パーティション設定を自動設定する

完全に自動実行したい場合は、以下のようにawscliでDDLを同期的に実行します。(同期的に実行する方法はAWS CLI で Athena のクエリ実行を同期的に行うを参考にさせていただきました)

dbname: dbnameを指定します
tablename: tablenameを指定します
s3bucket: データが保存されているバケットを指定します
s3prefix: フォルダのパスを指定します
outputlocation: 実行結果を保存するディレクトリを指定します
profile: プロファイルを指定します（オプション）

#!/bin/sh# Settingdbname=defaulttablename=access_logss3bucket=mybuckets3prefix=devio2017-handson/07-elblogsoutputlocation=s3://s3-staging-dir/tmp/#profile="--profile=example_profile"MAX_RETRY=30FETCH_INTERVAL_SECONDS=1# get pathspaths=$(aws s3api list-objects --bucket ${s3bucket} --prefix ${s3prefix} ${profile} | grep -v '\$folder\$' | jq ".Contents[].Key" | awk -v FS='/' -v OFS='/' '{print $(NF-4), $(NF-3), $(NF-2), $(NF-1)}' | awk '!a[$0]++' | sort)# generate ddlfor path in ${paths}doyear=$(echo ${path} | awk -v FS='/' -v OFS='/' '{print $2}')month=$(echo ${path} | awk -v FS='/' -v OFS='/' '{print $3}')day=$(echo ${path} | awk -v FS='/' -v OFS='/' '{print $4}')echo "ALTER TABLE ${dbname}.${tablename} ADD PARTITION (year='${year}',month='${month}',day='${day}') LOCATION 's3://${s3bucket}/${s3prefix}/${year}/${month}/${day}/';"query_execution_id=$(aws athena start-query-execution --query-string "ALTER TABLE ${dbname}.${tablename} ADD PARTITION (year='${year}',month='${month}',day='${day}') LOCATION 's3://${s3bucket}/${s3prefix}/${year}/${month}/${day}/';" --result-configuration OutputLocation="${outputlocation}" ${profile} | jq -r '.QueryExecutionId')try_cnt=1while truedoif [ ${try_cnt} -ge ${MAX_RETRY} ] ; thenecho "Error: timeout" >&2breakelsesleep ${FETCH_INTERVAL_SECONDS}fiquery_execution_result=$(aws athena get-query-execution --query-execution-id ${query_execution_id} ${profile})query_state=$(echo ${query_execution_result} | jq -r '.QueryExecution.Status.State')if [ ${query_state} = 'SUCCEEDED' ] ; thenecho "${query_state}"breakelif [ ${query_state} = 'FAILED' ] ; thenecho "${query_state}" >&2breakfitry_cnt=$(expr ${try_cnt} + 1)donedoneexit 0

実行すると、DDLと実行結果（SUCCEEDED、FAILED,Error: timeout）を表示します。すでにパーティションが登録済みの場合は、FAILEDになります。

$ ./accesslog_partitioner.shALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='01') LOCATION 's3://mybucket/devio2017-handson/07-elblogs/2015/01/01/';SUCCEEDEDALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='02') LOCATION 's3://mybucket/devio2017-handson/07-elblogs/2015/01/02/';SUCCEEDEDALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='03') LOCATION 's3://mybucket/devio2017-handson/07-elblogs/2015/01/03/';SUCCEEDEDALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='04') LOCATION 's3://mybucket/devio2017-handson/07-elblogs/2015/01/04/';SUCCEEDEDALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='05') LOCATION 's3://mybucket/devio2017-handson/07-elblogs/2015/01/05/';SUCCEEDEDALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='06') LOCATION 's3://mybucket/devio2017-handson/07-elblogs/2015/01/06/';SUCCEEDEDALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='07') LOCATION 's3://mybucket/devio2017-handson/07-elblogs/2015/01/07/';SUCCEEDED：