日付データを年度単位でパーティショニングする方法

1 はじめに
2 手順
- 2.1 方法1. ALTER TABLE文でパーティション定義
- 2.2 方法2. ファンクションで実装
3 注意事項
4 検証バージョンについて
5 更新履歴

はじめに

この記事では日付データを年度単位でパーティショニングする方法をご紹介します。

参考情報：パーティショニングの概要
http://vertica-tech.ashisuto.co.jp/partition_overview/

手順

通常、年単位でパーティショニングをすると、1月から12月までの範囲になります。業務要件で4月から翌年3月までを単位として年度でパーティショニングしたい場合には、以下2つの方法で設定することが可能です。

方法1. ALTER TABLE文によるパーティション定義の実装
方法2. ファンクションによるパーティション定義の実装

それぞれの手順をご紹介します。

方法1. ALTER TABLE文でパーティション定義

ALTER TABLE文を利用するとpartition byのあとに条件句を入れることが可能です。ここでCASE文を組み合わせることで「4月から翌年3月までを単位とした年度」など、任意の期間をパーティションの単位とすることができます。

(1)テーブル作成

 create table test1(col1 int, col2 date not null);

1	create table test1(col1 int, col2 date not null);

(2)ALTER TABLE文でパーティション定義を実装
以下のようにCASE文を定義することで、4月以降と4月より前の月（1～3月）の年を分け、年度単位でパーティショニングされます。

 ALTER TABLE test1 PARTITION BY (CASE WHEN date_part('month',col2) < 4 THEN date_part('year',col2)-1 ELSE date_part('year',col2) END) ;

1	ALTER TABLE test1 PARTITION BY (CASE WHEN date_part('month',col2) < 4 THEN date_part('year',col2)-1 ELSE date_part('year',col2) END) ;

(3)テストデータ挿入
以下のデータを挿入します。
2014年度　1件
2015年度　2件
2016年度　2件
2017年度　1件

 insert /*+DIRECT*/ into test1 values(1,'2015-01-01');
 insert /*+DIRECT*/ into test1 values(2,'2015-04-01');
 insert /*+DIRECT*/ into test1 values(3,'2016-01-01');
 insert /*+DIRECT*/ into test1 values(4,'2016-04-01');
 insert /*+DIRECT*/ into test1 values(5,'2017-01-01');
 insert /*+DIRECT*/ into test1 values(6,'2017-04-01');
 commit;

insert /*+DIRECT*/ into test1 values(1,'2015-01-01');

insert /*+DIRECT*/ into test1 values(2,'2015-04-01');

insert /*+DIRECT*/ into test1 values(3,'2016-01-01');

insert /*+DIRECT*/ into test1 values(4,'2016-04-01');

insert /*+DIRECT*/ into test1 values(5,'2017-01-01');

insert /*+DIRECT*/ into test1 values(6,'2017-04-01');

commit;

(4)データの確認

 select * from test1;

  <出力例>
   col1 |    col2
  ------+------------
      1 | 2015-01-01
      2 | 2015-04-01
      3 | 2016-01-01
      4 | 2016-04-01
      5 | 2017-01-01
      6 | 2017-04-01
  (6 rows)

select * from test1;

<出力例>

col1 | col2

------+------------

1 | 2015-01-01

2 | 2015-04-01

3 | 2016-01-01

4 | 2016-04-01

5 | 2017-01-01

6 | 2017-04-01

(6 rows)

(5)パーティション状況を確認
実際にパーティション化されているか確認してみましょう。

 select projection_name,partition_key,ros_row_count from
 partitions where projection_name like '%test1%' order by 2;

  ＜出力例＞
   projection_name | partition_key | ros_row_count
  -----------------+---------------+---------------
   tbl1_super      | 2014          |             1
   tbl1_super      | 2015          |             2
   tbl1_super      | 2016          |             2
   tbl1_super      | 2017          |             1
  (4 rows)

select projection_name,partition_key,ros_row_count from

partitions where projection_name like '%test1%' order by 2;

＜出力例＞

projection_name | partition_key | ros_row_count

-----------------+---------------+---------------

tbl1_super | 2014 | 1

tbl1_super | 2015 | 2

tbl1_super | 2016 | 2

