filename-yyyymmddの形式で退避したS3をAthenaで日付条件クエリ

はじめに

OSのログをS3へ退避する際、
とりあえず貯めとけ精神でOS上でログローテされたまま保存しますよね。

そうすると後々、
ある月のログに対してAthenaでクエリかけたい...ってときに苦しみますよね。

でもHive形式に作り替えるのも面倒ですよね。
※Athenaでのパーティション分割：https://docs.aws.amazon.com/ja_jp/athena/latest/ug/partitions.html

そんなときは、検索に時間かけても良いからゴリ押しでクエリをかけましょう。

#0. S3の構造

• バケットURL：s3://secure-log-athena-test/secure/
• オブジェクト：

secure
secure-20210701
secure-20210702
secure-20210703
secure-20210704
secure-20210705
secure-20210706
secure-20210707
・・・

#1. サンプルDDL

• テーブル名, 'バケット名URI' 部分は各S3バケットのURIに置き換えてください。

CREATE EXTERNAL TABLE `テーブル名`(
  `month` string COMMENT '',
  `day` string COMMENT '',
  `time` string COMMENT '',
  `host` string COMMENT '',
  `cmd` string COMMENT '',
  `user` string COMMENT '',
  `etc` string COMMENT '')
ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  'input.regex'='^(\\S+) +(\\S+) +(\\S+) +(\\S+) +(\\S+) +(\\S+) +(.+)$')
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'バケット名URI'
TBLPROPERTIES (
  'has_encrypted_data'='false',
  'transient_lastDdlTime'='1622793925')

2. クエリ

• "DB名", "テーブル名" 部分は各S3バケットのURIに置き換えてください。

SELECT *
FROM "DB名"."テーブル名"
WHERE "$path" LIKE '%secure-202107%'
        AND cmd LIKE '%sudo%'
        AND user NOT LIKE '%pam_unix%'; 

おわりに

・運用等で定期的に検索をかける必要がある
・クエリに時間をかけたくない(お金もかけたくない)
そんなときは腹を括ってS3の構造と退避スクリプトに向き合いましょう。