S3バケット内のファイル一覧(S3 Inventory)をAthenaを利用して分析する

スタートプラン

西澤です。お客様よりお問い合わせをいただき、数千万単位のオブジェクトを配置したS3バケット内に、特定の拡張子のオブジェクトが何点存在するかを簡単に確認する方法がないか調べる機会がありました。aws s3 lsの結果をgrepするっていう気の遠くなるような方法は現実的ではないオブジェクト数だった為、S3 Inventory機能を利用してファイル一覧を取得し、その結果をAthenaを利用して解析してみることにしましたので、その方法をご紹介します。

S3 Inventoryを利用してファイル一覧を生成する

まずは、S3 Inventory設定をしかけておく必要があります。今回は以下のように設定しました。

設定が完了してしばらくすると、決められた取得頻度で定期的にインベントリレポートが出力されるようになります。今回設定したポイントについて、いくつか箇条書きで補足しておきます。

  • 出力形式
    • Athenaからのみ利用する分には、パフォーマンス面でもコスト面でも列指向フォーマットであるORCを使った方が良いに決まっているのですが、今回はアドホックな処理を想定している為、人間でも読めるCSVを選択しました。
  • インベントリレポートに含めるオプションのフィールド
    • 今回の要件を満たすにはファイル名さえあれば十分だったのですが、付帯情報としてあると嬉しいと判断した"サイズ"と"最終変更日"の2列のみを指定しました。 この設定が変更された場合には、後述のAthenaテーブルの作成時のカラム指定が変わってきますので、ご注意ください。

以前に試した際には、インベントリレポートの送信先となるバケットのバケットポリシー設定をするのに苦労しましたが、今回GUIから試したところ、バケットポリシーはAWS側で自動設定してくれるようでした。これは便利な素晴らしいアップデートですね。今回実際には、複数バケットのレポートを1箇所のバケットに集約するような形で複数同時に設定したのですが、それも考慮された上で設定してくれたのは素晴らしいと思いました。送信先のバケットポリシーは以下のようになりました。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "S3PolicyStmt-DO-NOT-MODIFY-1234567890123",
            "Effect": "Allow",
            "Principal": {
                "Service": "s3.amazonaws.com"
            },
            "Action": "s3:PutObject",
            "Resource": "arn:aws:s3:::s3-inventory-destination-bucket/*",
            "Condition": {
                "StringEquals": {
                    "aws:SourceAccount": "123456789012",
                    "s3:x-amz-acl": "bucket-owner-full-control"
                },
                "ArnLike": {
                    "aws:SourceArn": [
                        "arn:aws:s3:::s3-inventory-source-bucket-1",
                        "arn:aws:s3:::s3-inventory-source-bucket-2",
                        "arn:aws:s3:::s3-inventory-source-bucket-3"
                    ]
                }
            }
        }
    ]
}

S3 Inventoryが出力するファイル一覧の構成について理解する

