はじめに
Verticaではアーキテクチャの関係上、大量データのロード処理はINSERTではなくCOPYコマンドを利用することが推奨されていますが、日々のバッチ処理等において、既存データへの更新処理が発生する場合の手法について、ご相談されることがよくあります。
本記事では、データの更新が伴う、データロード処理の手法について整理していますので、データ連携の手法にお悩みの方はご参考にいただければと思います。
データ更新が伴うデータロード処理について
データ更新が伴うデータロード処理は、テーブルデータ量や更新量、バッチ処理に許容される時間を加味して方法を検討する必要があります。データ更新が伴うデータロード処理は主に以下の方法から検討します。
TRUNCATE + COPY
毎回、テーブルデータをTRUNCATEし、COPYコマンドにて全件のデータ連携を実施します。いわゆる洗い替えです。
実行例
更新対象のテーブル：lineorder
1.TRUNCATE文によるデータ削除
dbadmin=> TRUNCATE TABLE lineorder;2.COPY文によるデータロード
dbadmin=> COPY lineorder FROM '/data/csv/lineorder_update.csv';※なお、Vertica9.3以降より、TRUNCATE実行後に統計情報が”NONE”(統計情報が取得されていない状態)となるように仕様変更されております。
そのため、TRUNCATE実行後には明示的に統計情報を取得する必要がありますので、ご注意ください。
手動で統計情報を取得する方法
https://www.ashisuto.co.jp/cm/analytics-database/collect_analyze.html
DELETE + COPY
CSVデータを一度ワークテーブルにロードし、DELETE ＋ COPYにてデータ連携を実施します。
実行例
更新対象のテーブル：lineorder
ワークテーブル：lineorder_temp
プライマリキー：LO_CUSTKEY
1.COPYコマンドによるワークテーブルへのデータロード
dbadmin=> COPY lineorder_temp FROM '/data/csv/lineorder_update.csv';2.更新対象のデータをDELETE
dbadmin=> DELETE FROM linerorder WHERE LO_CUSTKEY in(SELECT LO_CUSTKEY FROM lineorder_temp);3.本テーブルへのデータロード
dbadmin=> COPY lineorder FROM '/data/csv/lineorder_update.csv';VerticaではDELETEを利用する場合、いくつか注意点があります。
以下の記事をご参照ください。
DELETEの注意点(１)
https://www.ashisuto.co.jp/cm/analytics-database/delete-notice-1.html
DELETEの注意点(２)
https://www.ashisuto.co.jp/cm/analytics-database/delete-notice-2.html
DELETEの注意点(３)
https://www.ashisuto.co.jp/cm/analytics-database/delete-notice-3.html
MERGE
CSVデータを一度ワークテーブルにロードし、MERGE文にてデータ連携を実施します。
実行例
更新対象のテーブル：lineorder
ワークテーブル：lineorder_temp
プライマリキー：LO_CUSTKEY
1.COPYコマンドによるワークテーブルへのデータロード
dbadmin=> COPY lineorder_temp FROM '/data/csv/lineorder_update.csv';2.MERGE文にて本テーブルへのデータロード
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と比較すると、処理がシンプルになるため、開発工数を削減できる | ・データ走査が全件に実行されるため、データ件数が増えると処理に時間がかかる可能性がある |
参考情報
データロードの基本
https://www.ashisuto.co.jp/cm/analytics-database/dataload_overview.html
MERGE文の使用方法
https://www.ashisuto.co.jp/cm/analytics-database/merge.html
検証バージョンについて
この記事の内容はVertica 11.0で確認しています。
更新履歴
2021/12/27 本記事を公開