SnowflakeのスカラーSQL UDFsを試してみた

こんにちは！DA(データアナリティクス)事業本部 サービスソリューション部の大高です。

Snowflakeでは、UDFs(ユーザー定義関数)として「スカラー SQL UDFs」が利用できます。

これを利用すると「任意のSQLを実行して、スカラー値（単一の値）を返却する関数」を定義することができます。

一般的な機能ですが試したことがなかったので、以下のドキュメントを参考に実際に作成してました。

「円周の長さ」を求める関数

まずは、簡単な「円周の長さ」を求める関数を作成してみます。

「円周の長さ」は「直径 x 円周率」なので、「直径を引数として円周の長さを返す関数」を作成してみます。Snowflakeの関数として円周率を返すPI()があるので、これを利用して定義しましょう。

-- FUNCTION作成
CREATE OR REPLACE FUNCTION get_circum_len(diameter FLOAT)
RETURNS FLOAT
AS
$$
  diameter * pi()
$$
;

-- 取得テスト
SELECT get_circum_len(3.0);

╒═════════════════════╕                                                         
│ GET_CIRCUM_LEN(3.0) │
╞═════════════════════╡
│         9.424777961 │
╘═════════════════════╛

良い感じですね。このパターンのSQLの記述としては、単純に返却したいSQL式を定義すれば良いようです。

あるテーブルからスカラー値を返却する関数

次は、あるテーブルからスカラー値を返却する関数を作成してみます。

よくある「マスターデータが登録されているテーブル」から、キーカラムを指定して該当レコードのスカラー値を返却する関数を定義してみます。

まず、単純にテーブルから1レコードを取得するSQLは以下になります。

-- 指定レコードのSELECT
SELECT
  n_nationkey,
  n_name,
  n_regionkey,
  n_comment
FROM
  snowflake_sample_data.tpch_sf1.nation
WHERE
  n_nationkey = 12
;

╒═════════════╤════════╤═════════════╤══════════════════════════════════════╕   
│ N_NATIONKEY │ N_NAME │ N_REGIONKEY │ N_COMMENT                            │
╞═════════════╪════════╪═════════════╪══════════════════════════════════════╡
│          12 │ JAPAN  │           2 │ ously. final, express gifts cajole a │
╘═════════════╧════════╧═════════════╧══════════════════════════════════════╛

ここから、スカラー値になる「名称」のn_nameだけを取得するクエリにしてみます。

-- スカラー値のSELECT
SELECT
  n_name
FROM
  snowflake_sample_data.tpch_sf1.nation
WHERE
  n_nationkey = 12
;

╒════════╕                                                                      
│ N_NAME │
╞════════╡
│ JAPAN  │
╘════════╛

想定どおり、スカラー値が取得できました。これを関数として定義します。

-- FUNCTION作成
CREATE OR REPLACE FUNCTION get_nation_name(nationkey NUMBER)
RETURNS varchar()
AS
$$
  SELECT
    n_name
  FROM
    snowflake_sample_data.tpch_sf1.nation
  WHERE
    n_nationkey = nationkey
$$
;

-- 取得テスト
SELECT get_nation_name(12);

╒═════════════════════╕                                                         
│ GET_NATION_NAME(12) │
╞═════════════════════╡
│ JAPAN               │
╘═════════════════════╛

想定どおり、指定したコードでスカラー値が取得できました！

テーブルをJOINしてスカラー値を返却する関数

今度は単一のテーブルではなく、2つのテーブルをJOINして試してみます。nationテーブルに紐付いているregionテーブルからスカラー値を取得してみます。

まずはSELECT文からです。

-- スカラー値のSELECT
SELECT
  r_name
FROM
  snowflake_sample_data.tpch_sf1.nation
INNER JOIN snowflake_sample_data.tpch_sf1.region ON
  n_regionkey = r_regionkey
WHERE
  n_nationkey = 12
;

╒════════╕                                                                      
│ R_NAME │
╞════════╡
│ ASIA   │
╘════════╛

想定どおりスカラー値が取得できているので、これを関数として定義します。

-- FUNCTION作成
CREATE OR REPLACE FUNCTION get_region_name(nationkey NUMBER)
RETURNS varchar()
AS
$$
  SELECT
    r_name
  FROM
    snowflake_sample_data.tpch_sf1.nation
  INNER JOIN snowflake_sample_data.tpch_sf1.region ON
    n_regionkey = r_regionkey
  WHERE
    n_nationkey = nationkey
$$
;

-- 取得テスト
SELECT get_region_name(12);

╒═════════════════════╕                                                         
│ GET_REGION_NAME(12) │
╞═════════════════════╡
│ ASIA                │
╘═════════════════════╛

このパターンも特に問題ありませんね。

なお、今回はスカラー値が返却されることが分かっていたので問題なかったのですが、複数の値が返却されるとどうなるかも試してみました。

090150 (22000): Single-row subquery returns more than one row. 

結果としては、上記のようなエラーが返却されて、ちゃんと問題があることが認識できました。

おまけ

今回は試しませんでしたが、Snowflakeには「表形式 SQL UDFs （UDTFs）」というUDFもあります。

Snowflakeには「テーブル関数」という関数があり、以下のようなクエリが記述できます。

SELECT
  foo
FROM
  TABLE(bar)
;

このテーブル関数の引数にUDFを指定して利用するようなことができます。テーブル関数便利ですね！

まとめ

以上、Snowflakeのスカラー SQL UDFsを試してみました。

個人的に、UDFは共通処理をシンプルにまとめられることができるので便利だと思っています。その一方で、使い方を誤ると実行速度の低下にもつながると考えているので、用途に応じてうまく使っていきたいなと思いました。

どなたかのお役に立てば幸いです。それでは！