---
name: clinic-data-proxy
description: |
  Canonical Data Proxy warehouse SQL, batch query, table schema, service
  capabilities, persisted Statements, Top RU, and SQL best practices for Clinic.
---

# Data Proxy query API

Authenticate with `CLINIC_API_KEY` (see parent `clinic-api/SKILL.md`). New calls must use the canonical `${CLINIC_ENDPOINT}/data-proxy/api/v1/...` paths documented here.

In canonical paths, `{orgID}` is the **TiDB Cloud/DBAAS org ID**. Most paths use a TiDB Cloud logical `{clusterID}`. The BYOC live diagnosis routes documented in `../byoc-diagnostics/SUBSKILL.md` also accept the physical `byoc-host` ID where stated. If resolving IDs from legacy Dashboard metadata, use `tenantID` as canonical `{orgID}`; Dashboard `orgID` is a Clinic-internal ID and must not be put in canonical paths.

Canonical requests do not accept caller-selected provider, region, data-source, project, or deployment placement. Data Proxy validates org/cluster ownership and resolves the effective target from Clinic control-plane metadata.

For supported non-BYOC storage pipelines, query persisted **Slow Query**, **TopSQL**, **LogStats**, and other exported tables using SQL.

For BYOC, do not use this warehouse path for Slow Query, Top SQL, or live Statements. BYOC keeps diagnostic data customer-side and uses fixed canonical GET routes that proxy O11y directly. Never create S3/Athena tables or add a storage fallback for an empty BYOC response. Read `../byoc-diagnostics/SUBSKILL.md` for the unified support matrix and exact contracts.

Important: for **Starter / Essential / shared** slow query, prefer the NGM helper below (`ngm_slow_query_list` / `clinic_ngm_slow_query_list`). These clusters may have raw slowlog objects but no Data Proxy `slow_query_logs` Delta table / `cluster-dataurls.slowQueryS3Url`, so Data Proxy SQL can return `DATASOURCE_NOT_CONFIGURED` even when NGM slow query works.

## Python helper (preferred)

Use `clinic_api.py` for all SQL queries. The default helpers use canonical paths and resolve the canonical org ID from the cluster ID automatically; callers should not supply routing placement.

```python
import sys; sys.path.insert(0, ".opencode/skills/nutshell-skills/platform/clinic-api/scripts")
from clinic_api import ClinicAPI
api = ClinicAPI()

# Slow query top digests
r = api.query_sql("CLUSTER_ID", """
  SELECT digest, any_value(query) AS sample_sql, COUNT(*) AS exec_count, MAX(query_time) AS max_qt
  FROM clinic_data_proxy.slow_query_logs WHERE date='20260405'
  GROUP BY digest ORDER BY max_qt DESC LIMIT 20
""")
# NOTE: rows can be null; all values are strings — cast before numeric formatting
for row in r.get("rows") or []:
    print(f"digest={row[0]} cnt={row[2]} max_qt={float(row[3]):.1f}s")

# TopSQL top CPU
r = api.query_sql("CLUSTER_ID", """
  SELECT sql_digest, any_value(normalized_sql) AS sample_sql, SUM(topsql_cpu_time_ms) AS cpu_ms
  FROM clinic_data_proxy.topsql WHERE date='2026-04-05'
  GROUP BY sql_digest ORDER BY cpu_ms DESC LIMIT 20
""")

# LogStats top messages. Use numeric YYYYMMDD date for LogStats.
r = api.log_stats_top("CLUSTER_ID", "20260618", limit=20)

# Starter/Essential/shared slow query via NGM, not Data Proxy SQL.
r = api.ngm_slow_query_list(
    "CLUSTER_ID",
    begin_time=1783659360,
    end_time=1783659660,
    limit=20,
    order_by="query_time",
    desc=True,
)

# Table schema
r = api.get_schema("CLUSTER_ID", "slow_query_logs")
```

## Shell helpers

After sourcing `scripts/clinic-shell.sh`, the Data Proxy sub-skill exposes these helpers. The existing helper names now call canonical endpoints by default and automatically resolve the canonical org ID from `CLUSTER_ID`.

```bash
source scripts/clinic-shell.sh
get_clinic_auth

clinic_dataproxy_schema "CLUSTER_ID" "log_stats"
clinic_dataproxy_query "CLUSTER_ID" "SELECT level, SUM(count) FROM log_stats WHERE date = 20260618 GROUP BY level" 120
clinic_dataproxy_batch_query "CLUSTER_ID" '[{"sql":"SELECT COUNT(*) FROM clinic_data_proxy.slow_query_logs WHERE date = '\''20260721'\''"}]'
clinic_dataproxy_capabilities
clinic_statements_query "CLUSTER_ID" "2026-07-21" 20 300

# Top RU helpers resolve canonical org scope from CLUSTER_ID.
clinic_top_ru_summary "CLUSTER_ID" "$START_TS" "$END_TS" 10 ru deltalake 60
clinic_top_ru_summary_plan "CLUSTER_ID" "$START_TS" "$END_TS" 10 \
  "SQL_DIGEST" ru deltalake 60
clinic_top_ru_user_stats "CLUSTER_ID" "$START_TS" "$END_TS" 20

# BYOC helpers read customer-side O11y directly; see ../byoc-diagnostics/SUBSKILL.md.
clinic_byoc_slow_query_list "BYOC_CLUSTER_ID" "$START_TS" "$END_TS" 20 query_time true
clinic_byoc_top_sql_instances "BYOC_CLUSTER_ID" "$START_TS" "$END_TS"
clinic_byoc_statements_list "BYOC_CLUSTER_ID" "$START_TS" "$END_TS"

clinic_log_stats_schema "CLUSTER_ID"
clinic_log_stats_summary "CLUSTER_ID" "20260618" 50
clinic_log_stats_top "CLUSTER_ID" "20260618" 50
clinic_log_stats_search "CLUSTER_ID" "20260618" "region is unavailable" 50

# Starter/Essential/shared slow query via NGM.
clinic_ngm_slow_query_list "CLUSTER_ID" 1783659360 1783659660 20 query_time true \
  "query,timestamp,query_time,memory_max,digest,index_names" "" "" true
```

## Canonical API overview

| Capability | Method and path |
|------------|-----------------|
| SQL query | `POST /data-proxy/api/v1/orgs/{orgID}/clusters/{clusterID}/sql-queries` |
| Same-cluster SQL batch | `POST /data-proxy/api/v1/orgs/{orgID}/clusters/{clusterID}/sql-query-batches` |
| Table schemas | `GET /data-proxy/api/v1/orgs/{orgID}/clusters/{clusterID}/table-schemas` |
| Service capabilities | `GET /data-proxy/api/v1/capabilities` |
| Top RU summary | `GET /data-proxy/api/v1/orgs/{orgID}/clusters/{clusterID}/top-ru/summary` |
| Top RU summary by plan | `GET /data-proxy/api/v1/orgs/{orgID}/clusters/{clusterID}/top-ru/summary-plan` |
| Top RU user stats | `GET /data-proxy/api/v1/orgs/{orgID}/clusters/{clusterID}/top-ru/user-stats` |
| BYOC Slow Query | `GET .../slow-queries`, `.../slow-queries/available-fields`, `.../slow-queries/detail` |
| BYOC Top SQL | `GET .../top-sql/instances`, `.../top-sql/summary` |
| BYOC live Statements | `GET .../statements`, `.../statements/{available-fields,statement-types,plans,plan-detail}` |