tbl1_super | 2017 | 1

(4 rows)

テストデータがそれぞれ想定した年度に振り分けられていることが「ros_row_count列」の値から分かります。
※同じ結果にならない場合は、後述の注意事項をご確認ください。

方法2. ファンクションで実装

ファンクションを利用して任意の単位でパーティショニングすることができます。
方法1.と同様にCASE文を組み合わせて任意の期間にパーティショニングの指定をします。

(1)ファンクションの定義

  drop function d_f1(in1 timestamp);
  create function d_f1(in1 timestamp) return char(4) as
  begin
    return(case when to_char(in1,'mmdd') >= 401
  then
    to_char(in1,'YYYY')::char(4) 
  else
    to_char(add_months(in1,-11),'YYYY')::char(4) end);
  end;

drop function d_f1(in1 timestamp);

create function d_f1(in1 timestamp) return char(4) as

begin

return(case when to_char(in1,'mmdd') >= 401

then

to_char(in1,'YYYY')::char(4)

else

to_char(add_months(in1,-11),'YYYY')::char(4) end);

end;

(2)テーブルの作成

  drop table tbl1 cascade;
  create table tbl1(年月日 timestamp,年度 char(10) not null default d_f1(年月日)) partition by 年度;

1 2	drop table tbl1 cascade; create table tbl1(年月日 timestamp,年度 char(10) not null default d_f1(年月日)) partition by 年度;

(3)テストデータの挿入
今回の例では以下のデータを挿入します。
2016年度　4件
2017年度　3件
2018年度　1件

  insert /*+ direct */ into tbl1(年月日) values('2016/12/31');
  insert /*+ direct */ into tbl1(年月日) values('2017/1/1');
  insert /*+ direct */ into tbl1(年月日) values('2017/1/25');
  insert /*+ direct */ into tbl1(年月日) values('2017/3/31');
  insert /*+ direct */ into tbl1(年月日) values('2017/4/1');
  insert /*+ direct */ into tbl1(年月日) values('2017/12/31');
  insert /*+ direct */ into tbl1(年月日) values('2018/1/1');
  insert /*+ direct */ into tbl1(年月日) values('2018/4/1');
  commit;

insert /*+ direct */ into tbl1(年月日) values('2016/12/31');

insert /*+ direct */ into tbl1(年月日) values('2017/1/1');

insert /*+ direct */ into tbl1(年月日) values('2017/1/25');

insert /*+ direct */ into tbl1(年月日) values('2017/3/31');

insert /*+ direct */ into tbl1(年月日) values('2017/4/1');

insert /*+ direct */ into tbl1(年月日) values('2017/12/31');

insert /*+ direct */ into tbl1(年月日) values('2018/1/1');

insert /*+ direct */ into tbl1(年月日) values('2018/4/1');

commit;

(4)パーティション状況を確認

   select projection_name,partition_key,ros_row_count from
   partitions where projection_name like '%tbl1%' order by 2;

  ＜出力例＞
    projection_name |partition_key | ros_row_count
  ------------------+--------------+--------------
   tbl1_super       | 2016         |             4
   tbl1_super       | 2017         |             3
   tbl1_super       | 2018         |             1
  (3 rows)

select projection_name,partition_key,ros_row_count from

partitions where projection_name like '%tbl1%' order by 2;

＜出力例＞

projection_name |partition_key | ros_row_count

------------------+--------------+--------------

tbl1_super | 2016 | 4

tbl1_super | 2017 | 3

tbl1_super | 2018 | 1

(3 rows)

ファンクションを利用した場合にも、テストデータがそれぞれ想定した年度に振り分けられていることが分かります。
※同じ結果にならない場合は、後述の注意事項をご確認ください。

注意事項

データがパーティション化されるのはMergeoutが実行されるタイミングです。
このため、確認するタイミングによってはパーティション化されていない場合があります。

もし結果をすぐに確認する場合には、以下の記事を参考に手動でMergeoutを実行してからご確認ください。

【参考】
MoveoutとMergeout
MoveoutとMergeoutの手動実行

検証バージョンについて

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

更新履歴

2019/04/15　検証バージョンを9.2に変更
2017/03/17　本記事を公開

MENU