# Offline Log SQL Query API — User Guide

> 中文版：[log-query-api-user-guide.zh-CN.md](./log-query-api-user-guide.zh-CN.md)

## Background

TiDB Cloud cluster logs in O11Y are used in two main ways:

| Scenario | Common Approach | When to Use |
|----------|-----------------|-------------|
| **Near-real-time troubleshooting** | Console / Loki search | Recent events, tailing, interactive paging |
| **Historical detail analysis** | Offline log download | Retrieving raw logs for a specific day or hour |

Historically, historical detail was provided as **hourly merged plain-text files** (merged-logs): download the **entire file** via client or console, then filter locally with `grep`, `awk`, etc. log_stats statistics power **aggregate analysis** in Clinic and similar diagnosis products; they do not return full log lines.

The new **OLAP SQL API** (`POST /api/v1/olap`) upgrades the second scenario: historical logs are stored as **structured Parquet**, queryable via a unified HTTP interface with SQL filters on time, component, level, keywords, and more — without downloading a large file first. The same endpoint is the **generic** Athena/OLAP SQL entry for regional diagnosis-query (not logs-only); other cloud engines will share this interface later.

## Pain Points (Why This API)

1. **Whole-file download only, no on-demand query**  
   Legacy merged-logs are plain text concatenated per instance per hour. To find "ERROR from tidb for a cluster in a given hour", users often had to pull the full `.log` and grep locally — files easily reach hundreds of MB to several GB, wasting time and bandwidth.

2. **No structured filtering or aggregation**  
   Text files have no column concept; online operations like "count ERRORs per component", "search message by keyword", or "select only event_time + message" are hard without custom scripts.

3. **Unclear boundary with near-real-time**  
   Near-real-time log search should use **Loki**; historical hourly detail should use the **offline layer**. The old pipeline lacked a unified SQL entry point — users chose between "download a huge file" and "query recent logs in Loki", with poor experience for in-between historical analysis.

4. **Partition dive paths differ per table**  
   Each OLAP table has its own Hive-style path (e.g. logs: `env` / `deployType` / `date` / `component=`; log_stats: `date` / `org` / `cluster`). Manually building paths risks missing **injected** partitions or scanning too much data. The API locks the dive path via a **unified `scope`** (plus optional `time_range` for known time columns).

## What This API Does

- Run **SQL** on offline OLAP tables (filter, sort, aggregate, export).  
- Pick a **`dataset`**, pass **`scope`** (dive-path partitions — same shape for every table), and query the dataset’s **virtual table** (do not write partition predicates yourself).  
- **`time_range`** is **optional**: when the dataset declares `time_column` / `time_format`, the API converts unix or datetime bounds into predicates; omit it and filter time in SQL.  
- Data is isolated by **region**: call the endpoint in the cluster's region; **cross-region queries are not supported**.  
- Offline Parquet / Delta data only — **not** Loki near-real-time; only ranges after ETL completion are queryable.

---

This guide explains how to call the **Regional Diagnosis API** for the above queries and exports.

**Call flow overview:**

```
Authenticate (GitHub PAT) → Submit query (dataset + scope [+ time_range] + sql against virtual table, or raw sql)
→ Default wait up to 30s → inline JSON result or export presigned URL
→ (Optional) Poll status / GET results / GET download for async or later refresh
```

---

## 1. Quick Start

Concrete API details start at **§2 Authentication**; common query examples are in **§7**.

---

## 2. Authentication

Exchange a GitHub PAT for API credentials first.

```http
POST https://www.ds.us-west-2.aws.observability.tidbcloud.com/client/auth
Content-Type: application/json

{"github_token": "<YOUR_GITHUB_PAT>"}
```

**Response example:**

```json
{
  "token": "eyJhbGciOiJIUzI1NiIs...",
  "access_key_id": "ASIA...",
  "secret_access_key": "...",
  "session_token": "..."
}
```

| Field | Purpose |
|-------|---------|
| `token` | Call Diagnosis API: `Authorization: Bearer <token>` |
| `access_key_id` / `secret_access_key` / `session_token` | Download export files from object storage after large-result export (~12h validity) |

```bash
export O11Y_TOKEN="$(jq -r '.token' auth.json)"
export AWS_ACCESS_KEY_ID="$(jq -r '.access_key_id' auth.json)"
export AWS_SECRET_ACCESS_KEY="$(jq -r '.secret_access_key' auth.json)"
export AWS_SESSION_TOKEN="$(jq -r '.session_token' auth.json)"
```

---

## 3. API Base URL and Headers

**Base URL (example, `us-west-2` Premium):**

```
https://www.ds.us-west-2-premium.aws.observability.tidbcloud.com/internal/o11y/diagnosis/api/v1
```

Template: `https://www.ds.<region>[-<suffix>].<vendor>.observability.tidbcloud.com/internal/o11y/diagnosis/api/v1`

| Header | Required | Description |
|--------|----------|-------------|
| `Authorization` | Yes* | `Bearer <token>` from `/client/auth` |
| `o11ytoken` / `O11ytoken` / `X-O11y-Token` | Yes* | Alternative to `Authorization`; same JWT value |
| `Content-Type` | For POST | `application/json` |
| `x-provider` | Yes | Cloud vendor; Phase 0: `aws` |
| `x-region` | Yes | Must match Base URL region |
| `x-cluster-id` | Yes | Job ownership check (bound to submitted job). Use `all` for org-wide raw tables. **Not** auto-injected into SQL — filter `cluster` in `FROM logs WHERE ...` yourself |
| `x-org-id` | Depends on type | Tenant ID for Dedicated / Premium / Serverless |
| `x-deploy-type` | Recommended | `dedicated` / `premium` / `shared` |

