DB2DBA: INGESTでCSVからのUPSERT処理を高速化

IMPORTの代わりにINGESTを使うと、CSVファイルからのUPSERT処理を高速化できます（当社比10倍）。

IMPORTのINSERT_UPDATEモードはなぜ遅いのか

IMPORTのINSERT_UPDATEモードはUPSERT処理を内部的に以下のようにして実現しています。

IMPORTコマンド	DB2
INSERT指示
	INSERT実行
	キー重複エラー返却
UPDATE指示
	UPDATE実行
	正常終了返却

一度INSERTを試してみてキー重複で失敗した場合はUPDATEを行うという手順を1レコードずつ行うため、効率がよくありません。

また、INSERTモードではCOMPOUNDモディファイアによって複数レコードをまとめて処理し性能を上げることができますが、INSERT_UPDATEモードではCOMPOUNDを指定することが出来ません。

INGESTとは

DB2 10.1より提供されている新しいデータ移動ユーティリティです。INGEST/IMPORT/LOADにはそれぞれ様々な長所と短所がありますが、INGESTではINSERTだけでなくUPDATE/DELETE/MERGEや関数の利用といったDMLで行う処理が一通り出来るようになっています。IMPORTの代わりにINGESTのMERGEを利用すれば、UPSERT処理は以下のように効率的になります。

INGESTコマンド	DB2
MERGE指示
	MERGE実行
	正常終了返却

INGESTによるMERGEの例を以下に示します。CSVファイル各列の名前とデータ型を指定し、それらをMERGE文の条件の判定やUPDATE/INSERT操作に利用することができます。

INGEST FROM FILE ファイル名 FORMAT DELIMITED
(
        $id INT EXTERNAL
        ,$data CHAR(8)
)
MERGE INTO テーブル名
        ON id = $id
WHEN MATCHED THEN
        UPDATE SET data = $data
WHEN NOT MATCHED THEN
        INSERT VALUES($id, $data)

なお、DB2 10.1～DB2 10.5ではDB2の上位エディション (AWSE / AESE) でしか利用できませんでしたが、DB2 11.1ではExpress-C含む全エディションで使えるようになりました。

検証してみた

環境

• Db2 11.1 Express-C Edition
• VirtualBox 5.2.3
• Vagrant 2.0.3
• Ubuntu Linux 16.04.4 64-bit (ubuntu/xenial64)

テストケース

100万件のレコード全てに対してUPDATEが発生するケースで、IMPORT INSERT_UPDATEとINGEST MERGEそれぞれの処理時間を比較します。条件を同一にするため、IMPORTとINGESTのコミット間隔を共に10000件にしておきます。

事前準備

再開ログ表の作成

SYSINSTALLOBJECTSプロシージャを使用してINGESTの実行に必要な再開ログ表を作成します。再開ログ表によって、INGESTは中断した処理を再開することができます。

db2 => call sysproc.sysinstallobjects('ingest', 'c', null, null)

  Return Status = 0
db2 => select substr(tabname, 1, 32) from syscat.tables where tabname = 'INGESTRESTART'

1
--------------------------------
INGESTRESTART

  1 record(s) selected.

テスト表の作成

db2inst1@ubuntu-xenial:/vagrant$ cat test02a.sql
CREATE TABLE test02
(
        id INT NOT NULL PRIMARY KEY
        ,data INT WITH DEFAULT 100
)
;
db2inst1@ubuntu-xenial:/vagrant$ db2 -tf test02a.sql
DB20000I  The SQL command completed successfully.

データの事前LOAD

共通表式とカーソルからのLOAD機能を活用して100万件のデータを生成しました。

db2inst1@ubuntu-xenial:/vagrant$ cat test02b.sql
DECLARE csr CURSOR FOR
        WITH tmp
        (
                id
        )
        AS
        (
                SELECT 1 FROM SYSIBM.SYSDUMMY1
                UNION ALL
                SELECT id + 1 FROM tmp WHERE id < 1000000
        )
        SELECT id FROM tmp
;
LOAD FROM csr OF CURSOR REPLACE INTO test02 NONRECOVERABLE
;
db2inst1@ubuntu-xenial:/vagrant$ db2 -tf test02b.sql

-- 省略 --

Number of rows read         = 1000000
Number of rows skipped      = 0
Number of rows loaded       = 1000000
Number of rows rejected     = 0
Number of rows deleted      = 0
Number of rows committed    = 1000000

CSVの作成

表のデータをそのままEXPORTしてIMPORTとINGESTで利用するCSVを作成します。キーさえ一致していればUPDATEは行われるため、DATA列はそのまま。

db2inst1@ubuntu-xenial:/vagrant$ cat test02c.sql
EXPORT TO test02.csv OF DEL
        SELECT * FROM test02
;
db2inst1@ubuntu-xenial:/vagrant$ db2 -tf test02c.sql
SQL3104N  The Export utility is beginning to export data to file "test02.csv".

SQL3105N  The Export utility has finished exporting "1000000" rows.


Number of rows exported: 1000000

計測

IMPORT INSERT_UPDATE

db2inst1@ubuntu-xenial:/vagrant$ cat test02d.sql
IMPORT FROM test02.csv OF DEL
        COMMITCOUNT 10000
        INSERT_UPDATE INTO test02
;
db2inst1@ubuntu-xenial:/vagrant$ time db2 -tf test02d.sql
SQL3109N  The utility is beginning to load data from file "test02.csv".

-- 省略 --

Number of rows read         = 1000000
Number of rows skipped      = 0
Number of rows inserted     = 0
Number of rows updated      = 1000000
Number of rows rejected     = 0
Number of rows committed    = 1000000



real    13m42.588s
user    0m0.112s
sys     0m0.080s

INGEST MERGE

db2inst1@ubuntu-xenial:/vagrant$ cat test02e.sql
INGEST SET COMMIT_PERIOD 0
;
INGEST SET COMMIT_COUNT 10000
;
INGEST FROM FILE test02.csv FORMAT DELIMITED
(
        $id INT EXTERNAL
        ,$data INT EXTERNAL
)
MERGE INTO test02
        ON id = $id
WHEN MATCHED THEN
        UPDATE SET data = $data
WHEN NOT MATCHED THEN
        INSERT VALUES($id, $data)
;
db2inst1@ubuntu-xenial:/vagrant$ time db2 -tf test02e.sql
DB20000I  The INGEST SET command completed successfully.

SQL2979I  The ingest utility is starting at "04/25/2018 14:56:10.173827".
SQL2914I  The ingest utility has started the following ingest job:
"DB21101:20180425.145610.173827:00002:00005".

Number of rows read         = 1000000
Number of rows merged       = 1000000
Number of rows rejected     = 0

SQL2980I  The ingest utility completed successfully at timestamp "04/25/2018
14:57:12.503772"


real    1m2.415s
user    0m0.024s
sys     0m0.044s

全レコードUPDATEという極端なケースでしたが、10倍以上の差でINGESTの方が高速でした。今回はDB2と同じマシン上で両者を実行しましたが、ネットワーク越しの実行であればより大きな差が生じる可能性もありそうです。

参考資料

• IBM Knowledge Center
  • Db2 for Linux UNIX and Windows 11.1.0
    • INGEST ユーティリティー
    • INGEST、インポート、およびロードの各ユーティリティーの比較
• developerWorks
  • DB2 Ingestユーティリティ利用ガイド
• Qiita
  • 【DB2】 SQL一発で100万件のテストデータを生成＆投入する
  • DB2DBA: IMPORTのパフォーマンスをお手軽に改善する