こんにちは！ozawaです。
今年頭からSQLのコードやSnowflakeの機能を学び始めたのですが、触り始めてから早くも1年経とうとしています。
Snowflake上ではプロシージャを作成することができ、複数のテーブルへの操作を一つの関数の中で定義することができます。
今回はプロシージャを使って、まとめてSQL構文を実行する方法を紹介したいと思います。
プロシージャでSQLをまとめて実行する
プロシージャに必要なもの
まとめて実行したいSQL構文をSQL_QUERY_COLカラムに格納したテーブルSQL_QUERY_TABLEを作成します。
このSQL_QUERY_COLカラムに入ったSQLクエリを一斉に実行していくプロシージャを作成していきます。
ソースコード
database_nameデータベースのschema_nameスキーマ上に、SQLを一斉に実行するexecute_sql_query()を作成します。
まずはexecute_sql_query()を作成するための全体のコードを掲載します！
create or replace procedure database_name.schema_name.execute_sql_query()
returns string
language javascript
as
$$
var my_sql_command = "";
var statement = "";
var ret = 0;
//////////////////////////////////////////////////////
//解説1 javascriptのプロシージャでSQL構文を実行する方法//
//////////////////////////////////////////////////////
my_sql_command = `
select sql_query_col from sql_query_table;
`;
statement = snowflake.createStatement( {sqlText: my_sql_command} );
ret = statement.execute();
var err_array = [];
var err_count = 0;
////////////////////////////////////////////////////////////////
//解説2 SELECTされたテーブルからSQLクエリを一つづつ取り出して実行//
////////////////////////////////////////////////////////////////
while(ret.next()){
try{
my_sql_command = ret.SQL_QUERY_COL;
statement = snowflake.createStatement( {sqlText: my_sql_command} );
statement.execute()
}
catch(err){
//失敗したSQLクエリを格納
err_array.push(ret.SQL_QUERY_COL);
err_count += 1;
}
}
//実行でエラーが生じたSQLクエリの数を出力する
//err_count: 0のとき正常、1~のときはどこかで失敗が生じている。
return 'err create num: ' + count;
$$
;プロシージャの実行方法は次になります。callを使ってプロシージャを呼び出します。
call database_name.schema_name.execute_sql_query()
解説1 javascriptのプロシージャでSQL構文を実行する方法
まず、一斉に実行したいSQLクエリをテーブルから取り出します。
一斉に実行したいSQLクエリはSQL_QUERY_TABLEテーブルの中のSQL_QUERY_COLカラムの中にあるので、select SQL_QUERY_COL from SQL_QUERY_TABLE;とすれば取り出せそうですね！
取り出す対象のコード部分はこちらです！
my_sql_command = `
select sql_query_col from sql_query_table;
`;
statement = snowflake.createStatement( {sqlText: my_sql_command} );
ret = statement.execute();my_sql_commandに実行したいSQLクエリの文字列を格納して、createStatementメソッドでSQLクエリの文字列を型変換して、executeで実行するイメージです。
この3コードはSnowflakeのプロシージャ内でSQLクエリを実行するためのおまじない構文みたいな感じです。あまり深く考えずにコピペして下さい！詳しくは、JavaScript でのストアドプロシージャの記述 — Snowflake Documentation
5行目のexecuteで実行されると、実行されたSELECTの結果がretに格納されます！
一斉に実行したいSQLクエリはretに格納されているはずです。
次はretからSELECTされたSQL_QUERY_COLを取り出してみましょう！
解説2 SELECTされたテーブルからSQLクエリを一つづつ取り出して実行
どうやらret.next()を処理するたびに、SELECTされた1行目->2行目->3行目…とレコードを取り出せるようです。
それならwhile(ret.next())のようにwhile文で表現することで、最終レコードまでレコードを繰り返し取り出すことができそうです！
また、ret.SQL_QUERY_COLで現在のレコードのSQL_QUERY_COLカラムの値が取り出せるようです。
一斉に実行したいSQLクエリを一つづつ取り出して実行するコード部分はこちらです！
while(ret.next()){
try{
my_sql_command = ret.SQL_QUERY_COL;
statement = snowflake.createStatement( {sqlText: my_sql_command} );
statement.execute()
}こうすることで、while文で繰り返しされるごとに、nレコード目のSQL_QUERY_COLの中にある一斉に実行したいSQLクエリの一つがmy_sql_commandに格納されます！
最後に『解説1』でご紹介したSQLクエリを実行するための3コードを書いてあげることで、SQL_QUERY_COLに格納されていた複数のSQLクエリがまとめて実行されます！
SQL構文をまとめて実行するプロシージャの用途は？
繰り返し同じSQL文を実行する際に便利だと思います。
例えば、当社のサービス『PREPPER』では、テーブルを元にビューを提供していますが、
CREATE VIEW構文を用いてビューを作成する過程は、プロシージャによって自動化しています。
同じSQL文を数十回書き換えて実行しようとすると、どうしてもスペルミスが生じてエラーが多発し、イライラすることも少なくありません。
ですので、一斉に実行する操作をプロシージャで自動化することで、ストレスフリーな開発に一歩近づくのではないかと考えています！
おわりに
snowflakeには、今回ご紹介したプロシージャやプロシージャで使うデータベースやテーブルなどを、ユーザーに共有する Native Application機能もあります！
Native Applicationに関するmimura先生のブログもありますので、こちらもどうぞ！
初めての Snowflake NativeApplication
本ブログでは、javascriptでプロシージャを書きましたが、どうやらSQLやPythonとかでも書くことができるようです！次はPythonで書いてみたい！
最後まで読んでいただきありがとうございました！