\* Provide **either** `Authorization: Bearer <token>` **or** an `o11ytoken` header.

---

## 4. Datasets & Tables

Offline OLAP data is registered in Athena / Glue (database **`o11y-logs`** for log-related tables). Call **`GET /olap/tables`** for the live registry in this region (`catalog_status` / `data_status`).

Physical table names use the **logical region** (from `BUCKET_NAME`: `o11y-{env}-shared-{logical_region}`), e.g. `general_logs_us_west_2`, `log_stats_us_east_1_serverless`. With a scoped `dataset`, query the **virtual alias** in SQL — do not hard-code the physical name.

### 4.0 Catalog

| Dataset id | Virtual alias (`FROM …`) | Status | Dive path (partitions → `scope`) | Optional `time_range` → |
|------------|--------------------------|--------|----------------------------------|-------------------------|
| `logs` | `logs` | **Available** | `env` / `deployType` / `date` / `component` | `timestamp` (unix seconds) |
| `log-stats` | `log_stats` | **Available** | `date` / `org` / `cluster` | `hour` (0–23 UTC; `scope.date` locks the day) |
| `topsql` | `topsql` | **Available** | `org` / `cluster` / `component` / `instance` / `date` (under fixed `type=topsql`) | `timestamps` (unix seconds) |
| `sqlstatement` | `sqlstatement` | **Available** | `org` / `cluster` / `date` (under fixed `type=sqlstatement/component=sqlstatement`) | overlapping `begin`/`end` |
| `slowlogs` | `slowlogs` | **Available** | `org` / `cluster` / `date` (`deltalake/{org}/{cluster}/slowlogs/date=yyyyMMdd/`) | `time` (unix seconds) |
| `raw` (omit) | — | **Available** | You write all filters in SQL | Forbidden |

`scope` keys always follow that table’s dive path, and **`scope_fields` are returned in Hive path order** (parent → child). Fixed path segments (`type=…`) are not scope fields — they are baked into the Glue location template and into `POST /olap/scope/list` prefixes. Use **`POST /olap/scope/list`** to discover real partition values level by level (see §5.6).

### 4.1 `logs` — general offline logs

```
o11y-logs.general_logs_<logical_region>
```

| Part | Description | Example |
|------|-------------|---------|
| Database | Always `o11y-logs` | `o11y-logs` |
| Physical table | `general_logs` + `_` + logical region (`-` → `_`) + optional suffix | `general_logs_us_east_1_serverless` |
| Virtual alias | `dataset: "logs"` → query **`logs`** | `FROM logs` |

| Bucket | Logical region | Physical table |
|--------|----------------|----------------|
| `o11y-prod-shared-us-west-2` | `us-west-2` | `o11y-logs.general_logs_us_west_2` |
| `o11y-prod-shared-us-east-1-serverless` | `us-east-1-serverless` | `o11y-logs.general_logs_us_east_1_serverless` |

**Path:**

```
.../deltalake/global/logs/env={env}/deployType={deployType}/date={date}/component={component}/*.parquet
```

**Required `scope`:** `env`, `deploy_type`, `date`, `components`  
**Optional `time_range`:** → file column `timestamp` (unix seconds). Omit and filter in SQL if you prefer.

#### File columns

| Column | Type | Description |
|--------|------|-------------|
| `event_time` | string | Log time (ISO 8601) |
| `timestamp` | bigint | Unix seconds |
| `host` / `pod` | string | Host / pod |
| `level` | string | `ERROR` / `WARN` / `INFO` / `DEBUG` |
| `position` | string | Source location |
| `message` | string | Log body |
| `cluster` / `instance` | string | Cluster id / instance name |
| `tenant_id` | string | Tenant / org (**file column**, not a path partition) |

#### Partition columns

| Column | Glue name | `scope` key | Projection |
|--------|-----------|-------------|------------|
| env | `env` | `env` | enum |
| deploy type | `deploytype` | `deploy_type` | enum |
| date | `date` | `date` (`yyyy-MM-dd`) | date |
| component | `component` | `components` (array) | **injected** |

### 4.2 `log-stats` — offline log statistics

Clinic-style aggregates (counts + sample lines), not full log detail.

```
o11y-logs.log_stats_<logical_region>
```

| Part | Description | Example |
|------|-------------|---------|
| Virtual alias | `dataset: "log-stats"` → query **`log_stats`** | `FROM log_stats` |
| Physical table | `log_stats` + `_` + logical region | `log_stats_us_west_2` |

**Path:**

```
.../deltalake/global/log-stats/date={yyyyMMdd}/org={org}/cluster={cluster}/
```

**Required `scope`:** `date`, `org`, `cluster` (`org` / `cluster` are **injected**)  
**Optional `time_range`:** → file column `hour` (0–23 UTC). Must stay within one UTC day matching `scope.date`.  
**Date format:** `yyyyMMdd` on path; API also accepts `yyyy-MM-dd` and normalizes.

#### File columns

