---
name: o11y-olap
description: |
  Query TiDB Cloud O11Y offline OLAP data via the Diagnosis Query API (Athena):
  topsql, topsql-sql-meta, topsql-plan-meta, topru, slowlogs, sqlstatement,
  log-stats, logs, and metrics-status (VM TSDB snapshots).
  Authenticate with GitHub PAT, optionally list clusters to resolve region/org,
  then call the regional OLAP endpoints. Scope from region / deploy type / env
  down to a single cluster.
  Use when: OLAP SQL over logs, TopSQL, TopRU, slow logs, statement summary, log-stats;
    Athena/Glue offline queries; diagnosis-query API; "o11y olap";
    TopSQL/TopRU CPU/RU top-N via Athena (not topsql v2 / topru HTTP summary);
    resolve sql_text / plan_text via topsql-sql-meta / topsql-plan-meta.
  Trigger phrases: "olap query", "o11y olap", "topsql olap", "topsql top cpu",
  "topru olap", "topru top ru", "sql_text meta", "plan_text meta",
  "slowlogs athena", "log-stats dataset", "sqlstatement olap", "query logs via olap",
  "metrics-status olap", "vm tsdb series count".
---

# O11Y OLAP Query

Query offline OLAP datasets through the regional Diagnosis API. Prefer this skill
over raw S3/DuckDB for Athena-backed tables (`logs`, `log_stats`, `topsql`,
`sqlstatement`, `slowlogs`).

Full contract: [`docs/log-query-api-user-guide.md`](../../docs/log-query-api-user-guide.md)
(and `.zh-CN.md`). Dataset details: [`references/datasets.md`](references/datasets.md).

## Prerequisites

| Need | Notes |
|------|--------|
| **bash** | Scripts must be sourced with **bash**, not zsh |
| `curl`, `jq` | Required |
| `GITHUB_PAT` | **Always** — ask the user if missing (never invent one). Also searched in `.env` / `~/code/test/observability-client/.env` |
| Logical region | DNS host segment, e.g. `us-west-2-staging` |
| Vendor | Default `aws` |

If `GITHUB_PAT` is missing after env lookup, **stop and ask the user**.

## Workflow (must follow)

```
1. Auth (GitHub PAT)     →  O11Y_TOKEN (.token) + O11Y_AUTH0_TOKEN (.auth0_token)
2. Resolve logical region →  user provides OR list-clusters
3. Split host vs x-region →  Base URL uses logical; header x-region uses physical
4. Discover scope         →  POST /olap/scope/list (dataset + field + ancestors)
5. OLAP submit/poll       →  POST /olap → GET status → results / download
```

### Step 1 — Authenticate (bash only)

```bash
bash <<'EOF'
source skills/o11y-olap/scripts/o11y-olap.sh
olap_auth
# Exports: O11Y_TOKEN, O11Y_AUTH0_TOKEN, AWS_*
EOF
```

- Auth: `POST https://www.ds.us-west-2.aws.observability.tidbcloud.com/client/auth`
- Body: `{"github_token":"<GITHUB_PAT>"}`
- OLAP calls: send **`o11ytoken: ${O11Y_TOKEN}`** (and optionally `Authorization: Bearer`). Do not rely on Bearer alone — some gateways return `missing o11ytoken`.
- GS list clusters: `Authorization: Bearer ${O11Y_AUTH0_TOKEN}`

### Step 2 — Resolve region (when needed)

Skip if the user already gave a **logical** region (`us-west-2`, `us-west-2-staging`,
`us-west-2-premium`, `us-east-1-serverless`, …).

Otherwise: `olap_find_cluster <applicationID> [vendor] [hint_region]`.

| Field | Use |
|-------|-----|
| `logical_region` / DNS | **Base URL** host segment |
| Physical region | **`x-region` header** (see Step 3) |
| `id` / `tenantID` | `scope.cluster` / `scope.org` when required. For `slowlogs`: single applicationID (server → o11y UUID), or UUID array from `scope/list` |
| bizType | Map to `dedicated` / `premium` / `shared` |

### Step 3 — Base URL vs `x-region` (critical)

| Concept | Example (staging) | Used for |
|---------|-------------------|----------|
| **Logical region** | `us-west-2-staging` | Hostname + Glue table suffix via bucket |
| **Physical `x-region`** | `us-west-2` | Request header; must match diagnosis-query helm `region:` |

```text
Base = https://www.ds.<LOGICAL>.<vendor>.observability.tidbcloud.com/internal/o11y/diagnosis/api/v1
Header x-region = <PHYSICAL>   # NOT the same as LOGICAL when suffix exists
```

Script helpers:

```bash
olap_base_url us-west-2-staging aws
# → https://www.ds.us-west-2-staging.aws.observability.tidbcloud.com/internal/o11y/diagnosis/api/v1

olap_x_region us-west-2-staging
# → us-west-2
```

Strip rules for `olap_x_region`: `-staging`, `-premium`, `-serverless`, `-ng`, `-na-test`, `-byoc-*`.

| Product | Logical (host) | Typical `x-region` |
|---------|----------------|--------------------|
| Dedicated | `us-west-2` | `us-west-2` |
| Staging | `us-west-2-staging` | `us-west-2` |
| Premium | `us-west-2-premium` | `us-west-2` |
| Serverless | `us-east-1-serverless` | `us-east-1` |

Error `x-region … does not match deployment region …` → you passed logical in the header; use `olap_x_region`.

### Step 4 — Discover scope, then query

Injected partitions (`org`, `cluster`, `component`, …) **require** static equality
(or an **IN list** of known values — see slowlogs tenant-wide below).
Walk `POST /olap/scope/list` one level at a time. Body **must** include `dataset` and `field`:

```bash
olap_scope_list us-west-2-staging '{"dataset":"log-stats","field":"date","scope":{}}'
olap_scope_list us-west-2-staging '{"dataset":"log-stats","field":"org","scope":{"date":"20260724"}}'
olap_scope_list us-west-2-staging '{"dataset":"log-stats","field":"cluster","scope":{"date":"20260724","org":"infra-prod"}}'
```

#### Slowlogs: one cluster vs whole tenant

- **One cluster:** `"cluster": "<applicationID>"` — server maps to o11y UUID (same as slowquery). Bare UUID also accepted.
- **All clusters under a tenant:** list S3 children (returns **o11y UUIDs**), then submit with an array:

```bash
ORG=1372813089188151296
CLUSTERS=$(olap_scope_list us-west-2-staging "$(jq -n --arg o "$ORG" \
  '{dataset:"slowlogs",field:"cluster",scope:{org:$o}}')" | jq -c '.data.values')
# CLUSTERS is like ["019b23f6-…","34750647-…"]
BODY=$(jq -n --arg o "$ORG" --argjson c "$CLUSTERS" \
  --argjson tr "$(olap_day_time_range_json 2026-07-20)" '{
  dataset:"slowlogs",
  scope:{org:$o, cluster:$c},
  time_range:$tr,
  sql:"SELECT cluster, COUNT(*) AS n FROM slowlogs GROUP BY cluster ORDER BY n DESC LIMIT 50"
}')
olap_query us-west-2-staging "$BODY"
```

Do **not** use applicationIDs from GS as the array unless you want MetaCache to resolve each; prefer UUIDs from `scope/list` for tenant-wide queries.

Then submit with full `scope` + SQL against the **virtual alias**.

```bash
BODY=$(jq -n '{
  dataset:"log-stats",
  scope:{date:"20260724", org:"infra-prod", cluster:"k8s-infra"},
  sql:"SELECT component, level, CAST(count AS bigint) AS cnt FROM log_stats ORDER BY cnt DESC LIMIT 10"
}')
olap_query us-west-2-staging "$BODY" aws all "" ""
```

Required headers (script sets these):

```bash
-H "o11ytoken: ${O11Y_TOKEN}"
-H "Authorization: Bearer ${O11Y_TOKEN}"
-H "x-provider: aws"
-H "x-region: $(olap_x_region <logical>)"   # physical
-H "x-cluster-id: all|CLUSTER"
```

## Dataset cheat sheet

| `dataset` | SQL `FROM` | Required `scope` (ordered) | Date in `scope` | `time_range` |
|-----------|------------|----------------------------|-----------------|--------------|
| `logs` | `logs` | `env`, `deploy_type`, `date`, `components` | `yyyy-MM-dd` | often **required** on older builds → `timestamp` |
| `log-stats` | `log_stats` | `date`, `org`, `cluster` | `yyyyMMdd` ok | usually optional |
| `topsql` | `topsql` | `org`, `cluster`, `component`, `instance`, `date` | **`yyyy-MM-dd`** | often **required** → `timestamps` |
| `topsql-sql-meta` | `topsql_sql_meta` | `org`, `cluster` (+ optional `date`) | optional file col | no |
| `topsql-plan-meta` | `topsql_plan_meta` | `org`, `cluster` (+ optional `date`) | optional file col | no |
| `topru` | `topru` | `org`, `cluster`, `date` | **`yyyy-MM-dd`** | often → `timestamps` |
| `sqlstatement` | `sqlstatement` | `org`, `cluster`, `date` | **`yyyy-MM-dd`** (normalize if S3 returns `yyyyMMdd`) | usually optional |
| `slowlogs` | `slowlogs` | `org`, `cluster` (string or UUID/`applicationID` array), **`date`** | **`yyyyMMdd`** path partition (scope accepts `yyyy-MM-dd`) | often **required** → `time` |
| `metrics-status` | `metrics_status_sources` | `biztype`, `applicationID`, `date` | **`yyyyMMdd`** | optional |
| `metrics-status-by-metric` | `metrics_status_by_metric_name` | same as metrics-status | **`yyyyMMdd`** | optional |
| `metrics-status-by-label` | `metrics_status_by_label_name` | same as metrics-status | **`yyyyMMdd`** | optional |
| `metrics-status-by-label-value` | `metrics_status_by_label_value_pair` | same as metrics-status | **`yyyyMMdd`** | optional |
| `raw` | physical Glue name | forbidden | — | forbidden |

