目次
はじめに
Verticaではアーキテクチャの関係上、大量データのロード処理はINSERTではなくCOPYコマンドを利用することが推奨されていますが、日々のバッチ処理等において、既存データへの更新処理が発生する場合の手法について、ご相談されることがよくあります。本記事では、データの更新が伴う、データロード処理の手法について整理していますので、データ連携の手法にお悩みの方はご参考にいただければと思います。
データ更新が伴うデータロード処理について
データ更新が伴うデータロード処理は、テーブルデータ量や更新量、バッチ処理に許容される時間を加味して方法を検討する必要があります。データ更新が伴うデータロード処理は主に以下の方法から検討します。TRUNCATE + COPY
毎回、テーブルデータをTRUNCATEし、COPYコマンドにて全件のデータ連携を実施します。いわゆる洗い替えです。実行例
更新対象のテーブル：lineorder
1.TRUNCATE文によるデータ削除
|
1 |
dbadmin=> TRUNCATE TABLE lineorder; |
2.COPY文によるデータロード
|
1 |
dbadmin=> COPY lineorder FROM '/data/csv/lineorder_update.csv'; |
※なお、Vertica9.3以降より、TRUNCATE実行後に統計情報が”NONE”(統計情報が取得されていない状態)となるように仕様変更されております。
そのため、TRUNCATE実行後には明示的に統計情報を取得する必要がありますので、ご注意ください。
手動で統計情報を取得する方法
http://vertica-tech.ashisuto.co.jp/collect_analyze/
DELETE + COPY
CSVデータを一度ワークテーブルにロードし、DELETE ＋ COPYにてデータ連携を実施します。
実行例
更新対象のテーブル：lineorder
ワークテーブル：lineorder_temp
プライマリキー：LO_CUSTKEY
ワークテーブル：lineorder_temp
プライマリキー：LO_CUSTKEY
1.COPYコマンドによるワークテーブルへのデータロード
|
1 |
dbadmin=> COPY lineorder_temp FROM '/data/csv/lineorder_update.csv'; |
2.更新対象のデータをDELETE
|
1 |
dbadmin=> DELETE FROM linerorder WHERE LO_CUSTKEY in(SELECT LO_CUSTKEY FROM lineorder_temp); |
3.本テーブルへのデータロード
|
1 |
dbadmin=> COPY lineorder FROM '/data/csv/lineorder_update.csv'; |
VerticaではDELETEを利用する場合、いくつか注意点があります。
以下の記事をご参照ください。
DELETEの注意点(１)
http://vertica-tech.ashisuto.co.jp/delete-notice-1/
DELETEの注意点(２)
http://vertica-tech.ashisuto.co.jp/delete-notice-2/
DELETEの注意点(３)
http://vertica-tech.ashisuto.co.jp/delete-notice-3/
MERGE
CSVデータを一度ワークテーブルにロードし、MERGE文にてデータ連携を実施します。
実行例
更新対象のテーブル：lineorder
ワークテーブル：lineorder_temp
プライマリキー：LO_CUSTKEY
ワークテーブル：lineorder_temp
プライマリキー：LO_CUSTKEY
1.COPYコマンドによるワークテーブルへのデータロード
|
1 |
dbadmin=> COPY lineorder_temp FROM '/data/csv/lineorder_update.csv'; |
2.MERGE文にて本テーブルへのデータロード
|
1 2 3 4 5 6 |
MERGE INTO lineorder USING lineorder_temp ON ( lineorder_temp.LO_CUSTKEY=lineorder.LO_CUSTKEY ) WHEN MATCHED AND lineorder_temp.LO_CUSTKEY=lineorder.LO_CUSTKEY THEN UPDATE SET LO_LINENUMBER=lineorder_temp.LO_LINENUMBER, LO_ORDERKEY=lineorder_temp.LO_ORDERKEY WHEN NOT MATCHED THEN INSERT (LO_CUSTKEY, LO_LINENUMBER, LO_ORDERKEY) VALUES (lineorder_temp.LO_CUSTKEY, lineorder_temp.LO_LINENUMBER, lineorder_temp.LO_ORDERKEY); |
各手法のメリット・デメリット
| 方法 | メリット | デメリット |
|---|---|---|
| TRUNCATE + COPY | 処理がシンプルであるため、開発工数を大幅に削減できる | 更新対象のデータが少なく、テーブルデータが多い場合、DELETE + COPYと比較し、処理に長時間かかる可能性がある |
| DELETE + COPY | データ処理量が少なくなるため、処理が早く完了する可能性がある | 処理が複雑になるため、開発工数がかかる |
| MERGE | ・DELETE + COPYと比較すると、処理がシンプルになるため、開発工数を削減できる ・TRUNCATE + COPYと比較すると、データ処理量が少なくなるため、処理が早く完了する可能性がある | ・データ走査が全件に実行されるため、データ件数が増えると処理に時間がかかる可能性がある ・MERGEには列を記述する必要があるため、列定義が変更されるとメンテンスが発生する |
参考情報
データロードの基本http://vertica-tech.ashisuto.co.jp/dataload_overview/
MERGE文の使用方法
http://vertica-tech.ashisuto.co.jp/merge/
