Zenn
✌️

AthenaでCSV形式のS3 Inventory reportへクエリしたとき、結果が空になる問題

2023/01/24に公開
AWS
S3
Athena
tech

tldr

CREATE TABLE するときに、以下をつけると無事データを見ることができました。

WITH SERDEPROPERTIES ('separatorChar'=',', 'quoteChar'='\"')

問題

S3 Inventoryで出力したInventory reportに対して、Athenaでクエリをかけようと思い、公式ドキュメント[1]に沿って設定したところ、クエリを投げても結果が空になりました。

設定手順・デバッグ・解決

1. S3 Inventoryの設定

目的のbucketの、Management -> Create inventory configuration にて、Inventory reportの設定をします。
今回はCSVの形式で出力しました。

2. Inventory reportの出力先を見てみる

少し時間が経ったあと、以下のような階層構造が確認できました。

2023-01-23T01-00Z/
data/
hive/

3. Athenaの設定

Athenaを開きます。
最初に、Athenaの結果の出力先を指定したりする初期設定をします。

4. AthenaでCREATE TABLE

CREATE TABLEをします。
公式ドキュメント[1:1]をもとに、以下のクエリを投げました。

CREATE EXTERNAL TABLE my_inventory_table(
         bucket string,
         key string,
         size string,
         last_modified_date string,
         e_tag string,
         checksum_algorithm string
) PARTITIONED BY (
        dt string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
  STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.SymlinkTextInputFormat'
  OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
  LOCATION 's3://{{destination}}/{{bucket}}/{{config_id}}/hive/'
  TBLPROPERTIES (
    "projection.enabled" = "true",
    "projection.dt.type" = "date",
    "projection.dt.format" = "yyyy-MM-dd-HH-mm",
    "projection.dt.range" = "2022-01-01-00-00,NOW",
    "projection.dt.interval" = "1",
    "projection.dt.interval.unit" = "DAYS"
  )

クエリの後半には、テーブルのフォーマットに関するオプションが並んでいます。

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' は、
CSV形式のSerializer/Deserializerを使うということです。

STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.SymlinkTextInputFormat' は、symlinkの先にあるファイルがあるようなフォーマットであるということです。

S3 Inventory reportの hive/ 以下は、次のようになっています。

hive/dt=2023-01-23-01-00/symlink.txt

これはAthenaで読み込むためのファイル郡で、dtがpartitionになっており、ファイルの実体はsymlink.txtの先にあるという方式のようです。

5. (Partitionを読み込むロードする)

公式ドキュメントにはないですが、以下のようなクエリで、パーティション情報を読み込むようです。

MSCK REPAIR TABLE `my_inventory_table`;

6. 適当にクエリをかけてみる

SELECT dt, COUNT(1) FROM my_inventory_table GROUP BY dt LIMIT 10;

-> Results (0)
どうやら空っぽい...

7. Partitionを確認してみる

SHOW PARTITIONS my_inventory_table

-> Results(1)

dt=2023-01-23-01-00

お、Partitionは読めているっぽい。
ということはデータが適切に読めていない...？

8. CSVファイルをダウンロードして中を見てみる

ダウンロード後、gunzipで解凍してどんなCSVか見てみる。
-> , 区切りで、" がクォーテーションとして使われているCSVのよう。

9. SerDeの設定をいじってみる

一度テーブルをdropして、CREATE TABLEを次のクエリに変更してみました。

CREATE EXTERNAL TABLE my_inventory_table(
         bucket string,
         key string,
         size string,
         last_modified_date string,
         e_tag string,
         checksum_algorithm string
) PARTITIONED BY (
        dt string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
  WITH SERDEPROPERTIES ('separatorChar'=',', 'quoteChar'='\"')
  STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.SymlinkTextInputFormat'
  OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
  LOCATION 's3://{{destination}}/{{bucket}}/{{config_id}}/hive/'
  TBLPROPERTIES (
    "projection.enabled" = "true",
    "projection.dt.type" = "date",
    "projection.dt.format" = "yyyy-MM-dd-HH-mm",
    "projection.dt.range" = "2022-01-01-00-00,NOW",
    "projection.dt.interval" = "1",
    "projection.dt.interval.unit" = "DAYS"
  )

変更点として、次の行が加えられています。

WITH SERDEPROPERTIES ('separatorChar'=',', 'quoteChar'='\"')

これはそのとおり、separatorとして ,、 クォーテーションとして " が使われているという設定です。

10. 再度クエリをかけてみる

SELECT dt, COUNT(1) FROM my_inventory_table GROUP BY dt LIMIT 10;

-> Results(1)

✌

脚注

  1. 公式ドキュメント https://docs.aws.amazon.com/AmazonS3/latest/userguide/storage-inventory-athena-query.html ↩︎ ↩︎

Discussion