# run
Local:
 PYTHONPATH=src APP_ENV=dev LOG_LEVEL=INFO ./.venv/bin/uvicorn slowquery_proxy.main:app --host 127.0.0.1 --port 8088

 PYTHONPATH=src APP_ENV=prod LOG_LEVEL=INFO ./.venv/bin/uvicorn slowquery_proxy.main:app --host 127.0.0.1 --port 8088
 


 EC2:
 
 已激活venv启动
 PYTHONPATH=src APP_ENV=prod LOG_LEVEL=INFO python3 -m uvicorn slowquery_proxy.main:app --host 0.0.0.0 --port 8088
 PYTHONPATH=src APP_ENV=prod LOG_LEVEL=INFO python3 -m uvicorn slowquery_proxy.main:app --host 127.0.0.1 --port 8088


未激活 venv 的启动方式（直接用 venv 的 uvicorn）
 PYTHONPATH=src APP_ENV=prod LOG_LEVEL=INFO ./.venv/bin/uvicorn slowquery_proxy.main:app --host 127.0.0.1 --port 8088
 


# Test
curl -X POST http://127.0.0.1:8000/query -H "Content-Type: application/json" -d "{\"sql\":\"SELECT COALESCE(digest, '') AS digest, COALESCE(ROUND(AVG(mem_max), 0), 0) AS avg_mem, COALESCE(ROUND(MAX(mem_max), 0), 0) AS max_mem, COALESCE(ROUND(AVG(query_time), 2), 0) AS avg_query_time, COALESCE(ROUND(MAX(query_time), 2), 0) AS max_query_time, COUNT(*) AS total_count FROM slowlogs WHERE \\\"time\\\" BETWEEN 0 AND 32503680000 AND digest IN ('18576e217f5f93fa2a934f84290447caa4b783cab493503c4ad71c912ea16930','884949e194fc6d5293e9c78dbaa876afeec03ffeb31f44e5967fec92bffe4d15','22c3101adce33ad3e0d83349120fc23881474e23cbdaade314e82bfd6f506f68') AND lower(query) LIKE 'select%' AND lower(query) NOT LIKE 'analyze%' AND lower(query) NOT LIKE 'create%' AND lower(query) NOT LIKE 'alter%' AND lower(query) NOT LIKE 'drop%' AND lower(query) NOT LIKE 'truncate%' AND lower(query) NOT LIKE 'rename%' AND lower(query) NOT LIKE '%force index%' AND NOT (lower(query) LIKE '%read_from_storage(tiflash%' AND lower(query) NOT LIKE '%/*+ read_from_storage(tiflash%') GROUP BY digest ORDER BY total_count DESC\",\"vendor\":\"local\",\"sources\":[{\"name\":\"slowlogs\",\"type\":\"parquet\",\"uri\":\"/Users/wlt/Documents/GolandProjects/clinic/health_check/slow_query_proxy/1.parquet\"}]}"


curl -s -X POST http://127.0.0.1:8088/query \
-H 'Content-Type: application/json' \
-d '{
  "sql": "SELECT query, \"time\" FROM delta_scan(\"s3://o11y-prod-shared-us-east-1/deltalake/1372813089209061633/ed869d81-91c5-4a87-a28d-20795c738d3f/slowlogs\") WHERE \"time\" = 1749826966.899754",
  "vendor": "aws",
  "region": "us-east-1",
  "sources": [
    {
      "name": "slowlogs",
      "type": "delta",
      "uri": "s3://o11y-prod-shared-us-east-1/deltalake/1372813089209061633/ed869d81-91c5-4a87-a28d-20795c738d3f/slowlogs"
    }
  ]
}'


Respone：
{"rows":[{"digest":"18576e217f5f93fa2a934f84290447caa4b783cab493503c4ad71c912ea16930","avg_mem":17265.0,"max_mem":104328,"avg_query_time":2.39,"max_query_time":3.11,"total_count":479},{"digest":"884949e194fc6d5293e9c78dbaa876afeec03ffeb31f44e5967fec92bffe4d15","avg_mem":34251150.0,"max_mem":439441903,"avg_query_time":1.47,"max_query_time":5.42,"total_count":159},{"digest":"22c3101adce33ad3e0d83349120fc23881474e23cbdaade314e82bfd6f506f68","avg_mem":28926542.0,"max_mem":98131825,"avg_query_time":1.94,"max_query_time":6.11,"total_count":127}],"meta":{"columns":["digest","avg_mem","max_mem","avg_query_time","max_query_time","total_count"],"row_count":3,"elapsed_ms":23}}

Error Response：
```
{
  "detail": {
    "code": "internal_error",
    "message": "<error message>",
    "trace_id": "<req_id>"
  }
}
```
{"detail":[{"type":"json_invalid","loc":["body",200],"msg":"JSON decode error","input":{},"ctx":{"error":"Expecting ',' delimiter"}}]}




# Slowquery Proxy (Python + DuckDB)

轻量级 HTTP 查询代理：接收 SQL 与数据源信息，在 DuckDB 内存库中绑定 Delta/Parquet 数据后执行查询，返回 JSON。支持按环境切换：`APP_ENV=dev` 使用本地 Parquet；`APP_ENV=prod` 查询线上 S3 Delta（服务端认证封装）。

## 特性
- 仅通过 SQL 查询，轻量无状态
- Delta/Parquet 即时视图绑定：`delta_scan`/`parquet_scan`
- 服务端 S3 认证：`GITHUB_TOKEN + S3_AUTH_URL → 临时 AWS 凭证` 注入 DuckDB
- 安全控制：仅单语句 `SELECT`，保护性 `LIMIT`，统一错误响应