## Top RU APIs

Top RU is available for plain logical **Premium** and **Essential** clusters. Resource Pool variants, Dedicated, Starter, and BYOC are not part of this contract. This includes `summary-plan`; it is not a BYOC feature. Resolve exact metadata with `clinic_get_cluster_by_id` and check `deployType` / `deployTypeV2` before choosing this API. Data Proxy resolves the O11y target and forwards only the allowlisted query parameters; callers do not provide placement headers.

The accepted non-resource-pool `deployTypeV2` values are `premium`, `premium reserved`, `essential`, and `essential v2`. A legacy record with empty `deployTypeV2` is accepted only when `deployType=premium`; any other nonempty or conflicting value fails closed. In particular, values ending in `resource pool` are not supported.

### Summary

```text
GET /data-proxy/api/v1/orgs/{orgID}/clusters/{clusterID}/top-ru/summary
```

Required query parameters: inclusive Unix-second `start` and `end`, plus `top`. Optional parameters are `order_by` (`ru`, `exec_count`, or `duration`), `user`, `data_source` (normally `deltalake`), and integer-second `step`.

Do not send `digest` to this endpoint. SQL-digest filtering is available only on `summary-plan`.

```bash
curl --fail-with-body --get \
  -H "Authorization: Bearer ${CLINIC_API_KEY}" \
  --data-urlencode "start=${START_TS}" \
  --data-urlencode "end=${END_TS}" \
  --data-urlencode 'top=10' \
  --data-urlencode 'order_by=ru' \
  --data-urlencode 'data_source=deltalake' \
  --data-urlencode 'step=60' \
  "${CLINIC_ENDPOINT}/data-proxy/api/v1/orgs/${DBAAS_ORG_ID}/clusters/${CLUSTER_ID}/top-ru/summary"
```

`user` has three distinct meanings: omit it for no user filter, send an empty value for empty/NULL users, or send a name for one user. Do not collapse these cases.

### Summary by plan

```text
GET /data-proxy/api/v1/orgs/{orgID}/clusters/{clusterID}/top-ru/summary-plan
```

Required query parameters are inclusive Unix-second `start` and `end`, plus `top`. Optional parameters are `digest`, `order_by` (`ru`, `exec_count`, or `duration`), `user`, `data_source` (normally `deltalake`), and integer-second `step`. `digest` is an opaque SQL digest filter; omit it to aggregate all SQL/plan groups. The same three-state `user` rule as summary applies. Data Proxy intentionally leaves value-domain validation such as the allowed window and `top` range to O11y, so do not invent client-side limits that are absent from the current contract.

Omitted optional parameters are not sent; O11y applies its current defaults. A successful JSON value is returned without a Data Proxy wrapper, so callers must tolerate O11y-compatible additional fields rather than depending on a locally fixed response schema.

```bash
curl --fail-with-body --get \
  -H "Authorization: Bearer ${CLINIC_API_KEY}" \
  --data-urlencode "start=${START_TS}" \
  --data-urlencode "end=${END_TS}" \
  --data-urlencode 'top=10' \
  --data-urlencode "digest=${SQL_DIGEST}" \
  --data-urlencode 'order_by=ru' \
  --data-urlencode 'data_source=deltalake' \
  --data-urlencode 'step=60' \
  "${CLINIC_ENDPOINT}/data-proxy/api/v1/orgs/${DBAAS_ORG_ID}/clusters/${CLUSTER_ID}/top-ru/summary-plan"
```

Python callers can use `ClinicAPI.top_ru_summary_plan(...)`. `user=None` omits the filter, while `user=""` sends the distinct empty/NULL-user filter.

### User stats

```text
GET /data-proxy/api/v1/orgs/{orgID}/clusters/{clusterID}/top-ru/user-stats
```

Required query parameters: `start` and `end`. `top` is optional.

```bash
curl --fail-with-body --get \
  -H "Authorization: Bearer ${CLINIC_API_KEY}" \
  --data-urlencode "start=${START_TS}" \
  --data-urlencode "end=${END_TS}" \
  --data-urlencode 'top=20' \
  "${CLINIC_ENDPOINT}/data-proxy/api/v1/orgs/${DBAAS_ORG_ID}/clusters/${CLUSTER_ID}/top-ru/user-stats"
```

Successful responses preserve the O11y JSON body without a Data Proxy success envelope. `GET .../top-ru/config` is deferred and is not available.

## Compatibility and unmigrated APIs

The APIs in this section do not yet have canonical replacements. Use them only for the stated cases; do not use their route shape as a model for new calls.

### NGM Slow Query API for Starter / Essential / shared

Use this path when a Starter / Essential / shared cluster has slow query data in
NGM but Data Proxy SQL returns `DATASOURCE_NOT_CONFIGURED` or lacks
`cluster-dataurls.slowQueryS3Url`.

#### Endpoint

```
GET /ngm/api/v1/slow_query/list
```

#### Required headers

The shell and Python helpers resolve these headers from
legacy Dashboard discovery plus `clinic_legacy_get_cluster` fallback:

| Header | Source | Notes |
|--------|--------|-------|
| `Authorization` | `CLINIC_API_KEY` | Bearer internal access key |
| `x-provider` | `cloudProvider` / `provider` / `vendor` / `clusterProviderName` | Lowercase, for example `aws` |
| `x-region` | `region` / `clusterRegionName` | Cloud region, for example `us-east-1` |
| `x-org-id` | `tenantID` | NGM org id. Do not use internal Clinic REST `orgID` as the preferred value |
| `x-project-id` | `projectID` / `projectId` | TiDB Cloud project id |
| `x-cluster-id` | `clusterID` | The target TiDB cluster id |
| `x-deploy-type` | `clusterDeployType` / `deployType`; fallback `clusterDeployTypeV2` / `deployTypeV2` | Lowercase, spaces to `_`; **`starter` / `essential` / `shared` must be sent as `shared`** because NGM rejects `essential` as an unsupported deploy type |

#### Query parameters

| Parameter | Required | Description |
|-----------|----------|-------------|
| `begin_time` | Yes | Unix seconds start time |
| `end_time` | Yes | Unix seconds end time |
| `fields` | No | Comma-separated returned fields. Default helper value: `query,timestamp,query_time,memory_max,digest,index_names` |
| `limit` | No | Maximum rows. Default helper value: `100` |
| `orderBy` | No | Sort field. Default helper value: `query_time` |
| `desc` | No | `true` / `false`. Default helper value: `true` |
| `text` | No | Optional text filter |
| `digest` | No | Optional SQL digest filter |
| `show_internal` | No | `true` / `false`. Default helper value: `true` |

#### Shell examples

```bash
source scripts/clinic-shell.sh
get_clinic_auth

# Prod example window; response is raw NGM JSON.
clinic_ngm_slow_query_list \
  "10357457656304704876" \
  1783656600 \
  1783661100 \
  1 \
  query_time \
  true \
  "query,timestamp,query_time,memory_max,digest,index_names" \
  "" \
  "" \
  true
```

