背景
Synapse では、リンク設定されている Azure Storage や Cosmos DB の内容などを、右クリックから SQL文を直ぐに作成してくれる機能があります。
Microsoft が用意してくれているサンプルデータは英語のものが多いため、日本語のデータでクエリ実行すると文字化けする事が多くあります。
SQL Serverless は、データを個別に持ちません。Azure Data Lake Storage Gen2 上にファイルとして見える状態にしています。ですので、ファイルを確認すると日本語で表示できるのに、Azure Synapse Studio で表示させようとすると、文字化けする事になります。
これは、扱っている SQL Serverless (on-demand) の文字列の照合順序の設定に起因しているみたいです。
照合順序は、Server - Database - Table - Column の単位で柔軟に設定ができます。が、Synapse Studio の利便性を考えると、Table 以下では面倒です。Serverの設定は出来ないですので (恐らく)、現実的な Database レベルでの設定を行います。
データベースの照合順序の設定または変更:
画面の右上を見ると [master] Database に接続をしています。
以下のT-SQLクエリで設定されている照合順序を確認します。
SELECT CONVERT (varchar(256), SERVERPROPERTY('collation'));
結果
SQL_Latin1_General_CP1_CI_AS
要は西洋圏の文字列です。
UNICODEの照合順序を設定します。
手順
1. SQL Database に新規 Database を作成し、その際に照合順序を設定
opendataの部分は、お好きな名前に変更ください😊
CREATE DATABASE opendata COLLATE Latin1_General_100_BIN2_UTF8
終了です😊
2. 確認
SQL Serverless で、T-SQL 実行時に、Database を切り替えてください。
master:
照合順序を設定した database:
まとめ
Synapase の SQL Serverless は、SQL Engine で作られている事に立ち返る事が重要です。そして、文字化けについての対応は、ストレージへの格納方法、抽出するプロトコルの設定、アプリケーションの表示など多岐の場面で行われています。UNICODEの恩恵を私たちは知らないうちに受けているんだなーと改めて確認できました。
Spark で問題が起こったら、どうしたらいいのかも、対応しやすいと思います😎
2020/11/17 追記:
Always use UTF-8 collations to read UTF-8 text in serverless SQL pool:
https://techcommunity.microsoft.com/t5/azure-synapse-analytics/always-use-utf-8-collations-to-read-utf-8-text-in-serverless-sql/ba-p/1883633
2021/1/25 追記:
照合順序を、上記 Blob にあった Latin1_General_100_BIN2_UTF8 に変更。