まだプログラマーですが何か?

プログラマーネタ中心。たまに作成したウェブサービス関連の話も https://twitter.com/dotnsf

タグ:bulk

Node.js + PostgreSQL 環境で複数レコードのバルクインサートを、それも対象テーブルに UNIQUE 制約が存在している場合のバルクインサート方法について調べる機会があったので、ブログにまとめておきます。


【Node.js で PostgreSQL を使う】
まず、Node.js で PostgreSQL クライアント機能を実装する場合、よく使われているのが pg(node-postgres) という npm のライブラリだと思っています:
2022080601


こんな感じでコネクションプーリングを作って、、
  :
  :
var PG = require( 'pg' );
var pg = new PG.pool( 'postgres://localhost:5432/mydb' );
  :
  :


こんな感じで SQL (以下の例は "INSERT" 処理)を実行し、結果を取得します:
  :
  :
if( pg ){
  var conn = await pg.connect();                                      //. コネクション割当
  if( conn ){
    try{
      var sql = "insert into table1( name, num ) values ( $1, $2 )";  //. SQL
      var query = { text: sql, values: [ "木村", 100 ] );             //. SQL パラメータ
      conn.query( query, function( err, result ){
        if( err ){
          console.log( err );                                         //. SQL 実行時エラー
        }else{
          console.log( result );                                      //. SQL 実行結果
        }
      });
    }catch( e ){
      console.log( e );                                               //. 例外発生
    }finally{
      if( conn ){
        conn.release();                                               //. コネクションのリリース
      }
    }
  }
}
  :
  :


今回もこの pg を使って紹介します。


【Node.js + PostgreSQL でバルクインサートを実装する】
上の例では一回の SQL 実行で1つのレコードをインサートする、という処理の例を紹介しました。複数のレコードをインサートする場合もこれを複数回続けて実行してもよいのですが、レコード数が多い場合に効率よくインサートできると便利です。このように「複数のレコードを1回の処理でまとめて作成する」ことを「バルクインサート」と呼びます。

Node.js + PostgreSQL でバルクインサートを実装する方法もいくつかありますが、今回は node-pg-format という npm ライブラリを使った例を紹介します:
2022080602


実際に使う場合はバルクインサートデータを配列で用意して、以下のように実装します:
  :
  :
var format = require( 'pg-format' );

var records = [         //. インサートするレコードを配列で用意
  [ "木村", 100 ],
  [ "鈴木", 101 ],
  [ "木村", 102 ]
];

if( pg ){
  var conn = await pg.connect();                                      //. コネクション割当
  if( conn ){
    try{
      var sql = format( "insert into table1( name, num ) values %L", records );  //. SQL
      var query = { text: sql, values: [] );                          //. SQL パラメータ
      conn.query( query, function( err, result ){
        if( err ){
          console.log( err );                                         //. SQL 実行時エラー
        }else{
          console.log( result );                                      //. SQL 実行結果
        }
      });
    }catch( e ){
      console.log( e );                                               //. 例外発生
    }finally{
      if( conn ){
        conn.release();                                               //. コネクションのリリース
      }
    }
  }
}
  :
  :

上例のように、SQL 文全体を pg-format でインスタンス化しています。その際にインサートする複数レコードを配列データとして付与しています(conn.query() 実行時のパラメータは空です)。これで records 配列変数で指定された複数レコードを1回の SQL 実行でまとめてインサートすることができるようになります。


【Node.js + PostgreSQL のバルクインサートと UNIQUE 制約】
ここからがこのブログエントリの本題に入っていきます。バルクインサートの方法は上で紹介しました。問題はこれではうまくいかないケースがあるのでした。具体的には対象テーブルに UNIQUE 制約が付与されていて、この制約にかかるレコードがバルクインサートの一部に含まれる場合です。

具体的に紹介します。例えば今回は table1 という名称のテーブルに複数レコードをバルクインサートする例を紹介していますが、この table1 テーブル作成時に以下のような指定がされていた可能性は排除できません:
create table table1( name varchar(50) default '', num int default 0 );
alter table table1 add unique( name, num );

1行目の "create table ..." はいいとして、問題は2行目です。この制約を与えたことによって name と num の組み合わせは一意(UNIQUE) でないといけない、という条件が付与されました。name は被ってもいいし、num も被ってかまわないのですが、name と num の組み合わせはユニークでないといけない(この条件に反するデータはインサートできない)ようになりました。

このような UNIQUE 制約が存在している前提で、改めて上例を見てみます。この例でバルクインサートしようとしているレコードは、
var records = [         //. インサートするレコードを配列で用意
  [ "木村", 100 ],
  [ "鈴木", 101 ],
  [ "木村", 102 ]
];

という内容でした(配列の1つ目が name 、2つ目が num としてバルクインサートされます)。ここだけを見るとこの例に限っては(name と num の組み合わせは全て異なるので)問題なくバルクインサートできそうです。 ・・・が、もしこのバルクインサート実行前に既に { name: "木村", num: 100 }
のデータが記録済みであった場合
はどうでしょう?バルクインサートの最初のレコードは UNIQUE 制約にかかってしまいます。つまり実行エラーとなります。

