Snowflake の重いクエリと失敗を、画面を開かずに毎週点検する｜クエリ履歴を似たクエリごとに記録し、基準を超えた週だけメールで知らせる

ウェアハウスの使用量が増えてきたとき、どのクエリが重いのか、失敗が増えていないかを、社内で誰か確かめているでしょうか。クエリ履歴の画面を開いて眺める点検は、担当が画面を開いた週にしか行われません。重いクエリが見つかっても、誰のどの処理か分からなければ、直す相手が決まりません。

やることは、点検を SQL にして週に1回自動で動かし、基準を超えた週だけメールで知らせることです。ただ、集計の結果だけでは誰の処理か分からないことがあるので、クエリタグの付け方と、メールが来た週に人が確かめる順も一緒に決めます。

Snowflake の導入を手伝っている当社の試用のアカウントには、試しに実行したクエリと、ほかの検証のクエリの記録がたまっていました。画面は、その記録とウェアハウスの使用量を集計した結果と、アラートを動かした結果です。

重いクエリは1本ずつではなく、似たクエリのまとまりで数える

クエリの実行の記録は、QUERY_HISTORY ビューから集計します。SNOWFLAKE データベースの ACCOUNT_USAGE スキーマにあるビューです。過去365日分が残り、ビューへの反映は最大45分遅れます。

Snowsight のクエリ履歴の画面で見られるのは、過去14日までです。INFORMATION_SCHEMA にも、同じ名前の QUERY_HISTORY があります。FROM 句でテーブルのように使う関数で、テーブル関数と呼ばれ、さかのぼれるのは過去7日までです。このテーブル関数は、ストアドプロシージャの中からは実行できません。

ストアドプロシージャは、いくつもの SQL 文をまとめて Snowflake に保存し、CALL で呼び出す仕組みです。毎週の集計はストアドプロシージャにするので、ビューのほうを使います。

似たクエリをまとめるには、QUERY_PARAMETERIZED_HASH の列を使います。SQL の文から Snowflake が計算した値で、以下ではハッシュ値と呼びます。条件に =・!=・>=・<= で書いた値だけが違うクエリは、同じハッシュ値になります。ハッシュ値が同じクエリを、ここでは1つのまとまりとして数えます。

店の番号だけを変えた同じ集計で、確かめました。行を作る関数 GENERATOR で500万行の売上のテーブルを作り、店1・店2・店3の合計を出す SELECT を1回ずつ実行しています。その前に、ALTER SESSION でクエリタグ weekly_review_demo を付けました。クエリタグは、クエリ履歴に一緒に記録される目印の文字列です。

検証用のデータベースは TEST_REVIEW という名前にしました。本番のデータベースと分けておけば、試したあとで丸ごと削除できます。

CREATE DATABASE IF NOT EXISTS TEST_REVIEW;
CREATE SCHEMA IF NOT EXISTS TEST_REVIEW.RAW;
USE SCHEMA TEST_REVIEW.RAW;
ALTER SESSION SET QUERY_TAG = 'weekly_review_demo';
CREATE OR REPLACE TABLE SALES AS
  SELECT SEQ4() AS ID, MOD(SEQ4(), 50) AS STORE_ID, UNIFORM(1, 10000, RANDOM()) AS AMOUNT,
         DATEADD(DAY, -MOD(SEQ4(), 365), '2026-10-20'::DATE) AS SALE_DATE
  FROM TABLE(GENERATOR(ROWCOUNT => 5000000));
SELECT STORE_ID, SUM(AMOUNT) FROM SALES WHERE STORE_ID = 1 GROUP BY STORE_ID;
SELECT STORE_ID, SUM(AMOUNT) FROM SALES WHERE STORE_ID = 2 GROUP BY STORE_ID;
SELECT STORE_ID, SUM(AMOUNT) FROM SALES WHERE STORE_ID = 3 GROUP BY STORE_ID;
-- このあと、COUNT(DISTINCT …)、自己結合、存在しないテーブルへの SELECT も1回ずつ実行した

そのあと、タグで絞り、ハッシュ値ごとにまとめました。時間の列 TOTAL_ELAPSED_TIME はミリ秒なので、1000で割って秒にしています。

SELECT LEFT(QUERY_PARAMETERIZED_HASH, 8) AS PARAM_HASH, QUERY_TYPE, EXECUTION_STATUS, COUNT(*) AS RUNS,
       ROUND(SUM(TOTAL_ELAPSED_TIME) / 1000, 1) AS TOTAL_SEC, ROUND(MAX(TOTAL_ELAPSED_TIME) / 1000, 1) AS MAX_SEC,
       ROUND(SUM(BYTES_SCANNED) / 1024 / 1024, 1) AS SCANNED_MB,
       SUM(BYTES_SPILLED_TO_LOCAL_STORAGE) AS SPILLED_LOCAL, SUM(BYTES_SPILLED_TO_REMOTE_STORAGE) AS SPILLED_REMOTE
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE QUERY_TAG = 'weekly_review_demo'
GROUP BY 1, 2, 3
ORDER BY TOTAL_SEC DESC;

3回の集計は、cdfb680b で始まるハッシュ値の1行にまとまりました。RUNS は3、スキャンしたデータの量は合わせて35.9MB です。

ただ、結果は58行ありました。タグを付けてから実行した文は、7つだけです。行が多すぎた理由は、タグの付き方にありました。

