謎のスプレッド演算子！Snowflakeに突如実装された ** とは！？

小ネタです。

2025年4月8日に追記しました。
公式の紹介ブログなどを追記しています。

スプレッド演算子とは？

スプレッド演算子(Spread operator)は2025/3/15のリリースで実装されました。
「配列を個別の値リストに展開可能」とのことです。

追記：公式の紹介ブログもリリースされました。

配列とスプレッド演算子の使い方

試してみましょう。
Snowflakeでは配列は以下のような構文になります。

SELECT [1, 2, 3] AS ARR;

スプレッド演算子は以下のような構文で適用します。

SELECT **[1, 2, 3] AS ARR;

なお、ARRAY_CONSTRUCT関数を使っても[]で書いたのと同じ挙動だそうです。ふむふむ。

SELECT **ARRAY_CONSTRUCT(1, 2, 3) AS ARR;

空の配列を渡すと…クラッシュします。さらばSnowsight。
実行したときのワークシートが(おそらくキャッシュが残っている間はずっと)開けなくなるので絶対に実行してはいけない。

-- これは実行してはいけない
SELECT **[];

半構造化データの処理に便利そうなので、ちょっとそういうテーブルを作成して試してみましょう。

-- 準備する
CREATE OR REPLACE TEMPORARY TABLE array_example (id INT, array_column ARRAY);
INSERT INTO array_example (id, array_column)SELECT 1, [1, 2, 3];
INSERT INTO array_example (id, array_column)SELECT 2, [4, 5, 6];
INSERT INTO array_example (id, array_column)SELECT 3, [7, 8, 9];
SELECT * FROM array_example;

-- スプレッド演算子を適用！
SELECT id, **array_column FROM array_example;

ナムサン！無慈悲な非対応なのだ！
「非定数な配列へのスプレッド演算子は非対応です」みたいなメッセージが出ていますね。

000002 (0A000): Unsupported feature 'spread argument with non-constant array input'.

例えば以下のような「頑張ってコンパイルしたら定数って分かる可能性あるんじゃない？」みたいなSQL文でもNGでした。

-- 動かない！
SELECT **ARR FROM (
  SELECT [1, 2, 3] AS ARR
);

ということで、最初に示したような[]を直接展開するケースを対象にしているようです。
おそらく、以下のような書き方を省力化できるものを意図しているんじゃないかなーと想像しています。
追記したように、ストアドプロシージャなどを組み合わせるともっと広範な用途があります。

SELECT
  ARR[0] AS A1,
  ARR[1] AS A2,
  ARR[2] AS A3
FROM (
  SELECT [1, 2, 3] AS ARR
);

スプレッド演算子の用途

想像だけで書いているのですが…例えばパラメータを配列としてサッと書いて、そこから展開したいときに便利かもしれません。
プログラムでSQL文を生成するときにも楽できるシーンがあるかも。
ぜひ「こんな使い方もできるよ！」というアイディアを教えてください！

こいつ…ILIKEとも組み合わせられるぞ！

SELECT * ILIKE 'ARR%2%' FROM (
  SELECT **[1, 2, 3] AS ARR
);

追記：SnowVillageでのディスカッション

SnowVillageは国内のSnowflakeコミュニティです。
本記事の公開後、このSnowVillageのSlackで相談したところ、色々とコメントをいただきました。

• Pythonなどにおける記述に近づけるためだろう。動的SQLで?プレースホルダを並べるよりシンプルに書ける
• 可変長引数で使うイメージ
• ストアドプロシージャで使えるはず
• ARRAY_CONTAINSで値が配列に含まれているか判定するよりもずっと早くなるはず
• IN句で使うのでは？

公式の紹介ブログがリリースされる前だったのに、この精度のコメントがいただけるのはありがたすぎる…！

追記：シン・スプレッド演算子の用途

関数の可変長引数に展開する

実際にはこの用途がちょこちょこありそう。公式の紹介ブログでも「Other use cases」の最初に取り上げています。

-- COALESCEはNULLじゃない最初の要素を返す関数。このケースは1が返ってくる
SELECT COALESCE(**[NULL, 1, 2, 3]);

-- CONCATは文字列結合する関数。このケースは'HelloSnowflake!'という文字列になる
SELECT CONCAT(**['Hello', 'Snow', 'flake!']);

-- LEAST/GREATESTは最小/最大を返す関数
SELECT GREATEST(**[10, 100, 1000]);
-- 

一連の可変長引数に対して、複数の配列から展開してもOK。これは可能性を感じますね！

-- 3つの配列をすべて一連の可変長引数に展開する。結果として20が最大として選ばれる
SELECT GREATEST(**[10, 18, 14], **[13, 2, 16], **[1, 20, 4, 9]);

Snowflake Scriptingと組み合わせる

前述の可変長引数に展開するサンプルコードのように[]を直接展開するケースに使うなら、その中身を最初から並べて書いたらいいのでは？と思っていたのですが、Snowflake Scriptingと組み合わせると以下のようなことができます。

