[新機能] Snowflakeでクエリの特性に応じてコンピュートリソースが自動でスケールするAdaptive Warehouseを試してみた

-- サンプルデータの確認
USE SCHEMA SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000;

-- テーブル一覧の確認
SHOW TABLES;

USE ROLE ACCOUNTADMIN;

-- アカウントレベルでクエリ結果キャッシュを無効化
ALTER ACCOUNT SET USE_CACHED_RESULT = false;

-- 設定変更後の確認
SHOW PARAMETERS LIKE '%USE_CACHED_RESULT%';

USE ROLE SYSADMIN;

-- XLARGE設定のAdaptive Warehouseを新規作成
CREATE ADAPTIVE WAREHOUSE adaptive_wh_xlarge
  WITH MAX_QUERY_PERFORMANCE_LEVEL = XLARGE
       QUERY_THROUGHPUT_MULTIPLIER = 2;

-- ウェアハウスの設定を確認
SHOW WAREHOUSES LIKE 'adaptive_wh_xlarge';

-- セッションにQUERY_TAGを設定（後で比較のラベルとして使用）
ALTER SESSION SET QUERY_TAG = 'adaptive_xlarge_m2';

USE WAREHOUSE adaptive_wh_xlarge;
USE SCHEMA SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000;

-- TPC-H Q1: Pricing Summary Report
-- SF1000のLINEITEMテーブル（約60億レコード）を集計
SELECT
    L_RETURNFLAG,
    L_LINESTATUS,
    SUM(L_QUANTITY)                                       AS SUM_QTY,
    SUM(L_EXTENDEDPRICE)                                  AS SUM_BASE_PRICE,
    SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT))               AS SUM_DISC_PRICE,
    SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT) * (1 + L_TAX)) AS SUM_CHARGE,
    AVG(L_QUANTITY)                                       AS AVG_QTY,
    AVG(L_EXTENDEDPRICE)                                  AS AVG_PRICE,
    AVG(L_DISCOUNT)                                       AS AVG_DISC,
    COUNT(*)                                              AS COUNT_ORDER
FROM
    LINEITEM
WHERE
    L_SHIPDATE <= DATEADD(DAY, -90, TO_DATE('1998-12-01'))
GROUP BY
    L_RETURNFLAG,
    L_LINESTATUS
ORDER BY
    L_RETURNFLAG,
    L_LINESTATUS;

-- TPC-H Q5: Local Supplier Volume
-- 6テーブルをJOINする複雑なクエリ
SELECT
    N.N_NAME,
    SUM(L.L_EXTENDEDPRICE * (1 - L.L_DISCOUNT)) AS REVENUE
FROM
    CUSTOMER   C,
    ORDERS     O,
    LINEITEM   L,
    SUPPLIER   S,
    NATION     N,
    REGION     R
WHERE
    C.C_CUSTKEY    = O.O_CUSTKEY
    AND L.L_ORDERKEY  = O.O_ORDERKEY
    AND L.L_SUPPKEY   = S.S_SUPPKEY
    AND C.C_NATIONKEY = S.S_NATIONKEY
    AND S.S_NATIONKEY = N.N_NATIONKEY
    AND N.N_REGIONKEY = R.R_REGIONKEY
    AND R.R_NAME      = 'ASIA'
    AND O.O_ORDERDATE >= TO_DATE('1994-01-01')
    AND O.O_ORDERDATE <  DATEADD(YEAR, 1, TO_DATE('1994-01-01'))
GROUP BY
    N.N_NAME
ORDER BY
    REVENUE DESC;

USE ROLE SYSADMIN;

-- SMALL設定のAdaptive Warehouseを新規作成
CREATE ADAPTIVE WAREHOUSE adaptive_wh_small
  WITH MAX_QUERY_PERFORMANCE_LEVEL = SMALL
       QUERY_THROUGHPUT_MULTIPLIER = 2;

-- ウェアハウスの設定を確認
SHOW WAREHOUSES LIKE 'adaptive_wh_small';

-- QUERY_TAGを設定（SMALL設定での実行であることをラベル付け）
ALTER SESSION SET QUERY_TAG = 'adaptive_small_m2';

USE WAREHOUSE adaptive_wh_small;
USE SCHEMA SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000;

-- Q1を実行（MAX_QUERY_PERFORMANCE_LEVEL = SMALL）
SELECT
    L_RETURNFLAG,
    L_LINESTATUS,
    SUM(L_QUANTITY)                                       AS SUM_QTY,
    SUM(L_EXTENDEDPRICE)                                  AS SUM_BASE_PRICE,
    SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT))               AS SUM_DISC_PRICE,
    SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT) * (1 + L_TAX)) AS SUM_CHARGE,
    AVG(L_QUANTITY)                                       AS AVG_QTY,
    AVG(L_EXTENDEDPRICE)                                  AS AVG_PRICE,
    AVG(L_DISCOUNT)                                       AS AVG_DISC,
    COUNT(*)                                              AS COUNT_ORDER
FROM
    LINEITEM
WHERE
    L_SHIPDATE <= DATEADD(DAY, -90, TO_DATE('1998-12-01'))
GROUP BY
    L_RETURNFLAG,
    L_LINESTATUS
