Skip to content

Instantly share code, notes, and snippets.

@Intelrunner

Intelrunner/bq_slot_usage.sql

Last active March 29, 2022 18:43
Show Gist options
  • Save Intelrunner/1a1c7448cba5cc14daa5ef8e164050dd to your computer and use it in GitHub Desktop.
Save Intelrunner/1a1c7448cba5cc14daa5ef8e164050dd to your computer and use it in GitHub Desktop.
Download ZIP
This Query Estimates slot usage per SECOND at an organizational level, segregated by project_id. Use: Finding average number of slot seconds per second to determine if purchasing Google Cloud Platform Bigquery Slots is valuable for an organization. Requires: bigquery.jobs.listAll
Raw
bq_slot_usage.sql
SELECT
TIMESTAMP_TRUNC(jobs.start_time, second) as sec,
SUM(SAFE_DIVIDE(total_slot_ms, TIMESTAMP_DIFF(end_time, start_time, MILLISECOND))) AS Slot_Count, project_ID as project
FROM
`region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION jobs
WHERE jobs.start_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY) AND CURRENT_TIMESTAMP()
GROUP BY project_id, sec
ORDER BY sec DESC
@Intelrunner
Copy link
Author

Recommend this is run at 3 day intervals for 1M and average usage is aggregated over that time. Inter-departmental data cycles need to be accounted for to prevent over-purchase.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment