Author: Ramkrishna Ojha & Vinay Pratap Singh
Prerequisites:
Having an active account with Snowflake.
Introduction:
In Snowsight, for resource monitoring, we created a Dashboard in which different tiles monitor 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 eight 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 warehouse's 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 there are 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:
