SQL Serverに対して大量データを一括でインポートできるBCPコマンドについて言及します。性能試験環境を用意する方などは是非見てみてください！ また、エクスポートも可能ですが、本記事ではインポートについてのみとします。
インストール
BCPコマンドに限らずsqlcmdなどSQL Server関連ツールがインストールされます。
Windows環境
インストーラーをダウンロードし実行します。
Mac環境
公式ページに載っている以下のコマンドを実行します。
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
brew install mssql-tools使い方
前提・仮定
解説におけるテーブルは、以下を例とします。
-- ---------------------
-- 取引
-- ---------------------
CREATE TABLE blog.sales (
sales_id INT NOT NULL
, amount INT NOT NULL
, transaction_date DATE NOT NULL
, status VARCHAR(20) NOT NULL
, cancel_flg BIT
, shop_id INT NOT NULL
, user_id INT NOT NULL
, PRIMARY KEY(sales_id)
, FOREIGN KEY(user_id) REFERENCES blog.my_user(user_id) -- ユーザテーブルがある前提
, FOREIGN KEY(shop_id) REFERENCES blog.shop(shop_id) -- 店舗テーブルがある前提
)sales_idが1カラム目〜 user_idが7カラム目に定義されています。
基本操作
DBサーバの接続情報、対象テーブル、データファイルを指定し実行します。
bcp [スキーマ名].[テーブル名] in [データファイル] -S [DBサーバ名] -d [DB名] -U [ユーザ名] -P [パスワード]以下解説においてもDBサーバの接続情報については伏せた状態とします。
例の取引テーブルへのデータファイルは以下の様になります。デリミタは標準だとタブとなり、それぞれの値がDBのカラム定義の順番とリンクしてデータインポートされます。
50000001 8752 2022-06-08 success 0 9774 1425
50000002 5246 2022-01-03 success 0 6885 15022
50000003 3066 2022-11-06 success 0 6914 14836
50000004 3029 2022-08-14 success 0 2119 608
50000005 8156 2022-04-04 success 0 9430 16418
50000006 1003 2022-05-22 success 0 7512 13800
50000007 5420 2022-05-21 success 0 1034 12179
50000008 2814 2022-08-13 success 0 3851 27799
50000009 7812 2022-04-26 success 0 4517 185
50000010 3163 2022-11-24 success 0 6424 23914BCPコマンドは以下のようになります。オプションについては後述します。
bcp blog.sales in sales.tsv -S [DBサーバ名] -d [DB名] -U [ユーザ名] -P [パスワード] -q -E -k -e err.logフォーマット指定する場合
基本操作の場合だと、全てのカラムにデータを入れる場合の用途になりますが、特定のカラムのデータのみ入れたい・入れたくない場合や、テーブルでのカラム定義の順番と異なる順番でのデータファイルを作成したい場合などには、フォーマットファイルを指定することが有効です。
例えば、5カラム目のcancel_flgはデータを必ず入れない場合を想定してみます。データファイルはcancel_flgのデータ部分がなくなり、6カラム分のデータのみとなります。
50000001 8752 2022-06-08 success 9774 1425
50000002 5246 2022-01-03 success 6885 15022
50000003 3066 2022-11-06 success 6914 14836
50000004 3029 2022-08-14 success 2119 608
50000005 8156 2022-04-04 success 9430 16418
50000006 1003 2022-05-22 success 7512 13800
50000007 5420 2022-05-21 success 1034 12179
50000008 2814 2022-08-13 success 3851 27799
50000009 7812 2022-04-26 success 4517 185
50000010 3163 2022-11-24 success 6424 23914フォーマットファイルは以下のように定義します。
14.0
6
1 SQLCHAR 0 12 "\t" 1 sales_id ""
2 SQLCHAR 0 12 "\t" 2 amount ""
3 SQLCHAR 0 11 "\t" 3 transaction_date ""
4 SQLCHAR 0 20 "\t" 4 status SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 12 "\t" 6 shop_id ""
6 SQLCHAR 0 12 "\n" 7 user_id ""
左上2行目の「6」は、データをインポートするカラム数を指定します。全7カラムある内の1カラムだけ入れないため、6となります。また、その下に並ぶ1列目の1〜6は、データファイルのカラム順番を示します。右にある6,7列目はテーブルにおけるカラム順番とカラム名になります。今回は、5カラム目のcancel_flgにはデータを入れないので、データファイルの5列目に定義したデータをテーブル6カラム目のshop_idとしてインポートするという意味になります。
フォーマットファイルは形式として以下を気をつける必要があります。
- 最終行（最後のカラム定義）のデリミタは改行コード
- 最終行の後に改行
フォーマットの作成方法
先に説明したようなフォーマットファイルを手動で作成するのは大変ですが、以下の構文で実際のテーブルからファーマットファイルを出力することが可能です。
bcp [スキーマ名].[テーブル名] format -f [フォーマットファイル] -S [DBサーバ名] -d [DB名] -U [ユーザ名] -P [パスワード]基本的にはXML形式になりますが、XML以外のフォーマットを指定する場合は テーブルの後に「nul」とつけます。また、-cオプションでフィールドごとの型定義に関するプロンプト表示を回避してくれます。私はXMLではないフォーマットで作成しています。
bcp blog.sales format nul -c -f sales.fmt -S [DBサーバ名] -d [DB名] -U [ユーザ名] -P [パスワード]全カラムについて出力されるので、必要なカラムに合わせてフォーマットファイルを修正する必要があります。
14.0
7
1 SQLCHAR 0 12 "\t" 1 sales_id ""
2 SQLCHAR 0 12 "\t" 2 amount ""
3 SQLCHAR 0 11 "\t" 3 transaction_date ""
4 SQLCHAR 0 20 "\t" 4 status SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 1 "\t" 5 cancel_flg ""
6 SQLCHAR 0 12 "\t" 6 shop_id ""
7 SQLCHAR 0 12 "\n" 7 user_id ""よく使うオプション
| オプション | 内容 |
|---|---|
| -E | IDカラムを明示的な値としたい場合に使用します。オートインクリメントがセットされているテーブルで本オプションを指定しなかった場合は、ファイルのデータではなくDBが割り当てる値となります。 |
| -q | 空白や単一引用符をDB名に含む場合に使用します。 |
| -b | 指定した単位ごとにトランザクションを作成しコミットします。超大量データを処理する場合は細かくコミットしていくと、途中でエラーが起きた場合にも再実行時の負荷が下がります。 |
| -f | フォーマットファイルを指定する場合に使用します。 |
| -t | デリミタを指定する場合に使用します。既定値はタブ（\t）になります。 |
| -k | NULL値を扱いたい場合に使用します。データファイルには空文字としておくとNULL値になります。 |
| -e | エラー時のログファイルを指定する場合に使用します。 |
| -F | ファイルの開始位置を指定します。規定値は1のため、ヘッダーを飛ばして読み込みたい場合は2を指定します。 |
| -L | ファイルの終了位置を指定します。0より大きく、ファイル行数以下の番号を指定する必要があります 。1000行中 先頭 100行を処理する場合は、-L 100 とします。 |
| -c | フォーマットファイル作成時に、フィールドごとに型定義のプロンプトが表示されるのを回避するために使用します。 |
よく出るエラーメッセージ
| SQLState
| NativeError | エラーメッセージ（可変部含む） | 原因 |
|---|---|---|
| 22005 | ||
| 0 | Invalid character value for cast specification | ・テーブルの型に合致したデータでない。日付なのに、2022-0801であるなど（2022-08-01が正しい）。 |
| ・データファイルのカラム数がフォーマットファイルのカラム数よりも多い。 | ||
| ・フォーマットの最後のカラム定義のデリミタが改行コードになっていない。 | ||
| 23000 | ||
| 2627 | Violation of PRIMARY KEY constraint ‘PK__shop__AD08178667B614A6’. | |
| Cannot insert duplicate key in object ‘blog.shop’. The duplicate key value is (1000001). | プライマリーキーが重複している。 | |
| 23000 | ||
| 515 | Cannot insert the value NULL into column ‘sales_id’, table ‘develop.blog.sales’; column does not allow nulls. INSERT fails. | NULL値は許容されていない。 |
| S1000 | ||
| 0 | I/O error while reading BCP format file | フォーマットファイルの最終行の後に改行されていない。 |
| S1000 | ||
| 0 | Unexpected EOF encountered in BCP data-file | データファイルのカラム数がフォーマットファイルのカラム数よりも少ない。 |
| S1002 | ||
| 0 | Invalid Descriptor Index | フォーマットファイルの形式が不正である。 |
注意点
Azureなどサーバ上のDBにBCPでデータを入れる場合にローカルから実行していしまうと、とてつもなく時間がかかる&ネットワークが重くなる可能性があります。仕事ができなくなるくらいPCが遅くなってしまいましたし、データが入るスピードもかなり遅いです。そのため、DBにアクセスできるサーバ上でBCPを実行することをオススメします。
① ローカルでデータ作成
↓
② データを圧縮しサーバへ転送
↓
③ データを解凍しサーバ上にてBCPを実行
まとめ
フォーマットファイルの調整は大変ではありますが、一度整理してしまえば大量データをかなり効率的にインポートすることができます。性能試験環境の作成だけでなく、データを移行する処理や大量データが連携されるバッチ処理などに有効な手段となります。
BCPコマンド以外にもSQL Server関連では各種インデックスの説明をしていたり、性能改善した際のポイントについても言及しているので是非読んでみてください！
インデックスについて
【SQL Server】インデックスと実行計画について解説！
SQL Serverの各インデックスの特徴や実行計画の見方など、性能チューニングするにあたり基本的なことについて言及しています！
性能改善について
業務で行ったバッチ処理を性能改善した際に感じた重要なことについて発信しています！