# O11Y OLAP datasets

Live registry: `GET {BASE}/olap/tables`. Always query **virtual aliases**.

## Scope discovery

```json
{"dataset":"log-stats","field":"date","scope":{}}
```

Walk ordered `scope_fields` one level at a time. Injected columns need static equality in SQL rewrite (filled from `scope`).

**Do not re-filter scope / `time_range` fields in user SQL.** The server injects
`WITH <alias> AS (SELECT * FROM physical WHERE <scope preds> AND <time preds>)`.
Repeating `org` / `cluster` / `date` / `component` / … (or the dataset time column) in
`WHERE` risks format bugs (e.g. slowlogs `date` is `yyyyMMdd` after normalize — a
hand-written `date = '2026-07-20'` returns 0 rows). Put only extra predicates in SQL
(`level`, `query_time`, digests, `LIMIT`, aggregations).

## Host vs x-region

| Logical (DNS / skill arg) | Physical `x-region` |
|---------------------------|---------------------|
| `us-west-2-staging` | `us-west-2` |
| `us-west-2-premium` | `us-west-2` |
| `us-east-1-serverless` | `us-east-1` |
| `us-west-2` | `us-west-2` |

## `time_range`

Check `datasets[].requires_time_range` from `/olap/tables`. Prefer:

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

Helper: `olap_day_time_range_json 2026-07-20`. Do not rely on `begin`/`end` alone against older servers.

## `logs`

| | |
|--|--|
| Virtual alias | `logs` |
| Scope | `env`, `deploy_type`, `date`, `components` (array) |
| Optional `time_range` | → `timestamp` |

## `log-stats`

| | |
|--|--|
| Virtual alias | `log_stats` |
| Scope | `date`, `org`, `cluster` |
| Optional `time_range` | → `hour` 0–23 UTC |
| Count column | `count` (string in Parquet) — use `CAST(count AS bigint)` |

## `topsql`

| | |
|--|--|
| Virtual alias | `topsql` |
| Scope | `org`, `cluster`, `component`, `instance`, `date` (`yyyy-MM-dd`) |
| Metrics | `topsql_cpu_time_ms`, `topsql_stmt_exec_count`, `topsql_stmt_duration_sum_ns`, `topsql_stmt_duration_count`, … |
| Grain | **Raw time-series rows** (per `timestamps` / digest), not the topsql v2 summary API |

### OLAP vs topsql v2 `/summary`

| | topsql v2 | OLAP `dataset=topsql` |
|--|-----------|------------------------|
| What you get | Pre-built summary (top N, time buckets, sql_text join) | Glue/Athena table mapping only |
| “CPU top 100” | `order_by=cpu&top=100` | **You write** `GROUP BY` + `SUM` + `ORDER BY` + `LIMIT` |
| `sql_text` / `plan_text` | Auto from `topsql_sql_meta` / `topsql_plan_meta` | Separate follow-up queries on `topsql-sql-meta` / `topsql-plan-meta` |
| Premium TiKV `keyspace` | Returned on summary items | Column may exist in files; not a first-class scoped field |

### Recipe: CPU top 100 (same intent as v2 `order_by=cpu`)

Do **not** `ORDER BY topsql_cpu_time_ms LIMIT 100` on raw rows — that ranks samples, not digests.

```sql
SELECT
  sql_digest,
  SUM(topsql_cpu_time_ms) AS cpu_ms,
  SUM(topsql_stmt_exec_count) AS exec_count,
  CASE
    WHEN SUM(topsql_stmt_duration_count) > 0
    THEN SUM(topsql_stmt_duration_sum_ns) * 1.0
         / SUM(topsql_stmt_duration_count) / 1e6
    ELSE 0
  END AS duration_per_exec_ms
FROM topsql
WHERE sql_digest IS NOT NULL
GROUP BY sql_digest
ORDER BY cpu_ms DESC
LIMIT 100
```

Optional plan breakdown: `GROUP BY sql_digest, plan_digest`.
Scope still requires one `component` + `instance` (+ `date`); walk `scope/list` first.
`time_range` (when required) filters `timestamps` in the CTE — keep it aligned with `date`.

### Follow-up: resolve `sql_text` / `plan_text`

After you have digests from topsql/topru, run **two separate** meta queries (same org/cluster; optional `date`):

```sql
-- dataset=topsql-sql-meta, FROM topsql_sql_meta
SELECT sql_digest, normalized_sql AS sql_text
FROM topsql_sql_meta
WHERE sql_digest IN ('DIGEST1', 'DIGEST2')

-- dataset=topsql-plan-meta, FROM topsql_plan_meta
SELECT plan_digest, normalized_plan AS plan_text
FROM topsql_plan_meta
WHERE plan_digest IN ('PLAN1', 'PLAN2')
```

## `topsql-sql-meta` / `topsql-plan-meta`

| | |
|--|--|
| Dataset ids | `topsql-sql-meta`, `topsql-plan-meta` |
| Virtual aliases | `topsql_sql_meta`, `topsql_plan_meta` |
| S3 path | `…/type=topsql/component=topsql_sql_meta` or `…/topsql_plan_meta` (no `instance`) |
| Required scope | `org`, `cluster` |
| Optional scope | `date` (`yyyy-MM-dd` file column) |
| Columns | `sql_digest`+`normalized_sql` / `plan_digest`+`normalized_plan` |

## `topru`

| | |
|--|--|
| Virtual alias | `topru` |
| S3 path | `…/type=topsql/component=topru/instance=default/date=` (see `o11ydiagnosis/pkg/topru`) |
| Required scope | `org`, `cluster`, `date` (`yyyy-MM-dd`) |
| Metrics | `topru_total_ru`, `topru_exec_count`, `topru_exec_duration` (ns), `user`, `keyspace` |
| Optional `time_range` | → `timestamps` |

### Recipe: RU top 100 (same intent as topru `/summary?order_by=ru`)

```sql
SELECT
  sql_digest,
  plan_digest,
  SUM(topru_total_ru) AS total_ru,
  SUM(topru_exec_count) AS exec_count,
  SUM(topru_exec_duration) AS duration_ns
FROM topru
WHERE COALESCE(sql_digest, '') <> ''
GROUP BY sql_digest, plan_digest
ORDER BY total_ru DESC
LIMIT 100
```

Optional user filter: `AND "user" = 'alice'` (or `AND ("user" IS NULL OR "user" = '')` for empty user).
Then resolve text via `topsql-sql-meta` / `topsql-plan-meta` as above.

## `metrics-status` (VM TSDB snapshots)

Collected daily by `metrics-status-collector` CronJob into `deltalake/global/metrics-status/`.
Writer uses **delete-by-date + append** (no Delta compact job); one snapshot per biztype/applicationID/date.
**Multiple VM account/project rows** may exist under the same `applicationID` + `date` partition.

| Dataset id | SQL `FROM` | S3 subdir | What you get |
|------------|------------|-----------|--------------|
| `metrics-status` | `metrics_status_sources` | `sources/` | Daily summary: `totalSeries`, `totalLabelValuePairs` |
| `metrics-status-by-metric` | `metrics_status_by_metric_name` | `series_count_by_metric_name/` | Series count **by metric name** |
| `metrics-status-by-label` | `metrics_status_by_label_name` | `series_count_by_label_name/` | Series count **by label name** |
| `metrics-status-by-label-value` | `metrics_status_by_label_value_pair` | `series_count_by_label_value_pair/` | Series count **by label=value pair** |

Required `scope` (all four datasets): `biztype`, `applicationID`, `date` (`yyyyMMdd` or `yyyy-MM-dd`).

Path template (S3; Glue projection placeholder is lowercase `${applicationid}`):

```text
deltalake/global/metrics-status/{subdir}/biztype={biztype}/applicationID={applicationID}/date={yyyyMMdd}/
```

File columns include `accountID` (**string**), `projectID`, `tenantID`, plus table-specific `name`/`value`.

### Scoped example — top metrics for one cluster/day

```bash
olap_query us-west-2-staging "$(jq -n '{
  "dataset":"metrics-status-by-metric",
  "scope":{"biztype":"staging-tidbcloud","applicationID":"k8s-infra","date":"2026-08-18"},
  "sql":"SELECT name, value FROM metrics_status_by_metric_name ORDER BY value DESC LIMIT 20"
}')"
```

### Regional trend (injected partitions)

