☃️
Frosty Fridayに挑戦したよ #week74

2025/10/23に公開
 Frosty Friday Live ChallengeSnowflakeに関するアップデート情報にまつわるディスカッションや、Frosty Fridayの問題にチャレンジしたりする配信番組です。

隔週更新となっています。

https://youtu.be/-zJvHVK7hNs
今回はがくさんにお誘い頂き、初参加となりました。

ご一緒したメンバーはtomoさん、がくさん、あれさん、Taroさんです！

 Frosty Friday week 74の解説今回の課題は「様々なフォーマットが混じってしまった日付列をDATE型に直そう！」です。

https://frostyfriday.org/blog/2023/12/01/week-74-intermediate/comment-page-1/
データ分析をしよう！さぁテーブルをクエリするぞ！
...
...あれ？？？
日付の絞り込みが効かないぞ...
って、ええええぇぇぇぇぇ！この列、DATE型じゃなくてVARCHAR型じゃん！！
といった経験はあるのではないでしょうか？

データを見ると、こんな感じです。

これではフィルターが効かないのも当然です。

今回の問題は、このVARCHARになってしまっている日付列をDATE型に変換しようという問題です。

 まずは正攻法でやるSQLのcoaleasce関数とtry_to_date関数を使って、順番にフォーマットを適用することで解決します。
select
    BIRTHDATE
    ,coalesce(
        try_to_date(BIRTHDATE,'dd/mm/yyyy') --> cast出来ない場合はnullになる
        ,try_to_date(BIRTHDATE,'yyyy-mm-dd')
    ) as BIRTHDATE_CONVERTED
from week_74_1;
try_to_date関数は指定のフォーマットで変換できない場合はnullが返ってきます。

coaleasceと組み合わせることで、複数のフォーマットを照らし合わせて変換するということができます。

できました！

これでデータ分析に取り掛かることができます。

 バベルの塔ところで、世界には沢山の言語が存在するのと同時に、日付の表記についても沢山存在しています。
日本ではyyyy/mm/ddという表記が一般的ではないでしょうか？

しかし、同時にこんな表記もあります。
H9.2.11
S50.3.24
また、先程扱ったdd/mm/yyyyという表記はヨーロッパでよく使われている表記です。

一方で、アメリカではmm/dd/yyyyという表記が使われています。
そうです。日付の世界はカオスです。

こんなテーブルがあったとしましょう。



08/09/1968は1968年9月8日でしょうか？1968年8月9日でしょうか？
さすがにこのノーヒントの状態では正しくDATE型に変換することはできなさそうです。

そこでこんなテーブルを想像してみます。



BIRTHDATE列の隣に住んでいる都市を表すCITYという列があります。これをヒントに日付の変換をしてみましょう。

神は乗り越えられる試練しか与えない、、、はず！

 文明は人間のものずばり、書いたクエリはこれです。
select
        *
        ,AI_COMPLETE(
            'claude-4-sonnet',
            CONCAT('<location>', city ,'</location>でよく使われている日付フォーマットで、<date>', birthdate ,'</date>をYYYY-MM-DD形式に変換してください。出力は変換後の日付のみにしてください。')
        ) as formatted_date
from week_74_2;
AI_COMPLETE関数はLLMを使って画像やテキストなどの非構造化データに対してクエリが可能になる関数です。二つ目の引数にLLMに渡すプロンプトを入力します。このプロンプトにはテーブルの列を渡すことができるようになっており、上記のようにconcat関数を使うことで、柔軟なプロンプトを書けます。
さて、結果はこんな感じになりました。

Johnはアメリカのユタ州に在住です。ということは08/09/1968は1968年8月9日の可能性が高いです。
Georgeはドイツのゲッティンゲンという街に在住です。ということは03/08/1985は1985年8月3日の可能性が高いです。
Connieはオーストラリア、シドニーに在住です。オーストラリアはイギリスの影響を受けた国です。習慣としてヨーロッパ式のdd/mm/yyyyという表記が一般的なようです。
日本人データエンジニアを苦しめてきた和暦ですが、平成9年は1997年です。

これもちゃんとAI_COMPLETE関数で処理できていました。

 AI SQLと上手く付き合おうSnowflakeのAI SQLはとても面白い機能ですが、自身の業務で使うとなると、どこか一歩引いて「AI SQLはまだウチの業務で使う感じじゃないな」となっていませんか？

白状すると、私も今回Frosty Fridayのチャレンジで使用するまではそう思っていました。
しかし今回のような「都市名から日付のフォーマットを推定して、、、」といったファジーなロジックを作りたい（作らないといけない）場合に、小回りが利いてとても便利だなと感じました。

日付の変換だけではなく、データクレンジングの一歩目としてAI_COMPLETEを使っていこうと思います。
ちなみに、AI_COMLETE関数の第一引数はLLMのモデル名です。

デモではclaude-4-sonnetを使いましたが、試しに他モデルも試したところ上手く処理できないものもありました。

例えば次の画像はsnowflake-llama-3.1-405bを使用した結果です。

一見上手くいっているようにも見えますが、ゲッティンゲンやシドニーもアメリカ式で処理されてしまっていますし、和暦は見当違いな返答になってしまっています。
続いてはsnowflake-arcticです。

こちらはプロンプト通りの出力をしてくれませんでした。

LLMによって性能の良し悪し、得意不得意があると思いますので、いくつか試してみて最適なものを選ぶ必要がありそうです。

 感想今回Frosty Friday Live Challengeに初めて参加させて頂きました。

チャレンジそのものは比較的簡単な課題でしたが、改めてSnowflakeの機能について調べる良い機会になりました。

トピックスのコーナーでも、気になっていたTaroさんの記事について、ご本人の解説を聞くことができ、楽しい収録でした。

また出たいな～～
皆さんも気になるチャレンジがあればぜひ参加してみてください！