Custom Tag Usage Reporting

[pksilver]

Hi Team,

Does anyone have a good SQL query that I can use for showing usage costs against custom tags for example on clusters. The Account Console usage report is good but I can only seem to query one custom tag at a time and ideally I want a dashboard that will show them all with a timeframe selection of week. month and day. Thanks in advance

[Alberto_Umana]

Hi @pksilver,

Here's an example query you can use:

Please also see: https://docs.databricks.com/en/admin/account-settings/usage.html

SELECT
usage_date,
cluster_id,
custom_tag,
SUM(cost) AS total_cost
FROM
system.billing.usage
WHERE
usage_date BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) AND CURRENT_DATE -- For weekly timeframe
AND custom_tag IS NOT NULL
GROUP BY
usage_date,
cluster_id,
custom_tag
ORDER BY
usage_date,
cluster_id,
custom_tag;

[pksilver]

Thanks, Im getting errors when i run this in SQL Editor, should i be running this against the system.billing catalog/schema?

[pksilver]

SELECT

usage_date,

cluster_id,

custom_tag,

SUM(cost) AS total_cost

FROM

system.billing.usage

WHERE

usage_date BETWEEN date_sub(CURRENT_DATE, 7) AND CURRENT_DATE -- For weekly timeframe

AND custom_tag[] IS NOT NULL

GROUP BY

usage_date,

cluster_id,

custom_tag

ORDER BY

usage_date,

cluster_id,

[PARSE_SYNTAX_ERROR] Syntax error at or near ']'. SQLSTATE: 42601 line 10, pos 15

[Alberto_Umana]

Hi @pksilver,

Please use this one, which is updated:

%sql

SELECT

usage_date,

usage_metadata.cluster_id,

custom_tags,

SUM(usage_quantity) AS total_cost

FROM

system.billing.usage

WHERE

usage_date BETWEEN date_sub(CURRENT_DATE, 7) AND CURRENT_DATE -- For weekly timeframe

AND custom_tags IS NOT NULL

GROUP BY

usage_date,

usage_metadata.cluster_id,

custom_tags

ORDER BY

usage_date,

usage_metadata.cluster_id

[pksilver]

Thanks, and I run this against system.billing schema?

[Alberto_Umana]

That is correct 🙂 

[pksilver]

ok this is what i get now;

[INTERNAL_ERROR] Couldn't find custom_tags#1085 in [usage_date#1084,_groupingexpression#1106,mapsort(custom_tags#1085)#1130,sum(usage_quantity#1087)#1100] SQLSTATE: XX000

[Alberto_Umana]

Can you run select * from system.billing schema limit 20;  And validate the custom_tags field.

This query should work since it's a system table that has same column, I just tested it 🙂 

[Alberto_Umana]

For reference:

[pksilver]

Ok I was running this from SQL Editor not as python notebook so maybe that was the issue, its run ok now, im hoping i can now turn this into a visualization and put it on a dash?