Snowflake　

Snowflake Scripting を使ってみよう！

こんにちは
ちゅらデータの田代です！

皆さん！Snowflakeでストアドプロシージャ書いていますかぁぁぁぁ（挨拶）
Snowflakeでストアドプロシージャっていうと、JavaScriptで書くのが長らくメジャーだったかなと思います。
「Snowflake Procedure」で検索をかけても、JavaScriptで記載する記事がほとんどでした。
近年では、Snowparkを使用して、Javaで書いたり、Scalaで書いたり、Pythonでも書けるようになりましたね。
参考：https://docs.snowflake.com/ja/developer-guide/snowpark/index.html
ただ、自分としては、

  • ・Snowflakeは、SQLでどんな処理もできるし、SQLだけで書けたらいいのになー
  • ・BigQueryでは、複数のSQLを手続き的にワークシートで保存して、Schedule Queryで動かすことができていたので、Snowflakeでもできたらいいなー

なんて思っていました。
そんなときにやってきた機能がSnowflake Scripting！！！！！

Snowflake Scriptingとは

「Snowflakeスクリプトは、Snowflake SQL の拡張機能であり、手続き型ロジックのサポートを追加します。Snowflakeスクリプトを使用して、 SQL でストアドプロシージャを記述できます。」
引用：https://docs.snowflake.com/ja/release-notes/2022-01.html#snowflake-scripting-preview

2022年1月にプレビューが開始され、5月にGAされました。
複数のSQLをまとめて実行できる、Oracle PL/SQLのようなスクリプト言語で、手続き的に走らせる機能です。
まさにBigQueryにあった「保存したクエリ」「Schedule Query」みたいな機能で、非常に重宝します。
なお、

  • ・DBを操作するのに、SQLのみでやりたい！
  • ・JavaScriptやPythonでプログラミングするのは難しい！
  • ・学習コストが高くて厳しい！

って方々にも使いやすく、おすすめです。
SQLだけでなら比較的シンプルにかけ、使うのに慣れているSQLステートメントや関数でかけますしね。

Snowflake Scriptingのドキュメントはこちら

Snowflake Scriptingの基本的な書き方について

Snowflake Scriptingでは、ブロック内に手続き型コードを記述します。

ブロック構造について

大まかなブロック構造としては以下の通り

    DECLARE
    -- 変数、カーソルなどの宣言
    BEGIN
    -- SQLステートメントとSnowflakeスクリプトの構造を記述
    EXCEPTION
    -- 例外処理を追加する必要があるときに
    END;

BEGINは、トランザクションを開始するキーワード「BEGIN」とは違うので注意。トランザクションを開始する際は、「BEGIN TRANSACTION;」を用いることが強く推奨されています。

ストアドプロシージャの書き方

ストアドプロシージャでSnowflake Scriptingを用いる際には下記のような例になります。

create or replace procedure area()
    returns float
    language sql
    execute as caller
    as
    $$
      declare
        radius float;
        area_of_circle float;
      begin
        radius := 3;
        area_of_circle := pi() * radius * radius;
        return area_of_circle;
      end;
    $$
  ;
  
  call  area();
  • ・language sql
  • ・変数に値を代入する際には「:=」を用いる
  • ・execute as caller（ストアドプロシージャはデフォルトでは「OWNER」の権限で実行になってしまうので、呼び出す人のロールで実行されるように変更します。）
が特徴的でしょうか。

参考：https://docs.snowflake.com/ja/developer-guide/snowflake-scripting/blocks.html#using-a-block-in-a-stored-procedure

「execite as caller」 に関しては下記のブログがわかりやすいです。 ストアドプロシージャはデフォルトでは、OWNERのロールで実行されます。 https://medium.com/snowflake/difference-between-execute-as-a-caller-and-owner-in-snowflake-c1826033b782

変数の操作

変数の宣言

変数の宣言は二通りの方法があります

      1.declareブロック内で宣言
      2.beginブロック内で宣言

declare
　　　　　　　　　　<variable_name> <type>;
　　　　　　　　　　<variable_name> DEFAULT <expression>;
　　　　　　　　　　<variable_name> <type> DEFAULT <expression>;
　　　　　　　　　　-- 例
　　　　　　　　　　-- SQLのデータ型
     radius float;
