INSERT is not supported for bucketed tablesに負けず、Amazon Athenaでバケッティングを設定したテーブルにデータを追加する
external_locationを合体させます
データアナリティクス事業本部インテグレーション部コンサルティングチーム・新納（にいの）です。
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文はバケット化されたテーブルではサポートされていないためです。
回避方法：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エラーが出たときの回避策をご紹介しました。パーティションだけでなく、バケッティングも組み合わせて使うことでコスト効率化＆パフォーマンス向上が見込めます。もし同様のエラーに直面したどなたかのお役に立てれば幸いです。
![[Tips] Amazon Athena 6億レコードのサンプルデータ「SSB」を準備する](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-764c44f07bcd41184fe62a432ae120ab/512a4cdcd05a0ed3ddea950fdf619fa0/amazon-athena)
