Amazon Redshift: テーブル作成時のIDENTITYオプションによる自動採番処理を行う

2015.07.24

主要なデータベースにはAuto Increment/自動採番の機能が提供されているかと思いますが、Amazon Redshiftも同等の仕組みが存在します。それがCREATE TABLEコマンドに於けるIDENTITYオプションになります。当エントリではその使い方についてサンプルデータを元に実践してみた内容を備忘録として残しておこうと思います。

IDENTITYオプションについて

オプションに関する詳細な内容については、以下公式ドキュメントに記載があります。文章量としても然程多く無いので全文を引用します。

IDENTITY(seed, step)

列が IDENTITY 列であることを指定する句。IDENTITY 列には、一意の自動生成値が含まれます。これらの値は、seed として指定された値から始まり、step として指定された数が増分されます。IDENTITY 列のデータ型は、INT または BIGINT にする必要があります。

IDENTITYオプション指定による自動採番実践

ここからは簡単ではありますが実践内容を見て行きたいと思います。利用する定義及びデータは以下公式ドキュメントの情報とします。

まずはデータをそのまま取り込むためのテーブルを用意し、

# CREATE TABLE public.lineorder (
  lo_orderkey      	integer     	  not null,
  lo_linenumber        	integer     	not null,
  lo_custkey           	integer     	not null,
  lo_partkey           	integer     	not null distkey,
  lo_suppkey           	integer     	not null,
  lo_orderdate         	integer     	not null sortkey,
  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
);

COPY文による取込処理を実施。

# COPY public.lineorder FROM 's3://awssampledb/ssbgz/lineorder' 
CREDENTIALS 'aws_access_key_id=XXXXXXXXXX;aws_secret_access_key=YYYYYYYYYY'
GZIP COMPUPDATE OFF;

ターミナル上での時間は計測していませんでしたが、管理コンソールで確認してみたところ6億件のデータを15分程で投入し終えていました。

# SELECT COUNT(*) FROM public.lineorder;
   count   
-----------
 600037902
(1 row)

データの並び順を確認してみます。ここではlo_orderkeylo_linenumberという項目を指定しました。lo_orderkey毎にN件のデータがあり、その順番がlo_linenumberで指定されている内容となっています。

# SELECT
   public.lineorder.lo_orderkey,
   public.lineorder.lo_linenumber
FROM
   public.lineorder
ORDER BY
   public.lineorder.lo_orderkey,
   public.lineorder.lo_linenumber
LIMIT 30;

 lo_orderkey | lo_linenumber 
-------------+---------------
           1 |             1
           1 |             2
           1 |             3
           1 |             4
           1 |             5
           1 |             6
           2 |             1
           3 |             1
           3 |             2
           3 |             3
           3 |             4
           3 |             5
           3 |             6
           4 |             1
           5 |             1
           5 |             2
           5 |             3
           6 |             1
           7 |             1
           7 |             2
           7 |             3
           7 |             4
           7 |             5
           7 |             6
           7 |             7
          32 |             1
          32 |             2
          32 |             3
          32 |             4
          32 |             5
(30 rows)
=#

6億件超のデータの最後の方も見てみます。lo_orderkeyベースではちょうど6億件、そこからlo_linenumberが複数件あるデータも存在する事で現在の件数となっている様です。

# SELECT
   public.lineorder.lo_orderkey,
   public.lineorder.lo_linenumber
FROM
   public.lineorder
ORDER BY
   public.lineorder.lo_orderkey DESC,
   public.lineorder.lo_linenumber DESC
LIMIT 30;

 lo_orderkey | lo_linenumber 
-------------+---------------
   600000000 |             3
   600000000 |             2
   600000000 |             1
   599999975 |             7
   599999975 |             6
   599999975 |             5
   599999975 |             4
   599999975 |             3
   599999975 |             2
   599999975 |             1
   599999974 |             3
   599999974 |             2
   599999974 |             1
   599999973 |             6
   599999973 |             5
   599999973 |             4
   599999973 |             3
   599999973 |             2
   599999973 |             1
   599999972 |             4
   599999972 |             3
   599999972 |             2
   599999972 |             1
   599999971 |             1
   599999970 |             5
   599999970 |             4
   599999970 |             3
   599999970 |             2
   599999970 |             1
   599999969 |             2
