# General Log Offline Data Design

> TiDB Cloud O11Y — logstats ETL offline layer redesign  
> Status: **Recommended design**  
> Last updated: 2026-07-14 (path `component=` injected; Parquet + basename omit `component`; `tenant_id` data column)

---

## 1. Current State and Pain Points

### 1.1 Log Data Landscape

O11Y general logs play different roles across the pipeline. **The offline layer does not replace near-real-time search:**

| Pipeline | Component | Data Form | Typical Use Cases | Latency |
|----------|-----------|-----------|-------------------|---------|
| **Near-real-time query** | Vector → **Loki** | Streaming / indexed | Console search, tail, recent troubleshooting | Seconds to minutes |
| **Offline detail / download** | Vector → S3 raw gz → diagnosis-compute → Parquet | **diagnosis-query** / observability-client | Historical hourly log SQL queries, export, download | **Hourly** |
| **Offline statistics** | diagnosis-compute → log_stats → **Deltalake** | **Clinic** | **Log statistics** for diagnosis analysis | Hourly |

**Supported deployment types (all covered):**

| Product Type | `x-deploy-type` (diagnosis-query header) | Notes |
|--------------|------------------------------------------|-------|
| **Dedicated** | `dedicated` | Standard Dedicated clusters |
| **Premium** | `premium` / `nextgen` | Premium, NextGen, and similar forms |
| **Serverless** | `shared` | Essential / Starter (consistent with production slowquery APIs; uses SharedTier path resolution) |

All three deployment types use the **same Parquet path layout** (`env` / `deployType` / `date` / `component=`); Athena Glue partitions are **`env` / `deploytype` / `date` (projection)** + **`component` (injected)**. `tenant_id`, `cluster`, `instance`, etc. are **Parquet data columns**. Differences are only in cluster metadata resolution (tenant, project, o11y cluster id mapping); diagnosis-query reuses existing `clusterHeader` and K8s metadata logic — **no separate tables per deployment type**.

```mermaid
flowchart TB
  subgraph ingest["Ingestion layer (customer cluster)"]
    V[Vector DaemonSet]
  end

  subgraph realtime["Near-real-time (out of scope)"]
    L[Loki]
  end

  subgraph offline["Offline layer (in scope)"]
    S3R["S3 raw .log.gz"]
    DC[diagnosis-compute]
    PQT["Detail Parquet"]
    LS["log_stats SQLite"]
    DL["log_stats Deltalake"]
  end

  subgraph consume["Consumers"]
    UI["Console / Loki query"]
    DQ[diagnosis-query]
    OC[observability-client download]
    CL[Clinic diagnosis]
  end

  V --> L
  V --> S3R
  L --> UI
  S3R --> DC
  DC --> PQT
  DC --> LS
  LS --> DL
  PQT --> DQ
  DQ --> OC
  DQ --> ATH[Athena and other engines]
  DL --> CL
```

**Division of responsibilities:**

- Need **timely, interactive** log search → use **Loki**; do not depend on offline Parquet.
- Need **historical hourly** detail SQL or export → use **diagnosis-query unified API** (Phase 0: Athena).
- Need **aggregated statistics** → use **log_stats Deltalake** (unchanged before and after redesign).

### 1.2 Current State (logstats ETL)

Regional **diagnosis-compute** (`o11ydiagnosis-logstats`) runs logstats ETL per cluster. **As implemented today**, it processes each UTC hour directly from S3 raw `.log.gz` files (no 5-minute merge step). Main outputs:

| Output | Storage Path (example) | Consumer | Purpose |
|--------|------------------------|----------|---------|
| **Detail Parquet** | `deltalake/global/logs/env=.../deployType=.../date=.../component=.../{hour}_{cluster}_{seq}.parquet` | **diagnosis-query** / observability-client | Historical hourly log SQL, export, download |
| **log_stats statistics** | `diagnosis/data/{storage_id}/log-stats/1h/{utc_hour}.db` → synced to **Deltalake** | **Clinic** | **Log statistics** for diagnosis analysis |

**DEPRECATED — merged text logs (`merged-logs`):**

| Output | Storage Path (example) | Status |
|--------|------------------------|--------|
| **Aggregated text logs (merged)** | `diagnosis/data/{storage_id}/merged-logs/{utc_hour}/{component}/{instance}.log` | **DEPRECATED** — no new writes; replaced by Parquet |

**Legacy pipeline (removed):**

```mermaid
flowchart LR
  V[Vector] --> S3["S3 raw .log.gz"]
  S3 --> DC[diagnosis-compute]
  DC --> M5["5-min local merge (removed)"]
  M5 --> UP["Hourly merged text upload (removed)"]
  M5 --> ST["Hourly log_stats generation"]
  UP --> ML[merged-logs DEPRECATED]
  ST --> DB["SQLite .db"]
  DB --> DLO[deltalake-ops]
  DLO --> DL[log_stats Deltalake]
  ML --> OC[observability-client]
  DL --> CL[Clinic]
```

**Current pipeline (implemented):**

```mermaid
flowchart LR
  V[Vector] --> S3["S3 raw .log.gz"]
  S3 --> DC[diagnosis-compute]
  DC --> H["Hourly: read S3 raw gz"]
  H --> PQT["Write Parquet"]
  H --> ST["Generate log_stats"]
  ST --> DB["SQLite .db"]
  DB --> DLO[deltalake-ops]
  DLO --> DL[log_stats Deltalake]
  PQT --> DQ[diagnosis-query]
  DL --> CL[Clinic]
```

Task discovery and progress: `diagnosis/tasks/processor-dedicated/`; checkpoint: `diagnosis/checkpoints-logstats/{storage_id}/checkpoint-logstats.yaml`.

Deployment reference (us-east-1): default **1 Replica** (horizontally scalable), 7 CPU / 14Gi, ~60+ clusters per region; sharding rules in §3.2.2.

Core implementation: `runClusterHourlyTask` in `cmd/diagnosis-compute/pkg/process/tasks.go`; Parquet writer in `cmd/diagnosis-compute/pkg/parquet/`.

### 1.3 Pain Points

1. **Low utility of aggregated text**  
   merged-logs were plain text concatenated per instance — **not queryable via online SQL**, only downloadable for local grep; little value for Athena or similar analytics.

2. **Poor storage and query efficiency**  
   Text is not columnar-compressed, so volume is large; no partition pruning, making time/component filtering expensive.

3. **Overly heavy processing pipeline**  
   Offline scenarios **do not need 5-minute** local merge and incremental catch-up; timely log search is already covered by **Loki**. The old 5-minute polling added CPU, PVC usage, and code complexity with limited benefit.

4. **Misaligned with real requirements**  
   - Download: users need content for "a given hour, component, instance" — Parquet can export after column filtering.  
   - Statistics: Clinic already uses **log_stats Deltalake**; it does not depend on merged-logs.  
   - Near-real-time: should use **Loki**, not shorter offline ETL cycles.

5. **Parquet should not be written on the Vector side**  
   DaemonSet resources are constrained; raw gz upload is already a bottleneck (see Appendix Q4).

### 1.4 Redesign Scope (Important)

| Item | Changed? |
|------|----------|
| **Loki near-real-time pipeline** | **No** |
| log_stats SQLite + **Deltalake sync** | **No** — Clinic statistics pipeline unchanged |
| **5-minute local merge** | **Removed** — replaced by **hourly** direct processing from S3 raw gz |
| **Upload merged-logs text** | **Removed** — replaced by **Parquet** upload |
| **Raw .log.gz** | **Planned (not yet implemented):** delete processed objects after Parquet write verification (to control storage cost) |
| **Parquet lifecycle** | Default **180 days**; can be extended per **cluster_id** |
| Offline detail **query entry** | **diagnosis-query** unified API (Phase 0: AWS Athena) |
| observability-client log download | **Must adapt** to Parquet data source |

---

## 2. Design Goals and Non-Goals

### 2.1 Goals

