AWSのAthenaでS3のファイルを分析しようとしたらトラブった話

トラブル概要

S3にあるtsvファイルをAthenaでテーブル化してQuickSightで可視化したい。

CREATE EXTERNAL TABLE `2019101919_sample_data`(
  `created_at` string, 
  `updated_at` string, 
  `id` string, 
  `title` string, 
  `user` string, 
  `likes_count` bigint, 
  `comments_count` bigint, 
  `page_views_count` string, 
  `url` string, 
  `tags` string)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY '\t'
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://test-playground/2019/10/2019101919_sample_data.tsv'

このクエリ文自体は成功する。
が、なぜか結果が返ってこない。

解決策

Zero records returnedというエラーが出ているが、そもそも結果をよくみるとData scanned: 0KBと出ている。これはLOCATION句のS3のパスが間違っているのが原因であるらしい。

別にファイルの場所は間違ってないし、アクセス権限も問題ないんだが…

公式のマニュアルをよくみてみる。

ファイルの場所を指定するために、ファイル名、アンダースコア、ワイルドカード、または glob パターンを使用しないでください。
Amazon S3 のテーブルの場所 - Amazon Athena

Athenaでファイル名をダイレクトに指定するのはダメ！

ということで分析したいファイルの場所をどこか適当なフォルダ配下に移行する。
例えばs3://test-playground/2019/10/19/19/data.tsvなど　※後述

例えば2019/10/19 19:00のデータを
s3://test-playground/sample-data/dt=2019-10-19/19.tsv
というように格納する。

CREATE EXTERNAL TABLE `2019101919_sample_data`(
  `created_at` string, 
  `updated_at` string, 
  `id` string, 
  `title` string, 
  `user` string, 
  `likes_count` bigint, 
  `comments_count` bigint, 
  `page_views_count` string, 
  `url` string, 
  `tags` string)
PARTITIONED BY ( 
  `dt` string)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY '\t' -- ここがただの空白になっていないことを確認する
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://test-playground/sample-db/dt=2019-10-19/' --　ここをフォルダ名にする！

これでうまく結果が返ってくるようになった。

ちなみにtsvの区切り文字だが、Athenaのコンソール画面からテーブル作成するとただの空白になっているので注意。

学び

GlueのデータカタログをAthenaに統合する場合は注意！

そもそも何でこんなエラーにぶち当たったかというと、

• Lambdaで一時間に一回データを収集してS3に保存
• GlueでS3のファイルを全部テーブル化
• Athenaで分析

こういうシナリオだった。

この場合Glueで素直にクローラーを作ってテーブルを作ると1ファイルにつき1テーブル作成されてしまう。
Glueのコンソールからはしっかりテーブル作成できているように見えるが、いざAthenaで分析しようとすると結果が返ってこないというエラーにぶち当たるわけだ。

データを格納するときにHiveのパーティション形式で保存すると便利

※tokusyuさんのコメントを反映しました。

そもそものデータ収集の際のoutput先を下記のように指定しておくと便利。

s3://<bucket>/<year>/<month>/<day>/<hour>/data.tsv
s3://<bucket>/<適当なディレクトリ名>/dt=<year>-<month>-<day>/<hour>.tsv

こうしておけば、データを格納すると同時にパーティショニングも実行できる。

あとはAthenaから下記のようにパーティションをロードすればOK。

MSCK REPAIR TABLE <テーブル名>;

パーティション分割を行うことでデータのスキャン量が削減できる。そのため時間短縮やコスト削減につながるのでぜひやっておきたい。