【Snowflake】ストアド ~ デバッグログについて ~

■ はじめに

https://dk521123.hatenablog.com/entry/2022/12/11/202904
https://dk521123.hatenablog.com/entry/2022/12/16/143349
https://dk521123.hatenablog.com/entry/2022/12/17/000217

で、 Snowflake の ストアドプロシージャ(Stored Procedure) を
やってきたが、少し複雑な処理をする際に、
デバッグログを使いたいなっと思って調べてみた。
 => Snowflakeスクリプト/JavaScript の場合は
  デバッグログ機能がないので、代替案も記載しておく。

目次

【0】Snowflake の ストアド の デバッグログについて
 1)Python/Java/Scala の場合
 2)Snowflakeスクリプト/JavaScript の場合
【1】デバッグ機能・代替案1:簡易版として return でログ内容を返す
 1)サンプル
【2】デバッグ機能・代替案2:ログ用テーブルを独自に用意する
 1)サンプル
 2)補足:本番で使用したい場合

【0】Snowflake の ストアド の デバッグログについて

1)Python/Java/Scala の場合

 以下の公式ドキュメントから、
ログ機能は普通に使えそう

Python
https://docs.snowflake.com/ja/developer-guide/snowpark/python/troubleshooting.html#changing-the-logging-settings
Java
https://docs.snowflake.com/ja/developer-guide/snowpark/java/troubleshooting.html#changing-the-logging-settings
Scala
https://docs.snowflake.com/ja/developer-guide/snowpark/scala/troubleshooting.html#changing-the-logging-settings

2)Snowflakeスクリプト/JavaScript の場合

ログ機能としては、なさそう、、、
 => 以下、デバッグ機能の代替案を記す。

【1】デバッグ機能・代替案1:簡易版として return でログ内容を返す

 ストアドの return 文がなく、
ストアドが終わってから内容を表示してもいい場合、
return でログ内容を返し、結果を表示する

1)サンプル

https://dk521123.hatenablog.com/entry/2022/12/16/143349

-- の「例1:FOR文でのループ」をベースに、、、

execute immediate $$
declare
  counter integer default 0;
  max_count integer default 10;
  result varchar default ''; -- ★追加
begin
  -- FOR Loop
  for i in 1 to max_count do
    counter := counter + 1;
    result := result || 'index: ' || i::varchar || ', counter : ' || counter::varchar || '\n'; -- ★追加(ログ内容)
  end for;
  return result; -- ★修正
end;
$$
;

【2】デバッグ機能・代替案2:ログ用テーブルを独自に用意する

 Snowflake の公式ドキュメントや
他の海外サイトでも書かれている方法。

 => 公式ドキュメント(JavaScript)では、
 「エラーのログ」「エラーのログ(バージョン2)」 があり、
  その内容を確認すると、ログ用のテーブルを自分で用意して
  そのテーブルにログ内容を入れているだけ、、、

エラーのログ
https://docs.snowflake.com/ja/sql-reference/stored-procedures-javascript.html#logging-an-error
エラーのログ(バージョン2)
https://docs.snowflake.com/ja/sql-reference/stored-procedures-javascript.html#logging-an-error-version-2

1)サンプル

-- [1] ログ用テーブルを独自に用意する
CREATE OR REPLACE TABLE custom_log (
  error_code timestamp default current_timestamp(),
  log_type string default 'ERROR',
  message string
);

-- [2] ストアド
execute immediate $$
declare
  counter integer default 0;
  max_count integer default 10;
begin
  -- FOR Loop
  for i in 1 to max_count do
    counter := counter + 1;

    -- ★追加(ログ内容)
    let log_message := 'index: ' || i::varchar || ', counter : ' || counter::varchar || '\n';
    -- ★追加(挿入)
    insert into custom_log (log_type, message) values ('INFO', :log_message);
  end for;
  return counter;
end;
$$
;

2)補足:本番で使用したい場合

本番で使用したい場合は、以下を検討した方がいい。
~~~~~~~~~~~
[1] ログ機能用のストアドの検討
[2] ログメッセージを文字列に蓄積してINSERTすることを検討
~~~~~~~~~~~

[1] ログ機能用のストアドの検討

上記のサンプルでは、直接、insert文を書いているが
以下の公式ドキュメントの「エラーのログ(バージョン2)」のように
ログ機能用のストアドを用意した方がいい。

