EMRってなんじゃ？（HiveでS3のログをJSTに変換して1日分をまとめる）

S3のwebホスティングで、ログ出力の設定をしていた場合、ログファイルが大量に出力されます。
その際、ログの記録時間は標準時で出力されており、わかりづらく思っていました。
そこで今回はEMRのHiveを利用し、日本時間の0時～翌日の0時までのログを1ファイルにまとめてみたいと思います。

○HiveScript

HiveScriptは以下のようにします。
まずフォーマット解析用のJARを読み込みます。

add jar /home/hadoop/hive/contrib/hive-contrib-0.8.1.jar;

入力用のテーブルを定義します。
LOCATIONは引数から受け取ります。ネックとなるのは、S3のログは基本的に半角スペース区切りですが、項目の値の中に半角スペースが入り込むため、
「FIELDS TERMINATED BY」句ではなく、SERDEを使用して正規表現でログの1行を項目分けします。
今回は日付フィールドはそのまま年月日と時間部分に別けるしまいます。

CREATE EXTERNAL TABLE IF NOT EXISTS log (
 bucket_owner string,
 bucket_name string,
 log_time string,
 log_timezone string,
 remote_ip string,
 requester string,
 request_id string,
 operation string,
 key string,
 request_uri string,
 http_status string,
 error_code string,
 bytes_sent string,
 object_size string,
 total_time string,
 turn_around_time string,
 referer string,
 user_agent string,
 version_id string
) ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  "input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ "]*|"[^"]*") ([^ ]*) ([^ ]*) (-|[0-9]*) (-|[0-9]*) (-|[0-9]*) (-|[0-9]*) ([^ ]*) ([^ "]*|"[^"]*") ([^ ]*)",
      "output.format.string" = "%1$s %2$s %3$s %4$s %5$s %6$s %7$s %8$s %9$s %10$s %11$s %12$s %13$s %14$s %15$s %16$s %17$s %18$s %19$s"
)
LOCATION '${INPUT_BUCKET_LOCATION}';

出力用のテーブルを定義します。
LOCATIONは引数から取得します。
パーティションはyyyy,mm,ddという単位で分割します。

CREATE EXTERNAL TABLE IF NOT EXISTS log_archive (
 bucket_owner string, 
 bucket_name string, 
 log_time string,  
 remote_ip string, 
 requester string, 
 request_id string, 
 operation string, 
 key string, 
 request_uri string, 
 http_status string, 
 error_code string, 
 bytes_sent string, 
 object_size string, 
 total_time string, 
 turn_around_time string, 
 referer string, 
 user_agent string, 
 version_id string
 )
 PARTITIONED BY (yyyy string, mm string, dd string)
 ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n' STORED AS TEXTFILE LOCATION '${OUTPUT_BUCKET_LOCATION}'; 

集計設定をします。
DynamicPartitionを無効にし、出力をgz圧縮します。

set hive.exec.dynamic.partition= false;
set hive.exec.compress.output = true;

集計します。
対象日のYYYY、MM、DDを引数から受け取ります。
ポイントとなるのは日付の扱いで、日時分割した年月日の方のカラムを0:00:00としてタイムスタンプに変換し
JST→UTC変換したもので当日分のログとしてフィルタしています。

INSERT INTO TABLE log_archive PARTITION (yyyy='${YYYY}', mm='${MM}', dd='${DD}')
SELECT
 bucket_owner,
 bucket_name,
 concat("[", from_utc_timestamp(from_unixtime(unix_timestamp(concat(log_time, " ", log_timezone), '[dd/MMM/yyyy:HH:mm:ss Z]')), 'JST')," +0900]") as jsttime,
 remote_ip,
 requester,
 request_id,
 operation,
 key,
 request_uri,
 http_status,
 error_code,
 bytes_sent,
 object_size,
 total_time,
 turn_around_time,
 referer,
 user_agent,
 version_id
 FROM
 log
 WHERE
 unix_timestamp(concat(log_time, " ", log_timezone), '[dd/MMM/yyyy:HH:mm:ss Z]') >= unix_timestamp(to_utc_timestamp(concat('${YYYY}-${MM}-${DD}', ' 00:00:00'), 'JST')) 