今回苦戦したのがこちらです。これは実際の出力例を見ていただくのが早いと思います。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
$ aws s3 ls s3://s3-inventory-destination-bucket/prefix/s3-inventory-source-bucket-1/inventory-name-1/ --recursive
2018-05-20 13:38:39         33 prefix/s3-inventory-source-bucket-1/inventory-name-1/2018-05-19T08-00Z/manifest.checksum
2018-05-20 13:38:39       4743 prefix/s3-inventory-source-bucket-1/inventory-name-1/2018-05-19T08-00Z/manifest.json
2018-05-21 13:30:04         33 prefix/s3-inventory-source-bucket-1/inventory-name-1/2018-05-20T08-00Z/manifest.checksum
2018-05-21 13:30:04       4743 prefix/s3-inventory-source-bucket-1/inventory-name-1/2018-05-20T08-00Z/manifest.json
2018-05-21 09:09:07          0 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/
2018-05-21 13:23:57    1916158 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/03c04726-aa00-bb11-cc22-93ccb39cd07a.csv.gz
2018-05-21 10:00:20    3512535 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/06426d9a-aa00-bb11-cc22-4d3d68f50c62.csv.gz
2018-05-20 11:06:08    3476771 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/0661755e-aa00-bb11-cc22-926a78e727c6.csv.gz
2018-05-21 10:05:10    1838770 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/0e7d5ff2-aa00-bb11-cc22-19aabcf8a332.csv.gz
2018-05-21 11:27:33    1380163 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/14173275-aa00-bb11-cc22-5fa2e90922af.csv.gz
2018-05-21 11:36:36    2677594 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/14904190-aa00-bb11-cc22-72efcb964f1c.csv.gz
2018-05-20 09:37:29    3365708 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/1c70bec7-aa00-bb11-cc22-048b3ff636dc.csv.gz
2018-05-21 12:29:39    3302050 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/1d5bfa05-aa00-bb11-cc22-37b521882be8.csv.gz
2018-05-20 10:05:34    1352325 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/256b7e96-aa00-bb11-cc22-37c697442555.csv.gz
2018-05-21 11:12:15    1935869 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/2c84f7ae-aa00-bb11-cc22-b29d8566152c.csv.gz
2018-05-20 09:24:53    1986843 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/31b04b36-aa00-bb11-cc22-de54f1ca7e2e.csv.gz
2018-05-20 12:18:17    1914894 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/47252c33-aa00-bb11-cc22-227750185782.csv.gz
2018-05-21 12:10:27    2588918 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/49332c2a-aa00-bb11-cc22-a7a308da44d0.csv.gz
2018-05-20 11:49:17    3470552 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/4ea7816f-aa00-bb11-cc22-7616d5f03079.csv.gz
2018-05-20 11:47:45    1840044 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/50b19a90-aa00-bb11-cc22-b9d3320ed0a1.csv.gz
2018-05-21 12:06:47    3367550 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/5b0c7c9e-aa00-bb11-cc22-449d561601ee.csv.gz
2018-05-21 10:16:11    1987291 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/5ef0a125-aa00-bb11-cc22-177cb23403dc.csv.gz
2018-05-21 11:01:35    3888354 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/5f096685-aa00-bb11-cc22-5a5de53c9339.csv.gz
2018-05-20 11:02:44    1935674 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/6bd1070b-aa00-bb11-cc22-8a16722deb72.csv.gz
2018-05-20 13:08:00    1795494 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/6fcdd4e8-aa00-bb11-cc22-c1472d78c174.csv.gz
2018-05-21 11:19:46    1824518 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/70f87926-aa00-bb11-cc22-1bf9f988a7e1.csv.gz
2018-05-21 10:29:27    3458008 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/7caa086f-aa00-bb11-cc22-29aadba178a7.csv.gz
2018-05-21 09:35:30    2492915 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/8071928f-aa00-bb11-cc22-85e3485ee7a8.csv.gz
2018-05-20 13:00:51    3333093 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/89793014-aa00-bb11-cc22-dc89227e0c53.csv.gz
2018-05-20 13:06:10    3882923 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/8b853233-aa00-bb11-cc22-1c9de9556355.csv.gz
2018-05-20 10:23:44    2670892 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/8d980da4-aa00-bb11-cc22-866fafd81ec3.csv.gz
2018-05-20 13:26:34    2417901 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/96f3727d-aa00-bb11-cc22-aa8468fd4a84.csv.gz
2018-05-21 12:56:19    2404087 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/a3d579bd-aa00-bb11-cc22-45d5cd530cb6.csv.gz
2018-05-21 11:27:28    1910537 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/a60555db-aa00-bb11-cc22-dd37d651d95e.csv.gz
2018-05-20 10:05:43    1911072 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/aaa9976d-aa00-bb11-cc22-aaf7080cbc80.csv.gz
2018-05-20 10:34:07     976480 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/b704b1ab-aa00-bb11-cc22-2f2c1c5efb10.csv.gz
2018-05-20 10:18:25    2597409 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/c237cda8-aa00-bb11-cc22-9ff93b4d680e.csv.gz
2018-05-20 11:36:21    2211546 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/d0498692-aa00-bb11-cc22-25c82b78f73f.csv.gz
2018-05-21 10:33:00    2208063 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/d9e6060b-aa00-bb11-cc22-7a24492abae8.csv.gz
2018-05-21 09:35:12    1794058 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/db306ff3-aa00-bb11-cc22-577a55a99889.csv.gz
2018-05-20 09:23:27    3292545 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/eb554ce1-aa00-bb11-cc22-a531f769c259.csv.gz
2018-05-20 11:26:35    2482286 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/f241374d-aa00-bb11-cc22-e69892bd42b6.csv.gz
2018-05-20 11:43:40    1824261 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/f7eae8dd-aa00-bb11-cc22-f7f246d03fec.csv.gz
2018-05-21 12:40:46    3328386 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/f9d8744e-aa00-bb11-cc22-80e39fea9978.csv.gz
2018-05-21 13:16:25     965905 prefix/s3-inventory-source-bucket-1/inventory-name-1/data/ff980374-aa00-bb11-cc22-ac0455ec4c42.csv.gz
2018-05-20 13:38:39       2879 prefix/s3-inventory-source-bucket-1/inventory-name-1/hive/dt=2018-05-19-08-00/symlink.txt
2018-05-21 13:30:04       2879 prefix/s3-inventory-source-bucket-1/inventory-name-1/hive/dt=2018-05-20-08-00/symlink.txt

