SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server 2017 (Windows / Linux) の Unicode テキストの BULK INSERT / bcp での取り込みについて

leave a comment

SQL Server では、BULK INSERT ステートメント / bcp ユーティリティ 等を使用してテキストを取り込むことができます。

SQL Server 2017 の on Windows / on Linux 両方で、これらの方法を使用することができるのですが、使用する OS によって多少動作の違いが出てきます。

これらの動作について確認する機会がありましたのでまとめておきたいと思います。

最初にざっくりとした結果を書いておきますと、

  • SQL Server 2017 on Linux の BULK INSERT については、UTF-8 の取り込みができず、UTF-16 BOM あり (UTF-16 BE / UTF-16 LE) であれば可能であった
  • bcp については、Windows / Linux ともに、UTF-8 / UTF-16 LE / UTF-16 BE / UTF-16LE を取り込むことができた

SQL Server の Unicode の文字コードについて

最初に、SQL Server の Unicode 対応について軽く触れておきたいと思います。

SQL Server は Unicode の文字列を格納することができ、SQL Server 2017 の時点では、nchar / nvarchar を使用することで、Unicode の文字列を格納することができます。

Unicode には、いくつかの文字符号化方式 があります。

使用される機会の多いのは UTF-8 / UTF-16 の二種類ではないでしょうか。
(UTF-8 の方が使用頻度は高いと思いますが。)

SQL Server では、基本としては UTF-16 が使用されており、nchar / nvarchar に文字列を格納した場合、UTF-16 の文字コードで格納されていることになります。

SQL Server のステートメントや、コマンドに「Unicode」と書かれており、文字符号化方式 (エンコード) が指定されていない場合は、UTF-16 で動作していると考えた方が良い機会が多いです。

ベースが UTF-16 で考えられているので、データベースエンジン部分の UTF-8 対応については、バージョンアップで対応となっていることが多いです。
(SSIS のような ETL ツールに関しては、当初から UTF-8 のテキストを取り込むことができています。今回対象として記載しているのは「データベースエンジン本体部分」についてです)

BULK INSERT / bcp については、SQL Server 2016 以降で UTF-8 をサポートするようになりました。

次のバージョンとなる SQL Server 2019 では、データとして UTF-16 ではなく UTF-8 で文字データを登録できるようになります。
実装としては、nchar / nvarchar については、従来通り UTF-16 で格納し、char / varchar といった今までは非 Unicode 文字列を格納していたデータ型に対して、「照合順序」の設定によっては UTF-8 のデータを格納できるようになるというものとなっています。

 

SQL Server の照合順序

Unicode に対応するのはデータ型だけではありません。

先ほどの項でも触れましたが「照合順序」の設定も Unicode に影響を与える一因となります。

SQL Server には、照合順序という設定があり、非 Unicode の文字列 (char / varchar) の文字列の格納時のコードページと、文字列の比較時 (条件一致やソート) の重みづけ (同一とみなすか、どちらをソート時に先頭にするかという比較方法) に大きく影響を与えます。

日本語版の SQL Server をデフォルトの設定でインストールすると「Japanese_CI_AS」という照合順序が使用されます。

この照合順序を使用した場合、ざっくりというと次のような動作が行われることになります。

  • Japanese
    • char / varchar への格納は CP932 (Shift-JIS) で実施
  • 文字列の比較
    • CI : 大文字と小文字を区別しない
    • AS : アクセント文字を区別しない

文字コードも日々進化しており、新しい文字の利用方法 (サロゲートペア / 補助文字 / 異体字セレクタ等) が追加されています。

SQL Server ではこれらに対応するために、バージョンアップされたタイミングで照合順序が追加されます。

  • Japanese
    • Unicode 2.0
  • Japanese_90
    • Unicode 3.2
  • Japanese_Bushu_Kakusu_100
    • Unicode 5.0
  • Japanese_XJIS_100
    • Unicode 5.0
  • Japanese_XJIS_140
    • バージョンが明記されているドキュメントがなかったのですが、設定によって異体字セレクタは対応しています。
      (Unicode 6.1 , 6.3 あたりのバージョンと明記されている情報があればよかったのですが見つからないのですよね)

