【新機能】Redshift ALTER TABLE APPENDによるデータ移動を試してみました

石川覚

2016.02.22

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

ALTER TABLE APPENDによるデータ移動が利用できるようになりました。（Cluster Version: 1.0.1034）他のRDBでは聞いたことのない "ALTER TABLE APPEND" ですが、この機能はデータの物理的な移動を行わずにテーブル間でのデータ移動を実現することで高速になるというメリットがあります。どういった用途に適した機能であるかご紹介します。

ALTER TABLE APPENDによるデータ移動が追加 (2016/02/09)

ALTER TABLE APPEND とは

この新しいALTER TABLE APPENDは、ある表（ソーステーブル）のデータ全体を別の表（ターゲットテーブル）にデータを「移動（MOVE）」する機能です。「移動」ですので、ソーステーブルは空（から）になります。つまり、従来はINSERT INTO SELECT と、ソーステーブルのデータ削除（DELETE or TRUNCATE）が必要でしたが、ALTER TABLE APPEND ではデータの物理的な移動を行わずにテーブル間のデータ移動を実現しています。そのため実行速度が高速になる一方でソーステーブルからはデータが消えるというわけです。

詳細は、Redshift公式マニュアル（英語）ALTER TABLE APPEND を参照してください。

ALTER TABLE APPEND の構文

ALTER TABLE target_table_name APPEND FROM source_table_name 
[ IGNOREEXTRA | FILLTARGET ]

ALTER TABLE APPEND の使用上の注意

ソーステーブルとターゲットテーブルのカラム定義に違いがあった場合はどうなるのでしょうか？この場合は ALTER TABLE APPENDにIGNOREEXTRA か FILLTARGET のどちらかのオプションを指定する必要があります。

  • IGNOREEXTRA：ソーステーブルにある列がターゲットテーブルに存在しない場合、その列は無視されます（捨てられます）
  • FILLTARGET：ターゲットテーブルに、ソースには無い列が存在する場合、その列にはデフォルト値が適用されます。デフォルトが定義されていないケースではNULLが入ります
  • ソーステーブルとターゲットテーブルの両方が余分な列が含まれている場合、コマンドは失敗します。FILLTARGETとIGNOREEXTRAの両方を使用することはできません。

なお、ALTER TABLE APPEND はDDLであり、トランザクション対応では無いのでROLLBACKできません。

同じ名前で異なる属性を持つカラムが両方のテーブルに存在する場合、コマンドは失敗します。同じ名前の列は、以下の共通の属性を持っている必要があります。

  • Data type
  • Column size
  • Compression encoding
  • Not null
  • Sort style
  • Sort key columns
  • Distribution style
  • Distribution key columns

実行例

以下の例では、sales_monthly （ソーステーブル）のデータ全体をsales（ターゲットテーブル）にデータを移動します。

alter table sales append from sales_monthly;

動作検証

ALTER TABLE APPEND は内部的にソーステーブルのデータブロックをターゲットテーブルに移動していると考えられます。

検証の条件

テーブル定義

ソーステーブルとターゲットテーブル定義は同じ、ソートキーは lo_orderdate とします。

CREATE TABLE public.lineorder_target 
(
  lo_orderkey          INTEGER NOT NULL,
  lo_linenumber        INTEGER NOT NULL,
  lo_custkey           INTEGER NOT NULL,
  lo_partkey           INTEGER NOT NULL,
  lo_suppkey           INTEGER NOT NULL,
  lo_orderdate         INTEGER NOT NULL,
  lo_orderpriority     VARCHAR(15) NOT NULL,
  lo_shippriority      VARCHAR(1) NOT NULL,
  lo_quantity          INTEGER NOT NULL,
  lo_extendedprice     INTEGER NOT NULL,
  lo_ordertotalprice   INTEGER NOT NULL,
  lo_discount          INTEGER NOT NULL,
  lo_revenue           INTEGER NOT NULL,
  lo_supplycost        INTEGER NOT NULL,
  lo_tax               INTEGER NOT NULL,
  lo_commitdate        INTEGER NOT NULL,
  lo_shipmode          VARCHAR(10) NOT NULL
)
distkey(lo_partkey)
sortkey(lo_orderdate);

テスト用データ