#### Python example

```python
from clinic_api import ClinicAPI

api = ClinicAPI()
result = api.ngm_slow_query_list(
    "10357457656304704876",
    begin_time=1783656600,
    end_time=1783661100,
    limit=1,
    order_by="query_time",
    desc=True,
    fields="query,timestamp,query_time,memory_max,digest,index_names",
    show_internal=True,
)
```

#### Equivalent curl shape

Do not print the API key in logs. The concrete header values are resolved by
the helpers:

```bash
curl -sS -G "${CLINIC_ENDPOINT}/ngm/api/v1/slow_query/list" \
  -H "Authorization: Bearer ${CLINIC_API_KEY}" \
  -H "x-provider: aws" \
  -H "x-region: us-east-1" \
  -H "x-org-id: ${TENANT_ID}" \
  -H "x-project-id: ${PROJECT_ID}" \
  -H "x-cluster-id: 10357457656304704876" \
  -H "x-deploy-type: shared" \
  --data-urlencode "begin_time=1783656600" \
  --data-urlencode "end_time=1783661100" \
  --data-urlencode "fields=query,timestamp,query_time,memory_max,digest,index_names" \
  --data-urlencode "limit=1" \
  --data-urlencode "orderBy=query_time" \
  --data-urlencode "desc=true" \
  --data-urlencode "show_internal=true"
```

### Serverless parent metrics API (PromQL)

Use this Prometheus-compatible API for Starter/Essential parent/control-plane metrics when the caller has Grafana-style `control_plane_info` instead of a TiDB `cluster_id`.

**Endpoint prefix**:

```
/data-proxy/serverless-parent-metrics/api/v1
```

| Endpoint | Method | Description |
|----------|--------|-------------|
| `/query` | GET/POST | Instant PromQL query |
| `/query_range` | GET/POST | Range PromQL query |
| `/series` | GET/POST | Series discovery |

Required routing parameter:

| Parameter | Required | Description |
|-----------|----------|-------------|
| `control_plane_info` | Yes | `projectID/vendor-region/applicationID`, for example `us-east-1-f04/aws-us-east-1/devtier-infra` |

Prometheus parameters match the standard API:

- `query`: required for `/query` and `/query_range`
- `match[]`: required for `/series`
- `start`, `end`, `step`: required for `/query_range`
- `time`, `timeout`: optional where supported by Prometheus

Important behavior:

- `control_plane_info` is used to resolve the parent O11y Prometheus target.
- PromQL is forwarded unchanged. If the metric requires filtering by `control_plane_info`, include that label in the PromQL yourself.
- This API does not use `cluster_id`, `cluster-dataurls`, or Data Proxy SQL.

#### Example: parent storage usage ratio

```bash
curl -sS -G "${CLINIC_ENDPOINT}/data-proxy/serverless-parent-metrics/api/v1/query_range" \
  -H "Authorization: Bearer ${CLINIC_API_KEY}" \
  --data-urlencode "control_plane_info=us-east-1-f04/aws-us-east-1/devtier-infra" \
  --data-urlencode 'query=sum(pd_cluster_status{type="storage_size"}) / sum(pd_cluster_status{type="storage_capacity"})' \
  --data-urlencode "start=2026-03-23T14:55:13+08:00" \
  --data-urlencode "end=2026-03-23T15:45:34+08:00" \
  --data-urlencode "step=30s"
```

Successful response shape:

```json
{
  "status": "success",
  "data": {
    "resultType": "matrix",
    "result": [
      {
        "metric": {},
        "values": [[1774248900, "0.06291398405401377"]]
      }
    ]
  }
}
```

### Serverless parent logs API (LogQL)

Use this Loki-compatible API for Starter/Essential parent/control-plane logs when the caller has Grafana-style `control_plane_info` instead of a TiDB `cluster_id`.

**Endpoint prefix**:

```
/data-proxy/serverless-parent-logs/api/v1
```

| Endpoint | Method | Description |
|----------|--------|-------------|
| `/query_range` | GET | Query log lines over a time range. This is the main log search endpoint |
| `/labels` | GET | Discover available label names |
| `/label/:name/values` | GET | Discover values for one label, for example `container` or `namespace` |
| `/series` | GET | Discover matching log stream label sets without returning log lines |

Required routing parameter:

| Parameter | Required | Description |
|-----------|----------|-------------|
| `control_plane_info` | Yes | `projectID/vendor-region/applicationID`, for example `us-east-1-f04/aws-us-east-1/devtier-infra` |

Optional routing override:

- `datasource`, `datasource_uid`, `datasourceUid`, or `uid`: explicitly choose the system Loki datasource, for example `Loki-aws-us-east-1`.

Important behavior:

- `control_plane_info` resolves the system Loki datasource by provider/region, for example `aws/us-east-1` -> `Loki-aws-us-east-1`.
- The proxy automatically injects the parent cluster isolation label: `cluster="{env}-{projectID}"`.
- In prod, `control_plane_info=us-east-1-f04/aws-us-east-1/devtier-infra` injects `cluster="prod-us-east-1-f04"`.
- Routing-only parameters (`control_plane_info`, datasource aliases, `provider`, `region`) are not forwarded to upstream Loki.
- If the incoming LogQL already has a `cluster` matcher, it is replaced by the value derived from `control_plane_info`.
- `query_range` requires `query` to contain a LogQL stream selector such as `{}` or `{container="tidb-gateway"}`; selector-less metric expressions are rejected.
- `labels`, `label/:name/values`, and `series` inject `cluster` into every `match[]`; if no `match[]` is provided, the proxy creates `match[]={cluster="..."}`.
- Responses are raw Loki responses, not wrapped in a Clinic business envelope.

#### Example: query parent logs

```bash
curl -sS -G "${CLINIC_ENDPOINT}/data-proxy/serverless-parent-logs/api/v1/query_range" \
  -H "Authorization: Bearer ${CLINIC_API_KEY}" \
  --data-urlencode 'control_plane_info=us-east-1-f04/aws-us-east-1/devtier-infra' \
  --data-urlencode 'query={} != `operator_controller.go`' \
  --data-urlencode 'start=1782562920000000000' \
  --data-urlencode 'end=1782563220000000000' \
  --data-urlencode 'limit=20' \
  --data-urlencode 'direction=backward'
```

The upstream LogQL becomes:

```logql
{cluster="prod-us-east-1-f04"} != `operator_controller.go`
```

#### Example: discover labels

```bash
curl -sS -G "${CLINIC_ENDPOINT}/data-proxy/serverless-parent-logs/api/v1/labels" \
  -H "Authorization: Bearer ${CLINIC_API_KEY}" \
  --data-urlencode 'control_plane_info=us-east-1-f04/aws-us-east-1/devtier-infra' \
  --data-urlencode 'start=1782562920000000000' \
  --data-urlencode 'end=1782563220000000000'
```

Recent validation returned label names such as `app`, `cluster`, `cluster_env`, `cluster_id`, `container`, and `namespace`.

#### Example: discover label values

```bash
curl -sS -G "${CLINIC_ENDPOINT}/data-proxy/serverless-parent-logs/api/v1/label/container/values" \
  -H "Authorization: Bearer ${CLINIC_API_KEY}" \
  --data-urlencode 'control_plane_info=us-east-1-f04/aws-us-east-1/devtier-infra' \
  --data-urlencode 'start=1782562920000000000' \
  --data-urlencode 'end=1782563220000000000'
```

Useful labels to inspect include `container`, `namespace`, `pod`, `app`, and `service_name`.

#### Example: discover series

```bash
curl -sS -G "${CLINIC_ENDPOINT}/data-proxy/serverless-parent-logs/api/v1/series" \
  -H "Authorization: Bearer ${CLINIC_API_KEY}" \
  --data-urlencode 'control_plane_info=us-east-1-f04/aws-us-east-1/devtier-infra' \
  --data-urlencode 'match[]={container="tidb-gateway"}' \
  --data-urlencode 'start=1782562920000000000' \
  --data-urlencode 'end=1782563220000000000'
```

The upstream matcher becomes:

```logql
{container="tidb-gateway", cluster="prod-us-east-1-f04"}
```

#### Example: datasource override

```bash
curl -sS -G "${CLINIC_ENDPOINT}/data-proxy/serverless-parent-logs/api/v1/labels" \
  -H "Authorization: Bearer ${CLINIC_API_KEY}" \
  --data-urlencode 'control_plane_info=us-east-1-f04/aws-us-east-1/devtier-infra' \
  --data-urlencode 'datasource=Loki-aws-us-east-1' \
  --data-urlencode 'start=1782562920000000000' \
  --data-urlencode 'end=1782563220000000000'
```

Prefer omitting `datasource` unless debugging routing; `control_plane_info` normally provides enough information.

## Canonical SQL Query API

### Endpoint

```
POST /data-proxy/api/v1/orgs/{orgID}/clusters/{clusterID}/sql-queries
```

The path owns the cluster scope. The JSON body permits only these fields:

| Parameter | Type | Required | Description |
|-----------|------|----------|-------------|
| `sql` | string | Yes | SQL query. Use Data Proxy logical SQL accepted by the rewrite layer (TiDB parser compatible). MUST include date partition filter. Do not write Athena/Presto SQL as input |
| `timeout` | int | No | Query timeout in seconds; `0` uses the service default, maximum explicit value is 1800 |

Do not put `clusterId`, `cluster_id`, `orgId`, `options`, or routing/placement fields in the body. Unknown fields, duplicate keys, explicit `null`, trailing JSON, and bodies larger than 1 MiB are rejected.

Every physical table scan must have its own `date` predicate in its `WHERE` clause. This is checked independently in every UNION branch, CTE, and subquery. For a JOIN, qualify the partition predicate for each table alias; a predicate that appears only in `JOIN ... ON` is conservatively rejected.

### SQL Dialect and Query Strategy

**Slow Query / TopSQL / LogStats support by cloud vendor** (availability also depends on cluster tier / TiDB version enabling export):

This table describes warehouse-backed exports only. It does not describe BYOC, whose Slow Query and Top SQL data stay customer-side and use the canonical O11y proxy routes.

| Vendor | Slow Query / TopSQL | LogStats | Engine | Notes |
|--------|----------------------|----------|--------|------|
| AWS | Yes | Yes, Dedicated / Premium / NG-host where export exists | Athena | LogStats reads Delta table + cluster-scoped view |
| GCP | Yes | No | BigQuery | BigQuery SQL |
| Alibaba Cloud | Yes | No | AnalyticDB MySQL | Data Proxy logical SQL maps closely to MySQL |
| Azure | No | No | - | Not yet supported |

**Query strategy (recommended)**:
1. **Default**: Use Data Proxy logical SQL that the rewrite layer can parse. In practice this is TiDB parser compatible SQL over logical tables such as `clinic_data_proxy.slow_query_logs`, `clinic_data_proxy.topsql`, and `log_stats`.
2. Data Proxy rewrites logical table names, database names, identifiers, and supported functions before sending SQL to the backend execution engine.
3. **Do not write Athena/Presto SQL as input.** Athena is the AWS backend after rewrite, not the input dialect. Do not use `"db"."table"` just because the backend is Athena; do not use physical table names with cluster suffixes; do not use target-engine cast types such as `BIGINT` to compensate for Athena errors.
4. **Fallback**: Only when a function is explicitly documented as requiring vendor syntax, use that function expression carefully. Keep table names, identifiers, partition filters, and cast target types in the Data Proxy input dialect.

| Vendor | Engine after rewrite | When to use vendor dialect |
|--------|--------|----------------------------|
| AWS | Athena | Only explicitly documented function expressions; not identifiers, tables, or casts |
| GCP | BigQuery | Only explicitly documented function expressions; not identifiers, tables, or casts |
| Alibaba Cloud | AnalyticDB | Data Proxy logical SQL maps closely to MySQL; no fallback usually needed |

**Identifier conversion (already supported)**:
- Table/column names: `clinic_data_proxy.slow_query_logs`, `clinic_data_proxy.topsql`, `date`, `digest`, `sql_digest` — auto-converted
- Partition filter: `WHERE date = '...'` or `WHERE date = ...` — auto-converted. **Format differs by table**: `slow_query_logs` uses quoted `YYYYMMDD` (e.g. `'20260319'`), `topsql` uses quoted `YYYY-MM-DD` (e.g. `'2026-03-19'`), `log_stats` uses numeric `YYYYMMDD` in SQL examples (e.g. `20260618`) to avoid Athena varchar/bigint partition comparison mismatches
- No need to use `"db"."table"` for Athena or vendor-specific identifier syntax

**Casts and types**:
- For numeric casts in Data Proxy input, prefer parser-compatible forms such as `CAST(x AS SIGNED)` or `CAST(x AS UNSIGNED)`. The rewrite layer maps them to backend types such as Athena `BIGINT`.
- Do not write `CAST(x AS BIGINT)` just because the AWS backend is Athena.

**Dialect-specific functions**:
- Use vendor function syntax only when the function is explicitly required and known to pass through the Data Proxy rewrite layer.

### Example request (Data Proxy logical SQL, works for AWS/GCP/AliCloud)

```bash
# Data Proxy logical SQL - auto-converted to backend dialect
curl --fail-with-body -X POST \
  -H "Authorization: Bearer ${CLINIC_API_KEY}" \
  -H 'Content-Type: application/json' \
  "${CLINIC_ENDPOINT}/data-proxy/api/v1/orgs/${DBAAS_ORG_ID}/clusters/${CLUSTER_ID}/sql-queries" \
  --data '{
    "sql": "SELECT count(*) as cnt, date FROM clinic_data_proxy.slow_query_logs WHERE date IN ('\''20260201'\'','\''20260202'\'') GROUP BY date",
    "timeout": 60
  }'
```

### Response

| Field | Type | Description |
|-------|------|-------------|
| `columns` | string[] | Column names |
| `rows` | array \| null | Query result data. **Can be `null` when no rows match** — always use `rows or []` (not `rows` directly) |
| `metadata` | object | Query metadata |

**⚠️ CRITICAL: Response parsing pitfalls**