なお、ハッシュ値の計算のしかたは Snowflake が変えることがあり、変わると同じクエリでも値が変わります。記録テーブルにはハッシュ値だけを残します。週をまたいで値が変わったら、確かめる SELECT で QUERY_PARAMETERIZED_HASH_VERSION の列も見ます。

外し忘れたクエリタグは、そのあとの119件のクエリにも付いていた

タグの付いたクエリについて、最初と最後の時刻と件数を出しました。その前に、ALTER SESSION UNSET QUERY_TAG でタグを外しています。

ALTER SESSION UNSET QUERY_TAG;
SELECT 'weekly_review_demo' AS TAG, MIN(START_TIME) AS FIRST_AT, MAX(START_TIME) AS LAST_AT, COUNT(*) AS QUERIES,
       COUNT_IF(EXECUTION_STATUS = 'FAIL') AS FAILED
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE QUERY_TAG = 'weekly_review_demo';

タグの付いたクエリは119件で、うち3件が失敗でした。最初は9時23分、最後は9時35分です。9時23分にタグを付けたあと、同じワークシートで、ほかの検証のクエリを9時35分まで続けて実行していました。ワークシートは、Snowsight で SQL を書いて実行する画面です。QUERY_TAG はセッションのパラメーターなので、外すまでは、そのセッションのクエリすべてに付きます。

ハッシュ値ごとにまとめた結果の SPILLED_LOCAL と SPILLED_REMOTE は、スピルの量です。スピルは、メモリに収まらないデータをディスクに書き出すことです。スピルが起きると性能が大きく落ちる、とドキュメントは説明しています。あとで作る記録テーブルにスピルの量の列を置くのは、このためです。

タグを付けた処理の終わりには、ALTER SESSION UNSET QUERY_TAG を実行します。タグには、処理の名前を入れます。点検の結果を見た人が、その名前から誰に聞けばよいか分かるようにするためです。たとえば、毎朝の取り込み、月次の集計、BI の画面ごとに1つずつ決めます。名前と値をつないだ COST_CENTER=finance のような形がドキュメントの例で、長さは2000文字までです。

タスクは、決めた時刻に SQL を実行する Snowflake の仕組みです。CREATE TASK には、セッションのパラメーターとして QUERY_TAG を書けます。そのタスクの実行のときだけ付くので、外す文は要らないはずです。

BI ツールのように、人が ALTER SESSION を実行できない接続もあります。QUERY_TAG は、アカウント・ユーザー・セッションのどの単位でも設定できます。BI ツールが専用のユーザーで接続しているなら、そのユーザーに ALTER USER で設定する形が考えられます。

記録テーブルに残すのは、重い上位5つ・失敗のエラーコード・ウェアハウスのクレジットの3種類

ここからは、毎週の集計と知らせを、タスクとアラートに任せる一式です。アラートは、決めた時刻に条件の SQL を実行し、当てはまる行があれば決めた処理を実行する仕組みです。月曜の朝にタスクが先週の分を集計して記録テーブルに書き、そのあとアラートが基準を超えた行を探して、メールを送ります。

集計はストアドプロシージャ WEEKLY_QUERY_REVIEW にまとめ、結果を記録テーブル QUERY_REVIEW_LOG に書きます。行は KIND の列で3種類に分け、SLOW_GROUP・FAILED・WAREHOUSE と名前を付けました。KIND の名前は当社が決めたものです。

SLOW_GROUP は、成功したクエリをハッシュ値ごとにまとめ、合計時間の長い順に5つを残します。ウェアハウスを使わずに終わったクエリは WAREHOUSE_NAME が空なので、外しました。1回は短くても、回数が多ければ合計は長くなります。そのため、並べるのは合計時間の順です。

FAILED は、失敗したクエリをエラーコードごとに数えます。残すのはコードと件数だけで、エラー文とクエリの SQL 文は残しません。試したとき、構文エラーのエラー文には、クエリの中の語が含まれていました。クエリに顧客の名前や金額を書いていれば、エラー文にも出るおそれがあります。点検の結果は、クエリを書いた本人以外も見るので、記録には写しません。

RUNS の列は、KIND によって意味が変わります。SLOW_GROUP では実行の回数、FAILED では失敗の件数、WAREHOUSE では使用のあった1時間の枠の数です。

EXECUTION_STATUS の値は、ドキュメントでは小文字で書かれています。試した画面では SUCCESS・FAIL と大文字で出たので、条件も大文字で書きました。

試すときは、当社が試した版を検証用の TEST_REVIEW.RAW に作ります。次の SQL は、記録テーブルとストアドプロシージャを作り、CALL して中身を出すまでの文です。当社が実行した文と同じで、名前だけを TEST_REVIEW に置き換えています。この版は、実行した時点の7日前から、実行した時点までを集計します。記事に出る27行は、この版で CALL した結果です。区切りを変えた版は後半の本番の一式にだけ載せていて、試していません。

USE SCHEMA TEST_REVIEW.RAW;
CREATE OR REPLACE TABLE QUERY_REVIEW_LOG (REVIEWED_AT TIMESTAMP_LTZ, WEEK_FROM TIMESTAMP_LTZ, KIND STRING, ITEM STRING, RUNS NUMBER, TOTAL_SEC NUMBER(18,1), MAX_SEC NUMBER(18,1), SCANNED_MB NUMBER(18,1), SPILLED_BYTES NUMBER, CREDITS NUMBER(18,3));
CREATE OR REPLACE PROCEDURE WEEKLY_QUERY_REVIEW()
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
  week_from TIMESTAMP_LTZ;