(30 rows)

次いでIDENTITYオプションを指定した項目を追加したテーブルを用意します。先頭列にlo_idという項目を用意し、この項目にIDENTITYオプションを指定しました。1から始めて1ずつインクリメントする指定を行なっています。

# CREATE TABLE public.lineorder_with_id (
  lo_id INTEGER IDENTITY(1,1) NOT NULL,                                                                                                 
  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
);

INSERT文実行。下記ドキュメントによると、以下のようにテーブル名の後に項目を指定する必要があるようです。テーブルデータは6億件超ありますが、フルでINSERT-SELECT文を実行してどれ位時間が掛かるか分からなかったのでとりあえず1000万件で試してみました。対象クラスタはdc1.largeの2ノードです。およそ3分程で投入が完了しました。

INSERT INTO public.lineorder_with_id (
  lo_orderkey,
  lo_linenumber,
  lo_custkey,
  lo_partkey,
  lo_suppkey,
  lo_orderdate,
  lo_orderpriority,
  lo_shippriority,
  lo_quantity,
  lo_extendedprice,
  lo_ordertotalprice,
  lo_discount,
  lo_revenue,
  lo_supplycost,
  lo_tax,
  lo_commitdate,
  lo_shipmode
)
SELECT
  public.lineorder.lo_orderkey,
  public.lineorder.lo_linenumber,
  public.lineorder.lo_custkey,
  public.lineorder.lo_partkey,
  public.lineorder.lo_suppkey,
  public.lineorder.lo_orderdate,
  public.lineorder.lo_orderpriority,
  public.lineorder.lo_shippriority,
  public.lineorder.lo_quantity,
  public.lineorder.lo_extendedprice,
  public.lineorder.lo_ordertotalprice,
  public.lineorder.lo_discount,
  public.lineorder.lo_revenue,
  public.lineorder.lo_supplycost,
  public.lineorder.lo_tax,
  public.lineorder.lo_commitdate,
  public.lineorder.lo_shipmode
FROM
  public.lineorder
ORDER BY
  public.lineorder.lo_orderkey,
  public.lineorder.lo_linenumber
LIMIT 10000000;
INSERT 0 10000000
Time: 177124.841 ms

投入完了後のデータ内容を確認してみましょう。まずは先頭の方から。問題無さそうです。

# SELECT
    public.lineorder_with_id.lo_id,
    public.lineorder_with_id.lo_orderkey,
    public.lineorder_with_id.lo_linenumber
  FROM
    public.lineorder_with_id
  ORDER BY
    public.lineorder_with_id.lo_id ASC
  LIMIT 30;
 lo_id | lo_orderkey | lo_linenumber 
-------+-------------+---------------
     1 |           1 |             1
     2 |           1 |             2
     3 |           1 |             3
     4 |           1 |             4
     5 |           1 |             5
     6 |           1 |             6
     7 |           2 |             1
     8 |           3 |             1
     9 |           3 |             2
    10 |           3 |             3
    11 |           3 |             4
    12 |           3 |             5
    13 |           3 |             6
    14 |           4 |             1
    15 |           5 |             1
    16 |           5 |             2
    17 |           5 |             3
    18 |           6 |             1
    19 |           7 |             1
    20 |           7 |             2
    21 |           7 |             3
    22 |           7 |             4
    23 |           7 |             5
    24 |           7 |             6
    25 |           7 |             7
    26 |          32 |             1
    27 |          32 |             2
    28 |          32 |             3
    29 |          32 |             4
    30 |          32 |             5
(30 rows)

Time: 349.435 ms

次いで末尾の方から。こちらも問題無く採番されています。

