Sample customized queries for dashboard visualizations from Overwatch schema:

1.       select sku, isActive, any_value(contract_price) * count(*) as cost from overwatch.`dbucostdetails`

group by sku, isActive

having isActive = true;

 

sku        isActive cost

jobsLight            True 0.30000000000000004

interactive          True   1.6500000000000001

sqlCompute       True              0.66

automated         True 0.30000000000000004

 

2.       SELECT created_by, count(*) FROM (SELECT DISTINCT cluster_id, created_by FROM overwatch.`cluster`)

GROUP BY created_by

ORDER BY count(*) desc

limit 1000;

 

created_by              count(1)

JobsService        20051

User1    13

User2    13

User3    6

User4    3

User5    2

User6    1

 

3.       SELECT cluster_id, SUM(uptime_in_state_S) as uptime FROM overwatch.clusterstatefact

GROUP BY cluster_id

ORDER BY uptime DESC

limit 1000;

 

cluster_id           uptime

0822-134022-ssn7p7zy   2656586.3910000008

0909-211040-g7gw6ze     2655716.523000001

0914-142202-nx0u3s1a   2634530.8240000005

0907-170325-qf4ypd19   2611126.8639999996

0109-204324-dba1c5o   2602285.5589999994

0831-160354-2gds4r56     2601205.147000001

0728-171334-wqfvw8lm              2599745.636

1220-150950-1xfqwfeq              2533890.514

0828-204151-rqw3um2a   1986805.3609999998

0302-190420-h8rv9prn   1983515.9470000002

0803-144506-g98h4fl2   1975430.0520000001

0908-095703-w31xe9fb   1842740.3310000005

0917-185549-g4n3dqjl              1052153.248

0918-031805-t3zdjacw              1002694.213

 

4.       SELECT created_by, sum(total_dbu_cost) as sum_dbu_cost FROM

(SELECT distinct cluster_id, job_id, created_by, terminal_state, total_dbu_cost from overwatch.jobruncostpotentialfact where  terminal_state = "Succeeded")

GROUP BY created_by

HAVING created_by != 'null'

ORDER BY sum_dbu_cost desc

limit 1000;

 

 

created_by              sum_dbu_cost

User1   253.60490000000007

User2     83.07065199999978

User3     80.84025400000019

User4    58.004314

User5     56.34171099999961

User6     49.40466399999997

User7    12.238729

User8    2.528845

User9   1.4531079999999597

User10   0.4258950000000001

User11  0.30644

User12 0.17414799999999972