Unicode の文字列については最新の文字についても Japanese の照合順序を使用した nchar / nvarchar に格納することができますが、文字列の重みづけの判断が最新の文字に対応していないため、サロゲートペア / 補助文字 / 異体字セレクタといった方法が使われている文字の比較が想定通り動作しなかったり、文字列関数の結果が想定通りにならないことが考えられます。
例として、複数の文字で一文字を表現しているサロゲートペアのような文字を使用した場合、サロゲートペアをサポートしていない照合順序では、文字列長を取得した際に、1 文字ではなく 2 文字として認識するような動作となる可能性があります。

詳細については 照合順序 – 文字の比較と並び順 (その 1) を確認していただくとよいかと。

なお、SQL Server 2019 で追加される UTF-8 の文字格納についても照合順序の設定により、格納できるかが変わり、「_UTF8」というオプションが使用できる照合順序でのみ、char / varchar に UTF-8 の文字列を格納できるというものになっています。

 

今回試した内容

今回は、SQL Server 2017 on Windows と、SQL Server 2017 on Linux で次のような検証を実施しています。

  • UTF-8 / UTF-16 のエンコードと取り込みへの影響
  • 上記のファイルの改行が LF / CRLF となっている場合の取り込みへの影響
  • 上記のファイルの BOM の有無による取り込みへの影響

これらのファイルを BULK INERT / bcp で取り込もうとした場合に、どのような動作となり、Windows / Linux の SQL Server によって、動作の変化がないかを確認しています。

 

BULK INSERT と bcp の Windows / Linux の違い

今回、取り込みとしては BULK INSERTbcp を使用していますが、これにも少し理由があります。

BULK INSERT による取り込みは、Windows と Linux でサポートしているオプションに違いが元々あり、現時点では、SQL Server on Linux では一部サポートされていないオプションが存在しています。

  • BULK INSERT
    • Linux では、CODEPAGE オプションをサポートしていない
      • 2019 で CODEPAGE=’RAW’ をサポート予定

bcp については認証系のサポート状況が違うということが明記されているのですが、「-C」のコードページオプションの動作も少し違っているように見受けられるのですが、これについては明記されているところが見つかりませんでした。

 

二つの方法を確認した理由

BULK INSERT と bcp の二種類の方法を使用して取り込みをした理由ですが、SQL Server on Linux の仕組みを考慮したためです。

SQL Server on Linux ではデータベースエンジン / ツールの両方で Windows と同名のものが実装されていますが、データベースエンジンとツールでは動作の実装方法が異なっています。

ツールに関しては動作させる OS 向けのツールですので、bcp / sqlcmd といった、従来の SQL Server on Windows で使用していたツール群は、Windows / Linux で全く同じツールということではなく、各 OS 向けのツールとなっています。
そのため、もともとの動作の違いが発生する可能性は考えられます。
(新しく開発されたツールとなる mssql-cli は、当初からクロスプラットフォーム対応したものとして開発されていますので、このツールについては状況が変わりますが)

SQL Server のデータベースエンジン部分については、ツールとは少し考え方が変わります。

SQL Server on Linux のデータベースエンジンの実装方法ですが、「Windows 版の SQL Server を Linux 上で動作させる」というようなアプローチが行われています。
そのため、SQL Server on Linux で動作している SQL Server のデータベースエンジンのバイナリは、Windows と共通のものが使用されています。

Windows のバイナリを直接 Linux 上でどうさせることはできませんので、「SQL PAL」(SQL Platform Abstraction Layer) という抽象化層を介すことで、Windows の SQL Server の要求を Linux 向けのシステムコールとして実行するというような方法で動作しています。

SQL Server on Linux って?(第 2 回目)で紹介されている次の画像が、この説明となります。

そのため、bcp が Linux 寄りの動作をしていたとしても SQL Serve のデータベースエンジンに実装されている BULK INSERT であれば、Windows 寄りの動作をする可能性がありますので、両方法で確認をしています。

 

検証環境と使用したテキスト

今回使用した際に使用した環境等のコマンドは こちら においておきました。

SQL Server on Linux 側は Docker で作っていたので「docker run」した際のコマンドを置いてあります。

