BigQuery Cost Check

-- Finitizer BigQuery Cost Check, query 1 of 3: compute, hourly totals only.
-- Returns no query text, no table names and no user identities.
SELECT
  TIMESTAMP_TRUNC(creation_time, HOUR)                         AS hour,
  COUNT(*)                                                     AS jobs,
  SUM(total_bytes_billed)                                      AS bytes_billed,
  SUM(total_bytes_processed)                                   AS bytes_processed,
  SUM(total_slot_ms)                                           AS slot_ms,
  COUNTIF(reservation_id IS NOT NULL)                          AS reserved_jobs,
  SUM(IF(reservation_id IS NOT NULL, total_slot_ms, 0))        AS reserved_slot_ms
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND statement_type != 'SCRIPT'
  AND total_slot_ms IS NOT NULL
GROUP BY hour
ORDER BY hour;

-- Finitizer BigQuery Cost Check, query 2 of 3: the 50 heaviest query shapes.
-- A shape is BigQuery's hash of the query with its literals removed, so the
-- same report run with different dates is one shape. Returns the hash, never
-- the query text, and a COUNT of users, never who they are.
SELECT
  query_info.query_hashes.normalized_literals                  AS pattern,
  COUNT(*)                                                     AS runs,
  SUM(total_bytes_billed)                                      AS bytes_billed,
  SUM(total_bytes_processed)                                   AS bytes_processed,
  SUM(total_slot_ms)                                           AS slot_ms,
  COUNTIF(cache_hit)                                           AS cache_hits,
  COUNT(DISTINCT user_email)                                   AS users
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND statement_type != 'SCRIPT'
  AND total_slot_ms IS NOT NULL
  AND query_info.query_hashes.normalized_literals IS NOT NULL
GROUP BY pattern
ORDER BY bytes_processed DESC
LIMIT 50;

-- Finitizer BigQuery Cost Check, query 3 of 3: storage by dataset.
-- Returns dataset names (the advice is per dataset) and byte totals. The names
-- stay in your browser; an emailed report carries totals only.
SELECT
  s.table_schema                                               AS dataset,
  COUNT(*)                                                     AS tables,
  SUM(s.active_logical_bytes)                                  AS active_logical_bytes,
  SUM(s.long_term_logical_bytes)                               AS long_term_logical_bytes,
  SUM(s.active_physical_bytes)                                 AS active_physical_bytes,
  SUM(s.long_term_physical_bytes)                              AS long_term_physical_bytes,
  SUM(s.time_travel_physical_bytes)                            AS time_travel_physical_bytes,
  SUM(s.fail_safe_physical_bytes)                              AS fail_safe_physical_bytes,
  ANY_VALUE(o.option_value)                                    AS billing_model
FROM `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE s
LEFT JOIN `region-us`.INFORMATION_SCHEMA.SCHEMATA_OPTIONS o
  ON o.schema_name = s.table_schema AND o.option_name = 'storage_billing_model'
WHERE NOT s.deleted
GROUP BY dataset
ORDER BY SUM(s.active_logical_bytes + s.long_term_logical_bytes) DESC;