1. Replace aggregated text on S3 with **Parquet detail**; query and export via **diagnosis-query unified API** (Phase 0: AWS Athena).
2. **Zero changes** to **log_stats → Deltalake** logic, schema, and sync interfaces — Clinic remains unaware.
3. **Simplify ETL**: remove 5-minute merge; **per UTC hour** read raw gz from S3, single scan produces both log_stats and Parquet.
4. Continue using **regional unified background service** (diagnosis-compute), **horizontally scalable**; on-disk single file **≤ 500 MiB** (see §3.2.1).
5. Parquet **paths and filenames are human-readable**; S3 layout is **`env` / `deployType` / `date` / `component=`**; Athena uses partition projection for the first three layers and **`injected` projection for `component`**. `tenant_id` is a **Parquet data column** (optional filter / API inject via `x-org-id`). See §3.4.1.
6. **Controlled storage cost**: Parquet on disk is ~**2×** gz for same content (after gz deletion, no double storage); Parquet **default 180-day** Lifecycle, extendable per cluster. Regional estimate in §5.2 (**500 GB/day gz**).

### 2.2 Non-Goals

- **Do not replace Loki**: near-real-time search, tail, and minute-level troubleshooting still use Loki; offline layer serves historical hourly scenarios only.
- Offline detail exposed via **diagnosis-query unified query API**; Phase 0 AWS uses Athena; later extend to GCP / Azure / AliCloud engines.
- No client-direct Athena / DuckDB; console and observability-client go through diagnosis-query.
- Do not change **log_stats Deltalake** used by Clinic.
- Do not write detail logs into Deltalake transactional tables.
- Do not use AWS Glue as production hourly full-region ETL.
- No full backfill of historical merged-logs in phase 1.

---

## 3. Design

### 3.1 Target Architecture

```mermaid
flowchart TB
  subgraph ingest["Ingestion"]
    V[Vector DaemonSet]
  end

  subgraph realtime["Near-real-time"]
    L[Loki → console query]
  end

  subgraph etl["diagnosis-compute (hourly, multi-replica)"]
    SHARD["cluster_id mod N → clusters owned by this Pod"]
    R["List S3 raw .log.gz for the hour"]
    P["Stream parse + aggregate log_stats"]
    W["Write Parquet and upload"]
    VFY["Verify Parquet on disk"]
    DEL["Delete processed raw .log.gz (planned)"]
    SHARD --> R
    R --> P
    P --> W
    W --> VFY
    VFY --> DEL
    CP[Update checkpoint]
    DEL --> CP
    P --> CP
  end

  subgraph storage["S3"]
    S3R["Raw .log.gz"]
    PQT["Detail Parquet"]
    DB["log-stats/*.db"]
  end

  subgraph downstream["Downstream"]
    DLO[deltalake-ops]
    DL[log_stats Deltalake]
    ATH[Athena]
    OC[observability-client]
    CL[Clinic]
  end

  V --> L
  V --> S3R
  S3R --> R
  W --> PQT
  P --> DB
  DB --> DLO --> DL --> CL
  PQT --> ATH
  PQT --> OC
```

> **Note:** Raw gz deletion (`DEL`) is **planned but not yet implemented** in code. Today, checkpoint advances after Parquet + log_stats succeed; gz objects remain on S3.

### 3.2 Processing Strategy

| Dimension | Convention |
|-----------|------------|
| Schedule granularity | **Per UTC hour** — process the previous complete hour (not 5-minute micro-batch) |
| Input | S3 raw `.log.gz` (listed by checkpoint and hour window) |
| Output | Same scan: **log_stats SQLite** + **detail Parquet** |
| **Target single-file size** | On disk **≤ 500 MiB**; roll `{seq}` when same `(hour, component, cluster)` exceeds limit (see §3.2.1) |
| **Sharding** | Clusters where `hash(cluster_id) % replicaCount == podOrdinal` are handled by this Pod (see §3.2.2) |
| Compression | **ZSTD** (Parquet page compression; Athena-readable) |
| Idempotency | Overwrite by `(env, deployType, date, component, hour, cluster, seq)` |
| Local state | **No** persistent merged text directory; only temporary buffers during encoding (optional memory / temp files) |
| **Raw gz cleanup** | **Planned (not yet implemented):** after all Parquet for a **cluster-hour** is uploaded and verified, **batch Delete** consumed raw objects; on failure **do not delete**, retry next run |

**Differences from legacy production (implementation status):**

- **Done:** `runClusterMergeTask` 5-minute loop and local `merged-logs` directory maintenance **removed**.
- **Done:** `runClusterHourlyTask` **implemented** in `cmd/diagnosis-compute/pkg/process/tasks.go` — hourly S3 read → write Parquet + log_stats.
- **Planned:** Parquet verification → delete raw gz (or fold into the same hourly job).

Core code: `cmd/diagnosis-compute/pkg/process/tasks.go`, `cmd/diagnosis-compute/pkg/parquet/`, plus related metrics / tests.

#### 3.2.1 Parquet Single-File Size: 500 MiB On Disk

Parquet file size balances **object count**, **compression efficiency**, and **query memory**. This design uses a **500 MiB on-disk upper bound** (aligned with O11Y slowlog); general logs have more columns and wider rows, so smaller files help Athena control memory per split.

| Concept | Convention | Purpose |
|---------|------------|---------|
| **On-disk Parquet** (ZSTD) | Single file **≤ 500 MiB** | Roll `{seq}` when same `(hour, component, cluster)` exceeds limit |
| **Relative to raw gz** | Parquet ~**2×** gz | Regional **storage cost** estimate (§5.2) |

**Write rules (rolling split)**

- Files live under `component={component}/`, named `{hour}_{cluster}_{seq}.parquet` (see §3.4).  
- Per tenant, maintain writers per `(hour, component, cluster)`; when current part on disk **≥ 500 MiB** → close, upload, increment `{seq}` (`01`, `02`, …).  
- If a combination is under 500 MiB in one hour → **single file**, no padding.  
- Row group recommendation **128–256 MiB** (within file): helps Athena row-group pruning.

```mermaid
flowchart LR
  subgraph write["ETL write"]
    STREAM["Stream encode under component directory"]
    ROLL{"On disk ≥ 500 MiB?"}
    FLUSH["Close current part and upload"]
    NEXT["seq++"]
    STREAM --> ROLL
    ROLL -->|yes| FLUSH --> NEXT --> STREAM
    ROLL -->|no| STREAM
  end

  subgraph query["Athena query"]
    PART["Partition projection env/deploytype/date"]
    INJ["Injected component (required equality or IN)"]
    COL["Column filter tenant_id/cluster/instance/timestamp"]
    RG["Row group pruning"]
    PART --> INJ --> COL --> RG
  end
```

**Object count estimate:** dozens of `component=` prefixes under each `date=` (see §3.4.1); files per `(component, cluster)` limited by 500 MiB rolling.

#### 3.2.2 Horizontal Scaling and Sharding

When performance is the bottleneck, **add Replicas** horizontally; do not scale a single Pod without limit.

```mermaid
flowchart TB
  TASKS["diagnosis/tasks/processor-dedicated/"]
  TASKS --> P0["Pod-0 ordinal=0"]
  TASKS --> P1["Pod-1 ordinal=1"]
  TASKS --> PN["Pod-N ordinal=N-1"]

  P0 --> C0["cluster_id % N == 0"]
  P1 --> C1["cluster_id % N == 1"]
  PN --> CN["cluster_id % N == N-1"]
```

| Item | Convention |
|------|------------|
| Shard key | **`cluster_id`** (consistent with task config; if only `storage_id` is used, ensure regional uniqueness and stability) |
| Assignment | `shard = hash(cluster_id) % replicaCount` (or integer `cluster_id` mod directly) |
| This Pod's scope | `shard == podOrdinal` (StatefulSet ordinal or env `POD_INDEX`) |
| Checkpoint | **Still written to S3 per cluster**; each Pod updates only its clusters |
| Scale-out | After increasing `replicaCount`, **rebalance** is required (changing N changes ownership); reserve headroom, avoid frequent N changes |

Helm `replicaCount` default 1; raise to 2/3/4 under load — **compute cost scales roughly linearly** (§5.1).

### 3.3 Storage Cost and Lifecycle

#### 3.3.1 Volume Benchmark

Same cluster, same time window, same detail content — storage object comparison after redesign:

| Format | Relative Volume | Notes |
|--------|-----------------|-------|
| Raw `.log.gz` | **1×** (baseline) | Vector upload |
| Uncompressed plain text | **~10×** | Legacy merged-logs scale |
| Detail **Parquet (ZSTD)** | often **≤1×** vs gz | Columnar + ZSTD; previously uncompressed Parquet was ~5× gz |

