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

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

はじめに

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

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

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

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

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

$ aws s3 ls s3://mybucket/devio2017-handson/07-elblogs/ --recursive
2017-08-21 12:42:42   11789573 devio2017-handson/07-elblogs/2015/01/01/part-r-00000-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt
2017-08-21 12:42:45    9012723 devio2017-handson/07-elblogs/2015/01/02/part-r-00006-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt
2017-08-21 12:42:48   11360522 devio2017-handson/07-elblogs/2015/01/03/part-r-00012-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt
2017-08-21 12:42:50   11899582 devio2017-handson/07-elblogs/2015/01/03/part-r-00017-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt
2017-08-21 12:42:53    8321364 devio2017-handson/07-elblogs/2015/01/04/part-r-00023-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt
2017-08-21 12:42:55    7864475 devio2017-handson/07-elblogs/2015/01/05/part-r-00029-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt
2017-08-21 12:42:56    9148117 devio2017-handson/07-elblogs/2015/01/06/part-r-00035-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt
2017-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アクセスログを例に自動化の手順を解説します。なお、awsclijqについては事前にインストールしてください。

例.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

# Setting
dbname=default
tablename=access_logs
s3bucket=mybucket
s3prefix=devio2017-handson/07-elblogs
#profile="--profile=example_profile"

# get paths
paths=$(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 ddl
for path in ${paths}
do
  year=$(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}/';"
done

exit 0

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

$ ./accesslog_partitioner.sh
ALTER 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

# Setting
dbname=default
tablename=access_logs
s3bucket=mybucket
s3prefix=devio2017-handson/07-elblogs
outputlocation=s3://s3-staging-dir/tmp/
#profile="--profile=example_profile"

MAX_RETRY=30
FETCH_INTERVAL_SECONDS=1

# get paths
paths=$(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 ddl
for path in ${paths}
do
  year=$(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=1
  while true
  do
    if [ ${try_cnt} -ge ${MAX_RETRY} ] ; then
      echo "Error: timeout" >&2
      break
    else
      sleep ${FETCH_INTERVAL_SECONDS}
    fi

    query_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' ] ; then
      echo "${query_state}"
      break
    elif [ ${query_state} = 'FAILED' ] ; then
      echo "${query_state}" >&2
      break
    fi

    try_cnt=$(expr ${try_cnt} + 1)
  done
done

exit 0

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

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

最後に

Amazon Athena とブログタイトルに書きましたが、テーブル定義(メタデータ)の登録先は Glue Data Catalog ですので、Amazon Redshift Spectrum、Amazon EMR、AWS Glueにおいても同様に利用可能です。

参考

Webアクセスログ、CloudFrontのログ、CloudFrontのログに関するテーブル定義につきましては、以下のブログを御覧ください。

Amazon Athena RegexSerDe を利用して CLB ログ / Apache Web のアクセスログを探索する

Amazon Athena RegexSerDe を利用して ALB ログを探索する

Amazon Athena CloudTrailSerdeを利用してAWSのアクティビティを探索する

新機能 AWSCLIから Amazon Athena のクエリを実行する