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だけのテーブルも作成可能となります。