Therefore if **gz and Parquet coexist long-term**, cost depends on whether Parquet is smaller than gz after ZSTD. Cost strategy still includes: **delete raw gz after Parquet success** (planned), and retention on Parquet itself.

#### 3.3.2 Raw gz Deletion Strategy

> **Status: planned / future work — not yet implemented in diagnosis-compute.**

```mermaid
sequenceDiagram
  participant DC as diagnosis-compute
  participant S3R as S3 raw gz
  participant S3P as S3 Parquet

  DC->>S3R: List + Get (that UTC hour)
  DC->>DC: Parse + log_stats + write Parquet
  DC->>S3P: PutObject (all partitions for the hour)
  DC->>S3P: HeadObject / row count verification
  alt Verification passed
    DC->>S3R: DeleteObject (only processed keys for this hour)
    DC->>DC: Update checkpoint
  else Verification failed
    DC->>DC: Do not delete gz, alert, retry next run
  end
```

| Rule | Description |
|------|-------------|
| Delete timing | After **all** Parquet objects for that cluster UTC hour are uploaded and verified |
| Delete scope | Only raw gz keys **listed and read** in this hour's task (recorded in in-task manifest) |
| Safety | Parquet write failure, log_stats failure, or verification failure → **forbid gz deletion** |
| Re-run | If gz already deleted and hour must be reprocessed, only Parquet reverse-export or accept hour as non-recomputable (ops uses backfill flow) |
| Gradual rollout | Feature flag can disable deletion during observation; must enable deletion before full rollout |

**Steady-state storage (ignoring merged-logs deprecation):** detail Parquet (ZSTD) is expected **similar to or smaller than** gz; delete gz after success → net storage can drop vs "gz only"; removing merged-logs text saves additional historical redundancy.

#### 3.3.3 Parquet Lifecycle (S3 Lifecycle)

| Tier | Retention Days | Configuration |
|------|----------------|---------------|
| **Default (all regions)** | **180 days** | Bucket rule: `deltalake/global/logs/` prefix `Expiration = 180d` |
| **Per-cluster extension** | > 180 (e.g. 365, 730) | Cluster config `log_parquet_retention_days` (below) |

**Per cluster_id extension:** optional field in cluster task config (e.g. `diagnosis/tasks/processor-dedicated/{storage_id}.yaml`) or GS cluster metadata:

```yaml
# Example: key customer cluster retains 365 days
log_parquet_retention_days: 365
```

Implementation (one or both):

1. **S3 Lifecycle tags**: ETL tags Parquet on upload `retention-days=365`; Lifecycle matches multiple rules by tag (default no tag / `180`).  
2. **Prefix isolation**: long-retention clusters write to `deltalake/global/logs-retention/{days}/env=.../deployType=.../date=.../component=...` (rules per prefix).

After expiry, **Expire** deletes Parquet objects; **log_stats Deltalake** retention is **independent**.

```mermaid
flowchart LR
  subgraph default["Default"]
    P180["Parquet 180d"]
    LC180["Lifecycle Expire"]
    P180 --> LC180
  end

  subgraph extend["Per-cluster extension"]
    CFG["cluster config retention_days"]
    TAG["Object tag / prefix"]
    LCN["Lifecycle N days"]
    CFG --> TAG --> LCN
  end
```

### 3.4 S3 Paths and Naming

**Detail Parquet (new, replaces merged-logs):**

```
s3://o11y-prod-shared-{region}/deltalake/global/logs/
  env={prod|dev|staging}/
    deployType={dedicated|premium|serverless|...}/
      date=YYYY-MM-DD/
        component={component}/
          {hour}_{cluster}_{seq}.parquet
```

Example:

```
s3://o11y-prod-shared-us-east-1/deltalake/global/logs/
  env=prod/deployType=dedicated/date=2026-06-18/
    component=tidb/
      14_clusterA_01.parquet
    component=tikv/
      14_clusterA_01.parquet
```

**Naming rules:**

| Segment | Description |
|---------|-------------|
| `{hour}` | UTC hour, `00`–`23` (two digits) |
| `{cluster}` | O11Y cluster ID |
| `{seq}` | Increments from `01` under same `(date, hour, component, cluster)`; rolls when single file **≥ 500 MiB** |

`tenant_id`, `cluster`, `instance`, etc. are **Parquet data columns** (see §3.5). `component` is a **Glue partition only** (`injected` projection on path `component=`); it is **not** written into Parquet files or repeated in the object basename (avoids Athena/Hive path vs file column conflict).

#### 3.4.1 Path Design Rationale (Projection + Injected `component`)

**Goals:**

- Users / diagnosis-query **need not** pass `tenant_id` for every query (optional isolation via data column + `x-org-id`).
- Path cardinality under `date=` stays small and stable (**dozens of components**, not thousands of tenants).
- Athena can prune by `component` when known.

**Chosen strategy:**

| Layer | Role | Athena |
|-------|------|--------|
| 1–3 | `env` / `deploytype` / `date` | **Partition projection** (enum / date range) |
| 4 | `component=` | **`injected` projection** — query must supply static equality or `IN` |
| — | `tenant_id` / `cluster` / `instance` / … | **Parquet data columns** |

**Why not put `tenant_id` on the path as a Glue partition?**

| Approach | Problem |
|----------|---------|
| `tenant_id` + `injected` | Forces every query to pass `tenant_id` — conflicts with optional-tenant SQL |
| `tenant_id` + omit projection while `projection.enabled=true` | **Invalid** in Athena (all partition columns need projection; template must include them) |
| Nested `tenant_id=` under projected `date=` only | Listing finds files, but **does not** populate a `tenant_id` SQL column from the folder name |
| **`tenant_id` as Parquet column** (this design) | SELECT / optional `WHERE` without forcing path predicates; writers fill the field |

**Why `component` as `injected` (not enum)?**

- Component set is small (~dozens) but **not fixed at table-create time** (new infra / DB components appear over time).
- `enum` would require updating Glue `projection.component.values` whenever a new component appears.
- `injected` avoids a stale enum list; callers pass `component = 'tidb'` or `component IN (...)`.

**How to query “all components” without a predeclared enum?**

Athena **cannot** omit an `injected` column. Options:

1. **diagnosis-query expands “all”** (recommended): when `scope.components` is empty / `*`, list S3 prefixes under `.../date=YYYY-MM-DD/` (`component=*`), then rewrite to `component IN ('tidb','tikv',...)` (Athena `IN` limit 1000 — dozens is fine).
2. **Client / ops** runs the same prefix listing and injects `IN` into SQL.
3. **Do not** rely on `component IS NOT NULL` / omitting `component` — that fails with injected projection.

```mermaid
flowchart TB
  subgraph proj["Partition projection"]
    E["env"]
    D["deployType"]
    DT["date"]
    E --> D --> DT
  end

  subgraph inj["Injected component (required)"]
    C1["component=tidb"]
    C2["component=tikv"]
    DT --> C1
    DT --> C2
  end

  subgraph cols["Parquet data columns"]
    T["tenant_id / cluster / instance / timestamp ..."]
  end

  C1 --> T
  C2 --> T
```

**log_stats (unchanged):**

```
s3://.../diagnosis/data/{storage_id}/log-stats/1h/{utc_hour}.db
→ deltalake-ops-server POST /deltalake/log_stats
```

**Legacy merged-logs (deprecated):**

```
s3://.../diagnosis/data/{storage_id}/merged-logs/...   # No new writes
```

### 3.5 Parquet Schema

**Data columns (written to Parquet files; Glue non-partition columns):**

| Column | Type | Description |
|--------|------|-------------|
| `event_time` | string | Log event time (human-readable, e.g. ISO8601) |
| `timestamp` | bigint | Unix seconds or milliseconds; **prefer for precise time-range filter** in files |
| `host` | string | Hostname |
| `pod` | string | Pod name |
| `level` | string | Log level |
| `position` | string | Source location |
| `message` | string | Log body |
| `cluster` | string | Cluster ID (**regular column**, columnar filter in file) |
| `instance` | string | Instance name (**regular column**, columnar filter in file) |
| `tenant_id` | string | Tenant ID (**regular column**; writers must set it — optional `WHERE` / API inject, **not** a path partition) |
| `msg_digest` | string | Optional; numeric-normalized digest (consistent with log_stats, ETL internal use) |

