# O11Y OLAP examples

Always use **bash**. Assume `olap_auth` already ran.

## Staging log-stats top N

```bash
bash <<'EOF'
source skills/o11y-olap/scripts/o11y-olap.sh
olap_auth
LOGICAL=us-west-2-staging
DATE=$(olap_scope_first "$LOGICAL" '{"dataset":"log-stats","field":"date","scope":{}}')
# prefer latest:
DATE=$(olap_scope_list "$LOGICAL" '{"dataset":"log-stats","field":"date","scope":{}}' | jq -r '.data.values[-1]')
ORG=$(olap_scope_first "$LOGICAL" "$(jq -n --arg d "$DATE" '{dataset:"log-stats",field:"org",scope:{date:$d}}')")
CLUSTER=$(olap_scope_first "$LOGICAL" "$(jq -n --arg d "$DATE" --arg o "$ORG" '{dataset:"log-stats",field:"cluster",scope:{date:$d,org:$o}}')")
BODY=$(jq -n --arg d "$DATE" --arg o "$ORG" --arg c "$CLUSTER" '{
  dataset:"log-stats",
  scope:{date:$d, org:$o, cluster:$c},
  sql:"SELECT component, level, CAST(count AS bigint) AS cnt, substr(sample,1,120) AS sample FROM log_stats ORDER BY cnt DESC LIMIT 10"
}')
olap_query "$LOGICAL" "$BODY"
EOF
```

## Resolve cluster then TopSQL

```bash
CLUSTER_JSON=$(olap_find_cluster 10004313793950238553 aws)
LOGICAL=$(echo "$CLUSTER_JSON" | jq -r '.logical_region')
# Note: for staging buckets use the staging logical host even if GS region is bare us-west-2
```

## TopSQL: CPU top 100 (aggregated)

Mirrors topsql v2 `order_by=cpu&top=100` intent. **Do not** `ORDER BY topsql_cpu_time_ms` on raw rows.

```bash
bash <<'EOF'
source skills/o11y-olap/scripts/o11y-olap.sh
olap_auth
LOGICAL=us-west-2-staging
# After scope/list → org, cluster, component, instance, date:
ORG=...; CLUSTER=...; COMPONENT=tidb; INSTANCE=...; DATE=2026-05-28
BODY=$(jq -n --arg o "$ORG" --arg c "$CLUSTER" --arg p "$COMPONENT" \
  --arg i "$INSTANCE" --arg d "$DATE" \
  --argjson tr "$(olap_day_time_range_json "$DATE")" '{
  dataset:"topsql",
  scope:{org:$o, cluster:$c, component:$p, instance:$i, date:$d},
  time_range:$tr,
  sql:"SELECT sql_digest, SUM(topsql_cpu_time_ms) AS cpu_ms, SUM(topsql_stmt_exec_count) AS exec_count FROM topsql WHERE sql_digest IS NOT NULL GROUP BY sql_digest ORDER BY cpu_ms DESC LIMIT 100"
}')
olap_query "$LOGICAL" "$BODY"
EOF
```

No automatic `sql_text` on metrics rows — follow up with `topsql-sql-meta` / `topsql-plan-meta`.

## TopSQL: resolve sql_text / plan_text

```bash
bash <<'EOF'
source skills/o11y-olap/scripts/o11y-olap.sh
olap_auth
LOGICAL=us-west-2-staging
ORG=...; CLUSTER=...; DATE=2026-05-28
# DIGESTS / PLANS from a prior topsql or topru top-N result
BODY=$(jq -n --arg o "$ORG" --arg c "$CLUSTER" --arg d "$DATE" '{
  dataset:"topsql-sql-meta",
  scope:{org:$o, cluster:$c, date:$d},
  sql:"SELECT sql_digest, normalized_sql AS sql_text FROM topsql_sql_meta WHERE sql_digest IN ('\''DIGEST1'\'','\''DIGEST2'\'')"
}')
olap_query "$LOGICAL" "$BODY"

BODY=$(jq -n --arg o "$ORG" --arg c "$CLUSTER" --arg d "$DATE" '{
  dataset:"topsql-plan-meta",
  scope:{org:$o, cluster:$c, date:$d},
  sql:"SELECT plan_digest, normalized_plan AS plan_text FROM topsql_plan_meta WHERE plan_digest IN ('\''PLAN1'\'')"
}')
olap_query "$LOGICAL" "$BODY"
EOF
```

## TopRU: RU top 100

Path mirrors `o11ydiagnosis/pkg/topru` (`component=topru/instance=default`). Aggregate — do not rank raw rows.

```bash
bash <<'EOF'
source skills/o11y-olap/scripts/o11y-olap.sh
olap_auth
LOGICAL=us-west-2-staging
ORG=...; CLUSTER=...; DATE=2026-05-28
BODY=$(jq -n --arg o "$ORG" --arg c "$CLUSTER" --arg d "$DATE" \
  --argjson tr "$(olap_day_time_range_json "$DATE")" '{
  dataset:"topru",
  scope:{org:$o, cluster:$c, date:$d},
  time_range:$tr,
  sql:"SELECT sql_digest, plan_digest, SUM(topru_total_ru) AS total_ru, SUM(topru_exec_count) AS exec_count, SUM(topru_exec_duration) AS duration_ns FROM topru WHERE COALESCE(sql_digest,'\'\'') <> '\'\'' GROUP BY sql_digest, plan_digest ORDER BY total_ru DESC LIMIT 100"
}')
olap_query "$LOGICAL" "$BODY"
EOF
```

## Slowlogs: all clusters under one tenant

`scope/list` returns **o11y UUIDs** (S3 path segments). Pass them as a JSON array — server accepts UUID arrays as-is (and still maps applicationIDs if present).

**Date:** file column is **`yyyyMMdd`** (same as slowquery). Prefer filtering with `time_range` on `time`; if you set `scope.date`, pass `yyyyMMdd` or `yyyy-MM-dd` (server converts). Do not write `WHERE date = '2026-07-20'` in SQL.

```bash
bash <<'EOF'
source skills/o11y-olap/scripts/o11y-olap.sh
olap_auth
LOGICAL=us-west-2-staging
ORG=1372813089188151296
CLUSTERS=$(olap_scope_list "$LOGICAL" "$(jq -n --arg o "$ORG" \
  '{dataset:"slowlogs",field:"cluster",scope:{org:$o}}')" | jq -c '.data.values')
# List available date partitions under one cluster (pick first UUID):
C0=$(echo "$CLUSTERS" | jq -r '.[0]')
DATES=$(olap_scope_list "$LOGICAL" "$(jq -n --arg o "$ORG" --arg c "$C0" \
  '{dataset:"slowlogs",field:"date",scope:{org:$o,cluster:$c}}')" | jq -c '.data.values')
DAY=$(echo "$DATES" | jq -r '.[0]')
BODY=$(jq -n --arg o "$ORG" --argjson c "$CLUSTERS" --arg d "$DAY" \
  --argjson tr "$(olap_day_time_range_json "$(olap_normalize_date "$DAY" 2>/dev/null || echo "$DAY")")" '{
  dataset:"slowlogs",
  scope:{org:$o, cluster:$c, date:$d},
  time_range:$tr,
  sql:"SELECT time, query_time, substr(query,1,120) AS query FROM slowlogs ORDER BY query_time DESC LIMIT 50"
}')
olap_query "$LOGICAL" "$BODY"
EOF
```

## Smoke all datasets

```bash
bash skills/o11y-olap/scripts/smoke-test.sh us-west-2-staging
```