ORDER BY
    L_RETURNFLAG,
    L_LINESTATUS;

-- Q5を実行（MAX_QUERY_PERFORMANCE_LEVEL = SMALL）
SELECT
    N.N_NAME,
    SUM(L.L_EXTENDEDPRICE * (1 - L.L_DISCOUNT)) AS REVENUE
FROM
    CUSTOMER   C,
    ORDERS     O,
    LINEITEM   L,
    SUPPLIER   S,
    NATION     N,
    REGION     R
WHERE
    C.C_CUSTKEY    = O.O_CUSTKEY
    AND L.L_ORDERKEY  = O.O_ORDERKEY
    AND L.L_SUPPKEY   = S.S_SUPPKEY
    AND C.C_NATIONKEY = S.S_NATIONKEY
    AND S.S_NATIONKEY = N.N_NATIONKEY
    AND N.N_REGIONKEY = R.R_REGIONKEY
    AND R.R_NAME      = 'ASIA'
    AND O.O_ORDERDATE >= TO_DATE('1994-01-01')
    AND O.O_ORDERDATE <  DATEADD(YEAR, 1, TO_DATE('1994-01-01'))
GROUP BY
    N.N_NAME
ORDER BY
    REVENUE DESC;

-- 全20回分の実行履歴（実行時間のみ確認）
SELECT
    QUERY_TAG,
    WAREHOUSE_NAME,
    CASE
        WHEN QUERY_TEXT ILIKE '%L_RETURNFLAG%' THEN 'Q1'
        WHEN QUERY_TEXT ILIKE '%N_NAME%'       THEN 'Q5'
        ELSE 'OTHER'
    END                                       AS query_name,
    START_TIME,
    ROUND(EXECUTION_TIME / 1000, 2)           AS exec_sec
FROM TABLE(
    INFORMATION_SCHEMA.QUERY_HISTORY(
        END_TIME_RANGE_START => DATEADD(HOUR, -2, CURRENT_TIMESTAMP()),
        END_TIME_RANGE_END   => CURRENT_TIMESTAMP(),
        RESULT_LIMIT         => 1000
    )
)
WHERE WAREHOUSE_NAME   IN ('ADAPTIVE_WH_XLARGE', 'ADAPTIVE_WH_SMALL')
  AND QUERY_TAG        IN ('adaptive_xlarge_m2', 'adaptive_small_m2')
  AND EXECUTION_STATUS = 'SUCCESS'
  AND QUERY_TYPE       = 'SELECT'
ORDER BY QUERY_TAG, query_name, START_TIME;

-- QUERY_TAG × クエリ種別ごとに実行時間を集計して比較
-- 各 QUERY_TAG × query_name の直近5件のSELECT文のみを対象にする
WITH ranked AS (
    SELECT
        QUERY_TAG,
        WAREHOUSE_NAME,
        CASE
            WHEN QUERY_TEXT ILIKE '%L_RETURNFLAG%' THEN 'Q1'
            WHEN QUERY_TEXT ILIKE '%N_NAME%'       THEN 'Q5'
            ELSE 'OTHER'
        END                  AS query_name,
        EXECUTION_TIME,
        TOTAL_ELAPSED_TIME,
        QUEUED_OVERLOAD_TIME,
        ROW_NUMBER() OVER (
            PARTITION BY QUERY_TAG,
                         CASE
                             WHEN QUERY_TEXT ILIKE '%L_RETURNFLAG%' THEN 'Q1'
                             WHEN QUERY_TEXT ILIKE '%N_NAME%'       THEN 'Q5'
                             ELSE 'OTHER'
                         END
            ORDER BY START_TIME DESC
        ) AS rn
    FROM TABLE(
        INFORMATION_SCHEMA.QUERY_HISTORY(
            END_TIME_RANGE_START => DATEADD(HOUR, -2, CURRENT_TIMESTAMP()),
            END_TIME_RANGE_END   => CURRENT_TIMESTAMP(),
            RESULT_LIMIT         => 1000
        )
    )
    WHERE WAREHOUSE_NAME   IN ('ADAPTIVE_WH_XLARGE', 'ADAPTIVE_WH_SMALL')
      AND QUERY_TAG        IN ('adaptive_xlarge_m2', 'adaptive_small_m2')
      AND EXECUTION_STATUS = 'SUCCESS'
      AND QUERY_TYPE       = 'SELECT'
)
SELECT
    QUERY_TAG,
    WAREHOUSE_NAME,
    query_name,
    COUNT(*)                                           AS run_count,
    ROUND(AVG(EXECUTION_TIME)       / 1000, 2)        AS avg_exec_sec,
    ROUND(MIN(EXECUTION_TIME)       / 1000, 2)        AS min_exec_sec,
    ROUND(MAX(EXECUTION_TIME)       / 1000, 2)        AS max_exec_sec,
    ROUND(AVG(TOTAL_ELAPSED_TIME)   / 1000, 2)        AS avg_elapsed_sec,
    ROUND(AVG(QUEUED_OVERLOAD_TIME) / 1000, 2)        AS avg_queued_overload_sec
FROM ranked
WHERE rn <= 5
GROUP BY QUERY_TAG, WAREHOUSE_NAME, query_name
ORDER BY QUERY_TAG, query_name;