Przejdź do treści
Biblioteka skilli
DevOps i chmura · Analiza

bigquery-observability

/bigquery-observability

Pobiera telemetrię BigQuery z INFORMATION_SCHEMA, Cloud Monitoring i REST API, by diagnozować zadania, sloty i kolejki.

Licencja
Apache-2.0
Ostatnia zmiana repozytorium
12.11.2026

Ocena i bezpieczeństwo

100/ 100Klasa: dobry
Format25 / 25
Frontmatter z nazwą i opisem, poprawna nazwa, opis do 1024 znaków, nazwa zgodna z folderem.
Odkrywalność25 / 25
Opis mówi, kiedy użyć skilla, ma sensowną długość i jest w 3. osobie.
Treść35 / 35
Co najmniej 150 słów i do 500 linii, nagłówki, listy kroków, przykłady albo kod.
Higiena15 / 15
Bez zaślepek typu TODO, bez ścieżek Windows, z licencją.
Ryzykobrak sygnałów w skanie
Pliki w paczce
10
Skrypty
0
Komentarze HTML
2
Adresy URL w SKILL.md
0
Przestarzałość

Brak sygnałów. Repozytorium zmienione 12.11.2026, bez odwołań do wycofanych modeli i API.

Licencja

Apache-2.0, otwarta: można używać i zmieniać, z zachowaniem licencji i informacji o zmianach.

Co sprawdzić przed instalacją
  • SKILL.md zawiera komentarze HTML (2). Nie widać ich w podglądzie Markdown, a model je czyta.

Co robi i kiedy

Pobiera telemetrię BigQuery z INFORMATION_SCHEMA, Cloud Monitoring i REST API, by diagnozować zadania, sloty i kolejki.

Oryginalny opis (EN)

Provides data-retrieval best practices, tool selection guidance, and performant SQL query syntax for BigQuery telemetry across INFORMATION_SCHEMA, Cloud Monitoring, and the REST API. Use when the telemetry to fetch is already known, selecting telemetry tools, writing performant INFORMATION_SCHEMA queries, retrieving telemetry for diagnosing single-job performance bottlenecks, investigating slot contention, job concurrency and queue latency, analyzing reservation capacity, utilization and autoscaling saturation, or auditing capacity-based and on-demand compute and storage resource billable usag

Jak działa

  1. Tool Selection
  2. Workflow
  3. Best Practices for Writing INFORMATIONSCHEMA Queries
  4. Domain References & SQL Queries
Sekcje z nagłówków SKILL.md, w oryginale · 205 linii, 1 211 słów
Podgląd SKILL.md
---
name: bigquery-observability
license: Apache-2.0
metadata:
  version: v3
  publisher: google
description: >-
  Provides data-retrieval best practices, tool selection guidance, and
  performant SQL query syntax for BigQuery telemetry across INFORMATION_SCHEMA,
  Cloud Monitoring, and the REST API. Use when the telemetry to fetch is already
  known, selecting telemetry tools, writing performant INFORMATION_SCHEMA
  queries, retrieving telemetry for diagnosing single-job performance
  bottlenecks, investigating slot contention, job concurrency and queue latency,
  analyzing reservation capacity, utilization and autoscaling saturation, or
  auditing capacity-based and on-demand compute and storage resource billable
  usage. Don't use for root-cause diagnosis or symptom troubleshooting when the
  cause is unknown (use bigquery-troubleshooting first), or for writing or
  optimizing business logic SQL (use bigquery-optimization).
---

# BigQuery Observability

## Tool Selection

<!-- mdformat off -->

| Tool | Primary Use Cases | Strengths & Capabilities | When to Avoid / Limitations |
| --- | --- | --- | --- |
| **`INFORMATION_SCHEMA` (`I_S`)** | Historical analysis, cohort comparison (`normalized_literals`), discovery of fast/slow windows, reservation/project timelines, multi-job aggregates, cost/billing tracing. | Flexible SQL querying across `JOBS`, `JOBS_TIMELINE`, and `RESERVATIONS`; supports custom time windows and grouping. | Avoid for high-frequency real-time polling or single-job point-lookups (can consume slots and take seconds to execute). |
| **REST API (`jobs.api` / `reservation.api`)** | Single-job point-lookup, real-time stage bottleneck diagnosis, automated pipeline status checks, reservation/capacity commitment configuration inspection (`reservations.get`, `reservations.list`). | Zero-SQL overhead, fast REST/CLI point-lookups (`bq show -j`, `bq show --reservation`), instant access to `performanceInsights`, `queryPlan`, and structural metadata. | Avoid for aggregate analysis across thousands of jobs, cross-project historical comparison, or system timeline aggregations. |
| **`Cloud Monitoring` (Metrics Explorer / Charts)** | Real-time alerting, fleet-wide dashboards, continuous slot utilization tracking, high-level SLA/SLO monitoring. | Out-of-the-box charts for slot utilization, query throughput, `PENDING` queue depth, and execution latency; low-latency alerting without running queries. | Avoid for SQL-level debugging, individual query text inspection, or stage-level execution detail. |