> **`component` must not appear in Parquet data columns or object basenames.** Glue DDL declares it only under `PARTITIONED BY`; Athena binds `SELECT component` from the path. Object names are `{hour}_{cluster}_{seq}.parquet` under `component=/`.

**Hive partition columns (Glue `PARTITIONED BY`; must match projection / template placeholders):**

| Partition Column | S3 path key (literal) | Discovery | Notes |
|------------------|----------------------|-----------|-------|
| `env` | `env=` | **Partition projection** enum | `prod` / `staging` / `dev` |
| `deploytype` | `deployType=` (camelCase on S3) | **Partition projection** enum | Glue lowers names; use `projection.deploytype.*` and `${deploytype}` |
| `date` | `date=` | **Partition projection** date range | `YYYY-MM-DD` |
| `component` | `component=` | **`injected` projection** | Query must pass `component = '...'` or `IN (...)`; see §3.4.1 for “all components” |

> `hour` is not a Hive partition; it appears in the filename and in `timestamp` / `event_time`. `cluster`, `instance`, `tenant_id` are data columns.

### 3.6 Query Layer and diagnosis-query Unified API

```mermaid
flowchart TB
  subgraph clients["Callers"]
    CON["Console / Clinic tools"]
    OC[observability-client]
  end

  subgraph dq["diagnosis-query"]
    API["/api/v1/olap"]
    ENG["Engine abstraction"]
    AWS["Athena (AWS P0)"]
    GCP["BigQuery stub (501)"]
    AZ["Synapse stub (501)"]
    ALI["MaxCompute stub (501)"]
    API --> ENG
    ENG --> AWS
    ENG -.-> GCP
    ENG -.-> AZ
    ENG -.-> ALI
  end

  subgraph data["Data plane"]
    PQT["Parquet on S3 / object storage"]
    ATH_R["Athena result bucket / export bucket"]
  end

  CON --> API
  OC --> API
  AWS --> PQT
  AWS --> ATH_R
```

| Scenario | Data Source | Method |
|----------|-------------|--------|
| Near-real-time search, tail | **Loki** | Existing console / API |
| Log statistics / Clinic | log_stats Deltalake | **Existing pipeline, unchanged** |
| Offline detail SQL / filter | Parquet | **diagnosis-query → `/api/v1/olap`** |
| Large result export | Parquet | **Async Export + presigned download** |
| Historical log download | Parquet | observability-client (can reuse export API) |

**Design principles**

1. **One API across clouds**: HTTP paths and request bodies independent of `x-provider`; engine chosen by regional config.  
2. **Phase 0 AWS only**: `vendor=aws` uses `AthenaEngine`; other vendors return `501 EngineNotReady` (or feature flag).  
3. **Align with production diagnosis-query**: reuse `clusterHeader` (`x-provider` / `x-region` / `x-org-id` / `x-cluster-id` / `x-deploy-type`, etc.), JWT auth and per-cluster rate limits.  
4. **Large results not in HTTP body**: above threshold, write to result bucket, return **presigned URL** or page token.  
5. **Regional isolation**: each region has its own Athena + regional S3; **no cross-region queries** (see §3.6.1).

#### 3.6.1 Regional Athena Model (Not Global)

```mermaid
flowchart TB
  subgraph r1["Region us-east-1"]
    DQ1[diagnosis-query]
    ATH1[Athena Workgroup]
    GLUE1[Glue Catalog]
    S3D1["S3 o11y-prod-shared-us-east-1"]
    DQ1 --> ATH1 --> GLUE1
    GLUE1 --> S3D1
  end

  subgraph r2["Region eu-west-1"]
    DQ2[diagnosis-query]
    ATH2[Athena Workgroup]
    GLUE2[Glue Catalog]
    S3D2["S3 o11y-prod-shared-eu-west-1"]
    DQ2 --> ATH2 --> GLUE2
    GLUE2 --> S3D2
  end

  r1 -.->|"Cross-region query forbidden"| r2
```

| Principle | Description |
|-----------|-------------|
| **One query plane per region** | Same as production: `diagnosis-query` **deployed per region**, connects only to **same-region** Athena / Glue / object storage. |
| **No global Athena** | No "one Athena queries all global buckets"; cross-region data residency, IAM, and scan cost isolated per region. |
| **Unified logical table names, physical isolation** | Each region Glue uses **same database.table names**; `LOCATION` points to **local** `o11y-prod-shared-{region}`. |
| **Cross-region forbidden** | `x-region` must match this Pod's deployment region; SQL / structured filters **only hit local Catalog**; other region returns `400 invalid region`. |
| **Multi-cloud isomorphism** | GCP / Azure / AliCloud also use **regional** engine instances (BigQuery dataset, Synapse workspace, etc.), not a single global endpoint. |

**Regional config example (diagnosis-query Helm values, us-east-1):**

```yaml
vendor: aws
region: us-east-1
logsQuery:
  athena:
    workgroup: o11y-logs-us-east-1
    database: o11y
    table: general_logs
    dataBucket: o11y-prod-shared-us-east-1
    dataPrefix: deltalake/global/logs/
    resultPrefix: athena-results/logs-query/
```

#### 3.6.2 Table Names and SQL Rules

**Fixed table reference (client-visible):**

| Item | Value | Notes |
|------|-------|-------|
| Catalog (AWS) | `AwsDataCatalog` | Athena default |
| Database | **`o11y`** | Same name per region, independent Glue DB |
| Table | **`general_logs`** | Same name per region, points to local Parquet |
| **Full name** | **`o11y.general_logs`** | Unified in docs and API examples |

Users **may write SQL**, but the server must **validate + inject tenant isolation** before submitting to Athena:

```mermaid
flowchart LR
  IN["Client SQL or structured"]
  VAL["Parse: SELECT / WITH only"]
  TEN["Optional inject tenant_id; mandatory cluster (from header)"]
  PART["Validate: date or begin/end partition predicates"]
  ATH["StartQueryExecution"]
  IN --> VAL --> TEN --> PART --> ATH
```

| Rule | Description |
|------|-------------|
| Allowed statements | **Single** `SELECT` or `WITH ... SELECT` only |
| Forbidden | `INSERT` / `UPDATE` / `DELETE` / `DDL` / multi-statement / `UNLOAD` (export uses export API) |
| Table whitelist | `FROM` / `JOIN` may only reference **`o11y.general_logs`** (aliases like `l` allowed) |
| **User SQL** | **Need not** include `tenant_id`; **must** supply `component` (equality / `IN`) unless diagnosis-query expands “all components” by listing S3 prefixes. Server fills `env` / `deploytype` / `date` from scope when using the API. |
| **Server isolation** (header-driven) | With `x-org-id`, may **optionally** `AND tenant_id = '{x-org-id}'` on the **Parquet data column**; **mandatory** `AND cluster = '{x-cluster-id}'` (**do not trust SQL self-specified cluster**) |
| Partition pruning | `env` / `deploytype` / `date` via **partition projection**; `component` via **injected** values from the query / API |
| Column filter | `tenant_id` / `cluster` / `instance` / `level` / time on **`timestamp`** (or `event_time`) |
| Serverless | When `x-deploy-type=shared`, `tenant_id`/`cluster` mapping same as slowquery |

**Server-side structured → SQL template example:**

```sql
SELECT event_time, level, position, message
FROM o11y.general_logs
WHERE env = 'prod'
  AND deploytype = 'dedicated'
  AND date = '2025-12-31'
  AND cluster = '456'
  AND component IN ('tidb')
  AND instance IN ('db-tidb-0')
  AND level IN ('ERROR')
  AND timestamp >= 1735632000
  AND timestamp < 1735635600
  AND message LIKE '%timeout%'
ORDER BY timestamp DESC
LIMIT 1000
```

**User SQL mode example (`query_type=sql` in request body):**

```sql
SELECT event_time, instance, level, substr(message, 1, 200) AS msg
FROM o11y.general_logs
WHERE date = '2025-12-31'
  AND component = 'tidb'
  AND level IN ('ERROR', 'WARN')
  AND timestamp >= 1735632000
  AND timestamp < 1735635600
ORDER BY timestamp
LIMIT 500
```