db=> INSERT INTO public.lineorder_1992 SELECT * FROM public.lineorder WHERE lo_orderdate BETWEEN 19920000 AND 19929999;
INSERT 0 11402480
Time: 12061.933 ms
db=> INSERT INTO public.lineorder_1993 SELECT * FROM public.lineorder WHERE lo_orderdate BETWEEN 19930000 AND 19939999;
INSERT 0 11379006
Time: 12843.286 ms

[従来] ソート済みデータをディープコピー（INSERT INTO SELECT）

空のテーブルにソート済みデータをディープコピー

INSERT INTO SELECT でソート済みデータをディープコピーすると、ターゲットテーブル(lineorder_target)のデータはソート済みで格納されますので、VACUUMは不要です。ANALYZEの実行と、ソーステーブルのTRUNCATEを実施します。 データのディープコピーには12.878秒、データのソートは不要、統計情報の更新は3.806秒、ソーステーブルのデータ削除は0.423秒で、全て含めると17.107秒でした。

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 0_not_sorted | lineorder_target   |           0 |        0 |           0.000
 1_sorted     | lineorder_1992     |    11402480 | 11402480 |           1.000

db=> INSERT INTO public.lineorder_target SELECT * FROM public.lineorder_1992;
INSERT 0 11402480
Time: 12878.085 ms

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    11402480 | 11402480 |           1.000
 1_sorted     | lineorder_1992     |    11402480 | 11402480 |           1.000

db=> ANALYZE public.lineorder_target;
ANALYZE
Time: 3806.338 ms

db=> TRUNCATE TABLE public.lineorder_1992;
TRUNCATE TABLE and COMMIT TRANSACTION
Time: 423.389 ms

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    11402480 | 11402480 |           1.000
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000

更にテーブルにソート済みデータをディープコピーで追加

ターゲットテーブル(lineorder_target)へ更にINSERT INTO SELECT でソート済みデータをディープコピーします。ターゲットテーブル(lineorder_target)のデータは未ソートで格納されますので、VACUUMは必要です。ANALYZEの実行と、ソーステーブルのTRUNCATEを実施します。 データのディープコピーには12.717秒、データのソートは28.269秒、統計情報の更新は2.108秒、ソーステーブルのデータ削除は0.879秒で、全て含めると43.973秒でした。

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    11402480 | 11402480 |           1.000
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000
 1_sorted     | lineorder_1993     |    11379006 | 11379006 |           1.000 

db=> INSERT INTO public.lineorder_target SELECT * FROM public.lineorder_1993;
INSERT 0 11379006
Time: 12717.126 ms

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    11402480 | 22781486 |           0.501
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000
 1_sorted     | lineorder_1993     |    11379006 | 11379006 |           1.000

db=> VACUUM public.lineorder_target;
VACUUM
Time: 28269.610 ms

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    22781486 | 22781486 |           1.000
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000
 1_sorted     | lineorder_1993     |    11379006 | 11379006 |           1.000

db=> ANALYZE public.lineorder_target;
ANALYZE
Time: 2108.294 ms

db=> TRUNCATE TABLE public.lineorder_1993;
TRUNCATE TABLE and COMMIT TRANSACTION
Time: 879.741 ms

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    22781486 | 22781486 |           1.000
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000
 0_not_sorted | lineorder_1993     |           0 |        0 |           0.000

[新] ソート済みデータを移動（ALTER TABLE APPEND）

空のテーブルにソート済みデータを移動

ALTER TABLE APPEND でソート済みデータを移動すると、ターゲットテーブル(lineorder_target)のデータは未ソートで格納されますので、VACUUMが必要です。ANALYZEの実行を実施します。データは移動するのでソーステーブルのデータ削除は不要です。 データの移動には1.211秒でしたが、データのソートは47.653秒、統計情報の更新は1.021秒で、全て含めると49.855秒でした。 データの移動は高速ですが、データのソート（VACUUM）はかなり時間がかかってしまいました。

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 0_not_sorted | lineorder_target   |           0 |        0 |           0.000
 1_sorted     | lineorder_1992     |    11402480 | 11402480 |           1.000

db=> ALTER TABLE public.lineorder_target APPEND FROM public.lineorder_1992;
INFO:  ALTER TABLE APPEND "lineorder_target" from "lineorder_1992" is complete.
ALTER TABLE APPEND and COMMIT TRANSACTION
Time: 1211.296 ms

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 0_not_sorted | lineorder_target   |           0 | 11402480 |           0.000
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000