1. **`rows` can be `null`** (not just an empty array) when the query returns no results. Always guard: `rows = d.get("rows") or []` — do NOT rely on `d.get("rows", [])` because it only applies the default when the key is missing, not when the value is `null`.
2. **All row values are strings** regardless of the SQL column type (e.g., `"109215"` not `109215`, `"3.14"` not `3.14`). Cast explicitly before numeric formatting: `float(r[2])`, `int(r[3])`.

**Metadata Fields**:
| Field | Type | Description |
|-------|------|-------------|
| `rowCount` | int64 | Row count |
| `bytesScanned` | int64 | Data scanned |
| `executionTime` | string | Query time (e.g., "2.3s") |
| `queryId` | string | Query ID |
| `engine` | string | Query engine |
| `vendor` | string | Cloud vendor |
| `region` | string | Region |

### Example Response

Note: `rows` values are **always strings** (e.g. `"109215"` not `109215`). `rows` may also be `null`.

```json
{
  "columns": ["_col0", "date"],
  "rows": [["109215","20260201"],["92298","20260202"]],
  "metadata": {
    "rowCount": 2,
    "bytesScanned": 0,
    "executionTime": "3.4s",
    "queryId": "56035f2a-b953-4f4a-ba8f-a8354d8fbbad",
    "engine": "athena",
    "vendor": "aws",
    "region": "us-east-1"
  }
}
```

### Error Response

```json
{"error":"query failed: SQL validation failed: missing partition filter in WHERE clause for columns: [date]. Please add WHERE conditions like: WHERE \"date\" = '...' or WHERE \"date\" IN (...)"}
```

## Canonical same-cluster batch API

```text
POST /data-proxy/api/v1/orgs/{orgID}/clusters/{clusterID}/sql-query-batches
```

The body is an array of 1 to 100 query items. Every item allows only `sql` and optional `timeout`, with the same validation as the single-query API. All items use the org and cluster in the path; item-level cluster, routing options, and cross-cluster batches are not accepted. The request body limit is 4 MiB. If any item is invalid, the whole batch is rejected before any query runs. Statements are not supported in batch queries.

```bash
curl --fail-with-body -X POST \
  -H "Authorization: Bearer ${CLINIC_API_KEY}" \
  -H 'Content-Type: application/json' \
  "${CLINIC_ENDPOINT}/data-proxy/api/v1/orgs/${DBAAS_ORG_ID}/clusters/${CLUSTER_ID}/sql-query-batches" \
  --data '[
    {"sql":"SELECT COUNT(*) FROM clinic_data_proxy.slow_query_logs WHERE date = '\''20260721'\''","timeout":120},
    {"sql":"SELECT sql_digest, SUM(topsql_cpu_time_ms) FROM clinic_data_proxy.topsql WHERE date = '\''2026-07-21'\'' GROUP BY sql_digest LIMIT 20","timeout":120}
  ]'
```

A successful response is `{results,count}`. `results` preserves request order and each item has the same `{columns,rows,metadata}` contract as a single query.

## Canonical table schema API

Get table structure information (columns, partitions, location). 

### Endpoint

```
GET /data-proxy/api/v1/orgs/{orgID}/clusters/{clusterID}/table-schemas?tables=...
```

| Parameter | Type | Required | Description |
|-----------|------|----------|-------------|
| `tables` | string | Yes | One to 16 comma-separated standard logical tables from one data-source family |

`tables` is the only accepted query parameter. Do not pass `clusterId`, data source, vendor, region, or `options.*`. Statements schema is not exposed by this API.

```bash
curl --fail-with-body --get \
  -H "Authorization: Bearer ${CLINIC_API_KEY}" \
  --data-urlencode 'tables=slow_query_logs' \
  "${CLINIC_ENDPOINT}/data-proxy/api/v1/orgs/${DBAAS_ORG_ID}/clusters/${CLUSTER_ID}/table-schemas"
```

### Response Tables

| Field | Type | Description |
|-------|------|-------------|
| `schemas` | array | List of schema objects |
| `count` | integer | Number of returned schemas |
| `schemas[].database` | string | Database name |
| `schemas[].table` | string | Table name |
| `schemas[].columns` | array | Column definitions |
| `schemas[].columns[].name` | string | Column name |
| `schemas[].columns[].type` | string | Column data type |
| `schemas[].columns[].comment` | string | Column comment (optional, may be omitted) |
| `schemas[].partitions` | array | Partition definitions |
| `schemas[].partitions[].name` | string | Partition column name |
| `schemas[].partitions[].type` | string | Partition column type |
| `schemas[].dataSource` | string | Data source URI |
| `schemas[].location` | string | Table storage location |

### Example Response

```json
{
  "schemas": [
    {
      "database": "clinic_data_proxy",
      "table": "slow_query_logs",
      "columns": [
        {
          "name": "time",
          "type": "double"
        },
        {
          "name": "digest",
          "type": "string"
        }
      ],
      "partitions": [
        {
          "name": "date",
          "type": "string"
        }
      ],
      "dataSource": "<server-resolved storage URL>",
      "location": ""
    }
  ],
  "count": 1
}
```

## Service capabilities API

```text
GET /data-proxy/api/v1/capabilities
```

This is a service-level resource, so it intentionally has no org or cluster path parameters. It returns the service, version, and supported-vendor information exposed by Data Proxy.

```bash
curl --fail-with-body \
  -H "Authorization: Bearer ${CLINIC_API_KEY}" \
  "${CLINIC_ENDPOINT}/data-proxy/api/v1/capabilities"
```

## Persisted SQL Statements (non-BYOC)

Query persisted SQL Statements for supported Premium variants through the canonical SQL query API and the distinct logical table `clinic_data_proxy.statements`. This is flexible SQL access to persisted summary data. It is not the BYOC live Statements contract.

Requirements:

- Use the complete, lowercase table name `clinic_data_proxy.statements`.
- Include `date = 'YYYY-MM-DD'` in the SQL. The cluster scope comes from the canonical path.
- Do not mix `statements` with other Data Proxy logical tables in one query.
- Do not pass `options`; provider, region, and the `persisted_statements_summary` data source are resolved authoritatively by the service.
- Statements are not supported by the batch or schema APIs.
- This data source applies to Premium, Premium Reserved, Premium Resource Pool, and Premium Reserved Resource Pool clusters. A cluster has data only when persisted Statements is enabled.
- BYOC must use the separate canonical `GET .../statements` routes in `../byoc-diagnostics/SUBSKILL.md`; do not create a persisted data source for it.

```bash
curl --fail-with-body -X POST \
  -H "Authorization: Bearer ${CLINIC_API_KEY}" \
  -H 'Content-Type: application/json' \
  "${CLINIC_ENDPOINT}/data-proxy/api/v1/orgs/${DBAAS_ORG_ID}/clusters/${CLUSTER_ID}/sql-queries" \
  --data "{
    \"timeout\": 300,
    \"sql\": \"SELECT * FROM clinic_data_proxy.statements WHERE date = '2026-07-21' LIMIT 20\"
  }"
```