Before submit, diagnosis-query wraps as (**user need not write tenant_id**):

```sql
SELECT * FROM (
  /* user sql without limit override if needed */
  ...
) AS user_q
WHERE env = 'prod'
  AND deploytype = 'dedicated'
  AND cluster = '456'
LIMIT 500
```

(Implementation should use AST rewrite rather than naive string concat to avoid injection.)

#### 3.6.3 Query Engine Abstraction (multi-cloud)

Generic SQL analytics lives in **`o11ydiagnosis/pkg/query`** (registered **alongside** slowquery / sqls). It is **not** logs-only: any future Athena SQL (and later GCP/Azure/AliCloud engines) goes through `/api/v1/olap`. Offline logs are one consumer that passes `scope` / `time_range` to inject Hive partition predicates.

```go
// o11ydiagnosis/pkg/query/engine.go
type Engine interface {
    Name() string
    Submit(ctx context.Context, req *EngineSubmitRequest) (*Handle, error)
    Status(ctx context.Context, handle *Handle) (*ExecutionStatus, error)
    FetchPage(ctx context.Context, handle *Handle, nextToken string, maxRows int64) (*Page, error)
    Cancel(ctx context.Context, handle *Handle) error
}
```

| Implementation | Cloud | Engine product | Status |
|----------------|-------|----------------|--------|
| `athenaEngine` | AWS | Athena | **P0** |
| `notReadyEngine` | GCP | BigQuery | Stub → HTTP 501 |
| `notReadyEngine` | Azure | Synapse | Stub → HTTP 501 |
| `notReadyEngine` | AliCloud | MaxCompute | Stub → HTTP 501 |

P0 body is **`sql`** only (SELECT). Optional `scope` / `time_range` inject `env` / `deploytype` / `date` / `component` / `tenant_id` / timestamp predicates. Default rewrite also injects `cluster` from `x-cluster-id`.

#### 3.6.4 HTTP API (diagnosis-query)

**Common request headers** (same as slowquery):

| Header | Required | Description |
|--------|----------|-------------|
| `x-provider` | Yes | `aws` / `gcp` / `azure` / `alicloud` |
| `x-region` | Yes | **Must match diagnosis-query deployment region**; cross-region returns `400` |
| `x-cluster-id` | Yes | O11Y cluster ID |
| `x-org-id` | Depends on type | Common for Dedicated / Premium |
| `x-project-id` | Depends on type | Required for some types |
| `x-deploy-type` | Recommended | `dedicated` / `premium` / `shared` (Serverless) |
| `x-biz-type` | Depends on type | BYOC, etc. |

**Route registration** (`o11ydiagnosis/pkg/query`, `InitOlapApi(apiv1, svc)`):

| Method | Path | Description |
|--------|------|-------------|
| `POST` | `/api/v1/olap` | Submit SQL query (async) |
| `GET` | `/api/v1/olap/:query_id` | Query status |
| `GET` | `/api/v1/olap/:query_id/results` | Paginated JSON results (`inline`) |
| `DELETE` | `/api/v1/olap/:query_id` | Cancel running query |

`output_mode=export` and dedicated `/exports` routes are **planned** (P0 returns `501` for export).

```mermaid
sequenceDiagram
  participant C as Client
  participant DQ as diagnosis-query
  participant ATH as Athena
  participant S3 as S3 result bucket

  C->>DQ: POST /olap (sql + optional scope)
  DQ->>DQ: Validate SELECT + inject cluster / scope
  DQ->>ATH: StartQueryExecution (local Workgroup)
  ATH-->>DQ: queryExecutionId
  DQ-->>C: 202 { query_id, status: RUNNING }

  loop Poll
    C->>DQ: GET /olap/:id
    DQ->>ATH: GetQueryExecution
    ATH-->>DQ: SUCCEEDED
    DQ-->>C: { status, scanned_bytes }
  end

  C->>DQ: GET /olap/:id/results?page_token=
  DQ->>ATH: GetQueryResults
  DQ-->>C: 200 { rows, next_page_token }
```

---

##### A. Submit Query — `POST /api/v1/olap`

**Request headers:**

```http
POST /api/v1/olap HTTP/1.1
Host: diagnosis-query.observability.svc
Content-Type: application/json
x-provider: aws
x-region: us-east-1
x-org-id: "1234567890123456789"
x-project-id: "9876543210987654321"
x-cluster-id: "10989049060142230334"
x-deploy-type: dedicated
Authorization: Bearer <o11ytoken>
```

**Mode 1: `query_type=structured` (default)**

```json
{
  "query_type": "structured",
  "begin_time": 1735632000,
  "end_time": 1735635600,
  "components": ["tidb", "tikv"],
  "instances": ["db-tidb-0"],
  "levels": ["ERROR"],
  "message_contains": "write conflict",
  "message_regex": null,
  "fields": ["event_time", "component", "instance", "level", "position", "message"],
  "order_by": "event_time",
  "desc": true,
  "limit": 1000,
  "output_mode": "inline"
}
```

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| `query_type` | string | No | `structured` (default) or `sql` |
| `begin_time` | int | Required for structured | Unix seconds (inclusive); maps to `date` partition + `timestamp` column range |
| `end_time` | int | Required for structured | Unix seconds (exclusive or inclusive; doc fixes **left-closed right-open** `[begin,end)`) |
| `components` | []string | No | Data column `component`; empty means all components in range (use with care) |
| `instances` | []string | No | Data column `instance` |
| `levels` | []string | No | e.g. `ERROR` / `WARN` / `INFO` |
| `message_contains` | string | No | `message LIKE %...%` |
| `message_regex` | string | No | `regexp_like` if engine supports; P0 may only implement `contains` |
| `fields` | []string | No | Return columns; default `*` for small inline queries only |
| `order_by` | string | No | Column name, default `event_time` |
| `desc` | bool | No | Default `true` |
| `limit` | int | No | Inline default 1000, max 10000 |
| `output_mode` | string | No | `inline` / `export` |

**Mode 2: `query_type=sql`**

```json
{
  "query_type": "sql",
  "sql": "SELECT event_time, instance, level, position, message\nFROM o11y.general_logs\nWHERE date = '2025-12-31'\n  AND component = 'tidb' AND level = 'ERROR'\n  AND timestamp >= 1735632000 AND timestamp < 1735635600\nORDER BY timestamp DESC\nLIMIT 200",
  "output_mode": "inline"
}
```

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| `sql` | string | Required for sql mode | Query `o11y.general_logs` only; **do not write** `tenant_id`/`cluster`/`env`/`deployType` (`cluster` injected by server; `tenant_id` optional) |
| `output_mode` | string | No | Same as structured |

**Success response `202 Accepted`:**

```json
{
  "status": "ok",
  "data": {
    "query_id": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
    "engine": "athena",
    "region": "us-east-1",
    "workgroup": "o11y-logs-us-east-1",
    "table": "o11y.general_logs",
    "status": "RUNNING",
    "submitted_at": "2025-12-31T11:05:02Z",
    "output_mode": "inline"
  }
}
```

**Error example — cross region:**

```json
{
  "error": true,
  "message": "invalid region",
  "full_text": "x-region eu-west-1 does not match deployment region us-east-1"
}
```

**Error example — invalid SQL:**

```json
{
  "error": true,
  "message": "invalid sql",
  "full_text": "only SELECT on o11y.general_logs is allowed"
}
```

---

##### B. Query Status — `GET /api/v1/olap/:query_id`

```http
GET /api/v1/olap/a1b2c3d4-e5f6-7890-abcd-ef1234567890 HTTP/1.1
x-provider: aws
x-region: us-east-1
x-cluster-id: "10989049060142230334"
```

**Response `200` (running):**

```json
{
  "status": "ok",
  "data": {
    "query_id": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
    "status": "RUNNING",
    "engine": "athena",
    "region": "us-east-1",
    "scanned_bytes": null,
    "elapsed_ms": 3200
  }
}
```

**Response `200` (success, inline):**

```json
{
  "status": "ok",
  "data": {
    "query_id": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
    "status": "SUCCEEDED",
    "engine": "athena",
    "region": "us-east-1",
    "scanned_bytes": 10485760,
    "elapsed_ms": 8500,
    "row_count": 237,
    "output_mode": "inline",
    "result_ready": true
  }
}
```

