Db2 on Cloudにデータをコマンドでロードする3つの方法

Db2 on Cloudでデータをロードするには、オーソドックスなCLI(db2コマンド)でロードする方法、Web管理コンソールのGUIを使用してLOADする方法,　REST APIを使ってロードする方法などがあります。

当記事ではCLI(db2コマンド)でロードする方法を3つご紹介します。

コマンドにはたくさんオプションがありますので、コマンドの説明へのリンクと実際の実行コマンドを記載しています。オプションはご自分の環境に合わせて確認・調整・追加お願いします。

0. 前準備・環境など

0-1. ここで確認している環境と準備は以下です:

• Db2 on Cloud

  • ロード先テーブルとロードするCSVファイルを準備しておきます
    • ここでは@teruqさんの以下の投稿で使用しているテーブルとCSVファイルを使わせていただきました。こちらの記事はINSERTに言及してますが、時間なども出してあったので比較できるかと思い、同じにさせていただきました。
      • Db2 on CloudのINSERT性能を向上させる
      • CSVファイルはヘッダーなし、10万行、約5.1M

• IBM Cloud VPC上のVirtual Server Instance(VSI), Red Hat Enterprise Linux 8

  • ここにIBM Data Server Runtime Client v11.5.7を導入
  • Db2 on Cloudのdbをカタログしておく
    • カタログの方法は　こちらの「4. nodeとdbを登録」と同じです。

• IBM Cloud Object Storage

  • S3互換なのでAWSのS3でも同じことができます
  • バケットを作成し、サービス資格情報作成しておきます
    • 以下の@kawada2017さんの投稿を参考にしてみてください
      • Db2 Warehouse on Cloudで、ICOS上のCSVから外部表を作成する
  • バケットにLOADするCSVファイルをアップロードしておきます

0-2. 前提: Db2への接続

3つの方法「1. IMPORTコマンドでローカルのCSVファイルを使用したロード」「2.LOADコマンドでローカルのCSVファイルを使用したロード」「3.外部表を利用したロード」はコマンドでDbに接続済であることが前提です。

必要な情報:

• <db_name>: カタログ時に定義したDb名
• <user_name>: 接続user name
• <password>: パスワード

以下のコマンドで接続をしておいてください(<>の記載不要):

db2 "connect to <db_name> user <user_name> using <password>"

実行例:

db2 "connect to bludb user aaa12345bbb using xxxxxxxxx"

1. IMPORTコマンドでローカルのCSVファイルを使用したロード

まずは普通にIMPORTコマンドでローカルのCSVファイルを使用したロードする方法です。

IMPORTコマンド

実行例:
テーブルに自動ID列c1があるので、カラム名指定しています。全カラムがCSVにあれば指定不要です。

db2 "import from /tmp/100000.csv of del commitcount 10000 insert into nishito.perftest (c2,c3,c4,c5,c6,c7,c8,c9,c10)"

参考所要時間:

HADRあり	HADRなし
5m57.038s	6m41.124s

2. LOADコマンドでローカルのCSVファイルを使用したロード

次にLOADコマンドでローカルのCSVファイルを使用したロードする方法です。

LOADコマンド

実行例:
ローカルファイルの読み込みなので、LOADの次にclientというキーワードを入れます。
テーブルに自動ID列c1があるので、カラム名指定しています。全カラムがCSVにあれば指定不要です。

db2 "load client from /tmp/100000.csv of del modified by codepage=1208 insert into nishito.perftest (c2,c3,c4,c5,c6,c7,c8,c9,c10)"

参考所要時間:

HADRあり	HADRなし
0m5.848s	0m5.843s

ものすごく速くなりました!

ものすごく速いのですが注意事項があります:

• Db2 on CloudへのLOADコマンドは強制的にCOPY YESオプションが付き、HADRの場合でもスタンバイDBへの伝播がされます。ただし一般的なHADR構成で考えると、スタンバイDBからコピー・イメージにアクセスできなかった場合、LOADの対象表が格納されている表スペース全体が、スタンバイDB上でリストア・ペンディング状態となりアクセス不能となってしまいます。この場合の回復方法はプライマリーDBからのバックアップリストアのみとのことで、余りないかもしれませんが、万が一LOAD失敗の場合はサポートCaseをOpenして回復依頼となりそうです。

• ロードできるファイルサイズに5GBの制限があるようです（未確認)。

• HADRの場合、スタンバイDBでのLOAD操作はシリアルに実行され、後続のトランザクションの更新のREPLAYも並行して実行されないため、ログギャップの原因になりますので、実行時はトランザクションの少ない時間に実行するなど考慮が必要です。