| Column | Type | Description |
|--------|------|-------------|
| `hour` | bigint | UTC hour of day (0–23) |
| `project` | string | Project id |
| `level` | string | Log level |
| `file` / `line` | string | Parsed from position |
| `count` | string | Occurrence count (string in Parquet) |
| `component` | string | Component |
| `msg_digest` | string | Message digest / pattern |
| `sample` | string | Sample log line |

#### Partition columns

| Column | `scope` key | Projection |
|--------|-------------|------------|
| `date` | `date` | integer `yyyyMMdd` |
| `org` | `org` | **injected** |
| `cluster` | `cluster` | **injected** |

### 4.3 `topsql` — TopSQL instance metrics

```
o11y-logs.topsql_<logical_region>
```

**Path:**

```
.../deltalake/org={org}/cluster={cluster}/type=topsql/component={component}/instance={instance}/date={yyyy-MM-dd}/
```

**Required `scope`:** `org`, `cluster`, `component`, `instance`, `date`  
**Optional `time_range`:** → file column `timestamps` (unix seconds)  
**Notes:** Prefer separate scoped datasets for meta: `topsql-sql-meta` / `topsql-plan-meta` (virtual `topsql_sql_meta` / `topsql_plan_meta`). TopRU is `dataset=topru` (`component=topru/instance=default`).

#### Partition columns

| Column | `scope` key | Projection |
|--------|-------------|------------|
| `org` | `org` | **injected** |
| `cluster` | `cluster` | **injected** |
| `component` | `component` | **injected** |
| `instance` | `instance` | **injected** |
| `date` | `date` (`yyyy-MM-dd`) | date |

### 4.4 `sqlstatement` — SQL statement summaries

```
o11y-logs.sqlstatement_<logical_region>
```

**Path:**

```
.../deltalake/org={org}/cluster={cluster}/type=sqlstatement/component=sqlstatement/date={yyyy-MM-dd}/
```

**Required `scope`:** `org`, `cluster`, `date`  
**Optional `time_range`:** → overlapping `"begin"` / `"end"` windows (or filter those columns in SQL)  
**Notes:** Legacy org-level Glue tables (`sqlstatement_org_*_all`) still work via `dataset=raw`.

### 4.5 `slowlogs` — slow query logs (legacy Delta)

```
o11y-logs.slowlogs_<logical_region>
```

**Path:**

```
.../deltalake/{org}/{cluster}/slowlogs/date={yyyyMMdd}/
```

Example: `s3://o11y-prod-shared-us-east-1/deltalake/1372813089188151296/019b23f6-ffc9-731d-be49-eb8f9e651855/slowlogs/date=20260727/`

| Scope field | Required | Meaning |
|-------------|----------|---------|
| `org` | Yes | Tenant id (bare path segment; aliases `tenant` / `tenant_id`) |
| `cluster` | Yes | TiDB Cloud **cluster id** (applicationID) or o11y UUID; **string or string array** (`IN`). Server maps applicationIDs → o11y UUIDs via MetaCache. For tenant-wide queries: `scope/list` `field=cluster` returns UUIDs — pass that array on submit. |
| `date` | Yes | Hive path partition `date=` as `yyyyMMdd` (or `yyyy-MM-dd`, normalized). List with `scope/list` `field=date`. |

**Optional `time_range`:** → file column `time` (unix seconds, float)

**Notes:** Glue partition projection: `org`/`cluster` injected, `date` integer(8 digits). Template: `…/deltalake/${org}/${cluster}/slowlogs/date=${date}`.

---

## 5. API Design: dataset + scope + time_range + sql

### 5.1 Why Split Them?

OLAP tables use **different dive paths**. Athena **injected** partitions (e.g. `component`, `org`, `cluster`) fail unless the effective SQL contains a static `=` / `IN`. A single global “wrap user SQL” rewrite does not scale.

Therefore:

| Part | Role |
|------|------|
| **`dataset`** | Selects which table profile (virtual alias + partition map + optional time column) |
| **`scope`** | Locks that table’s **dive-path partitions** (unified value forms — see §5.2) |
| **`time_range`** | **Optional** convenience: convert unix/datetime bounds onto the dataset’s known time column |
| **`sql`** | Full Athena `SELECT` against the **virtual alias** only |

The server injects **`WITH <alias> AS (SELECT * FROM <physical> WHERE <scope[/time]>)`** and does **not** rewrite the rest of your SQL.

### 5.2 Unified `scope` value forms

Every scoped dataset uses the same JSON shapes for each scope field (keys come from that table’s dive path / `scope_fields` in `/olap/tables`):

| Form | Example | SQL |
|------|---------|-----|
| String (equality) | `"deploy_type": "dedicated"` | `"deploytype" = 'dedicated'` |
| String array (`IN`) | `"components": ["tidb", "tikv"]` | `"component" IN ('tidb', 'tikv')` |
| Range object | `"date": {"gte":"2026-07-17","lt":"2026-07-20"}` | `"date" >= '…' AND "date" < '…'` |

Range bounds: `gte` / `gt` / `lte` / `lt` (at least one required).  
Legacy for `logs`: `begin_date` + `end_date` still work as inclusive `date` range (`gte` + `lte`).

`scope` keys are **logical** API names (`deploy_type`, `components`); the server maps them to Glue column names (`deploytype`, `component`).

### 5.3 User SQL rules (all scoped datasets)