**`status` enum:** `QUEUED` | `RUNNING` | `SUCCEEDED` | `FAILED` | `CANCELLED`

---

##### C. Paginated Results — `GET /api/v1/olap/:query_id/results`

**Query parameters:**

| Parameter | Type | Default | Description |
|-----------|------|---------|-------------|
| `page_token` | string | empty | Previous page `next_page_token` |
| `max_rows` | int | 1000 | Max rows per page, ≤1000 |

```http
GET /api/v1/olap/a1b2c3d4-.../results?max_rows=500 HTTP/1.1
```

**Response `200`:**

```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": "2025-12-31T10:15:23.123Z",
        "instance": "db-tidb-0",
        "level": "ERROR",
        "position": "[txn.go:412]",
        "message": "write conflict..."
      }
    ],
    "row_count": 500,
    "next_page_token": "eyJjIjoiLi4uIn0=",
    "has_more": true
  }
}
```

---

##### D. Cancel — `DELETE /api/v1/olap/:query_id`

**Response `200`:**

```json
{
  "status": "ok",
  "data": {
    "query_id": "a1b2c3d4-...",
    "status": "CANCELLED"
  }
}
```

---

##### E. Large Result Export — `output_mode=export` (planned; P0 returns 501)

Request body same as queries (`structured` or `sql`); **`limit` ignored** or higher cap allowed; fixed `output_mode=export`.

```json
{
  "query_type": "sql",
  "sql": "SELECT event_time, instance, level, message FROM o11y.general_logs WHERE date BETWEEN '2025-12-30' AND '2025-12-31' AND component = 'tikv'",
  "format": "csv",
  "compression": "gzip"
}
```

| Field | Description |
|-------|-------------|
| `format` | `csv` (default) or `parquet` |
| `compression` | `none` / `gzip` (for csv) |

**Response `202`:**

```json
{
  "status": "ok",
  "data": {
    "export_id": "exp-7f8e9d0c-...",
    "query_id": "a1b2c3d4-...",
    "status": "RUNNING",
    "region": "us-east-1",
    "format": "csv",
    "compression": "gzip"
  }
}
```

---

##### F. Export Download — planned (presigned URL)

**Response `200` (not ready):**

```json
{
  "status": "ok",
  "data": {
    "export_id": "exp-7f8e9d0c-...",
    "status": "RUNNING"
  }
}
```

**Response `200` (ready):**

```json
{
  "status": "ok",
  "data": {
    "export_id": "exp-7f8e9d0c-...",
    "status": "SUCCEEDED",
    "region": "us-east-1",
    "format": "csv",
    "compression": "gzip",
    "bytes": 524288000,
    "file_count": 3,
    "download_urls": [
      {
        "url": "https://o11y-prod-shared-us-east-1.s3.amazonaws.com/athena-results/logs-query/exp-.../part-00000.csv.gz?X-Amz-...",
        "expires_at": "2025-12-31T12:00:00Z",
        "bytes": 174762667
      }
    ],
    "expires_at": "2025-12-31T12:00:00Z"
  }
}
```

When multiple files are not bundled, returns URL array with `file_count` > 1; optional future single zip support.

---

**Response envelope:** success `{ "status": "ok", "data": ... }`; failure `{ "error": true, "message": "...", "full_text": "..." }` (aligned with slowquery).

#### 3.6.5 Large Result Strategy

| Threshold (default, configurable) | Behavior |
|-----------------------------------|----------|
| Estimated rows ≤ **1,000** and `output_mode=inline` | `GetQueryResults` paginated JSON |
| Estimated rows **> 1,000** or `output_mode=export` | Auto or forced **Export** |
| Export size **> 50 MiB** | **Presigned URL only** (e.g. 1h TTL), no diagnosis-query body proxy |
| Single export cap | **10 GiB** (configurable); above returns `413`, prompt to narrow time range or add `instances` filter |
| Result retention | Athena result bucket / export prefix **24h Lifecycle** |

AWS Phase 0 implementation notes:

- **Workgroup**: `o11y-logs-{region}` (e.g. `o11y-logs-us-east-1`), same region as local diagnosis-query.  
- **Table**: `o11y.general_logs` → `s3://o11y-prod-shared-{region}/deltalake/global/logs/`.  
- **Result bucket**: `s3://o11y-prod-shared-{region}/athena-results/logs-query/` (isolated from detail prefix).  
- **IAM**: diagnosis-query service account only `StartQueryExecution` in region + read/write local result bucket + **read-only** detail prefix.  
- **Cross-region**: Athena API calls fixed to deployment config e.g. `region=us-east-1`; **do not** accept client-specified other regions.

#### 3.6.6 Glue / Athena DDL (run once per region)

**Chosen layout:**

- Glue partitions: **`env` / `deploytype` / `date`** (projection) + **`component` (`injected`)**.
- S3 path: `.../env=/deployType=/date=/component=/...parquet`.
- **`tenant_id` is a Parquet data column** (writers fill it): optional filter / API inject; **not** required in SQL.

**“All components”:** Athena forbids omitting `injected` columns. diagnosis-query (or ops) should **list** `component=*` prefixes under the projected `date=` and rewrite to `component IN (...)`.

**Glue casing:** partition name `deploytype` (lowercase); path literal `deployType=`; template `${deploytype}`.

```sql
CREATE DATABASE IF NOT EXISTS o11y;

CREATE EXTERNAL TABLE IF NOT EXISTS o11y.general_logs (
  event_time string,
  `timestamp` bigint,
  host string,
  pod string,
  `level` string,
  position string,
  message string,
  cluster string,
  instance string,
  tenant_id string      -- Parquet data column (writers must set)
)
PARTITIONED BY (
  env string,
  deploytype string,    -- lowercase in Glue; S3 path uses deployType=
  `date` string,
  component string      -- injected; required in query (or expanded IN)
)
STORED AS PARQUET
LOCATION 's3://o11y-prod-shared-us-east-1/deltalake/global/logs/'
TBLPROPERTIES (
  'projection.enabled'='true',
  'projection.env.type'='enum',
  'projection.env.values'='prod,staging,dev',
  'projection.deploytype.type'='enum',
  'projection.deploytype.values'='dedicated,premium,serverless',
  'projection.date.type'='date',
  'projection.date.range'='2024-01-01,NOW',
  'projection.date.format'='yyyy-MM-dd',
  'projection.component.type'='injected',
  'storage.location.template'='s3://o11y-prod-shared-us-east-1/deltalake/global/logs/env=${env}/deployType=${deploytype}/date=${date}/component=${component}/'
);
```

> **Notes:**
> - Replace bucket **per region**. Logical name **`o11y.general_logs`** unchanged.
> - Do **not** put `tenant_id` in `PARTITIONED BY` for this design.
> - Do **not** list `component` as both a data column and a partition column in Glue.
> - With `x-org-id`, optionally inject `AND tenant_id = '...'` on the data column.
> - **Do not** add `cluster` to Hive path.

---

## 4. Effort Estimate

| Module | Work Item | Estimate |
|--------|-----------|----------|
| **diagnosis-compute** | Remove 5-minute merge; hourly S3 read; log_stats + Parquet; **cluster_id sharding**; delete gz (**planned**) | **5–7 person-days** |
| **diagnosis-compute** | Unit / FT tests: hourly path, gz delete safety gate (**planned**), checkpoint | **2–3 person-days** |
| **Infrastructure** | S3 Lifecycle: default 180d; per-cluster tag/prefix extension; IAM DeleteObject (**for gz deletion**) | **2–3 person-days** |
| **Config** | `log_parquet_retention_days` per-cluster config and ETL tagging | **1–2 person-days** |
| **o11ydiagnosis / diagnosis-query** | `pkg/query` Engine + Athena (AWS); `/api/v1/olap`; SQL validate/rewrite; export later | **8–10 person-days** |
| **o11ydiagnosis / diagnosis-query** | Dedicated / Premium / Serverless path unit tests; rate limit and auth | **2–3 person-days** |
| **observability-client** | Download via export (planned) or `/api/v1/olap` inline results | **3–5 person-days** |
| **Infrastructure** | Athena Workgroup, result bucket, Glue table, IAM; per-region registration | **2–3 person-days/region** (AWS P0) |
| **Gradual rollout and rollback** | Feature flags; compatibility period reading old merged-logs | **1 person-day** |
| **Docs and integration** | Confirm Clinic unaffected; Loki / Athena scenario docs | **1–2 person-days** |

