SE の雑記

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

Synapse Analytics の SQL on-demand の共有メタデータ テーブルの照合順序について

leave a comment

SQL on-demand (Serverless SQL Pool) では、共有メタデータテーブル という形で Spark Pool で作製した Parquet フォーマットを使用したテーブルを、SQL on-demand で参照することができます。

先日投稿した、Synapse Analytics の Serverless SQL Pool (SQL on-demand) でテキストを参照する際の文字コードの設定 (おまけで Synapse Link for Cosmos DB) でも Synapse Analytics の照合順序に触れましたが、共有メタデータテーブルでも照合順序はポイントとなる点がありますので、触れておきたいと思います。

ドキュメントについては、SQL オンデマンド (プレビュー) で Apache Spark for Azure Synapse の外部テーブル定義を同期する を参照すると良いかと。

既定の照合順序

Spark で作成したデータベースについては、SQL on-demand にもメタデータの共有が行われ、データベース内に作成したテーブルを参照することができます。

「default」が、Spark とメタデータを連携しているデータベース名となりますが、照合順序は「Latin1_General_100_BIN2_UTF8」固定となっています。

image

これは、DB 内に作成したテーブルについても同様で、String がマッピングされた nvarchar(max) については、DB の作成後に、DB の照合順序を変更したとしても「Latin1_General_100_BIN2_UTF8」が使用され、これを変更することは現状はできないようです。
image

照合順序については、冒頭で紹介した外部テーブル定義を同期するでも触れられています。

image

 

BIN2 照合順序 (バイナリ照合順序) が使用されている場合、データの比較はバイナリレベルで実施されるため、大文字 / 小文字の区別が行われます。

先ほど、照合順序を確認したテーブルはアクセスログを格納した次のようなデータが入っているテーブルとなります。

image

既定の照合順序は、バイナリレベルでの比較となりますので、_c1 を使用した次のクエリを実行してみます。

image

BIN2 照合順序はバイナリ比較となりますので、文字列で考えた場合は「大文字 / 小文字の区別が行われる」ということになります。そのため、大文字の GET で格納されている情報に対して、小文字の get で検索しても一致するデータがないということになります。

大文字の GET で検索を行えば、もちろん結果の取得は行われます。

image

検索時に列レベルの照合順序を指定

この状態で、大文字 / 小文字を区別しないで検索する方法は、いくつかのパターンがあるかと思いますが、「COLLATE Latin1_General_100_CI_AS_SC_UTF8」を指定して検索を行うのも、一つの方法として考えられます。

SELECT top 100 * FROM parquettable WHERE _c1 COLLATE Latin1_General_100_CI_AS_SC_UTF8 = 'get'

image

これにより、_c1 の検索については、、「COLLATE Latin1_General_100_CI_AS_SC_UTF8」(大文字 / 小文字を区別しない) 方法により、検索が行われますので、小文字の get で検索を行った場合でも情報の取得を行うことができるようになります。

左辺を Lower や Upper で変換する方法もありますが、照合順序を使用した方法であれば、かな / カナについての対応も行われるかと思います。

image

Share

Written by Masayuki.Ozawa

11月 2nd, 2020 at 10:17 am

Posted in Synapse Analytics

Tagged with

Leave a Reply