1. Must be **`SELECT`** (may include `WITH`; Athena functions allowed).  
2. Query the dataset’s **virtual alias** (`FROM logs` / `FROM log_stats`). Do **not** use the physical Glue name in user SQL.  
3. **Do not** repeat dive-path / `time_range` predicates in SQL — pass them via `scope` / `time_range`.  
4. Injected partitions must be present in `scope` as equality or non-empty arrays (`["*"]` / empty is rejected until auto-discovery exists).  
5. Extra filters (`level`, `instance`, file columns, …) belong in user SQL `WHERE`.  
6. Prefer `LIMIT`; use `output_mode: "export"` for large results.

### 5.4 Merge logic examples

**`dataset=logs`:**

```sql
WITH logs AS (
  SELECT * FROM "o11y-logs".general_logs_us_west_2
  WHERE "env" = :scope.env
    AND "deploytype" = :scope.deploy_type
    AND "date" = :scope.date          -- or range bounds
    AND "component" IN (:scope.components)
    AND "timestamp" >= :begin_time
    AND "timestamp" <  :end_time
)
SELECT event_time, instance, level, message
FROM logs
WHERE cluster = 'my-cluster-id' AND level = 'ERROR'
ORDER BY event_time DESC
LIMIT 500
```

**`dataset=log-stats`:**

```sql
WITH log_stats AS (
  SELECT * FROM "o11y-logs".log_stats_us_west_2
  WHERE "date" = :scope.date          -- normalized yyyyMMdd
    AND "org" = :scope.org
    AND "cluster" = :scope.cluster
)
SELECT component, level, count, msg_digest, sample
FROM log_stats
WHERE level = 'ERROR'
ORDER BY cast(count AS bigint) DESC
LIMIT 500
```

If user SQL already starts with `WITH`, the scoped CTE is **prepended**. Response `effective_sql` shows the merge.

### 5.5 Request body

```json
{
  "dataset": "logs",
  "scope": {
    "env": "prod",
    "deploy_type": "dedicated",
    "date": {"gte": "2026-06-18", "lt": "2026-06-20"},
    "org": "1372813089187041280",
    "components": ["tidb", "tikv"]
  },
  "time_range": {
    "begin_time": 1718668800,
    "end_time": 1718672400
  },
  "sql": "SELECT event_time, instance, level, message FROM logs WHERE cluster = 'my-cluster-id' AND level = 'ERROR' ORDER BY event_time DESC LIMIT 500",
  "output_mode": "inline"
}
```

**`log-stats` example:**

```json
{
  "dataset": "log-stats",
  "scope": {
    "date": "2026-07-17",
    "org": "1372813089187041280",
    "cluster": "10004313793950238553"
  },
  "sql": "SELECT hour, component, level, count, msg_digest, sample FROM log_stats WHERE level = 'ERROR' ORDER BY cast(count AS bigint) DESC LIMIT 200",
  "output_mode": "inline"
}
```

**`dataset` values:**

| Value | Behavior |
|-------|----------|
| `logs` | Scope (+ optional `time_range` → `timestamp`) → `WITH logs AS (...)`; SQL must `FROM logs` |
| `log-stats` | Scope (+ optional `time_range` → `hour`) → `WITH log_stats AS (...)`; SQL must `FROM log_stats` |
| `topsql` | Scope (+ optional `time_range` → `timestamps`) → `WITH topsql AS (...)`; SQL must `FROM topsql` |
| `sqlstatement` | Scope (+ optional `time_range` → begin/end overlap) → `WITH sqlstatement AS (...)`; SQL must `FROM sqlstatement` |
| `slowlogs` | Scope (+ optional `time_range` → `time`) → `WITH slowlogs AS (...)`; SQL must `FROM slowlogs` |
| `raw` / omitted | Pass SQL through; **forbid** `scope` / `time_range`. See §5.8 |

**Per-dataset `scope`:**

| Dataset | Required scope keys | Notes |
|---------|---------------------|-------|
| `logs` | `env`, `deploy_type`, `date`, `components` | `org` optional metadata (not injected into SQL); filter `tenant_id` in SQL if needed |
| `log-stats` | `date`, `org`, `cluster` | `date` accepts `yyyy-MM-dd` or `yyyyMMdd`; optional `time_range` → `hour` |
| `topsql` | `org`, `cluster`, `component`, `instance`, `date` | `date` is `yyyy-MM-dd`; optional `time_range` → `timestamps` |
| `sqlstatement` | `org`, `cluster`, `date` | Optional `time_range` → overlapping `begin`/`end` |
| `slowlogs` | `org`, `cluster`, `date` (path partition `yyyyMMdd`) | Optional `time_range` → `time` |

**`time_range` (optional):** half-open `[begin, end)`. Server converts to the dataset’s time column format.

| Field | Description |
|-------|-------------|
| `begin_time` / `end_time` | Unix seconds **or** datetime string |
| `begin` / `end` | Datetime string (`RFC3339`, `yyyy-MM-dd HH:mm[:ss]`, `yyyy-MM-dd`) |

Examples: `{"begin_time":1718668800,"end_time":1718672400}` or `{"begin":"2026-07-17T02:00:00Z","end":"2026-07-17T03:00:00Z"}`.

**Other fields:** `sql`, `output_mode` (`inline` \| `export`), `wait_seconds` (default 30; o11yapi deployment sets 60 via `OLAP_DEFAULT_WAIT_SECONDS`), `max_rows` (≤1000), `database` (defaults to `o11y-logs` for log datasets).

**Validation examples:**

```json
{"error": true, "message": "invalid sql", "full_text": "scope.date is required"}
```

