Analyzing Google Cloud spend with Datasette#

1
sqlite-utils insert /tmp/billing.db lines bq-results-20220816-213359-1660685720334.json --nl

1
sqlite-utils /tmp/subset.db --attach billing /tmp/billing.db '
2
create table items as select
3
  json_extract(invoice, "$.month") as month,
4
  cost,
5
  json_extract(service, "$.description") as service,
6
  json_extract(sku, "$.description") as sku_description,
7
  usage_start_time,
8
  usage_end_time,
9
  json_extract(project, "$.id") as project_id,
10
  json_extract(labels, "$[0].value") as service_name,
11
  json_extract(location, "$.location") as location,
12
  json_extract(resource, "$.name") as resource_name,
13
  currency,
14
  currency_conversion_rate,
15
  credits,
16
  invoice,
17
  cost_type,
18
  adjustment_info
19
from
20
  billing.lines
21
'

1
select month, count(*), sum(cost) from items group by month

1
select service_name, cast('' || sum(cost) as float) as total_cost
2
from items group by service_name order by total_cost desc;