Synapse Analytics の Serverless SQL Pool (SQL on-demand) では、BLOB / ADL Gen2 上のファイルに対してクエリを実行することができます。
- Synapse SQL 内で SQL オンデマンド (プレビュー) リソースを使用してストレージ ファイルに対してクエリを実行する
- SQL オンデマンド (プレビュー) のストレージ アカウント アクセスを制御する
同様の内容を実現する機能としては、Azure Data Lake Storage のクエリ アクセラレーション があります。
こちらについては、しばやん先生が Azure Data Lake Storage の Query Acceleration が GA になったので試したら最高だった で機能の解説をされています。
この機能の比較をするために検証していたときに「そういえば、Serverless SQL Pool で、テキストを読むとき文字コードって何にする必要があったっけ？」と思い、軽く検証してみました。
今回は Shift-JIS / UTF8? / UTF16 LE の 3 パターンで検証しています。
使用しているデータは、本ブログのアクセスログを CSV に出力したものです。
シンプルな構成にするのであれば、次のような構成をしておけばいいのではないでしょうか。
- ファイルは UTF8 のエンコードを使用する
- データベースの照合順序は _UTF8 を使用する
- 日本語環境の SQL Server の照合順序と同様にするのであれば、次の照合順序のいずれかを使用しておけば、最新のUnicode を考慮した文字コード体系になる
- Japanese_XJIS_100_CI_AS_SC_UTF8
- Japanese_XJIS_140_CI_AS_UTF8
- Synapse SQL でのデータベースの照合順序のサポート では、140 系はサポートされていないということになっていますが、本投稿を書いているタイミングでは、設定はできます。(サポート対象なのかは要確認ですが)
- 基本的な検索であれば、任意の照合順序に「_UTF8」を設定したものであれば、ある程度カバーできるはず
- 日本語環境の SQL Server の照合順序と同様にするのであれば、次の照合順序のいずれかを使用しておけば、最新のUnicode を考慮した文字コード体系になる
- 検索条件で、文字列リテラルを使用する場合、「N’xxxxx’」の Unicde 変数で検索を行う。
Contents
Serverless SQL Pool のデフォルトの照合順序
Serverless SQL Pool の照合順序ですが、デフォルトでは次の設定になっています。
SELECT name, collation_name,COLLATIONPROPERTY(collation_name, 'CodePage') AS code_page FROM sys.databases
master については「SQL_Latin1_General_CP1_CI_AS」が使用され、コードページについては 1252 となっています。
Spark とのデータ連携を行うために使用される既定のデータベースである default では、「Latin1_General_100_BIN2_UTF8」が使用されます。
これが、デフォルトの設定となり、ユーザーが新規に作成したデータベースについても、単純に CREATE DATABASE をしただけでは、master と同様の「SQL_Latin1_General_CP1_CI_AS」でデータベースが作成されます。
今回は、デフォルトの照合順序である「SQL_Latin1_General_CP1_CI_AS」の状態を起点として確認したいと思います。
Shift-JIS のデータ参照
ひとまず、何も考えずに次のクエリを実行してみます。
SELECT TOP 100
*
FROM
OPENROWSET(
BULK 'result.txt',
FORMAT='csv',
PARSER_VERSION = '2.0',
FIELDTERMINATOR =',',
DATA_SOURCE = 'accesslog'
) AS T
現在、DB の照合順序は「SQL_Latin1_General_CP1_CI_AS」が設定されていますので、Shift-JIS のファイルを検索すると、検索自体はできるのですが、日本語は文字化けします。
SQL_Latin1_General_CP1 の照合順序では、通常の文字列は、コードページは 1252 で処理されますので Shift-JIS のコードページの文字を正しく表示することができません。
Shift-JIS のファイルを正しく表示したい場合には、次のように WITH で列の情報を取得する際に COLLATE で照合順序を補正します。
SELECT TOP 100
*
FROM
OPENROWSET(
BULK 'result.txt',
FORMAT='csv',
PARSER_VERSION = '2.0',
FIELDTERMINATOR =',',
DATA_SOURCE = 'accesslog'
)
WITH(
time datetime2(3),
httpMethod varchar(10) COLLATE Japanese_XJIS_140_CI_AS,
httpVersion numeric(2,1),
requestUri varchar(4000) COLLATE Japanese_XJIS_140_CI_AS,
clientIp varchar(50) COLLATE Japanese_XJIS_140_CI_AS,
securityProtocol varchar(20) COLLATE Japanese_XJIS_140_CI_AS,
requestBytes int,
responseBytes int,
httpStatusCode varchar(10) COLLATE Japanese_XJIS_140_CI_AS,
httpStatusDetails varchar(10) COLLATE Japanese_XJIS_140_CI_AS,
cacheStatus varchar(100) COLLATE Japanese_XJIS_140_CI_AS
) AS T
これで、正常に出力されます。
WHERE 句でも日本語を使用することはできるのですが、N 付きで検索しないと、日本語が ? で帰ってきて面倒なことになります。
ということで、次のようなクエリで検索を行う必要がありそうです。
SELECT TOP 100
*
FROM
OPENROWSET(
BULK 'result.txt',
FORMAT='csv',
PARSER_VERSION = '2.0',
FIELDTERMINATOR =',',
DATA_SOURCE = 'accesslog'
)
WITH(
time datetime2(3),
httpMethod varchar(10) COLLATE Japanese_XJIS_140_CI_AS,
httpVersion numeric(2,1),
requestUri varchar(4000) COLLATE Japanese_XJIS_140_CI_AS,
clientIp varchar(50) COLLATE Japanese_XJIS_140_CI_AS,
securityProtocol varchar(20) COLLATE Japanese_XJIS_140_CI_AS,
requestBytes int,
responseBytes int,
httpStatusCode varchar(10) COLLATE Japanese_XJIS_140_CI_AS,
httpStatusDetails varchar(10) COLLATE Japanese_XJIS_140_CI_AS,
cacheStatus varchar(100) COLLATE Japanese_XJIS_140_CI_AS
) AS T
WHERE
requestUri LIKE N'%メンテナンス%'
Shift-JIS のファイルは使用しない方が面倒がなくてよさそうです。
UTF8 (BOM 付き)
次は UTF8 を検索してみます。
UTF8 も何も考えずに検索すると Shift-JIS と同様に文字化けします。(結果省略)
ということで、次のような検索を行います。
SELECT TOP 100
*
FROM
OPENROWSET(
BULK 'result_UTF8.txt',
FORMAT='csv',
PARSER_VERSION = '2.0',
FIELDTERMINATOR =',',
DATA_SOURCE = 'accesslog'
)
WITH(
time datetime2(3),
httpMethod varchar(10) COLLATE Japanese_XJIS_140_CI_AS_UTF8,
httpVersion numeric(2,1),
requestUri varchar(4000) COLLATE Japanese_XJIS_140_CI_AS_UTF8,
clientIp varchar(50) COLLATE Japanese_XJIS_140_CI_AS_UTF8,
securityProtocol varchar(20) COLLATE Japanese_XJIS_140_CI_AS_UTF8,
requestBytes int,
responseBytes int,
httpStatusCode varchar(10) COLLATE Japanese_XJIS_140_CI_AS_UTF8,
httpStatusDetails varchar(10) COLLATE Japanese_XJIS_140_CI_AS_UTF8,
cacheStatus varchar(100) COLLATE Japanese_XJIS_140_CI_AS_UTF8
) AS T
これで、UTF8 のテキストファイルの内容を正常に表示することができます。
上記のクエリでは、varchar に対して、UTF8 サポートの照合順序を使用していますが、nvarchar を使用して、COLLATE なしのパターンでも動作するかと思います。
検索についても Shift-JIS と同様に N 付きで検索すれば、正常に結果も含めて検索できるはずです。
UTF16 LE (BOM 付き)
最後に UTF16 LE のファイルを検索してみます。
UTF16 を何も考えずに検索すると次のエラーになります。
メッセージ 15813、レベル 16、状態 1、行 1
Error handling external file: ‘Incorrect file encoding. DATAFILETYPE = ‘char’ selected for querying UTF16 file.’.
DATAFILETYPE = ‘char’ がデフォルトとなっているため UTF16 のファイルに対してクエリ実行ができません。
ということで、こちらも次のように実行します。
SELECT TOP 100
*
FROM
OPENROWSET(
BULK 'result_UTF16LE.txt',
FORMAT='csv',
PARSER_VERSION = '2.0',
FIELDTERMINATOR =',',
DATA_SOURCE = 'accesslog',
DATAFILETYPE = 'widechar'
)
WITH(
time datetime2(3),
httpMethod nvarchar(10),
httpVersion numeric(2,1),
requestUri nvarchar(4000),
clientIp nvarchar(50),
securityProtocol nvarchar(20),
requestBytes int,
responseBytes int,
httpStatusCode nvarchar(10),
httpStatusDetails nvarchar(10),
cacheStatus nvarchar(100)
)AS T
SQL Server の nchar / nvarchar は UTF16 が使用されているので、こちらについては COLLATE で明示的に補正する必要はありません。
検索は Unicode に対しての検索ですので、UTF8 と同様に、N 付きで検索を行います。
そもそも照合順序ってなんだっけ？
SQL Server の照合順序 (Collation) については 照合順序と Unicode のサポート で解説が行われています。
照合順序はざっくり言ってしまうと、
- 文字列を比較 / 照合する際の重み
- 非 Unicode データ型 (char / varchar) のコードページになるかと思います。
重みについては「大文/字小文字の区別」のように、文字を比較 (WHERE / Sort) する際に、どのようなルールに基づいて比較を行うかの設定です。
コードページについてですが、SQL Server の Unicode データ型 (nchar / nvarchar) については、UCS2 / UTF16 固定となっており、このコードページは変更することはできません。
それ以外の文字列型のコードページについては、照合順序の影響を受けます。
「Japanese～」系の照合順序を使用した場合、char / varchar に格納される文字列が CP932 になるのは照合順序の設定の影響を受けるためです。
Serverless SQL Pool では、次の 2 種類の照合順序を設定することができます。
- データベースレベル照合順序
- 列レベル照合順序
データベースの既定値として特定の照合順序を設定する方法と、列単位で照合順序を設定する方法の 2 種類を使用することができます。
Synapse Link for Cosmos DB と照合順序
しばやん先生が Synapse Link for Cosmos DB が Synapse SQL Serverless から使えるようになったので試した で検証されていますが、Synapse Link で Cosmos DB のデータを参照する際、マルチバイトのデータを参照する際には、「_UTF8」の照合順序が必要となります。
データベース照合順序については、しばやん先生の記事でも触れられていますが、Serverless SQL Pool の照合順序は、DB の作成時 / 作成後に変更することができます。
Serverless SQL Pool は DB には、実データは持たず、メタデータのみ保持しますので、DB レベルの照合順序を変更するための障壁は SQL Server より低いかもしれませんね。
Serverless SQL Pool は DB には、実データは持たず、メタデータのみ保持しますので、DB レベルの照合順序を変更するための障壁は SQL Server より低いかもしれませんね。
CREATE DATABASE TEST2 COLLATE Japanese_XJIS_100_CI_AS_SC_UTF8 ALTER DATABASE TEST2 COLLATE Japanese_XJIS_100_CI_AS_SC_UTF8
Synapse Link でマルチバイトのデータを取り扱う際には UTF8 の照合順序が必要となりますのでデータベースレベルで、既定の設定を変えておくのも一つの方法です。
「_UTF8」の照合順序を使用すると、非 Unicode データ型のコードページは 65001 (UTF8) がデフォルトとなり、文字列のエンコードは UTF8 が基本となります。
データベースレベルの照合順序として、デフォルトの「SQL_Latin1_General_CP1_CI_AS」を使用していると、Synapse Link でマルチバイトが表示できないかというとそういうわけでもありません。
テキストファイルを操作した時と同じで、次のような構文を使用することもできます。
SELECT TOP 10 time, httpMethod, httpVersion, requestUri, clientIp, securityProtocol, requestBytes, responseBytes, httpStatusCode, httpStatusDetails, cacheStatus FROM OPENROWSET ( 'CosmosDB', N'<接続情報>', <データベース名>) WITH ( _rid varchar(1000) COLLATE Japanese_XJIS_100_CI_AS_SC_UTF8, clientIp varchar(1000) COLLATE Japanese_XJIS_100_CI_AS_SC_UTF8, securityProtocol varchar(1000) COLLATE Japanese_XJIS_100_CI_AS_SC_UTF8, userAgent varchar(1000) COLLATE Japanese_XJIS_100_CI_AS_SC_UTF8, httpVersion float, _etag varchar(1000) COLLATE Japanese_XJIS_100_CI_AS_SC_UTF8, httpStatusDetails varchar(3) COLLATE Japanese_XJIS_100_CI_AS_SC_UTF8, _ts varchar(1000) COLLATE Japanese_XJIS_100_CI_AS_SC_UTF8, requestUri varchar(3000) COLLATE Japanese_XJIS_100_CI_AS_SC_UTF8, time varchar(1000) COLLATE Japanese_XJIS_100_CI_AS_SC_UTF8, httpMethod varchar(1000) COLLATE Japanese_XJIS_100_CI_AS_SC_UTF8, requestBytes bigint, requestUri3 varchar(1000) COLLATE Japanese_XJIS_100_CI_AS_SC_UTF8, responseBytes varchar(1000) COLLATE Japanese_XJIS_100_CI_AS_SC_UTF8, httpStatusCode varchar(3) COLLATE Japanese_XJIS_100_CI_AS_SC_UTF8, cacheStatus varchar(1000) COLLATE Japanese_XJIS_100_CI_AS_SC_UTF8, id varchar(36) COLLATE Japanese_XJIS_100_CI_AS_SC_UTF8, requestUri2 varchar(1000) COLLATE Japanese_XJIS_100_CI_AS_SC_UTF8, val1 varchar(1000) COLLATE Japanese_XJIS_100_CI_AS_SC_UTF8, val2 varchar(1000) COLLATE Japanese_XJIS_100_CI_AS_SC_UTF8, val3 varchar(1000) COLLATE Japanese_XJIS_100_CI_AS_SC_UTF8, val4 varchar(1000) COLLATE Japanese_XJIS_100_CI_AS_SC_UTF8, val varchar(1000) COLLATE Japanese_XJIS_100_CI_AS_SC_UTF8 ) AS T1 WHERE requestUri LIKE N'%メンテナンス%'データベースレベルで、UTF8 対応の照合順序を使用していない場合は、WITH 句に列リストを設定し、その列リストに対して照合順序を設定します。(WITH 句のデータ型は適当に設定しています)
そうすると、データベースレベルの照合順序で「SQL_Latin1_General_CP1_CI_AS」を使用していても、Synapse Link でマルチバイトのデータを表示することが可能です。
Synapse Link for Cosmos DB では、Cosmos DB の分析ストアにアクセスされますが、このストアは BLOB 上に保存されている snappy の圧縮形式が使用された Parquet ファイルのようです。
そのため、型のマッピングについては、Azure Synapse Analytics の SQL オンデマンド (プレビュー) を使用して Parquet ファイルに対してクエリを実行する が使用されます。
このデータ型のマッピングでは、文字列データ型は次のようのマッピングされています。
データ型としては varchar にマッピングされており、エンコードは UTF8 が使用されています。
varchar のデータ型については、照合順序のコードページの影響を受けます。
データ型については、Azure Synapse Link (プレビュー) で SQL オンデマンドを使用して Azure Cosmos DB のデータのクエリを実行する でも触れられていますね。(Parquet で見るよりは、こちらが公式のドキュメント)
そのため、コードページ 65001 が使用されている照合順序でないと、マルチバイトの文字を正常に取り扱うことができません。
SELECT * FROM sys.fn_helpcollations() WHERE COLLATIONPROPERTY(name, 'CodePage') = 65001
文字列は UTF8 で取り扱われますので、日本語のデータが入っていたとしても「Japanese～」以外を使っても、基本的には動作するかと思います。
SQL Server のように、日本語の照合順序を設定するのであれば「Japanese_XJIS_100_CI_AS_SC_UTF8」「Japanese_XJIS_140_CI_AS_UTF8」辺りを使用しておけば問題ないかと。
DB の照合順序がデフォルトで、異体字を考慮すると次のようなクエリになります。
(接続文字列を隠したくて EXECUTE で実行しています)
デフォルトの照合順序である「SQL_Latin1_General_CP1_CI_AS」を使用すると、ちょっと面倒…。
Serverless SQL Pool で DB を作成するときはおとなしく UTF8 の照合順序にしておいた方がよいですね。
Serverless SQL Pool でマルチバイトのデータを操作する際には、ファイルから読むか Synapse Link から読むかで、文字コードの考え方が少し変わってきそうですが、SQL Server の照合順序の基本的な動作を考えてみると、役に立つのかもしれませんね。