Verticaではテーブルに主キーを設定できますが、主キーを設定していても、デフォルトでは該当列に
重複データをロードできます。
本記事では主キーが設定された列の重複データをチェックする方法をご紹介します。
※主キーは、重複データのチェック以外にも、プリジョイン・プロジェクションを使用するために必要な制約です。
構文
重複データのチェックには、ANALYZE_CONSTRAINTS関数を使用します。|
1 |
dbadmin=> SELECT ANALYZE_CONSTRAINTS('スキーマ名.テーブル名','列名'); |
※列名は省略可能です。
実行例
col1列が主キーのテーブルに、重複データが格納されている場合の実行例です。|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
/* 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にデータロード時の日時を投入して削除対象行を絞り込めるようにし、
重複データ行の古い方を削除しています（最新データを正と仮定）。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
/* テーブル定義の確認 */ 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) |
