Snowflake で動的 UNPIVOT

例えば「QUERY_HISTORY から、直近 1 時間の全クエリについて、実行時間内訳(コンパイル時間とかクエリ実行時間とか)それぞれの合計時間を抽出したい」という要件があるときに、それらの内訳はそれぞれ列として格納されているため、結果は横長になります。

select 
    sum(TOTAL_ELAPSED_TIME) TOTAL_ELAPSED_TIME,
    sum(COMPILATION_TIME) COMPILATION_TIME,
    sum(EXECUTION_TIME) EXECUTION_TIME,
    sum(QUEUED_PROVISIONING_TIME) QUEUED_PROVISIONING_TIME,
    sum(QUEUED_OVERLOAD_TIME) QUEUED_OVERLOAD_TIME,
    sum(QUEUED_REPAIR_TIME) QUEUED_REPAIR_TIME,
    sum(TRANSACTION_BLOCKED_TIME) TRANSACTION_BLOCKED_TIME,
    sum(LIST_EXTERNAL_FILES_TIME) LIST_EXTERNAL_FILES_TIME
from
    snowflake.account_usage.query_history
where
    start_time between dateadd(hour, -1, current_timestamp) and current_timestamp
;

TOTAL_ELAPSED_TIME	COMPILATION_TIME	EXECUTION_TIME	QUEUED_PROVISIONING_TIME	QUEUED_OVERLOAD_TIME	QUEUED_REPAIR_TIME	TRANSACTION_BLOCKED_TIME	LIST_EXTERNAL_FILES_TIME
600556	156905	442926	725	0	0	0	0

これでもまあいいのですが、例えばバグトラッカーだったりメールだったりに貼り付けたいとなると、だいたいのテキスト表示領域は縦長なのでちょっと扱いにくいです。あと列はソートできないのもちょっと困ります。

そこで UNPIVOT をすると、列を行に変換できます。

https://docs.snowflake.com/ja/sql-reference/constructs/unpivot.html

が、この UNPIVOT、Snowflake では変換対象の列リストにサブクエリが指定できないため、列をすべて列挙する必要があります。

なので、クエリにするとこんな感じになります。

with queries as (
select 
    sum(TOTAL_ELAPSED_TIME) TOTAL_ELAPSED_TIME,
    sum(COMPILATION_TIME) COMPILATION_TIME,
    sum(EXECUTION_TIME) EXECUTION_TIME,
    sum(QUEUED_PROVISIONING_TIME) QUEUED_PROVISIONING_TIME,
    sum(QUEUED_OVERLOAD_TIME) QUEUED_OVERLOAD_TIME,
    sum(QUEUED_REPAIR_TIME) QUEUED_REPAIR_TIME,
    sum(TRANSACTION_BLOCKED_TIME) TRANSACTION_BLOCKED_TIME,
    sum(LIST_EXTERNAL_FILES_TIME) LIST_EXTERNAL_FILES_TIME
from snowflake.account_usage.query_history
where start_time between dateadd(hour, -1, current_timestamp) and current_timestamp
)
select *
from queries
unpivot (duration for metric in (
    TOTAL_ELAPSED_TIME,
    COMPILATION_TIME,
    EXECUTION_TIME,
    QUEUED_PROVISIONING_TIME,
    QUEUED_OVERLOAD_TIME,
    QUEUED_REPAIR_TIME,
    TRANSACTION_BLOCKED_TIME,
    LIST_EXTERNAL_FILES_TIME
))
order by duration desc
;

METRIC	DURATION
TOTAL_ELAPSED_TIME	600556
EXECUTION_TIME	442926
COMPILATION_TIME	156905
QUEUED_PROVISIONING_TIME	725
LIST_EXTERNAL_FILES_TIME	0
QUEUED_REPAIR_TIME	0
QUEUED_OVERLOAD_TIME	0
TRANSACTION_BLOCKED_TIME	0

まだ 8 個しか列がないのでなんとか収まってますが、これが数十個とかになるとまず手動で書くのはめんどくさすぎます。

実際私も業務でこの問題にぶち当たって、巨大な UNPIVOT をスクリプトで書いてテンプレート化して使い回すみたいなことをやっていたのですが、ある日神が降りてきて、動的にできることに気がついたので共有します。

with queries as (
    select object_construct(*) line
    from snowflake.account_usage.query_history
    where start_time between dateadd(hour, -1, current_timestamp) and current_timestamp
)
select f.key metric, sum(f.value) duration
from queries q, lateral flatten(q.line) f
where (metric like '%TIME' and metric not in ('START_TIME', 'END_TIME'))
group by metric
order by duration desc;

METRIC	DURATION
TOTAL_ELAPSED_TIME	600556
EXECUTION_TIME	442926
COMPILATION_TIME	156905
QUEUED_PROVISIONING_TIME	725
LIST_EXTERNAL_FILES_TIME	0
QUEUED_REPAIR_TIME	0
QUEUED_OVERLOAD_TIME	0
TRANSACTION_BLOCKED_TIME	0

だいぶすっきりしました。

OBJECT_CONSTRUCT を集約関数として使うと、各行の値は {"カラム名": 値} という形式の JSON になります。(おそらく、私の知る限りこれが Snowflake 唯一のカラム名を結果行に入れることのできるインターフェースです。)

この JSON を LATERAL FLATTEN で再度展開して、KEY にフィルタをかけることで特定の列のみを抽出し、KEY で GROUP BY しつつ VALUE を SUM することで列ごとの合計に集約することができます。

一見 KEY がパターンに落とし込めない(完全に離散的なネーミングの列を抽出したい)場合、結局列挙が必要そうに見えますが、その場合でも SUM(列) を列挙しなくていいというメリットは残るので記述量は減ります。

デメリットとしては、一瞬何やってるのかわからない(可読性が低い)ことと、行数が多くなってきたときに OBJECT_CONSTRUCT のオーバーヘッドがちょっと心配な点がありますが、とりあえず UNPIVOT 書きたくないときの回避策としてはいい気がするなあと思い、気に入っております。