Introduction

GCP BigQuery query times out when query too complex or slot allocation insufficient. This guide provides step-by-step diagnosis and resolution.

Symptoms

Typical error output:

bash
Error: Query execution exceeded timeout
Query would use too much CPU: estimated 1000 slots
JOB_TIMEOUT: job exceeded maximum execution time

Common Causes

  1. 1.Query too complex without proper partitioning
  2. 2.On-demand pricing exceeds slot estimation
  3. 3.Query scans large table without column pruning
  4. 4.Recursive CTE or nested subquery too deep

Step-by-Step Fix

Step 1: Check Current State

bash
bq show --job=true <job-id>
bq query --use_legacy_sql=false --max_query_time=60000
gcloud logging read "resource.type=bigquery"

Step 2: Identify Root Cause

bash
gcloud logging read --project=<project> --filter="severity>=ERROR"

Step 3: Apply Primary Fix

bash # Increase query timeout and optimize bq query --use_legacy_sql=false --max_query_time=600000 --maximum_bytes_billed=1000000000000 "SELECT * FROM project.dataset.table` WHERE DATE(timestamp) = CURRENT_DATE()"

# Use partitioned table and column pruning SELECT specific_columns FROM table WHERE _PARTITIONDATE = DATE(...) ```

Step 4: Apply Alternative Fix

```bash # Alternative fix: Check configuration gcloud resource describe <resource> --project=<project> --format=yaml

# Update specific properties gcloud resource update <resource> --project=<project> --<flag>=<value>

# Verify the fix gcloud resource describe <resource> --project=<project> --format=json ```

Step 5: Verify the Fix

bash
bq show --job=true <job-id>
bq ls -j --max_results=10

Common Pitfalls

  • Forgetting to check regional quotas before provisioning
  • Not waiting for async operations to complete before next step
  • Missing IAM permissions for GCP resource operations
  • Confusing zone-level and region-level resources

Best Practices

  • Always check quotas before provisioning new resources
  • Use GCP Cloud Monitoring for observability
  • Implement proper error handling in gcloud scripts
  • Enable logging for all critical GCP resources
  • GCP Quota Exceeded
  • GCP Resource Deployment Failed
  • GCP Network Connectivity Issues
  • GCP IAM Permission Denied