INSERT is not supported for bucketed tablesに負けず、Amazon Athenaでバケッティングを設定したテーブルにデータを追加する

external_locationを合体させます

#Amazon Athena

#AWS

niino

2022.07.29

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

データアナリティクス事業本部インテグレーション部コンサルティングチーム・新納（にいの）です。

Amazon Athenaでのパフォーマンスチューニング戦略と言えばパーティション分割がまず挙げられますが、バケッティングによるパフォーマンス向上のメリットも見逃せません。

バケッティングを設定したテーブルにデータをインサートしようとするとエラーが発生してしまうのですが、それを回避してデータを追加する方法をご紹介します。

バケッティングとは

バケッティングとは複数のノードで水平分散処理を行えるよう、データファイルをキーとファイル数を指定して分割するAmazon Athenaの機能です。分析の切り口としてよく使用するディメンションをキーとしてバケッティングを設定することによりクエリ実行のパフォーマンス向上が期待できます。

詳細については以下のエントリをご参照ください。

バケッティングを設定したテーブルを作成

まずはサンプルの購買データを使って、バケッティングを設定したテーブルを作成します。キー（bucketed_by）にはcityを指定し、bucket_countで5つにファイルが分割されるように記述しています。今回はパーティションに指定しているstateがAlabamaのみのデータに絞ってデータを作成してみます。


CREATE TABLE superstore_bucketed
WITH
(format = 'PARQUET',
external_location = 's3://<バケット名>/bucketed/',
partitioned_by = ARRAY['state'],
bucketed_by = ARRAY['city'],
bucket_count = 5
)
AS
SELECT 
  "row_id" , 
  "order_id" , 
  "ship_mode" , 
  "customer_id" , 
  "customer_name" , 
  "segment" , 
  "country_region" , 
  "city" , 
  "postal_code" , 
  "region" , 
  "product_id" , 
  "category" , 
  "sub_category" , 
  "product_name" , 
  "sales" , 
  "quantity", 
  "discount" , 
  "profit" ,
  "order_date",
  "state"
FROM superstore_date_parsed
WHERE "state" = 'Alabama'

実行するとexternal_locationに指定したS3バケットに5つ分ファイルが作成されていることが確認できました。


[cloudshell-user@ip-10-0-82-111 ~]$ aws s3 ls --recursive s3://<バケット名>/bucketed/
2022-07-28 16:18:20       6208 bucketed/state=Alabama/20220728_161818_00019_krp3v_bucket-00000
2022-07-28 16:18:20       6287 bucketed/state=Alabama/20220728_161818_00019_krp3v_bucket-00001
2022-07-28 16:18:20       5952 bucketed/state=Alabama/20220728_161818_00019_krp3v_bucket-00002
2022-07-28 16:18:20       6173 bucketed/state=Alabama/20220728_161818_00019_krp3v_bucket-00003
2022-07-28 16:18:20       5250 bucketed/state=Alabama/20220728_161818_00019_krp3v_bucket-00004

`INSERT is not supported for bucketed tables`が出た

stateがAlabama以外のデータを先ほど作成したバケッティング設定済みのテーブルにインサートしてみましょう。


INSERT INTO superstore_bucketed
(
SELECT 
  "row_id" , 
  "order_id" , 
  "ship_mode" , 
  "customer_id" , 
  "customer_name" , 
  "segment" , 
  "country_region" , 
  "city" , 
  "postal_code" , 
  "region" , 
  "product_id" , 
  "category" , 
  "sub_category" , 
  "product_name" , 
  "sales" , 
  "quantity", 
  "discount" , 
  "profit" ,
  "order_date",
  "state"
FROM superstore_date_parsed
WHERE "state" <> 'Alabama'
)

すると、INSERT is not supported for bucketed tablesというエラーが出てしまいました。

原因は以下ドキュメントにある通り、INSERT文はバケット化されたテーブルではサポートされていないためです。

INSERT INTO - Amazon Athena

回避方法：external_locationを統合する

このエラーを回避してデータをバケッティングしたテーブルに追加する方法として、external_locationに出力したファイルを統合します。

