【小ネタ】Snowflakeの照合順序をCOLLATE関数を使って検証してみた

前置き

既存のDWHまたはRDBMSからSnowflakeへ移行するケースをもとに、実際に照合順序で頭を悩まされたことがあるのでその経験も踏まえてSnowflakeでの照合順序の仕様について紹介させて頂きます。

公式ドキュメント

該当ドキュメントは下記リンクをご参照ください。
Snowflake - 照合のサポート
Snowflake - COLLATE
Snowflake - COLLATION

一般的なCloud DWHの照合順序のサポート

まず、他のクラウド基盤でサービス展開されている
Amazon RedshiftやGoogle Bigqueryなどの高速処理に長けた列指向（カラムナー）型は、
どのような照合順序の仕様なのか調べてみました。

• Amazon Redshift
  • データベース開発者ガイド - 照合順序
  • Amazon公式記事 2021年06月 「列レベルでCSからCIへの照合順序の変更が可能」
    ※CS → Case Sensitive（大文字小文字を区別する）
    　CI → Case Insensitive（大文字小文字を区別しない）
  • 公式ドキュメントからデフォルトは「utf8(_bin)」に準じていると考えられます。
  • 照合順序の変更は、大文字小文字の区別のみカラム単位で指定可能です。
• Google Bigquery
  • 「照合順序」についての公式ドキュメントはない - Stack overflow 「Bigquery Collation」

• • 「ORDER BY」の公式ドキュメント

• • こちらも公式ドキュメントから「デフォルトは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の実行と結果の比較

まとめ

今回は、Amazon RedshiftやGoogle bigqueryなどのDWHによく使われるサービスとを比較しデフォルト値が「utf8(_bin)」であることや照合サポートに明確な違いがあることが分かりました。

また、特にRDBMS系から移行を検討しているお客様には現在適用されている照合順序
についてPoCの段階で明確に定義し、本移行に望むのがベストです。

さらに、グローバル展開していくお客様では、Snowflake上で各国の照合順序を適用して
適切なデータ順序でレポート配信などにも有効な整合性の取れたデータが適用できます。

本記事の内容は以上となります！
設計段階から設定していくべき観点なので本記事の知見が少しでもお役立てできればと思います。

以上、「【小ネタ】Snowflakeの照合順序を操作してみる」でした！

弊社では、Snowflakeの活用に向けた提案、PoC実施サポート、導入、運用保守、既存DWHからの移行などをご支援いたします。また、BIツール・ETLも含めたデータ分析基盤全体の構築支援もご提供しております。お気軽にお問い合わせください。