**Total (first AWS region): ~3–4 person-weeks** (includes diagnosis-query query plane).

**Out of scope this phase:** historical merged-logs backfill; GCP / Azure / AliCloud engine implementation.

---

## 5. Cost Comparison Before and After

### 5.1 Compute Cost

| Item | Before | After |
|------|--------|-------|
| ETL runtime | Regional single Pod ~**$320/month** (us-east-1) | **Same**, no Glue cluster |
| Work mode | 7×24 5-minute merge + hourly analyze | **Hourly batch**, lower CPU when idle |
| Hourly peak | Continuous merge + text upload + stats | Single-hour S3 read + Parquet encode + stats |
| PVC | 200Gi holding large local merged files | Requirement **decreases** (no persistent merge directory) |
| Glue approach (not adopted) | — | ~**$700–1,400/month**, higher ops complexity |

### 5.2 Storage Cost

**Estimate basis:** single **region**, **500 GB / day** raw `.log.gz` ingested (consistent with regional backend scale assumption).

**Volume relationship (benchmark):**

- Parquet (ZSTD) on disk vs uncompressed plain text: typically **≥10×** smaller
- Parquet (ZSTD) on disk vs same-content **gz**: often **similar or smaller** (previous uncompressed writes were ~5× larger than gz)

| Phase | Daily Increment (500 GB gz baseline) | Notes |
|-------|--------------------------------------|-------|
| Before | gz **500 GB** + merged-logs **~500 GB** | Detail dual text storage **~1 TB/day** |
| During migration (dual write) | gz + Parquet **~1 TB** + merged stopped | Short-term increase during gray release; enable gz deletion ASAP (**planned**) |
| **Steady state after** | Parquet only **~1 TB/day** (500×2), **gz deleted** (**planned**) | Detail vs before **roughly flat or slightly lower** (merged removed) |

**Regional Parquet steady-state inventory at default 180-day Lifecycle (peak approximation):**

```
500 GB/day × 2 × 180 days ≈ 180 TB (Parquet, S3 Standard scale)
```

| Item | Estimate |
|------|----------|
| S3 storage ($0.023/GB·month, us-east-1 Standard) | 180 TB ≈ **$4,200/month** (full retention, steady-state upper bound) |
| Actual bill | Below peak (rolling delete + no gz/merged overlap); use CloudWatch/S3 storage metrics |

```mermaid
flowchart TB
  IN["Ingest 500 GB/day gz"]
  ETL["ETL → Parquet ~1 TB/day"]
  DEL["Delete processed gz (planned)"]
  LC["180d Lifecycle"]
  IN --> ETL --> DEL
  ETL --> LC

  subgraph compare["Detail storage comparison"]
    BEF["Before: gz + merged ≈ 1 TB/day"]
    AFT["After: Parquet ≈ 1 TB/day"]
  end
```

**Cost principle:** Parquet verification passed → **delete gz** (**planned**) → avoid "gz + Parquet" overlap; Parquet **180d** (or per-cluster extension) caps total; **do not assume Parquet is smaller than gz**.

### 5.3 Query Cost

| Item | Before | After |
|------|--------|-------|
| Near-real-time | Loki | **Unchanged** |
| Offline detail | Mostly full-file download | **Athena** shallow partition projection + column pruning |
| Clinic statistics | Deltalake log_stats | **Unchanged** |

---

## 6. Post-Redesign Use Cases

### Case 1: User queries last 15 minutes of ERROR (near-real-time)

Use **Loki** — **not** offline Parquet. Offline layer latency is hourly; does not serve this scenario.

### Case 2: Console downloads yesterday 10:00–11:00 TiDB logs (observability-client)

Locate via `env/deployType/date` projection + **injected `component`** (from scope / `IN`); optionally filter Parquet `tenant_id` / `cluster` / `timestamp` → export `.log` or download.

### Case 3: Clinic log pattern statistics (unchanged)

Continue using **log_stats Deltalake** — out of scope for this redesign.

### Case 4: SRE — structured query

```http
POST /api/v1/olap
x-provider: aws
x-region: us-east-1
x-org-id: 1234567890123456789
x-cluster-id: 10989049060142230334
x-deploy-type: dedicated
Content-Type: application/json
```

```json
{
  "query_type": "structured",
  "begin_time": 1735632000,
  "end_time": 1735635600,
  "components": ["tidb"],
  "instances": ["db-tidb-0"],
  "levels": ["ERROR"],
  "fields": ["event_time", "position", "message"],
  "limit": 500,
  "output_mode": "inline"
}
```

### Case 4b: SRE — custom SQL (fixed table `o11y.general_logs`)

```json
{
  "query_type": "sql",
  "sql": "SELECT event_time, instance, level, substr(message,1,500) AS message FROM o11y.general_logs WHERE date='2025-12-31' AND component='tidb' AND level='ERROR' AND timestamp >= 1735632000 AND timestamp < 1735635600 ORDER BY timestamp DESC LIMIT 200",
  "output_mode": "inline"
}
```

Server auto-adds `AND cluster='...'` (from header); `tenant_id` **optionally** injected. Poll `GET .../olap/{id}` → `GET .../results`.

### Case 5: Large result export (hundreds of MB of logs)

```json
{
  "begin_time": 1735660800,
  "end_time": 1735747200,
  "components": ["tikv"],
  "output_mode": "export",
  "format": "csv"
}
```

`output_mode=export` (planned) → on completion download via **presigned URL**; client pulls object storage directly without filling diagnosis-query memory.

### Case 6: Ops fetch by S3 path

`14_clusterA_01.parquet` under `component=tidb/` identifies UTC hour (`14`) and cluster (`clusterA`); full path `env=prod/deployType=dedicated/date=2026-06-18/component=tidb/` identifies environment and component partition.

---

## 7. Alerting and Operations

### 7.1 Deployment

- Helm: `deployments/drhouse/o11ydiagnosis-logstats/`
- Binary: `cmd/diagnosis-compute`
- Release per region `envs/prod-aws-{region}/values.yaml`
- **`replicaCount`**: default 1; increase replicas under load with **StatefulSet ordinal** and `cluster_id % N` sharding (§3.2.2)

### 7.2 Metrics

| Metric | Description | Alert Suggestion |
|--------|-------------|------------------|
| `diagnosis_compute_checkpoint_ts` | UTC time processed per cluster | Lag **> 2 hours** |
| **`diagnosis_compute_hourly_job_running`** (new) | Whether hourly job is running | Single cluster **> 2 hours** incomplete |
| **`parquet_write_success`** (new) | Whether cluster-hour succeeded | **≥ 2 hours** consecutive failure |
| **`parquet_bytes_written`** (new) | Bytes written | **> 80%** drop vs 7-day average |
| **`parquet_write_duration_seconds`** (new) | Hourly job duration | P99 **> 45min** suggests scale-out |
| **`raw_gz_deleted_objects`** (new, **planned**) | gz objects deleted this hour | Long-term mismatch with read count |
| **`raw_gz_delete_failures`** (new, **planned**) | DeleteObject failure count | **> 0** (avoid gz pile-up) |
| `diagnosis_compute_current_merge_tasks` | **Deprecated** after redesign | — |
| **`logs_query_duration_seconds`** (new) | Athena query duration | P99 exceeds Workgroup limit |
| **`logs_export_bytes`** (new) | Export volume | Alert on abnormal spike |

### 7.3 Operations Notes

1. **Re-run an hour:** if gz deleted, only Parquet-based recovery or accept non-recomputable; if gz remains, delete that hour's Parquet + log_stats `.db` → rewind checkpoint → re-run.  
2. **Gradual rollout:** control Parquet write / gz deletion per `storage_id`; recommend **write-only, no delete** first to observe 2× volume, then enable deletion (**planned**).  
3. **Capacity:** watch Parquet write peak memory; monitor **gz pile-up from delete failures** (`raw_gz_delete_failures`, **planned**).  
4. **Athena:** partition projection for `env` / `deploytype` / `date` + **`component` injected** (§3.6.6). Writers store **`tenant_id` in Parquet** so SELECT / optional filters work without requiring `tenant_id` in SQL. For “all components”, API lists S3 `component=*` under `date=` and expands `IN (...)`.  
5. **Lifecycle:** default `deltalake/global/logs/` **180 days**; after changing `log_parquet_retention_days`, sync tag rules or prefixes.  
6. **Legacy data:** merged-logs lifecycle cleanup; historical gz can be batch-deleted after Parquet backfill (separate script).

