【Snowflake】プロシージャで作業を自動化！SQLをまとめて実行してみた！

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で書いてみたい！

最後まで読んでいただきありがとうございました！