https://docs.snowflake.com/ja/sql-reference/stored-procedures-javascript.html#logging-an-error-version-2

-- より一部抜粋 (JavaScript)

CREATE or replace PROCEDURE do_log(MSG STRING)
    RETURNS STRING
    LANGUAGE JAVASCRIPT
    EXECUTE AS CALLER
    AS $$
 
    // See if we should log - checks for session variable do_log = true.
    try {
       var stmt = snowflake.createStatement( { sqlText: `select $do_log` } ).execute();
    } catch (ERROR){
       return ''; //swallow the error, variable not set so don't log
    }
    stmt.next();
    if (stmt.getColumnValue(1)==true){ //if the value is anything other than true, don't log
       try {
           snowflake.createStatement( { sqlText: `create temp table identifier ($log_table) if not exists (ts number, msg string)`} ).execute();
           snowflake.createStatement( { sqlText: `insert into identifier ($log_table) values (:1, :2)`, binds:[Date.now(), MSG] } ).execute();
       } catch (ERROR){
           throw ERROR;
       }
    }
 $$
;

-- 略。。。

    var accumulated_log_messages = '';

    // Define the SP call as a function - it's cleaner this way.
    // Add this function to your stored procs.
    function log(msg) {
        snowflake.createStatement( { sqlText: `call do_log(:1)`, binds:[msg] } ).execute();
    }

-- 略...

log(accumulated_log_messages);

-- 略...

[2] ログメッセージを文字列に蓄積してINSERTすることを検討

ある程度、ログメッセージを文字列に蓄積してINSERTすることを検討する
 => 結局、これって「【1】デバッグ機能・代替案1」とあまり変わらない、、、
 => 重い処理をストアドで実行し、どこまで実行しているかを確認したい場合、
  公式ドキュメントに記載されている「ストアドプロシージャが終了するまで」
  だと、終了するまでは出力されないので、確認できない
 => 「ある一定ログ数又は一定処理が完了でINSERTする」なども考えられるが
  その場合、複雑化し可読性が落ちる危険性があるので、悩ましいところ、、、

https://docs.snowflake.com/ja/sql-reference/stored-procedures-javascript.html#logging-an-error-version-2

より抜粋
~~~~~~~~~~~~~~~
!重要
テーブルにログメッセージを個別に挿入すると、
コストがかかり非効率になる可能性があります。
特に、コードが処理された行ごとに、
1つ以上のメッセージをログに記録する場合はそうです。

さらに、多くのストアドプロシージャが同時に実行され、
それぞれが同じログテーブルに多くのメッセージを追加する場合は、
同時実行のボトルネックが発生する可能性があります。

これらの潜在的な問題を回避するために、
上記のサンプルコードは、ストアドプロシージャが終了するまで
(またはエラーがキャッチされるまで)メッセージを文字列に蓄積し、
蓄積されたメッセージを単一の INSERT に書き込みます。
~~~~~~~~~~~~~~~

関連記事

Snowflake ~ 基礎知識編 ~
https://dk521123.hatenablog.com/entry/2021/11/02/130111
Snowflake ~ 入門編 / Hello world
https://dk521123.hatenablog.com/entry/2021/11/22/212520
Snowflake ~ 基本編 / データ型 ~
https://dk521123.hatenablog.com/entry/2021/12/16/095524
Snowflake ~ 日時関連 ~
https://dk521123.hatenablog.com/entry/2022/06/17/113003
Snowflake ~ 文字列操作関連 ~
https://dk521123.hatenablog.com/entry/2022/10/01/000000
Snowflake】ストアド ~ 入門編 ~
https://dk521123.hatenablog.com/entry/2022/12/11/202904
Snowflake】ストアド ~ 基本編 / 条件分岐 ~
https://dk521123.hatenablog.com/entry/2022/12/26/211349
Snowflake】ストアド ~ 基本編 / ループ ~
https://dk521123.hatenablog.com/entry/2022/12/16/143349
Snowflake】ストアド ~ EXECUTE IMMEDIATE ~
https://dk521123.hatenablog.com/entry/2022/12/17/000217
Snowflake】ストアド ~ 例外 / EXCEPTION ~
https://dk521123.hatenablog.com/entry/2022/12/23/223345
ストアド ~ 基礎知識編 ~
https://dk521123.hatenablog.com/entry/2010/08/06/112528