# SELECT
    public.lineorder_with_id.lo_id,
    public.lineorder_with_id.lo_orderkey,
    public.lineorder_with_id.lo_linenumber
  FROM
    public.lineorder_with_id
  ORDER BY
    public.lineorder_with_id.lo_id DESC
  LIMIT 30;

  lo_id   | lo_orderkey | lo_linenumber 
----------+-------------+---------------
 10000000 |    10000611 |             3
  9999999 |    10000611 |             2
  9999998 |    10000611 |             1
  9999997 |    10000610 |             2
  9999996 |    10000610 |             1
  9999995 |    10000609 |             6
  9999994 |    10000609 |             5
  9999993 |    10000609 |             4
  9999992 |    10000609 |             3
  9999991 |    10000609 |             2
  9999990 |    10000609 |             1
  9999989 |    10000608 |             7
  9999988 |    10000608 |             6
  9999987 |    10000608 |             5
  9999986 |    10000608 |             4
  9999985 |    10000608 |             3
  9999984 |    10000608 |             2
  9999983 |    10000608 |             1
  9999982 |    10000583 |             4
  9999981 |    10000583 |             3
  9999980 |    10000583 |             2
  9999979 |    10000583 |             1
  9999978 |    10000582 |             3
  9999977 |    10000582 |             2
  9999976 |    10000582 |             1
  9999975 |    10000581 |             3
  9999974 |    10000581 |             2
  9999973 |    10000581 |             1
  9999972 |    10000580 |             2
  9999971 |    10000580 |             1
(30 rows)

IDENTITYオプションの内容を変えて試してみます。以下の様な形でテーブルを作成しなおし、データを投入。

# CREATE TABLE public.lineorder_with_id (
   lo_id                INTEGER IDENTITY(90000001,3) NOT NULL,                                                                             
   lo_orderkey          INTEGER NOT NULL,
   lo_linenumber        INTEGER NOT NULL,
   lo_custkey           INTEGER NOT NULL,
   :

# INSERT INTO public.lineorder_with_id (
  lo_orderkey,
  lo_linenumber,
  :
LIMIT 1000000;
INSERT 0 1000000
Time: 156583.531 ms

結果を確認すると指定通りのルールで採番出来ている事を確認出来ました。

# SELECT
    public.lineorder_with_id.lo_id,
    public.lineorder_with_id.lo_orderkey,
    public.lineorder_with_id.lo_linenumber
  FROM
    public.lineorder_with_id
  ORDER BY
    public.lineorder_with_id.lo_id ASC
  LIMIT 10;
  lo_id   | lo_orderkey | lo_linenumber 
----------+-------------+---------------
 90000001 |           1 |             1
 90000004 |           1 |             2
 90000007 |           1 |             3
 90000010 |           1 |             4
 90000013 |           1 |             5
 90000016 |           1 |             6
 90000019 |           2 |             1
 90000022 |           3 |             1
 90000025 |           3 |             2
 90000028 |           3 |             3
(10 rows)

Time: 1066.520 ms
# SELECT
    public.lineorder_with_id.lo_id,
    public.lineorder_with_id.lo_orderkey,
    public.lineorder_with_id.lo_linenumber
  FROM
    public.lineorder_with_id
  ORDER BY
    public.lineorder_with_id.lo_id DESC
  LIMIT 10;
  lo_id   | lo_orderkey | lo_linenumber 
----------+-------------+---------------
 92999998 |      999939 |             5
 92999995 |      999939 |             4
 92999992 |      999939 |             3
 92999989 |      999939 |             2
 92999986 |      999939 |             1
 92999983 |      999938 |             5
 92999980 |      999938 |             4
 92999977 |      999938 |             3
 92999974 |      999938 |             2
 92999971 |      999938 |             1
(10 rows)

Time: 956.796 ms

まとめ

以上、データの投入(INSERT)時に於けるIDENTITYオプション指定の自動採番についてのご紹介でした。業務要件に拠っては今回ご紹介した様な『自動採番を行わせたい』ケースもあるかと思います。その際はこのオプションを有効活用してみてはいかがでしょうか。こちらからは以上です。

参考情報: