Analyze BigQuery Usage

Analyze BigQuery Usage

This skill performs comprehensive analysis of BigQuery usage patterns, costs, and query performance for a given project. It identifies expensive queries, heavy users, and provides actionable optimization recommendations.

When to Use This Skill

This skill is automatically invoked by the /bigquery:analyze-usage command to perform usage analysis.

Prerequisites

• Google Cloud SDK (bq command-line tool) must be installed
• User must have BigQuery read access to the project
• User must be authenticated (gcloud auth login)
• User needs bigquery.jobs.list permission at minimum

Parameters

When invoked, this skill expects:

• Project ID: The GCP project ID to analyze (required)
• Timeframe: Time period for analysis in hours (e.g., 24, 168 for 7 days)

Analysis Workflow

1. Validate Prerequisites

First, verify the environment is ready:

• Check if bq command is available
• Verify project access
• Parse timeframe into hours

2. Collect Usage Data

Execute the following BigQuery queries against INFORMATION_SCHEMA:

Total Usage Summary

SELECT
  COUNT(*) as total_queries,
  ROUND(SUM(total_bytes_processed) / POW(10, 12), 2) as total_tb_scanned,
  ROUND(SUM(total_bytes_processed) / POW(10, 12) * 6.25, 2) as estimated_cost_usd
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL @hours HOUR)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND statement_type != 'SCRIPT'

Usage by User/Service Account

SELECT
  user_email,
  COUNT(*) as query_count,
  ROUND(SUM(total_bytes_processed) / POW(10, 12), 2) as total_tb_scanned,
  ROUND(SUM(total_bytes_processed) / POW(10, 12) * 6.25, 2) as estimated_cost_usd,
  ROUND(AVG(total_bytes_processed) / POW(10, 9), 2) as avg_gb_per_query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL @hours HOUR)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND statement_type != 'SCRIPT'
GROUP BY user_email
ORDER BY total_tb_scanned DESC
LIMIT 20