CData Software Blog

クラウド連携のCData Software の技術ブログです。

CData Google BigQuery DriverでBulk Updateを実行する方法

CData Google BigQuery DriverはUpdateをバッチで実行するBulk Update機能をサポートしています。 Bulk Updateは逐次のUpdate処理と比較して複数レコード更新のパフォーマンスが向上します。 本記事ではCData Google BigQuery DriverでBulk Updateを実行する方法を説明します。

1. Bulk Updateに必要な設定

Bulk Updateにはテーブルに対する主キーの設定と、バルクモードの設定が必要です。

1.1 主キーの設定

Bulk Updateの対象テーブルには主キーが必要ですが、Google BigQueryのテーブルには主キーの設定はありません。 そこで、ドライバの接続プロパティPrimaryKeyIdentifiersで各テーブルの主キーとして動作するカラムを指定します。

主キー設定のフォーマットは以下の通りです。

<テーブル名>=<主キーのカラム名(複数設定可)>

設定例を以下に示します。

  • Table1=Key : Table1テーブルのKeyカラムを主キーとする
  • Table1=Key1,Key2 : Table1テーブルのKey1カラムとKey2カラムを複合主キーとする
  • *=Key : 全テーブルのKeyカラムを主キーとする

複数のテーブルに対して指定する場合、以下のように各テーブルの主キー設定の文字列をセミコロン(;)で区切ってください。

Table1=Key;Table2=Key1,Key2;Table3=Code

1.2 Insert Modeの設定

CData Google BigQuery Driverはバルクデータを扱うインタフェース(バルクモード)を選択できます。 選択できるバルクモードについてはこちらを参考にしてください。

Bulk Updateが可能なバルクモードはUploadGCSStagingです。 バルクモードはInsert Modeプロパティで設定します。 Bulk Updateを行う際にはこれをUploadまたはGCSStagingに設定してください。

2. Bulk UpdateのSQL構文

Bulk Updateは一時テーブルを利用し、複数のUpdateクエリをまとめて発行します。 一時テーブルに対してInsert文でキーと更新する値を挿入し、最後にUpdate文で一時テーブルの値を入力します。

ここでは、以下の3つのUpdate文を実行する例を示します。

逐次実行 (Update)

UPDATE Items SET name = 'item1', qty = 10 WHERE id = 1;
UPDATE Items SET name = 'item2', qty = 15 WHERE id = 2;
UPDATE Items SET name = 'item3', qty = 18 WHERE id = 3;

これをBulk Updateで実行すると以下のようなSQL文になります。

一括実行 (Bulk Update)

--- 一時テーブルへの更新値の設定
INSERT INTO Items#TEMP (id, name, qty) VALUES(1,'item1',10);
INSERT INTO Items#TEMP (id, name, qty) VALUES(2,'item2',15);
INSERT INTO Items#TEMP (id, name, qty) VALUES(3,'item3',8);

--- 一時テーブルの値を展開してBulk Updateを実行
UPDATE Items(id, name, qty) SELECT id, name ,qty FROM Items#TEMP;

一時テーブルの名前は更新対象のテーブル名に#TEMPを付与した名前を設定してください。 また、一時テーブルはセッションがクローズするまでデータがメモリに保持されます。 同一のセッションで同じ名前の一時テーブルを利用する場合は以下のように内容を削除してからInsertを行ってください。

DELETE FROM Items#TEMP;

Bulk Update構文の詳細については以下ヘルプを参考にしてください。

https://cdn.cdata.com/help/DBG/jp/jdbc/pg_updateselect.htm

3. Bulk Update実行例

それでは実際のデータでBulk Updateを試してみます。

テストデータとして以下のProductsテーブルを用意しました。 このテーブルは製品のId、コード、名前を保持しています。

Productsテーブル

id code name
1 P001 Product 001
2 P002 Product 002
3 P003 Product 003

今回はこのテーブルに対して、製品コードをキーとして製品名の値を変更します。 具体的には、コード「P002」の製品名を「Product A」、「P003」の製品名を「Product B」に変更します。 これを逐次実行すると以下のようなSQLになります。

UPDATE Products SET name = 'Product A' WHERE code = 'P002';
UPDATE Products SET name = 'Product B' WHERE code = 'P003';

それではこれをBulk Updateで実行してみます。

接続プロパティの設定

はじめに接続プロパティを設定します。 InsertModeはデフォルトでStreamingのため、これをUpload(GCSStagingでも可)に設定します。 また、接続プロパティのPrimaryKeyIdentifiersでcodeを主キーに設定します。

以下のプロパティ設定を接続文字列に追加します。

  • InsertMode=Upload
  • PrimaryKeyIdentifiers="Products=code"

SQLの実行

上記設定で接続し、Bulk Upsertのクエリを実行します。

INSERT INTO Products#TEMP(code, name) VALUES('P002','Product A');
INSERT INTO Products#TEMP(code, name) VALUES('P003','Product B');
UPDATE Products(code, name) SELECT code, name FROM Products#TEMP;

上記SQLを実行すると、以下のようにデータが更新されているのが確認できます。

id code name
1 P001 Product 001
2 P002 Product A
3 P003 Product B