```json
{"error": true, "message": "invalid sql", "full_text": "dataset=log-stats requires querying virtual table \"log_stats\" (e.g. FROM log_stats / JOIN log_stats)"}
```

```json
{"error": true, "message": "invalid sql", "full_text": "scope is not allowed for dataset=\"raw\" (use a scoped dataset such as \"logs\" / \"log-stats\")"}
```

### 5.6 Discovering scope values (`POST /olap/scope/list`)

Because `scope` is required and **injected** partitions must appear as static predicates, clients need a way to discover real Hive values. This endpoint wraps S3 `ListObjectsV2` with `Delimiter=/` under the table location + ancestor path.

**Request:**

```json
{
  "dataset": "logs",
  "field": "component",
  "scope": {
    "env": "prod",
    "deploy_type": "dedicated",
    "date": "2026-07-17"
  },
  "max_keys": 1000,
  "page_token": ""
}
```

| Field | Required | Description |
|-------|----------|-------------|
| `dataset` | Yes | Scoped dataset id (`logs`, `log-stats`, …) |
| `field` | Yes | One name from that dataset’s **ordered** `scope_fields` (aliases like `components` / `component` accepted) |
| `scope` | For non-root levels | **String equality** ancestors for every level **before** `field` |
| `max_keys` | No | Default / max **1000** |
| `page_token` | No | From previous `next_page_token` |

**Response:**

```json
{
  "status": "ok",
  "data": {
    "dataset": "logs",
    "field": "components",
    "values": ["pd", "tidb", "tiflash", "tikv"],
    "has_more": false,
    "next_page_token": ""
  }
}
```

Values are in S3 lexicographic order (Hive dive order for the same key). Walk the path one level at a time:

```
list(env) → list(deploy_type | env=…) → list(date | …) → list(components | …)
```

`GET /olap/tables` / `datasets[].scope_fields` objects are ordered the same way:

```json
"scope_fields": [
  {"name": "env", "projection": "enum", "required": true, "path_key": "env"},
  {"name": "deploy_type", "projection": "enum", "required": true, "path_key": "deployType"},
  {"name": "date", "projection": "date", "required": true, "path_key": "date"},
  {"name": "components", "projection": "injected", "required": true, "path_key": "component"}
]
```

### 5.7 Injected partitions

Athena **injected** projection rejects queries that omit a static predicate for that column.

| Dataset | Injected columns | How to pass |
|---------|------------------|-------------|
| `logs` | `component` | `scope.components: ["tidb","tikv"]` (discover via §5.6) |
| `log-stats` | `org`, `cluster` | `scope.org` / `scope.cluster` strings |

`["*"]` / empty arrays are **rejected** until S3 auto-discovery exists. `IS NOT NULL` does **not** satisfy injection.

### 5.8 Direct query (`dataset=raw`)

For tables without a scoped profile yet (e.g. today’s org-level `sqlstatement_org_*` tables), or any known Glue name:

1. Omit `dataset` or set `"dataset": "raw"`.  
2. Do **not** send `scope` / `time_range`.  
3. Write a full `SELECT` against the physical table; put all filters in SQL.

```json
{
  "sql": "SELECT DISTINCT serverless_cluster_id FROM sqlstatement_org_1372813089209260919_all WHERE \"date\" >= '2026-07-15' AND \"end\" >= to_unixtime(current_timestamp) - 1200",
  "database": "default",
  "output_mode": "inline"
}
```

Managed OLAP tables use Glue `o11y-logs` (also the service default for raw). Legacy org-level tables (`sqlstatement_org_*_all`) live in Glue `default` — pass `"database": "default"`.

| | Scoped (`logs` / `log-stats` / …) | `dataset=raw` |
|--|-----------------------------------|---------------|
| Table in SQL | Virtual alias | Real catalog name |
| `scope` / `time_range` | Per dataset profile | Forbidden |
| Partition filters | Injected in `WITH … AS (...)` | You write them in SQL |
| Typical use | Registered OLAP datasets | Ad-hoc / not-yet-scoped tables |

See §7 Scenario 6 for export + download.

---

## 6. API Reference

| Method | Path | Description |
|--------|------|-------------|
| `GET` | `/olap/tables` | List datasets + Glue tables (`scope_fields` ordered by Hive dive path, `catalog_status` / `data_status`) |
| `POST` | `/olap/scope/list` | List one scope level via S3 delimiter listing (max 1000 / page) |
| `POST` | `/olap` | Submit OLAP SQL; returns `query_id`. Default **wait 30s** (o11yapi deployment: 60s); on success includes `result` |
| `GET` | `/olap/:query_id` | Task status |
| `GET` | `/olap/:query_id/results` | Paged JSON results (`inline`); query `max_rows` (1–1000), `page_token` |
| `GET` | `/olap/:query_id/download` | Presigned CSV download URL; query `expires_seconds` (default 3600, max 12h) |
| `DELETE` | `/olap/:query_id` | Cancel task |

**`output_mode` behavior after successful wait (default 30s):**

| `output_mode` | `result` shape |
|---------------|------------------|
| `inline` | `{ columns, rows, has_more, next_page_token }` |
| `export` | `{ format, s3_uri, download_urls[], expires_at, ... }` — presigned S3 URL for full Athena CSV |

If the query is still running after `wait_seconds`, the response is **`202`** with `query_id` only; poll `GET /olap/:query_id` then fetch `/results` or `/download`.

