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）監修のもと掲載しています。
元記事は、こちら

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

IT健保「鮨一新」食レポ

【次世代MSP】SLI / SLO を定義してみた～ New Relic でサービスの見える化に挑戦！

間接部門もデータ活用で業務効率化を推進！Looker で実現するデータドリブンな労働環境改善
〜コーポレート統括本部部長・清水那昭 × クラウドインテグレーション事業部・小林千紘インタビュー〜

AWSの生成AI活用事例集GenUを使い倒す

RHEL7を8に, 8を9にインプレースアップグレードする

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

関連記事Related Articles

祝！cloudpackブログ1000エントリー突破

cloudpack Toolsのプロキシサービスに『キャッシュを使ったコンテンツの高速配信』を追加しました

GitWebでセキュリティグループの状態を確認

DynamoDB + Data Pipeline + S3 + Google Chart API を少しずつ使ってそらまめ君で提供されている大気中の汚染物質濃度速報データを可視化するサイトを作ってみた

Check! プログラマブルLED の BlinkM で遊んでみよう！ ～ Arduino デビュー編

Check! プログラマブルLED の BlinkM で遊んでみよう！～ Arduino デビュー編