　　　　　　　　　　-- RESULTSET
     res resultset default (SELECT id,name FROM table_a);
　　　　　　　　　　-- CURSOR
　　　　　　　　　　cur1 cursor for res;
　　　　　　　　　　-- EXCEPTION
　　　　　　　　　　e1 exception (-20001, 'Exception e1');
といった形で、declareブロック内で宣言します。 
begin
    let <variable_name> <type> := <value>;
    
    -- 例
    let radius3.14 float := 3;
end;
＜type＞
データ型は です。

変数への値のセット

変数には、単純に値をセットすることもできますし、SELECTステートメントの結果をセットしたりすることもできます。

    create or replace procedure return_fload()
        returns float
        language sql
        execute as caller
        as
        $$
        begin
          let radius float := 3.14;
          
          return radius;
        end;
        
        $$;
        call return_fload();
      
    create or replace procedure return_table()
    returns table(id string, name string)
    language sql
    execute as caller
    as
    $$
    begin
      let res resultset := (select id, name from table_a);
      return table(res);
    end;
    
    $$;
    call return_table();

【参考】こちらでテーブルを作成しています 
    create or replace table table_a
    (
        id string,
        name string
    )
    ;
    insert into table_a
    values
    ('hoge', 'HOGE'),
    ('geho','GEHO')
    ;
変数へのSQL実行結果のセットもできます。
INTOステートメントを使います。
参考：https://docs.snowflake.com/ja/sql-reference/constructs/into.html
下記の例では、table_aのレコード数（＝count(*)）の値を、変数 count にセットしています。 
    create or replace procedure return_count()
    returns integer
    language sql
    execute as caller
    as
    $$
    begin
      let count integer default 0;
      select count(*) as count into :count from table_a;
      return :count;
    end;
    
    $$;
    call return_count();
※SELECTステートメントは単一の行を返す必要があります。

2023年1月のリリースにて2023年1月のリリースにて、ストアドプロシージャのスカラー戻り値を変数にセットする機能がリリースされました。 これによって、サブルーチン的に「サブストアドプロシージャ」での実装もより楽になるかもしれませんね。 
    DECLARE
    ret1 NUMBER;
  BEGIN
    CALL my_procedure('Manitoba', 127.4) into :ret1;
    RETURN ret1;
  END;

変数の使用

変数への値を代入することが出来たら、その変数を使ってなにか処理をしたくなるのが人情ってもの。
まずは、SQL文の中での変数を使ってみましょう。 

SELECT COUNT(*) AS COUNT INTO :count FROM table_a;
プロシージャの引数も変数として利用することが出来ます。
下記の例では、引数にDB名
プロシージャの中で、引数のDBに対して、desc[describe]しています。 
    create or replace procedure describe_database(db_name varchar)
    returns table()
    language sql
    execute as caller
    as
    $$
    begin
      let res resultset := (desc database identifier(:db_name));
      return table(res);
    end;
    
    $$;
    call describe_database('MAIN_DB');
※ identifier句を使って、「データオブジェクト」として扱えるようにしています。
次に、動的にSQL文を構築してみましょう。 
    create or replace procedure show_users_dynamic_sql(id varchar)
    returns table()
    language sql
    execute as caller
    as
    $$
    begin
      -- 動的にクエリを構築してます
      let query_show_users string := 'SHOW USERS LIKE \'' || :id || '\'';
      -- ↑で作ったクエリを実行
      EXECUTE IMMEDIATE :query_show_users;
  
      let res resultset := (select * from table(result_scan(last_query_id())));
      return table(res);
    end;
    
    $$;
    call show_users_dynamic_sql('TEST_USER');

SQL分を文字列連結（||）を使って、変数 id を「:id 」で連結し、EXECUTE IMMEDIATEステートメントで生成したクエリ文を実行しています。
if 文の中でも変数を使うことができます。 
    let role_check default true;
    if (:role = null) then
         role_check := false;
    end if;

for文をつかってテーブルを逐次処理しよう

SELECTステートメントの結果をresultsetにいれ、cursorを用いて、その1行毎に逐次処理をすることができます。
※cursorの使い方などは別のやり方もあるので、その場合はドキュメントを参照ください。こちらは一例となっています。 

