はじめに

MOVE_PARTITIONS_TO_TABLE関数を使用して、パーティションをあるテーブルから別のテーブルに移動できます。
この機能を利用して、アクセス頻度の低いパーティションをアーカイブできます。

構文

MOVE_PARTITIONS_TO_TABLE (
    '[[database.]schema.]source‑table',
    'min‑range‑value',
    'max‑range‑value',
    '[[database.]schema.]target-table'
     [, force‑split]
)

ソーステーブル…データの移行元テーブル
ターゲットテーブル…データの移行先テーブル

使用例

任意の範囲のパーティションを、ソーステーブルからターゲットテーブルへ移動させる一連の手順を以下にご案内します。

■年単位のパーティションテーブルを作成
ソーステーブルとターゲットテーブルをそれぞれ作成します。

/*ソーステーブル作成*/
dbadmin=> CREATE TABLE new_t1(col1 date NOT NULL, col2 varchar(30)) PARTITION BY EXTRACT (year FROM col1);
WARNING 6100: Using PARTITION expression that returns a Numeric value
HINT: This PARTITION expression may cause too many data partitions. Use of an expression that returns a more accurate value, such as a regular VARCHAR or INT, is encouraged
CREATE TABLE

※WARNING 6100は無視可能です。

/*ターゲットテーブル作成*/
 dbadmin=> CREATE TABLE old_t1 LIKE new_t1 INCLUDING PROJECTIONS;
 CREATE TABLE

※ターゲットテーブルの作成は、以下の記事をご確認ください。
既存テーブルのテーブル定義のみをコピーして、新規にテーブルを作成する方法
https://www.ashisuto.co.jp/cm/analytics-database/create_table_like.html

■データロード
サンプルデータをソーステーブルにロードします。

\o /dev/null
INSERT INTO new_t1 VALUES(to_date('20140821','YYYYMMDD'), '鉛筆');
INSERT INTO new_t1 VALUES(to_date('20150821','YYYYMMDD'), '消しゴム');
INSERT INTO new_t1 VALUES(to_date('20160821','YYYYMMDD'), 'ものさし');
INSERT INTO new_t1 VALUES(to_date('20170821','YYYYMMDD'), 'はさみ');
INSERT INTO new_t1 VALUES(to_date('20180821','YYYYMMDD'), 'ボールペン');
INSERT INTO new_t1 VALUES(to_date('20190821','YYYYMMDD'), 'シャープペンシル');
COMMIT;
\o

■ムーブアウト実行
ロードしたデータをディスクに移動させます。

dbadmin=> SELECT DO_TM_TASK('moveout','new_t1');
 DO_TM_TASK
-------------------------------------------------------------------------
 Task: moveout
(Table: public.new_t1) (Projection: public.new_t1_super)

(1 row)

■テーブルデータの確認
両テーブルの現在保持しているデータを確認します。

/*ソーステーブルのデータを確認*/
dbadmin=> SELECT * FROM new_t1;
 col1 | col2
------------+------------------
 2018-08-21 | ボールペン
 2016-08-21 | ものさし
 2019-08-21 | シャープペンシル
 2015-08-21 | 消しゴム
 2017-08-21 | はさみ
 2014-08-21 | 鉛筆
(6 rows)

/*ターゲットテーブルのデータを確認*/
dbadmin=> SELECT * FROM old_t1;
 col1 | col2
------+------
(0 rows)

■PARTITIONSシステムテーブルを確認
システムテーブルからパーティションキー列が格納されている場所を確認します。

dbadmin=> SELECT * FROM PARTITIONS ORDER BY projection_name, partition_key;
 partition_key | projection_id | table_schema | projection_name | ros_id | ros_size_bytes | ros_row_count | node_name | deleted_row_count | location_label
---------------+-------------------+--------------+-----------------+-------------------+----------------+---------------+-----------------+-------------------+----------------
 2014 | 45035996273838138 | public | new_t1_super | 45035996273838175 | 83 | 1 | v_test_node0001 | 0 |
 2015 | 45035996273838138 | public | new_t1_super | 54043195528576825 | 89 | 1 | v_test_node0003 | 0 |
 2016 | 45035996273838138 | public | new_t1_super | 49539595901206451 | 89 | 1 | v_test_node0002 | 0 |
 2017 | 45035996273838138 | public | new_t1_super | 45035996273838169 | 86 | 1 | v_test_node0001 | 0 |
 2018 | 45035996273838138 | public | new_t1_super | 49539595901206445 | 93 | 1 | v_test_node0002 | 0 |
 2019 | 45035996273838138 | public | new_t1_super | 54043195528576819 | 101 | 1 | v_test_node0003 | 0 |
