Athenaでパーティションを設定したテーブルへのデータ作成を試してみた

Nayuta S.

2022.11.14

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

データアナリティクス事業本部の鈴木です。

Athenaでパーティションを設定したテーブルへのINSERT INTOをすると、DDLのLOCATIONで設定してあるS3にパーティションごとに分けてデータが作成されます。

「パーティション射影を設定しているときもできるんだっけ？」とか「PARTITIONED BYに複数カラムを設定しているときってどうなるんだっけ？」のような細かい仕様に自信がなかったので、今回は改めて検証してみました。

また、パーティショニングだけでなく、バケッティングを組み合わせたCTASによるデータ作成の例も記載しました。

ユースケース

例えばS3バケットに入っている生データを加工し、加工済みのデータをパーティション分割してS3に出力したいケースに使えます。SQLで加工処理を表現できるのであれば、処理をAthenaからサーバーレスに実行できますし、大規模なデータを対象にしても問題ありません。課金もスキャン量のみが対象です。

ユースケース

ポイントとしては、今回ご紹介するINSERT INTOやCTASでパーティション分割したデータを作成する場合、記事執筆時点だと書き込みできるパーティションが最大100個に制限される点です。個人的には、データの再作成のような場面を除いて^※、制限に引っかかる数のパーティションを１度に作成する場合はパーティションを細かい粒度で分割しすぎている可能性があるので、そのような場合はバケッティングも合わせて組み合わせることを検討するとよいと思います。

※ 年月日単位で分割した際にまとめて作成するような例は、CTAS および INSERT INTO を使用した、100 個を超えるパーティションを持つテーブルの作成に記載があります。

使用したテーブルとデータについて

以下のようにテーブルを作成しました。

#	テーブル名	設定
1	device_data_not_partitioned	INSERT INTOするデータを格納しているデータソースのテーブル
2	device_data_insert	パーティションキーを１つ設定した、INSERT INTOされるテーブル
3	device_data_insert2	パーティションキーを2つ設定した、INSERT INTOされるテーブル
4	device_data_insert3	パーティションキーを2つ設定した（ただしDDLでの指定はNo3と逆順）、INSERT INTOされるテーブル
5	device_data_insert_projection	パーティション射影を設定した、INSERT INTOされるテーブル
6	device_data_partitioned_bucketed	CTASで作成するテーブル。これについては後述。

たくさんテーブルを作りましたが、検証したいのは以下の３点です。

パーティションキーを１つ設定したテーブルにINSERT INTOするとどうなるのか
パーティションキーを2つ設定したテーブルにINSERT INTOするとどうなるのか（ただしDDLのPARTITIONED BYのパーティションキーの記載順が変わるとデータのでき方が変わるのか気になる）
パーティション射影を設定したテーブルにINSERT INTOするとどうなるのか

各々作成すると、以下のようになりました。

作成したテーブル

device_data_not_partitionedテーブル（が参照しているS3のオブジェクト）には、以下のデータを入れておきます。検証用なので適当なファイルを手作りしています。

使用したデータ

検証としては、infoにIDのようなものが入る想定で、このカラムをパーティションキーに指定しました。２つパーティションキーを指定したい場合は、timestampカラムを加工してyyyymmカラムを作って指定してみました。

各テーブルは以下のDDLで作成しました。数が多いので畳んでおきますが、ご興味があればご覧ください。

テーブルのDDL一覧