**Task status:** `QUEUED` → `RUNNING` → `SUCCEEDED` | `FAILED` | `CANCELLED`

**Table metadata:** diagnosis-query **idempotently registers** Glue tables for this region on startup (S3 data not required). `GET /olap/tables` returns the built-in registry; `catalog_status=READY` means the Glue table exists; `data_status=EMPTY` means no Parquet yet (e.g. compute has not written the first hour).

---

## 7. Common Scenarios

Examples below assume:

- `BASE` = Regional API prefix  
- `Authorization` and `x-*` headers are set  
- `scope.org` matches `x-org-id` in headers  

---

### Scenario 1: Query TiDB ERROR logs for one hour (Serverless)

**Requirement:** 2026-06-18 10:00–11:00 (UTC), tidb component ERROR for a Serverless cluster.

**Request:**

```json
{
  "dataset": "logs",
  "scope": {
    "env": "prod",
    "deploy_type": "serverless",
    "date": "2026-06-18",
    "org": "1372813089187041280",
    "components": ["tidb"]
  },
  "time_range": {
    "begin_time": 1750237200,
    "end_time": 1750240800
  },
  "sql": "SELECT event_time, instance, level, position, message FROM logs WHERE level = 'ERROR' ORDER BY event_time DESC LIMIT 200",
  "output_mode": "inline"
}
```

**Submit response (default `wait_seconds: 30`):**

When the query finishes within the wait window, the response is **`200`** and includes inline `result`:

```json
{
  "status": "ok",
  "data": {
    "query_id": "8f3a2b1c-4d5e-6f70-8192-a3b4c5d6e7f8",
    "status": "SUCCEEDED",
    "wait_seconds": 30,
    "submitted_at": "2026-06-18T11:05:02Z",
    "completed_at": "2026-06-18T11:05:04Z",
    "result": {
      "columns": [ ... ],
      "rows": [ ... ],
      "has_more": false
    }
  }
}
```

If still running after 30s, you get **`202`** with `status: "RUNNING"` — poll `GET /olap/{query_id}` then `GET /olap/{query_id}/results`. Set `"wait_seconds": 0` for fully async submit (legacy behavior).

**`GET /olap/{query_id}/results` excerpt (when polling manually):**

```json
{
  "status": "ok",
  "data": {
    "columns": [
      {"name": "event_time", "type": "varchar"},
      {"name": "instance", "type": "varchar"},
      {"name": "level", "type": "varchar"},
      {"name": "position", "type": "varchar"},
      {"name": "message", "type": "varchar"}
    ],
    "rows": [
      {
        "event_time": "2026-06-18T10:15:23.123Z",
        "instance": "db-tidb-0",
        "level": "ERROR",
        "position": "[txn.go:412]",
        "message": "[2026/06/18 10:15:23.123 +00:00] [ERROR] [txn.go:412] [write conflict] ..."
      }
    ],
    "has_more": false
  }
}
```

---

### Scenario 2: Count ERROR / WARN per component (Dedicated)

**Requirement:** Full day, ERROR/WARN counts per component for a Dedicated cluster.

**Request:**

```json
{
  "dataset": "logs",
  "scope": {
    "env": "prod",
    "deploy_type": "dedicated",
    "date": "2026-06-18",
    "org": "1234567890123456789",
    "components": ["tidb", "tikv", "pd", "tiflash"]
  },
  "time_range": {
    "begin_time": 1750204800,
    "end_time": 1750291200
  },
  "sql": "SELECT component, level, count(*) AS cnt FROM logs WHERE level IN ('ERROR', 'WARN') GROUP BY component, level ORDER BY cnt DESC",
  "output_mode": "inline"
}
```

> List components explicitly in `scope.components`. `["*"]` is not supported yet (§5.7).

**Result example:**

```json
{
  "status": "ok",
  "data": {
    "rows": [
      {"component": "tikv", "level": "ERROR", "cnt": "42"},
      {"component": "tidb", "level": "WARN", "cnt": "128"},
      {"component": "tidb", "level": "ERROR", "cnt": "15"}
    ],
    "has_more": false
  }
}
```

---

### Scenario 3: Keyword search for slow transactions / timeout (Premium)

**Requirement:** Within a 30-minute window, logs whose message contains `timeout`.

**Request:**

```json
{
  "dataset": "logs",
  "scope": {
    "env": "prod",
    "deploy_type": "premium",
    "date": "2026-06-18",
    "org": "9876543210987654321",
    "components": ["tidb", "tikv", "pd"]
  },
  "time_range": {
    "begin_time": 1750240000,
    "end_time": 1750241800
  },
  "sql": "SELECT event_time, component, instance, level, substr(message, 1, 500) AS message FROM logs WHERE lower(message) LIKE '%timeout%' ORDER BY event_time LIMIT 100",
  "output_mode": "inline"
}
```

---

### Scenario 4: Single TiKV instance only

**Request:**

```json
{
  "dataset": "logs",
  "scope": {
    "env": "prod",
    "deploy_type": "dedicated",
    "date": "2026-06-18",
    "org": "1234567890123456789",
    "components": ["tikv"]
  },
  "time_range": {
    "begin_time": 1750237200,
    "end_time": 1750240800
  },
  "sql": "SELECT event_time, level, message FROM logs WHERE instance = 'db-tikv-0' AND level IN ('ERROR', 'WARN') ORDER BY event_time DESC LIMIT 1000"
}
```