インサートしたいデータでテーブルを作成

まずはインサートしたいデータでテーブルを一時的に作成します。先ほどエラーになったstateがAlabama以外のデータのみでテーブルを作成しています。

CREATE TABLE superstore_bucketed_tmp
WITH
(format = 'PARQUET',
external_location = 's3://<バケット名>/bucketed_tmp/',
partitioned_by = ARRAY['state'],
bucketed_by = ARRAY['city'],
bucket_count = 5
)
AS
SELECT 
  "row_id" , 
  "order_id" , 
  "ship_mode" , 
  "customer_id" , 
  "customer_name" , 
  "segment" , 
  "country_region" , 
  "city" , 
  "postal_code" , 
  "region" , 
  "product_id" , 
  "category" , 
  "sub_category" , 
  "product_name" , 
  "sales" , 
  "quantity", 
  "discount" , 
  "profit" ,
  "order_date",
  "state"
FROM superstore_date_parsed
WHERE "state" <> 'Alabama'

この一時的なテーブルの作成で指定したexternal_locationを見ると、見事にstateごとにデータファイルが5つ分分割されて作成されています。


[cloudshell-user@ip-10-0-82-111 ~]$ aws s3 ls --recursive s3://<バケット名>/bucketed_tmp/
2022-07-28 16:24:04       8331 bucketed_tmp/state=Arizona/20220728_162400_00067_s3yi6_bucket-00000
2022-07-28 16:24:02       7153 bucketed_tmp/state=Arizona/20220728_162400_00067_s3yi6_bucket-00001
2022-07-28 16:24:03       7196 bucketed_tmp/state=Arizona/20220728_162400_00067_s3yi6_bucket-00002
2022-07-28 16:24:04       8210 bucketed_tmp/state=Arizona/20220728_162400_00067_s3yi6_bucket-00003
2022-07-28 16:24:03       8794 bucketed_tmp/state=Arizona/20220728_162400_00067_s3yi6_bucket-00004
2022-07-28 16:24:10       4629 bucketed_tmp/state=Arkansas/20220728_162400_00067_s3yi6_bucket-00000
2022-07-28 16:24:05       8015 bucketed_tmp/state=Arkansas/20220728_162400_00067_s3yi6_bucket-00001
2022-07-28 16:24:08       5292 bucketed_tmp/state=Arkansas/20220728_162400_00067_s3yi6_bucket-00002
2022-07-28 16:24:07       5198 bucketed_tmp/state=Arkansas/20220728_162400_00067_s3yi6_bucket-00003
2022-07-28 16:24:05       4906 bucketed_tmp/state=Arkansas/20220728_162400_00067_s3yi6_bucket-00004
:
:
2022-07-28 16:24:15        585 bucketed_tmp/state=Wyoming/20220728_162400_00067_s3yi6_bucket-00000
2022-07-28 16:24:15        585 bucketed_tmp/state=Wyoming/20220728_162400_00067_s3yi6_bucket-00001
2022-07-28 16:24:15        585 bucketed_tmp/state=Wyoming/20220728_162400_00067_s3yi6_bucket-00002
2022-07-28 16:24:15        585 bucketed_tmp/state=Wyoming/20220728_162400_00067_s3yi6_bucket-00003
2022-07-28 16:24:07       4743 bucketed_tmp/state=Wyoming/20220728_162400_00067_s3yi6_bucket-00004

external_locationをs3 syncで統合

この一時テーブルを作成した際にできたexternal_locationを、最初に作成したテーブルのexternal_locationに統合します。今回はs3 syncコマンドを使用します。