| Provider | Engine | Status |
|----------|--------|--------|
| AWS | Athena + Delta | Available; production positive-data query and authoritative routing validated |
| GCP | BigQuery + Delta | Implemented; no production Premium sample was available, so positive E2E is not exercised |
| Azure | Synapse + Delta | Implemented; available production sample had no Statements data source, so positive E2E is not exercised |
| Alibaba Cloud | — | Unsupported; the current MaxCompute/OSS path cannot safely read the Delta transaction log |

If the resolved storage root is empty (or contains only directory markers), the response is `200` with `columns=[]`, `rows=[]`, and `metadata.rowCount=0`. A missing Statements data-source URL returns `400` with `DATASOURCE_NOT_CONFIGURED`. Storage permission, timeout, network, and invalid Delta-log conditions remain errors.

The persisted SQL contract does not replace the live Statement Summary operations. Canonical Data Proxy now exposes those operations for logical and physical BYOC clusters as `available-fields`, `statement-types`, list, `plans`, and `plan-detail`, backed directly by customer-side O11y.

## Legacy Data Proxy compatibility

Canonical APIs are additive, but new skill calls must use them. The following endpoints remain only for existing callers:

| Capability | Legacy path | Compatibility boundary |
|------------|-------------|------------------------|
| SQL query | `POST /data-proxy/query` | Body may contain `clusterId`/`cluster_id`, timeout, and existing routing options |
| Batch SQL query | `POST /data-proxy/batch-query` | May use item-level clusters, caller routing options, and cross-cluster batches |
| Schema | `GET /data-proxy/schema` | May use `clusterId`, data-source/vendor/region, and existing aliases |
| Service info | `GET /data-proxy/info` | Existing response is preserved; use canonical `/capabilities` for new calls |

Default Python and shell helpers use canonical endpoints and resolve canonical org scope automatically. Use an explicitly named legacy helper only when maintaining a caller that requires one of the legacy-only behaviors above.

## LogStats table schema

Table: `log_stats`

Aggregated TiDB / TiKV / PD / TiFlash log message counts from the O11Y LogStats Delta export. This table is currently supported for **AWS Dedicated / Premium / NG-host** clusters when `cluster-dataurls` returns `logStatsS3Url`.

LogStats is exposed through a cluster-scoped view such as `log_stats_<clusterId>` behind the logical table name `log_stats`; use `log_stats` in SQL and let Data Proxy rewrite it.

### Key fields

| Field | Type | Description |
|-------|------|-------------|
| `date` | string / partition | Log date. Use numeric `YYYYMMDD` in SQL examples, for example `WHERE date = 20260618` |
| `org` | string / partition | TiDB Cloud tenant / org id from LogStats export |
| `cluster` | string / partition | TiDB cluster id |
| `hour` | int | Hour bucket |
| `project` | string | Project or source label from export |
| `level` | string | Log level, for example `INFO`, `WARN`, `ERROR` |
| `component` | string | Component, for example `tidb`, `tikv`, `pd`, `backup` |
| `file` | string | Source file |
| `line` | int | Source line |
| `count` | bigint | Aggregated message count |
| `msg_digest` | string | Message digest / fingerprint |
| `sample` | string | Representative message sample |

### LogStats examples

Use `clinic_log_stats_*` shell helpers or the equivalent Python methods where possible. They generate the safe numeric date predicate.

#### Schema

```bash
clinic_log_stats_schema "10989049060142230334"
```

#### Summary by level and component

```bash
clinic_log_stats_summary "10989049060142230334" "20260618" 50
```

Equivalent SQL:

```sql
SELECT
  level,
  component,
  SUM(count) AS total_count
FROM log_stats
WHERE date = 20260618
GROUP BY level, component
ORDER BY total_count DESC
LIMIT 50
```

#### Top repeated messages

```bash
clinic_log_stats_top "10989049060142230334" "20260618" 50
```

Equivalent SQL:

```sql
SELECT
  level,
  component,
  file,
  line,
  msg_digest,
  sample,
  SUM(count) AS total_count
FROM log_stats
WHERE date = 20260618
GROUP BY level, component, file, line, msg_digest, sample
ORDER BY total_count DESC
LIMIT 50
```

#### Search by message text / digest / file / component

```bash
clinic_log_stats_search "10989049060142230334" "20260618" "region is unavailable" 50
```

Equivalent SQL:

```sql
SELECT
  level,
  component,
  file,
  line,
  msg_digest,
  sample,
  SUM(count) AS total_count
FROM log_stats
WHERE date = 20260618
  AND (
    sample LIKE '%region is unavailable%'
    OR msg_digest LIKE '%region is unavailable%'
    OR file LIKE '%region is unavailable%'
    OR component LIKE '%region is unavailable%'
  )
GROUP BY level, component, file, line, msg_digest, sample
ORDER BY total_count DESC
LIMIT 50
```

**Important**:

- Do not run `MSCK REPAIR TABLE` for LogStats. The source is a Delta table; deleted Delta files can otherwise still be counted incorrectly.
- For newly added partitions, Data Proxy relies on the Delta table metadata and the cluster-scoped view, not manual partition repair.
- LogStats `rows` can be `null` when no messages match; helpers normalize this to `[]` only in Python.

## Slow Query Table Schema

Table: `slow_query_logs`

Records SQL statements with execution time exceeding threshold (default 300ms).

### Key Fields

| Field | Type | Description |
|-------|------|-------------|
| `id` | bigint | Unique ID |
| `time` | double | Execution timestamp (Unix) |
| `digest` | string | Normalized SQL hash |
| `instance` | string | TiDB instance address |
| `query_time` | double | Total execution time |
| `parse_time` | double | Parse time |
| `compile_time` | double | Compile time |
| `cop_time` | double | Coprocessor total time |
| `process_time` | double | KV processing time |
| `wait_time` | double | KV wait time |
| `mem_max` | bigint | Max memory usage |
| `disk_max` | bigint | Max disk usage |
| `total_keys` | string | Total keys scanned |
| `process_keys` | string | Keys processed |
| `result_rows` | bigint | Rows returned |
| `db` | string | Database name |
| `index_names` | string | Indexes used |
| `query` | string | Original SQL |
| `plan` | string | Execution plan (⚠️ usually contains only `"default"` — do NOT rely on this field) |
| `decoded_plan` | string | **Decoded execution plan (JSON)** — contains the full operator tree with `actRows`, `estRows`, `copExecInfo`, access methods, etc. **Always use this field instead of `plan`** for execution plan analysis |
| `stats` | string | Statistics health info for tables involved |
| `backoff_time` | double | Total backoff time (retry delay) |
| `backoff_types` | string | Types of backoff encountered |
| `warnings` | string | Optimizer warnings |
| `date` | string | Partition field (log date) |

## TopSQL table schema

Table: `topsql`

Aggregated **TopSQL** samples (CPU, keys, logical IO, network, stmt counts/durations) per reporting interval, keyed by SQL/plan digest and optional table/region dimensions. Use the canonical **schema API** (`GET /data-proxy/api/v1/orgs/{orgID}/clusters/{clusterID}/table-schemas?tables=topsql`) for the exact column list on a cluster.

### Key fields

| Field | Type | Description |
|-------|------|-------------|
| `date` | string | **Partition field** — format `YYYY-MM-DD` (e.g. `'2026-04-01'`). **Different from slow_query_logs** which uses `YYYYMMDD`. Always filter `WHERE date = '...'` or `IN (...)` |
| `sql_digest` | string | SQL fingerprint |
| `plan_digest` | string | Plan fingerprint |
| `normalized_sql` | string | Normalized SQL text (sample) |
| `normalized_plan` | string | Normalized plan text |
| `encoded_normalized_plan` | string | Encoded plan |
| `component` | varchar(4) | Reporting component (e.g. TiDB / TiKV side of TopSQL) |
| `instance` | string | Instance identifier |
| `keyspace` | string | Keyspace name |
| `region_id` | string | Region id (when broken down by region) |
| `table` | string | Table name (when broken down by table) |
| `table_id` | bigint | Table id |
| `tag_label` | string | Additional label dimension |
| `timestamps` | bigint | Bucket / report timestamp (backend-defined) |
| `topsql_cpu_time_ms` | int | CPU time (ms) in the bucket |
| `topsql_stmt_exec_count` | bigint | Statement execution count in the bucket |
| `topsql_stmt_duration_sum_ns` | bigint | Sum of stmt durations (ns) |
| `topsql_stmt_duration_count` | bigint | Count of duration samples (for averages) |
| `topsql_read_keys` | bigint | Read keys |
| `topsql_write_keys` | bigint | Write keys |
| `topsql_logical_read_bytes` | bigint | Logical read bytes |
| `topsql_logical_write_bytes` | bigint | Logical write bytes |
| `topsql_network_in_bytes` | bigint | Network in |
| `topsql_network_out_bytes` | bigint | Network out |

### Example request (TopSQL)

```bash
curl --fail-with-body -X POST \
  -H "Authorization: Bearer ${CLINIC_API_KEY}" \
  -H 'Content-Type: application/json' \
  "${CLINIC_ENDPOINT}/data-proxy/api/v1/orgs/${DBAAS_ORG_ID}/clusters/${CLUSTER_ID}/sql-queries" \
  --data '{
    "sql": "SELECT sql_digest, any_value(normalized_sql) AS sample_sql, SUM(topsql_cpu_time_ms) AS cpu_ms, SUM(topsql_stmt_exec_count) AS exec_cnt FROM clinic_data_proxy.topsql WHERE date = '\''2026-04-01'\'' GROUP BY sql_digest ORDER BY cpu_ms DESC LIMIT 20",
    "timeout": 60
  }'
```

## TopSQL analysis examples

SQL below uses Data Proxy logical SQL. Identifiers are auto-converted; do not use Athena/Presto SQL as input.

### Scenario 1: Top SQL by cumulative CPU

```sql
SELECT
  sql_digest,
  any_value(normalized_sql) AS sample_sql,
  SUM(topsql_cpu_time_ms) AS total_cpu_ms
FROM topsql
WHERE date = '2026-04-01'
GROUP BY sql_digest
ORDER BY total_cpu_ms DESC
LIMIT 50
```

### Scenario 2: Top SQL by execution count

```sql
SELECT
  sql_digest,
  any_value(normalized_sql) AS sample_sql,
  SUM(topsql_stmt_exec_count) AS total_exec
FROM topsql
WHERE date = '2026-04-01'
GROUP BY sql_digest
ORDER BY total_exec DESC
LIMIT 50
```

### Scenario 3: Average stmt duration from sum/count

```sql
SELECT
  sql_digest,
  any_value(normalized_sql) AS sample_sql,
  SUM(topsql_stmt_duration_sum_ns) AS dur_sum_ns,
  SUM(topsql_stmt_duration_count) AS dur_cnt,
  SUM(topsql_stmt_duration_sum_ns) / NULLIF(SUM(topsql_stmt_duration_count), 0) AS avg_dur_ns
FROM topsql
WHERE date = '2026-04-01'
GROUP BY sql_digest
HAVING SUM(topsql_stmt_duration_count) > 0
ORDER BY avg_dur_ns DESC
LIMIT 50
```

### Scenario 4: Break down by TiDB vs TiKV (`component`)

```sql
SELECT
  component,
  SUM(topsql_cpu_time_ms) AS cpu_ms,
  SUM(topsql_stmt_exec_count) AS exec_cnt
FROM topsql
WHERE date = '2026-04-01'
GROUP BY component
ORDER BY cpu_ms DESC
```

### Scenario 5: Heavy logical IO by SQL

```sql
SELECT
  sql_digest,
  any_value(normalized_sql) AS sample_sql,
  SUM(topsql_logical_read_bytes) AS read_b,
  SUM(topsql_logical_write_bytes) AS write_b
FROM topsql
WHERE date = '2026-04-01'
GROUP BY sql_digest
ORDER BY read_b + write_b DESC
LIMIT 50
```

### Scenario 6: Instance-level CPU drill-down on a specific TiKV hotspot node

**When to use**: After identifying a TiKV hotspot instance via resource metrics (CPU/Memory/IOPS), drill into TopSQL on that specific instance to find which SQL is consuming the most CPU **on that node**.

```sql
SELECT
  sql_digest,
  any_value(normalized_sql) AS sample_sql,
  SUM(topsql_cpu_time_ms) AS total_cpu_ms,
  ROUND(SUM(topsql_cpu_time_ms) * 100.0 / SUM(SUM(topsql_cpu_time_ms)) OVER (), 2) AS cpu_pct
FROM topsql
WHERE date = '2026-04-01'
  AND component = 'tikv'
  AND instance LIKE '%tikv-19%'
GROUP BY sql_digest
ORDER BY total_cpu_ms DESC
LIMIT 20
```

**CRITICAL**: Use `component = 'tikv'` (not `'tidb'`) when diagnosing TiKV CPU hotspots. TiDB-side TopSQL shows SQL execution from TiDB's perspective; TiKV-side TopSQL shows actual CPU consumption on TiKV nodes, which is what matters for TiKV hotspot diagnosis.

### Scenario 7: Time-windowed TopSQL for a specific incident period

**When to use**: The incident has a known time window. Filter TopSQL by timestamp range to avoid noise from outside the incident period.

```sql
SELECT
  sql_digest,
  any_value(normalized_sql) AS sample_sql,
  SUM(topsql_cpu_time_ms) AS total_cpu_ms,
  ROUND(SUM(topsql_cpu_time_ms) * 100.0 / SUM(SUM(topsql_cpu_time_ms)) OVER (), 2) AS cpu_pct
FROM topsql
WHERE date = '2026-04-01'
  AND component = 'tikv'
  AND timestamps BETWEEN 1743847200 AND 1743854400
GROUP BY sql_digest
ORDER BY total_cpu_ms DESC
LIMIT 20
```

### Scenario 8: Compare SQL CPU contribution across TiKV instances

**When to use**: Check whether a SQL's CPU load is concentrated on one TiKV or spread across all.

```sql
SELECT
  instance,
  sql_digest,
  any_value(normalized_sql) AS sample_sql,
  SUM(topsql_cpu_time_ms) AS total_cpu_ms
FROM topsql
WHERE date = '2026-04-01'
  AND component = 'tikv'
  AND sql_digest = '<target_digest>'
GROUP BY instance, sql_digest
ORDER BY total_cpu_ms DESC
LIMIT 20
```

