8
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

Day 13

KDDIアジャイル開発センター（KAG） Advent Calendar 2025

@fkdfkdfkd(FKD)inKDDIアジャイル開発センター株式会社

Snowflake UDFの言語別パフォーマンスを計測してみた

8
Last updated at Posted at 2025-12-14

はじめに

Snowflakeで UDF（ユーザー定義関数）を実装する際、SQL、JavaScript、Python のどれを選ぶべきか、迷ったことはありませんか？

私自身、データパイプライン構築の中で「このロジックはUDFにしたいけど、言語は何が最適なんだろう？」と悩むことがしばしばありました。

対DWHを考えると、ネイティブなSQLで書けるならそれがいい、ちょっと複雑なロジックになるならJavaScriptが書きやすそう、豊富なライブラリを活用してサクッと処理を書きたいならPythonみたいなざっくりイメージ。

業務的には、その時そのタイミングでのタスクに応じた実装においては、特にどれでも良さそうな感じがします。
しかし、時が立って大量データを相手にした時のパフォーマンスに跳ねてきたり、改めてリファクタや機能追加修正等にあわせて修正しようとした時のメンテナンス性・拡張性に跳ねてきます。

というわけで、今回は Snowflake UDF で代表的な言語(SQL,JavaScript,Python)別パフォーマンスをベンチマーク検証してみました。

ポイント

この記事のポイントは以下の通りです：

  • Snowflakeの組み込み関数と、JavaScript/Python UDFのオーバーヘッドやパフォーマンスを比較
  • XS、S（SMALL）、Gen2 XS、Gen2 S の4つのウェアハウスパターンで検証
  • 演算が重めの処理（MD5ハッシュ化、Base64エンコード、SHA256ハッシュ化）で検証
  • ストアドプロシージャで自動実行し、結果をテーブルに格納して分析
  • テストデータ生成から計測まで、すべてSQLで完結 -> ハンズオン的にすぐお試しできます！
  • 実測データをもとに、実務での使い分けポイントなどを考察

検証環境

今回の検証環境は以下の通りです：

  • Snowflakeアカウント: 任意（トライアルアカウントでもOK）
  • テストデータ件数: 100万件
  • 実行回数: 各パターン3回実行して平均を取得
  • 計測日時: 2025年12月時点

SnowflakeはSaaSのため、ウェアハウスなどのパフォーマンス改善が裏で行われて、いつの間にか処理速度が上がっていたりボトルネックが解消されているケースがあります。
そのため、その時々での測り方などの参考にしていただけると幸いです。

ウェアハウスパターン

今回は以下の4つのウェアハウスパターンで検証します：

  1. X-Small (XS) - Snowflakeの最小サイズ、基本はここから始めるのが鉄則
  2. Small (S) - XSから1段階スケールアップ、性能向上とコストのバランスを検証
  3. Gen2 Warehouse (XS) - 新世代ウェアハウス、クエリの負荷に応じてリソースを動的に調整（XSサイズベース）
  4. Gen2 Warehouse (S) - Gen2のSMALLサイズベース版、通常のSとの比較用

ちなみに、XSでも十分に言語間の性能差は見えるはずです。
むしろXSの方が差が顕著に出る可能性もあります。

Gen2 Warehouseは特に注目です。クエリの負荷に応じてリソースを動的に調整することで、Gen1で大きなサイズを固定で使い続けるよりコスト効率が良くなる可能性があります。
この「リソース最適化によるコスト削減効果」を検証するのも今回の目的の一つです。
また、通常のウェアハウスとGen2の違いがサイズによってどう変わるかも比較します。

セットアップ

この記事の検証は、Snowflakeアカウントを持っていれば誰でも実行できるように設計しています。以下の手順で環境をセットアップしましょう。

前提条件

  • Snowflakeアカウント（トライアルアカウントでもOK）
  • SYSADMIN ロール、または同等の権限を持つロール

1. ロールとウェアハウスの設定

まず、SYSADMIN ロールを使用し、セットアップ作業用のウェアハウスを準備します。

SQLワークシート (最新だとワークスペース内での新規SQLファイル)で以下を実行します。

-- SYSADMINロールを使用（データベース、スキーマ、ウェアハウス作成権限が必要）
USE ROLE SYSADMIN;

-- セットアップ作業用のウェアハウスを作成
CREATE WAREHOUSE IF NOT EXISTS setup_wh
    WAREHOUSE_SIZE = 'X-SMALL'
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE
    COMMENT = 'セットアップ作業用ウェアハウス';

-- 作成したウェアハウスを使用
USE WAREHOUSE setup_wh;

2. データベースとスキーマの作成

検証用のデータベースとスキーマを作成します。

-- データベースを作成
CREATE DATABASE IF NOT EXISTS udf_benchmark_db
    COMMENT = 'UDF性能検証用データベース';

-- スキーマを作成
CREATE SCHEMA IF NOT EXISTS udf_benchmark_db.benchmark_schema
    COMMENT = 'UDF性能検証用スキーマ';

-- データベースとスキーマを使用
USE DATABASE udf_benchmark_db;
USE SCHEMA benchmark_schema;

以降は udf_benchmark_db.benchmark_schema というコンテキストで作業する前提とします。

実験1

以下の3つの処理パターンで検証します。

  • MD5
  • BASE64
  • SHA256

これらを

  • SQL (Snowflake組み込み関数)
  • JavaScript
  • Python

で処理させてみてベンチマークを測ってみます。

テストデータの準備

まずはテストデータを準備します。Snowflake の GENERATOR 関数を使えば、外部データなしで大量のテストデータを生成できます。

-- テーブル作成
CREATE OR REPLACE TABLE benchmark_data (
    id NUMBER,
    text_value VARCHAR,
    numeric_value NUMBER,
    date_value DATE
);

-- 100万件のテストデータを生成
INSERT INTO benchmark_data
SELECT
    ROW_NUMBER() OVER (ORDER BY SEQ4()) AS id,
    'test_string_' || (SEQ4() % 1000)::VARCHAR || '_benchmark_data_for_hashing_and_encoding_performance_test',
    UNIFORM(1, 10000, RANDOM()),
    DATEADD(day, UNIFORM(1, 365, RANDOM()), '2024-01-01'::DATE)
FROM TABLE(GENERATOR(ROWCOUNT => 1000000));

データの確認

テストデータが正しく作成されたか確認します。

-- 件数を確認（100万件になっているはず）
SELECT COUNT(*) as row_count FROM benchmark_data;
-- 結果: row_count = 1,000,000

-- 先頭10件を確認
SELECT * FROM benchmark_data LIMIT 10;

実行結果の例：

ID TEXT_VALUE NUMERIC_VALUE DATE_VALUE
1 test_string_0_benchmark_data_for_hashing_and_encoding_performance_test 5432 2024-06-15
2 test_string_1_benchmark_data_for_hashing_and_encoding_performance_test 8791 2024-03-22
3 test_string_2_benchmark_data_for_hashing_and_encoding_performance_test 2156 2024-11-08
... ... ... ...

このように、以下の構造のデータが100万件生成されています：

  • id: 1から100万までの連番
  • text_value: "test_string_XXX_benchmark_data_for_hashing_and_encoding_performance_test" 形式の文字列（ハッシュ化・エンコード用に少し長めに）
  • numeric_value: 1〜10000のランダムな整数
  • date_value: 2024年のランダムな日付

食わせる処理次第ですが、2025年だと下手したら未来の日時エラー的なものが出かねないので、あえて去年度指定です。

データが確認できたら、次にUDFを作成します。

検証するUDF

前述通り、今回は以下の3つの処理パターンで検証します。
各処理について、Snowflakeの組み込み関数と、JavaScript/Python UDFを比較します。

1. MD5ハッシュ化

MD5アルゴリズムによるハッシュ値を計算します。暗号化演算としては比較的軽量ですが、それなりに演算が必要です。

組み込み関数（SQL / ベースライン）

-- 組み込み関数を直接使う
SELECT MD5('test');
-- 結果: 098f6bcd4621d373cade4e832627b4f6

Python UDF

Pythonの標準ライブラリ hashlib を使用します。

SQLで同関数が存在するので、あえて「MD5単品」のために作る必要はないですが、ここはベンチマークのため作っています。

Pythonのロジックの中に組み込まれることがあると思うので、PythonのMD5だけでどれぐらいかかるのか？の目安にはなるかと思います。

CREATE OR REPLACE FUNCTION udf_py_md5(input VARCHAR)
RETURNS VARCHAR
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'compute_md5'
AS
$$
import hashlib

def compute_md5(input):
    return hashlib.md5(input.encode()).hexdigest()
$$;

-- 動作確認
SELECT udf_py_md5('test');
-- 結果例: 098f6bcd4621d373cade4e832627b4f6

JavaScript UDF