-- No1
CREATE EXTERNAL TABLE `device_data_not_partitioned`(
  `timestamp` string, 
  `temperature` float, 
  `info` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://${バケット名}/device_data'
TBLPROPERTIES (
  'skip.header.line.count'='1')

-- No2
CREATE EXTERNAL TABLE `device_data_insert`(
  `timestamp` string, 
  `temperature` float)
PARTITIONED BY ( 
  `info` string
  )
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  's3://${バケット名}/device_data_insert/'
TBLPROPERTIES (
  'has_encrypted_data'='false')

-- No3
CREATE EXTERNAL TABLE `device_data_insert2`(
  `timestamp` string, 
  `temperature` float)
PARTITIONED BY ( 
  `info` string,
  `yyyymm` string
  )
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  's3://${バケット名}/device_data_insert2/'
TBLPROPERTIES (
  'has_encrypted_data'='false')

-- No4
CREATE EXTERNAL TABLE `device_data_insert3`(
  `timestamp` string, 
  `temperature` float)
PARTITIONED BY ( 
  `yyyymm` string,
  `info` string
  )
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  's3://${バケット名}/device_data_insert3/'
TBLPROPERTIES (
  'has_encrypted_data'='false')

-- No5
CREATE EXTERNAL TABLE `device_data_insert_projection`(
  `timestamp` string, 
  `temperature` float)
PARTITIONED BY ( 
  `info` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  's3://${バケット名}/device_data_insert_projection'
TBLPROPERTIES (
  'projection.info.type'='enum', 
  'projection.info.values'='1,2,3', 
  'projection.enabled'='true', 
  'skip.header.line.count'='1', 
  'storage.location.template'='s3://${バケット名}/device_data_insert_projection/info=${info}')

やってみた

パーティションキーを１つ設定したとき

期待通りパーティションで分割されてS3にオブジェクトが作成されました。

以下のようにINSERT INTOを実行しました。

INSERT INTO device_data_insert
SELECT *
FROM device_data_not_partitioned;

S3では以下のようになりました。例えばinfo=1の下にオブジェクトが作成されています。

パーティションキーが１つの場合

パーティションキーを2つ設定したとき

こちらも期待通りS3にオブジェクト作成されましたが、階層はPARTITIONED BYに指定したキーの順になることが分かりました。

まず以下のSQLを実行してみます。

INSERT INTO device_data_insert2
SELECT 
  "timestamp",
  temperature,
  info,
  date_format(date_parse("timestamp", '%Y-%m-%d'), '%Y%m') as yyyymm
FROM device_data_not_partitioned

すると以下のようにオブジェクトが作成されます。

パーティションキーを２つ設定した場合その１

PARTITIONED BYの記載は以下のようにしていたので、順番通りに階層ができています。

PARTITIONED BY ( 
  `info` string,
  `yyyymm` string
  )

次に以下のSQLを実行してみます。

INSERT INTO device_data_insert3
SELECT 
  "timestamp",
  temperature,
  info,
  date_format(date_parse("timestamp", '%Y-%m-%d'), '%Y%m') as yyyymm
FROM device_data_not_partitioned

すると以下のようにオブジェクトが作成され、hogehoge=の組み合わせが、入れ替わってしまうことが分かりました。

パーティションキーを２つ設定した場合その２

この場合、SQLのSELECTのカラム順をパーティションキーの順番に合わせるか、INSERT INTOでカラムを指定すると正しく作成できました。

例えば以下のようになります。

# カラム順をパーティションキーの順番に合わせる
INSERT INTO device_data_insert3
SELECT 
  "timestamp",
  temperature,
  date_format(date_parse("timestamp", '%Y-%m-%d'), '%Y%m') as yyyymm,
  info
FROM device_data_not_partitioned

# カラムを指定
INSERT INTO device_data_insert3 ("timestamp", temperature, info, yyyymm)
SELECT 
  "timestamp",
  temperature,
  info,
  date_format(date_parse("timestamp", '%Y-%m-%d'), '%Y%m') as yyyymm
FROM device_data_not_partitioned

こうすると、以下のようにオブジェクトが作成されました。

パーティションキーを２つ設定した場合その３

パーティション射影を設定した場合

期待通りパーティションで分割されてS3にオブジェクトが作成されました。少なくとも今回のような例だと、パーティション射影を設定したからと言って、心配することはなさそうです。

以下のようにINSERT INTOを実行しました。

INSERT INTO device_data_insert_projection
SELECT 
  "timestamp",
  temperature,
  info
FROM device_data_not_partitioned

S3では以下のようになりました。例えばinfo=2の下にオブジェクトが作成されています。

パーティション射影を設定した場合

バケッティングを使うパターン

INSERT INTOでパーティション分割を設定したテーブルにデータをINSERTすると、パーティションごとにオブジェクトを作成してくれて便利ですが、以下のドキュメントに記載があるよう、記事執筆時点では最大100個のパーティションの書き込みの制限が存在します。

例えばパーティションを日付ごとに作っていて、過去分のデータを再作成したい場合などは別ですが、単純にパーティションを細かすぎる粒度で作るような設計をしている場合は、パーティションではなくバケッティングを組み合わせるとよいかもしれません。

バケッティングはINSERT INTOではなくCTASで利用できます。

例えば以下のようにSQLを実行します（細かい仕様は上記ドキュメントを参照ください）。

CREATE TABLE device_data_partitioned_bucketed
WITH (
format = 'PARQUET',
external_location = 's3://${バケット名}/device_data_insert_partitioned_bucketed/',
partitioned_by = ARRAY['yyyymm'],
bucketed_by = ARRAY['info'],
bucket_count = 1)
AS
SELECT 
  "timestamp",
  temperature,
  info,
  date_format(date_parse("timestamp", '%Y-%m-%d'), '%Y%m') as yyyymm
FROM device_data_not_partitioned

以下のようにオブジェクトが作成されました。

バケッティングを組み合わせる場合

bucket_countにてファイルの分割数を指定します。分割数は、Athenaのパフォーマンスが十分に出る大きさになるように設定します。Amazon Athena のパフォーマンスチューニング Tips トップ 10 | Amazon Web Services ブログには、ファイルサイズが非常に小さい場合（特に128MB未満の場合）には余分な時間がかかる可能性がある記載があります。

最後に

Athenaでパーティション分割を設定したテーブルへのデータ作成について、試してみた例をご紹介しました。

一つ一つはとても簡単なものの、いろいろなパターンがあり、あまりAthenaに慣れていない方は手が出しづらいかと思うので、参考になりましたら幸いです。

Athenaでパーティションを設定したテーブルへのデータ作成を試してみた

ユースケース

使用したテーブルとデータについて

やってみた

パーティションキーを１つ設定したとき

パーティションキーを2つ設定したとき

パーティション射影を設定した場合

バケッティングを使うパターン

最後に

そのた参考文献

イベント

EVENT【6/5（水）】QuickSightとTableauのデモで営業分析を解説！アクションに繋げるダッシュボード設計

EVENT【5/29（水）大阪】Alteryxの使い方から導入メリットまですべてがわかるセミナー＆ワークショップ

EVENT【5/15（水）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/8リモート】クラスメソッドのフリーランスエンジニア会社説明会〜フィンテック / リテール業界案件特集〜を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

Athenaでパーティションを設定したテーブルへのデータ作成を試してみた

ユースケース

使用したテーブルとデータについて

やってみた

パーティションキーを１つ設定したとき

パーティションキーを2つ設定したとき

パーティション射影を設定した場合

バケッティングを使うパターン

最後に

そのた参考文献

イベント

EVENT【6/5（水）】QuickSightとTableauのデモで営業分析を解説！アクションに繋げるダッシュボード設計

EVENT【5/29（水）大阪】Alteryxの使い方から導入メリットまですべてがわかるセミナー＆ワークショップ

EVENT【5/15（水）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/8リモート】クラスメソッドのフリーランスエンジニア会社説明会 〜フィンテック / リテール 業界案件特集〜 を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金） 名古屋】クラスメソッドグループの会社説明会を開催します！

関連記事

Amazon Titan Image GeneratorがGAになっていたのでX(Twitter)のアイコンを作ってもらいました

[レポート] 可観測性を構築して復元力を高める #AWSreInvent #COP343

AWS FISでシナリオライブラリ利用時には、設定済みの AWS FIS SSM ドキュメントでOSがサポートされているか確認してほしい

[アップデート] Amazon Bedrockで新モデル「Cohere Command R/R+」が利用可能になったので、RAGで使ってみた

EVENT【5/8リモート】クラスメソッドのフリーランスエンジニア会社説明会〜フィンテック / リテール業界案件特集〜を開催します

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！