AND
 unix_timestamp(concat(log_time, " ", log_timezone), '[dd/MMM/yyyy:HH:mm:ss Z]')  SORT BY
 jsttime
;

○実行

Hiveジョブフローの主な起動設定などは以下の通りです。
Extra ArgsでHiveスクリプトに渡す引数を設定しています。

• Script Location: s3n://memorycraft-script/hive/s3_daily.hql
• Input Location: s3n://memorycraft-logs/log/
• Output Location: s3n://memorycraft-archive/rslt/
• Extra Args: -d INPUT_BUCKET_LOCATION=s3n://memorycraft-log/logs/ -d OUTPUT_BUCKET_LOCATION=s3n://memorycraft-archive/rslt/ -d YYYY=2013 -d MM=03 -d DD=20

実行すると以下のように出力されます。

中身を見ると以下のように1日分がまとまっています。
また、日時の部分はJSTに変換して出力されています。

b00fb3b3fbeb37e2dc44f010cdbeff2c31bc467a2022f00401bf18abbf9e4543,myfirst-bucket,[2013-03-20 01:29:44 +0900],10.115.82.47,arn:aws:iam::821635308497:user/iam-user,52A74398D7C4DBFE,REST.GET.VERSIONING,-,"GET /myfirst-bucket?versioning HTTP/1.1",200,-,162,-,28,-,"-","S3Console/0.4",-
b00fb3b3fbeb37e2dc44f010cdbeff2c31bc467a2022f00401bf18abbf9e4543,myfirst-bucket,[2013-03-20 01:29:44 +0900],10.115.82.47,arn:aws:iam::821635308497:user/iam-user,7CB888CE4F00C988,REST.GET.BUCKET,-,"GET /myfirst-bucket?prefix=&max-keys=100&marker=&delimiter=/ HTTP/1.1",200,-,1254,-,1044,1043,"-","S3Console/0.4",-
b00fb3b3fbeb37e2dc44f010cdbeff2c31bc467a2022f00401bf18abbf9e4543,myfirst-bucket,[2013-03-20 01:29:44 +0900],10.15.128.6,arn:aws:iam::821635308497:user/iam-user,473043B904924EB1,REST.GET.LOCATION,-,"GET /myfirst-bucket?location HTTP/1.1",200,-,142,-,29,-,"-","S3Console/0.4",-
b00fb3b3fbeb37e2dc44f010cdbeff2c31bc467a2022f00401bf18abbf9e4543,myfirst-bucket,[2013-03-20 01:29:44 +0900],10.15.149.57,arn:aws:iam::821635308497:user/iam-user,E1A726EAAA9ED195,REST.GET.LOCATION,-,"GET /myfirst-bucket?location HTTP/1.1",200,-,142,-,47,-,"-","S3Console/0.4",-
b00fb3b3fbeb37e2dc44f010cdbeff2c31bc467a2022f00401bf18abbf9e4543,myfirst-bucket,[2013-03-20 01:29:49 +0900],10.115.82.47,arn:aws:iam::821635308497:user/iam-user,648FA2CF0669ED48,REST.GET.VERSIONING,-,"GET /myfirst-bucket?versioning HTTP/1.1",200,-,162,-,26,-,"-","S3Console/0.4",-
b00fb3b3fbeb37e2dc44f010cdbeff2c31bc467a2022f00401bf18abbf9e4543,myfirst-bucket,[2013-03-20 01:29:49 +0900],10.115.82.47,arn:aws:iam::821635308497:user/iam-user,4620CF20BB92DE10,REST.GET.BUCKET,-,"GET /myfirst-bucket?prefix=&max-keys=100&marker=&delimiter=/ HTTP/1.1",200,-,1254,-,601,600,"-","S3Console/0.4",-
b00fb3b3fbeb37e2dc44f010cdbeff2c31bc467a2022f00401bf18abbf9e4543,myfirst-bucket,[2013-03-20 01:29:51 +0900],10.115.82.47,arn:aws:iam::821635308497:user/iam-user,AD489CC457FB430B,REST.GET.ACL,-,"GET /myfirst-bucket?acl HTTP/1.1",200,-,1317,-,406,-,"-","S3Console/0.4",-
b00fb3b3fbeb37e2dc44f010cdbeff2c31bc467a2022f00401bf18abbf9e4543,myfirst-bucket,[2013-03-20 01:29:51 +0900],10.115.144.24,arn:aws:iam::821635308497:user/iam-user,700100DED6F992D5,REST.GET.NOTIFICATION,-,"GET /myfirst-bucket?notification HTTP/1.1",200,-,115,-,21,-,"-","S3Console/0.4",-
b00fb3b3fbeb37e2dc44f010cdbeff2c31bc467a2022f00401bf18abbf9e4543,myfirst-bucket,[2013-03-20 01:29:51 +0900],10.115.144.24,arn:aws:iam::821635308497:user/iam-user,EEF9831033863E2B,REST.GET.LOGGING_STATUS,-,"GET /myfirst-bucket?logging HTTP/1.1",200,-,932,-,414,-,"-","S3Console/0.4",-