BEGIN
  week_from := DATEADD(DAY, -7, CURRENT_TIMESTAMP());
  INSERT INTO QUERY_REVIEW_LOG (REVIEWED_AT, WEEK_FROM, KIND, ITEM, RUNS, TOTAL_SEC, MAX_SEC, SCANNED_MB, SPILLED_BYTES)
    SELECT CURRENT_TIMESTAMP(), :week_from, 'SLOW_GROUP', QUERY_PARAMETERIZED_HASH, COUNT(*),
           ROUND(SUM(TOTAL_ELAPSED_TIME) / 1000, 1), ROUND(MAX(TOTAL_ELAPSED_TIME) / 1000, 1),
           ROUND(SUM(BYTES_SCANNED) / 1024 / 1024, 1), SUM(BYTES_SPILLED_TO_LOCAL_STORAGE + BYTES_SPILLED_TO_REMOTE_STORAGE)
    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
    WHERE START_TIME >= :week_from AND EXECUTION_STATUS = 'SUCCESS' AND WAREHOUSE_NAME IS NOT NULL
    GROUP BY QUERY_PARAMETERIZED_HASH
    ORDER BY SUM(TOTAL_ELAPSED_TIME) DESC
    LIMIT 5;
  INSERT INTO QUERY_REVIEW_LOG (REVIEWED_AT, WEEK_FROM, KIND, ITEM, RUNS)
    SELECT CURRENT_TIMESTAMP(), :week_from, 'FAILED', ERROR_CODE, COUNT(*)
    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
    WHERE START_TIME >= :week_from AND EXECUTION_STATUS = 'FAIL'
    GROUP BY ERROR_CODE;
  INSERT INTO QUERY_REVIEW_LOG (REVIEWED_AT, WEEK_FROM, KIND, ITEM, RUNS, CREDITS)
    SELECT CURRENT_TIMESTAMP(), :week_from, 'WAREHOUSE', WAREHOUSE_NAME, COUNT(*), ROUND(SUM(CREDITS_USED), 3)
    FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
    WHERE START_TIME >= :week_from
    GROUP BY WAREHOUSE_NAME;
  RETURN 'ok';
END;
$$;
CALL WEEKLY_QUERY_REVIEW();
SELECT KIND, LEFT(ITEM, 12) AS ITEM, RUNS, TOTAL_SEC, MAX_SEC, SCANNED_MB, SPILLED_BYTES, CREDITS FROM QUERY_REVIEW_LOG ORDER BY KIND, TOTAL_SEC DESC NULLS LAST, RUNS DESC;

CALL は、タグで絞らずに、アカウント全体の直近7日を集計しています。記録テーブルには27行が書かれ、FAILED が20行、SLOW_GROUP が5行、WAREHOUSE が2行でした。失敗でいちばん多かったのは、エラーコード 0042127 の12件で、次が 002003 の3件です。002003 には、試しに実行した、存在しないテーブルへの SELECT の失敗も入っています。

1757 と 001757、1015 と 001015 は、別の行に分かれていました。同じ意味のコードかどうかは、確かめていません。同じ意味なら、合わせると基準の件数に届く週もあります。ドキュメントの列の一覧では、ERROR_CODE は NUMBER と書かれています。値が分かれた理由は分かっていません。

ウェアハウスのクレジットは、クエリ履歴とは別のビューから週の合計を残す

ウェアハウスの使用量は、ACCOUNT_USAGE の WAREHOUSE_METERING_HISTORY ビューにあります。ウェアハウスごとに1時間ずつ行があり、過去365日分が残ります。反映の遅れは最大3時間です。ただし、クラウドサービスのクレジットの列 CREDITS_USED_CLOUD_SERVICES だけは、最大6時間遅れます。

記録テーブルには、CREDITS_USED の合計を残しました。計算のクレジットとクラウドサービスのクレジットを足した値です。今回の CALL では、COMPUTE_WH は使用のあった1時間の枠が9つで、合計3.412クレジットでした。WAREHOUSE_NAME が CLOUD_SERVICES_ONLY の行も入り、枠が15個で0.003クレジットでした。何を表す行かは、今回は確かめていません。

ドキュメントによると、CREDITS_USED は請求される量より大きいことがあります。この一式では週ごとの増え方を見るために残し、基準は置いていません。クレジットに上限を決めて、止めたり知らせたりするのはリソースモニターの役目です。料金の見積もりや請求とずれる所とあわせて、料金とコストの記事で説明しています。

最大667.7秒のまとまりが何の処理かは、ハッシュ値だけでは分からなかった

SLOW_GROUP の1つ目は、7回で合計3,965.6秒、最大667.7秒のまとまりでした。記録テーブルに残るのはハッシュ値と回数と時間で、どの処理のクエリなのかは、ここからは分かりませんでした。上位5つは、スキャンしたデータの量がどれも0.0MBでした。経過時間が長くても、データを多く処理していたとは限りません。重さを見るときは、時間と一緒に SCANNED_MB も並べて見ます。

3つ目と4つ目は、どちらも830回のまとまりで、1回あたりは長くても7.0秒です。Snowsight の画面を開いているあいだに裏で実行されるクエリではないか、と当社は見ていますが、確かめていません。

上位のまとまりと失敗の行は、次の図の順に確かめます。