DECLARE
    -- 配列を定義する
    INPUT_ARRAY ARRAY DEFAULT [1, 2, 3, 4];
    OUTPUT RESULTSET;
BEGIN
    -- 定義した配列を参照する
    OUTPUT := (SELECT COALESCE(**:INPUT_ARRAY));
    RETURN TABLE(OUTPUT);
END;

こういう書き方だと[]の中身を書く場所がDECLAREに集約できるので、SQL文が少し抽象化されていい感じですね！

ストアドプロシージャで使う

Snowflake Scriptingで使えるということは、ストアドプロシージャで使えるということ…！
まずは匿名ストアドプロシージャで試してみましょう！

-- WITH句で定義すると匿名ストアドプロシージャを作れる。実装言語はSQLを指定
WITH ANONYMOUS_PROC AS PROCEDURE (INPUT_ARRAY ARRAY)
    RETURNS TABLE(VAL INTEGER)
    LANGUAGE SQL
    AS
    $$
    DECLARE
        OUTPUT RESULTSET;
    BEGIN
        OUTPUT := (SELECT LEAST(**:INPUT_ARRAY));
        RETURN TABLE(OUTPUT);
    END;
    $$
CALL ANONYMOUS_PROC([7, 5, 2, 3]);
-- 結果として2が得られる

動いたァァァァ！
そして、もちろん通常のストアドプロシージャでも使えます。

-- 実装言語はSQLを指定
CREATE OR REPLACE PROCEDURE ARR_SPREAD_PROC(INPUT_ARRAY ARRAY)
RETURNS TABLE(VAL INTEGER)
LANGUAGE SQL
AS
DECLARE
    OUTPUT RESULTSET;
BEGIN
    OUTPUT := (SELECT LEAST(**:INPUT_ARRAY));
    RETURN TABLE(OUTPUT);
END;

-- 動作確認1：結果として2が得られる
CALL ARR_SPREAD_PROC([2, 3]);
-- 動作確認2：違うサイズの配列でもOK
CALL ARR_SPREAD_PROC([5, 7, 2, 3, 5, 7]);
-- 動作確認3：ストアドプロシージャの引数をSQLから与えても大丈夫
CALL ARR_SPREAD_PROC(SELECT [2, 3, 5, 7, 11]);

やったぁ！
公式の紹介ブログによるとUDF(User Defined Function)でもOKだそうです。

IN句でも使える

ストアドプロシージャ内におけるIN句で使うのが最も有用そう。

WITH FILTER_CUSTOMER AS PROCEDURE (INPUT_ARRAY ARRAY)
    RETURNS TABLE(C_CUSTKEY INTEGER, C_NAME STRING)
    LANGUAGE SQL
    AS
    $$
    DECLARE
        OUTPUT RESULTSET;
    BEGIN
        OUTPUT := (
            SELECT C_CUSTKEY, C_NAME
            FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER
            WHERE C_CUSTKEY IN (**:INPUT_ARRAY)
        );
        RETURN TABLE(OUTPUT);
    END;
    $$
CALL FILTER_CUSTOMER([60001, 60035, 60073]);

以下のようになります！ちゃんと動いてそう。

スプレッド演算子がない場合、FLATTENしてTABLEした結果をSELECTする必要があります。かなりシンプルになりますね。

-- もし、世界にスプレッド演算子がなかったら
WITH FILTER_CUSTOMER AS PROCEDURE (INPUT_ARRAY ARRAY)
    RETURNS TABLE(C_CUSTKEY INTEGER, C_NAME STRING)
    LANGUAGE SQL
    AS
    $$
    DECLARE
        OUTPUT RESULTSET;
    BEGIN
        OUTPUT := (
            SELECT C_CUSTKEY, C_NAME
            FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER
            WHERE C_CUSTKEY IN (
                SELECT VALUE FROM TABLE(FLATTEN(:INPUT_ARRAY))
            )
        );
        RETURN TABLE(OUTPUT);
    END;
    $$
CALL FILTER_CUSTOMER([60001, 60035, 60073]);

ストアドプロシージャでもこのパターンはダメ

-- NULLが含まれていると結果はNULLになる
SELECT LEAST(5, 1, 2, 3, NULL, 7);
-- 上記と同じ挙動
SELECT LEAST(**[5, 1, 2, 3, NULL, 7]);
-- これをストアドプロシージャ＋スプレッド演算子でやろうとすると動かない！
CALL ARR_SPREAD_PROC([5, 1, 2, 3, NULL, 7]);

上記のようにNULLを含む配列が渡されると残念ながら動きません。無念。

000002 (0A000): Uncaught exception of type 'STATEMENT_ERROR' on line 4 at position 14 : Unsupported feature 'spread argument with non-constant array input'.

追記：SnowVillageに参加するには？

SnowVillageは主にSlackで活動しています。日々、Snowflakeの活用方法やベストプラクティス、悩み事についてかなり活発にディスカッションしています。
下記のサイトに参加規約とSlackへの招待リンクがありますので、興味のある方はぜひ参加してくださいね！