**Always check live metadata:** `olap_tables` → `datasets[].requires_time_range`. When `true`, include:

```json
"time_range": { "begin_time": 1752969600, "end_time": 1753056000 }
```

Prefer **`begin_time` / `end_time`** (not only `begin` / `end`) — older diagnosis-query builds ignore `begin`/`end` and error with `require begin_time < end_time`. Helper: `olap_day_time_range_json 2026-07-20`.

- Never `FROM log-stats` — use `log_stats`.
- **Do not duplicate `scope` / `time_range` keys in SQL `WHERE`.** Those predicates are already injected into the CTE (with server-side date/time normalization). User SQL should only add *extra* filters (`level`, digests, `query_time`, …), aggregates, and `LIMIT`.
- TopSQL column is `topsql_cpu_time_ms` (not `cpu_time_ms`). TopRU uses `topru_total_ru`.
- For “CPU / RU top N”: **aggregate** (`SUM` + `GROUP BY` digest + `ORDER BY` + `LIMIT`). Never rank raw sample rows.
- Resolve `sql_text` / `plan_text` with separate `topsql-sql-meta` / `topsql-plan-meta` queries (do not expect them on metrics rows).
- Prefer `LIMIT`; use `output_mode: "export"` for large results.

## Agent behavior

1. Use **bash** for all script sourcing.
2. Ask for `GITHUB_PAT` if unset after env lookup.
3. Always set **logical host** + **physical x-region** correctly (script does this if you pass logical to helpers).
4. Always send **`o11ytoken`**.
5. Use `/olap/scope/list` with **`field`** before guessing org/cluster/component/instance.
   For tenant-wide `slowlogs`, list `field=cluster` under `org` and pass `.data.values` as a `cluster` array.
6. Read `requires_time_range` from `/olap/tables`; when true (or unsure), attach `begin_time`/`end_time` via `olap_day_time_range_json`.
7. Date formats differ by dataset:
   - `logs` / `topsql` / `sqlstatement` / `topru`: `scope.date` is **`yyyy-MM-dd`** (use `olap_normalize_date` if S3 returns `yyyyMMdd`).
   - `log-stats` / **`slowlogs`**: partition date is **`yyyyMMdd`**. You may pass `yyyy-MM-dd` in `scope.date` — the **server converts** to `yyyyMMdd`. Do **not** put `yyyy-MM-dd` literals in user SQL for these datasets. **`slowlogs` requires `scope.date`** (S3 path `…/slowlogs/date=yyyyMMdd/`).
8. **Never restate scope/`time_range` columns in generated SQL** (`org`, `cluster`, `date`, `component`, `instance`, `env`, `deploy_type`, or the dataset time column). Rely on the CTE rewrite; duplicate filters often use the wrong literal format and return empty rows while still scanning bytes.
9. Keep scopes tight; report `scanned_bytes` / `error_message` / `effective_sql` on failure.
10. Never print full JWTs or PATs.

## Pitfalls (from real runs)

