Athenaでは複数のS3ロケーションを指定できないのでその対応方法

5761 ワード
AWS Athena SQL AWS テキストリンク

Athenaでは単一のディレクトリしかS３を指定できない

2020年4月現在Athenaでは複数のS3ディレクトリ（ディレクトリは存在しないが便宜上ディレクトリと呼びます）を指定できません。
扱うデータを集中させればいいのですが、場合によってはそう行かないこともありえます。

例えば以下のようなデータ構成があります。

s3://bucket_name/2019/05/01/foo/1.csv
s3://bucket_name/2019/05/02/foo/1.csv
s3://bucket_name/2019/05/03/foo/1.csv
...
s3://bucket_name/2019/05/01/bar/1.csv
s3://bucket_name/2019/05/02/bar/1.csv
s3://bucket_name/2019/05/03/bar/1.csv

上記のような構造で、fooテーブルとbarテーブルを作成したいのだが、単一のディレクトリを指定するとどうしても両方のファイルが混ざります。
同一形式のファイルであればSQLで対応すればできなくはないかもしれませんが、ミスも発生するので避けるべきです。
外部システムがこのようなディレクトリ構造でファイルを作成していたため対応が必要となりました。

対応方法

該当ファイルを別ディレクトリにコピーすることで対応もできますが、ファイル数が大きくなると時間も費用もかかってしまいます。
こういった場合はパーティションを利用するとなんとかテーブルを分けられます。
テーブルをパーティションを利用したもので作成します。

create_foo.sql
CREATE EXTERNAL TABLE `foo_and_bar`(
  `id` int, 
  `name` string)
PARTITIONED BY ( 
  `year` int, 
  `month` int,
  `day` int,
  'type' string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://bucket_name/'
TBLPROPERTIES (
  'has_encrypted_data'='false', 
  'transient_lastDdlTime'='1586756612')

これに対してパーティションを加えていきます

add_partition.sql
ALTER TABLE foo_and_bar ADD PARTITION (year='2019',month='05', day='01', type='foo') location 's3://bucket_name/2019/05/01/foo/1.csv'
ALTER TABLE foo_and_bar ADD PARTITION (year='2019',month='05', day='02', type='foo') location 's3://bucket_name/2019/05/02/foo/1.csv'
...

今回は日付のパーティションも加えています。これで作成したfoo_and_barテーブルに対してwhere句でtypeを指定すればfoo配下のファイルだけ参照が可能となります。
毎回fooを指定するのが大変な場合、ここから更に新しいテーブルを作成します。

create_foo.sql
CREATE TABLE foo
WITH (
format='PARQUET',
external_location='s3://bucket_name/foo'
) AS
SELECT * FROM foo where type = 'foo'

これでfooだけのファイルが存在するディレクトリが作成され、fooだけのテーブルも作成可能となります。

関連記事

AthenaでCSVファイルからParquet形式へ変換
Athenaで後からパーティションを追加する

【配列】14週間項目4.発泡法ソート
『C++Primer 4 th』オブジェクト向けプログラミング読書ノート－－protectedメンバー