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」の権限で実行になってしまうので、呼び出す人のロールで実行されるように変更します。）
「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＞
データ型は
- ・SQLのデータ型（https://docs.snowflake.com/ja/sql-reference/data-types.html）
- ・cursole
- ・resultset
- ・exception
変数への値のセット
変数には、単純に値をセットすることもできますし、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!!!
このページをシェアする：