db=> VACUUM public.lineorder_target;
VACUUM
Time: 47653.109 ms

db=> ANALYZE public.lineorder_target;
ANALYZE
Time: 1021.992 ms

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    11402480 | 11402480 |           1.000
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000

更にテーブルにソート済みデータを移動

ターゲットテーブル(lineorder_target)へ更にALTER TABLE APPEND でソート済みデータを移動します。先程と同様にターゲットテーブル(lineorder_target)のデータは未ソートで格納されますので、VACUUMが必要です。ANALYZEの実行を実施します。データは移動するのでソーステーブルのデータ削除は不要です。 データの移動には1.344秒でしたが、データのソートは27.653秒、統計情報の更新は1.602秒で、全て含めると30.589秒でした。 データのソート（VACUUM）は空のテーブルに移動させた後のVACUUMの半分の時間で済ました。

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    11402480 | 11402480 |           1.000
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000
 1_sorted     | lineorder_1993     |    11379006 | 11379006 |           1.000 

db=> ALTER TABLE public.lineorder_target APPEND FROM public.lineorder_1993;
INFO:  ALTER TABLE APPEND "lineorder_target" from "lineorder_1993" is complete.
ALTER TABLE APPEND and COMMIT TRANSACTION
Time: 1344.405 ms

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    11402480 | 22781486 |           0.501
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000
 0_not_sorted | lineorder_1993     |           0 |        0 |           0.000

db=> VACUUM public.lineorder_target;
VACUUM
Time: 27653.198 ms

db=> ANALYZE public.lineorder_target;
ANALYZE
Time: 1602.358 ms

    status    |     tablename      | sorted_rows |   rows   | sort_percentage
--------------+--------------------+-------------+----------+-----------------
 1_sorted     | lineorder_target   |    22781486 | 22781486 |           1.000
 0_not_sorted | lineorder_1992     |           0 |        0 |           0.000
 0_not_sorted | lineorder_1993     |           0 |        0 |           0.000

検証の結果

検証結果を以下の表にまとめました。ALTER TABLE APPENDは、データの「移動」については速いのですが、ソート済みデータを移動しても未ソートデータとして格納されるため、ソートキー指定したテーブルではデータ移動後にVACUUMが必要となります。

  • [従来] ソート済みデータをディープコピー（INSERT INTO SELECT）
データ ディープコピー時間 VACUUM時間 ANALYZE時間 TRUNCATE時間 合計時間
1992 12.878秒 (初回は不要) 3.806秒 0.423秒 17.107秒
1993 12.717秒 28.269秒 2.108秒 0.879秒 43.973秒
  • [新] ソート済みデータを移動（ALTER TABLE APPEND）
データ データ移動時間 VACUUM時間 ANALYZE時間 TRUNCATE時間 合計時間
1992 1.211秒 47.653秒 1.021秒 (不要) 49.855秒
1993 1.344秒 27.653秒 1.602秒 (不要) 30.589秒

最後に

ALTER TABLE APPENDでソート済みデータを移動することで、データブロックやゾーンマップのマージができたら画期的だと思いましたが、残念ながら現状は「移動」のようです。 といっても検証の結果から、ディープコピー（INSERT INTO SELECT）とデータの移動（ALTER TABLE APPEND）を組合せることでデータコピーが約３割ほど速くなることがわかりました。

以下の方針に従い、データコピーの方式の見直しをご検討ください。

  • 空のテーブルに対するデータコピーは従来通り、ディープコピー（INSERT INTO SELECT）
  • データが既に入っているテーブルに対するデータコピーは、データの移動（ALTER TABLE APPEND）

UPSERTでワーキングテーブルにコピー・作成した結果をファクトテーブルにコピーするといったユースケースでは、このテクニックが使えます。

AWS

関連記事

Amazon DataZone の新しい生成 AIベースの新機能「AIリコメンデーション」を試してみる！

石川覚

2024.04.10

Amazon DataZone 初期導入から Amazon Redshift 統合の新機能まで解説する！

石川覚

2024.04.09

クラスメソッド データアナリティクス通信(AWSデータ分析編) – 2024年4月号

石川覚

2024.04.01

[Redshift] 伸縮自在なサイズ変更機能を利用して移行可能なクラスター構成を確認する方法

村上紗敏

2024.03.26