Databricks Systems Tables Quickstart

[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:

  1. how these tables work,

  2. what one may find within these tables, and

  3. 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 object

  • interval_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:

  1. system.billing.usage

  2. system.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 DESC

DBUs 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 DESC

DBUs 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 DESC

DBUs 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 DESC

Cost

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_pricing

Here, 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 DESC

DLT 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 NULL

Warehouse 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 DESC

Informational

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) DESC

Largest 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) DESC

Minimum 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_runs

Why 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 DESC

Clusters 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 DESC

Active 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 DESC

Successful 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