Hi,
Hi, there isn't a table which shows mappings of Users to groups, you can only see this through the groups editing. You can, however, check grants on tables to see which groups or users have been granted permission to tables. The following query is an example of how you can see this:
SELECT
'catalog' AS level,
catalog_name,
NULL AS schema_name,
NULL AS table_name,
grantee,
privilege_type
FROM system.information_schema.catalog_privileges
UNION ALL
SELECT
'schema' AS level,
catalog_name,
schema_name,
NULL AS table_name,
grantee,
privilege_type
FROM system.information_schema.schema_privileges
UNION ALL
SELECT
'table' AS level,
table_catalog AS catalog_name,
table_schema AS schema_name,
table_name,
grantee,
privilege_type
FROM system.information_schema.table_privileges
ORDER BY level, catalog_name, schema_name, table_name, grantee, privilege_type;
In terms of analysing system usage by user, here are some queries that should help you:
Analysing Usage For SQL Warehouses, attributing cost based on time
%sql
WITH wh_hourly_usd AS (
SELECT
u.usage_metadata.warehouse_id AS warehouse_id,
date_trunc('hour', u.usage_start_time) AS hour_ts,
SUM(u.usage_quantity * p.pricing.effective_list.default) AS usd_hour
FROM system.billing.usage AS u
JOIN system.billing.list_prices AS p
ON p.sku_name = u.sku_name
AND u.usage_end_time >= p.price_start_time
AND (p.price_end_time IS NULL OR u.usage_end_time < p.price_end_time)
WHERE u.usage_metadata.warehouse_id IS NOT NULL
GROUP BY ALL
),
wh_hourly_load AS (
SELECT
h.compute.warehouse_id AS warehouse_id,
date_trunc('hour', h.start_time) AS hour_ts,
h.executed_by AS user_email,
SUM(h.total_task_duration_ms) AS task_ms
FROM system.query.history AS h
WHERE h.compute.type = 'WAREHOUSE'
AND h.execution_status IN ('FINISHED','FAILED','CANCELED')
GROUP BY ALL
),
user_share AS (
SELECT
l.warehouse_id,
l.hour_ts,
l.user_email,
l.task_ms
/ NULLIF(SUM(l.task_ms) OVER (PARTITION BY l.warehouse_id, l.hour_ts), 0) AS share
FROM wh_hourly_load l
),
attributed AS (
SELECT
s.warehouse_id,
s.user_email,
SUM(s.share * u.usd_hour) AS usd_attributed
FROM user_share s
JOIN wh_hourly_usd u
ON s.warehouse_id = u.warehouse_id AND s.hour_ts = u.hour_ts
GROUP BY s.warehouse_id, s.user_email
)
SELECT warehouse_id, user_email, usd_attributed
FROM attributed
ORDER BY usd_attributed DESC;
Analysing costs for jobs
%sql
-- Estimated $USD per job run_as identity
SELECT
usage.usage_metadata.job_id AS job_id,
usage.usage_metadata.job_run_id AS job_run_id,
usage.identity_metadata.run_as AS run_as_identity,
SUM(usage.usage_quantity * prices.pricing.effective_list.default) AS est_cost_usd
FROM system.billing.usage AS usage
JOIN system.billing.list_prices AS prices
ON prices.sku_name = usage.sku_name
AND usage.usage_end_time >= prices.price_start_time
AND (prices.price_end_time IS NULL OR usage.usage_end_time < prices.price_end_time)
WHERE usage.usage_metadata.job_id IS NOT NULL
GROUP BY ALL
ORDER BY est_cost_usd DESC;
Costs for all purpose clusters, note for all purpose clusters you can only attribute costs to the person who set it up, you may want to thing about a tagging strategy to attribute to teams etc
SELECT
usage.usage_metadata.cluster_id AS cluster_id,
usage.custom_tags['creator'] AS creator_tag,
SUM(usage.usage_quantity) AS dbus
FROM system.billing.usage AS usage
WHERE usage.usage_metadata.cluster_id IS NOT NULL
GROUP BY ALL
ORDER BY dbus DESC;
