How to fetch max or avg worker nodes for a specific time period using system tables?

pranitha
New Contributor II

‎01-21-2025 02:58 AM

 
0 Kudos

Alberto_Umana
Databricks Employee
Databricks Employee

‎01-21-2025 04:30 AM

Hi @pranitha,

You can utilize the node_timeline table. https://docs.databricks.com/en/admin/system-tables/compute.html

0 Kudos

saurabh18cs
Honored Contributor III

‎01-21-2025 05:22 AM - edited ‎01-21-2025 05:25 AM

try this 

SELECT
  cluster_id,
 MAX(max_autoscale_workers) AS max_workers,
  DATE_TRUNC('day', change_time) AS day
FROM
    system.compute.clusters
WHERE
  change_time BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY
  cluster_id,
  DATE_TRUNC('day', change_time)
ORDER BY
  day;
0 Kudos

MadhuB
Valued Contributor

‎01-21-2025 06:45 AM

Hi @pranitha 

Use this query to get the cluster details along with cost info as well.

 

WITH hourly_metrics AS (
  SELECT
    date_trunc('hour', usage_start_time) as hour,
    usage_metadata.cluster_id,
    sku_name,
    MAX(usage_quantity) as max_usage,
    ROUND(AVG(usage_quantity), 2) as avg_usage,
    SUM(usage_quantity) as DBUs_consumed
  FROM system.billing.usage
  WHERE usage_start_time BETWEEN '2025-01-01' AND '2025-01-21'
  GROUP BY
    date_trunc('hour', usage_start_time),
    usage_metadata.cluster_id,
    sku_name
),
cost_calculation AS (
  SELECT
    h.*,
    l.pricing.default AS unitprice,
    ROUND((h.DBUs_consumed * l.pricing.default)) AS total_cost
  FROM hourly_metrics h
  LEFT OUTER JOIN system.billing.list_prices l ON l.sku_name = h.sku_name
)
SELECT
  cluster_id,
  sku_name,
  hour,
  max_usage,
  avg_usage,
  DBUs_consumed,
  unitprice,
  total_cost
FROM cost_calculation
ORDER BY hour DESC, total_cost DESC;
0 Kudos