--- title: 【小ネタ】Snowflakeの照合順序をCOLLATE関数を使って検証してみた description: Snowflakeにおける照合のサポートを他のクラウドDWHサービスと比較し、実際にCOLLATE関数を利用してクエリ内での照合順序を操作をしながら検証しました。 image: https://knowledge.insight-lab.co.jp/hubfs/%E3%82%AD%E3%83%BC%E3%83%93%E3%82%B8%E3%83%A5%E3%82%A2%E3%83%AB.jpg --- ![INSIGHT LAB](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF_WHT-1.png?width=400&height=110&name=INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF_WHT-1.png) ![INSIGHT LAB](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF.png?width=400&height=110&name=INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF.png) [![お問い合わせ](https://hubspot-no-cache-na2-prod.s3.amazonaws.com/cta/default/5935783/c64a7bb0-e32a-499b-851b-b4e7a66da275.png)](https://hubspot-cta-redirect-na2-prod.s3.amazonaws.com/cta/redirect/5935783/c64a7bb0-e32a-499b-851b-b4e7a66da275) - [Snowflakeとは](https://service.insight-lab.co.jp/snowflake) - [SNOWFLAKEナレッジ](https://knowledge.insight-lab.co.jp/snowflake) - [お役立ち資料](https://download.insight-lab.co.jp/snowflake/download) ###### 6 分で読むことができます # [【小ネタ】Snowflakeの照合順序をCOLLATE関数を使って検証してみた](https://knowledge.insight-lab.co.jp/snowflake/collation-0) 執筆者 [Chris Hirasawa](https://knowledge.insight-lab.co.jp/snowflake/author/chris) 更新日時 2021年12月17日 ###### Topics: [snowflake](https://knowledge.insight-lab.co.jp/snowflake/tag/snowflake) [照合順序](https://knowledge.insight-lab.co.jp/snowflake/tag/照合順序) ![](https://knowledge.insight-lab.co.jp/hubfs/%E3%82%AD%E3%83%BC%E3%83%93%E3%82%B8%E3%83%A5%E3%82%A2%E3%83%AB.jpg) 目次 ## 前置き 既存のDWHまたはRDBMSからSnowflakeへ移行するケースをもとに、実際に照合順序で頭を悩まされたことがあるのでその経験も踏まえてSnowflakeでの照合順序の仕様について紹介させて頂きます。 公式ドキュメント 該当ドキュメントは下記リンクをご参照ください。 [Snowflake - 照合のサポート](https://docs.snowflake.com/ja/sql-reference/collation.html#collation-support) [Snowflake - COLLATE](https://docs.snowflake.com/ja/sql-reference/functions/collate.html) [Snowflake - COLLATION](https://docs.snowflake.com/ja/sql-reference/functions/collation.html) ## 一般的なCloud DWHの照合順序のサポート まず、他のクラウド基盤でサービス展開されている Amazon RedshiftやGoogle Bigqueryなどの高速処理に長けた列指向（カラムナー）型は、 どのような照合順序の仕様なのか調べてみました。 - Amazon Redshift - [データベース開発者ガイド - 照合順序](https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_collation_sequences.html) - [Amazon公式記事 2021年06月 「列レベルでCSからCIへの照合順序の変更が可能」](https://aws.amazon.com/jp/about-aws/whats-new/2021/06/amazon-redshift-now-supports-case-insensitive-collation-with-column-level-overrides/) ※CS → Case Sensitive（大文字小文字を区別する） 　CI → Case Insensitive（大文字小文字を区別しない） - 公式ドキュメントからデフォルトは「utf8(\_bin)」に準じていると考えられます。 - 照合順序の変更は、大文字小文字の区別のみカラム単位で指定可能です。 - Google Bigquery - [「照合順序」についての公式ドキュメントはない - Stack overflow 「Bigquery Collation」](https://stackoverflow.com/questions/65441456/bigquery-collation) - - [「ORDER BY」の公式ドキュメント](https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_ORDER_BY_clause.html) - - こちらも公式ドキュメントから「デフォルトは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言語コード](https://www.asahi-net.or.jp/~ax2s-kmtn/ref/iso639.html)}(\_{[ISO 3166-1国名コード](https://www.asahi-net.or.jp/~ax2s-kmtn/ref/iso3166-1.html)}) - 大文字小文字区別　：\_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も含めたデータ分析基盤全体の構築支援もご提供しております。お気軽にお問い合わせください。 Snowflakeを体験してみませんか？ INSIGHT LABではSnowflake紹介セミナーを定期開催しています。Snowflakeの製品紹介だけでなく、デモンストレーションを通してSnowflakeのシンプルなUI操作や処理パフォーマンスの高さを体感いただけます。 [![詳細はこちら](https://hubspot-no-cache-na2-prod.s3.amazonaws.com/cta/default/5935783/0d0b0d86-fdb7-4351-ab14-d482c854defc.png)](https://hubspot-cta-redirect-na2-prod.s3.amazonaws.com/cta/redirect/5935783/0d0b0d86-fdb7-4351-ab14-d482c854defc) - [Tweet](https://twitter.com/share) ![Chris Hirasawa](https://knowledge.insight-lab.co.jp/hubfs/publicdomainq-0046330yay-2.jpg) #### 執筆者 [Chris Hirasawa](https://knowledge.insight-lab.co.jp/snowflake/author/chris) FISH ###### [前の投稿](https://knowledge.insight-lab.co.jp/snowflake/digdag-snowflake) [![Digdagを使ってSnowflakeでクエリを実行してみた](https://knowledge.insight-lab.co.jp/hubfs/Snowflake-Logo-1.png)](https://knowledge.insight-lab.co.jp/snowflake/digdag-snowflake) ###### [Digdagを使ってSnowflakeでクエリを実行してみた](https://knowledge.insight-lab.co.jp/snowflake/digdag-snowflake) ###### [次の投稿](https://knowledge.insight-lab.co.jp/snowflake/snowflake-external-function-nlp-sentiment) [![ネガティブ！ポジティブ！ニュートラル？  Snowflakeの外部関数×Amazon Comprehendで感情分析してみた](https://knowledge.insight-lab.co.jp/hubfs/image-png-Dec-17-2021-07-33-33-52-AM.png)](https://knowledge.insight-lab.co.jp/snowflake/snowflake-external-function-nlp-sentiment) ###### [ネガティブ！ポジティブ！ニュートラル？  Snowflakeの外部関数×Amazon Comprehendで感情分析してみた](https://knowledge.insight-lab.co.jp/snowflake/snowflake-external-function-nlp-sentiment) RECOMMEND こちらの記事も人気です。 ###### 4 分で読むことができます。 ##### [Snowflakeの料金体系｜クレジットと費用最適化のポイントをご紹介](https://knowledge.insight-lab.co.jp/snowflake/pricing) ###### 2021年 6月 28日 ###### 12 分で読むことができます。 ##### [【Snowflake×dbt】データパイプライン構築](https://knowledge.insight-lab.co.jp/snowflake/dbt-data-pipeline) ###### 2022年 6月 17日 ###### 5 分で読むことができます。 ##### [【禁断の比較？】SnowflakeとTreasure Dataを比べてみました](https://knowledge.insight-lab.co.jp/snowflake/snowflake-vs-treasuredata) ###### 2021年 5月 24日 ###### 1 分で読むことができます。 ##### [Snowsightでどこまでできるか！？限界に挑戦！](https://knowledge.insight-lab.co.jp/snowflake/snowsight-executive-dashboard) ###### 2023年 12月 14日 ###### 5 分で読むことができます。 ##### [【Snowflake】新機能「Streamlit in Snowflake」とは何者か！？](https://knowledge.insight-lab.co.jp/snowflake/-snowflake-streamlit-in-snowflake-introduce) ###### 2023年 9月 21日 ###### 11 分で読むことができます。 ##### [【Snowflake】SQLと標準関数のみでRAGシステムをサクッと構築（プロトタイプ編）](https://knowledge.insight-lab.co.jp/snowflake/-snowflake-rag-prototype) ###### 2024年 5月 28日 ##### 運営会社 運営会社 - [会社概要](https://insight-lab.co.jp/about/) - [お問い合わせ](https://contact.insight-lab.co.jp/main) ##### メニュー メニュー - [Snowflakeとは](https://service.insight-lab.co.jp/snowflake) - [Snowflakeナレッジ](https://knowledge.insight-lab.co.jp/snowflake) - [お役立ち資料](https://download.insight-lab.co.jp/snowflake/download) [![INSIGHT LAB](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF_WHT-1.png?width=200&height=55&name=INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF_WHT-1.png "INSIGHT LAB")](https://www.insight-lab.co.jp/) - [プライバシーポリシー](https://www.insight-lab.co.jp/privacy.html) Copyright © 2020 INSIGHT LAB, Inc.