| Symptom | Fix |
|---------|-----|
| `missing o11ytoken` | Add `o11ytoken` header (script `_olap_curl` does this) |
| `x-region us-west-2-staging does not match … us-west-2` | Pass physical region via `olap_x_region` |
| `dataset and field are required` | Include `"field":"date"` (etc.) in scope/list body |
| `time_range is required for dataset=…` | Check `requires_time_range`; add full-day `begin_time`/`end_time` |
| `require begin_time < end_time` with non-empty bounds | Use `begin_time`/`end_time` keys (not only `begin`/`end`) |
| `scope.date: invalid date "20260706"` | For logs/topsql/sqlstatement/topru use `olap_normalize_date` → `2026-07-06`. For **slowlogs/log-stats**, `yyyyMMdd` is correct (or pass `yyyy-MM-dd` in scope and let the server convert). |
| Athena `injected projected partition …` | Fill every injected scope key via scope/list |
| Empty / wrong table | `olap_tables <logical>`; confirm `data_status: AVAILABLE` |
| slowlogs: scanned_bytes &gt; 0 but 0 rows | Ensure `scope.date` matches S3 `date=yyyyMMdd` (pass `yyyy-MM-dd` in scope — server converts). Do not re-filter `date` in SQL with `yyyy-MM-dd`. List `field=date` under org+cluster to see available days. |
| Sourced under zsh | `bash -c 'source …'` |
| Smoke regression | `bash skills/o11y-olap/scripts/smoke-test.sh us-west-2-staging` |
| `invalid token` from cached `.env` | Re-auth: force refresh `O11Y_TOKEN` via `/client/auth` (cached token may expire before `O11Y_TOKEN_EXPIRES`) |
| `dataset=raw` + `FROM s3://…` or `read_parquet(...)` | **Not supported.** Raw = SQL passthrough to **Glue table names** only (e.g. `"o11y-logs".metrics_status_sources_us_west_2_staging`) |
| metrics-status `INVALID_TABLE_PROPERTY … applicationid` | Glue projection keys must be lowercase: `projection.applicationid.type`, template `applicationID=${applicationid}` (Hive path segment stays camelCase) |
| metrics-status `CONSTRAINT_VIOLATION … injected … biztype/applicationid` | Injected partitions require **static equality** per key. Regional `GROUP BY date` without `"biztype"='…'` AND `"applicationID"='…'` fails. Use `scope/list` → per-cluster queries → client-side rollup, or `UNION ALL` one subquery per biztype with `"biztype"='…'` |
| metrics-status `HIVE_BAD_DATA … accountid … INT64 … varchar` | Glue `accountID` is **`string`**. Legacy INT64 Parquet (e.g. us-west-2-staging before collector rewrite) fails until partition is rewritten |
| Only queried `metrics-status` / `sources` | Summary only (`totalSeries`). For metric/label breakdown use **`metrics-status-by-metric`**, **`metrics-status-by-label`**, **`metrics-status-by-label-value`** |
| metrics-status multiple rows per day/cluster | One row per **VM account/project** snapshot under the same `applicationID` partition — sum when aggregating |
| metrics-status partition cols in Parquet | `biztype`, `applicationID`, `date` live in **Hive path only**, not file columns |

## Minimal examples

**log-stats (staging):** see Step 4.

**TopSQL (CPU top 100 — aggregate, not raw row ORDER BY):**

```bash
# Discover: field=org → cluster → component → instance → date
TR=$(olap_day_time_range_json 2026-05-28)
BODY=$(jq -n --argjson tr "$TR" '{
  dataset:"topsql",
  scope:{org:"ORG", cluster:"CLUSTER", component:"tidb",
    instance:"db-tidb-0.db-tidb-peer.…:10080", date:"2026-05-28"},
  time_range: $tr,
  sql:"SELECT sql_digest, SUM(topsql_cpu_time_ms) AS cpu_ms, SUM(topsql_stmt_exec_count) AS exec_count FROM topsql WHERE sql_digest IS NOT NULL GROUP BY sql_digest ORDER BY cpu_ms DESC LIMIT 100"
}')
olap_query us-west-2-staging "$BODY"
```

OLAP does **not** call topsql v2 `/summary`. It only scopes the metrics table; write the aggregation yourself (see `references/datasets.md`).

**Resolve sql_text / plan_text (separate queries):**

```bash
BODY=$(jq -n '{
  dataset:"topsql-sql-meta",
  scope:{org:"ORG", cluster:"CLUSTER", date:"2026-05-28"},
  sql:"SELECT sql_digest, normalized_sql AS sql_text FROM topsql_sql_meta WHERE sql_digest IN ('\''DIGEST1'\'','\''DIGEST2'\'')"
}')
olap_query us-west-2-staging "$BODY"

BODY=$(jq -n '{
  dataset:"topsql-plan-meta",
  scope:{org:"ORG", cluster:"CLUSTER", date:"2026-05-28"},
  sql:"SELECT plan_digest, normalized_plan AS plan_text FROM topsql_plan_meta WHERE plan_digest IN ('\''PLAN1'\'')"
}')
olap_query us-west-2-staging "$BODY"
```

**TopRU (RU top 100):**

```bash
TR=$(olap_day_time_range_json 2026-05-28)
BODY=$(jq -n --argjson tr "$TR" '{
  dataset:"topru",
  scope:{org:"ORG", cluster:"CLUSTER", date:"2026-05-28"},
  time_range: $tr,
  sql:"SELECT sql_digest, plan_digest, SUM(topru_total_ru) AS total_ru, SUM(topru_exec_count) AS exec_count FROM topru WHERE COALESCE(sql_digest,'\'\'') <> '\'\'' GROUP BY sql_digest, plan_digest ORDER BY total_ru DESC LIMIT 100"
}')
olap_query us-west-2-staging "$BODY"
```

More: [`references/examples.md`](references/examples.md), [`references/datasets.md`](references/datasets.md).