## 环境与配置
- `APP_ENV`：`dev|prod`（默认 `dev`）
- `DEV_PARQUET_URI`：本地 Parquet 文件路径（默认 `/Users/wlt/Downloads/slow-query/1.parquet`）
- `S3_AUTH_URL`：认证服务地址（prod 必填）
- `GITHUB_TOKEN`：认证所需 GitHub Token（prod 必填）
- `AWS_REGION`：默认区域（可被请求覆盖）
- 可选：`S3_ENDPOINT`、`S3_URL_STYLE`

## 启动

内置虚拟环境并手动安装依赖
```bash
  # cd /path/to/slow_query_proxy
  # python3 -m venv .venv
  # source .venv/bin/activate
  # pip install -U pip
  # pip install duckdb fastapi uvicorn requests

  python3 -m venv .venv
  source .venv/bin/activate
  python -m ensurepip --upgrade
  python3 -m pip install -U pip

  python3 -m pip install -U pip setuptools wheel
  python3 -m pip install duckdb fastapi uvicorn requests

```
- 启动服务：
  - PYTHONPATH=src APP_ENV=prod LOG_LEVEL=INFO uvicorn slowquery_proxy.main:app --host 127.0.0.1 --port 8088
```

健康检查：
```bash
curl http://127.0.0.1:8000/health
# {"status":"ok"}
```

## 接口
- `POST /query`
  - 请求体
    - `sql`：仅允许单语句 `SELECT`
    - `vendor`：`aws|gcp|azure|local`（默认 `aws`）
    - `region`：如 `us-east-1`
    - `tenant_id`、`o11y_id`：prod 环境拼接 S3 Delta 路径用
    - `sources`（可选）：`[{name,type('delta'|'parquet'),uri}]`
  - 响应体
    - `rows`：`[{...}, ...]`
    - `meta`：`columns`、`row_count`、`elapsed_ms`

## 示例（dev，本地 Parquet）
- 统计 `digest` 维度 TopN（修改自 ClusterATopN）
```bash
cat > /tmp/req.json <<'JSON'
{
  "sql": "SELECT COALESCE(digest, '') AS digest, COALESCE(ROUND(AVG(mem_max), 0), 0) AS avg_mem, COALESCE(ROUND(MAX(mem_max), 0), 0) AS max_mem, COALESCE(ROUND(AVG(query_time), 2), 0) AS avg_query_time, COALESCE(ROUND(MAX(query_time), 2), 0) AS max_query_time, COUNT(*) AS total_count FROM slowlogs WHERE \"time\" BETWEEN 0 AND 32503680000 AND lower(query) LIKE 'select%' AND lower(query) NOT LIKE 'analyze%' AND lower(query) NOT LIKE 'create%' AND lower(query) NOT LIKE 'alter%' AND lower(query) NOT LIKE 'drop%' AND lower(query) NOT LIKE 'truncate%' AND lower(query) NOT LIKE 'rename%' AND lower(query) NOT LIKE '%force index%' AND NOT (lower(query) LIKE '%read_from_storage(tiflash%' AND lower(query) NOT LIKE '%/*+ read_from_storage(tiflash%') GROUP BY digest ORDER BY total_count DESC LIMIT 10",
  "vendor": "local",
  "sources": [{"name":"slowlogs","type":"parquet","uri":"/Users/wlt/Documents/GolandProjects/clinic/health_check/slow_query_proxy/1.parquet"}]
}
JSON
curl -X POST http://127.0.0.1:8000/query -H 'Content-Type: application/json' --data-binary @/tmp/req.json
```
- 示例响应（截断）
```json
{
  "rows": [
    {
      "digest": "18576e...16930",
      "avg_mem": 17265.0,
      "max_mem": 104328,
      "avg_query_time": 2.39,
      "max_query_time": 3.11,
      "total_count": 479
    },
    ... 共 10 行
  ],
  "meta": {
    "columns": ["digest","avg_mem","max_mem","avg_query_time","max_query_time","total_count"],
    "row_count": 10,
    "elapsed_ms": 22
  }
}
```
- 字段解释
  - `digest`：SQL 摘要（哈希）
  - `avg_mem`/`max_mem`：内存峰值的平均与最大值
  - `avg_query_time`/`max_query_time`：执行时间的平均与最大值（秒）
  - `total_count`：满足过滤条件的记录数

## 示例（prod，S3 Delta）
- 环境：设置 `APP_ENV=prod`，并提供 `S3_AUTH_URL` 与 `GITHUB_TOKEN`
- 请求（自动绑定 `slowlogs = delta_scan('s3://o11y-prod-shared-{region}/deltalake/{tenant_id}/{o11y_id}/slowlogs')`）
```bash
curl -X POST http://127.0.0.1:8000/query \
  -H 'Content-Type: application/json' \
  -d '{
    "sql": "SELECT count(*) AS c FROM slowlogs",
    "vendor": "aws",
    "region": "us-east-1",
    "tenant_id": "<tenant_id>",
    "o11y_id": "<o11y_id>"
  }'
```

## 注意事项
- SQL 字面量请使用单引号（例如 `'select%'`）；双引号用于标识符（例如 `"time"`）
- 不允许多语句及高风险指令（`PRAGMA/ATTACH/INSTALL/LOAD/COPY` 等）
- 结果集默认有保护性 `LIMIT`，如需更大结果请在 SQL 中明确设置并权衡性能

## 开发测试
- 运行测试：
```bash
PYTHONPATH=src ./.venv/bin/pytest -q
```
- 测试包括：本地 Parquet 基础查询、prod 认证形态（mock）验证

## 目录结构
```
pyproject.toml
src/slowquery_proxy/
  main.py
  routers/query.py
  db.py
  auth.py
  models.py
  config.py
  errors.py
tests/
  test_parquet_local.py
  test_delta_s3_stub.py
```