💡

Snowflakeで快適に日本語をSELECTするには？

2022/06/29に公開

data

Snowflake

tech

前置き

こんにちは。株式会社GENDAのこみぃです。

日本で事業をしているとデータに日本語の文字列が入ると思います。
このときに、日本語を曖昧にSELECTしたいときってありますよね。

ひらがなとカタカナを混同したり、大文字小文字、全角半角を区別しないで良い検索がしたくなったり。。。

先日社内でこのあたりの検索のやり方を質問いただきまして、そのときに色々調べて解決を見ました。
調べている中でsnowflake関連ではこの話に触れた日本語の記事がないことに気づきましたので、この記事ではそのやり方をまとめます。

サンプルデータを作成

とりあえずサンプルデータを作ります。こんな感じのテーブルですね。

sample-table

>select * from test_tables.test_20220625;
+----+------------+
| ID | NAME       |
|----+------------|
|  1 | あいうえお |
|  2 | アイウエオ |
|  3 | アイうえお |
|  4 | アイウえお |
|  5 | アいウえオ |
|  6 | aiueo      |
|  7 | AIUEO      |
|  8 | AIueo      |
|  9 | AIUeo      |
| 10 | AiUeO      |
+----+------------+

collateを使う

曖昧にSELECTするための関数としてsnowflakeにはcollateがあります。

collateを使うことにより英語の部分を大文字小文字を区別せずに検索することが出来ます。

collate-en-ci

>select * from test_tables.test_20220625 where collate(name, 'en-ci') like '%aiueo%';
+----+-------+
| ID | NAME  |
|----+-------|
|  6 | aiueo |
|  7 | AIUEO |
|  8 | AIueo |
|  9 | AIUeo |
| 10 | AiUeO |
+----+-------+
5 Row(s) produced. Time Elapsed: 1.272s

collate + LIKE だと日本語は検索できない。。。

ここで、日本語も同じようになることを期待してクエリを書いてみます。

collate-jp-ci

>select * from test_tables.test_20220625 where collate(name, 'ja_JP-ci') like '%あいうえお%';
002403 (0A000): SQL compilation error: error line 1 at position 74
Function LIKE does not support collation: ja_jp-ci.

！！！

日本語だとLIKEが使えない！！！

こうして私は途方にくれていました。

このままでは自分に助けを求めた人に「出来ません」という、エンジニアとして最もやりたくない返しをすることになってしまいます。

containsを使おう

途方にくれる私に一筋の光を与えてくれたのがcontainsという関数です。

ひょっとして、これなら！！

collate-jp-ci-with-contains

>select * from test_tables.test_20220625 where contains(name, collate('あいうえお', 'ja_JP-ci'));
+----+------------+
| ID | NAME       |
|----+------------|
|  1 | あいうえお |
|  2 | アイウエオ |
|  3 | アイうえお |
|  4 | アイウえお |
|  5 | アいウえオ |
+----+------------+

YATTA!!

ちなみにひらがなカタカナの他に全角半角も吸収してくれました。

collate-jp-ci-with-contains-2

select * from test_tables.test_20220625 where contains(name, collate('ｱｲｳｴｵ', 'ja_JP-ci'));
+----+------------+
| ID | NAME       |
|----+------------|
|  1 | あいうえお |
|  2 | アイウエオ |
|  3 | アイうえお |
|  4 | アイウえお |
|  5 | アいウえオ |
+----+------------+
5 Row(s) produced. Time Elapsed: 2.313s

La victoire est à moi!

そういうわけで、わかったこととしては、
collateとcontainsを使いこなして、快適に日本語を検索しよう！！

おまけ

その後さらなる追加調査を行ったところ、ja_JP-ciだと伸ばし棒がうまく区別できないがunicode-ciだとうまくいくという話も出てきました。
ja_JP-ciで意図した挙動が得られなかった場合にはunicode-ciも是非試してみてください。

結びの言葉

文字コードは難しい分野の1つなので、こういう機会に理解を深めるのは非常に重要です。

検索が快適だと分析業務も捗りますので、今回の話の他にもTipsがあれば是非共有していきたいですね。

さらに詳細が聞きたいという方は、私がわかる範囲であればお答えしますので、Twitterあたりでお気軽にお声がけください。
@kommy_jp

なお、株式会社GENDAでは、一緒に働くデータ関連の人材を絶賛募集中です。
興味がある方は是非お声がけください。
https://genda.jp/

本日はこのあたりで。
それじゃあ、バイバイ！

前置き