Snowflake单查询获取整体与库表存储指标匹配问题咨询

现有查询数值不匹配的核心问题 #

• 语法错误：SELECT子句末尾多余逗号，会直接执行报错
• 关联逻辑错误：DATABASE_STORAGE_USAGE_HISTORY是按日统计的视图，直接关联表级存储视图会产生笛卡尔积，导致聚合数值偏大；内连接逻辑会漏掉无活跃表的数据库存储数据
• 字段拼写错误：过滤条件中TABLE_DROPPES为拼写错误，正确字段名为TABLE_DROPPED
• 统计范围缺失：Snowflake账户总存储包含表存储（活跃、时间旅行、故障保护）、内部阶段存储、云服务层关联存储三类，现有查询仅覆盖表相关存储，自然和账户展示总数值不匹配

可同时获取三级存储指标的查询语句 #

以下查询结果可以同时输出账户整体、数据库级、表级的月度存储指标，总数值和账户侧展示一致：

WITH 表级存储月度统计 AS (
    SELECT
        DATE_TRUNC('MONTH', USAGE_DATE) AS 统计月份,
        TABLE_CATALOG AS 数据库名称,
        TABLE_NAME AS 表名称,
        AVG(ACTIVE_BYTES) AS 月均活跃存储字节数,
        AVG(TIME_TRAVEL_BYTES) AS 月均时间旅行存储字节数,
        AVG(FAILSAFE_BYTES) AS 月均故障保护存储字节数
    FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS
    WHERE 
        DELETED = 'FALSE'
        AND COALESCE(TABLE_DROPPED, SCHEMA_DROPPED, CATALOG_DROPPED) IS NULL
    GROUP BY 1,2,3
),
库级存储月度统计 AS (
    SELECT
        统计月份,
        数据库名称,
        NULL AS 表名称,
        SUM(月均活跃存储字节数) AS 月均活跃存储字节数,
        SUM(月均时间旅行存储字节数) AS 月均时间旅行存储字节数,
        SUM(月均故障保护存储字节数) AS 月均故障保护存储字节数
    FROM 表级存储月度统计
    GROUP BY 1,2
),
账户级其他存储月度统计 AS (
    SELECT
        DATE_TRUNC('MONTH', USAGE_DATE) AS 统计月份,
        NULL AS 数据库名称,
        NULL AS 表名称,
        AVG(STAGE_BYTES) AS 月均活跃存储字节数, -- 阶段存储计入活跃存储项
        0 AS 月均时间旅行存储字节数,
        0 AS 月均故障保护存储字节数
    FROM SNOWFLAKE.ACCOUNT_USAGE.STORAGE_USAGE_HISTORY
    GROUP BY 1
),
账户级总存储月度统计 AS (
    SELECT
        统计月份,
        NULL AS 数据库名称,
        NULL AS 表名称,
        SUM(月均活跃存储字节数) AS 月均活跃存储字节数,
        SUM(月均时间旅行存储字节数) AS 月均时间旅行存储字节数,
        SUM(月均故障保护存储字节数) AS 月均故障保护存储字节数
    FROM (
        SELECT * FROM 库级存储月度统计
        UNION ALL
        SELECT * FROM 账户级其他存储月度统计
    )
    GROUP BY 1
)
-- 合并所有层级结果输出
SELECT * FROM 表级存储月度统计
UNION ALL
SELECT * FROM 库级存储月度统计
UNION ALL
SELECT * FROM 账户级总存储月度统计
ORDER BY 统计月份 DESC, 数据库名称 NULLS LAST, 表名称 NULLS LAST;

结果说明 #

• 若行数据中数据库名称和表名称均为空，对应指标为账户整体存储统计
• 若行数据中仅表名称为空，对应指标为该行数据库名称对应库的存储统计
• 若两个名称字段均有值，对应指标为单表的存储统计

内容的提问来源于stack exchange，提问作者Virally