## TopSQL: Distinguishing "Slow" vs "Hot" (CRITICAL)

**P99 latency high ≠ CPU hotspot.** These are two different problems requiring different evidence:

| Dimension | "Slow" (high latency per request) | "Hot" (high total CPU consumption) |
|-----------|-----------------------------------|------------------------------------|
| Metric | P99 / max latency per request | Total CPU time = sum(rate(handle_seconds_sum)) or TopSQL `topsql_cpu_time_ms` |
| Example | ANALYZE: P99 = 10.4s, but only 1.48s total CPU | SELECT join: P99 = 200ms, but 16.39s total CPU (high frequency × moderate latency) |
| Diagnosis | One request is slow, but may not consume much CPU overall | The SQL is the actual CPU consumer causing the hotspot |
| Tool | Slow query logs, coprocessor P99 | **TopSQL** (sum of `topsql_cpu_time_ms`), sum(rate(handle_seconds_sum)) by type |

**Common mistake**: Seeing `tikv_coprocessor_request_handle_seconds` P99 = 10s for `analyze_full_sampling` and concluding ANALYZE causes the CPU hotspot. P99 only tells you each ANALYZE request is slow (expected — it's a full table scan). To determine what's actually consuming CPU, you must check **total CPU time** via TopSQL or `sum(rate(handle_seconds_sum))`.

**Notes**:

- Prefer the canonical **`GET /data-proxy/api/v1/orgs/{orgID}/clusters/{clusterID}/table-schemas?tables=topsql`** if column names or types differ slightly by pipeline version.
- If a day returns **no rows**, the export may be empty for that date or TopSQL may be off for that cluster; cross-check Clinic UI / cluster version.

## Slow Query Analysis Examples

SQL below uses Data Proxy logical SQL. Identifiers are auto-converted; do not use Athena/Presto SQL as input.

### Scenario 1: Overall Slow Query Statistics

```sql
SELECT
  COUNT(*) AS total_queries,
  COUNT(DISTINCT digest) AS unique_sql,
  AVG(query_time) AS avg_query_time,
  MAX(query_time) AS max_query_time
FROM slow_query_logs
WHERE date='20260301'
```

### Scenario 2: Find Slowest SQL

```sql
SELECT
  digest,
  any_value(query) AS sample_sql,
  COUNT(*) AS exec_count,
  MAX(query_time) AS max_time
FROM slow_query_logs
WHERE date='20260301'
GROUP BY digest
ORDER BY max_time DESC
LIMIT 20
```

### Scenario 3: Find High Memory SQL

```sql
SELECT
  digest,
  any_value(query) AS sample_sql,
  MAX(mem_max) AS max_mem,
  COUNT(*) AS exec_count
FROM slow_query_logs
WHERE date='20260301'
AND mem_max > 5368709120  -- 5GB
GROUP BY digest
ORDER BY max_mem DESC
LIMIT 20
```

**Recommended thresholds**:
| Threshold | Value | Description |
|-----------|-------|-------------|
| High Memory | > 5GB | Risk of OOM |
| Large Scan | > 10M keys | Potential full table scan |

### Scenario 4: Find Large Scan SQL

```sql
SELECT
  digest,
  any_value(query) AS sample_sql,
  SUM(total_keys) AS scanned_rows
FROM slow_query_logs
WHERE date='20260301'
GROUP BY digest
ORDER BY scanned_rows DESC
LIMIT 20
```

**Analysis focus**:
| Indicator | Issue |
|-----------|-------|
| High total_keys, low process_keys | Full table scan |
| No index_names | Missing index |

### Scenario 5: Execution Plan & Statistics Health Analysis (CRITICAL for root cause)

**When to use**: After identifying a problematic SQL digest, retrieve the full execution plan and statistics health to diagnose why it's slow.

```sql
SELECT
  digest,
  any_value(query) AS sample_sql,
  any_value(decoded_plan) AS exec_plan,
  any_value(stats) AS stats_info,
  any_value(warnings) AS optimizer_warnings,
  any_value(backoff_types) AS backoff,
  MAX(backoff_time) AS max_backoff_time,
  MAX(query_time) AS max_time,
  MAX(process_time) AS max_process_time,
  MAX(wait_time) AS max_wait_time,
  MAX(total_keys) AS max_total_keys,
  MAX(process_keys) AS max_process_keys,
  MAX(result_rows) AS max_result_rows,
  any_value(index_names) AS indexes_used
FROM slow_query_logs
WHERE date = '20260301'
  AND digest = '<target_digest>'
ORDER BY max_time DESC
LIMIT 5
```

**Key fields in `decoded_plan` to examine**:
| Field | What to check |
|-------|--------------|
| `actRows` vs `estRows` | If `actRows >> estRows` (e.g., 10x+), statistics are severely stale — run ANALYZE TABLE |
| Access method | `IndexFullScan` on a large table = likely wrong plan; should be `IndexRangeScan` or `TableRangeScan` |
| `copExecInfo` | Execution details at TiKV coprocessor level |
| Operator tree | Follow "first child first" to find the bottleneck operator |

**⚠️ Do NOT use the `plan` column** — it typically contains only `"default"` and is not useful. Always use `decoded_plan` for execution plan analysis.

### Scenario 6: Find High Frequency SQL

```sql
SELECT
  digest,
  any_value(query) AS sample_sql,
  COUNT(*) AS exec_count
FROM slow_query_logs
WHERE date='20260301'
GROUP BY digest
ORDER BY exec_count DESC
LIMIT 20
```

---

# Data Proxy SQL Best Practices

## 1. Use Data Proxy Logical SQL; Identifiers Auto-Converted

Data Proxy **auto-converts identifiers** (table/column names) to the backend engine. Write SQL accepted by the Data Proxy rewrite layer:
- Table ref: `clinic_data_proxy.slow_query_logs`, `clinic_data_proxy.topsql`, or `database.table`
- Partition filter: `WHERE date = '...'` or `WHERE date IN (...)` on each partitioned table

**Do not write Athena/Presto SQL as input.** Athena is only the AWS backend after rewrite. Use vendor syntax only for explicitly documented function expressions.

## 2. Always Use Partition Filter

The underlying data tables use `date` as the partition field (`slow_query_logs`, `topsql`, etc.).

**Format differs by table**:
```sql
-- slow_query_logs: YYYYMMDD
WHERE date = '20260301'
WHERE date IN ('20260301', '20260302')

-- topsql: YYYY-MM-DD
WHERE date = '2026-03-01'
WHERE date IN ('2026-03-01', '2026-03-02')

-- log_stats: YYYYMMDD, use numeric literal in examples
WHERE date = 20260618
```

## 3. Select Only Required Fields

**Recommended**:
```sql
SELECT digest, query_time
```

**Avoid**:
```sql
SELECT *
```

## 4. Control Result Size

Always use `LIMIT`:
```sql
LIMIT 10000
```

## 5. Use Aggregation

**Recommended**:
```sql
GROUP BY digest
```

Instead of returning raw records.

## 6. Set Appropriate Timeout

API supports `timeout` parameter:
```json
{"timeout": 60}
```

For complex queries:
```json
{"timeout": 120}
```