Snowflake SQLのプロシージャ用拡張機能「Snowflake Scripting」を試してみた

「カスタマーストーリー」を支えるツール特集

#Snowflake

大高大輔

2022.01.25

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

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

Snowflakeの2022年1月のリリース（6.0）で、新しいSnowflake SQLのプロシージャ用拡張機能「Snowflake Scripting」が追加されました。（2022年1月現在はプレビュー機能です）

今回はこの「Snowflake Scripting」を実際に試していきたいと思います。

「Snowflake Scripting」とは？

「Snowflake Scripting」については、リリースノートに以下のように記載されています。

Snowflakeスクリプトは、Snowflake SQL の拡張機能であり、手続き型ロジックのサポートを追加します。Snowflakeスクリプトを使用して、 SQL でストアドプロシージャを記述できます。

Oracle の PL/SQL や SQL Server の Transact-SQL のような、プロシージャ用のSQL拡張ですね。Snowflake Scripting についての詳細は、以下のドキュメントに記載があります。

試してみた

では、実際に試してみましょう。

利用するインターフェースによる注意点

まずはじめに、利用するインターフェースによって注意が必要なようです。

SnowSQL でのSnowflakeスクリプトと従来のウェブインターフェイスの使用 — Snowflake Documentation

こちらに記載のとおり、「SnowSQL(CLI経由)」の場合と「従来のウェブインターフェイス」の場合にはSnowflakeスクリプトのブロックを「一重引用符または二重ドル記号」で囲む必要があるとのことです。

一方で、「新しいウェブインターフェイス」の場合には、この考慮は不要となります。実際に引用符で囲まなかったケースで試してみます。

従来のウェブインターフェイスの場合

create or replace procedure myprocedure()
  returns varchar
  language sql
  as
    -- Snowflake Scripting code
    declare
      radius_of_circle float;
      area_of_circle float;
    begin
      radius_of_circle := 3;
      area_of_circle := pi() * radius_of_circle * radius_of_circle;
      return area_of_circle;
    end;

想定通り、エラーになりますね。

新しいウェブインターフェイスの場合

create or replace procedure myprocedure()
  returns varchar
  language sql
  as
    -- Snowflake Scripting code
    declare
      radius_of_circle float;
      area_of_circle float;
    begin
      radius_of_circle := 3;
      area_of_circle := pi() * radius_of_circle * radius_of_circle;
      return area_of_circle;
    end;

こちらは問題なく作成できていますね。

今回は「従来のウェブインターフェイス」で試したいと思うので「一重引用符または二重ドル記号」で囲むクエリで試していきます。

プロシージャの作成と利用

通常ではストアドプロシージャはCREATE PROCEDUREを利用して作成していきます。

CREATE PROCEDURE — Snowflake Documentation

一方で、「匿名ブロック」というストアドプロシージャとして保存せずに、スクリプトブロックを記述して実行することもできます。今回は手軽に試せるこちらを試してみます。

Snowflakeスクリプトのブロックを理解する — Snowflake Documentation

円の面積を計算する

ドキュメントに記載されている円の面積を計算するスクリプトを試してみます。

execute immediate $$
declare
    radius_of_circle float;
    area_of_circle float;
begin
    radius_of_circle := 3;
    area_of_circle := pi() * radius_of_circle * radius_of_circle;
    return area_of_circle;
end;
$$
;

DECLAREセクションで宣言した変数のradius_of_circleに3を代入して、同じく変数宣言したarea_of_circleに計算結果を代入して返却するスクリプトです。

円の面積なので、3 x 3 x 3.14 = 28.26に近い値になるはずです。

想定どおりの円の面積が返ってきました！

曜日を求める（ツェラーの公式）

別の例として、年月日から曜日を求めることのできるツェラーの公式というものを試してみます。

ツェラーの公式 - Wikipedia

こちらのPython向けのコードを参考にさせていただきました。

Pythonでツェラーの公式を使って曜日を求める方法 | You Look Too Cool

以下のプロシージャの結果は「0〜6」の数字が出力され、それぞれ曜日の「日〜土」が対応します。

execute immediate $$
declare
    year number(4,0);
    month number(2,0);
    day number(2,0);
    w number;
    x number(1,0);
begin
    year := 2022;
    month := 1;
    day := 25;
    
    if (month <= 2) then
        year := year - 1;
        month := month + 10;
    else
        month := month - 2;
    end if;
    
    w := day + ((13 * month - 1) / 5)::integer + year + (year / 4)::integer - (year / 100)::integer + (year / 400)::integer;
    x := w % 7;

    return x;
end;
$$
;