// Query : 1. WH Credits by Month
DATE
usage_month,
Round(SUM(credits_used_compute), 0) AS "Compute Credits Used",
Round(SUM(credits_used_cloud_services), 0) "Cloud Services Credits Used"
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time BETWEEN Convert_timezone('America/Chicago',
Date_trunc('month', Dateadd('month', -6,
Current_timestamp()))) AND
Current_timestamp()
GROUP BY 1
ORDER BY 1;
// Query 2 : WH Credits by Week
round(sum(credits_used_compute),0) AS "Compute Credits Used" ,
round(sum(credits_used_cloud_services),0) "Cloud Services Credits Used"
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time BETWEEN convert_timezone('America/Chicago',date_trunc('month', dateadd('month',-6,CURRENT_TIMESTAMP()))) AND CURRENT_TIMESTAMP()
GROUP BY 1
ORDER BY 1;
// Query 3 : WH Credits by Month (Top 10)
(
SELECT warehouse_name,
Round(Sum(credits_used_compute),0) + Round(Sum(credits_used_cloud_services),0) AS credits_used
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time BETWEEN Date_trunc('month', Dateadd('month',-2,Convert_timezone('America/Chicago',CURRENT_TIMESTAMP()))) AND CURRENT_TIMESTAMP()
GROUP BY warehouse_name
ORDER BY 2 DESC limit 10)
SELECT Date_trunc('month', Convert_timezone('America/Chicago',start_time))::date usage_month,
warehouse_name ,
round(sum(credits_used_compute),0) + round(sum(credits_used_cloud_services),0) AS credits_used
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time BETWEEN convert_timezone('America/Chicago',date_trunc('month', dateadd('month',-6,CURRENT_TIMESTAMP()))) AND CURRENT_TIMESTAMP()
AND warehouse_name IN
(
SELECT warehouse_name
FROM wh_list )
GROUP BY 1,
2
ORDER BY 1,
2 DESC ;
// Query 4 : WH Credits by Week (Top 10)
(
SELECT warehouse_name,
Round(Sum(credits_used_compute),0) + Round(Sum(credits_used_cloud_services),0) AS credits_used
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time BETWEEN Date_trunc('month', Dateadd('month',-2,Convert_timezone('America/Chicago',CURRENT_TIMESTAMP()))) AND CURRENT_TIMESTAMP()
GROUP BY warehouse_name
ORDER BY 2 DESC limit 10)
SELECT Date_trunc('week', Convert_timezone('America/Chicago',start_time))::date usage_week,
warehouse_name ,
round(sum(credits_used_compute),0) + round(sum(credits_used_cloud_services),0) AS credits_used
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time BETWEEN convert_timezone('America/Chicago',date_trunc('month', dateadd('month',-6,CURRENT_TIMESTAMP()))) AND CURRENT_TIMESTAMP()
AND warehouse_name IN
(
SELECT warehouse_name
FROM wh_list )
GROUP BY 1,
2
ORDER BY 1,
2 DESC ;
// Query 5 : WH Credits used last 30 days
SELECT warehouse_name,
Round(Sum(credits_used_compute),0) AS "Compute Credits Used" ,
Round(Sum(credits_used_cloud_services),0) AS "Cloud Services Credits Used"
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time BETWEEN Date_trunc('day', Dateadd('day',-29,Convert_timezone('America/Chicago',CURRENT_TIMESTAMP()))) AND CURRENT_TIMESTAMP()
GROUP BY 1
ORDER BY 2 DESC;
// Query 6 : Top users by Execution Time (last 7 days)
Round(Sum(execution_time)/(1000*60*60),1) exec_hrs,
Count(1) AS num_queries
FROM snowflake.account_usage.query_history
WHERE start_time > dateadd(day, -7 , CURRENT_DATE())
--AND user_name NOT LIKE '%SVC%'
--AND user_name NOT IN ('EDC_SCAN','SEENOSNOWMAN')
--AND role_name NOT LIKE '%SNOWFLAKE%'
GROUP BY 1
ORDER BY exec_hrs DESC limit 10;
// Query 7 : Top users by Query Count (last 7 days)
SELECT user_name,
Round(Sum(execution_time)/(10006060),1) exec_hrs,
Count(1) AS num_queries
FROM snowflake.account_usage.query_history
WHERE start_time > dateadd(day, -7 , CURRENT_DATE())
--AND user_name NOT LIKE '%SVC%'
--AND user_name NOT IN ('EDC_SCAN','SEENOSNOWMAN')
--AND role_name NOT LIKE '%SNOWFLAKE%'
AND warehouse_name = 'ENTPRS_WH'
GROUP BY 1
ORDER BY exec_hrs DESC limit 10;
// Query 8 Sum of credit consumption at the account level
SELECT Sum(credits_used)
FROM account_usage.metering_history
WHERE start_time = :daterange;
// Query 9 Total storage used till current date at account level
SELECT Avg(storage_bytes + stage_bytes + failsafe_bytes) / Power(1024, 4) AS
billable_tb
FROM account_usage.storage_usage
WHERE usage_date = CURRENT_DATE() - 1;
// Query 10 Total no of jobs executed in the account till current date
SELECT Count(*) AS number_of_jobs
FROM account_usage.query_history
WHERE start_time >= Date_trunc(month, CURRENT_DATE);
// Query 11 How much data is stored monthly for failsafe, database, stage
SELECT Date_trunc(month, usage_date) AS usage_month,
Avg(storage_bytes + stage_bytes + failsafe_bytes) / Power(1024, 4) AS billable_tb,
Avg(storage_bytes) / Power(1024, 4) AS Storage_TB,
Avg(stage_bytes) / Power(1024, 4) AS Stage_TB,
Avg(failsafe_bytes) / Power(1024, 4) AS Failsafe_TB
FROM account_usage.storage_usage
GROUP BY 1
ORDER BY 1;
// Query 12 Sum of credits that are consumed monthly at the account level
SELECT Date_trunc('MONTH', usage_date) AS Usage_Month,
Sum(credits_billed)
FROM account_usage.metering_daily_history
GROUP BY usage_month;
// Query 13 Total credit consumed by a particular Warehouse
SELECT warehouse_name,
Sum(credits_used) AS total_credits_used
FROM account_usage.warehouse_metering_history
WHERE start_time = :daterange
GROUP BY 1
ORDER BY 2 DESC;
// Query 14 sum of credit that is consumed by the cloud service warehouse and compute warehouse
SELECT warehouse_name,
Sum(credits_used_cloud_services) credits_used_cloud_services,
Sum(credits_used_compute) credits_used_compute,
Sum(credits_used) credits_used
FROM account_usage.warehouse_metering_history
WHERE true
AND start_time = :daterange
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;
// Query 15 Query execution time by different query types
SELECT query_type,
warehouse_size,
Avg(execution_time) / 1000 AS average_execution_time
FROM account_usage.query_history
WHERE start_time = :daterange
GROUP BY 1,
2
ORDER BY 3 DESC;
// Query 16 Average query execution time by a particular user
SELECT user_name,
( Avg(execution_time) ) / 1000 AS average_execution_time
FROM account_usage.query_history
WHERE start_time = :daterange
GROUP BY 1
ORDER BY 2 DESC;
// Query 17 Execution time is taken by the repeated query
SELECT query_text,
( Sum(execution_time) / 60000 ) AS exec_time
FROM account_usage.query_history
WHERE execution_status = 'SUCCESS'
GROUP BY query_text
ORDER BY exec_time DESC
LIMIT 25;
// Query 18 How much credit is consumed daily in terms of the dollar at the organizational level
SELECT usage_in_currency,
usage_date
FROM snowflake.organization_usage.usage_in_currency_daily;
// Query 19 Sum of Credit consumption based on Warehouse tags
Prerequisite:
To get the below chart, first apply tags on the warehouse based on your need and then execute the below query in snow sight to get the chart.
SELECT tag_value AS department,
Sum(credits_used) AS credits
FROM "SNOWFLAKE"."ACCOUNT_USAGE"."warehouse_metering_history",
"SNO WFLAKE"."ACCOUNT_USAGE"."tag_references"
WHERE true
AND warehouse_name = object_name
AND tag_name = 'COST_CENTER'
AND tag_database = 'DEV_ACCIDENTS_DB'
AND tag_schema = 'LANDING_SCHEMA'
AND start_time >= Dateadd('days', -30, CURRENT_DATE()) GROUP BY 1 ORDER
BY 2 DESC;
// Query 20 Snowflake Accounts Usage in Currency
use role orgadmin;
USE WAREHOUSE ENTPRS_WH;
select account_name,sum(USAGE_IN_CURRENCY) as USAGE_IN_CURRENCY from snowflake.ORGANIZATION_USAGE.USAGE_IN_CURRENCY_DAILY group by account_name;
// Query 21 Monthly Usage in Currency on Accounts
USE Role Orgadmin;
SELECT Date_trunc('MONTH', usage_date) AS usage_month,
account_name,
Round(Sum(usage_in_currency), 2) AS total_usage
FROM snowflake.organization_usage.usage_in_currency_daily
GROUP BY 1,
2
ORDER BY 1,
2;
SELECT Date_trunc('MONTH', usage_date) AS usage_month,
account_name,
Upper(usage_type) AS usage_type,
Round(Sum(usage), 3) AS units_consumed,
Round(Sum(usage_in_currency), 2) AS total_usage
FROM snowflake.organization_usage.usage_in_currency_daily
--WHERE USAGE_MONTH = '2022-11-01' ---Feel free to change the month
GROUP BY 1,
2,
3
ORDER BY 1,
2,
3;
No comments:
Post a Comment