topic Re: How to fetch max or avg worker nodes for a specific time period using system tables? in Data Engineering

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

pranitha — Tue, 21 Jan 2025 10:58:34 GMT

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

Alberto_Umana — Tue, 21 Jan 2025 12:30:00 GMT

Hi @pranitha,

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

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

saurabh18cs — Tue, 21 Jan 2025 13:25:51 GMT

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;

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

MadhuB — Tue, 21 Jan 2025 14:45:21 GMT

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;