# mcp-saphana

MCP (Model Context Protocol) server for SAP HANA. Enables Claude and other MCP clients to query, explore, and bulk-export data from SAP HANA instances using the native `hdbcli` driver.

---

## Features

- **Schema & table discovery** — list schemas, list tables/views, search across schemas
- **Table introspection** — column definitions, data types, nullability
- **Data sampling** — fetch sample rows for profiling
- **Table statistics** — row count, disk size, memory footprint (with privilege-aware fallback)
- **Column profiling** — min/max/null count/distinct count for any column
- **Large volume export** — chunked pagination via `LIMIT/OFFSET` for multi-million row exports
- **Dual output formats** — `markdown` (human-readable) and `json` (machine-readable)
- **Privilege-safe** — gracefully falls back when `MONITORING` privilege is absent

---

## Architecture

```
server.py                   # FastMCP server (single file)
├── Lifespan connection     # One persistent hdbcli connection per server lifetime
├── 9 MCP tools             # Schema/table/column/query/export operations
└── Shared helpers          # _get_conn, _cursor_to_rows, _handle_hana_error, _safe_json
```

### Connection strategy
- Uses SAP HANA's native `hdbcli` driver (not JDBC/ODBC)
- Single persistent connection via FastMCP lifespan context
- Supports single-container, multi-tenant (system DB), and SAP HANA Cloud
- TLS/SSL configurable via env vars

### Large volume strategy
`saphana_chunked_export` wraps the user's SQL in a subquery with `LIMIT/OFFSET` and returns one chunk at a time. The caller increments `chunk_index` until `has_more` is `false`.

```
chunk 0: SELECT * FROM (user_sql) AS __q__ LIMIT 1000 OFFSET 0
chunk 1: SELECT * FROM (user_sql) AS __q__ LIMIT 1000 OFFSET 1000
...
```

---

## Setup

### 1. Install dependencies

```bash
pip install -e .
# or manually:
pip install mcp hdbcli pydantic python-dotenv
```

### 2. Configure credentials

```bash
cp .env.example .env
# Edit .env with your HANA connection details
```

```env
HANA_HOST=your-hana-host.example.com
HANA_PORT=39015
HANA_USER=your_username
HANA_PASSWORD=your_password

# Optional — for HANA Cloud / multi-tenant:
HANA_DATABASE=
HANA_ENCRYPT=false
HANA_SSL_VALIDATE_CERTIFICATE=true
```

**Port reference:**
| Scenario | Port |
|----------|------|
| On-premise, single container | `39015` |
| On-premise, multi-tenant (System DB) | `30013` |
| SAP HANA Cloud | `443` (with `HANA_ENCRYPT=true`) |

### 3. Run

```bash
python server.py
```

---

## Tools

| Tool | Description |
|------|-------------|
| `saphana_execute_query` | Run any SELECT; auto-applies LIMIT via safe subquery wrap |
| `saphana_list_schemas` | List all accessible schemas |
| `saphana_list_tables` | List tables/views in a schema; optional type/name filter |
| `saphana_describe_table` | Column names, types, nullability, defaults |
| `saphana_get_table_sample` | Sample N rows from a table |
| `saphana_get_table_stats` | Row count + disk/memory size (falls back to COUNT(*) without MONITORING) |
| `saphana_chunked_export` | Paginated bulk export for large result sets |
| `saphana_search_tables` | Search tables by partial name across all schemas |
| `saphana_get_column_stats` | Min/max/null count/distinct for a single column |

---

## Claude Desktop / MCP Client Configuration

Add to your MCP client config (e.g., `~/.claude/claude_desktop_config.json`):

```json
{
  "mcpServers": {
    "saphana": {
      "command": "python",
      "args": ["/path/to/mcp_saphana/server.py"],
      "env": {
        "HANA_HOST": "your-host",
        "HANA_PORT": "39015",
        "HANA_USER": "your_user",
        "HANA_PASSWORD": "your_password"
      }
    }
  }
}
```

Or use a `.env` file and omit the `env` block (the server loads `.env` automatically).

---

## Evaluations

`evaluation.xml` contains 10 read-only questions targeting stable SAP HANA system catalog objects. Run them with the MCP evaluation harness:

```bash
pip install anthropic
export ANTHROPIC_API_KEY=your_key

python scripts/evaluation.py \
  -t stdio \
  -c python \
  -a server.py \
  -e HANA_HOST=your-host \
  -e HANA_USER=your_user \
  -e HANA_PASSWORD=your_password \
  evaluation.xml
```

> **Note:** Question 8 in `evaluation.xml` has a placeholder answer (`FILL_IN_FROM_YOUR_INSTANCE`). Fill it in by running:
> ```sql
> SELECT COUNT(*) FROM SYS.TABLES WHERE SCHEMA_NAME = 'SYS'
> ```

---

## Known Limitations / Future Work

- `saphana_chunked_export` uses `OFFSET`-based pagination — performance degrades at very high offsets on large tables. For extreme volumes, consider SAP HANA's native `EXPORT` statement or partition-key-based pagination.
- No write tools (`INSERT`/`UPDATE`/`DELETE`) — read-only by design. Add a `saphana_execute_dml` tool with explicit destructive annotations if needed.
- No connection pooling — single persistent connection per server instance. For high-concurrency deployments, switch transport to `streamable_http` and manage a pool in the lifespan context.
- Evaluation question answers tied to standard HANA 2.0 SPS06+ catalog structure. Verify on your specific version.

---

## Project Status

| Area | Status |
|------|--------|
| Core server | ✅ Complete |
| All 9 tools | ✅ Complete |
| Privilege fallbacks | ✅ Complete |
| `.env` auto-load | ✅ Complete |
| Large volume export | ✅ Complete |
| Evaluation file | ✅ Complete (Q8 needs instance-specific answer) |
| Write tools (DML) | ❌ Not implemented |
| Connection pooling | ❌ Not implemented |
| Streaming HTTP transport | ❌ Not implemented (stdio only) |
| Tests | ❌ Not implemented |