1〜6は、クエリ履歴を、ハッシュ値と週、またはエラーコードと週で絞る SELECT で見られます。そのため、SQL 文を出さずに済みます。SELECT は、一式の (10) と (11) に載せました。失敗の行は、エラーコードで絞り、QUERY_ID・開始時刻・ロール・ウェアハウス・クエリタグを出します。試しでアラートに入った 0042127 の12件が何だったかは、確かめていません。

SQL 文は、Snowsight のクエリ履歴の画面で開きます。条件には Parameterized Query Hash か Query ID を入れます。Query Profile は、そのクエリの実行計画と、段ごとにかかった時間を見る画面です。画面で SQL 文を開けるのは、そのクエリを実行した本人か、ACCOUNTADMIN のように全員のクエリを見られるロールの人です。ウェアハウスに MONITOR か OPERATE の権限があれば、そのウェアハウスでほかの人が実行したクエリも見られます。GOVERNANCE_VIEWER のデータベースロールだけでは、ほかのユーザーの個々のクエリは画面に出ません。SQL 文を開くのは上位の数件だけにして、クエリを書いた人と一緒に見ます。

何の処理か分かったら、扱いを3つから決めます。そのままでよい処理か、書いた人に直してもらう処理か、ほかの処理と別のウェアハウスに分ける処理かです。

基準を超えた週だけ、アラートが行を書き出し、メールを送る

アラートは毎週月曜の9時に、直近1日に書かれた記録テーブルの行を調べます。SLOW_GROUP の最大時間が600秒以上の行か、FAILED の件数が10件以上の行を探します。あれば、その行を QUERY_REVIEW_ALERTS に書き出します。QUERY_REVIEW_ALERTS には、月曜に人が見る行だけが入ります。書き出したあと、行の件数を書いたメールを送ります。

記録は合計時間で並べ、知らせは最大時間で決めています。合計は、ウェアハウスの時間をどこが使ったかを見るためです。最大は、1回の実行を待っている人がいるかを見るためです。ただ、この組み合わせでは、合計で上位5つに入らない1回だけの長いクエリは、記録にも知らせにも出ません。

条件の SQL は記録テーブルだけを見るので、ACCOUNT_USAGE の遅れを考えるのは、集計のストアドプロシージャの側だけで済みます。

作ったばかりのアラートは一時停止の状態で、予定の時刻には動きません。試すときは、EXECUTE ALERT で1回だけ実行します。EXECUTE ALERT で実行しても、アラートは一時停止のままです。次の SQL は、当社が試した版のアラートです。メールは送らず、QUERY_REVIEW_ALERTS に行を書くだけの形で、記事に出る4行はこの版の結果です。

USE SCHEMA TEST_REVIEW.RAW;
CREATE OR REPLACE TABLE QUERY_REVIEW_ALERTS (DETECTED_AT TIMESTAMP_LTZ, KIND STRING, ITEM STRING, RUNS NUMBER, MAX_SEC NUMBER(18,1));
CREATE OR REPLACE ALERT CHECK_WEEKLY_QUERIES WAREHOUSE = COMPUTE_WH SCHEDULE = 'USING CRON 0 9 * * 1 Asia/Tokyo'
IF (EXISTS (SELECT 1 FROM QUERY_REVIEW_LOG WHERE REVIEWED_AT >= DATEADD(DAY, -1, CURRENT_TIMESTAMP()) AND ((KIND = 'SLOW_GROUP' AND MAX_SEC >= 600) OR (KIND = 'FAILED' AND RUNS >= 10))))
THEN INSERT INTO QUERY_REVIEW_ALERTS SELECT CURRENT_TIMESTAMP(), KIND, ITEM, RUNS, MAX_SEC FROM QUERY_REVIEW_LOG WHERE REVIEWED_AT >= DATEADD(DAY, -1, CURRENT_TIMESTAMP()) AND ((KIND = 'SLOW_GROUP' AND MAX_SEC >= 600) OR (KIND = 'FAILED' AND RUNS >= 10));
EXECUTE ALERT CHECK_WEEKLY_QUERIES;

結果は、EXECUTE ALERT とは分けて、あとから次の SELECT で出しました。アラートの実行履歴と QUERY_REVIEW_ALERTS を、1つの結果にまとめています。V1 には状態か KIND、V2 にはアラートのエラー文か ITEM、V3 には件数が入ります。AT_JST には、1行目は時刻、2行目から下は MAX_SEC が入ります。

USE SCHEMA TEST_REVIEW.RAW;
SELECT 'ALERT' AS SRC, STATE AS V1, COALESCE(SQL_ERROR_MESSAGE, '') AS V2, NULL AS V3, TO_CHAR(CONVERT_TIMEZONE('Asia/Tokyo', SCHEDULED_TIME), 'HH24:MI:SS') AS AT_JST
FROM TABLE(INFORMATION_SCHEMA.ALERT_HISTORY(SCHEDULED_TIME_RANGE_START => DATEADD(MINUTE, -15, CURRENT_TIMESTAMP()))) WHERE NAME = 'CHECK_WEEKLY_QUERIES'
UNION ALL
SELECT 'ALERTS_TABLE', KIND, LEFT(ITEM, 12), RUNS, TO_CHAR(MAX_SEC) FROM QUERY_REVIEW_ALERTS;

