雪花成本治理查询

Question

如果我们有任何关于获取所有查询的引用/链接，则需要一些指针，这些查询将识别各种成本指标。我的要求是使用这些查询构建雪花仪表板。

1. 存储成本
2. 计算成本
3. 托管服务成本(斯诺管道/物化视图等)

示例查询如下所示，需要类似于描述中提到的所有指标的查询集。

-计算(这是仓库/人力资源使用的学分)

select to_char(start_time,'HH24') as hour,
       WAREHOUSE_NAME,
       sum(credits_used)
from snowflake.account_usage.warehouse_metering_history wmh 
where wmh.start_time >= dateadd(month, -1, current_date())
group by to_char(start_time,'HH24'), WAREHOUSE_NAME
order by 1;

Answer 1

https://medium.com/snowflake/monitoring-your-snowflake-organization-with-snowsight-b1acd470dc17

下面是一些示例查询，您可以根据需要即兴制作这些指标：

-每个仓库每月的成本

SELECT
WMH.WAREHOUSE_NAME,
MONTHNAME(WMH.START_TIME) MONTH,
SUM((4 * WMH.CREDITS_USED)) AS DOLLARS_USED
FROM
SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY WMH
GROUP BY
WMH.WAREHOUSE_NAME,
MONTHNAME(WMH.START_TIME)

--MONTHLY COST BY WAREHOUSE (CUSTOM/SNOWFLAKE)--
SELECT
WMH.WAREHOUSE_NAME,
MONTHNAME(WMH.START_TIME) MONTH,
SUM(WMH.CREDITS_USED) AS TOTAL_CREDITS,
SUM(WMH.CREDITS_USED_COMPUTE) AS CUSTOMER_COMPUTE,
SUM(CREDITS_USED_CLOUD_SERVICES) AS SNOWFLAKE_COMPUTE
FROM
SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY WMH
GROUP BY
WMH.WAREHOUSE_NAME,
MONTHNAME(WMH.START_TIME);