如何从Snowflake的ACCOUNT_USAGE表/视图中获取仓库大小及历史尺寸信息？

嘿，我完全懂你找了一圈ACCOUNT_USAGE视图却没找到直接能看仓库历史尺寸的困扰——确实Snowflake的ACCOUNT_USAGE里没有专门的视图来直接展示仓库名称和对应时间的大小变化，但咱们可以通过组合现有视图来搞定这个需求！

核心思路 #

Snowflake会把仓库的所有操作事件（比如创建、修改大小）记录在SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_EVENTS_HISTORY里，我们可以从这里提取尺寸变更的记录，再结合SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSES获取当前尺寸，就能构建出完整的仓库尺寸时间线。

具体实现步骤 #

1. 提取仓库尺寸变更的历史记录
   当你修改仓库大小时，会触发ALTER类型的事件，事件里的QUERY_TEXT会包含新的仓库尺寸信息。我们可以用正则表达式从这段文本里提取出尺寸：

SELECT
  warehouse_name,
  event_time,
  REGEXP_SUBSTR(query_text, 'WAREHOUSE_SIZE\\s*=\\s*\\'(.*?)\\'', 1, 1, 'e') AS new_warehouse_size,
  query_text
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_EVENTS_HISTORY
WHERE event_type = 'ALTER'
  AND query_text LIKE '%WAREHOUSE_SIZE%'
ORDER BY warehouse_name, event_time;

2. 构建完整的尺寸时间线（含创建初始尺寸和当前尺寸）
   上面的查询只能拿到变更记录，我们还需要加上仓库创建时的初始尺寸，以及当前的尺寸，这样才能得到从创建到现在的完整变化：

WITH warehouse_creation AS (
  SELECT
    warehouse_name,
    event_time AS creation_time,
    REGEXP_SUBSTR(query_text, 'WAREHOUSE_SIZE\\s*=\\s*\\'(.*?)\\'', 1, 1, 'e') AS initial_size
  FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_EVENTS_HISTORY
  WHERE event_type = 'CREATE'
),
warehouse_size_changes AS (
  SELECT
    warehouse_name,
    event_time AS change_time,
    new_warehouse_size AS size
  FROM (
    SELECT
      warehouse_name,
      event_time,
      REGEXP_SUBSTR(query_text, 'WAREHOUSE_SIZE\\s*=\\s*\\'(.*?)\\'', 1, 1, 'e') AS new_warehouse_size
    FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_EVENTS_HISTORY
    WHERE event_type = 'ALTER'
      AND query_text LIKE '%WAREHOUSE_SIZE%'
  )
  WHERE new_warehouse_size IS NOT NULL
),
current_warehouse_sizes AS (
  SELECT
    warehouse_name,
    warehouse_size AS current_size,
    CURRENT_TIMESTAMP AS current_time
  FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSES
)
-- 合并所有时间点的尺寸信息
SELECT
  wc.warehouse_name,
  wc.creation_time AS effective_time,
  wc.initial_size AS warehouse_size
FROM warehouse_creation wc
UNION ALL
SELECT
  wsc.warehouse_name,
  wsc.change_time AS effective_time,
  wsc.size AS warehouse_size
FROM warehouse_size_changes wsc
UNION ALL
SELECT
  cws.warehouse_name,
  cws.current_time AS effective_time,
  cws.current_size AS warehouse_size
FROM current_warehouse_sizes cws
ORDER BY warehouse_name, effective_time;

额外提示 #

• 如果你的仓库曾经被重命名过，记得结合WAREHOUSE_EVENTS_HISTORY里的RENAME事件，把旧名称和新名称关联起来，这样历史记录就不会断档。
• ACCOUNT_USAGE的视图一般有1-2小时的数据延迟，所以最新的尺寸变更可能不会立刻出现在查询结果里。

备注：内容来源于stack exchange，提问作者RubenLaguna