そして更にこの問題をややこしくしている事情があります。それは「バルクインサートが UNIQUE 制約エラーで失敗するのはいいが、その時 UNIQUE 制約にかからなかったレコードはインサートされるべきか?否か? という問題です。この答はおそらくデータやアプリケーション次第、つまりケース・バイ・ケースです。まとめてインサートされないと意味がないレコードであった場合はまとめてエラー(UNIQUE 制約にかからなかったレコードもインサートされない)という挙動が正しい挙動となり、UNIQUE 制約にかかったレコードはインサートされないが、他のレコードはインサートされるべきデータであった場合はバルクインサートの一部だけが失敗し、残りのレコードは挿入されているべき、ということになります。 

ちなみに上例で紹介したバルクインサート実装の場合は前者の挙動となります。つまり「全てのレコードが制約違反にならなかった場合のみバルクインサートが成功し、1つでも制約違反のレコードがあった場合はそうでないレコードも含めて1つもインサートされずにエラーとなる」という挙動です(そもそも厳密には「バルクインサート」とはそのような挙動および処理を意味する言葉です)。


【Node.js + PostgreSQL のバルクインサートと UNIQUE 制約】
長い前置きでした。ではここまでの背景を理解した上で UNIQUE 制約に違反しているレコードのみ挿入されず、違反していないレコードは正しく挿入されるようなバルクインサート(「バルクインサート」の定義としてはおかしいですけど・・)を実装するにはどのようにすればよいでしょうか? これが本ブログエントリの肝となる部分です。

これは実際には使っている RDB やライブラリ、プログラミング言語によって異なるようですが、PostgreSQL 9.5 以上で、Node.js + node-postgres を使っている場合であれば以下の方法で実現できるようです:
  :
  :

var records = [         //. インサートするレコードを配列で用意
  [ "木村", 100 ],
  [ "鈴木", 101 ],
  [ "木村", 102 ]
];

if( pg ){
  var conn = await pg.connect();                                      //. コネクション割当
  if( conn ){
    try{
      //. UNIQUE 制約に違反したデータを無視するような SQL を作る
      var sql = "insert into table1( name, num ) ";
      var selects = [];
      for( var i = 0; i < records.length; i ++ ){
        selects.push( "select '" + records[i][0] + "', " + records[i][1] );
      }
      sql += selects.join( " union all" ) + " on conflict ( name, num ) do nothing";

      var query = { text: sql, values: [] );                          //. SQL パラメータ
      conn.query( query, function( err, result ){
        if( err ){
          console.log( err );                                         //. SQL 実行時エラー
        }else{
          console.log( result );                                      //. SQL 実行結果
        }
      });
    }catch( e ){
      console.log( e );                                               //. 例外発生
    }finally{
      if( conn ){
        conn.release();                                               //. コネクションのリリース
      }
    }
  }
}
  :
  :


この例ではプログラミングによって、以下のような SQL 文を生成して実行しています:
insert into table1( name, num )
  select '木村', 100 union all
  select '鈴木', 101 union all
  select '木村', 102
  on conflict ( name, num ) do nothing

厳密にはバルクインサートではないのですが、1回の SQL 実行で複数のレコードをインサートし、"conflict"(UNIQUE 制約違反)が発生した場合は "do nothing"(無視)するよう指定しています。その結果、UNIQUE 制約違反のないレコードのみがまとめてインサートされる、という処理が実現できています。

バルクインサートで1度に挿入するレコード数が莫大になるようなケース(SQL 文自体が膨大で複雑になるケース)には向かないかもしれませんが、自分の手元では一応期待どおりに動くことを確認しました。ちょうどこういう処理を行いたいアプリを作っていて調べたのですが、他の人の参考になれば。


【参照】
https://stackoverflow.com/questions/53722405/how-to-insert-bulk-rows-and-ignore-duplicates-in-postgresql-9-3










NoSQL な DBaaS である IBM Cloudant を使うようになり、単に「管理/環境構築の手間がない」とか「クラスタリングされてる」という以上に便利な機能がいくつもあることが分かってきました。そのいくつかを実際のサンプルを使って動かす形で紹介しようと思います。今回はいわゆる「バルクインサート」の API で、複数のドキュメントを1回の API でまとめて作成する方法です。


以下の内容を読み進めるにあたり、「実際に自分でも試してみたい」という人は是非 IBM Cloudant のアカウントを取得して、サービスインスタンスを作って動かしてみてください。まだ環境をお持ちでない場合、IBM Bluemix のアカウントを登録して、"Cloudant NoSQL DB" を選択してサービスを作成してください。加えて API の実行には curl コマンドを使うので、curl の実行環境を用意してください(Windows であればこちら、他はおそらく標準のはず):
2017100401


サービス作成の際に利用プランを選択します。1GB かつ 30 日利用がないと削除されるという条件であれば無料の Lite プランを選択することも可能です。利用用途や目的に合わせて選択してください:
2017100402


