Zenn
ちゅらデータ株式会社Publicationへの投稿
❄️

Frosty Friday Week121 Basic Snowflake Functions

Gaku Tashiro＠ちゅらデータエンジニア
2024/12/09に公開
snowpark
frostyfriday
tech

こんにちは！　がく＠ちゅらデータエンジニアです。

https://qiita.com/advent-calendar/2024/frostyfriday

こちらの9日目の記事になります。

Frosty Friday Week121 Basic Snowflake Functions

https://frostyfriday.org/blog/2024/11/29/week-121/

start up code

create or replace schema week121;
CREATE OR REPLACE TABLE DucksAndGeese (
id INT PRIMARY KEY,
column1 VARCHAR(10),
column2 VARCHAR(10),
column3 VARCHAR(10),
column4 VARCHAR(10),
column5 VARCHAR(10),
column6 VARCHAR(10),
column7 VARCHAR(10),
column8 VARCHAR(10),
column9 VARCHAR(10),
column10 VARCHAR(10),
column11 VARCHAR(10),
column12 VARCHAR(10),
column13 VARCHAR(10),
column14 VARCHAR(10),
column15 VARCHAR(10),
column16 VARCHAR(10),
column17 VARCHAR(10),
column18 VARCHAR(10),
column19 VARCHAR(10),
column20 VARCHAR(10),
column21 VARCHAR(10),
column22 VARCHAR(10),
column23 VARCHAR(10),
column24 VARCHAR(10),
column25 VARCHAR(10),
column26 VARCHAR(10),
column27 VARCHAR(10),
column28 VARCHAR(10),
column29 VARCHAR(10),
column30 VARCHAR(10),
column31 VARCHAR(10),
column32 VARCHAR(10),
column33 VARCHAR(10),
column34 VARCHAR(10),
column35 VARCHAR(10),
column36 VARCHAR(10),
column37 VARCHAR(10),
column38 VARCHAR(10),
column39 VARCHAR(10),
column40 VARCHAR(10),
column41 VARCHAR(10),
column42 VARCHAR(10),
column43 VARCHAR(10),
column44 VARCHAR(10),
column45 VARCHAR(10),
column46 VARCHAR(10),
column47 VARCHAR(10),
column48 VARCHAR(10),
column49 VARCHAR(10),
column50 VARCHAR(10),
column51 VARCHAR(10),
column52 VARCHAR(10),
column53 VARCHAR(10),
column54 VARCHAR(10),
column55 VARCHAR(10),
column56 VARCHAR(10),
column57 VARCHAR(10),
column58 VARCHAR(10),
column59 VARCHAR(10),
column60 VARCHAR(10),
column61 VARCHAR(10),
column62 VARCHAR(10),
column63 VARCHAR(10),
column64 VARCHAR(10),
column65 VARCHAR(10),
column66 VARCHAR(10),
column67 VARCHAR(10),
column68 VARCHAR(10),
column69 VARCHAR(10),
column70 VARCHAR(10),
column71 VARCHAR(10),
column72 VARCHAR(10),
column73 VARCHAR(10),
column74 VARCHAR(10),
column75 VARCHAR(10),
column76 VARCHAR(10),
column77 VARCHAR(10),
column78 VARCHAR(10),
column79 VARCHAR(10),
column80 VARCHAR(10),
column81 VARCHAR(10),
column82 VARCHAR(10),
column83 VARCHAR(10),
column84 VARCHAR(10),
column85 VARCHAR(10),
column86 VARCHAR(10),
column87 VARCHAR(10),
column88 VARCHAR(10),
column89 VARCHAR(10),
column90 VARCHAR(10),
column91 VARCHAR(10),
column92 VARCHAR(10),
column93 VARCHAR(10),
column94 VARCHAR(10),
column95 VARCHAR(10),
column96 VARCHAR(10),
column97 VARCHAR(10),
column98 VARCHAR(10),
column99 VARCHAR(10),
column100 VARCHAR(10)
);
INSERT INTO DucksAndGeese VALUES
(1, 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck','Duck','Duck','Duck', 'Duck'),
(2, 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck','Duck','Duck','Duck', 'Duck'),
(3, 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck','Duck','Duck','Duck', 'Duck'),
(4, 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck','Duck','Duck','Duck', 'Duck'),
(5, 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck','Duck','Duck','Duck', 'Duck'),
(6, 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck','Duck','Duck','Duck', 'Duck'),
(7, 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck','Duck','Duck','Duck', 'Duck'),
(8, 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Goose', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck','Duck','Duck','Duck', 'Duck'),
(9, 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck','Duck','Duck','Duck', 'Duck'),
(10, 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck', 'Duck','Duck','Duck','Duck', 'Duck');

Week121のチャレンジ内容

チャレンジの内容は

昔の子供のゲーム「あひるのこ遊び」をしましょう。

テーブルが広すぎてナビゲートしにくいことは、多くの人が知っています。これらのテーブルにエラーがある場合、そのエラーが実際にどこにあるかを見つけるのは非常に困難です。
しかし、Snowflake が新しい関数を使用して簡単に見つけられるようになっていました。

このチャレンジを開始するには、次のデータを作成します。

今週の課題は、ガチョウを隠しているコード行を見つけることです。
楽しんで見つけてください。そして、忘れずにコードを投稿してください 🙂

ヒントとなるのは
「しかし、Snowflake が新しい関数を使用して簡単に見つけられるようになっていました。」
です。

が、思い当たるところがあまりない・・・・
テーブルの構成としては
ID, COLUMN1, COLUMN2......
となり、COLUMNの値が、Duckだけか、Gooseが入ってしまっているか

カラムの同一性を見るならば、Hash関数を使うといいかなと思いました。

まずテーブルの中身を確認します。

select * from DucksAndGeese;

Duckだらけ！！！

まず、hashを取ってみます

select id , hash(*) as hashed from DucksAndGeese;

IDが中にはいるので、HASHの値（HASH(*)）が、バラバラですね。

select id , hash(* exclude id) as hashed from DucksAndGeese;

2024年8月（8.30）のリリースで、functionやstored procerureの引数でも、* exlude col1や* ilike 'pattern'が使えるようになりました。

https://docs.snowflake.com/en/release-notes/2024/8_30#wildcard-filtering-for-functions

解法① hash(* exclude id）

select id , hash(* exclude id) as hashed from DucksAndGeese;

ID=8が、4291830866937842519となっており、ここにGooseが潜んでるであろうことがわかります。

ちょっと整形して

with hashed as (
    select 
        id
        , hash(* exclude id) as hashed
    from DucksAndGeese
)
select hashed, array_agg(id) as ids from hashed group by hashed
;


今回は、8のみがほかと違う＝Gooseが紛れ込んでる事がわかります。

この整形を行うと、

  • もっとレコード数が多い（例えば、1万）
  • Gooseの潜んでるところが違ったり、2羽紛れてたりしても視認性が高そう

解法② hash(* ilike 'column%')

select id , hash(* ilike 'column%') as hashed from DucksAndGeese;

こちらでも同様の結果になりました

おまけ

id=8にGooseが紛れてるのがわかりましたが、どのカラムに潜んでるのか・・・
調べてみないか試してみましょう。

colum1, colum2,,,,,,colum100まであり、普通にunpivotを使うと、これらを列挙せねばならずなかなかつらい・・・

ということで、以前のチャレンジで使った動的Unpivotのテクニックを使ってみましょう

https://zenn.dev/churadata/articles/8516e4931514f6

-- id=8にGooseが紛れ込んでいる
with hoge as (
    select * from DucksAndGeese where id = 8
)
-- {*}記法を使って、OBJECT_CONSTRUCT＝JSONに変換します
,geho as (
    select {* exclude id} as line from hoge
)
-- 動的Unpivot
, unpivoted as (
select 
    f.key
    , f.value 
from 
    geho q
    , lateral flatten(q.line) f
)
select * from unpivoted
where value != 'Duck'
;

86番目のカラムに、Gooseが潜んでいることがわかりました。

まとめ

ワイルドカードへのフィルタリングは、以前はSELECTにしか使えなかったのですが、FUNCTIONやPROCEDUREでも使えるようになっていたので、多分それを使うのかなーーと思いました。

また、動的UNPIVOTのテクニックは、

https://zenn.dev/indigo13love/articles/971cdb3b893590

で、Snowflakeの中のYoshiさんのを参考にさせてもらっています。

解法は、こちらのレポジトリで公開しています。

https://github.com/gakut12/Frosty-Friday/tree/main/week121_basic_snowflake_functions

ちゅらデータ株式会社
ちゅらデータ株式会社Publication

沖縄に「最高に面白い仕事」を作る IT ベンチャーです！ エンジニア積極採用中！！

Discussion