テーブルについては、上記の Gist の PostCode というテーブルを使用しています。

テキストですが、PostCode Base Text.txt という住所のデータを基にしたテキストを使用していますが、1 行目については、Unicode でないと格納することができない「森鷗外る」という文字を入力しており、Unicode 依存の文字が含まれているようなテキストを使用しています。

このテキストを、次のエンコードで保存し、それぞれについて改行コードを LF (Linux 標準) / CRLF (Windows 標準) 変えたものを作って検証を行いました。

  • UTF-8
    • BOM (0xEF 0xBB 0xBF) あり
    • UTF-8 BOM なし
  • UTF –16 BE
    • BOM (0xFE 0xFF) あり
  • UTF-16 LE
    • BOM (0xFF 0xFE) あり
  • UTF-16BE
    • BOM なしの UTF-16 Big Endian
  • UTF-16LE
    • BOM なしの UTF-16 Little Endian

 

検証結果

検証結果ですが、次の画像の結果となりました。

image

今回使用したパターンのファイルについては、bcp であれば、Windows / Linux の bcp ともに同程度の取り込みとなっています。
(Linux の方が正常に取り込めるパターンが少し多かったです。)

BULK INSERT については大きく取り込み結果に違いが出ています。

こちらについては Windows ベースで開発されたものであり、「CODEPAGE=65001」が、現時点の SQL Server on Linux では指定することができないため、UTF-8 の取り込みについては行うことができませんでした。
SQL Server 2019 CTP 2.3 で「CODEPAGE=’RAW’」が指定できるようになりますので、これを使用することで UTF-8 の取り込みができるかも試してみたのですが、取り込めるオプションを見つけることができませんでした。

UTF-16 については、SQL Server が標準的に使用している Unicode 体系ですので、こちらについては、「UTF-16」(BE / LE の BOM があるもの) であれば、取り込みが行えています。

LF / CRLF の両改行コードを取り込むことができましたが、改行コードによって使用するオプションが異なっているので、この辺は注意が必要となります。
(SQL Server on Linux で動作している SQL Server のベースは、Windows 版ですので、Unicode のエンコードや改行コードの標準的な考え方が Windows 寄りになっています)

BOM のない、「UTF-16BE」「UTF-16LE」については、Windows / Linux ともに取り込めるオプションを見つけることができませんでした。

BULK INSERT では Unicode のデータファイルタイプとして認識すると、割と融通を聞かせてくれ、インポートをしてくれる動作のように見えるのですが、BULK INSERT ステートメントが取り込もうとしたファイルを Unicode のデータファイルタイプとして認識するのには条件があるような動作をしていました。

BULK INSERT ステートメントは次のファイルを Unicode のファイルとして認識しているようで、BOM のないファイルについては、有効な Unicode のデータファイルタイプと認識させることができませんでした。

  • データファイルの先頭が「0xFE 0xFF」で始まっている → UTF16 BE (BOM あり)
  • データファイルの先頭が「0xFF 0xFE」で始まっている → UTF16 LE (BOM あり)

Linux であれば、「hd <ファイル名> | head」で確認できますので、「iconv」で UTF-16 で、文字コードを変更した場合は、BOM の状態は意識しておいた方がよさそうかと。

 

フィードバック等

今回の検証を実施している際に少し情報を調べてみたところ、Github 上にも SQL Server on Linux のコードページについての話題が BULK with Codepage Parameter on Linux で上がっていました。

SQL Server on Linux で、「CODEPAGE = 65001」がサポートされると、UTF-8 のファイル取り込みの利便性が向上するのではと思い、サポートされる予定があるのかはコメントをつけてみました。

検証した感じでは、SQL Server 2019 on Linux で 「CODEPAGE = ‘RAW’」がサポートされるより、「CODEPAGE = ‘65001’」がサポートされた方が良いのではないかと思い、フィードバックとしても SQL Server on Linux BULK INSERT to support CODEPAGE = ‘65001’ で上げておきました。

Written by masayuki.ozawa

3月 9th, 2019 at 2:48 pm

Posted in SQL Server

Tagged with ,

Leave a Reply

*