Resource Monitoring Using Snowsight

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:


20 views0 comments

Recent Posts

See All