Amazon Redshift 分散キーの変更について試してみた

分散キー変更の際にVACUUMを実行する必要が不要になったというリリースを目にしました。正直、ALTERにて分散キーの変更が可能になったことに気づいていませんでしたので、この機会に分散キーの変更について試してみました。マニュアルに記載にない動作も確認できましたのでご紹介したいと思います。試したクラスタバージョンは、1.0.11116です。

Cluster Version History - Version 1.0.10013, 1.0.10221, 1.0.10393, 1.0.10480, or 1.0.10648 の抜粋です。

The ALTER TABLE ALTER DISTKEY command for tables with a sort key now performs an inline sort. Therefore, running VACUUM afterwards is no longer required.

ソートキーを持つテーブルのALTER TABLE ALTER DISTKEYコマンドは、インラインソートを実行するようになりました。 したがって、後でVACUUMを実行する必要はありません。

目次

分散キー変更(ALTER DISTKEY構文)

以下、データベース開発者ガイドの抜粋です。

ALTER DISTKEY column_name または ALTER DISTSTYLE KEY DISTKEY column_name

テーブルの分散キーとして使用される列を変更する句。以下の点に考慮してください。

  • テーブルの元の DISTSTYLE が EVEN または KEY である DISTKEY のみ変更することができます。
  • 同じテーブルで、VACUUM と ALTER DISTKEY を同時に実行することはできません。
    • VACUUM がすでに実行されている場合は、ALTER DISTKEY よりエラーが返ります。
    • ALTER DISTKEY が実行されている場合は、テーブルでバックグラウンドバキュームは開始されません。
    • ALTER DISTKEY が実行されている場合は、フォアグラウンドバキュームよりエラーが返ります。
  • ALTER DISTKEY コマンドは、1 つのテーブルに対して一度に 1 回のみ実行することができます。

DISTSTYLE KEY を指定する場合、データは、DISTKEY 列の値で分散されます。DISTSTYLE の詳細については、「CREATE TABLE」を参照してください。

つまり、分散スタイルEVENからKEY(column_name)に変更可能、分散スタイルKEY(column_name_a)からKEY(column_name_b)に変更可能と読み取れます。「ALTER DISTKEY コマンドは、1 つのテーブルに対して一度に 1 回のみ実行することができます。」は、分散キーを一度しか変更できないのか気になるところです。

また、分散キーの変更は2つの記述が可能なようです。

ALTER TABLE table_name
{
ADD table_constraint 
:
| ALTER DISTKEY column_name 
| ALTER DISTSTYLE KEY DISTKEY column_name   
:

以降では順に確認します。

分散スタイルEVENからKEY(lo_orderkey)に変更

分散スタイルEVENのテーブルを作成して、ALTER DISTKEY column_nameにて、分散キーをlo_orderkeyに変更します。分散スタイルを指定することなく、分散スタイル・分散キーともに変更できました。しかし、全てソートされていません。(unsortedが100%)

cmdb=> CREATE TABLE IF NOT EXISTS "cm_ishikawa_satoru"."lineorder_test"
cmdb-> (
cmdb(>         "lo_orderkey" INTEGER   ENCODE raw
cmdb(>         ,"lo_linenumber" INTEGER   ENCODE raw
cmdb(>         ,"lo_custkey" INTEGER   ENCODE raw
cmdb(>         ,"lo_partkey" INTEGER   ENCODE raw
cmdb(>         ,"lo_suppkey" INTEGER   ENCODE raw
cmdb(>         ,"lo_orderdate" INTEGER   ENCODE raw
cmdb(>         ,"lo_orderpriority" VARCHAR(15)   ENCODE raw
cmdb(>         ,"lo_shippriority" VARCHAR(1)   ENCODE raw
cmdb(>         ,"lo_quantity" INTEGER   ENCODE raw
cmdb(>         ,"lo_extendedprice" INTEGER   ENCODE raw
cmdb(>         ,"lo_ordertotalprice" INTEGER   ENCODE raw
cmdb(>         ,"lo_discount" INTEGER   ENCODE raw
cmdb(>         ,"lo_revenue" INTEGER   ENCODE raw
cmdb(>         ,"lo_supplycost" INTEGER   ENCODE raw
cmdb(>         ,"lo_tax" INTEGER   ENCODE raw
cmdb(>         ,"lo_commitdate" INTEGER   ENCODE raw
cmdb(>         ,"lo_shipmode" VARCHAR(10)   ENCODE raw
cmdb(> )
cmdb-> DISTSTYLE EVEN
cmdb-> SORTKEY(lo_orderdate)
cmdb-> ;
CREATE TABLE
cmdb=> INSERT INTO lineorder_test SELECT * FROM lineorder limit 1;
INSERT 0 1
cmdb=> SELECT "schema", "table", diststyle, unsorted FROM SVV_TABLE_INFO WHERE "schema" = 'cm_ishikawa_satoru' AND "table" = 'lineorder_test';
       schema       |     table      | diststyle | unsorted
--------------------+----------------+-----------+----------
 cm_ishikawa_satoru | lineorder_test | EVEN      |     0.00
(1 row)

cmdb=> ALTER TABLE lineorder_test ALTER DISTKEY lo_orderkey;
ALTER TABLE
cmdb=> SELECT "schema", "table", diststyle, unsorted FROM SVV_TABLE_INFO WHERE "schema" = 'cm_ishikawa_satoru' AND "table" = 'lineorder_test';
       schema       |     table      |    diststyle     | unsorted
--------------------+----------------+------------------+----------
 cm_ishikawa_satoru | lineorder_test | KEY(lo_orderkey) |   100.00
(1 row)

同様にALTER DISTSTYLE KEY DISTKEY column_nameにて、分散キーをlo_linenumberに変更します。同様に分散スタイル・分散キーともに変更できました。しかし、全てソートされていません。(unsortedが100%)

cmdb=> CREATE TABLE IF NOT EXISTS "cm_ishikawa_satoru"."lineorder_test"
cmdb-> (
cmdb(>         "lo_orderkey" INTEGER   ENCODE raw
cmdb(>         ,"lo_linenumber" INTEGER   ENCODE raw
cmdb(>         ,"lo_custkey" INTEGER   ENCODE raw
cmdb(>         ,"lo_partkey" INTEGER   ENCODE raw
cmdb(>         ,"lo_suppkey" INTEGER   ENCODE raw
cmdb(>         ,"lo_orderdate" INTEGER   ENCODE raw
cmdb(>         ,"lo_orderpriority" VARCHAR(15)   ENCODE raw
cmdb(>         ,"lo_shippriority" VARCHAR(1)   ENCODE raw
cmdb(>         ,"lo_quantity" INTEGER   ENCODE raw
cmdb(>         ,"lo_extendedprice" INTEGER   ENCODE raw
cmdb(>         ,"lo_ordertotalprice" INTEGER   ENCODE raw
cmdb(>         ,"lo_discount" INTEGER   ENCODE raw
cmdb(>         ,"lo_revenue" INTEGER   ENCODE raw
cmdb(>         ,"lo_supplycost" INTEGER   ENCODE raw
cmdb(>         ,"lo_tax" INTEGER   ENCODE raw
cmdb(>         ,"lo_commitdate" INTEGER   ENCODE raw
cmdb(>         ,"lo_shipmode" VARCHAR(10)   ENCODE raw
cmdb(> )
cmdb-> DISTSTYLE EVEN
cmdb-> SORTKEY(lo_orderdate)
cmdb-> ;
CREATE TABLE
cmdb=>
cmdb=> INSERT INTO lineorder_test SELECT * FROM lineorder limit 1;
INSERT 0 1
cmdb=>
cmdb=> SELECT "schema", "table", diststyle, unsorted FROM SVV_TABLE_INFO WHERE "schema" = 'cm_ishikawa_satoru' AND "table" = 'lineorder_test';
       schema       |     table      | diststyle | unsorted
--------------------+----------------+-----------+----------
 cm_ishikawa_satoru | lineorder_test | EVEN      |     0.00
(1 row)

cmdb=>
cmdb=> ALTER TABLE lineorder_test ALTER DISTSTYLE KEY DISTKEY lo_linenumber;
ALTER TABLE

cmdb=> SELECT "schema", "table", diststyle, unsorted FROM SVV_TABLE_INFO WHERE "schema" = 'cm_ishikawa_satoru' AND "table" = 'lineorder_test';
       schema       |     table      |     diststyle      | unsorted
--------------------+----------------+--------------------+----------
 cm_ishikawa_satoru | lineorder_test | KEY(lo_linenumber) |   100.00
(1 row)

分散スタイルKEY(lo_linenumber)からKEY(lo_custkey)に変更

次に上記の分散スタイルKEYのテーブルを作成して、ALTER DISTKEY column_nameにて、分散キーをlo_custkeyに変更します。分散スタイル・分散キーともに変更できました。しかし、全てソートされていませんでした。(unsortedが100%)

また、分散キーは複数回変更できることも確認できました。

cmdb=> SELECT "schema", "table", diststyle, unsorted FROM SVV_TABLE_INFO WHERE "schema" = 'cm_ishikawa_satoru' AND "table" = 'lineorder_test';
       schema       |     table      |     diststyle      | unsorted
--------------------+----------------+--------------------+----------
 cm_ishikawa_satoru | lineorder_test | KEY(lo_linenumber) |   100.00
(1 row)

cmdb=> ALTER TABLE lineorder_test ALTER DISTSTYLE KEY DISTKEY lo_custkey;
ALTER TABLE

cmdb=> SELECT "schema", "table", diststyle, unsorted FROM SVV_TABLE_INFO WHERE "schema" = 'cm_ishikawa_satoru' AND "table" = 'lineorder_test';
       schema       |     table      |    diststyle    | unsorted
--------------------+----------------+-----------------+----------
 cm_ishikawa_satoru | lineorder_test | KEY(lo_custkey) |   100.00
(1 row)

分散スタイルKEY(lo_orderkey)から分散スタイルEVENに変更

マニュアルに明記されていませんが、分散スタイルKEYのテーブルをEVENに変更できないことを確認しました。

cmdb=> SELECT "schema", "table", diststyle, unsorted FROM SVV_TABLE_INFO WHERE "schema" = 'cm_ishikawa_satoru' AND "table" = 'lineorder_test';
       schema       |     table      |    diststyle    | unsorted
--------------------+----------------+-----------------+----------
 cm_ishikawa_satoru | lineorder_test | KEY(lo_custkey) |   100.00
(1 row)

cmdb=> ALTER TABLE lineorder_test ALTER DISTSTYLE KEY DISTKEY EVEN;
ERROR:  Column "even" of relation "lineorder_test" does not exist

cmdb=> ALTER TABLE lineorder_test ALTER DISTSTYLE EVEN;
ERROR:  syntax error at or near "EVEN"
LINE 1: ALTER TABLE lineorder_test ALTER DISTSTYLE EVEN;
                                                   ^

分散スタイルALLからKEY(lo_orderkey)に変更

マニュアルには、「テーブルの元の DISTSTYLE が EVEN または KEY である DISTKEY のみ変更することができます。」とありますが、分散スタイル・分散キーともに変更できました。しかし、全てソートされていません。(unsortedが100%)

cmdb=> CREATE TABLE IF NOT EXISTS "cm_ishikawa_satoru"."lineorder_test"
cmdb-> (
cmdb(>         "lo_orderkey" INTEGER   ENCODE raw
cmdb(>         ,"lo_linenumber" INTEGER   ENCODE raw
cmdb(>         ,"lo_custkey" INTEGER   ENCODE raw
cmdb(>         ,"lo_partkey" INTEGER   ENCODE raw
cmdb(>         ,"lo_suppkey" INTEGER   ENCODE raw
cmdb(>         ,"lo_orderdate" INTEGER   ENCODE raw
cmdb(>         ,"lo_orderpriority" VARCHAR(15)   ENCODE raw
cmdb(>         ,"lo_shippriority" VARCHAR(1)   ENCODE raw
cmdb(>         ,"lo_quantity" INTEGER   ENCODE raw
cmdb(>         ,"lo_extendedprice" INTEGER   ENCODE raw
cmdb(>         ,"lo_ordertotalprice" INTEGER   ENCODE raw
cmdb(>         ,"lo_discount" INTEGER   ENCODE raw
cmdb(>         ,"lo_revenue" INTEGER   ENCODE raw
cmdb(>         ,"lo_supplycost" INTEGER   ENCODE raw
cmdb(>         ,"lo_tax" INTEGER   ENCODE raw
cmdb(>         ,"lo_commitdate" INTEGER   ENCODE raw
cmdb(>         ,"lo_shipmode" VARCHAR(10)   ENCODE raw
cmdb(> )
cmdb-> DISTSTYLE ALL
cmdb-> SORTKEY(lo_orderdate)
cmdb-> ;
CREATE TABLE
cmdb=> INSERT INTO lineorder_test SELECT * FROM lineorder limit 1;
INSERT 0 1

cmdb=> SELECT "schema", "table", diststyle, unsorted FROM SVV_TABLE_INFO WHERE "schema" = 'cm_ishikawa_satoru' AND "table" = 'lineorder_test';
       schema       |     table      | diststyle | unsorted
--------------------+----------------+-----------+----------
 cm_ishikawa_satoru | lineorder_test | ALL       |     0.00
(1 row)

cmdb=> ALTER TABLE lineorder_test ALTER DISTKEY lo_orderkey;
ALTER TABLE

cmdb=> SELECT "schema", "table", diststyle, unsorted FROM SVV_TABLE_INFO WHERE "schema" = 'cm_ishikawa_satoru' AND "table" = 'lineorder_test';
       schema       |     table      |    diststyle     | unsorted
--------------------+----------------+------------------+----------
 cm_ishikawa_satoru | lineorder_test | KEY(lo_orderkey) |   100.00
(1 row)

分散スタイルAUTOからKEY(lo_orderkey)に変更

同じく、マニュアルには、「テーブルの元の DISTSTYLE が EVEN または KEY である DISTKEY のみ変更することができます。」とありますが、分散スタイル・分散キーともに変更できました。しかし、全てソートされていません。(unsortedが100%)

cmdb=> CREATE TABLE IF NOT EXISTS "cm_ishikawa_satoru"."lineorder_test"
cmdb-> (
cmdb(>         "lo_orderkey" INTEGER   ENCODE raw
cmdb(>         ,"lo_linenumber" INTEGER   ENCODE raw
cmdb(>         ,"lo_custkey" INTEGER   ENCODE raw
cmdb(>         ,"lo_partkey" INTEGER   ENCODE raw
cmdb(>         ,"lo_suppkey" INTEGER   ENCODE raw
cmdb(>         ,"lo_orderdate" INTEGER   ENCODE raw
cmdb(>         ,"lo_orderpriority" VARCHAR(15)   ENCODE raw
cmdb(>         ,"lo_shippriority" VARCHAR(1)   ENCODE raw
cmdb(>         ,"lo_quantity" INTEGER   ENCODE raw
cmdb(>         ,"lo_extendedprice" INTEGER   ENCODE raw
cmdb(>         ,"lo_ordertotalprice" INTEGER   ENCODE raw
cmdb(>         ,"lo_discount" INTEGER   ENCODE raw
cmdb(>         ,"lo_revenue" INTEGER   ENCODE raw
cmdb(>         ,"lo_supplycost" INTEGER   ENCODE raw
cmdb(>         ,"lo_tax" INTEGER   ENCODE raw
cmdb(>         ,"lo_commitdate" INTEGER   ENCODE raw
cmdb(>         ,"lo_shipmode" VARCHAR(10)   ENCODE raw
cmdb(> )
cmdb-> DISTSTYLE AUTO
cmdb-> SORTKEY(lo_orderdate)
cmdb-> ;
CREATE TABLE
cmdb=> INSERT INTO lineorder_test SELECT * FROM lineorder limit 1;
INSERT 0 1

cmdb=> SELECT "schema", "table", diststyle, unsorted FROM SVV_TABLE_INFO WHERE "schema" = 'cm_ishikawa_satoru' AND "table" = 'lineorder_test';
       schema       |     table      | diststyle  | unsorted
--------------------+----------------+------------+----------
 cm_ishikawa_satoru | lineorder_test | AUTO(EVEN) |     0.00
(1 row)

cmdb=> ALTER TABLE lineorder_test ALTER DISTKEY lo_orderkey;
ALTER TABLE

cmdb=> SELECT "schema", "table", diststyle, unsorted FROM SVV_TABLE_INFO WHERE "schema" = 'cm_ishikawa_satoru' AND "table" = 'lineorder_test';
       schema       |     table      |    diststyle     | unsorted
--------------------+----------------+------------------+----------
 cm_ishikawa_satoru | lineorder_test | KEY(lo_orderkey) |   100.00
(1 row)

まとめ

分散キーの変更する機能は、以下のとおりです。

  • 全ての分散スタイルから分散スタイルKEYに変更可能
  • 分散スタイルKEYはその他の分散スタイル(AUTO、ALL、EVEN)に変更不可能
  • 分散キーは、何度でも変更可能

分散スタイル、分散キーを変更すると、データはソートされていないように見えました(システムビューSVV_TABLE_INFOを利用)、「インラインソート」が何を意味するのかは気になるところです。

従来、分散キーを変更するには、新しいテーブルを作成してデータをコピーした後、リネームする必要がありました。しかし、テーブルの置き換えは、テーブルのオブジェクトIDが変わるため参照しているビューの再作成や権限の再設定が必要でした。オブジェクトIDを変えずに分散スタイルや分散キーを変更できることは大変意義のあることがと思います。なお、ALTER TABLEの実行が完了するまで、テーブルの読み取りと書き込み操作をロックしますので、本番テーブルを変更する際にはこの動作を想定してご利用ください。

Amazon Redshiftのパフォーマンスチューニングテクニック Top 10 の課題 #2 - 偏ったテーブルデータにも記載がありますが、分散スタイルや分散キーを変更する際には以下の点も考慮してください。

良い分散キーは以下の様な特徴を持っているべきです

  • 高いカーディナリティ - クラスタのノードの数と比較して、その列の値が非常に多種なユニークな値を持っていること
  • 正規分布/低い偏り - 分散キーの各ユニークな値が、均等な回数、表内に現れること。これによってAmazon Redshiftは同じ数のレコードをクラスタ内の各ノードに配置することができます。
  • 頻繁にJOINされる - 分散キーの列は他の表とのJOINによく使われること。もしこの条件に当てはまりそうな列が多数あった場合は、最も大きい表とJOINするときに使われる列を選びます。