3. 外部表を利用したロード

ICOSなのどのオブジェクトストレージにCSVファイルをおいて、外部表を定義し、そこからロードする方法があります。

オブジェクト・ストアからのデータのロード

3-方法1 一時的な外部表を作成してロードする

一時外部表を使用したデータのロード

ここではCSVファイルを格納したのオブジェクトストレージの情報が必要です:

• <endpoint>: S3 互換 Web サービスの URL
  • ICOSの場合はこちらに記載
• <authKey1>: ICOSの場合、HMAC資格情報のaccess_key_id
• <authKey2>: ICOSの場合、HMAC資格情報のsecret_access_key
• <bucket>: ファイルを格納したbucket名

さらに一時的な外部表のデータ定義も必要です:

(c2 DECIMAL(10,0),c3 DECIMAL(10,0),c4 DECIMAL(10,0),c5 DECIMAL(10,0),c6 DECIMAL(10,0),c7 DECIMAL(10,0), c8 DECIMAL(10,0),c9 DECIMAL(10,0),c10 DECIMAL(10,0)) 

例

db2 "INSERT INTO nishito.perftest (c2,c3,c4,c5,c6,c7,c8,c9,c10) \
select * from external '100000.csv' (c2 DECIMAL(10,0),c3 DECIMAL(10,0),c4 DECIMAL(10,0),c5 DECIMAL(10,0),c6 DECIMAL(10,0),c7 DECIMAL(10,0), c8 DECIMAL(10,0),c9 DECIMAL(10,0),c10 DECIMAL(10,0)) \
using (DELIMITER ',' s3('<endopoint>', \
'<authKey1>', \
'<authKey2>', \
'<bucket>') )"

参考所要時間:

HADRあり	HADRなし
1m34.017s	1m11.190s

ローカルからIMPORTよりは速いですね。

尚このテーブルはDb2 on Cloud標準の「行表(行オーガナイズ表, ORGANIZE BY ROW)」ですが、このロードの方法はDb2 Warehouse on Cloudなどで標準で使われる「列表(カラム・オーガナイズ表, ORGANIZE BY COLUMN)」に対して行うと「ものすごく」速くなります。
試しに同じテーブル構成で「列表(カラム・オーガナイズ表)」として作成し場合の参考所要時間は以下になりました。

参考所要時間(列表(カラム・オーガナイズ表)):

列表HADRあり	列表HADRなし
0m4.173s	0m2.463s

3-方法2 外部表を作成後ロードする

「一時的な外部表を作成してロードする」は外部表作成とロードを連続して実行して、外部表は消してしまいますが、先にまとめて外部表を作成して、その後まとめてロードしたい、なんて状況もあるかもしれません。
その方法は以下になります。

1. 外部表の作成

CREATE EXTERNAL TABLE ステートメント

例

db2 "CREATE EXTERNAL TABLE \
nishito.perftest_ext(c2 DECIMAL(10,0),c3 DECIMAL(10,0),c4 DECIMAL(10,0),c5 DECIMAL(10,0),c6 DECIMAL(10,0),c7 DECIMAL(10,0), c8 DECIMAL(10,0),c9 DECIMAL(10,0),c10 DECIMAL(10,0)) \
using (dataobject '100000.csv' \
s3('<endopoint>', \
'<authKey1>', \
'<authKey1>', \
'<bucket>') \
DELIMITER ',')"

2. 外部表からのロード
名前付き外部表を使用したデータのロード

例

db2 "INSERT INTO nishito.perftest (c2,c3,c4,c5,c6,c7,c8,c9,c10) select * from nishito.perftest_ext"

参考所要時間(INSERTコマンドのみ):

HADRあり	HADRなし
1m17.831s	1m12.450s

4. まとめ

3つ方法を書きましたが、一番速いのは「LOADコマンドでローカルのCSVファイルを使用したロード」でした。ただファイルサイズ、HADRだと考慮点もありますので、そこを検討の上ご自身のデータでもお試しください。またデータサイズなどによっても変わる可能性があります。
また列表(カラム・オーガナイズ表)の場合は他の方法はテストはしていませんが、「一時的な外部表を作成してロード」が一番速い方法となるはずです。

方法	HADRあり	HADRなし
IMPORTコマンドでローカルのCSVファイルを使用したロード	5m57.038s	6m41.124s
LOADコマンドでローカルのCSVファイルを使用したロード	0m5.848s	0m5.843s
一時的な外部表を作成してロード	1m34.017s	1m11.190s
参考:一時的な外部表を作成してロード(列表)	0m4.173s	0m2.463s