SQL Server で BULK INSERT する (2) 蛇足編
クロスワープの大鷲です。
前回、SQL Server の BULK INSERT の使い方について、最もシンプルなケースを紹介しました。
今回は、せっかく色々調べた情報をお蔵入りさせるのももったいないのでもう少し複雑なケースについて紹介します。
ただし、前回も口を酸っぱくして言いましたが、可能な限りデータ ファイルの方を修正して、SQL はシンプルに保つべきだというのは繰り返し言っておきます。
ですから、今回の記事の内容は、基本的には「やらなくて済むならやるべきではないこと」だということを念頭に置いてお読みください。
なお、データを取り込む対象のテーブルについては、前回の記事を参照してください。
UTF-8 のファイルを取り込む
前回のデータファイルは UTF-16 でしたが、SQL Server 2016 以降であれば、UTF-8 のファイルを取り込むこともできます。
SQL はこうなります。
BULK INSERT [dbo].[People] FROM 'D:\Bulk\Bulk4.txt' WITH ( DATAFILETYPE = 'char', CODEPAGE = '65001' );
データ ファイルの BOM はあってもなくてもよさそうです。
Shift-JIS のファイルを取り込む
UTF-8 の場合と同様ですが、CODEPAGE = '932' にします。
フォーマット ファイル
ここまで、テーブルとデータ ファイルの列順は同じであるという前提でやってきました。
そのため、データ ファイルの先頭には、実際には使われないダミーの Id がありました。
しかし、場合によっては、テーブルとデータ ファイルのレイアウトが異なることもあるでしょう。
そのような場合には、フォーマット ファイルを使う必要があります。
フォーマット ファイルとは、テーブルとデータ ファイルの列をマッピングするためのものです。
フォーマット ファイルを作る
フォーマット ファイルは手書きでも作れますが、ツールで作ったものを手で書き換える方が簡単です。
というわけで、まずは既定のフォーマットファイルをツールで作りましょう。
SQL Server に付属している bcp というツールを使います。
なお、bcp はフォーマット ファイルを作るだけでなく、データの一括エクスポート／インポートも可能です*1が、今回はその使い方はとりあげません。
フォーマット ファイルを出力するコマンドは以下のようなものになります。
bcp dbo.People format nul -S <server> -d <database> -f <format file> -w -T
最初の format が、フォーマット ファイルを作るコマンドです。
その次の nul は、データの一括エクスポート／インポートする際はデータ ファイルを指定するのですが、今回はフォーマット ファイルを作るだけなので、「ファイル無し」という意味で nul を指定します。
-f で出力するフォーマットファイル名を指定します。
-w は前回の DML でいうと DATAFILETYPE = 'widechar' に相当します。
-T は Windows 認証を使うことを意味します。SQL Server 認証の場合は -U と -P を使います。
フォーマット ファイルの構造
このコマンドで出力されるのは、以下のようなファイルです。
13.0 3 1 SQLNCHAR 0 24 "\t\0" 1 Id "" 2 SQLNCHAR 0 200 "\t\0" 2 Name Japanese_XJIS_100_BIN2 3 SQLNCHAR 0 24 "\r\0\n\0" 3 Age ""
このファイルの形式は以下の通りです。*2
一行目の 13.0 は SQL Server のバージョン（13.0 は SQL Server 2016）、次の 3 はその後の行数＝取り込むデータ ファイルの列数です。
3 行目からが、取り込むデータファイル中の各列に対応します。
項目を順に解説しますと
| 1 | 行番号＝データ ファイル中の列番号 |
| SQLNCHAR | データ ファイル中の列のデータ型 |
| 0 | プレフィックス長 |
| 24 | 最大データ長 |
| "\t\0" | 区切り文字 |
| 1 | データベース中のテーブルの列番号 |
| Id | データベース中のテーブルの列名 |
| "" | データベース中のテーブルの照合順序 |
となっています。
SQLNCHAR は、テーブル中の列の型ではなく、取り込むデータ ファイル中のデータ型になります。今回は -w オプションで出力しているので、すべての列が Unicode 文字列型であることを示す SQLNCHAR になっています。
その後の 0 はプレフィックス長ですが、これは -w オプション利用時には使われない項目なので 0 になっています。*3
次の 24 は、データ ファイル中の最大バイト数です。何故 24 なのかはよくわかりません。Id は int なので整数で最大 10 桁と、符号で１桁、それが Unicode なので１文字２バイトとして 22 バイト……という感じかと思いますが、もう２バイトは何なんでしょう。
区切り文字は、前回やった列区切り文字および行区切り文字です。最後の区切り文字は行区切り文字（\r\0\n\0）、それ以外の場合は列区切り文字（\t\0）になります。この例では Unicode なので \0 がついています。
その後、テーブルの列番号、列名、照合順序と続いています。
テーブル側の列をスキップする
データ ファイルの先頭にあったダミーの Id 列を消せないでしょうか。
つまり、テーブル定義はそのまま、こういうデータ ファイルを取り込みたいわけです。
アリス 20 ボブ 31 チャーリー 56
テーブル側の Id 列を飛ばして、2 列目からインポートしたいというわけです。
フォーマット ファイルを使えば可能です。
先程のフォーマット ファイルを、このように書き換えます。
13.0 2 1 SQLNCHAR 0 200 "\t\0" 2 Name Japanese_XJIS_100_BIN2 2 SQLNCHAR 0 24 "\r\0\n\0" 3 Age ""
ファイルの列数を１つ減らし、Id 列に対応する行を消し、列番号を詰めています。
このファイルを使うには、DML を以下のように書き換えます。
BULK INSERT [dbo].[People] FROM 'D:\Bulk\Bulk5.txt' WITH ( DATAFILETYPE = 'widechar', FORMATFILE = 'D:\Bulk\Bulk5.fmt' );
ファイル側の列をスキップする
逆に、データ ファイルに、INSERT には使用されない余分な列がある場合もあります。
0 アリス 20 女 0 ボブ 31 男 0 チャーリー 56 男
４列目は性別ですが、これは INSERT には使用しないという状況を想定します。
この場合もフォーマット ファイルを使います。
以下のように書き換えましょう。
13.0 4 1 SQLNCHAR 0 24 "\t\0" 1 Id "" 2 SQLNCHAR 0 200 "\t\0" 2 Name Japanese_XJIS_100_BIN2 3 SQLNCHAR 0 24 "\t\0" 3 Age "" 4 SQLNCHAR 0 2 "\r\0\n\0" 0 __Gender__ Japanese_XJIS_100_BIN2
注目すべきは４列目の５項目目です。
取り込まない列に関しては「テーブルの列番号」を 0 にすることで無視することができます。
CSV ファイルを取り込む
前回の最後に
CSV を取り込むのは諦めてください。
と書きました。
実は、SQL Server 2017 からは、CSV ファイルを取り込む機能があります。
が、2017 はまだプレビュー版ですので、2016 でなんとかやりたい……ということもあるでしょう。
いくつかの条件が揃えば、やってやれないことはありません。
たとえば、こんなファイルを取り込むことを考えてみましょう。
"アリス",20,"女" "ボブ",31,"男" "チャーリー",56,"男"
フォーマット ファイルはこうなります。
13.0 4 1 SQLNCHAR 0 0 "\"\0" 0 __DUMMY_1__ "" 2 SQLNCHAR 0 200 "\"\0,\0" 2 Name Japanese_XJIS_100_BIN2 3 SQLNCHAR 0 24 ",\0\"\0" 3 Age "" 4 SQLNCHAR 0 2 "\"\0\r\0\n\0" 0 __Gender__ Japanese_XJIS_100_BIN2
CSV では、値をダブル クォーテーションで囲むことはよくあります。
しかし、BULK INSERT は、その際のダブル クォーテーション文字を無視して、囲まれている内側だけをデータと認識するような気の利いたことはしてくれません。
そこで、フォーマット ファイルで頑張ることになります。
最初の __DUMMY_1__ というのは、Name の前のダブル クォーテーションを除去するための仕掛けです。
Name の前に空の列が存在し、それと Name 列を区切る列区切り文字がダブル クォーテーションであるということにしています。
テーブルの列番号が 0 なので、INSERT には影響を与えません。
さらに、Name 列（と Age 列の間の）列区切り文字を「",」にすることで、Name の後のダブル クォーテーションを取り除いています。
つまり、こういうことです（赤文字が区切り文字）。
[空]"アリス",20,"女"[CR LF]
このフォーマット ファイルを書くのは結構骨が折れました。
前回も冒頭で言いましたが、BULK INSERT のエラー メッセージはとても不親切なので、フォーマット ファイルのデバッグは大変です。
その苦労の割に、あまり自由度は高くありません。
ヘッダーがあるとダメです*4し、データの中にダブル クォーテーションがある場合もダメです。
また、同じ列の値が、行によってダブル クォーテーションで囲まれていたりいなかったりする場合もダメです。
苦労の割にメリットが少ないので、お勧めはしません。
それより、素直な TSV に変換した方がずっと簡単に取り込めます。
SQL Server 2017 では
SQL Server 2017 からは、CSV ファイルを取り込む機能が備わりました。
やり方は簡単で、
BULK INSERT [dbo].[People] FROM 'D:\Bulk\Bulk6.csv' WITH ( DATAFILETYPE = 'widechar', FORMAT = 'CSV' );
と書くだけです。
CSV は RFC 4180 の仕様に沿ってパースされます。
そのため、ダブル クォーテーションの内側だけを値として解釈してくれるのはもちろんのこと
- 行によってダブル クォーテーションで囲まれていたり囲まれていなかったりしても大丈夫です
- データ中にダブル クォーテーションがある場合は、"" のように２つ続けて書くことでエスケープされます
という利点があります。これらはフォーマット ファイルではどうにもできない点です。
しかし、ヘッダーがあるとやはりだめです。
また、フォーマット ファイルと併用できないようなので、列のレイアウトはテーブルと同じでなければなりません。
さらに、RFC 4180 にはこのような注意書きもあります。
Interoperability considerations:
Due to lack of a single specification, there are considerable differences among implementations.
Implementors should "be conservative in what you do, be liberal in what you accept from others" (RFC 793 [8]) when processing CSV files.
世の中、この仕様に従っている CSV ばかりではないということです。
SQL Server 2016 よりはいくらか簡単に取り込めるようになったものの、やはり「うまい条件が揃えば」という前提つきなのです。
他のアプリケーションで生成された CSV ファイルをそのまま取り込めるという期待は基本的にはすべきではなく、多かれ少なかれ手動での変換が必要にります。
SQL Server 2017 を Docker for Windows で動かす
蛇足の蛇足ですが、今回、SQL Server 2017 を動かすにあたって、Docker を使ってみました。
ホスト マシンの環境は Windows 10 Enterprise (Creators Update) で、Docker for Windows 17.06 をインストールしてあります。
動かし方は DockerHub の SQL Server 2017 のページに書いてありますが、２点ほど注意事項があります。
- sa_password が単純すぎると、パスワード ポリシーに引っかかってログインできない場合があります。docker run した時点では警告は何も表示されませんので、複雑なパスワードを設定するようにしましょう。*5
- 取り込むデータ ファイルやフォーマット ファイルはコンテナー内の SQL Server からアクセスできる場所（コンテナー内のローカル ディスクなど）になければなりません。docker run の際に -v=C:\DockerShared:C:\DockerShared のようにしてディレクトリを共有しておくと、ファイルのやり取りに便利です。*6
docker run の後で表示される長い 16 進数の文字列がコンテナー ID なので、これをコピーしておきます。
続いてコンテナーの IP アドレスを取得します。
こちらのブログでは docker inspect コマンドを使う方法が提示されていますが、docker exec <コンテナー ID> ipconfig という馴染み深い方法でも可能です。
IP アドレスが分かったら、SQL Server Management Studio で接続できます。後はいつも通りにどうぞ。