<!-- mdformat on -->

## Workflow

1.  **Single-Job Point-Lookup (Zero-SQL Overhead):** For single-job slowness or
    inspection, always prioritize the REST API or CLI (`bq show -j`) first. It
    provides zero-SQL overhead and fast point-lookups for internal stage
    bottlenecks (`performanceInsights`, `queryPlan`, shuffle spill).

    ```bash
    bq show --location={location} -j {project_id}:{job_id}
    ```

2.  **Diagnostic Transition Logic:** If no job-level issues are found (e.g. no
    clear internal bottlenecks), the investigation should transition to
    system-level `INFORMATION_SCHEMA` queries (such as `JOBS_TIMELINE` or
    `RESERVATIONS_TIMELINE`) to check for broader issues like slot contention,
    queueing delay, or noisy neighbors.

## Best Practices for Writing `INFORMATION_SCHEMA` Queries

Every query against a BigQuery `INFORMATION_SCHEMA` view must be qualified with
either a **region qualifier** or a **dataset qualifier**, optionally prefixed by
a **project qualifier**.

### Qualification Syntax & Scope Matching

1.  **Region-Qualified Syntax:**

    ```googlesql
    `{project_id}`.`region-{region}`.INFORMATION_SCHEMA.{view}
    ```

    *Example:* `` `my-project`.`region-us`.INFORMATION_SCHEMA.JOBS``

    *Applies to:* Regional telemetry views (`JOBS*`, `JOBS_TIMELINE*`,
    `RESERVATIONS*`, `CAPACITY_COMMITMENTS*`, `TABLE_STORAGE*`,
    `STREAMING_TIMELINE*`). The client query execution location MUST match the
    `region-{region}` qualifier (or BigQuery throws: `Not found: Table
    {project_id}:region-{region}.INFORMATION_SCHEMA.{view} was not found in
    location {location}`).

2.  **Dataset-Qualified Syntax:**

    ```googlesql
    `{project_id}`.`{dataset_id}`.INFORMATION_SCHEMA.{view}
    ```

    *Example:* `` `my-project`.`analytics`.INFORMATION_SCHEMA.TABLES``

    *Applies to:* Dataset-scoped views (`PARTITIONS`, `SEARCH_INDEXES*`,
    `ROW_ACCESS_POLICIES`). Never use `region-` with dataset views.

3.  **Dual-Scoped Views:** Views like `TABLES`, `COLUMNS`, `COLUMN_FIELD_PATHS`,
    `VIEWS`, `ROUTINES`, and `VECTOR_INDEXES` can be qualified with either
    `{dataset_id}` or `region-{region}` depending on whether dataset or
    region-wide analysis is required.

4.  **Project Qualifier (`{project_id}`):** Optional. If omitted, queries
    default to the project in which the query is executing. Specifying a project
    qualifier on organization-level views (e.g. `JOBS_BY_ORGANIZATION`) has no
    impact on results.

### Principle of Least Privilege & Scope Selection

When constructing `INFORMATION_SCHEMA` queries, **always select the scope and
view variant with the least IAM permission requirement** that satisfies the
analytical need:

1.  **User-Level over Project-Level (`_BY_USER`):** When diagnosing queries or
    sessions executed by the current user, use `_BY_USER` (e.g. `JOBS_BY_USER`,
    `SESSIONS_BY_USER`). This requires only `bigquery.jobs.list` (granted via
    `roles/bigquery.user`), avoiding the need for `bigquery.jobs.listAll` or
    `roles/bigquery.admin`. If `JOBS_BY_USER` also returns `403 Access Denied`
    (e.g. when the caller only holds `roles/bigquery.jobUser`, which grants
    `bigquery.jobs.create` but not `bigquery.jobs.list`), fall back to
    `bq ls -j -n {max_results} {project_id}` for a zero-compute CLI job listing.
2.  **Dataset-Level over Region/Project-Level:** When querying table metadata,
    columns, or views for a specific dataset, qualify with `{dataset_id}` rather
    than `region-{region}` when project-level metadata access is restricted.
    Dataset-scoped queries require permissions only on that target dataset.
3.  **Project-Level over Org/Folder-Level (`_BY_PROJECT`):** Always start with
    project-scoped views before escalating to `_BY_FOLDER` or
    `_BY_ORGANIZATION`. Folder and organization queries require broad folder/org
    IAM permissions (`bigquery.jobs.listAll` or `bigquery.tables.list` at the
    Org/Folder node).