---

### Scenario 5: Span multiple calendar days (bounded range)

Prefer a **range object** on `date` (legacy `begin_date` / `end_date` still work). `time_range` must still fall within the corresponding Unix interval.

```json
{
  "dataset": "logs",
  "scope": {
    "env": "prod",
    "deploy_type": "serverless",
    "date": {"gte": "2026-06-17", "lte": "2026-06-18"},
    "org": "1372813089187041280",
    "components": ["tidb", "tikv"]
  },
  "time_range": {
    "begin_time": 1750150800,
    "end_time": 1750240800
  },
  "sql": "SELECT date(from_unixtime(timestamp)) AS d, level, count(*) AS cnt FROM logs WHERE level = 'ERROR' GROUP BY 1, 2 ORDER BY d, cnt DESC",
  "output_mode": "inline"
}
```

> Multi-day queries scan multiple `date=` partitions; keep `time_range` narrow and list components explicitly.

---

### Scenario 5b: Top ERROR digests from `log-stats`

**Requirement:** Clinic-style aggregates for one cluster on one UTC day (no `time_range`).

```json
{
  "dataset": "log-stats",
  "scope": {
    "date": "2026-07-17",
    "org": "1372813089187041280",
    "cluster": "10004313793950238553"
  },
  "sql": "SELECT component, level, count, msg_digest, sample FROM log_stats WHERE level = 'ERROR' ORDER BY cast(count AS bigint) DESC LIMIT 50",
  "output_mode": "inline"
}
```

---

### Scenario 6: Large-result CSV export (org-level SQL / raw)

Use `output_mode: "export"` when row count exceeds inline paging (Athena returns at most **1000** rows per `/results` call) or when you want the full CSV locally.

This is the **direct / raw** path (§5.8): you already know the Athena table name, so omit `dataset` (or use `"dataset": "raw"`), do **not** pass `scope` / `time_range`, and query the physical table in SQL. Use **`x-cluster-id: all`** for org-wide tables (job ownership check only).

**Request:**

```bash
curl --location 'https://www.ds.us-east-1-serverless.aws.observability.tidbcloud.com/internal/o11y/diagnosis/api/v1/olap' \
  --header 'accept: application/json' \
  --header 'x-cluster-id: all' \
  --header 'x-provider: aws' \
  --header 'x-region: us-east-1' \
  --header 'o11ytoken: <O11Y_TOKEN>' \
  --header 'Content-Type: application/json' \
  --data '{
  "sql": "SELECT DISTINCT serverless_cluster_id FROM sqlstatement_org_1372813089209260919_all WHERE \"date\" >= '\''2026-07-15'\'' AND \"end\" >= to_unixtime(current_timestamp) - 600",
  "database": "default",
  "output_mode": "export"
}'
```

> Prefer a **literal date** in SQL (e.g. `'2026-07-15'`). Athena functions such as `date_add(...)` are allowed by the API validator; choose what fits the table schema.

**Response `200` (query finished within default 30s wait):**

```json
{
  "status": "ok",
  "data": {
    "query_id": "42485dec-ad06-4e38-90a9-cb6380ca2ffa",
    "engine": "athena",
    "region": "us-east-1",
    "database": "default",
    "status": "SUCCEEDED",
    "output_mode": "export",
    "submitted_at": "2026-07-15T09:21:46Z",
    "completed_at": "2026-07-15T09:21:47Z",
    "wait_seconds": 30,
    "scanned_bytes": 857684,
    "effective_sql": "SELECT DISTINCT serverless_cluster_id FROM sqlstatement_org_1372813089209260919_all WHERE \"date\" >= '2026-07-15' AND \"end\" >= to_unixtime(current_timestamp) - 600",
    "result": {
      "query_id": "42485dec-ad06-4e38-90a9-cb6380ca2ffa",
      "status": "SUCCEEDED",
      "format": "csv",
      "s3_uri": "s3://o11y-prod-shared-us-east-1-serverless/athena-results/logs-query/a415de37-5a3e-4ad5-867a-50cc2b86b298.csv",
      "file_count": 1,
      "scanned_bytes": 857684,
      "expires_at": "2026-07-15T10:21:47Z",
      "download_urls": [
        {
          "url": "https://o11y-prod-shared-us-east-1-serverless.s3.us-east-1.amazonaws.com/athena-results/logs-query/a415de37-5a3e-4ad5-867a-50cc2b86b298.csv?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Expires=3600&X-Amz-Signature=...",
          "expires_at": "2026-07-15T10:21:47Z",
          "bytes": 220536
        }
      ]
    }
  }
}
```

**Download the CSV:**

```bash
curl -o result.csv "$(jq -r '.data.result.download_urls[0].url' response.json)"
```

If the query is still running after the wait window, you get **`202`** with `query_id` only. Poll until `SUCCEEDED`, then either:

```bash
# Refresh presigned URL (default TTL 1h)
curl -s "${BASE}/olap/${QUERY_ID}/download" \
  -H "x-provider: aws" -H "x-region: us-east-1" \
  -H "x-cluster-id: all" -H "o11ytoken: ${O11Y_TOKEN}" | jq '.data.download_urls[0].url'
```

Or use AWS temporary credentials from §2: `aws s3 cp s3://...` with the `s3_uri` from the response.

---

## 8. End-to-End Example