~~中略~~

+0900],10.89.198.21,b00fb3b3fbeb37e2dc44f010cdbeff2c31bc467a2022f00401bf18abbf9e4543,139A543A03AB1E01,REST.GET.ACL,-,"GET /?acl=&x-amz-security-token=AQYGQXBwVGtubLMUm1%2BEi%2BJ69YRAEYru2QGPPayzdgIKktgcIhBULb3hBbeFaN3DjPydiCyvDuvQ7g4kucsy0nQsWnAMBR2zfK1R%2B7Y7%2BdVgxVeH2fVJ9FQLFTkg2kkCaVj%2Fj%2FEWi8r%2F1hShQ4prv8OLBfDFETmtdw%3D%3D HTTP/1.1",200,-,1317,-,14,-,"-","aws-sdk-java/unknown-version Linux/2.6.18-194.17.4.el5.acc4xen Java_HotSpot(TM)_Server_VM/20.12-b01",-
b00fb3b3fbeb37e2dc44f010cdbeff2c31bc467a2022f00401bf18abbf9e4543,myfirst-bucket,[2013-03-20 17:36:44 +0900],10.89.198.21,b00fb3b3fbeb37e2dc44f010cdbeff2c31bc467a2022f00401bf18abbf9e4543,5FA69CCF9D1F8ABF,REST.GET.BUCKET,-,"GET /?max-keys=0&x-amz-security-token=AQAGQXBwVGtu2GApXRs%2FDi9mf5qWz55MQRSyS9v%2FnmGvqPo7lsP%2BAkR1V57UhWpJlAn6tYGp2tOL%2BC6Ai1BrRVOWUdUp6JkO%2FMqep7zd4h%2B5xJP8HtcO6pEWEV6t%2BikUh0qYfG8TatCWKvh15j6qu3XExYR5fnveRw%3D%3D HTTP/1.1",200,-,237,-,19,19,"-","aws-sdk-java/unknown-version Linux/2.6.18-194.17.4.el5.acc4xen Java_HotSpot(TM)_Server_VM/20.12-b01",-
b00fb3b3fbeb37e2dc44f010cdbeff2c31bc467a2022f00401bf18abbf9e4543,myfirst-bucket,[2013-03-20 17:36:44 +0900],10.89.198.21,b00fb3b3fbeb37e2dc44f010cdbeff2c31bc467a2022f00401bf18abbf9e4543,EB88F164094C5ECB,REST.GET.ACL,-,"GET /?acl=&x-amz-security-token=AQEGQXBwVGtuw71vZ%2F%2BhXOKS3VoBVeYAGzc2Csc0R73DDmsCndxf9cMwWqyG9fGRYM%2F%2BJVpuKk4gdS%2Ftr64M7O2VA%2BmzWfPUz8eSwYA3zvEmdBvC8JJrDlvDImPfhqi8mXHF5weAqj2byecuDWLTqJ7AQZBtY3b8dQ%3D%3D HTTP/1.1",200,-,1317,-,26,-,"-","aws-sdk-java/unknown-version Linux/2.6.18-194.17.4.el5.acc4xen Java_HotSpot(TM)_Server_VM/20.12-b01",-

以上で、日本時間でのアクセス集計ができました。

こちらの記事はなかの人（memorycraft）監修のもと掲載しています。
元記事は、こちら