結果の5行のうち、1行目がアラートの実行履歴で、残りの4行が QUERY_REVIEW_ALERTS に書かれた行です。アラートの実行履歴の STATE は TRIGGERED で、条件に当てはまって処理が実行されたことを表します。時刻は10時5分32秒でした。書かれた4行は、エラーコード 0042127 の12件と、最大667.7秒・665.3秒・607.7秒のまとまりです。002003 の3件は、10件に届かないので入っていません。

600秒と10件は、試すために決めた値です。自社の基準は、記録テーブルに数週分の行がたまってから、ふだんの最大時間と失敗の件数を見て決めます。

メールを送る部分は、毎朝の取り込みの結果を知らせる記事で実行した形を変えて使いました。この記事の点検では、メールは送っていません。通知インテグレーションは、Snowflake からメールを送るための設定です。宛先にできるのは、同じアカウントの Snowflake のユーザーで、メールアドレスを確認済みの人だけです。受け取り用のユーザーと通知インテグレーションの作り方は、そちらの記事に書きました。

Snowflake のメールは AWS の仕組みを通って送られ、中身が Snowflake の側に最大30日残ることがあるとドキュメントにあります。そのため、メールの本文には件数だけを書き、ハッシュ値やクエリの SQL 文は入れません。

検証用の TEST_REVIEW は、アラートを一時停止してから削除します。

ALTER ALERT TEST_REVIEW.RAW.CHECK_WEEKLY_QUERIES SUSPEND;
-- データ保持期間を0日にして、削除したあとに戻せる期間を残さない
ALTER DATABASE TEST_REVIEW SET DATA_RETENTION_TIME_IN_DAYS = 0;
DROP DATABASE TEST_REVIEW;

週の区切りは、反映の遅れの分だけ前にずらして切る

試したとき、9時35分に実行したクエリは、9時58分にはビューに出ていました。ドキュメントにある遅れの最大は45分で、今回はその内側でした。1回見ただけなので、いつもこの速さとは限りません。

試した版のストアドプロシージャは、実行した時点の7日前から、実行した時点までを集計していました。この切り方では、実行する直前のクエリが、まだビューに出ていないことがあります。翌週の集計は翌週の実行時点の7日前から始まるので、その分はどちらの週にも入りません。

そこで一式では、週の終わりを、実行の時刻より6時間前の正時にしました。始まりは、その7日前です。月曜の8時に実行すると、先週の月曜2時から今週の月曜2時までになります。6時間は、クラウドサービスのクレジットの列の遅れの最大に合わせた値です。

7日前を出す計算は、時刻の型によっては夏時間の影響を受けます。TIMESTAMP_LTZ は時点を UTC で持つ型です。TIMESTAMP_LTZ の計算はセッションのタイムゾーンで行われる、とドキュメントにあります。TIMESTAMP_TZ は、時点と一緒に時差を持ち、計算もその時差で行う型です。

タスクのセッションでタイムゾーンを決めていなければ、アカウントの設定が使われるはずです。その設定が夏時間のある地域だと、LTZ のまま7日戻した週は、切り替わりをまたぐと区切りが1時間ずれるはずです。そのため一式では、時差を値ごとに持つ TIMESTAMP_TZ にしてから7日戻しています。

正時で切るので、タスクが毎週8時台に動くかぎり、週と週の間はすき間なくつながるはずです。手で CALL し直すときも、8時台に実行すれば同じ週になります。8時台以外に実行すると、区切りもずれます。

週に入れるかどうかは、クエリの開始時刻で決めています。週をまたいで動いたクエリは、集計の時点で終わっていれば、始まった週に入ります。区切りの前に始まり、集計の時点でまだ動いているクエリは、どちらの週にも入りません。

同じ週を二度集計しても行が重ならないように、書く前に、同じ WEEK_TO の行を消します。消すことと書くことは、1つのトランザクションにまとめました。トランザクションは、いくつかの文を、全部反映するか全部取り消すかのどちらかにする仕組みです。途中で失敗すれば、消した行も元に戻ります。

月曜8時のタスクと9時のアラートで動かす一式

毎週月曜の8時にタスクがストアドプロシージャを呼び、9時にアラートが記録テーブルを確かめる順に組みます。<…> の所は、自社の名前と値に書き換えます。今回の点検で実行した文は、名前と基準だけを書き換えて載せました。今回実行していない SQL の行には、行末に「試していない」と書いています。(3) のストアドプロシージャは、取り込みの記事で実行した形を変えたもので、全体を実行していません。宛先の data-team@example.com は、受け取り用のユーザーの確認済みのアドレスに書き換えます。

-- (1) ACCOUNTADMIN が実行する。通知インテグレーションは、取り込みの記事の手順で先に作っておく
GRANT DATABASE ROLE SNOWFLAKE.GOVERNANCE_VIEWER TO ROLE <点検のロール>;  -- 試していない
GRANT DATABASE ROLE SNOWFLAKE.USAGE_VIEWER TO ROLE <点検のロール>;  -- 試していない
GRANT EXECUTE TASK ON ACCOUNT TO ROLE <点検のロール>;  -- 試していない
GRANT EXECUTE ALERT ON ACCOUNT TO ROLE <点検のロール>;  -- 試していない
GRANT USAGE ON INTEGRATION <通知インテグレーション> TO ROLE <点検のロール>;  -- 試していない
-- BI ツールが接続に使うユーザーにタグを設定する。ユーザーを管理するロールで実行する
ALTER USER <BI ツールのユーザー> SET QUERY_TAG = '<BI ツールの名前>';  -- 試していない
-- 月曜に結果を見る人のロールへの付与。データベース・スキーマ・ウェアハウスの持ち主か、MANAGE GRANTS か WITH GRANT OPTION を持つロールでないと付与できないので、ここで実行する
GRANT USAGE ON DATABASE <データベース> TO ROLE <見る人のロール>;  -- 試していない
GRANT USAGE ON SCHEMA <データベース>.<スキーマ> TO ROLE <見る人のロール>;  -- 試していない
GRANT USAGE ON WAREHOUSE <見る人が使うウェアハウス> TO ROLE <見る人のロール>;  -- 試していない

