1
0
During this post we will discuss about some important queries which we can use in our day-to-day task to determine the list of warehouses where Data spill to the Remote. Queries to find out the Active Roles in your particular Database or Schema. In addition to it we will see the queries to find out the full table scan queries approx. 90%.
Moreover will discuss the Queries to find the Privilege’s assign to the Role and in turn Roles assign to the Users. In addition we will see warehouse_load_history to identify how much work was done over a period of time (load) and Warehouse_Metering_History view is used to return the hourly credit usage for one or more warehouses in your account.
1: Firstly, Query to Find Active Roles in Database:
Select * From Snowflake.Account_Usage.Roles
Where DELETED_ON IS NULL;
2: Query to find Privileges in Database:
Select
Grantor,
Grantee,
Object_Catalog as DB_NAME,
Object_Schema as DB_SCHEMA,
Object_Name as OBJ_NAME,
Object_Type, Privilege_Type
From Information_Schema.Object_Privileges;
3: Query to Find Privileges assigned Role:
Select
Privilege, Granted_On,
NAME AS OBJECT_NAME,
Table_Catalog as DB_NAME, Table_Schema as SCHEMA_NAME,
Grantee_Name as ROLE_ASSIGNED_PRIV,
Granted_By
From Snowflake.Account_Usage.Grants_To_Roles;
4: Roles Assigned to Users:
Select * from Snowflake.Account_Usage.Grants_To_Users
5: Users who have not logged in with 90 days of account creation
Show users
Select *
From Table(Result_Scan(Last_Query_Id()))
Where "last_success_login" IS NULL
And DateDiff('Day',"created_on",Current_Date) > 90;
6: List of Warehouse where data spilled to Remote disk
SELECT * FROM "SNOWFLAKE"."ACCOUNT_USAGE"."QUERY_HISTORY"
WHERE BYTES_SPILLED_TO_REMOTE_STORAGE > 0;
7: Query to find out the full table scan queries approx. 95%
SELECT query_text,database_name,query_type,warehouse_name,bytes_scanned,PARTITIONS_SCANNED,
PARTITIONS_TOTAL
FROM "SNOWFLAKE"."ACCOUNT_USAGE"."QUERY_HISTORY"
WHERE PARTITIONS_SCANNED > (PARTITIONS_TOTAL*0.95)
8: The Warehouse_Metering_History view is use to return the hourly credit usage for on or more warehouses in your account, for a specified date range
Select
Start_Time,End_Time,
Warehouse_Name,
Credits_Used as Credits_Billed,
Credits_Used_Compute as Credits_Used,
Credits_Used_Cloud_Services as Credits_Cloud_Services
From Snowflake.Account_Usage.Warehouse_Metering_History
Order By Start_Time;
9. Warehouse Profiling
To profile your current warehouse usage, use the WAREHOUSE_LOAD_HISTORY and WAREHOUSE_METERING_HISTORY functions. A good way to think about the relationship between these two functions is that the first one shows how much work was done over a period of time (load) and the second one shows the cost for doing the work (metering).
select * from table(information_schema.warehouse_load_history(date_range_start=>dateadd('hour',-1,current_timestamp())));
select * from table(information_schema.warehouse_metering_history(dateadd('hour',-1,current_date()),current_date()));
10.Long Running Query :Retrieve all currently running queries that have exceeded a certain amount of time.
select * From Snowflake.Account_Usage.Query_History
Where
Execution_Status ilike 'RUNNING' And
DateDiff(Second, Start_Time, Current_Timestamp) >= 300;
