[Views and opinions are my own.]
Databricks released System Tables, which allow a user with sufficient GRANTs to inspect system-level data regarding their account. I write this following the writing of one of my colleagues that provides a comprehensive overview of:
how these tables work,
what one may find within these tables, and
how to operationalize their use.
This post does not rehash these already well-attended topics. Instead, this shall serve as a sort of Quickstart guide for those who are technically savvy, up against a deadline, and just want to jump right in - as it were. I have written a great deal of SQL to query these tables for a number of use cases, and can reasonably state that the specific uses hereinafter shown are super common and can form the foundation for a dashboard of operational insights. We divide it into two sections: Billing and Informational.
We take some variables as given:
tag- Some queries search and organize information by the custom tags set on the securable objectinterval_hours- The time to look back for queries. Of course, this can be changed by the reader to fit the need.discount- if any exists, we want to get the accurate price with the discount, not the list price.
There is also plenty of use of SQL functions that can be read about at this link. The code below can be found in GitHub here.
Billing
Here there are two main tables:
system.billing.usagesystem.billing.list_prices
DBUs
These are the basic unit to measure usage; please read the content behind this link if you do not understand them. Without an understanding of how DBUs are emitted, no billing dashboard would be possible. The usage can be queried to give a great deal of information. Since this is a Quickstart, we’ll just show how to aggregate DBUs by the common features: cluster, Serverless SQL, and SKU. To agg over other features is a simple find-and-replace through these queries.
DBUs by cluster:
SELECT usage_metadata.cluster_id, SUM(usage_quantity) AS DBUs
FROM system.billing.usage
WHERE (usage_start_time >= current_timestamp() - make_dt_interval(0, :interval_hours)
OR usage_end_time >= current_timestamp() - make_dt_interval(0, :interval_hours))
AND usage_metadata.cluster_id IS NOT NULL
AND usage_unit = 'DBU'
GROUP BY (usage_metadata.cluster_id)
ORDER BY 2 DESCDBUs by tag:
SELECT tag, SUM(DBUs) AS DBUs
FROM (
SELECT CONCAT(:tag, '=', IFNULL(custom_tags[':tag'], 'null')) AS tag, usage_quantity AS DBUs
FROM system.billing.usage
WHERE (usage_start_time >= current_timestamp() - make_dt_interval(0, :interval_hours)
OR usage_end_time >= current_timestamp() - make_dt_interval(0, :interval_hours))
AND usage_metadata.cluster_id IS NOT NULL
AND usage_unit = 'DBU')
GROUP BY (tag)
ORDER BY 2 DESCDBUs by SKU for Classic Compute:
SELECT sku_name, SUM(usage_quantity) AS DBUs
FROM system.billing.usage
WHERE (usage_start_time >= current_timestamp() - make_dt_interval(0, :interval_hours)
OR usage_end_time >= current_timestamp() - make_dt_interval(0, :interval_hours))
AND usage_metadata.cluster_id IS NOT NULL
AND usage_unit = 'DBU'
GROUP BY (sku_name)
ORDER BY 2 DESCDBUs by SKU for Serverless SQL:
SELECT sku_name, SUM(usage_quantity) AS DBUs
FROM system.billing.usage
WHERE (usage_start_time >= current_timestamp() - make_dt_interval(0, :interval_hours)
OR usage_end_time >= current_timestamp() - make_dt_interval(0, :interval_hours))
AND usage_unit = 'DBU'
AND sku_name LIKE '%SERVERLESS_SQL%'
GROUP BY (sku_name)
ORDER BY 2 DESCCost
To find cost, we will need to understand usage as above and multiple by the price. This requires a join between the usage and pricing tables. Further, since pricing can change, we need to ensure we split the usage amounts and times between the price change boundaries. To make things simple, we employ CTEs.
WITH current_pricing AS (
SELECT
sku_name, price_start_time, price_end_time,
(
pricing.effective_list.default
* (1.000 - (:discount / 100.000))
) AS price_with_discount
FROM system.billing.list_prices
WHERE usage_unit='DBU'
AND currency_code='USD'
AND (price_start_time >= current_timestamp() - make_dt_interval(0, :interval_hours)
OR price_end_time >= current_timestamp() - make_dt_interval(0, :interval_hours)
OR price_end_time IS NULL)
),
current_usage AS (
SELECT sku_name, usage_start_time, usage_end_time, usage_quantity AS DBUs
FROM system.billing.usage
WHERE (usage_start_time >= current_timestamp() - make_dt_interval(0, :interval_hours)
OR usage_end_time >= current_timestamp() - make_dt_interval(0, :interval_hours))
AND usage_metadata.cluster_id IS NOT NULL
AND usage_unit = 'DBU'
),
current_usage_with_pricing AS (
SELECT
p.sku_name,
UNIX_MILLIS(p.price_start_time) AS price_start_time,
UNIX_MILLIS(p.price_end_time) AS price_end_time,
UNIX_MILLIS(u.usage_start_time) AS usage_start_time,
UNIX_MILLIS(u.usage_end_time) AS usage_end_time,
u.DBUs,
p.price_with_discount
FROM current_pricing p
JOIN current_usage u
ON p.sku_name = u.sku_name
AND (CASE
WHEN p.price_end_time IS NOT NULL THEN
(u.usage_start_time BETWEEN p.price_start_time AND p.price_end_time
OR u.usage_end_time BETWEEN p.price_start_time AND p.price_end_time)
ELSE
(u.usage_start_time >= p.price_start_time
OR u.usage_end_time >= p.price_start_time)
END
)
)
SELECT SUM(
(
(ARRAY_MIN(ARRAY(usage_end_time, price_end_time))
- ARRAY_MAX(ARRAY(usage_start_time, price_start_time))
)
/ (usage_end_time-usage_start_time)
) * DBUs * price_with_discount
) AS cost
FROM current_usage_with_pricingHere, we get the relevant pricing as current_pricing; the relevant usage as current_usage; join those together, and then aggregate the values. This should produce a value in dollars for cost of clusters within the time interval. Note the usage_metadata.cluster_id IS NOT NULL in the filter.
Just for good measure we exemplify a few more common uses.
%DBUs by SKU
WITH usage AS (
SELECT sku_name, usage_quantity
FROM system.billing.usage
WHERE usage_unit = 'DBU'
AND (usage_start_time >= current_timestamp() - make_dt_interval(0, :interval_hours)
OR usage_end_time >= current_timestamp() - make_dt_interval(0, :interval_hours))
),
total_usage AS (
-- OK to cross-join against this, it is a singleton dataset
SELECT SUM(usage_quantity) AS total
FROM usage
),
by_sku AS (
SELECT sku_name, SUM(usage_quantity) AS DBUs
FROM usage, total_usage
GROUP BY sku_name
)
SELECT sku_name, DBUs/total AS ratio
FROM by_sku, total_usage
ORDER BY 2 DESCDLT Maintenance Usage in DBUs
SELECT SUM(usage_quantity) dlt_maintenance_dbus
FROM system.billing.usage
WHERE usage_unit = 'DBU'
AND (usage_start_time >= current_timestamp() - make_dt_interval(0, :interval_hours)
OR usage_end_time >= current_timestamp() - make_dt_interval(0, :interval_hours))
AND usage_metadata.dlt_maintenance_id IS NOT NULLWarehouse Usage in DBUs
SELECT usage_metadata.warehouse_id, SUM(usage_quantity) warehouse_dbus
FROM system.billing.usage
WHERE usage_unit = 'DBU'
AND (usage_start_time >= current_timestamp() - make_dt_interval(0, :interval_hours)
OR usage_end_time >= current_timestamp() - make_dt_interval(0, :interval_hours))
AND usage_metadata.warehouse_id IS NOT NULL
GROUP BY usage_metadata.warehouse_id
ORDER BY warehouse_dbus DESCInformational
Operational complexity eventually rises to a level that no human can reasonably crunch down into a report. Always. Bet on it. We need the machine to do this work for us.
We add in more tables:
system.query.history
system.compute.node_types
system.compute.clusters
system.access.audit
Node Types that match a certain criteria
This can help a developer set up a suite of benchmarking tests for nodes within certain minimum and maximum size characteristics. A test tool can easily resubmit jobs with different node types and collect results using some of the queries above for review once complete.
SELECT
node_type,
memory_mb/1024 AS memory_gb,
core_count,
gpu_count
FROM system.compute.node_types
WHERE
-- Memory from 64GB to 512GB
memory_mb/1024 BETWEEN 64 AND 513
AND core_count > 16
ORDER BY
-- Some metric to track approx cost
core_count * memory_mb * (1+gpu_count) DESCLargest Clusters and their Owners
This enumerates recent clusters, orders by size, and shows the most significant first. This can help track either the processing leader or waste leader, depending upon the circumstances.
WITH clusters AS (
SELECT *
FROM system.compute.clusters
WHERE delete_time IS NULL -- current
OR create_time >= (current_timestamp() - make_dt_interval(0, :interval_hours)) -- recent
QUALIFY 1 = (ROW_NUMBER() OVER (
PARTITION BY cluster_id
ORDER BY
change_time DESC
))
),
drivers AS (
SELECT c.owned_by, c.cluster_id, nt.core_count, nt.memory_mb/1024 AS memory_gb, nt.gpu_count
FROM system.compute.node_types nt,
clusters c
WHERE nt.node_type = c.driver_node_type
),
workers AS (
SELECT c.owned_by, c.cluster_id, nt.core_count, nt.memory_mb/1024 AS memory_gb, nt.gpu_count, COALESCE(c.worker_count, c.max_autoscale_workers) AS qty
FROM system.compute.node_types nt,
clusters c
WHERE nt.node_type = c.worker_node_type
)
SELECT DISTINCT
drivers.owned_by,
drivers.cluster_id,
drivers.core_count + (workers.core_count * workers.qty) AS total_cores,
drivers.memory_gb + (workers.memory_gb * workers.qty) AS total_memory_gb,
drivers.gpu_count + (workers.gpu_count * workers.qty) AS total_gpus
FROM drivers, workers
WHERE drivers.cluster_id = workers.cluster_id
ORDER BY total_cores*total_memory_gb*(1+total_gpus) DESCMinimum DBR in use and by whom
This chooses the minimum DBR version currently being used by any cluster and which users own those low-version clusters. Useful during an upgrade cycle.
WITH dbrs AS (
SELECT regexp_extract(dbr_version, '(\\d+\\.\\d+\\.(\\d|\\w)+)') AS dbr
FROM system.compute.clusters
WHERE delete_time IS NULL -- current
OR create_time >= (current_timestamp() - make_dt_interval(0, :interval_hours)) -- recent
),
min_dbr AS(
SELECT MIN(dbr) AS dbr
FROM dbrs
WHERE dbr IS NOT NULL
AND '' <> dbr
)
SELECT DISTINCT owned_by, dbr
FROM system.compute.clusters, min_dbr
WHERE dbr_version LIKE CONCAT('%',dbr,'%')Relative Query rates by Status
Provides the percentage of total queries are successful, cancelled, or failed.
WITH totals AS (
SELECT execution_status, COUNT(*) qty
FROM system.query.history
WHERE execution_status IN ('FINISHED','CANCELED','FAILED') -- Completed only
AND (start_time >= current_timestamp() - make_dt_interval(0, :interval_hours)
OR end_time >= current_timestamp() - make_dt_interval(0, :interval_hours))
GROUP BY execution_status
),
total_runs AS (
SELECT SUM(qty) AS total
FROM totals
)
SELECT execution_status, qty/total AS ratio
FROM totals, total_runsWhy Queries Fail
If a high failure rate is shown above, this will cause extra costs due to cluster use without returning results. Best to find the failure reasons that are most common and educate the team about those failures.
WITH errors AS (
SELECT
COALESCE(
NULLIF(REGEXP_EXTRACT(error_message, '\\[(.*?)\\]'), ''),
NULLIF(REGEXP_EXTRACT(error_message, '(\\w+((Exception)|(Error)):)'), ''),
error_message
) AS error_type
FROM system.query.history
WHERE error_message IS NOT NULL
AND (start_time >= current_timestamp() - make_dt_interval(0, :interval_hours)
OR end_time >= current_timestamp() - make_dt_interval(0, :interval_hours))
)
SELECT error_type, COUNT(*) AS qty
FROM errors
GROUP BY error_type
ORDER BY qty DESCClusters that do not bear the owner’s name
It is common to enforce a naming convention. Queries like this can help find holes in your policies.
SELECT cluster_id, cluster_name, owned_by
FROM system.compute.clusters
WHERE cluster_name IS NOT NULL -- clusters with names
AND owned_by IS NOT NULL -- clusters with Owners
AND NOT REGEXP(LOWER(cluster_name), 'job-\\d+-run-\\d+.*') -- ignore jobs
AND NOT REGEXP(
REPLACE(LOWER(cluster_name), ' '),
REPLACE(SUBSTR(LOWER(owned_by) FROM 1 FOR INSTR(owned_by, '@')-1), '.', '.?')
)Job Triggers by creator and type
Who is triggering jobs through which means. Who is triggering the most.
SELECT
request_params['runCreatorUserName'] AS creator,
request_params['jobTriggerType'] AS trigger,
COUNT(*) AS qty
FROM system.access.audit
WHERE service_name = 'jobs'
AND action_name = 'runTriggered'
AND event_time >= (current_timestamp() - make_dt_interval(0, :interval_hours))
GROUP BY all
ORDER BY qty DESCActive Users
How widespread is adoption?
SELECT COUNT(DISTINCT user_identity)
FROM system.access.audit
WHERE event_time >= (current_timestamp() - make_dt_interval(0, :interval_hours))
AND user_identity.email LIKE '%@%'Failure Leaders
Which usernames are causing the most failure responses in the audit trail? This could be indicative of networking issues or malicious intent.
SELECT user_identity.email AS email, COUNT(*) AS qty
FROM system.access.audit
WHERE event_time >= (current_timestamp() - make_dt_interval(0, :interval_hours))
AND user_identity.email LIKE '%@%'
AND NOT (response.status_code BETWEEN 200 AND 300)
GROUP BY ALL
ORDER BY qty DESCSuccessful DB SQL Downloads by Hour
DB SQL exfiltration rate tracker. Exfil can be extremely costly depending upon cloud vendor terms; this is a hidden direct cost that is often overlooked until it is too late.
SELECT DATE_FORMAT(event_time, 'yyyyMMddHH') AS the_hour, COUNT(*) AS qty
FROM system.access.audit
WHERE event_time >= (current_timestamp() - make_dt_interval(0, :interval_hours))
AND service_name = 'databrickssql'
AND action_name = 'downloadQueryResult'
AND response.status_code = 200
GROUP BY ALL
ORDER BY the_hour DESC