-- (2) ここから下は <点検のロール> で実行する。記録テーブルと、週に1回の集計
USE ROLE <点検のロール>;  -- 試していない
USE SCHEMA <データベース>.<スキーマ>;
CREATE TABLE IF NOT EXISTS QUERY_REVIEW_LOG (REVIEWED_AT TIMESTAMP_LTZ, WEEK_FROM TIMESTAMP_LTZ, WEEK_TO TIMESTAMP_LTZ, KIND STRING, ITEM STRING, RUNS NUMBER, TOTAL_SEC NUMBER(18,1), MAX_SEC NUMBER(18,1), SCANNED_MB NUMBER(18,1), SPILLED_BYTES NUMBER, CREDITS NUMBER(18,3));  -- 試していない
CREATE OR REPLACE PROCEDURE WEEKLY_QUERY_REVIEW()
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
  week_from TIMESTAMP_LTZ;
  week_to TIMESTAMP_LTZ;  -- 試していない
BEGIN
  week_to := DATEADD(HOUR, -6, DATE_TRUNC('HOUR', CURRENT_TIMESTAMP()::TIMESTAMP_TZ));  -- 試していない
  week_from := DATEADD(DAY, -7, week_to::TIMESTAMP_TZ);  -- 試していない
  BEGIN TRANSACTION;  -- 試していない
  DELETE FROM QUERY_REVIEW_LOG WHERE WEEK_TO = :week_to;  -- 試していない
  INSERT INTO QUERY_REVIEW_LOG (REVIEWED_AT, WEEK_FROM, WEEK_TO, KIND, ITEM, RUNS, TOTAL_SEC, MAX_SEC, SCANNED_MB, SPILLED_BYTES)  -- 試していない
    SELECT CURRENT_TIMESTAMP(), :week_from, :week_to, 'SLOW_GROUP', QUERY_PARAMETERIZED_HASH, COUNT(*),  -- 試していない
           ROUND(SUM(TOTAL_ELAPSED_TIME) / 1000, 1), ROUND(MAX(TOTAL_ELAPSED_TIME) / 1000, 1),
           ROUND(SUM(BYTES_SCANNED) / 1024 / 1024, 1), SUM(BYTES_SPILLED_TO_LOCAL_STORAGE + BYTES_SPILLED_TO_REMOTE_STORAGE)
    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
    WHERE START_TIME >= :week_from AND START_TIME < :week_to AND EXECUTION_STATUS = 'SUCCESS' AND WAREHOUSE_NAME IS NOT NULL  -- 試していない
    GROUP BY QUERY_PARAMETERIZED_HASH
    ORDER BY SUM(TOTAL_ELAPSED_TIME) DESC
    LIMIT 5;
  INSERT INTO QUERY_REVIEW_LOG (REVIEWED_AT, WEEK_FROM, WEEK_TO, KIND, ITEM, RUNS)  -- 試していない
    SELECT CURRENT_TIMESTAMP(), :week_from, :week_to, 'FAILED', ERROR_CODE, COUNT(*)  -- 試していない
    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
    WHERE START_TIME >= :week_from AND START_TIME < :week_to AND EXECUTION_STATUS = 'FAIL'  -- 試していない
    GROUP BY ERROR_CODE;
  INSERT INTO QUERY_REVIEW_LOG (REVIEWED_AT, WEEK_FROM, WEEK_TO, KIND, ITEM, RUNS, CREDITS)  -- 試していない
    SELECT CURRENT_TIMESTAMP(), :week_from, :week_to, 'WAREHOUSE', WAREHOUSE_NAME, COUNT(*), ROUND(SUM(CREDITS_USED), 3)  -- 試していない
    FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
    WHERE START_TIME >= :week_from AND START_TIME < :week_to  -- 試していない
    GROUP BY WAREHOUSE_NAME;
  COMMIT;  -- 試していない
  RETURN 'ok';
END;
$$;

-- (3) 基準を超えた行を書き、メールを送る。取り込みの記事で実行した形を変えたもので、この記事の点検では実行していない
CREATE TABLE IF NOT EXISTS QUERY_REVIEW_ALERTS (DETECTED_AT TIMESTAMP_LTZ, KIND STRING, ITEM STRING, RUNS NUMBER, MAX_SEC NUMBER(18,1));
CREATE OR REPLACE PROCEDURE NOTIFY_QUERY_REVIEW()
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
  rows_n NUMBER;
  body STRING;