(6 rows)

→projection_name列を確認すると、全パーティションのデータがnew_t1_superプロジェクションに格納されていることがわかります。

■一部のパーティションをnew_t1テーブルからold_t1テーブルに移動

dbadmin=> SELECT MOVE_PARTITIONS_TO_TABLE('test.public.new_t1'
dbadmin(> ,'2014'
dbadmin(> ,'2016'
dbadmin(> ,'test.public.old_t1'
dbadmin(> ,true);
 MOVE_PARTITIONS_TO_TABLE
-------------------------------------------------
 3 distinct partition values moved at epoch 37.

(1 row)

■テーブルデータの確認
両テーブルの現在保持しているデータを確認します。

/*ソーステーブルのデータを確認*/
dbadmin=> SELECT * FROM new_t1;
 col1 | col2
------------+------------------
 2018-08-21 | ボールペン
 2019-08-21 | シャープペンシル
 2017-08-21 | はさみ
(3 rows)

/*ターゲットテーブルのデータを確認*/
dbadmin=> SELECT * FROM old_t1;
 col1 | col2
------------+----------
 2016-08-21 | ものさし
 2015-08-21 | 消しゴム
 2014-08-21 | 鉛筆
(3 rows)

→2014年~2016年のデータがold_t1テーブルに移動しています。

■PARTITIONSシステムテーブルを確認
システムテーブルからパーティションキー列が格納されている場所を確認します。

dbadmin=> SELECT * FROM PARTITIONS ORDER BY projection_name, partition_key;
 partition_key | projection_id | table_schema | projection_name | ros_id | ros_size_bytes | ros_row_count | node_name | deleted_row_count | location_label
---------------+-------------------+--------------+-----------------+-------------------+----------------+---------------+-----------------+-------------------+----------------
 2017 | 45035996273838138 | public | new_t1_super | 45035996273838169 | 86 | 1 | v_test_node0001 | 0 |
 2018 | 45035996273838138 | public | new_t1_super | 49539595901206445 | 93 | 1 | v_test_node0002 | 0 |
 2019 | 45035996273838138 | public | new_t1_super | 54043195528576819 | 101 | 1 | v_test_node0003 | 0 |
 2014 | 45035996273838184 | public | old_t1_super | 45035996273838195 | 83 | 1 | v_test_node0001 | 0 |
 2015 | 45035996273838184 | public | old_t1_super | 54043195528576837 | 89 | 1 | v_test_node0003 | 0 |
 2016 | 45035996273838184 | public | old_t1_super | 49539595901206463 | 89 | 1 | v_test_node0002 | 0 |
(6 rows)

→partition_keyとprojection_nameから、2014~2016パーティションがold_t1_superプロジェクションに移動していることがわかります。

注意事項

権限について

MOVE_PARTITIONS_TO_TABLEを実行するには、次の内いずれかの一つ条件を満たす必要があります。
・DBADMINロールを付与されている
・ソーステーブルとターゲットテーブルの所有者である
・ソースおよびターゲットスキーマに対するUSAGE権限、ソーステーブルに対するTRUNCATE権限、およびターゲットテーブルに対するINSERT権限を付与されている

また、ターゲットテーブルが存在していない場合、ターゲットスキーマに対するCREATE権限を保持していれば、MOVE_PARTITIONS_TO_TABLEの実行時にテーブルを作成します。

テーブル属性の要件

ソーステーブルとターゲットテーブルは、以下の設定が同じである必要があります。
・NOT NULL制約を含む列定義
・セグメンテーションの有無
・パーティション句
・プロジェクション数
・プロジェクションのソート順
・主キーおよび一意キーの制約
※有効/無効の状態は問いません。
ただし、主キーまたは一意キーが有効になっているターゲットテーブルに対し、重複したパーティションキー列の値が挿入される場合、処理はロールバックされます。

テーブルの制限

以下のオブジェクトはソーステーブルあるいはターゲットテーブルとして使用できません。
・一時テーブル
・ビュー
・システムテーブル
・外部テーブル

参考情報

MOVE_PARTITIONS_TO_TABLE
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/PartitionManagement/MOVE_PARTITIONS_TO_TABLE.htm

検証バージョンについて

この記事の内容はVertica 9.2で確認しています。

更新履歴

2019/08/22　本記事を公開