IBM Cloudant のサービスインスタンスを作成したら(或いは既にお持ちであれば)、作成済みのサービスインスタンスの「サービス接続情報」から「資格情報の表示」を選択して、IBM Cloudant に接続するための情報を確認します。具体的にはここで表示される username と password の値を後で利用することになるので、メモしておいてください:
2017100403


また(今回はあまり使いませんが)IBM Cloudant はウェブのダッシュボード機能があり、データを確認したり、一部の操作をこのダッシュボードから行ったりすることができます。ダッシュボードにアクセスするにはサービスインスタンスの「管理」から「LAUNCH」ボタンをクリックすることで移行できます(或いは上記の「資格情報の表示」の中に表示されている "url" の値をウェブブラウザで指定して開きます。この場合はユーザー名とパスワードを聞かれるので、上記でメモした username と password を指定して開きます):
2017100404


IBM Cloudant のダッシュボード画面です。左ペインの上から2つ目がデータベース一覧タブになっており、ここから現在作成済みのデータベースを一覧できます(下図ではまだ1つもありません)。この後の作業用に1つデータベースを作っておきます。画面右上の「Create Database」をクリックします:
2017100405


適当な名前(以下例では "mydb")を入力して「Create」ボタンをクリックします:
2017100406


指定した名前(mydb)のデータベースができました。この時点ではまだ1つもドキュメント(データ)が入っていないので空の状態です。再度画面左のデータベースアイコンをクリックして、データベース一覧に戻ります:
2017100407


先程の画面に戻りました。mydb というデータベースが追加されて、現在のサイズやデータ数(# of Docs)などが確認できる状態になっています:
2017100408


ではこのデータベースに対して、バルクインサート API を実行してみます。まずはインサートするドキュメントデータのサンプル(prefs.json)をここからダウンロードします:
https://raw.githubusercontent.com/dotnsf/samples/master/prefs.json


prefs.json の内容は以下のようになっています。"docs" 内に日本の47都道府県のコード(code)と名前(prefecture)、都道府県庁所在地名(capital)、そしてその緯度(lat)経度(lng)が JSON オブジェクトで定義されており、その配列を docs としています:
{
  "docs": [
    { "code": 1, "prefecture": "北海道", "capital": "札幌市", "lat": 43.06417, "lng": 141.34694 },
    { "code": 2, "prefecture": "青森県", "capital": "青森市", "lat": 40.82444, "lng": 140.74 },
    { "code": 3, "prefecture": "岩手県", "capital": "盛岡市", "lat": 39.70361, "lng": 141.1525 },
       :
    { "code": 46, "prefecture": "鹿児島県", "capital": "鹿児島市", "lat": 31.56028, "lng": 130.55806 },
    { "code": 47, "prefecture": "沖縄県", "capital": "那覇市", "lat": 26.2125, "lng": 127.68111 }
  ]
}

これが Cloudant のバルクインサート API で使う場合のデータフォーマットになります。挿入したい複数のドキュメントデータを { "docs": [ .. ] } という形式で、"docs" の配列として定義します。

ではこのドキュメントデータ(prefs.json)を上記で作成した mydb データベースにまとめて格納します。curl を使って以下のように実行します:
$ curl -u "username:password" -XPOST "https://username.cloudant.com/mydb/_bulk_docs" -H "Content-Type: application/json" -d @prefs.json

※↑青字usernamepassword の部分には IBM Cloudant の資格情報で確認した値をそのまま指定します。またデータファイル prefs.json はコマンド実行時のカレントディレクトリに存在しているものとします。


コマンド実行後にダッシュボード画面を(リロードして)確認すると、mydb データベースに 47 件の(47都道府県の)ドキュメントデータが格納されていることが分かります。詳しく見るために mydb を選択します:
2017100401


先程は空だった mydb に 47 件のドキュメントデータが格納されていることが確認できます。個々のドキュメントデータの中身はこの(デフォルトの "Metadata" の)画面だとわかりにくいので、表示形式を "Table" か "JSON" に切り替えてみます(ここでは JSON を選択します):
2017100402


ドキュメントデータが JSON フォーマットで、実際に格納した中身(doc)まで含めて表示されます。画面では北海道の1ドキュメントデータしか表示されていませんが、下にスクロールすると他のドキュメントデータも確認することができます:
2017100403


なお、データベースのドキュメントデータ一覧は curl で以下のコマンドを実行した結果でも確認することができます:
$ curl -u "username:password" -XGET "https://username.cloudant.com/mydb/_all_docs?include_docs=true"

include_docs=true のパラメータを付けて実行すると doc の中身まで、付けずに実行すると id と key の一覧のみ取得します。


今回は1回の API 実行で複数のドキュメントデータをまとめて Cloudant に格納するバルクインサート API を紹介しました。バルクインサート自体は必ずしも Cloudant の特徴というわけではなく、他のデータベースシステムにも存在している機能だと思いますが、今後 Cloudant の特徴でもある Design Document API を紹介していくつもりで、その時には今回作成した都道府県データを使ったサンプルを紹介する予定です。


なお、IBM Cloudant の REST API リフェレンスはこちらを参照ください:
https://console.bluemix.net/docs/services/Cloudant/api/http.html
 

このページのトップヘ