`EVENT`, event_time as `WHEN`, IFNULL(request_params.full_name_arg, 'Non-specific') AS `TABLE ACCESSED`, IFNULL(request_params.commandText, 'GET table') AS `QUERY TEXT` FROM system.access.audit WHERE user_identity.email = '{{User}}' AND action_name IN ('createTable', 'commandSubmit','getTable','deleteTable') -- AND datediff(now(), event_date) < 1 -- ORDER BY event_date DESC Query Sample Which tables did a user access?
sum(usage_quantity) as `DBUs` FROM system.billing.usage WHERE month(usage_date) = month(NOW()) AND year(usage_date) = year(NOW()) GROUP BY sku_name, usage_date Query Sample How many DBUs of each SKU were used this month?
`Job ID`, sum(usage_quantity) as `DBUs` FROM system.billing.usage WHERE usage_metadata.job_id IS NOT NULL GROUP BY `Job ID` ORDER BY `DBUs` DESC Query Sample Which jobs consumed the most DBU?
after_dbus, ((after_dbus - before_dbus)/before_dbus * 100) AS growth_rate FROM (SELECT sku_name, sum(usage_quantity) as before_dbus FROM system.billing.usage WHERE usage_date BETWEEN "2023-04-01" and "2023-04-30" GROUP BY sku_name) as before JOIN (SELECT sku_name, sum(usage_quantity) as after_dbus FROM system.billing.usage WHERE usage_date BETWEEN "2023-05-01" and "2023-05-30" GROUP BY sku_name) as after where before.sku_name = after.sku_name SORT by growth_rate DESC Query Sample Show me the SKUs growing in usage
SUM(usage_quantity) as `DBUs consumed` FROM system.billing.usage WHERE custom_tags.{{key}} = "{{value}}" GROUP BY 1, 2 Query Sample What consumption is assigned to resources with a certain tag?
c.cluster_id cluster_id, max_by(c.owned_by, c.change_time) owned_by, max(c.change_time) change_time, any_value(u.usage_start_time) usage_start_time, any_value(u.usage_quantity) usage_quantity FROM system.billing.usage u JOIN system.compute.clusters c WHERE u.usage_metadata.cluster_id is not null and u.usage_start_time >= '2023-01-01' and u.usage_metadata.cluster_id = c.cluster_id and c.change_time <= u.usage_start_time GROUP BY 1, 2 ORDER BY cluster_id, usage_start_time desc; Query Sample Which cluster owners use the most DBUs?
400000 as dbu_used_ratio from system.billing.usage where usage_date > date_trunc("month", now()) select usage_quantity as dbu_used_ratio from system.billing.usage select sum(usage_quantity) / 400000 as dbu_used_ratio from system.billing.usage Query Sample Notification: As an administrator, I want to receive a notification if the monthly budget is about to be consumed
need are tracked with the commandSubmit, commandFinish, runCommand events (see Databrickssql logs reference for more information). Query Sample Identify long-running queries and track inefficient code running through Notebooks with System Tables and Lakeview
Activate verbose audit logs Once enabled, Databricks will populate the system.access.audit table with the events in question. We can then run the queries and create visualizations to spot areas where we need to pay more attention to reduce costs. Query Sample Identify long-running queries and track inefficient code running through Notebooks with System Tables and Lakeview
Prepare dataset SELECT event_date, user_identity.email, request_params.notebookId, request_params.clusterId, request_params.executionTime, request_params.status, request_params.commandLanguage, request_params.commandId, request_params.commandText FROM system.access.audit WHERE 1=1 AND action_name = 'runCommand' AND request_params.status NOT IN ('skipped') AND TIMESTAMPDIFF(HOUR, event_date, CURRENT_TIMESTAMP()) < 24 * 90 ORDER BY request_params.executionTime DESC SELECT action_name as `EVENT`, event_time as `WHEN`, IFNULL(request_params.full_name_arg, 'Non-specific') AS `TABLE ACCESSED`, IFNULL(request_params.commandText,'GET table') AS `QUERY TEXT` FROM system.access.audit WHERE user_identity.email like '[email protected]' AND action_name IN ('createTable', 'commandSubmit','getTable','deleteTable') AND datediff(now(), event_date) < 1 Query Sample Identify long-running queries and track inefficient code running through Notebooks with System Tables and Lakeview
to execute the query at least once Query Sample Identify long-running queries and track inefficient code running through Notebooks with System Tables and Lakeview Step 3 : Create dashboard
databricksjapan
kommy339
kakehashi
ju_hnny5
yu4u
guyrleech
____rina____
oracle4engineer
ma2shita
naohachi89
sashimimochi
toru_kubota
pfn
lara
mza
tammielis
addyosmani
akosma
tanoku
holman
sstephenson
notwaldorf
chriscoyier
eileencodes