### 7.4 Rollback

Disable Parquet flag, restore legacy hourly flow (merge + text upload). log_stats pipeline is independent; Clinic unaffected.

---

## Appendix A — FAQ

### Q1. What is the relationship between offline logs and Loki?

**Complementary, not replacement.**

| | Loki | Offline Parquet |
|--|------|-----------------|
| Latency | Seconds to minutes | Hourly (produced after hour boundary) |
| Use cases | Real-time search, tail, recent troubleshooting | Historical hourly download, Athena analysis |
| Retention | Hot data retention | Long-term cold storage |

Need to "see logs from a moment ago" → **Loki**. Need "ERROR detail for 10:00 last week" → **Athena + Parquet**.

---

### Q2. Why cancel 5-minute merge and switch to hourly processing?

Offline has **no minute-level** query need; that latency is served by **Loki**. 5-minute polling caused:

- 7×24 continuous LIST/read S3, write local disk;
- 200Gi PVC and merge code maintenance;
- Misalignment with "hourly partitioned Parquet" goal.

Switch to **hourly** read of all raw gz for that hour from S3 — **one scan** produces log_stats + Parquet; shorter pipeline, lower resource use.

---

### Q3. Why not use AWS Glue for ETL?

Glue suits ad-hoc backfill, not **hourly, full-region** periodic jobs.

| Scenario | Result |
|----------|--------|
| diagnosis-compute fixed Pod | ~**$320/month** |
| 66 clusters × 1 hour Glue | Extrapolated ~**$1–2/hour** ($700–1,400/month) |

DPU billed on wall clock, low utilization, Spark cold start, extra IAM/ops surface. **Conclusion:** continue diagnosis-compute.

---

### Q4. Why Parquet instead of Avro?

| Dimension | Parquet | Avro |
|-----------|---------|------|
| Layout | Columnar | Row-oriented |
| Athena | Column pruning, smaller scans | Must read full rows |

Offline detail is mainly Athena **column filter and aggregate** — choose Parquet.

---

### Q5. Why Parquet over ORC?

Same-data Athena benchmark: `GROUP BY component` scan Parquet ~**11 KiB** vs ORC ~**77 MiB**. Athena / Glue Catalog Parquet support is mature. **Conclusion:** Parquet only for detail layer.

---

### Q6. Why not have Vector upload Parquet directly?

Vector is a DaemonSet with **limited CPU/memory**; raw gz upload is already a bottleneck; node-side Parquet encoding is **slower**. Regional hourly aggregation and file size control belong in **diagnosis-compute**.

---

### Q7. Why write Parquet hourly instead of continuously?

Continuous micro-batch → many small files → must merge; Deltalake compaction slows writes and filenames become UUIDs.

Our approach: **1 hour** per batch + on-disk **500 MiB** rolling + **projected** `env/deploytype/date` + **injected `component`** path + semantic filenames; **`tenant_id` in Parquet** for optional isolation without path predicates.

---

### Q8. Why diagnosis-query instead of exposing Athena directly?

- **Multi-cloud consistency**: unified REST across four clouds; Phase 0 then BigQuery / Synapse / MaxCompute.  
- **Security**: SQL whitelist + **mandatory `cluster` injection** (from header); `tenant_id` **optional** injection (`x-org-id`), prevent cross-tenant scan.  
- **Regional isolation**: each region Athena **queries local bucket only**; no client cross-region.  
- **Large results**: unified Export + presigned URLs.  
- **Production alignment**: auth, rate limits, `x-deploy-type` same as slowquery.

---

### Q8b. Cross-region query? Global Athena or per-region stack?

**No cross-region queries.** `x-region` must match current `diagnosis-query` deployment; Athena accesses **local** `o11y-prod-shared-{region}` bucket only.

Use **per-region** Athena + Glue + bucket — **not** one global Athena scanning multi-region S3:

| Model | Conclusion |
|-------|------------|
| Per-region Athena + local S3 | **Adopted**; aligns with regional diagnosis-query / diagnosis-compute |
| Global Athena cross-region buckets | **Not adopted**; cross-region IAM, latency, compliance, billing hard to control |

Logical table name unified as **`o11y.general_logs`**; each region Glue registers independently, `LOCATION` points to local path.

---

### Q9. Why must log_stats Deltalake be kept?

Clinic depends on existing **log_stats** aggregate tables. This redesign only replaces **detail download aggregated text** with Parquet; statistics pipeline unchanged.

---

### Q10. Parquet is larger than gz — why still Parquet? How to avoid storage cost increase?

**Benchmark:** same detail content Parquet (**ZSTD**) is typically **smaller than or comparable to** raw gz; previously uncompressed Parquet inflated ~5× vs gz. Parquet is for **Athena column pruning and structured query**, with ZSTD for on-disk size.

**Cost measures (must do together):**

1. **After Parquet verification, delete** processed raw gz for that hour (**planned**) — avoid dual detail storage.  
2. **Stop merged-logs**, remove useless third text copy.  
3. Parquet **default 180-day** S3 Lifecycle; extend only for **cluster_id** with `log_parquet_retention_days`.

Steady-state detail ≈ one ZSTD Parquet copy (≤ gz in typical cases); vs current "gz + merged-logs", total storage **controllable, not necessarily higher**; long-term capped by **180d (or per-cluster)**.

---

### Q11. Why 500 MiB on-disk Parquet single-file limit?

Aligned with O11Y slowlog; general log uses **500 MiB on-disk upper bound**:

- **Roll condition**: under same `(date, hour, component, cluster)`, when current part on disk **≥ 500 MiB** → increment `{seq}` and new file.  
- **Benefit**: moderate file size, Athena split memory controlled; vs 128 MiB scheme, object count does not explode.  
- **Path pairing**: files under `component=` as `{hour}_{cluster}_{seq}.parquet` (§3.4.1); `tenant_id` is a Parquet column.

Row groups within file still recommended **128–256 MiB**.

### Q12. Why `injected` for `component`? Why not path / injected for `tenant_id`?

**`component` uses `injected` (not a stale enum):**

1. Component cardinality is small (~dozens) but the set is **not fully known at DDL time**.
2. `enum` would require updating Glue `projection.component.values` whenever a new component appears.
3. Queries must pass `component = '...'` or `IN (...)`. For **“all components”**, diagnosis-query **lists** `component=*` under `date=` and expands `IN` (Athena cannot omit injected columns).

**`tenant_id` is a Parquet column (not Glue partition):**

1. Avoid forcing every SQL to include `tenant_id` (`injected` would require it).
2. Optional `AND tenant_id = ...` / `x-org-id` inject remains a **column filter**.
3. Path no longer uses `tenant_id=`; writers must set the Parquet field.

**`cluster` not in Hive path:**

1. Prefix explosion + repair pressure if registered without projection.
2. `cluster` stays a **Parquet data column**.

---

## Appendix B — Code and Config Index

| Item | Path |
|------|------|
| Main processing (`runClusterHourlyTask`) | `cmd/diagnosis-compute/pkg/process/tasks.go` |
| Parquet writer | `cmd/diagnosis-compute/pkg/parquet/` (`manager.go`, `row.go`, `path.go`) |
| log_stats schema | `cmd/diagnosis-compute/pkg/process/db.go` |
| Checkpoint / task list | `cmd/diagnosis-compute/pkg/process/storage.go` |
| Deltalake sync (log_stats) | `cmd/diagnosis-compute/pkg/deltalake/deltalake.go` |
| Regional ETL deployment | `deployments/drhouse/o11ydiagnosis-logstats/` |
| diagnosis-query entry | `cmd/diagnosis-query/main.go` |
| Query API | `o11ydiagnosis/pkg/query/` |
| Cluster header / deployment type | `o11ydiagnosis/pkg/slowquery/service.go` (`clusterHeader`) |
| diagnosis-query deployment | `deployments/drhouse/diagnosis-query/` |