Verticaではテーブルに主キーを設定できますが、主キーを設定していても、デフォルトでは該当列に
重複データをロードできます。
本記事では主キーが設定された列の重複データをチェックする方法をご紹介します。
※主キーは、重複データのチェック以外にも、プリジョイン・プロジェクションを使用するために必要な制約です。
構文
重複データのチェックには、ANALYZE_CONSTRAINTS関数を使用します。
dbadmin=> SELECT ANALYZE_CONSTRAINTS('スキーマ名.テーブル名','列名');※列名は省略可能です。
実行例
col1列が主キーのテーブルに、重複データが格納されている場合の実行例です。
/* test1テーブル定義の確認 */
dbadmin=> \d test1;
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+-------------+------+-------------+----------+-------------+-------------
public | test1 | col1 | int | 8 | | t | t |
public | test1 | col2 | varchar(10) | 10 | | f | f |
public | test1 | col3 | timestamp | 8 | "sysdate"() | f | f |
(3 rows)
/* test1表のデータを確認。1行目と2行目が重複データ */
dbadmin=> dbadmin=> select * from test1;
col1 | col2 | col3
------+------+----------------------------
1 | 1-1 | 2018-08-06 16:34:17.492298
1 | 1-1 | 2018-08-06 16:34:24.250089
2 | 2-1 | 2018-08-06 16:34:28.73048
3 | 3-1 | 2018-08-06 16:34:32.388474
(4 rows)
/* ANALYZE_CONSTRAINTS関数を使用して重複データを確認 */
/* キー項目col1=1のデータが重複していることを検出 */
dbadmin=> SELECT ANALYZE_CONSTRAINTS('public.test1');
Schema Name | Table Name | Column Names | Constraint Name | Constraint Type | Column Values
-------------+------------+--------------+-----------------+-----------------+---------------
public | test1 | col1 | C_PRIMARY | PRIMARY | ('1')
(1 row)重複データを検出した場合の対処
重複データを検出した場合は、以下の対処が考えられます。
1) 重複データ行を全て削除し、最新のデータをロードする
2) 重複データ行のどちらか一方を削除する
2番目の対処については、主キー項目以外に削除対象行を絞り込み可能な項目が必要になります。
以下の例では、col3にデータロード時の日時を投入して削除対象行を絞り込めるようにし、
重複データ行の古い方を削除しています（最新データを正と仮定）。
/* テーブル定義の確認 */
dbadmin=> \d test1;
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+-------------+------+-------------+----------+-------------+-------------
public | test1 | col1 | int | 8 | | t | t |
public | test1 | col2 | varchar(10) | 10 | | f | f |
public | test1 | col3 | timestamp | 8 | "sysdate"() | f | f |
(3 rows)
/* test1表のデータを一旦削除 */
dbadmin=> TRUNCATE TABLE test1;
TRUNCATE TABLE
/* 同一データを2回連続でロードし、データを重複させる。col3にはデータロード実行時の日時を投入 */
dbadmin=> COPY test1(col1, col2, col3 as sysdate) FROM '/tmp/test1.csv' DELIMITER ',';
Rows Loaded
-------------
3
(1 row)
dbadmin=> COPY test1(col1, col2, col3 as sysdate) FROM '/tmp/test1.csv' DELIMITER ',';
Rows Loaded
-------------
3
(1 row)
/* test1表のデータを確認 */
/* 1行目と2行目、3行目と4行目、5行目と6行目がそれぞれ重複データ */
dbadmin=> SELECT * FROM test1 ORDER BY col1, col3;
col1 | col2 | col3
------+------+----------------------------
1 | 1-1 | 2018-08-06 17:19:27.373734
1 | 1-1 | 2018-08-06 17:19:32.320163
2 | 2-1 | 2018-08-06 17:19:27.373734
2 | 2-1 | 2018-08-06 17:19:32.320163
3 | 3-1 | 2018-08-06 17:19:27.373734
3 | 3-1 | 2018-08-06 17:19:32.320163
(6 rows)
/* ANALYZE_CONSTRAINTS関数を使用して重複データを確認 */
/* 3種類の重複データが存在することを検出 */
dbadmin=> SELECT ANALYZE_CONSTRAINTS('public.test1');
Schema Name | Table Name | Column Names | Constraint Name | Constraint Type | Column Values
-------------+------------+--------------+-----------------+-----------------+---------------
public | test1 | col1 | C_PRIMARY | PRIMARY | ('2')
public | test1 | col1 | C_PRIMARY | PRIMARY | ('3')
public | test1 | col1 | C_PRIMARY | PRIMARY | ('1')
(3 rows)
/* キー項目col1=1の重複データの内、col3が最も古い行を削除 */
dbadmin=> DELETE FROM test1
dbadmin-> WHERE col1 = 1
dbadmin-> AND col3 = (SELECT MIN(col3)
dbadmin(> FROM test1
dbadmin(> WHERE col1=1);
OUTPUT
--------
1
(1 row)
/* キー項目col1=1の古い行が削除されたことを確認 */
dbadmin=> SELECT * FROM test1 ORDER BY col1, col3;
col1 | col2 | col3
------+------+----------------------------
1 | 1-1 | 2018-08-06 17:19:32.320163
2 | 2-1 | 2018-08-06 17:19:27.373734
2 | 2-1 | 2018-08-06 17:19:32.320163
3 | 3-1 | 2018-08-06 17:19:27.373734
3 | 3-1 | 2018-08-06 17:19:32.320163
(5 rows)検証バージョンについて
この記事の内容はVertica 9.1で確認しています。