JavaScriptにはMD5のネイティブ実装がないため、RFC 1321 の仕様を元に実装してみました。Snowflake JavaScript UDFでは外部ライブラリのインポートができないため、アルゴリズム全体をUDF内に埋め込む必要があります。

ゴリゴリの自前実装です...まあこれは実行するまでもなく遅そうですよね。一応、実験なのでベンチマークとして後で測ってみます。

CREATE OR REPLACE FUNCTION udf_js_md5(input VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
    // MD5実装（RFC 1321準拠）
    // Snowflake JavaScript UDFでは外部ライブラリがインポートできないため、実装を埋め込み

    function md5(string) {
        function md5cycle(x, k) {
            var a = x[0], b = x[1], c = x[2], d = x[3];
            a = ff(a, b, c, d, k[0], 7, -680876936);
            d = ff(d, a, b, c, k[1], 12, -389564586);
            c = ff(c, d, a, b, k[2], 17, 606105819);
            b = ff(b, c, d, a, k[3], 22, -1044525330);
            a = ff(a, b, c, d, k[4], 7, -176418897);
            d = ff(d, a, b, c, k[5], 12, 1200080426);
            c = ff(c, d, a, b, k[6], 17, -1473231341);
            b = ff(b, c, d, a, k[7], 22, -45705983);
            a = ff(a, b, c, d, k[8], 7, 1770035416);
            d = ff(d, a, b, c, k[9], 12, -1958414417);
            c = ff(c, d, a, b, k[10], 17, -42063);
            b = ff(b, c, d, a, k[11], 22, -1990404162);
            a = ff(a, b, c, d, k[12], 7, 1804603682);
            d = ff(d, a, b, c, k[13], 12, -40341101);
            c = ff(c, d, a, b, k[14], 17, -1502002290);
            b = ff(b, c, d, a, k[15], 22, 1236535329);
            a = gg(a, b, c, d, k[1], 5, -165796510);
            d = gg(d, a, b, c, k[6], 9, -1069501632);
            c = gg(c, d, a, b, k[11], 14, 643717713);
            b = gg(b, c, d, a, k[0], 20, -373897302);
            a = gg(a, b, c, d, k[5], 5, -701558691);
            d = gg(d, a, b, c, k[10], 9, 38016083);
            c = gg(c, d, a, b, k[15], 14, -660478335);
            b = gg(b, c, d, a, k[4], 20, -405537848);
            a = gg(a, b, c, d, k[9], 5, 568446438);
            d = gg(d, a, b, c, k[14], 9, -1019803690);
            c = gg(c, d, a, b, k[3], 14, -187363961);
            b = gg(b, c, d, a, k[8], 20, 1163531501);
            a = gg(a, b, c, d, k[13], 5, -1444681467);
            d = gg(d, a, b, c, k[2], 9, -51403784);
            c = gg(c, d, a, b, k[7], 14, 1735328473);
            b = gg(b, c, d, a, k[12], 20, -1926607734);
            a = hh(a, b, c, d, k[5], 4, -378558);
            d = hh(d, a, b, c, k[8], 11, -2022574463);
            c = hh(c, d, a, b, k[11], 16, 1839030562);
            b = hh(b, c, d, a, k[14], 23, -35309556);
            a = hh(a, b, c, d, k[1], 4, -1530992060);
            d = hh(d, a, b, c, k[4], 11, 1272893353);
            c = hh(c, d, a, b, k[7], 16, -155497632);
            b = hh(b, c, d, a, k[10], 23, -1094730640);
            a = hh(a, b, c, d, k[13], 4, 681279174);
            d = hh(d, a, b, c, k[0], 11, -358537222);
            c = hh(c, d, a, b, k[3], 16, -722521979);
            b = hh(b, c, d, a, k[6], 23, 76029189);
            a = hh(a, b, c, d, k[9], 4, -640364487);
            d = hh(d, a, b, c, k[12], 11, -421815835);
            c = hh(c, d, a, b, k[15], 16, 530742520);
            b = hh(b, c, d, a, k[2], 23, -995338651);
            a = ii(a, b, c, d, k[0], 6, -198630844);
            d = ii(d, a, b, c, k[7], 10, 1126891415);
            c = ii(c, d, a, b, k[14], 15, -1416354905);
            b = ii(b, c, d, a, k[5], 21, -57434055);
            a = ii(a, b, c, d, k[12], 6, 1700485571);
            d = ii(d, a, b, c, k[3], 10, -1894986606);
            c = ii(c, d, a, b, k[10], 15, -1051523);
            b = ii(b, c, d, a, k[1], 21, -2054922799);
            a = ii(a, b, c, d, k[8], 6, 1873313359);
            d = ii(d, a, b, c, k[15], 10, -30611744);
            c = ii(c, d, a, b, k[6], 15, -1560198380);
            b = ii(b, c, d, a, k[13], 21, 1309151649);
            a = ii(a, b, c, d, k[4], 6, -145523070);
            d = ii(d, a, b, c, k[11], 10, -1120210379);
            c = ii(c, d, a, b, k[2], 15, 718787259);
            b = ii(b, c, d, a, k[9], 21, -343485551);
            x[0] = add32(a, x[0]);
            x[1] = add32(b, x[1]);
            x[2] = add32(c, x[2]);
            x[3] = add32(d, x[3]);
        }
        function cmn(q, a, b, x, s, t) {
            a = add32(add32(a, q), add32(x, t));
            return add32((a << s) | (a >>> (32 - s)), b);
        }
        function ff(a, b, c, d, x, s, t) {
            return cmn((b & c) | ((~b) & d), a, b, x, s, t);
        }
        function gg(a, b, c, d, x, s, t) {
            return cmn((b & d) | (c & (~d)), a, b, x, s, t);
        }
        function hh(a, b, c, d, x, s, t) {
            return cmn(b ^ c ^ d, a, b, x, s, t);
        }
        function ii(a, b, c, d, x, s, t) {
            return cmn(c ^ (b | (~d)), a, b, x, s, t);
        }
        function md51(s) {
            var n = s.length, state = [1732584193, -271733879, -1732584194, 271733878], i;
            for (i = 64; i <= s.length; i += 64) {
                md5cycle(state, md5blk(s.substring(i - 64, i)));
            }
            s = s.substring(i - 64);
            var tail = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0];
            for (i = 0; i < s.length; i++)
                tail[i >> 2] |= s.charCodeAt(i) << ((i % 4) << 3);
            tail[i >> 2] |= 0x80 << ((i % 4) << 3);
            if (i > 55) {
                md5cycle(state, tail);
                for (i = 0; i < 16; i++) tail[i] = 0;
            }
            tail[14] = n * 8;
            md5cycle(state, tail);
            return state;
        }
        function md5blk(s) {
            var md5blks = [], i;
            for (i = 0; i < 64; i += 4) {
                md5blks[i >> 2] = s.charCodeAt(i) + (s.charCodeAt(i + 1) << 8) + (s.charCodeAt(i + 2) << 16) + (s.charCodeAt(i + 3) << 24);
            }
            return md5blks;
        }
        var hex_chr = '0123456789abcdef'.split('');
        function rhex(n) {
            var s = '', j = 0;
            for (; j < 4; j++)
                s += hex_chr[(n >> (j * 8 + 4)) & 0x0F] + hex_chr[(n >> (j * 8)) & 0x0F];
            return s;
        }
        function hex(x) {
            for (var i = 0; i < x.length; i++)
                x[i] = rhex(x[i]);
            return x.join('');
        }
        function add32(a, b) {
            return (a + b) & 0xFFFFFFFF;
        }
        return hex(md51(string));
    }

    return md5(INPUT);
$$;

-- 動作確認
SELECT udf_js_md5('test');
-- 結果: 098f6bcd4621d373cade4e832627b4f6（組み込み関数MD5()と同じ結果）

全実装の結果一致確認（テストデータで検証）

SELECT
    MD5(text_value) as NATIVE_MD5,
    udf_js_md5(text_value) as JS_MD5,
    udf_py_md5(text_value) as PY_MD5,
    CASE
        WHEN MD5(text_value) = udf_js_md5(text_value)
         AND MD5(text_value) = udf_py_md5(text_value)
        THEN '✓ 一致'
        ELSE '✗ 不一致'
    END as "結果検証"
FROM benchmark_data
LIMIT 10;
-- 期待結果: 全行で「✓ 一致」が表示されること

2. Base64エンコード

文字列をBase64形式にエンコードします。エンコード演算の代表例です。

組み込み関数（SQL / ベースライン）

-- 組み込み関数を直接使う
SELECT BASE64_ENCODE(TO_BINARY('test', 'UTF-8'));
-- 結果: dGVzdA==

Python UDF

Pythonの標準ライブラリ base64 を使用します。

CREATE OR REPLACE FUNCTION udf_py_base64(input VARCHAR)
RETURNS VARCHAR
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'encode_base64'
AS
$$
import base64

