Snowflakeで複数のLISTやSHOW結果をSELECTでまとめて表示したい時 #SQL

概要

LISTとかSHOWってちょっと不便な関数で、呼び出しと同時にSELECTにかける事ができず

源泉が複数あり、源泉ごとにステージがある場合
それぞれのステージごとにファイルの状況をみようとおもったら

それぞれのステージを対象にLISTって打つ
RESULT_SCAN経由でSELECT〜でステージ単位の集計を見る
別のステージ対象にして繰り返し…

と考えるだけでものすごくめんどくさくなってくるので
以下のサンプルを元に、集計してくるクエリを考えようと思います

サンプルデータ

TEST_DB
  └ TEST
      ├ TEST_STAGE
      │     ├ hoge_202404252050.csv
      │     └ hoge_202404282050.csv
      └ TEST2_STAGE
      　      ├ fuga_202404272050.csv
      　      └ fuga_202404282050.csv

こんな感じでステージごとにファイル名にデータ日付がある場合
例えばステージのファイル種別ごとの更新日とか1回で取りたいですよね？

と、いうことで…

各ステージのファイル種別（分割した上で左側をファイル種別とする）ごとの最終ファイル名と最終更新時刻を取るクエリ

SQL

DECLARE
    QUERIES ARRAY DEFAULT [];
BEGIN
    LET STG_RESULT RESULTSET := (SELECT STAGE_NAME,STAGE_SCHEMA FROM INFORMATION_SCHEMA.STAGES );
    LET STG_CUR CURSOR FOR STG_RESULT;
    
    FOR STG IN STG_CUR DO
        LET STG_NAME TEXT := STG.STAGE_NAME;
        LET STG_SCHEMA TEXT := STG.STAGE_SCHEMA;
        EXECUTE IMMEDIATE ('LIST @'|| :STG_SCHEMA || '.'|| :STG_NAME );
        QUERIES := ARRAY_APPEND(QUERIES,LAST_QUERY_ID());
    END FOR;
    
    LET Q_RS RESULTSET := (SELECT VALUE FROM TABLE(FLATTEN( INPUT => :QUERIES)));
    LET Q_CUR CURSOR FOR Q_RS;

    LET GET_LIST_QUERY TEXT := '';

    FOR QID IN Q_CUR DO
        LET Q TEXT := QID.VALUE;
        IF (GET_LIST_QUERY != '') THEN
            GET_LIST_QUERY := GET_LIST_QUERY || '\n UNION ALL \n';
        END IF;

        GET_LIST_QUERY := GET_LIST_QUERY || 'SELECT
            SPLIT_PART($1,''/'',-2) AS STAGE ,
            SPLIT_PART(SPLIT_PART($1,''/'',-1),''_'',1) AS FILE_TYPE, 
            MAX(TRY_TO_TIMESTAMP(SPLIT_PART(SPLIT_PART(SPLIT_PART($1,''/'',-1),''_'',2),''.'',1),''YYYYMMDDHH24MI'')) AS FILE_DATE,
            MAX_BY($4,TRY_TO_TIMESTAMP(SPLIT_PART(SPLIT_PART(SPLIT_PART($1,''/'',-1),''_'',2),''.'',1),''YYYYMMDDHH24MI'')) AS LAST_MODIFIED
            FROM TABLE(RESULT_SCAN(''' || :Q || ''')) GROUP BY 1,2';
    END FOR;
    LET RS RESULTSET := (EXECUTE IMMEDIATE :GET_LIST_QUERY);
    RETURN TABLE(RS);
END;

ちょーっと後半が奇怪な処理になってしまっていますが…

実行結果

複数ステージの最大日付とファイル日付、最終更新日が取れました (∩´∀｀)∩

ちょっとした説明

LISTの結果を加工する場合はRESULT_SCANを通してからSELECTするしかないのですが
上にも書いたように、RESULT_SCANに渡せるのはQUERY_ID、そして実行ごとにクエリIDを取るのはものすごいめんどくさい

のでちょっと力こそパワーで解決するべく下記の感じで処理を行っています。

INFOMATION_SCHEMAからステージをGET
LISTを実行
LAST_QUERY_IDを使い、LISTが実行された時のクエリIDを取得
取得したクエリIDを行に戻す
RESULT_SCANごとに集計
集計結果をUNION ALL…

SHOWとかLISTがもうちょい加工しやすくなればこんな処理はいらないんですが… (´･ω･`)