BEGIN
  INSERT INTO QUERY_REVIEW_ALERTS SELECT CURRENT_TIMESTAMP(), KIND, ITEM, RUNS, MAX_SEC FROM QUERY_REVIEW_LOG
    WHERE REVIEWED_AT >= DATEADD(DAY, -1, CURRENT_TIMESTAMP())
    AND ((KIND = 'SLOW_GROUP' AND MAX_SEC >= <秒>) OR (KIND = 'FAILED' AND RUNS >= <件数>));
  rows_n := SQLROWCOUNT;
  body := '先週のクエリの点検で、基準を超えた行が ' || rows_n || ' 件ありました。' || CHR(10) ||
          'テーブル QUERY_REVIEW_ALERTS の今日の行を確認してください。';
  CALL SYSTEM$SEND_EMAIL(
    '<通知インテグレーション>',
    'data-team@example.com',
    '<件名>',
    :body);
  RETURN 'sent';
END;
$$;

-- (4) アラート（月曜9時）。条件は試した形で、処理を (3) の CALL に変えた
CREATE OR REPLACE ALERT CHECK_WEEKLY_QUERIES WAREHOUSE = <ウェアハウス> SCHEDULE = 'USING CRON 0 9 * * 1 Asia/Tokyo'
IF (EXISTS (SELECT 1 FROM QUERY_REVIEW_LOG WHERE REVIEWED_AT >= DATEADD(DAY, -1, CURRENT_TIMESTAMP())
  AND ((KIND = 'SLOW_GROUP' AND MAX_SEC >= <秒>) OR (KIND = 'FAILED' AND RUNS >= <件数>))))
THEN CALL NOTIFY_QUERY_REVIEW();  -- 試していない

-- (5) 月曜8時のタスク。点検そのもののクエリにもタグを付ける
CREATE OR REPLACE TASK WEEKLY_QUERY_REVIEW_TASK  -- 試していない
  WAREHOUSE = <ウェアハウス>  -- 試していない
  SCHEDULE = 'USING CRON 0 8 * * 1 Asia/Tokyo'  -- 試していない
  QUERY_TAG = 'weekly_query_review'  -- 試していない
AS CALL WEEKLY_QUERY_REVIEW();  -- 試していない

-- (6) 月曜に結果を見る人のロールに、2つのテーブルの SELECT を付与する。テーブルの持ち主の点検のロールが付与できる
GRANT SELECT ON TABLE QUERY_REVIEW_LOG TO ROLE <見る人のロール>;  -- 試していない
GRANT SELECT ON TABLE QUERY_REVIEW_ALERTS TO ROLE <見る人のロール>;  -- 試していない

-- (7) 作ったら、または作り直したら、動かし始める
ALTER TASK WEEKLY_QUERY_REVIEW_TASK RESUME;  -- 試していない
ALTER ALERT CHECK_WEEKLY_QUERIES RESUME;  -- 試していない

-- (8) 人が手で実行する処理には、始めにタグを付け、終わったら外す
ALTER SESSION SET QUERY_TAG = '<処理の名前>';
ALTER SESSION UNSET QUERY_TAG;

-- (9) 月曜に見る。アラートの実行履歴は、点検のロール（アラートの持ち主）か ACCOUNTADMIN で実行する。ほかのロールでは0行になる
SELECT NAME, STATE, SQL_ERROR_MESSAGE, CONVERT_TIMEZONE('Asia/Tokyo', SCHEDULED_TIME) AS SCHEDULED_JST  -- 試していない
FROM TABLE(INFORMATION_SCHEMA.ALERT_HISTORY(SCHEDULED_TIME_RANGE_START => DATEADD(DAY, -1, CURRENT_TIMESTAMP()), SCHEDULED_TIME_RANGE_END => CURRENT_TIMESTAMP()))  -- 試していない
WHERE NAME = 'CHECK_WEEKLY_QUERIES';  -- 試していない
-- 書き出された行と、記録テーブルに今週の行があるか。見る人のロールでも実行できる
SELECT CONVERT_TIMEZONE('Asia/Tokyo', DETECTED_AT) AS DETECTED_JST, KIND, ITEM, RUNS, MAX_SEC FROM QUERY_REVIEW_ALERTS  -- 試していない
WHERE DETECTED_AT >= DATEADD(DAY, -1, CURRENT_TIMESTAMP()) ORDER BY KIND, MAX_SEC DESC;  -- 試していない
SELECT MAX(REVIEWED_AT) AS LAST_REVIEWED_AT, COUNT(*) AS ROWS_N FROM QUERY_REVIEW_LOG  -- 試していない
WHERE REVIEWED_AT >= DATEADD(DAY, -7, CURRENT_TIMESTAMP());  -- 試していない

-- (10) SLOW_GROUP の行を確かめる。GOVERNANCE_VIEWER を持つ点検のロールで実行する。クエリの SQL 文は出さない
SELECT QUERY_TYPE, WAREHOUSE_NAME, USER_NAME, ROLE_NAME, QUERY_TAG, QUERY_PARAMETERIZED_HASH_VERSION,  -- 試していない
       TO_CHAR(DATE_TRUNC('DAY', CONVERT_TIMEZONE('Asia/Tokyo', START_TIME)), 'YYYY-MM-DD') AS DAY_JST, COUNT(*) AS RUNS  -- 試していない
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE QUERY_PARAMETERIZED_HASH = '<ハッシュ値>'  -- 試していない
  AND START_TIME >= (SELECT MAX(WEEK_FROM) FROM QUERY_REVIEW_LOG) AND START_TIME < (SELECT MAX(WEEK_TO) FROM QUERY_REVIEW_LOG)  -- 試していない
GROUP BY 1, 2, 3, 4, 5, 6, 7  -- 試していない
ORDER BY 7;  -- 試していない