declare
	res resultset default ( SELECT id, name FROM table_a );
	cur1 cursor for res;
begin
	for row_valiable in cur1 do
	     let id := row_valiable.id;
	     let name := UPPER( row_valiable.name );
	-- （なんからの処理）
	　
		if (なんかの条件 ) then
			-- これ以降の処理はすっとばして、次のレコード処理へ
			CONTINUE;
		elseif（なんかの条件 ) then
			-- もう処理自体を終わらす
			BREAK;
		end if;
		
		-- （なんからの処理）
	end for;
end;
resultset のカラムにアクセスするときは、row_valiable.id のように記載します。
途中、それ以降の処理をすっとばし、次の行を処理したい時は、「CONTINE」 処理自体を終わらせたい時は「BRAKE」を使います。
※詳細の使い方はドキュメントを参照ください。
https://docs.snowflake.com/ja/sql-reference/snowflake-scripting/for.html
https://docs.snowflake.com/ja/sql-reference/snowflake-scripting/continue.html
https://docs.snowflake.com/ja/sql-reference/snowflake-scripting/break.html

TIPS （Snowflake Scriptingでストアド書いてみた）

「SHOW USERS」の値を取得してテーブルに入れてみよう

show users や show databases といった、show OBJECTs系の結果をtable形式では取得できません。
※DATABASE：SNOWFLAKE　の中viewを操作すればできなくはないですが 

  create or replace procedure show_users()
    returns table()
    language sql
    execute as caller
    as
    $$
    begin
      show users;
      let res resultset := (select * from table(result_scan(last_query_id())));
      return table(res);
    end;
    
    $$;
  call show_users();
  select 
    $1 as name, 
    $2 as created_on, 
    $3 as login_name
  from table(result_scan(last_query_id()))
  where
    name = 'TEST_USER';

とあるユーザの保持ロール数で処理を分岐したい

show系のステートメントの結果を用いて、処理をしたい！第二弾 

create or replace procedure grant_role_users(user_name string)
        returns integer
        language sql
        execute as caller
        as
        $$
            begin
                show grants to user identifier(:user_name);
                let user_gants_num integer default 0;
                select 
                    count(*) as count into :user_gants_num 
                from 
                    table(result_scan(last_query_id()));
                if(user_gants_num = 0) then
                    -- 所持ロールが０個の場合
                    -- なんかの処理
                    select * from table_a;
                else
                    -- ロールを所持してる場合
                    -- なんかの処理
                    select * from table_a;
                end if;
                return :user_gants_num;
            end;
        $$
        ;
        call grant_role_users('TEST_USER');

デバッグみたいなことがしたい

厳密にステップごとのデバッグをすると言ったことは出来ないのですが、 print debug に近いことはできそうです。

      1.変数：log_array を array 型で宣言
      2.何らかのデバッグポイントで、メッセージ文字列を追加
      log_array := array_append(:log_array,'[CHECK1 OK] ‘ || :check_flg);
      3.最後に　log_array をリターン
      4.ストアドプロシージャを実行したら、結果としてメッセージ文字列の配列が表示
      call debug_procedure(); 
create or replace procedure debug_procedure()
    returns array
    language sql
    execute as caller
    as
    $$
        declare
            log_array array default ARRAY_CONSTRUCT();
        begin
            let check_flg boolean default false;
            log_array := array_append(:log_array,'[CHECK1 OK] ' || :check_flg);
    
            let name string := 'HOGE';
    
            log_array := array_append(:log_array,'[CHECK2 OK] name is ' || :name);
        
        return log_array;
        end;
    $$
    ;
    call debug_procedure();

まとめ

複数のSQLを手続き的に実行できるSnowflake Scriptingについてご紹介させていただきました。

  • ・Snowflake Scripting とはどういった機能か
  • ・Snowflake Scriptingを使ったプロシージャの例をTIPSとして紹介

Happy Snowflake Life!!!

このページをシェアする：


DATUM STUDIOは、クライアントの事業成長と経営課題解決を最適な形でサポートする、データ・ビジネスパートナーです。
データ分析の分野でお客様に最適なソリューションをご提供します。まずはご相談ください。

Contact

サービス・研修・アドバイザリーに関するお問い合わせはこちら

Explore Jobs

採用に関する情報はこちら

関連記事

前へ 次へ