-
Notifications
You must be signed in to change notification settings - Fork 1
/
0021. System usage and billing.sql
64 lines (56 loc) · 1.91 KB
/
0021. System usage and billing.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
-- system usage and billing
use role accountadmin;
SELECT
WAREHOUSE_NAME,
SUM(CREDITS_USED) AS TOTAL_CREDIT_USED,
SUM(CREDITS_USED_COMPUTE) AS TOTAL_WAREHOUSE_CREDIT_USED,
SUM(CREDITS_USED_CLOUD_SERVICES) AS
TOTAL_CREDITS_USED_CLOUD_SERVICES
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
GROUP BY 1;
SELECT
USAGE_DATE,
SERVICE_TYPE,
SUM(CREDITS_USED_COMPUTE) AS CREDITS_USED_COMPUTE,
SUM(CREDITS_USED_CLOUD_SERVICES) AS
CREDITS_USED_CLOUD_SERVICES,
SUM(CREDITS_USED) AS TOTAL_CREDITS_USED,
SUM(CREDITS_ADJUSTMENT_CLOUD_SERVICES) AS
CREDITS_ADJUSTMENT_CLOUD_SERVICES,
SUM(CREDITS_BILLED) AS CREDITS_BILLED
FROM SNOWFLAKE.ACCOUNT_USAGE.METERING_DAILY_HISTORY
GROUP BY 1,2
ORDER BY USAGE_DATE DESC;
-- views from Information schema
use database demo_data_loading;
SELECT *
FROM
TABLE(INFORMATION_SCHEMA.WAREHOUSE_METERING_HISTORY(DATEADD('DAYS',-10,current_date)));
SELECT
USAGE_DATE,
DATABASE_ID,
DATABASE_NAME,
DELETED,
AVERAGE_DATABASE_BYTES /1024 / 1024 AS AVERAGE_DATABASE_MB,
AVERAGE_FAILSAFE_BYTES / 1024 / 1024 AS AVERAGE_FAILSAFE_MB
FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASE_STORAGE_USAGE_HISTORY
WHERE DATABASE_NAME = 'DEMO_DATA_LOADING'
ORDER BY USAGE_DATE DESC;
SELECT
TABLE_CATALOG AS DATABASE_NAME,
TABLE_SCHEMA,
TABLE_NAME,
ACTIVE_BYTES / 1024 / 1024 AS ACTIVE_BYTES_MB,
TIME_TRAVEL_BYTES / 1024 / 1024 AS TIME_TRAVEL_BYTES_MB,
FAILSAFE_BYTES / 1024 / 1024 AS FAILSAFE_BYTES_MB
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS
WHERE TABLE_CATALOG = 'DEMO_DATA_LOADING';
SELECT
TABLE_CATALOG AS DATABASE_NAME,
TABLE_SCHEMA,
TABLE_NAME,
ACTIVE_BYTES / 1024 / 1024 AS ACTIVE_BYTES_MB,
TIME_TRAVEL_BYTES / 1024 / 1024 AS TIME_TRAVEL_BYTES_MB,
FAILSAFE_BYTES / 1024 / 1024 AS FAILSAFE_BYTES_MB
FROM INFORMATION_SCHEMA.TABLE_STORAGE_METRICS
WHERE TABLE_CATALOG IN ('DEMO_DATA_LOADING','TEST_CLONING');