-- (11) FAILED の行を確かめる。点検のロールで実行する。クエリの SQL 文とエラー文は出さない。エラーコードは ITEM のとおりに書く
SELECT QUERY_ID, CONVERT_TIMEZONE('Asia/Tokyo', START_TIME) AS START_JST, ROLE_NAME, WAREHOUSE_NAME, QUERY_TAG, EXECUTION_STATUS  -- 試していない
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE ERROR_CODE = '<エラーコード>'  -- 試していない
  AND START_TIME >= (SELECT MAX(WEEK_FROM) FROM QUERY_REVIEW_LOG) AND START_TIME < (SELECT MAX(WEEK_TO) FROM QUERY_REVIEW_LOG)  -- 試していない
ORDER BY START_TIME;  -- 試していない

記録テーブルと QUERY_REVIEW_ALERTS は、CREATE TABLE IF NOT EXISTS にしました。試したときの CREATE OR REPLACE のままだと、一式を実行し直すたびに記録が消えるからです。ストアドプロシージャ・アラート・タスクは CREATE OR REPLACE です。作り直すと一時停止の状態に戻るので、作り直したら (7) の RESUME も実行し直します。

点検のロールに要る権限は、集計するビュー・タスクとアラート・メールの3つに分かれます。SNOWFLAKE データベースには、ACCOUNT_USAGE のビューを見るためのデータベースロールが用意されています。データベースロールは、アカウントのロールに付与して使います。ドキュメントの一覧では、QUERY_HISTORY のビューに要るのは GOVERNANCE_VIEWER です。WAREHOUSE_METERING_HISTORY のビューには USAGE_VIEWER が要ります。

EXECUTE TASK と EXECUTE ALERT は、タスクとアラートを作って動かすためのアカウントの権限です。付与は ACCOUNTADMIN が行います。メールを送るには、通知インテグレーションの USAGE も要ります。ほかに、データベース・スキーマ・ウェアハウスの USAGE も要ります。スキーマにテーブル・ストアドプロシージャ・タスク・アラートを作る権限も、点検のロールに付与しておきます。

(2) から下を点検のロールで実行すると、テーブル・ストアドプロシージャ・タスク・アラートの持ち主はそのロールになります。アラートは、持ち主のロールの権限で動きます。(1) の ALTER USER は、ほかのユーザーの設定を変える文なので、ACCOUNTADMIN のようにユーザーを管理するロールで実行します。

月曜に結果を見る人のロールには、(6) で2つのテーブルの SELECT を付与します。データベース・スキーマ・ウェアハウスの USAGE は、(1) で付与します。この3つの USAGE を付与できるのは、持ち主か、MANAGE GRANTS などを持つロールだからです。見る人は、USE WAREHOUSE と USE SCHEMA を実行してから SELECT します。見る人のロールで見られるのは、この2つのテーブルだけです。(9) のアラートの実行履歴は、ACCOUNTADMIN とアラートの持ち主のロールにしか結果を返さず、ほかのロールでは0行になります。(10) と (11) はクエリ履歴のビューを見るので、GOVERNANCE_VIEWER を持つ点検のロールで実行します。

タスクには、クエリタグ weekly_query_review を書きました。翌週の点検で、点検の一式そのもののクエリを見分けるためです。

アラートは、基準を超えた行が無い週にも、タスクが動かなかった週にも、アラート自体が失敗した週にも、メールを送りません。見分けるには、月曜に (9) の SELECT を、点検のロールか ACCOUNTADMIN で実行します。アラートの実行履歴の STATE が CONDITION_FALSE なら、基準を超えた行が無かった週です。CONDITION_FAILED か ACTION_FAILED なら、アラートの条件か処理が失敗しています。STATE には、ほかに FAILED や CANCELLED もあります。

記録テーブルの最後の REVIEWED_AT が今週の8時台でなければ、タスクが動いていないか遅れています。タスクが9時より後に動いた週は、アラートがその週の行を見ないまま終わります。メールが来ない月曜も、動かし始めてしばらくは (9) を実行します。

当社が動かしたのは、前半に載せた試した版の2つだけです。記録テーブルとストアドプロシージャを作って CALL する文と、行を書くだけのアラートを EXECUTE ALERT する文で、どちらも1回ずつです。この一式そのものは、動かしていません。試した版の文は、どれも ACCOUNTADMIN で実行しました。ほかのロールで試すと、ACCOUNT_USAGE のビューを見る権限や EXECUTE ALERT の権限が無いところで止まるはずです。クエリごとのクレジットを持つ QUERY_ATTRIBUTION_HISTORY のビューは使っていません。わざとスピルを起こす重いクエリは、試用のアカウントの使用量に上限を決めていたので、実行していません。

最初の月曜は、上位5つのまとまりを処理の名前に置き換える

一式を作ったら、最初の月曜に、記録テーブルの SLOW_GROUP の5行を出します。ハッシュ値ごとに (10) の SELECT を実行し、どの処理のクエリかを書き留めます。分からなかったまとまりは、その処理を作った人か使っている人を探して、クエリタグを付けてもらいます。タグが増えるほど、メールが来た週に、誰に聞けばよいかがすぐ分かります。

用途ごとにウェアハウスを分ける設計は、当社の Snowflake 導入支援の提供内容の1つです。重い処理を別のウェアハウスに分けると決めたら、分け方から一緒に考えます。