[cloudshell-user@ip-10-0-82-111 ~]$ aws s3 sync s3://<バケット名>/bucketed_tmp/ s3://<バケット名>/bucketed/
copy: s3://cm-test-superstore/bucketed_tmp/state=Arizona/20220728_162400_00067_s3yi6_bucket-00000 to s3://cm-test-superstore/bucketed/state=Arizona/20220728_162400_00067_s3yi6_bucket-00000
copy: s3://cm-test-superstore/bucketed_tmp/state=Arizona/20220728_162400_00067_s3yi6_bucket-00002 to s3://cm-test-superstore/bucketed/state=Arizona/20220728_162400_00067_s3yi6_bucket-00002
:
:
copy: s3://cm-test-superstore/bucketed_tmp/state=Wyoming/20220728_162400_00067_s3yi6_bucket-00001 to s3://cm-test-superstore/bucketed/state=Wyoming/20220728_162400_00067_s3yi6_bucket-00001
copy: s3://cm-test-superstore/bucketed_tmp/state=Wyoming/20220728_162400_00067_s3yi6_bucket-00004 to s3://cm-test-superstore/bucketed/state=Wyoming/20220728_162400_00067_s3yi6_bucket-00004

パーティションのロード

最後にAthenaに戻り、以下コマンドを使ってパーティションをロードします。

MSCK REPAIR TABLE `superstore_bucketed`;

すると、以下のように統合したデータ（今回はstateがAlabama以外のデータ）のパーティションがロードされていることが確認できました。

Partitions not in metastore:	superstore_bucketed:state=Arizona	superstore_bucketed:state=Arkansas	superstore_bucketed:state=California	superstore_bucketed:state=Colorado	superstore_bucketed:state=Connecticut	superstore_bucketed:state=Delaware	superstore_bucketed:state=District of Columbia	superstore_bucketed:state=Florida	superstore_bucketed:state=Georgia	superstore_bucketed:state=Idaho	superstore_bucketed:state=Illinois	superstore_bucketed:state=Indiana	superstore_bucketed:state=Iowa	superstore_bucketed:state=Kansas	superstore_bucketed:state=Kentucky	superstore_bucketed:state=Louisiana	superstore_bucketed:state=Maine	superstore_bucketed:state=Maryland	superstore_bucketed:state=Massachusetts	superstore_bucketed:state=Michigan	superstore_bucketed:state=Minnesota	superstore_bucketed:state=Mississippi	superstore_bucketed:state=Missouri	superstore_bucketed:state=Montana	superstore_bucketed:state=Nebraska	superstore_bucketed:state=Nevada	superstore_bucketed:state=New Hampshire	superstore_bucketed:state=New Jersey	superstore_bucketed:state=New Mexico	superstore_bucketed:state=New York	superstore_bucketed:state=North Carolina	superstore_bucketed:state=North Dakota	superstore_bucketed:state=Ohio	superstore_bucketed:state=Oklahoma	superstore_bucketed:state=Oregon	superstore_bucketed:state=Pennsylvania	superstore_bucketed:state=Rhode Island	superstore_bucketed:state=South Carolina	superstore_bucketed:state=South Dakota	superstore_bucketed:state=Tennessee	superstore_bucketed:state=Texas	superstore_bucketed:state=Utah	superstore_bucketed:state=Vermont	superstore_bucketed:state=Virginia	superstore_bucketed:state=Washington	superstore_bucketed:state=West Virginia	superstore_bucketed:state=Wisconsin	superstore_bucketed:state=Wyoming
Repair: Added partition to metastore superstore_bucketed:state=Arizona
Repair: Added partition to metastore superstore_bucketed:state=Arkansas
Repair: Added partition to metastore superstore_bucketed:state=California
Repair: Added partition to metastore superstore_bucketed:state=Colorado
Repair: Added partition to metastore superstore_bucketed:state=Connecticut
Repair: Added partition to metastore superstore_bucketed:state=Delaware
Repair: Added partition to metastore superstore_bucketed:state=District of Columbia
Repair: Added partition to metastore superstore_bucketed:state=Florida

テーブルをSELECTすると、きちんと目的のデータが追加されていることが確認できました。Alabama以外のデータが入っています。

最後に

バケッティングを設定したテーブルにインサートしようとしてINSERT is not supported for bucketed tablesエラーが出たときの回避策をご紹介しました。パーティションだけでなく、バケッティングも組み合わせて使うことでコスト効率化＆パフォーマンス向上が見込めます。もし同様のエラーに直面したどなたかのお役に立てれば幸いです。