4.  **Metadata Roles over Data Roles:** For table and storage introspection,
    prefer `roles/bigquery.metadataViewer` (which provides `bigquery.tables.get`
    and `bigquery.tables.list`) over `roles/bigquery.dataViewer` or
    `roles/bigquery.dataOwner` when data read access (`bigquery.tables.getData`)
    is not needed. (Note: `INFORMATION_SCHEMA.PARTITIONS` uniquely requires
    `bigquery.tables.getData`).

### Execution Guardrails & Query Invariants

*   **Mandatory Partition & Time Filtering:** Always filter on `creation_time`
    (e.g.,
    `creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)`) or
    `usage_date` to avoid full metadata table scans.
*   **Script Wrapper Exclusion:** Add
    `AND (statement_type != 'SCRIPT' OR statement_type IS NULL)` when
    aggregating compute spend to avoid double-counting parent scripts and child
    jobs.
*   **Column Pruning:** Never use `SELECT *` against `INFORMATION_SCHEMA`; only
    project required columns.
*   **Dry Run & Cost Estimation:** Use a dry run
    (`bq query --dry_run --use_legacy_sql=false "{query}"` or API `dryRun=true`)
    before executing complex queries, multi-view joins, or large scans to
    validate syntax and estimate `totalBytesProcessed` at zero cost.
*   **Empty Regional Scope (0 Rows):** If the execution location matches the
    qualifier, but the project has no datasets or jobs in that region, the query
    succeeds and returns **0 rows**. Never assume 0 rows means 0 usage—always
    verify the target dataset locations.
*   **Non-Hierarchical Region Scope:** Region qualifiers are not hierarchical.
    Multi-regions do not encompass single regions (e.g. `region-us` returns only
    multi-region `US` metadata and does not include single regions like
    `region-us-central1`).
*   **No Multi-Region Aggregation in SQL:** Region qualifiers cannot be joined
    cross-region in a single query (e.g. `region-us` cannot join `region-eu`).
*   **Uncached Execution & Minimum Scan Size:** `INFORMATION_SCHEMA` query
    results are never cached. On-demand queries incur a minimum of 10 MB of data
    processing charges per execution.

## Domain References & SQL Queries

### Telemetry Query Guides

*   **On-Demand Compute: Billed Bytes**
    (`references/compute_ondemand_billable.md`): Authoritative Golden CTE
    (`bytes_billed_cte`), timezone-aligned billing date extraction (PST8PDT),
    BQML CREATE_MODEL 50x multiplier rules, script wrapper deduplication, and
    row-level security (RLS) masking checks.
*   **Capacity Compute: Billable Slots & Commitments**
    (`references/compute_capacity_billable.md`): Query templates for auditing
    billable capacity hours across 1-Year/3-Year commitments, uncovered baseline
    PAYG slots, and dynamic autoscaling hours.
*   **Storage Footprints & Usage (Bytes Stored)**
    (`references/storage_footprints.md`): Storage snapshot queries, compression
    ratio calculations, Time Travel / Fail-Safe churn, daily average GiB
    time-integrals, billing model evaluation, and search index coverage.

### Performance & Troubleshooting Guides

*   **Job Performance Queries** (`references/job_performance_queries.md`):
    Queries for evaluating individual and aggregate job performance, stage
    bottleneck flags, comparable jobs via normalized literals
    (`query_info.query_hashes.normalized_literals`), BI Engine acceleration,
    metadata cache (cmeta) acceleration, search index usage, materialized view
    rewrite usage, and execution variance outliers.
*   **Resource Contention Queries**
    (`references/resource_contention_queries.md`): Queries for diagnosing slot
    contention, queue latency, per-minute concurrency/queue timelines, and
    1-second reservation slot saturation.
*   **Capacity & Configuration Queries**
    (`references/capacity_and_configuration_queries.md`): Queries for evaluating
    second-by-second baseline/max capacity ceilings, autoscaling saturation
    timelines, and auditing configuration changes
    (`RESERVATION_CHANGES_BY_PROJECT`, `ASSIGNMENT_CHANGES_BY_PROJECT`).

### Schema Dictionaries (Column Definitions & Units)

*   **Compute & Capacity Schema Dictionary** (`references/schema_compute.md`):
    Complete column dictionary, physical units, and least-privilege IAM roles
    for all compute, job, session, reservation, capacity commitment, and
    assignment views (`JOBS*`, `JOBS_TIMELINE*`, `SESSIONS_BY_USER`,
    `SESSIONS_BY_PROJECT`, `RESERVATIONS*`, `RESERVATION_CHANGES*`,
    `RESERVATIONS_TIMELINE*`, `CAPACITY_COMMITMENTS*`,
    `CAPACITY_COMMITMENT_CHANGES_BY_PROJECT`, `ASSIGNMENTS*`,
    `ASSIGNMENT_CHANGES_BY_PROJECT`).