You **cannot** `SELECT … GROUP BY date` region-wide in one raw query — `biztype` and `applicationID` are **injected** and need static equality.

```bash
# 1) list biztypes / applicationIDs
olap_scope_list us-west-2-staging '{"dataset":"metrics-status","field":"biztype","scope":{}}'
olap_scope_list us-west-2-staging '{"dataset":"metrics-status","field":"applicationID","scope":{"biztype":"staging-tidbcloud"}}'

# 2) per (biztype, applicationID) raw query, then sum client-side
# dataset=raw example (both injected keys required):
SELECT date, totalSeries, totalLabelValuePairs
FROM "o11y-logs".metrics_status_sources_us_west_2_staging
WHERE "biztype" = 'staging-tidbcloud'
  AND "applicationID" = 'k8s-infra'
  AND date >= '20260813' AND date <= '20260819'
ORDER BY date
```

### metrics-status pitfalls

| Mistake | Fix |
|---------|-----|
| `FROM s3://…` or `read_parquet('s3://…')` with `dataset=raw` | Use Glue name; raw ≠ direct S3 SQL |
| `projection.applicationID.type` in Glue | Use **`projection.applicationid.type`** (Athena lowercases partition keys) |
| Regional `GROUP BY date` without biztype/applicationID `=` | Injected partition error — query per cluster or UNION per biztype |
| Only `metrics-status` / `sources` | Use **`metrics-status-by-metric`** / **`by-label`** for breakdown |
| `accountID` Glue type wrong | Must be **`string`** (writer emits varchar). Legacy INT64 Parquet fails until partition rewrite |
| Expect one row per cluster/day | Sum all VM account/project rows in the partition |

## `sqlstatement`

| | |
|--|--|
| Virtual alias | `sqlstatement` |
| Scope | `org`, `cluster`, `date` (`yyyy-MM-dd`) |

## `slowlogs`

| | |
|--|--|
| Virtual alias | `slowlogs` |
| **S3 path** | `deltalake/{org}/{cluster}/slowlogs/date={yyyyMMdd}/` (bare tenant + o11y UUID; Hive `date=` partition) |
| Required scope | `org` (aliases `tenant`/`tenant_id`), `cluster` — **string** (applicationID → o11y UUID) or **string array** of o11y UUIDs / applicationIDs (`IN`), **`date`** → path partition **`yyyyMMdd`** |
| Date format | Scope accepts `yyyy-MM-dd` too — **server normalizes to `yyyyMMdd`**. Never put `yyyy-MM-dd` in user SQL for this column. |
| Optional `time_range` | → `time` (unix float). Use with `date` for finer filtering. |
| Tenant-wide pattern | `scope/list` `field=cluster` under `org` returns **o11y UUIDs** from S3; pass them as `"cluster": ["uuid1","uuid2",…]` on submit. List `field=date` under org+cluster to discover days. |
| Glue note | Partition projection: `org`/`cluster` injected, `date` integer(8). Template ends with `/slowlogs/date=${date}`. |

## Common mistakes

| Mistake | Fix |
|---------|-----|
| `FROM log-stats` | `FROM log_stats` |
| Logical region in `x-region` | Use physical (`olap_x_region`) |
| Missing `field` on scope/list | Required |
| `cpu_time_ms` on topsql | `topsql_cpu_time_ms` |
| `WHERE date/org/cluster/…` already in `scope` | Drop from SQL — CTE already filters (and normalizes formats) |
| slowlogs without `scope.date` | Required path partition — pass `yyyy-MM-dd` or `yyyyMMdd` |
| metrics-status raw without `"biztype"='…'` AND `"applicationID"='…'` | Injected partitions — per-cluster raw SQL or scoped dataset |
| metrics-status `applicationID` in Glue projection keys | Use lowercase **`applicationid`** in `projection.*` and `${applicationid}` in template |
| metrics-status detail tables `HIVE_BAD_DATA accountid` | Glue `accountID` must be **`string`**. Staging INT64 legacy files fail until collector rewrite + backfill |
| Queried only `metrics_status_sources` | Use **`metrics_status_by_metric_name`** / **`by_label_name`** for breakdown |
| `dataset=raw` with `s3://` path in SQL | Raw queries **Glue table names** only — not S3 paths |