この記事は公開されてから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-00004INSERT 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文はバケット化されたテーブルではサポートされていないためです。
回避方法：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-00004external_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エラーが出たときの回避策をご紹介しました。パーティションだけでなく、バケッティングも組み合わせて使うことでコスト効率化＆パフォーマンス向上が見込めます。もし同様のエラーに直面したどなたかのお役に立てれば幸いです。
22