*   **Storage & Data Catalog Schema Dictionary**
    (`references/schema_storage.md`): Complete column dictionary, physical
    units, and least-privilege IAM roles for all table storage, partition,
    column, snapshot, dataset, constraint, and replication views
    (`TABLE_STORAGE*`, `TABLE_STORAGE_USAGE_TIMELINE*`, `TABLES*`,
    `TABLE_OPTIONS`, `COLUMNS`, `COLUMN_FIELD_PATHS`, `PARTITIONS`, `VIEWS`,
    `MATERIALIZED_VIEWS`, `TABLE_SNAPSHOTS*`, `TABLE_CONSTRAINTS`,
    `KEY_COLUMN_USAGE`, `SCHEMATA*`, `SCHEMATA_OPTIONS`, `SCHEMATA_REPLICAS*`,
    `SCHEMATA_LINKS`, `SHARED_DATASET_USAGE`).
*   **Platform, Governance & Ingestion Schema Dictionary**
    (`references/schema_others.md`): Complete column dictionary, physical units,
    and least-privilege IAM roles for all remaining views including Access
    Control (`OBJECT_PRIVILEGES`, `ROW_ACCESS_POLICIES`,
    `ROW_ACCESS_POLICY_OPTIONS`), Streaming Ingestion
    (`STREAMING_TIMELINE_BY_PROJECT*`, `WRITE_API_TIMELINE_BY_PROJECT*`),
    Configuration Options (`PROJECT_OPTIONS*`, `EFFECTIVE_PROJECT_OPTIONS`,
    `ORGANIZATION_OPTIONS*`, `ORGANIZATION_OPTIONS_CHANGES`), Insights &
    Recommendations (`RECOMMENDATIONS*`, `INSIGHTS`), and Indexes/BI
    Engine/Routines (`SEARCH_INDEXES*`, `SEARCH_INDEX_COLUMNS`,
    `SEARCH_INDEX_OPTIONS`, `VECTOR_INDEXES*`, `VECTOR_INDEX_COLUMNS`,
    `VECTOR_INDEX_OPTIONS`, `BI_CAPACITIES`, `BI_CAPACITY_CHANGES`, `ROUTINES*`,
    `ROUTINE_OPTIONS`, `PARAMETERS`).
Pliki w paczce (10)
  • SKILL.md 12 KB
  • references/capacity_and_configuration_queries.md 7 KB
  • references/compute_capacity_billable.md 6 KB
  • references/compute_ondemand_billable.md 6 KB
  • references/job_performance_queries.md 11 KB
  • references/resource_contention_queries.md 9 KB
  • references/schema_compute.md 40 KB
  • references/schema_others.md 32 KB
  • references/schema_storage.md 34 KB
  • references/storage_footprints.md 10 KB

Wersje

RepozytoriumWynikLicencja
Ta strona · gemini-cli-extensions/data-agent-kit-starter-pack100Apache-2.0
google/skills100Apache-2.0

Migawka bazy z 13.11.2026 · wersja paczki 1b6fb93bc731 (skrót SHA-256 całej paczki)

Pobrania w ostatnich 30 dniach: 0 · ranking

Zgłoś problem z tym skillem: szkodliwa treść, błędny opis albo prośba autora o usunięcie.

Podobne skille

  • 100gcp-observability-patterns
    Bada w trybie tylko do odczytu awarie Composer, błędy zadań BigQuery, skoki kosztów i brakujące pliki w Google Cloud.
  • 90bigquery-sql
    Optymalizuje zapytania SQL w BigQuery pod kątem kosztu i wydajności, stosując sprawdzone zasady przetwarzania danych.
  • 100mcp-server-google-cloud
    Instaluje serwer MCP Google Cloud do Compute Engine, Cloud Storage i BigQuery oraz obsługuje jego narzędzia.
  • 100cloud-monitoring-metric-selection
    Wyszukuje i wskazuje właściwe deskryptory metryk Cloud Monitoring dla usług GCP, takich jak Compute Engine, Spanner czy BigQuery.
  • 100cloud-monitoring-metric-selection
    Wyszukuje i identyfikuje deskryptory metryk Cloud Monitoring dla usług i zasobów Google Cloud, takich jak Compute Engine, Spanner, BigQuery, Cloud Run i Pub/Sub.
  • 100cloud-monitoring-metric-selection
    Wyszukuje i identyfikuje deskryptory metryk Cloud Monitoring dla usług i zasobów Google Cloud, takich jak Compute Engine, Spanner, BigQuery, Cloud Run i Pub/Sub.

Uwagi i zgłoszenia

Wiadomość trafi do Bartosza Sokolińskiego. Adres e-mail jest potrzebny tylko do odpowiedzi. Szczegóły w polityce prywatności.