Snowflakeで分毎のウェアハウス実行状態を取得してクレジット消費量（と消費額）の概算を取得する

背景

「ある人が◯◯分〜◯◯分までSnowflakeを使いある検証を行っていた。そのときのクレジット消費量を知りたい」ということを知りたかったのですが、SnowflakeではWebUIからクレジット消費量を時間毎までしか見れず、分毎のクレジット消費量を取ることはできない。

欲しい情報は分単位でのクレジット消費量（そして消費額）で、WebUIからは取得できなさそうだったので、それを出すクエリを書きましたので共有です。

クエリと使い方

クエリの説明

前提

まず、前提として、概算しか取れないです。それほど正確ではないのでご注意ください。

そして、ウェアハウスの起動時間が1分になっていること & クエリの実行時間が数秒以下の短いクエリが大多数であることが前提です。
これらの前提をもって、クエリの実行時間をもとにして、ウェアハウスの起動時間を予想して、そこからクレジット消費量を予想しています。

クエリで何をやっているか

まず、ほしい期間の時分のリストを作ります。

その各時分に関して、その時分のときにクエリが実行されているかを見ます。

クエリが実行されているのであれば、その時分ではウェアハウスが動いているのでクレジット消費がされています。

というのをすべての時分に関して行えば、ほしい期間のどの時分でウェアハウスが動いているかがわかります。

それらの時分をコピーしてエクセルとかに貼ってい、フィルターしたり合計したりすることでクレジット消費量がわかります。

クレジット消費量に単位クレジット消費額をかければクレジット消費額がわかります。
クレジット料金はこちらから。

実際のクエリ

-- 15日前から15日分の毎分のタイムスタンプが入ったテーブルを作る
WITH date_series AS (
  SELECT
    -- 15日前からという指定
    date_trunc(minute, DATEADD('MINUTE', SEQ4(), CURRENT_TIMESTAMP() - INTERVAL '15 DAYS')) AS minute
  FROM
    -- 15日間の分数を取得するという指定
    TABLE(GENERATOR(ROWCOUNT => 60 * 24 * 15)) -- ここでは過去30日間の毎分のタイムスタンプを生成
),
-- 動いていた時点を分の精度で取得する
wh_working_minute_series as (
    select
        distinct
        date_trunc(minute, start_time) start_minute,
        date_trunc(minute, end_time) end_minute,
        warehouse_name,
        warehouse_size
    from 
        SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
    where
        -- （オプション）ユーザーの指定
        user_name = 'HAGIWARA'
        -- （オプション）ウェアハウスの指定
        warehouse_name = 'HAGIWARA_WH'
        -- ウェアハウス名がnullのときはクエリ実行しても課金がされないようなので除外する
        and warehouse_size is not null
)
SELECT
  -- 時分
  minute,
  -- ウェアハウスが動いていたか
  exists (
    select 1
    from wh_working_minute_series wh
    where ds.minute between wh.start_minute and wh.end_minute
  ),
  -- そのときのウェアハウスのサイズ
  (
    select
      max(wh.warehouse_size)
    from wh_working_minute_series wh
    where ds.minute between wh.start_minute and wh.end_minute
  )
FROM
  date_series ds
ORDER BY
  minute;