def encode_base64(input):
    return base64.b64encode(input.encode()).decode()
$$;

-- 動作確認
SELECT udf_py_base64('test');
-- 結果例: dGVzdA==

JavaScript UDF

JavaScriptにはBase64エンコードのネイティブ実装がないため、RFC 4648 の仕様を元に実装してみました。

こちらもゴリゴリの自前実装...実験なのでベンチマークで測ってみます。

CREATE OR REPLACE FUNCTION udf_js_base64(input VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
    // Base64エンコードの実装
    var base64Chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/';
    var result = '';
    var i = 0;

    while (i < INPUT.length) {
        var chr1 = INPUT.charCodeAt(i++);
        var chr2 = i < INPUT.length ? INPUT.charCodeAt(i++) : null;
        var chr3 = i < INPUT.length ? INPUT.charCodeAt(i++) : null;

        var enc1 = chr1 >> 2;
        var enc2 = ((chr1 & 3) << 4) | (chr2 !== null ? chr2 >> 4 : 0);
        var enc3 = chr2 !== null ? (((chr2 & 15) << 2) | (chr3 !== null ? chr3 >> 6 : 0)) : 64;
        var enc4 = chr3 !== null ? (chr3 & 63) : 64;

        result += base64Chars.charAt(enc1);
        result += base64Chars.charAt(enc2);
        result += enc3 === 64 ? '=' : base64Chars.charAt(enc3);
        result += enc4 === 64 ? '=' : base64Chars.charAt(enc4);
    }

    return result;
$$;

-- 動作確認
SELECT udf_js_base64('test');
-- 結果例: dGVzdA==

全実装の結果一致確認（テストデータで検証）

SELECT
    BASE64_ENCODE(TO_BINARY(text_value, 'UTF-8')) as NATIVE_BASE64,
    udf_js_base64(text_value) as JS_BASE64,
    udf_py_base64(text_value) as PY_BASE64,
    CASE
        WHEN BASE64_ENCODE(TO_BINARY(text_value, 'UTF-8')) = udf_js_base64(text_value)
         AND BASE64_ENCODE(TO_BINARY(text_value, 'UTF-8')) = udf_py_base64(text_value)
        THEN '✓ 一致'
        ELSE '✗ 不一致'
    END as "結果検証"
FROM benchmark_data
LIMIT 10;
-- 期待結果: 全行で「✓ 一致」が表示されること

3. SHA256ハッシュ化

SHA256アルゴリズムによるハッシュ値を計算します。MD5より演算量が多く、より重い処理です。

組み込み関数（SQL / ベースライン）

-- 組み込み関数を直接使う
SELECT SHA2('test', 256);
-- 結果: 9f86d081884c7d659a2feaa0c55ad015a3bf4f1b2b0b822cd15d6c15b0f00a08

Python UDF

Pythonの標準ライブラリ hashlib を使用します。

CREATE OR REPLACE FUNCTION udf_py_sha256(input VARCHAR)
RETURNS VARCHAR
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'compute_sha256'
AS
$$
import hashlib

def compute_sha256(input):
    return hashlib.sha256(input.encode()).hexdigest()
$$;

-- 動作確認
SELECT udf_py_sha256('test');
-- 結果例: 9f86d081884c7d659a2feaa0c55ad015a3bf4f1b2b0b822cd15d6c15b0f00a08

JavaScript UDF

JavaScriptにはSHA256のネイティブ実装がないため、RFC 6234 (US Secure Hash Algorithms) の仕様を元に実装してみました。

こちらもゴリゴリ...(以下略)。

CREATE OR REPLACE FUNCTION udf_js_sha256(input VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
    // SHA-256実装（標準アルゴリズムに準拠）
    // Snowflake JavaScript UDFでは外部ライブラリがインポートできないため、実装を埋め込み

    function sha256(str) {
        function rightRotate(value, amount) {
            return (value >>> amount) | (value << (32 - amount));
        }

        var mathPow = Math.pow;
        var maxWord = mathPow(2, 32);
        var lengthProperty = 'length';
        var i, j;
        var result = '';

        var words = [];
        var asciiBitLength = str[lengthProperty] * 8;

        // 初期ハッシュ値 (最初の8つの素数の平方根の小数部分)
        var hash = [
            0x6a09e667, 0xbb67ae85, 0x3c6ef372, 0xa54ff53a,
            0x510e527f, 0x9b05688c, 0x1f83d9ab, 0x5be0cd19
        ];

        // ラウンド定数 (最初の64個の素数の立方根の小数部分)
        var k = [
            0x428a2f98, 0x71374491, 0xb5c0fbcf, 0xe9b5dba5, 0x3956c25b, 0x59f111f1, 0x923f82a4, 0xab1c5ed5,
            0xd807aa98, 0x12835b01, 0x243185be, 0x550c7dc3, 0x72be5d74, 0x80deb1fe, 0x9bdc06a7, 0xc19bf174,
            0xe49b69c1, 0xefbe4786, 0x0fc19dc6, 0x240ca1cc, 0x2de92c6f, 0x4a7484aa, 0x5cb0a9dc, 0x76f988da,
            0x983e5152, 0xa831c66d, 0xb00327c8, 0xbf597fc7, 0xc6e00bf3, 0xd5a79147, 0x06ca6351, 0x14292967,
            0x27b70a85, 0x2e1b2138, 0x4d2c6dfc, 0x53380d13, 0x650a7354, 0x766a0abb, 0x81c2c92e, 0x92722c85,
            0xa2bfe8a1, 0xa81a664b, 0xc24b8b70, 0xc76c51a3, 0xd192e819, 0xd6990624, 0xf40e3585, 0x106aa070,
            0x19a4c116, 0x1e376c08, 0x2748774c, 0x34b0bcb5, 0x391c0cb3, 0x4ed8aa4a, 0x5b9cca4f, 0x682e6ff3,
            0x748f82ee, 0x78a5636f, 0x84c87814, 0x8cc70208, 0x90befffa, 0xa4506ceb, 0xbef9a3f7, 0xc67178f2
        ];

        // パディング処理
        str += '\x80'; // ビット1を追加
        while (str[lengthProperty] % 64 - 56) str += '\x00'; // 0で埋める

        // メッセージをワードに変換
        for (i = 0; i < str[lengthProperty]; i++) {
            j = str.charCodeAt(i);
            if (j >> 8) return; // ASCIIのみ対応
            words[i >> 2] |= j << ((3 - i) % 4) * 8;
        }
        words[words[lengthProperty]] = ((asciiBitLength / maxWord) | 0);
        words[words[lengthProperty]] = (asciiBitLength);

        // メインループ
        for (j = 0; j < words[lengthProperty];) {
            var w = words.slice(j, j += 16);
            var oldHash = hash;
            hash = hash.slice(0, 8);

            for (i = 0; i < 64; i++) {
                var w15 = w[i - 15], w2 = w[i - 2];
                var a = hash[0], e = hash[4];
                var temp1 = hash[7]
                    + (rightRotate(e, 6) ^ rightRotate(e, 11) ^ rightRotate(e, 25))
                    + ((e & hash[5]) ^ ((~e) & hash[6]))
                    + k[i]
                    + (w[i] = (i < 16) ? w[i] : (
                            w[i - 16]
                            + (rightRotate(w15, 7) ^ rightRotate(w15, 18) ^ (w15 >>> 3))
                            + w[i - 7]
                            + (rightRotate(w2, 17) ^ rightRotate(w2, 19) ^ (w2 >>> 10))
                        ) | 0
                    );
                var temp2 = (rightRotate(a, 2) ^ rightRotate(a, 13) ^ rightRotate(a, 22))
                    + ((a & hash[1]) ^ (a & hash[2]) ^ (hash[1] & hash[2]));

                hash = [(temp1 + temp2) | 0].concat(hash);
                hash[4] = (hash[4] + temp1) | 0;
            }

            for (i = 0; i < 8; i++) {
                hash[i] = (hash[i] + oldHash[i]) | 0;
            }
        }

        // ハッシュ値を16進文字列に変換
        for (i = 0; i < 8; i++) {
            for (j = 3; j + 1; j--) {
                var b = (hash[i] >> (j * 8)) & 255;
                result += ((b < 16) ? 0 : '') + b.toString(16);
            }
        }
        return result;
    }

    return sha256(INPUT);
$$;

-- 動作確認
SELECT udf_js_sha256('test');
-- 結果: 9f86d081884c7d659a2feaa0c55ad015a3bf4f1b2b0b822cd15d6c15b0f00a08（組み込み関数SHA2(,256)と同じ結果）

全実装の結果一致確認（テストデータで検証）

SELECT
    SHA2(text_value, 256) as NATIVE_SHA256,
    udf_js_sha256(text_value) as JS_SHA256,
    udf_py_sha256(text_value) as PY_SHA256,
    CASE
        WHEN SHA2(text_value, 256) = udf_js_sha256(text_value)
         AND SHA2(text_value, 256) = udf_py_sha256(text_value)
        THEN '✓ 一致'
        ELSE '✗ 不一致'
    END as "結果検証"
FROM benchmark_data
LIMIT 10;
-- 期待結果: 全行で「✓ 一致」が表示されること

ベンチマーク実行

それぞれのUDFを実行し、パフォーマンスを計測します。ストアドプロシージャで自動実行し、実行時刻と実行時間を記録して結果をテーブルに格納します。

実験用ウェアハウスの準備

ベンチマーク実行用に、4つのウェアハウスを作成します。これらは性能測定専用のウェアハウスです。

-- XS サイズのウェアハウス（実験用）
CREATE WAREHOUSE IF NOT EXISTS benchmark_xs
    WAREHOUSE_SIZE = 'X-SMALL'
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE
    COMMENT = 'ベンチマーク実験用: XS';

-- S (SMALL) サイズのウェアハウス（実験用）
CREATE WAREHOUSE IF NOT EXISTS benchmark_s
    WAREHOUSE_SIZE = 'SMALL'
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE
    COMMENT = 'ベンチマーク実験用: S';

-- Gen2 ウェアハウス（XS サイズ、実験用）
CREATE WAREHOUSE IF NOT EXISTS benchmark_gen2_xs
    WAREHOUSE_SIZE = 'X-SMALL'
    RESOURCE_CONSTRAINT = STANDARD_GEN_2  -- Gen2の指定
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE
    COMMENT = 'ベンチマーク実験用: Gen2 XS';

-- Gen2 ウェアハウス（SMALL サイズ、実験用）
CREATE WAREHOUSE IF NOT EXISTS benchmark_gen2_s
    WAREHOUSE_SIZE = 'SMALL'
    RESOURCE_CONSTRAINT = STANDARD_GEN_2  -- Gen2の指定
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE
    COMMENT = 'ベンチマーク実験用: Gen2 S';

なお、これらの実験用ウェアハウスは、セットアップ時に作成した setup_wh とは別物です。setup_wh はセットアップ作業用、benchmark_* は性能測定用という使い分けです。

結果格納用テーブルの作成

ベンチマーク結果を格納するテーブルを作成します。これにより、結果の分析が容易になります。

CREATE OR REPLACE TABLE benchmark_results (
    run_id NUMBER AUTOINCREMENT,
    warehouse_name VARCHAR,
    warehouse_size VARCHAR,
    warehouse_type VARCHAR,  -- 'standard' or 'gen2'
    process_type VARCHAR,    -- 'md5', 'base64', 'sha256', 'haversine'
    implementation VARCHAR,  -- 'builtin', 'js_udf', 'py_udf'
    run_number NUMBER,
    query_id VARCHAR,
    start_time TIMESTAMP_LTZ,
    end_time TIMESTAMP_LTZ,
    execution_time_ms NUMBER,
    created_at TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP(),
    PRIMARY KEY (run_id)
);

ベンチマーク自動実行プロシージャ

複数パターンを手動で実行するのは現実的ではないため、ストアドプロシージャで自動実行します。プロシージャは現在のウェアハウスコンテキストから CURRENT_WAREHOUSE() で情報を自動取得し、実行回数は引数で指定できます（デフォルト1回）。

CREATE OR REPLACE PROCEDURE run_benchmark(run_count NUMBER DEFAULT 1)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    process_types ARRAY := ['md5', 'base64', 'sha256'];
    implementations ARRAY := ['builtin', 'js_udf', 'py_udf'];

    wh_name VARCHAR;
    wh_size VARCHAR;
    wh_type VARCHAR;
    proc_type VARCHAR;
    impl VARCHAR;
    run_num NUMBER;
    query_id VARCHAR;
    sql_stmt VARCHAR;
    show_result RESULTSET;
    start_ts TIMESTAMP_LTZ;
    end_ts TIMESTAMP_LTZ;

    total_runs NUMBER := 0;
BEGIN
    -- 現在のウェアハウス名を取得
    wh_name := CURRENT_WAREHOUSE();

    -- ウェアハウス情報を取得
    show_result := (EXECUTE IMMEDIATE 'SHOW WAREHOUSES LIKE ''' || wh_name || '''');
    LET cur CURSOR FOR show_result;
    FOR record IN cur DO
        -- サイズを短縮形に変換（X-SMALL → XS, SMALL → S）
        wh_size := CASE
            WHEN UPPER(record."size") = 'X-SMALL' THEN 'XS'
            WHEN UPPER(record."size") = 'SMALL' THEN 'S'
            ELSE record."size"
        END;

        -- タイプを判定（RESOURCE_CONSTRAINT列から gen2 かどうか判定）
        IF (record."resource_constraint" = 'STANDARD_GEN_2') THEN
            wh_type := 'gen2';
        ELSE
            wh_type := 'standard';
        END IF;
    END FOR;

    -- 3つの処理 × 3つの実装 × run_count回実行 = 9×run_count パターン（単一ウェアハウス）
    FOR j IN 0 TO ARRAY_SIZE(process_types) - 1 DO
        proc_type := process_types[j];

        FOR k IN 0 TO ARRAY_SIZE(implementations) - 1 DO
            impl := implementations[k];

            -- 各パターンを指定回数実行
            FOR run_num IN 1 TO run_count DO
                -- ウェアハウスキャッシュをクリア（重要：キャッシュの影響を排除）
                -- クエリ結果キャッシュとウェアハウスのローカルSSDキャッシュをクリアし、
                -- 毎回フルスキャンで実行することで、純粋なUDFの性能を計測
                EXECUTE IMMEDIATE 'ALTER WAREHOUSE ' || wh_name || ' SUSPEND';
                EXECUTE IMMEDIATE 'ALTER WAREHOUSE ' || wh_name || ' RESUME';

                -- 実行するSQLを構築
                CASE
                    WHEN proc_type = 'md5' AND impl = 'builtin' THEN
                        sql_stmt := 'SELECT MD5(text_value) FROM benchmark_data';
                    WHEN proc_type = 'md5' AND impl = 'js_udf' THEN
                        sql_stmt := 'SELECT udf_js_md5(text_value) FROM benchmark_data';
                    WHEN proc_type = 'md5' AND impl = 'py_udf' THEN
                        sql_stmt := 'SELECT udf_py_md5(text_value) FROM benchmark_data';
                    WHEN proc_type = 'base64' AND impl = 'builtin' THEN
                        sql_stmt := 'SELECT BASE64_ENCODE(TO_BINARY(text_value, ''UTF-8'')) FROM benchmark_data';
                    WHEN proc_type = 'base64' AND impl = 'js_udf' THEN
                        sql_stmt := 'SELECT udf_js_base64(text_value) FROM benchmark_data';
                    WHEN proc_type = 'base64' AND impl = 'py_udf' THEN
                        sql_stmt := 'SELECT udf_py_base64(text_value) FROM benchmark_data';
                    WHEN proc_type = 'sha256' AND impl = 'builtin' THEN
                        sql_stmt := 'SELECT SHA2(text_value, 256) FROM benchmark_data';
                    WHEN proc_type = 'sha256' AND impl = 'js_udf' THEN
                        sql_stmt := 'SELECT udf_js_sha256(text_value) FROM benchmark_data';
                    WHEN proc_type = 'sha256' AND impl = 'py_udf' THEN
                        sql_stmt := 'SELECT udf_py_sha256(text_value) FROM benchmark_data';
                END CASE;

                -- SQLを実行（実行時刻を記録）
                start_ts := CURRENT_TIMESTAMP();
                EXECUTE IMMEDIATE sql_stmt;
                end_ts := CURRENT_TIMESTAMP();
                query_id := LAST_QUERY_ID();

                -- 結果をテーブルに格納（実行時刻とクエリIDを記録、詳細メトリクスは後で更新）
                EXECUTE IMMEDIATE '
                    INSERT INTO benchmark_results (
                        warehouse_name, warehouse_size, warehouse_type,
                        process_type, implementation, run_number,
                        query_id, start_time, end_time, execution_time_ms
                    )
                    VALUES (
                        ''' || wh_name || ''',
                        ''' || wh_size || ''',
                        ''' || wh_type || ''',
                        ''' || proc_type || ''',
                        ''' || impl || ''',
                        ' || run_num || ',
                        ''' || query_id || ''',
                        ''' || start_ts || ''',
                        ''' || end_ts || ''',
                        ' || DATEDIFF('millisecond', start_ts, end_ts) || '
                    )
                ';

                total_runs := total_runs + 1;
            END FOR;
        END FOR;
    END FOR;

    RETURN 'ベンチマーク完了（' || wh_name || '）: ' || total_runs || ' パターン実行済み';
END;
$$;

ベンチマーク実行

各ウェアハウスでプロシージャを実行します。CURRENT_WAREHOUSE() から自動的にウェアハウス情報を取得し、実行回数は引数で指定します。

-- 1回だけ実行する場合（デフォルト）
USE WAREHOUSE benchmark_xs;
CALL run_benchmark();
-- 実行結果例: 'ベンチマーク完了（BENCHMARK_XS）: 9 パターン実行済み'

-- 3回実行する場合（より正確なベンチマーク）
-- CALL run_benchmark(3);
-- 実行結果例: 'ベンチマーク完了（BENCHMARK_XS）: 27 パターン実行済み'

通しでベンチマークする場合は以下を全て実行します。
全部実行するのに、約30分 かかります。

ALTER SESSION SET USE_CACHED_RESULT = FALSE;

-- 全ウェアハウスで3回ずつ実行する場合
USE WAREHOUSE benchmark_xs;
CALL run_benchmark(3);

USE WAREHOUSE benchmark_s;
CALL run_benchmark(3);

USE WAREHOUSE benchmark_gen2_xs;
CALL run_benchmark(3);

USE WAREHOUSE benchmark_gen2_s;
CALL run_benchmark(3);
-- 合計: 4ウェアハウス × 9パターン × 3回 = 108パターン実行

注意:

  • デフォルトは1回実行（9パターン）、より正確なベンチマークには3回実行を推奨
  • 各ウェアハウスの実行には時間がかかる可能性があります（1ウェアハウスあたり数分〜数十分）

ベンチマーク結果の確認

プロシージャ実行後、benchmark_results テーブルに結果が格納されています。まずは実行結果を確認してみます。

-- 実行結果を確認（実行時間を秒に変換）
SELECT
    warehouse_name,
    warehouse_size,
    warehouse_type,
    process_type,
    implementation,
    run_number,
    execution_time_ms / 1000 as execution_time_seconds,
    ROUND(execution_time_ms / 1000 / 60, 2) as execution_time_minutes,
    start_time,
    end_time
FROM benchmark_results
ORDER BY created_at DESC
;

実行時間が長い場合は、分単位で見た方がわかりやすいです。

-- 実行時間が長い順に表示（分単位）
SELECT
    warehouse_name,
    process_type,
    implementation,
    run_number,
    ROUND(execution_time_ms / 1000 / 60, 2) as execution_minutes,
    start_time
FROM benchmark_results
ORDER BY execution_time_ms DESC
;

複数回実行した場合（run_count > 1）、キャッシュの影響を確認するために、実務において run_number ごとの実行時間を比較してみるのも有効です。

-- run_number ごとの実行時間を比較（キャッシュの影響確認）
SELECT
    warehouse_name,
    process_type,
    implementation,
    run_number,
    ROUND(execution_time_ms / 1000, 2) as execution_seconds
FROM benchmark_results
WHERE warehouse_name = 'BENCHMARK_XS'
  AND process_type = 'md5'
  AND implementation = 'js_udf'
ORDER BY run_number;

結果の集計・分析

ベンチマーク結果はテーブルに格納されているため、簡単に集計・分析できます。

-- ウェアハウス別・処理別・実装別の平均実行時間（秒）
SELECT
    warehouse_name,
    warehouse_size,
    warehouse_type,
    process_type,
    implementation,
    COUNT(*) as run_count,
    ROUND(AVG(execution_time_ms) / 1000, 2) as avg_execution_seconds,
    ROUND(MIN(execution_time_ms) / 1000, 2) as min_execution_seconds,
    ROUND(MAX(execution_time_ms) / 1000, 2) as max_execution_seconds
FROM benchmark_results
GROUP BY ALL
ORDER BY warehouse_name, process_type, implementation;

-- 実装別の性能比較（組み込み関数を基準とした倍率）
WITH builtin_baseline AS (
    SELECT
        warehouse_name,
        process_type,
        AVG(execution_time_ms) as baseline_time
    FROM benchmark_results
    WHERE implementation = 'builtin'
    GROUP BY ALL
)
SELECT
    r.warehouse_name,
    r.warehouse_size,
    r.process_type,
    r.implementation,
    ROUND(AVG(r.execution_time_ms) / 1000, 2) as avg_execution_seconds,
    ROUND(AVG(r.execution_time_ms) / MAX(b.baseline_time), 1) as ratio_to_builtin
FROM benchmark_results r
JOIN builtin_baseline b
    ON r.warehouse_name = b.warehouse_name
    AND r.process_type = b.process_type
GROUP BY ALL
ORDER BY warehouse_name, process_type, implementation;

-- ウェアハウス間の比較（XSを基準とした倍率）
WITH xs_baseline AS (
    SELECT
        process_type,
        implementation,
        AVG(execution_time_ms) as baseline_time
    FROM benchmark_results
    WHERE warehouse_size = 'XS' AND warehouse_type = 'standard'
    GROUP BY ALL
)
SELECT
    r.warehouse_name,
    r.warehouse_size,
    r.warehouse_type,
    r.process_type,
    r.implementation,
    ROUND(AVG(r.execution_time_ms) / 1000, 2) as avg_execution_seconds,
    ROUND(MAX(b.baseline_time) / AVG(r.execution_time_ms), 2) as speedup_vs_xs
FROM benchmark_results r
JOIN xs_baseline b
    ON r.process_type = b.process_type
    AND r.implementation = b.implementation
GROUP BY ALL
ORDER BY warehouse_name, process_type, implementation;

結果

各ウェアハウスで CALL run_benchmark(3); を実行し、3回ずつ計測した結果を以下に示します。

主な結果:

  • 実装別の速さ: 組み込み関数 > Python UDF > JavaScript UDF
  • ウェアハウスサイズ: データ100万件程度では、XSとSで性能差はほとんどなし
  • Gen2の効果: UDF使用時に標準より高速、特にJavaScript UDFで顕著

XS (X-Small) ウェアハウス

MD5ハッシュ化（XS）

実装 実行時間（秒） 組み込み関数比
組み込み関数 0.131 1.0x
JavaScript UDF 12.927 98.7x
Python UDF 2.142 16.4x

Base64エンコード（XS）

実装 実行時間（秒） 組み込み関数比
組み込み関数 0.092 1.0x
JavaScript UDF 18.737 203.7x
Python UDF 1.959 21.3x

SHA256ハッシュ化（XS）

実装 実行時間（秒） 組み込み関数比
組み込み関数 0.097 1.0x
JavaScript UDF 78.441 808.7x
Python UDF 1.933 19.9x

S (Small) ウェアハウス

MD5ハッシュ化（S）

実装 実行時間（秒） 組み込み関数比
組み込み関数 0.102 1.0x
JavaScript UDF 13.317 130.6x
Python UDF 2.142 21.0x

Base64エンコード（S）

実装 実行時間（秒） 組み込み関数比
組み込み関数 0.087 1.0x
JavaScript UDF 20.190 232.1x
Python UDF 1.665 19.1x

SHA256ハッシュ化（S）

実装 実行時間（秒） 組み込み関数比
組み込み関数 0.100 1.0x
JavaScript UDF 80.624 806.2x
Python UDF 1.670 16.7x

Gen2 XS ウェアハウス

MD5ハッシュ化（Gen2 XS）

実装 実行時間（秒） 組み込み関数比 Gen1 XS比
組み込み関数 0.108 1.0x 0.8x（高速化）
JavaScript UDF 8.886 82.3x 0.7x（高速化）
Python UDF 1.930 17.9x 0.9x（やや高速化）

Base64エンコード（Gen2 XS）

実装 実行時間（秒） 組み込み関数比 Gen1 XS比
組み込み関数 0.102 1.0x 1.1x（やや遅い）
JavaScript UDF 12.458 122.1x 0.7x（高速化）
Python UDF 1.646 16.1x 0.8x（高速化）

SHA256ハッシュ化（Gen2 XS）

実装 実行時間（秒） 組み込み関数比 Gen1 XS比
組み込み関数 0.101 1.0x 1.0x（同等）
JavaScript UDF 57.270 567.0x 0.7x（高速化）
Python UDF 1.637 16.2x 0.8x（高速化）

Gen2 S ウェアハウス

MD5ハッシュ化（Gen2 S）

実装 実行時間（秒） 組み込み関数比 Gen1 S比
組み込み関数 0.118 1.0x 1.2x（やや遅い）
JavaScript UDF 8.859 75.1x 0.7x（高速化）
Python UDF 1.776 15.1x 0.8x（高速化）

Base64エンコード（Gen2 S）

実装 実行時間（秒） 組み込み関数比 Gen1 S比
組み込み関数 0.108 1.0x 1.2x（やや遅い）
JavaScript UDF 12.448 115.3x 0.6x（高速化）
Python UDF 1.391 12.9x 0.8x（高速化）

SHA256ハッシュ化（Gen2 S）

実装 実行時間（秒） 組み込み関数比 Gen1 S比
組み込み関数 0.129 1.0x 1.3x（やや遅い）
JavaScript UDF 57.301 444.2x 0.7x（高速化）
Python UDF 1.393 10.8x 0.8x（高速化）

ウェアハウス別の比較サマリー

各処理での最速実装と実行時間をまとめます：

処理 XS最速 S最速 Gen2 XS最速 Gen2 S最速
MD5 組み込み（0.131秒） 組み込み（0.102秒） 組み込み（0.108秒） 組み込み（0.118秒）
Base64 組み込み（0.092秒） 組み込み（0.087秒） 組み込み（0.102秒） 組み込み（0.108秒）
SHA256 組み込み（0.097秒） 組み込み（0.100秒） 組み込み（0.101秒） 組み込み（0.129秒）

まあ、言わずもがな組み込み関数圧勝ですよね。
特に理由がなければ、もともとあるものはそのまま使いましょう。
Pythonで他のロジックの中にMD5等を組み込む場合はその限りではありません。
...と言った感じですかね。

考察

1. 組み込み関数の実行時間

組み込み関数（MD5、BASE64_ENCODE、SHA2）は、すべてのウェアハウスパターンで概ね 0.09〜0.13秒 程度で完了しました（今回の3回平均ベース）。

今回のデータでは、同じ処理をUDFで実装すると実行時間が増えました（同じく平均ベース）：

  • Python UDF: 組み込み関数の 約10.8〜21.3倍
  • JavaScript UDF: 組み込み関数の 約75.1〜808.7倍

ここで言っているのは「UDFという仕組みが必ず遅い」という断定ではなく、今回の実装＋実行条件の結果としてそう見えた、という事実整理です。

2. JavaScript UDFの実行時間

標準（Gen1）XSにおけるJavaScript UDF（平均）：

  • MD5: 12.927秒（Python UDFの約 6.0倍）
  • Base64: 18.737秒（Python UDFの約 9.6倍）
  • SHA256: 78.441秒（Python UDFの約 40.6倍）

今回のJavaScript実装は（結果から見て）計算ロジック側の負荷が大きく、また実装・保守面でもコード量が増えやすい形でした。
そのため本ベンチの数値は、「JavaScriptという言語のオーバーヘッド」よりも “ロジック実装の重さ”が効いた結果として扱うのが安全です。

3. Python UDFの実行時間

標準（Gen1）XSにおけるPython UDF（平均）：

  • MD5: 2.142秒
  • Base64: 1.959秒
  • SHA256: 1.933秒

今回の範囲では 概ね2秒前後で安定しており、JavaScript UDFより大幅に短い結果になりました。

4. キャッシュの影響（ウェアハウスキャッシュ vs クエリ結果キャッシュ）

各実行前に SUSPEND → RESUME を行っているため、少なくとも「ウェアハウスをまたいだローカルな状態」はリセットされます。

一方で、Snowflakeにはクラウドサービスレイヤー側の クエリ結果キャッシュ（persisted results / result cache） があり、これはウェアハウスの停止・起動と独立に効き得ます。
そのため、ベンチマーク目的では ALTER SESSION SET USE_CACHED_RESULT = FALSE; を明示して再計測するのが前提として安全です（＝キャッシュ議論を封じられる）。

今回のログを見る限り、

  • UDF系（特に秒オーダーのもの）は3回の揺れが小さい
  • 組み込み関数は絶対値が小さいため、ms単位の揺れが相対的に目立つ

という傾向でした。
いずれにせよ、「結果キャッシュが効いた／効いてない」をこのログだけで断定するのは難しいので、上記の通り次回以降は USE_CACHED_RESULT = FALSE を前提にするのが結論です。

5. XSとSの性能差について

平均値で比較すると、XS→Sで差はあるものの 一定方向ではなく、差も大きくはありませんでした（今回の規模・条件の範囲）。

例（標準 / 平均）：

  • MD5 builtin: XS 0.131秒 / S 0.102秒
  • MD5 Python UDF: XS 2.142秒 / S 2.142秒
  • MD5 JS UDF: XS 12.927秒 / S 13.317秒

この結果だけで「サイズ差が効かない理由」を断定せず、あくまで “今回の条件では大差が出なかった” という事実として置きます。

6. Gen2 Warehouseの効果（標準比）

標準（Gen1）に対するGen2の比率（平均、Gen2 / 標準）を見ると、傾向として：

  • JavaScript UDF: 0.62〜0.73倍（≒ 27〜38%短縮）
  • Python UDF: 0.83〜0.90倍（≒ 10〜17%短縮）
  • 組み込み関数: 0.82〜1.29倍（速くなるケース／遅くなるケースの両方がある）

という結果でした。
今回の結果では、相対的に 重い処理（秒オーダー）ほど Gen2 の短縮が分かりやすいという見え方になっています。

7. JavaScriptをいつ選ぶか

今回のテーマ（md5/sha/base64のように組み込みがある変換）に限ると、JavaScript UDFを積極的に選ぶ理由は薄くなっていそうですね。
少なくとも本ベンチの数値は「JSは不利になりやすい」寄りの結果になっています。

一方で、言語選定は性能だけでは決まりません。
SnowflakeではUDFに 共有（シェア）や定義方法の制約があり、要件次第で選択肢が絞られます。
たとえば「共有可能（sharable）なUDFにしたい」「ハンドラーをステージに置きたい／インライン必須」など、運用要件が先に立つケースがあります。

このため、

  • この手の処理は組み込み関数が第一候補
  • 組み込みだけで足りないなら、次に SQL（SQL UDF / SnowflakeスクリプトUDF）か Python UDF 等を検討
  • JavaScript UDFは「性能最適」というより、要件（共有・運用・既存資産）で必要になったときやスキルセット次第での選定

という整理が実務上ブレにくいかなと思いました。

8. 実装選定と「遅さ」の切り分け（今回のベンチから得た教訓）

  • 組み込み関数だけでできるものは積極的に組み込み関数を使う。

    ただし、前処理・条件分岐・正規化などで「処理をまとめたい」場合は、UDF（SQL/Python等）の中で最後に組み込み関数（今回の MD5 / SHA2 / BASE64_ENCODE など）を呼ぶ形にすると、可読性と性能の折り合いがつけやすいのかなと思います。

  • “同じロジック”での比較は、オーバーヘッドの目安を作るのに役立つ。

    例えば「md5を組み込み関数でやる」vs「Python UDFで同等ロジックをやる」という比較は、（ロジック差をほぼ固定した上で）追加コストの目安を掴みやすいですね。

    一方で今回のJavaScript UDFは自前実装の比重が大きく、どこまでが“呼び出し方式の差”でどこまでが“実装差”か分離しづらいので、オーバーヘッド評価としては割愛する、扱いに注意するのが良さそうです。

  • 「Pythonだから遅い」という短絡は避ける。

    組み込み関数とPython UDFを比較した時のオーバーヘッドの目安は今回の実験の結果が参考になるかと思います。
    主な遅さの原因は

    1. ロジックそのものの計算量
    2. データ受け渡しや実行モデル由来のコスト (UDFの呼び出し・データ受け渡し（型変換）・ランタイム初期化、行ごとかバッチか、などの実行方式に伴う付帯コスト)
    3. 追加のシステム処理（システム関数呼び出し、ポリシー評価、シークレット参照、外部呼び出し等）

    など複数に分解できます。
    実務のトラブルシュートに向けては、どれが支配的か？を切り分けると良いですね。

    なお、実践知としてはシークレット参照がめちゃくちゃ遅い(1回callならまだしも。回数が重なった時にチリツモになる)ので注意した方が良い、という教訓を得ました。

実験2

...として、公平な比較 - ネイティブな関数(追加パッケージなしで使用できるもの、標準関数など)を使った検証を考えてみます。

ここまでの検証で、JavaScript UDFが圧倒的に遅いという結果が出ました。
ただ、よく考えると今回の比較には公平性の問題があります。

MD5やSHA256のような暗号学的ハッシュ関数は、JavaScript UDFではアルゴリズム全体を手書き実装する必要があるのに対し、Python UDFは標準ライブラリ hashlib を1行で呼び出すだけでした。

これだと、「JavaScript UDF自体が遅いのか」「手書き実装のアルゴリズムが遅いのか」が分からないですよね。

そこで、公平な比較をするためには 3言語すべてでネイティブ関数のみで実現できる処理を考えないといけないです。

検証シナリオ：2地点間の距離計算（Haversine公式）

調べていたら、こんな記事を見つけました：

Haversine formula（ハバーサイン公式）は、2つの緯度経度座標から地球上の大圏距離を計算する方法です。店舗検索や配送最適化など、位置情報を扱うシステムでよく使われます。
（参考: Haversine formula - Wikipedia）

正直、私自身はHaversine公式を使ったことはないのですが、今回のベンチマークにはちょうど良さそうだと思いました。

  • SQL/JavaScript/Python すべてで SIN, COS, SQRT, ATAN2 などの数学関数が追加パッケージなしで使用できる
  • アルゴリズムがシンプルで、実装差が出にくい（公式に当てはめるだけ）
  • ランダムな座標ノイズを加えれば、キャッシュを無効化できる
  • 実務でも使われる処理なので、ベンチマークとして意味がありそう
  • 計算量が適度にあり、1000万行で処理時間の差が観測できそう

というわけで、この公式を3言語で実装して、パフォーマンスを比較してみたいと思います。

Haversine公式:

a = sin²(Δlat/2) + cos(lat1) × cos(lat2) × sin²(Δlon/2)
c = 2 × atan2(√a, √(1-a))
distance = R × c  （R = 地球の半径 6371 km）

三角関数と平方根の組み合わせなので、それなりに計算負荷があります。これなら公平に比較できるはず。

ステップ1: テストデータの準備

1000万件の緯度経度データを生成します。日本全国をカバーする範囲（北緯26度〜43度、東経125度〜145度）で均等に分布させます。

USE ROLE SYSADMIN;
USE WAREHOUSE setup_wh;

USE DATABASE udf_benchmark_db;
USE SCHEMA benchmark_schema;

-- 緯度経度を含む100万件のテストデータ
CREATE OR REPLACE TABLE sample_data_geo AS
SELECT
    ROW_NUMBER() OVER (ORDER BY SEQ4()) as id,
    -- 緯度: 北海道（43度）〜 沖縄（26度）をカバー
    26.0 + ((ROW_NUMBER() OVER (ORDER BY SEQ4()) - 1) % 1000) * 0.017 AS latitude,
    -- 経度: 日本列島（125度 〜 145度）をカバー
    125.0 + ((ROW_NUMBER() OVER (ORDER BY SEQ4()) - 1) % 1000) * 0.020 AS longitude
FROM TABLE(GENERATOR(ROWCOUNT => 10000000));

-- データ確認
SELECT COUNT(*), MIN(latitude), MAX(latitude), MIN(longitude), MAX(longitude)
FROM sample_data_geo;
-- 結果: 10,000,000件、緯度 26.0〜42.983、経度 125.0〜144.98

ステップ2: UDF作成（3パターン）

2-1. 組み込み関数版（SQL）

Snowflakeの組み込み数学関数（RADIANS, SIN, COS, SQRT, POWER, ATAN2）を直接使用します。

-- SQL UDF: 組み込み関数のみ使用
CREATE OR REPLACE FUNCTION haversine_builtin(
    lat1 FLOAT,
    lon1 FLOAT,
    lat2 FLOAT,
    lon2 FLOAT
)
RETURNS FLOAT
AS
$$
  6371 * 2 * ATAN2(
    SQRT(
      POWER(SIN(RADIANS(lat2 - lat1) / 2), 2) +
      COS(RADIANS(lat1)) * COS(RADIANS(lat2)) *
      POWER(SIN(RADIANS(lon2 - lon1) / 2), 2)
    ),
    SQRT(1 - (
      POWER(SIN(RADIANS(lat2 - lat1) / 2), 2) +
      COS(RADIANS(lat1)) * COS(RADIANS(lat2)) *
      POWER(SIN(RADIANS(lon2 - lon1) / 2), 2)
    ))
  )
$$;

2-2. JavaScript UDF版

JavaScriptの Math オブジェクトのネイティブ関数（Math.sin, Math.cos, Math.sqrt, Math.pow, Math.atan2）を使用します。

注意: Snowflake JavaScript UDFでは、引数名が自動的に大文字に変換されるため、コード内では LAT1, LON1, LAT2, LON2 としてアクセスします。

-- JavaScript UDF: Math.* のネイティブ関数を使用
CREATE OR REPLACE FUNCTION haversine_js(
    lat1 FLOAT,
    lon1 FLOAT,
    lat2 FLOAT,
    lon2 FLOAT
)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
AS
$$
  const R = 6371; // 地球の半径（km）

  // 度数法からラジアンへ変換
  const toRad = (deg) => deg * Math.PI / 180;

  const dLat = toRad(LAT2 - LAT1);
  const dLon = toRad(LON2 - LON1);
  const lat1Rad = toRad(LAT1);
  const lat2Rad = toRad(LAT2);

  const a = Math.pow(Math.sin(dLat / 2), 2) +
            Math.cos(lat1Rad) * Math.cos(lat2Rad) *
            Math.pow(Math.sin(dLon / 2), 2);

  const c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));

  return R * c;
$$;

2-3. Python UDF版

Pythonの math モジュールのネイティブ関数（math.radians, math.sin, math.cos, math.sqrt, math.pow, math.atan2）を使用します。

-- Python UDF: math モジュールのネイティブ関数を使用
CREATE OR REPLACE FUNCTION haversine_py(
    lat1 FLOAT,
    lon1 FLOAT,
    lat2 FLOAT,
    lon2 FLOAT
)
RETURNS FLOAT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'haversine'
AS
$$
import math

def haversine(lat1, lon1, lat2, lon2):
    R = 6371  # 地球の半径（km）

    # 度数法からラジアンへ変換
    lat1_rad = math.radians(lat1)
    lat2_rad = math.radians(lat2)
    dLat = math.radians(lat2 - lat1)
    dLon = math.radians(lon2 - lon1)

    a = math.pow(math.sin(dLat / 2), 2) + \
        math.cos(lat1_rad) * math.cos(lat2_rad) * \
        math.pow(math.sin(dLon / 2), 2)

    c = 2 * math.atan2(math.sqrt(a), math.sqrt(1 - a))

    return R * c
$$;

ステップ3: 実装の正確性確認

3つとも同じような記述で実装ができましたね。

3つの実装が同じ結果を返すことを確認します。東京（35.6762, 139.6503）から大阪（34.6937, 135.5023）までの距離を計算します。

SELECT
    haversine_builtin(latitude, longitude, 35.6762, 139.6503) AS builtin_km,
    haversine_js(latitude, longitude, 35.6762, 139.6503) AS js_km,
    haversine_py(latitude, longitude, 35.6762, 139.6503) AS py_km,
    CASE
        WHEN ABS(haversine_builtin(latitude, longitude, 35.6762, 139.6503)
               - haversine_js(latitude, longitude, 35.6762, 139.6503)) < 0.01
         AND ABS(haversine_builtin(latitude, longitude, 35.6762, 139.6503)
               - haversine_py(latitude, longitude, 35.6762, 139.6503)) < 0.01
        THEN '✓ 一致'
        ELSE '✗ 不一致'
    END AS "結果検証"
FROM sample_data_geo
LIMIT 10;
-- 期待結果: 全行で「✓ 一致」が表示されること

ステップ4: ベンチマーク用プロシージャ作成

既存の benchmark_results テーブルを使い回します。process_type に 'haversine' を追加する形です。

-- ベンチマーク実行プロシージャ（Haversine専用）
CREATE OR REPLACE PROCEDURE run_benchmark_geo(run_count NUMBER DEFAULT 1)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    implementations ARRAY := ['builtin', 'js_udf', 'py_udf'];
    wh_name VARCHAR;
    wh_size VARCHAR;
    wh_type VARCHAR;
    result_set RESULTSET;
    impl VARCHAR;
    run_num NUMBER;
    sql_stmt VARCHAR;
    start_ts TIMESTAMP_LTZ;
    end_ts TIMESTAMP_LTZ;
    query_id VARCHAR;
BEGIN
    -- 現在のウェアハウス情報を取得
    wh_name := CURRENT_WAREHOUSE();
    result_set := (EXECUTE IMMEDIATE 'SHOW WAREHOUSES LIKE ''' || wh_name || '''');
    LET c1 CURSOR FOR result_set;
    FOR record IN c1 DO
        wh_size := record."size";
        -- サイズを短縮形に変換（X-SMALL → XS, SMALL → S）
        wh_size := CASE
            WHEN UPPER(record."size") = 'X-SMALL' THEN 'XS'
            WHEN UPPER(record."size") = 'SMALL' THEN 'S'
            ELSE record."size"
        END;
        IF (record."resource_constraint" = 'STANDARD_GEN_2') THEN
            wh_type := 'gen2';
        ELSE
            wh_type := 'standard';
        END IF;
    END FOR;

    -- ウェアハウスキャッシュをクリア
    EXECUTE IMMEDIATE 'ALTER WAREHOUSE ' || wh_name || ' SUSPEND';
    EXECUTE IMMEDIATE 'ALTER WAREHOUSE ' || wh_name || ' RESUME';

    -- 各実装 × 実行回数分ループ
    FOR i IN 0 TO ARRAY_SIZE(implementations) - 1 DO
        impl := implementations[i];

        FOR run_num IN 1 TO run_count DO
            -- 実装ごとにクエリを構築
            -- 基準地点: 東京（35.6762, 139.6503）にランダムノイズ ±0.01度を付加
            -- ノイズ範囲: 約 ±1km（キャッシュ無効化のため）
            CASE (impl)
                WHEN 'builtin' THEN
                    sql_stmt := '
                        SELECT
                            haversine_builtin(
                                latitude,
                                longitude,
                                35.6762 + UNIFORM(-0.01, 0.01, RANDOM()),
                                139.6503 + UNIFORM(-0.01, 0.01, RANDOM())
                            ) AS distance_km
                        FROM sample_data_geo
                    ';
                WHEN 'js_udf' THEN
                    sql_stmt := '
                        SELECT
                            haversine_js(
                                latitude,
                                longitude,
                                35.6762 + UNIFORM(-0.01, 0.01, RANDOM()),
                                139.6503 + UNIFORM(-0.01, 0.01, RANDOM())
                            ) AS distance_km
                        FROM sample_data_geo
                    ';
                WHEN 'py_udf' THEN
                    sql_stmt := '
                        SELECT
                            haversine_py(
                                latitude,
                                longitude,
                                35.6762 + UNIFORM(-0.01, 0.01, RANDOM()),
                                139.6503 + UNIFORM(-0.01, 0.01, RANDOM())
                            ) AS distance_km
                        FROM sample_data_geo
                    ';
            END CASE;

            -- 実行時間を計測
            start_ts := CURRENT_TIMESTAMP();
            EXECUTE IMMEDIATE sql_stmt;
            end_ts := CURRENT_TIMESTAMP();
            query_id := LAST_QUERY_ID();
            
            -- 結果を記録
            EXECUTE IMMEDIATE '
                INSERT INTO benchmark_results (
                    warehouse_name, warehouse_size, warehouse_type,
                    process_type, implementation, run_number,
                    query_id, start_time, end_time, execution_time_ms
                )
                VALUES (
                    ''' || wh_name || ''',
                    ''' || wh_size || ''',
                    ''' || wh_type || ''',
                    ''haversine'',
                    ''' || impl || ''',
                    ' || run_num || ',
                    ''' || query_id || ''',
                    ''' || start_ts || ''',
                    ''' || end_ts || ''',
                    ' || DATEDIFF('millisecond', start_ts, end_ts) || '
                )
            ';

            -- 次の実行前にキャッシュクリア
            IF (run_num < run_count) THEN
                EXECUTE IMMEDIATE 'ALTER WAREHOUSE ' || wh_name || ' SUSPEND';
                EXECUTE IMMEDIATE 'ALTER WAREHOUSE ' || wh_name || ' RESUME';
            END IF;
        END FOR;
    END FOR;

    RETURN 'Haversine benchmark completed: ' || ARRAY_SIZE(implementations) || ' implementations × ' || run_count || ' runs';
END;
$$;

ステップ5: ベンチマーク実行

既存のウェアハウス（BENCHMARK_XS、BENCHMARK_S、BENCHMARK_GEN2_XS、BENCHMARK_GEN2_S）を使い回します。

-- XS Warehouse で実行（3回）
USE WAREHOUSE BENCHMARK_XS;
CALL run_benchmark_geo(3);

-- S Warehouse で実行（3回）
USE WAREHOUSE BENCHMARK_S;
CALL run_benchmark_geo(3);

-- Gen2 XS Warehouse で実行（3回）
USE WAREHOUSE BENCHMARK_GEN2_XS;
CALL run_benchmark_geo(3);

-- Gen2 S Warehouse で実行（3回）
USE WAREHOUSE BENCHMARK_GEN2_S;
CALL run_benchmark_geo(3);

ステップ6: 結果確認

-- Haversine検証の結果を確認
SELECT
    warehouse_name,
    warehouse_type,
    implementation,
    run_number,
    ROUND(execution_time_ms / 1000, 2) as execution_seconds
FROM benchmark_results
WHERE process_type = 'haversine'
ORDER BY warehouse_name, implementation, run_number;

-- 実装ごとの平均実行時間
SELECT
    warehouse_name,
    warehouse_type,
    implementation,
    ROUND(AVG(execution_time_ms) / 1000, 2) as avg_seconds,
    ROUND(MIN(execution_time_ms) / 1000, 2) as min_seconds,
    ROUND(MAX(execution_time_ms) / 1000, 2) as max_seconds
FROM benchmark_results
WHERE process_type = 'haversine'
GROUP BY ALL
ORDER BY warehouse_name, implementation;

結果と考察

実行結果（1000万件）

ウェアハウス 実装 平均実行時間（秒） 組み込み関数比
XS standard 組み込み関数 (SQL UDF) 2.53 1.0x
JavaScript UDF 4.93 1.95x
Python UDF 7.78 3.08x
S standard 組み込み関数 (SQL UDF) 2.38 1.0x
JavaScript UDF 4.36 1.83x
Python UDF 4.94 2.08x
Gen2 XS 組み込み関数 (SQL UDF) 1.89 1.0x
JavaScript UDF 4.51 2.39x
Python UDF 5.77 3.05x
Gen2 S 組み込み関数 (SQL UDF) 1.99 1.0x
JavaScript UDF 3.63 1.82x
Python UDF 3.80 1.91x

考察

今回の検証では、コアとなる演算部分は 「各実行環境が提供する標準の組み込み関数（= 手書き実装ではない）」 を使いました：

  • SQL（Snowflake組み込み関数）: RADIANS, SIN, COS, SQRT, POWER, ATAN2
  • JavaScript（JSランタイムの標準関数）: Math.sin, Math.cos, Math.sqrt, Math.pow, Math.atan2
  • Python（Python標準ライブラリ）: math.radians, math.sin, math.cos, math.sqrt, math.pow, math.atan2

この場合、組み込み関数（SQL UDF）が最速、次にJavaScript UDF、Python UDFが最も遅いという結果になりました。

  1. 標準関数を使っても言語間の差は残る

    • JavaScript UDF: 組み込み関数の 1.82〜2.39倍
    • Python UDF: 組み込み関数の 1.91〜3.08倍
    • これが組み込み関数と比べて「UDF実行オーバーヘッド」の言語間差も現れている模様

  2. MD5/Base64/SHA256との比較

    • 手書き実装の関数（MD5/SHA256）: JavaScript UDFは組み込みの 75〜808倍 (自前実装なので...)
    • 標準関数（Haversine）: JavaScript UDFは組み込みの 約2倍
    • → アルゴリズム実装の差が効いてそう

  3. Python UDFの遅さ

    • MD5/Base64/SHA256では Python が JavaScript より速かった (自前実装なので...)
    • Haversineでは逆に Python が JavaScript より遅い（特にXSで顕著）
    • → 少なくとも本検証の範囲で見てみると、「アルゴリズム実装差（手書き vs 標準ライブラリ）」の影響を除くと、UDF実行時の上乗せ（実行モデル・ランタイム・型変換等）の面で Python が不利に出るケースがありそう

  4. Gen2の効果

    • Gen2 Sで JavaScript/Python UDFが最速（3.63秒、3.80秒）
    • 標準ウェアハウスより30〜40%高速化

  5. ウェアハウスサイズの影響

    • 1000万件でも、XSとSで性能差は限定的(ロジックの複雑さやデータ量次第かと思う)
    • 今回の実験の範疇ではGen2がGen1より速い

こんな感じでしょうか。

おわりに

今回は、Snowflake UDF の言語別パフォーマンスを、XS/S/Gen2 XS/Gen2 Sの4つのウェアハウスパターンでベンチマーク検証してみました。

まずは組み込み関数で何ができるか？を調べた上で、できないことがあるならPython等での実装を考える、みたいなやり方が良さそうですかね。

処理が軽かったとはいえ、Gen2 Warehouseの挙動も少し見えましたね。
Gen2は負荷に応じてリソースを動的に調整するため、今回のJavaScript UDFで実現したような高負荷ロジック+クエリでは Gen1と比べて実行時間が短縮されました。
コストやパフォーマンス最適化を行う際の考慮点、選択肢として再認識できました。

また、ストアドプロシージャでの自動化により、複数パターンのベンチマークを効率的に実行できました。
実行回数も引数で指定できるため、まず1回だけ試して動作確認し、本格的なベンチマークでは3回実行して平均を取るといった使い分けが可能です。

クエリヒストリーから拾うのもいいですが、ベンチマークに特化したテーブルを用意して結果を書き出すことで、後から色々確認したり分析もしやすいですね。

ベンチマークの仕方の参考にもなれば幸いです。

以上です。

8
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
8
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?