```bash
#!/usr/bin/env bash
set -euo pipefail

GITHUB_PAT="${GITHUB_PAT:?}"
REGION="us-west-2"
BASE="https://www.ds.${REGION}-premium.aws.observability.tidbcloud.com/internal/o11y/diagnosis/api/v1"

AUTH=$(curl -s -X POST 'https://www.ds.us-west-2.aws.observability.tidbcloud.com/client/auth' \
  -H 'Content-Type: application/json' -d "{\"github_token\":\"${GITHUB_PAT}\"}")
TOKEN=$(echo "$AUTH" | jq -r '.token')

HDR=(-H "Authorization: Bearer ${TOKEN}" -H "Content-Type: application/json"
      -H "x-provider: aws" -H "x-region: ${REGION}"
      -H "x-cluster-id: 10324983984131567830"
      -H "x-org-id: 1372813089187041280"
      -H "x-deploy-type: shared")

BODY=$(jq -n '{
  dataset: "logs",
  scope: {env:"prod", deploy_type:"serverless", date:"2026-06-18", org:"1372813089187041280", components:["tidb"]},
  time_range: {begin_time:1750237200, end_time:1750240800},
  sql:"SELECT event_time, level, message FROM logs WHERE level = '\''ERROR'\'' LIMIT 50"
}')

QUERY_ID=$(curl -s -X POST "${BASE}/olap" "${HDR[@]}" -d "$BODY" | jq -r '.data.query_id')

until [[ "$(curl -s "${BASE}/olap/${QUERY_ID}" "${HDR[@]}" | jq -r '.data.status')" == "SUCCEEDED" ]]; do
  sleep 2
done

curl -s "${BASE}/olap/${QUERY_ID}/results" "${HDR[@]}" | jq '.data.rows'
```

---

## 9. Limits and Troubleshooting

| Limit | Description |
|-------|-------------|
| Region | Local region only; `x-region` must match Base URL |
| Cloud vendor | Phase 0 AWS; GCP / Azure / AliCloud later with same API shape |
| Data freshness | Queryable after hourly ETL; not real-time |
| Scoped datasets | `scope` required; keys = that table’s dive path (§4.0). `time_range` **optional** when `time_column` is set |
| Auth / metering | OLAP requires o11y JWT (`o11ytoken`). Server logs `olap query submitted` / `olap query finished` with `github_username` and Athena `scanned_bytes` (+ execution timings). Prometheus metrics: `olap_queries_total`, `olap_scanned_bytes_total`, `olap_engine_execution_seconds_total`, `olap_queries_submitted_total` (labels: `github_username`, `status`, `dataset`, `region`). Grafana dashboard: **OLAP usage by GitHub user** (shipped with o11yapi / diagnosis-query charts). |
| `dataset=logs` | `scope`: `env`, `deploy_type`, `date`, `components`; optional `time_range` → `timestamp`; SQL `FROM logs` |
| `dataset=log-stats` | `scope`: `date`, `org`, `cluster`; optional `time_range` → `hour`; SQL `FROM log_stats` |
| `dataset=topsql` | `scope`: `org`, `cluster`, `component`, `instance`, `date`; optional `time_range` → `timestamps`; SQL `FROM topsql` |
| `dataset=sqlstatement` | `scope`: `org`, `cluster`, `date`; optional `time_range` → begin/end overlap; SQL `FROM sqlstatement` |
| `dataset=slowlogs` | `scope`: `org`, `cluster`, `date` (`deltalake/{org}/{cluster}/slowlogs/date=yyyyMMdd/`); optional `time_range` → `time`; SQL `FROM slowlogs` |
| `dataset=raw` | No `scope` / `time_range`; SQL passed through unchanged |
| Inline rows | **Max 1000** per page (Athena `GetQueryResults` limit); use `export` for full CSV |
| Export presigned URL | Default TTL **1 hour** (`expires_seconds` on `/download`, max 12h) |
| Export retention | Athena result objects in S3 typically retained **24h** (bucket lifecycle) |

| Error | Action |
|-------|--------|
| `401` | Re-authenticate via `/client/auth` |
| `400 invalid region` | Use correct regional Base URL |
| `400 invalid request` / `invalid sql` | Check `dataset`, `scope` keys/forms, `time_range`, virtual alias in SQL |
| `FAILED` + SQL error | Check SQL syntax and table name |
| Empty results | Verify `date` / partitions, ETL completed, `org` / `cluster` |
| Slow queries | Tighten `scope`; add file-column filters in SQL; narrow `time_range` when applicable |
| Athena injected error | Pass injected partitions in `scope` (equality or `IN`); `IS NOT NULL` does **not** work |

---

## 10. Mapping `scope` to Headers

| Cluster Type | `scope.deploy_type` | `x-deploy-type` Header | Common `scope.env` |
|--------------|---------------------|------------------------|-------------------|
| Dedicated | `dedicated` | `dedicated` | `prod` / `staging` / `dev` |
| Premium / NextGen | `premium` | `premium` | `prod` / `staging` / `dev` |
| Serverless / Essential | `serverless` | `shared` | `prod` / `staging` / `dev` |

`scope.org` should match `x-org-id`. For `slowlogs`, pass TiDB Cloud cluster id (applicationID) in `scope.cluster`; the server resolves it to the o11y UUID used in the S3 path (same as slowquery).

---

## Appendix: Response Envelope

Success:

```json
{"status": "ok", "data": { ... }}
```

Failure:

```json
{"error": true, "message": "short summary", "full_text": "detailed reason"}
```