まとめると下記のようなツリー構成となります。問題なのは、"data"フォルダの配下にフラットに大量のファイルが生成されることになり、ファイル名も全くランダムとなっている為、どこからどこまでがいつ時点のファイル一覧として作成されたものなのかが外から非常にわかりづらい点です。（タイムスタンプでおよその検討はつけられますが）

  • YYYY-MM-DDThh-mmZ/
    • manifest.checksum
    • manifest.json
  • data/
    • xxx...xxx.csv.gz
    • xxx...xxx.csv.gz
    • xxx...xxx.csv.gz
    • xxx...xxx.csv.gz
    • xxx...xxx.csv.gz
    • xxx...xxx.csv.gz
  • hive/
    • dt=YYYY-MM-DDThh-mm/
      • symlink.txt

詳細な説明は公式ドキュメントに任せますが、その情報はjsonまたはsymlink.txtのマニフェストファイルの中身を見て判断をする必要があります。実際のマニフェストファイルの例が載っていますので、ぜひご確認ください。

今回は、"hive"フォルダ配下に生成されるApache Hive互換のマニフェストファイルである"symlink.txt"をAthenaから利用する方法で進めることにしました。

S3インベントリ出力結果をAthenaでクエリする

実際にはデータファイルはフラットに配置されているのですが、試行錯誤の結果、下記のような指定でテーブル作成をすることで、symlink.txtを利用したパーティション利用に成功しました。SymlinkTextInputFormatやIgnoreKeyTextOutputFormatを適切に設定しつつ、LOCATIONには、実データのパスではなく、symlink.txtが配置されている"hive"フォルダ　のパスを指定するところがポイントです。パーティション分割ができることで、パフォーマンス面でもコスト面でもお得に動くので、これは本当に便利ですね。

1
2
3
4
5
6
7
8
9
10
11
12
CREATE EXTERNAL TABLE s3inventory (
  `bucket` string,
  key string,
  size bigint,
  last_modified_date string
)
PARTITIONED BY (dt string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES ( 'separatorChar' = ',' )
STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.SymlinkTextInputFormat'
OUTPUTFORMAT  'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
LOCATION 's3://s3-inventory-destination-bucket/prefix/s3-inventory-source-bucket-1/inventory-name-1/hive';

CREATE TABLEをした後、MSCK REPAIR TABLEして、パーティションをロードしましょう。

あとは、お好きなクエリを実行するだけです。パーティションを活かすためにdt = 'YYYY-MM-DD-hh-mm'をWHERE句に指定するのをお忘れなく。

まとめ

S3インベントリでは、Apache Hive互換のマニフェストファイルを生成してくれるので、こちらを適切に利用すれば非常に簡単にAthenaから利用できることがわかりました。SymlinkTextInputFormatについて専門的な知識が事前にあったわけではなく、公式ドキュメントの手順を参考に試してみたら上手く行ったという感じですが、一度テーブルを適切に作成する方法さえわければ、あとはお好きなクエリが書けるのは本当にいいですね。今回で益々、Athenaが好きになってしまいました。

ちなみに、今回は分析にAthenaを利用しましたが、ファイル数が多くなければ、データファイルを直接S3 Selectする方法でも事足りるかもしれません。

どこかの誰かのお役に立てば嬉しいです。

スタートプラン

コメントは受け付けていません。