Amazon Redshift: テーブル作成時のIDENTITYオプションによる自動採番処理を行う
主要なデータベースには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_orderkeyとlo_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オプション指定の自動採番についてのご紹介でした。業務要件に拠っては今回ご紹介した様な『自動採番を行わせたい』ケースもあるかと思います。その際はこのオプションを有効活用してみてはいかがでしょうか。こちらからは以上です。
![[レポート]AWSのデータ取り込み戦略 - Solving different data ingestion use cases with AWS (ANT330) #AWSreInvent](https://images.ctfassets.net/ct0aopd36mqt/3IQLlbdUkRvu7Q2LupRW2o/edff8982184ea7cc2d5efa2ddd2915f5/reinvent-2024-sessionreport-jp.jpg)
![[Redshift] テーブルへのアクセス権付与権限について調べてみた](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-63f1274931b942e9a92e601c1127ad73/cfe87ec6d62fa2fc3c474ed4cb2f6c2e/amazon-redshift)
