Author: Ramkrishna Ojha & Vinay Pratap Singh
Prerequisites:
Having an active account of Snowflake.
Introduction:
In snowsight for resource monitoring we created a Dashboard in which different tiles are monitoring the total credit usage, credit usage per warehouse, storage usage, User login and also Query execution in the snowflake account.
1) Sign in to Snowflake snowsight interface:
2) Go to dashboards and create a new Dashboard:
3) Inside the newly created Dashboard create a Tile.
4) In this project (Cinemalytics) we created 8 different tiles :
1. Total Credit Used: This tile tells about the total credit used by the account.
select
sum(Credits_used),
warehouse_name
from
"SNOWFLAKE"."ACCOUNT_USAGE"."WAREHOUSE_METERING_HISTORY"
group by
Warehouse_name
2. Credit Used Per Warehouse: This tile tells about the total credit used by a warehouse. For different warehouses no. of credits used by them.
select
sum(Credits_used),
warehouse_name
from
"SNOWFLAKE"."ACCOUNT_USAGE"."WAREHOUSE_METERING_HISTORY"
group by
Warehouse_name
3. Credit Used Per week:This tile tells about the total credit used per week.
select
date_trunc('WEEK', usage_date) as USAGE_WEEK,
sum(CREDITS_BILLED)
from
account_usage.metering_daily_history
group by
USAGE_WEEK;
4. User Login: This tile tells about how many logins are there like by the Python Driver, Snowflake UI, JDBC and ODBC Connector.
select
*
from
table(information_schema.login_history_by_user())
order by
Event_timestamp;
5. Total Storage Used:This tile tells about the total storage used by the account.
select
div0( sum (AVERAGE_DATABASE_BYTES) , 1048576)
from
(
select
*
from
table(
information_schema.database_storage_usage_history(
dateadd('days', -30, current_date()),
current_date()
)
)
)
6. Storage Used(Byte): This tile tells about the total storage used by the different databases in Bytes.
select
*
from
table(
information_schema.database_storage_usage_history(
dateadd('days', -10, current_date()),
current_date()
)
);
7. Query Execution time per user: This tile tells about the time taken per user for query execution in descending order.
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;
8. No. of Jobs Executed: This tile tells about the total number of jobs executed in the account.
select
count(*) as number_of_jobs
from
query_history
where
start_time >= date_trunc(month, current_date);
Conclusion:
For monitoring the credit usage in your account, Logins, query execution time, and Jobs in your snowflake account we use the snowsight feature of snowflake to create a dashboard by combining the different tiles.
Final Dashboard:
