前置き
既存のDWHまたはRDBMSからSnowflakeへ移行するケースをもとに、実際に照合順序で頭を悩まされたことがあるのでその経験も踏まえてSnowflakeでの照合順序の仕様について紹介させて頂きます。
公式ドキュメント
該当ドキュメントは下記リンクをご参照ください。
Snowflake - 照合のサポート
Snowflake - COLLATE
Snowflake - COLLATION
一般的なCloud DWHの照合順序のサポート
まず、他のクラウド基盤でサービス展開されている
Amazon RedshiftやGoogle Bigqueryなどの高速処理に長けた列指向（カラムナー）型は、
どのような照合順序の仕様なのか調べてみました。
- Amazon Redshift
- Google Bigquery
-
- こちらも公式ドキュメントから「デフォルトはutf8(_bin)」に準じていると考えられます。
- 照合順序の変更は、指定できません。
ここまででカラムナー型は、照合順序は詳細に設定できないことが分かりました。
それこそカラムナー型の特性 = 利用用途がRDBMSとは異なるため
・Amazon RDS及びAmazon Aurora
・Google Cloud SQL及びGoogle Cloud Spanner
のような行指向のデータベースで扱うべきテーマであることが分かります。
上記を踏まえてSnowflakeではどのように照合順序を取り扱えるのか見てみましょう。
Snowflakeにおける照合順序のサポート
先ずは、Snowflakeの照合順序の仕様についてまとめてみました。
- デフォルト値
照合順序はバイナリ「utf8(_bin)」でAmazon RedshiftとGoogle Bigquery
と同じ仕様になります。
- 適用単位
ACCOUNT
DATABASE
ACHEMA
TABLE
COLUMN
※QUERY内の各評価の際やSELECTでカラムに対して指定可能
- 照合種類
{ロケール}(-{大文字小文字区別}-{アクセント記号区別}-{句読点区別}-{大文字小文字の順序}-{大文字小文字変換}-{スペース値リミング})
- ロケール：{ISO 639言語コード}(_{ISO 3166-1国名コード})
- 大文字小文字区別 ：_cs or _ci
- アクセント記号区別：_as or _ai
- 句読点区別 ：_ps or _pi
- 大文字小文字の順序：_fl or _fu
- 大文字小文字変換 ：_upper or _lower
- スペース値リミング：_trim or _ltrim or _rtrim
照合順序のサポートの比較
Amazon Redshift, Google Bigquery, Snowflakeをそれぞれまとめると下記になります。
- Amazon Redshift
- デフォルト値：utf8_bin
- 順序仕様変更可否：カラム単位で大文字小文字区別のみ変更可能
- Google Bigquery
- デフォルト値：utf8_bin
- 順序仕様変更可否：変更不可
- Snowflake
- デフォルト値：utf8_bin
- 順序仕様変更可否：従来のRDBMSと同じ粒度で変更可能
実際にCOLLATEを使って検証してみた
①テストデータ準備
②COLLATEの実行と結果の比較
1．COLLATE利用なし
TEST_000
TEST_000 -- 半角スペース
TEST_000A
TEST_000B
TEST_000a
TEST_000b
TEST_000À -- アクセント記号あり
TEST_000à
TEST_000 -- 全角スペース
NULL
→ デフォルトのutf8の昇順でソートされています
2．COLLATE利用, 空文字を指定
TEST_000
TEST_000 -- 半角スペース
TEST_000A
TEST_000B
TEST_000a
TEST_000b
TEST_000À -- アクセント記号あり
TEST_000à
TEST_000 -- 全角スペース
NULL
→ ''（空文字）の場合もデフォルト値が利用されています
3．COLLATE利用, ロケールが存在しないものを指定
TEST_000
TEST_000 -- 半角スペース
TEST_000 -- 全角スペース
TEST_000a
TEST_000A
TEST_000à
TEST_000À -- アクセント記号あり
TEST_000b
TEST_000B
NULL
→ 不正な値が指定が入力された場合は、utf8とは異なるソートになりましたので注意が必要です
4．COLLATE利用, 正規のロケールを指定
※'ja_JP' → 'ja'でも同じ動作となります。方言の適用がある場合は、言語コード_国名コードの組み合わせが必要になります
TEST_000
TEST_000 -- 全角スペース
TEST_000 -- 半角スペース
TEST_000a
TEST_000A
TEST_000à
TEST_000À -- アクセント記号あり
TEST_000b
TEST_000B
NULL
→ 分かりにくいですが全角スペースと半角スペースが不正な値が指定された場合と逆になっています
5．アクセント記号を区別しない形式を指定
TEST_000
TEST_000 -- 全角スペース
TEST_000 -- 半角スペース
TEST_000a
TEST_000à
TEST_000A
TEST_000À -- アクセント記号あり
TEST_000b
TEST_000B
NULL
→ アクセント記号が区別されず小文字の「a」と「à」などが同列の順序でソートされています
6．大文字小文字を区別しない形式を指定
TEST_000
TEST_000 -- 全角スペース
TEST_000 -- 半角スペース
TEST_000A
TEST_000a
TEST_000À -- アクセント記号あり
TEST_000à
TEST_000B
TEST_000b
NULL
→ 大文字と小文字の順序関係が【1．】とは異なります
他にも多種多様に指定ができますが、本記事では割愛させて頂きます。
また、COLLATION関数等を使ってカラムに独自に適用されている照合順序を調べてみることも今後記事にしていきたいと考えています。
まとめ
今回は、Amazon RedshiftやGoogle bigqueryなどのDWHによく使われるサービスとを比較しデフォルト値が「utf8(_bin)」であることや照合サポートに明確な違いがあることが分かりました。
また、特にRDBMS系から移行を検討しているお客様には現在適用されている照合順序
についてPoCの段階で明確に定義し、本移行に望むのがベストです。
さらに、グローバル展開していくお客様では、Snowflake上で各国の照合順序を適用して
適切なデータ順序でレポート配信などにも有効な整合性の取れたデータが適用できます。
本記事の内容は以上となります！
設計段階から設定していくべき観点なので本記事の知見が少しでもお役立てできればと思います。
以上、「【小ネタ】Snowflakeの照合順序を操作してみる」でした！
弊社では、Snowflakeの活用に向けた提案、PoC実施サポート、導入、運用保守、既存DWHからの移行などをご支援いたします。また、BIツール・ETLも含めたデータ分析基盤全体の構築支援もご提供しております。お気軽にお問い合わせください。
