Frosty Friday Week5 Basic UDFs
こんばんは。m.fujitaです。
今日はWeek5を解いてみます。
Frosty Fridayとは
Snowflakeユーザの
Snowflakeユーザによる
Snowflakeのための
Snowflakeのスキルアップウィークリーチャレンジ です。
Frosty Fridayは、Snowflakeユーザーによって作成された、Snowflakeのスキル向上を目的とした週次チャレンジシリーズです。2022年7月14日に開始され、毎週金曜日に新しい課題が公開されています。
各チャレンジは、Snowflakeの特定の機能に焦点を当てており、タイムトラベルやパイプ、データ変換、ストリームなど、多岐にわたります。課題は「基本」「中級」「上級」の3つのレベルに分類されており、主にSQLを使用しますが、希望に応じてJavaやPythonを使用するオプションも提供されています。
Frosty Friday Live Challenge Vol 2
解説のYoutubeはこちらにあります。
Week5の問題
英語で書かれているのですが、日本語で翻訳して転記します。
今週は、この記事の執筆時点(2022-07-15)では最新の機能である「Snowflake の Python」を使用します。
まず、数字が入った1列のシンプルな表を作成します。サイズと量は自由に決められます。
その後、非常に基本的な関数、つまりそれらの数値を 3 倍にする関数から始めます。
ここでの課題は、「非常に難しい Python 関数を構築する」ことではなく、Snowflake で関数を構築して使用することです。
簡単な選択ステートメントを使用してコードをテストできます。
SELECT timesthree(start_int)
FROM FF_week_5
UDFとは
まず、UDFがよく分からなかったのでChatGPTに聞いてみました。
UDFとは、「User-Defined Function（ユーザー定義関数）」の略です。プログラミングやデータ処理において、ユーザーが独自に定義した関数のことを指します。
PythonやJavaなどで、自作の関数を定義して使う場合もUDFと呼ばれることがあります。
def custom_sum(a, b):
return a + b
print(custom_sum(3, 5)) # 出力: 8
なるほど、関数ってことですね。共通処理があったときに別の場所から1か所に呼び出すときとか、ロジックの可読性を上げるときに役に立つものと理解しています。
さて解いてみよう
まずはここまで自分で考えながらやってみました。
Table句使ってみた過ぎて使ってみた。めっちゃ楽しいです。
でも、結果を3倍にしなきゃいけないのにすごい大きいor小さい数値が出ちゃっています💦
CREATE or replace TABLE FROSTY_FRIDAY.FROSTY.ff_week5_raw (
RANDOM_NUMBER NUMBER -- ランダムな数値
);
table FROSTY_FRIDAY.FROSTY.ff_week5_raw ;
-- 100行のデータを挿入
INSERT INTO FROSTY_FRIDAY.FROSTY.ff_week5_raw (RANDOM_NUMBER)
SELECT
RANDOM() * 100 AS RANDOM_NUMBER
FROM TABLE(GENERATOR(ROWCOUNT => 100)) AS T;
table FROSTY_FRIDAY.FROSTY.ff_week5_raw ;
動画視聴開始！
がくさんの解法を視聴してみました。
どうやら間違っていたのは1行だけだったようです。
-- 100行のデータを挿入
INSERT INTO FROSTY_FRIDAY.FROSTY.ff_week5_raw (RANDOM_NUMBER)
SELECT
-- RANDOM() * 100 AS RANDOM_NUMBER
uniform(0,1000,random()) as RANDOM_NUMBER
FROM TABLE(GENERATOR(ROWCOUNT => 100)) AS T;UNIFORM？!
UNIFORM は、Snowflake のランダム値生成に使われる関数で、特定の範囲内で一様分布に基づくランダムな値を生成するものです。これにより、指定した範囲の中で等確率でランダム値を生成できます。
テストデータを作るときに生成する関数ってことですね。
ChatGPTさんに聞いたこの解説がすごく分かりやすかったです。
そして↓のサンプルコードがほぼそのまんま。
動画内ではGENERATOR関数がテストデータを作る際にとても便利ですとのことだったので、UNIFORM関数とセットで使われるのかを聞いてみました。
UNIFORM 関数は必ずしも GENERATOR 関数とセットで使う必要はありませんが、特にテストデータを大量に生成する場合には GENERATOR と組み合わせるのが便利です。GENERATOR 関数は指定した行数を効率的に生成するために使われ、UNIFORM はその行ごとにランダムな値を生成する際に役立ちます。
GENERATOR関数の横の=>
不等号だと思っていたのです。100行作りたいと言っているのに=>って100行以上データができてしまうのでは？と疑問に思ったのです。
GENERATOR(ROWCOUNT => 100) の => は、不等号ではなく パラメータとその値を関連付ける記号 です。これは、Snowflakeの構文仕様で使われる書き方で、不等号の意味とはまったく異なります。
=> の意味パラメータとその値をセットで指定するための記号 です。
右側に設定した値がパラメータに割り当てられることを示します。
例:
ROWCOUNT => 100 は「ROWCOUNTパラメータに100を設定する」という意味。
「100行以内」や「不等号」の意味ではありません。
なぜ => を使うのか？
Snowflakeでは、一部の関数や構文でキーバリュー形式（key => value）のペアを使います。これにより、関数に複数のオプションやパラメータを渡すことが簡単になります。
パラメータだったのか！初めて知りました・・・！
これは後々よく使いそうですね！覚えられないので後で自分のnoteを調べに来ますw
途中から動かなくなった
PythonでFunctionを作ってみました。
あれあれ？その後から動かなくなりました。
どうやら、途中で実行しているロールが変更されてしまったようです。
イチからやり直すことにしました。
use schema frosty_friday.frosty;
use role sysadmin;
CREATE or replace TABLE FROSTY_FRIDAY.FROSTY.ff_week5_raw (
RANDOM_NUMBER NUMBER -- ランダムな数値
);
table FROSTY_FRIDAY.FROSTY.ff_week5_raw ;
truncate table FROSTY_FRIDAY.FROSTY.ff_week5_raw ;
-- 100行のデータを挿入
INSERT INTO FROSTY_FRIDAY.FROSTY.ff_week5_raw (RANDOM_NUMBER)
SELECT
-- RANDOM() * 100 AS RANDOM_NUMBER
uniform(0,1000,random()) as RANDOM_NUMBER
FROM TABLE(GENERATOR(ROWCOUNT => 100)) AS T;
table FROSTY_FRIDAY.FROSTY.ff_week5_raw ;
--Pythonで関数を作って3倍の値を入れてみる
create or replace function FROSTY_FRIDAY.FROSTY.timesthree(i int)
returns int
language python
runtime_version = '3.11'
handler = 'timesthree_py'
as
$$
def timesthree_py(i):
return i*3
$$
;
table FROSTY_FRIDAY.FROSTY.ff_week5_raw ;
select RANDOM_NUMBER,
FROSTY_FRIDAY.FROSTY.timesthree(RANDOM_NUMBER) as RANDOM_NUMBER_x3
from FROSTY_FRIDAY.FROSTY.ff_week5_raw
order by RANDOM_NUMBER;
できたーーー！（歓喜）
Functionを作るときの$$はなにか？
Snowflake で Python 関数や SQL 関数を作成する際に、関数本体を囲むために $$ を使う理由は、関数の定義を文字列として扱うためです。この記法は、Snowflake の SQL スクリプトで特定のテキストブロックを囲むためのデリミタ(＝文字列やコードの区切り を示すための特定の文字や記号のこと)として使用されます。
最終的なコード
use schema frosty_friday.frosty;
use role sysadmin;
CREATE or replace TABLE FROSTY_FRIDAY.FROSTY.ff_week5_raw (
RANDOM_NUMBER NUMBER -- ランダムな数値
);
table FROSTY_FRIDAY.FROSTY.ff_week5_raw ;
truncate table FROSTY_FRIDAY.FROSTY.ff_week5_raw ;
-- 100行のデータを挿入
INSERT INTO FROSTY_FRIDAY.FROSTY.ff_week5_raw (RANDOM_NUMBER)
SELECT
-- RANDOM() * 100 AS RANDOM_NUMBER
uniform(0,1000,random()) as RANDOM_NUMBER
FROM TABLE(GENERATOR(ROWCOUNT => 100)) AS T;
table FROSTY_FRIDAY.FROSTY.ff_week5_raw ;
//==========================================================================
--Pythonで関数を作って3倍の値を入れてみる
create or replace function FROSTY_FRIDAY.FROSTY.timesthree(i int)
returns int
language python
runtime_version = '3.11'
handler = 'timesthree_py'
as
$$
def timesthree_py(i):
return i*3
$$
;
table FROSTY_FRIDAY.FROSTY.ff_week5_raw ;
--3倍の値は、FROSTY_FRIDAY.FROSTY.timesthree(i)から関数に飛ばすことでうまく計算される
select RANDOM_NUMBER,
FROSTY_FRIDAY.FROSTY.timesthree(RANDOM_NUMBER) as RANDOM_NUMBER_x3
from FROSTY_FRIDAY.FROSTY.ff_week5_raw
order by RANDOM_NUMBER;
//==========================================================================
--SQLで関数を作ってみる
create or replace function FROSTY_FRIDAY.FROSTY.SQL_timesthree(i int)
returns int
language sql
as
$$
i*3
$$
;
--内容を確認
--3倍の値は、FROSTY_FRIDAY.FROSTY.timesthree(i)から関数に飛ばすことでうまく計算される
select RANDOM_NUMBER,
FROSTY_FRIDAY.FROSTY.SQL_timesthree(RANDOM_NUMBER) as RANDOM_NUMBER_SQLx3
from FROSTY_FRIDAY.FROSTY.ff_week5_raw
order by RANDOM_NUMBER;
番外編
さっそくWeek1やWeek3で学んだステージの内容を取得する方法が業務にも役に立ちました。学んだことが仕事に役に立つとさらにモチベーションがあがりますね✨
#FrostyFriday で学んだことが早速仕事で役に立ちました！
— m.fujita (@mfujita2023) January 10, 2025
まとめ
問題文とフィールド名が異なってしまったのはご愛敬で。
目的となるFunctionの作成ができました。
・UNIFORM
・GENERATOR
・Function(python)
・Function(SQL)
の使い方を学びました。
Basicなら長く続けられそう！3回続きました。
Snowflakeを自分で勉強しようとしたら思考停止していた2024年の自分からするとすごい成長です。
引き続き発信していくので、皆様よろしくお願いいたします。