KWDB Text-to-SQL Skill#

Query Type Routing#

Based on the user's query, read the appropriate reference file:

Query TypeReference FileQuery routing (start here)references/scenarios.mdMCP integrationreferences/mcp-integration.md时序DDL (创建时序库/表)references/ts-ddl.md聚合操作及降采样 (每小时/每天统计)references/ts-downsampling.md插值/填充缺失值references/ts-interpolation.md最新值查询references/ts-latest-value.md滑动窗口/session/eventreferences/ts-window-events.md关系表查询references/relational.md跨模查询（时序表+关系表）references/cross-model.md时序函数语法速查references/ts-functions.md关系函数语法速查references/relational-functions.md

Quick Reference#

NL PatternSQL Pattern最近N分钟/小时/天的数据WHERE ts >= NOW() - INTERVAL 'N hour'每小时/每天的平均值time_bucket(ts, '1h/1d') + avg(col)每N分钟/小时/天降采样time_bucket(ts, 'X') + aggregation填充缺失值time_bucket_gapfill() + interpolate()最新数据last(col) or ORDER BY ts DESC LIMIT 1滑动窗口TIME_WINDOW(ts, '1h', '15m')关联设备信息JOIN devices ON ...

Workflow#

Phase 0: MCP Detection & Schema Discovery (Recommended)#

1. Detect MCP availability: Call read-query with SELECT 1

   • If successful → MCP is available
   • If failed → MCP is unavailable, proceed to fallback

2. Get database name (if not provided by user):

   • Ask user: "请提供要查询的数据库名称"
   • Or execute SHOW DATABASES to list all databases

3. Discover tables in database: Execute SHOW TABLES FROM {database_name}

4. Identify candidate tables:

   • Match NL keywords to table names (e.g., "传感器" → sensor_data)
   • If multiple candidates → ask user: "请确认表名: [A, B, C]?"

5. Get table schema: Execute SHOW CREATE TABLE {database_name}.{table_name}, do not use DESCRIBE

   • Note column names, types, primary key, tags, comments
   • Map NL field names to actual column names

6. Proceed to Phase 1 with verified schema

Phase 0 Fallback: No MCP Available#

When MCP is unavailable:

1. Option A - Ask user: "请提供表结构信息（表名、列名）"
   • Wait for user to describe the schema
   • Proceed to Phase 1
2. Option B - Use assumed fields: "我将使用常见字段名生成 SQL，请验证"
   • Use standard field names (ts, device_id, temperature, etc.)
   • Mark output as "ASSUMED SCHEMA - please verify"
3. Proceed to Phase 1

Phase 1: Query Type Routing#

1. Read scenarios.md: references/scenarios.md - single entry point with decision tree

2. Route to scenario file based on query type:

   • aggregation/downsampling → ts-downsampling.md
   • interpolation → ts-interpolation.md
   • latest value → ts-latest-value.md
   • window/session/event → ts-window-events.md
   • cross-model → cross-model.md
   • relational → relational.md

3. Function syntax → see ts-functions.md (time-series) or relational-functions.md (relational)

Phase 2: SQL Generation#

1. Extract entities: Table name, columns, time range, conditions
2. Use schema from Phase 0 (if MCP was used)
3. Generate SQL: Use patterns from reference to construct SQL
4. Validate: Ensure SQL follows KWDB function syntax

Phase 3: Output#

1. Format output: Follow assets/output-template.md
2. Include field mapping if MCP was used
3. Mark assumptions if schema was assumed
4. Add verification checklist

Phase 4: KWDB Execute#

Prerequisite: SQL has been generated in Phase 2 and formatted in Phase 3.

Step 1: Check MCP Availability

Note: If MCP was successfully used in Phase 0 and schema was discovered, MCP is available. If Phase 0 indicated MCP was unavailable, skip this phase entirely.

If MCP availability is unknown (e.g., Phase 0 was skipped), verify now:

• Call read-query with SELECT 1
• If successful → MCP is available, proceed to Step 2
• If failed → MCP is unavailable, skip this phase entirely and end workflow

Step 2: Ask User for Execution Confirmation

Prompt user:

text

生成的 SQL 已准备就绪。是否需要通过 kwdb-mcp-server 执行该 SQL？
- 输入 "是" 或 "执行" → 继续执行
- 输入 "否" 或 "跳过" → 结束，不再执行

If user declines → end workflow.

Step 3: Determine Query Type

Analyze the generated SQL:

• Read query: SELECT, SHOW, EXPLAIN → use read-query
• Write query: INSERT, UPDATE, DELETE, CREATE, DROP, ALTER → use write-query

Step 4: Execute Query

Call the appropriate MCP tool:

For read queries ( read-query):

json

{
  "sql": "<generated SQL>"
}

For write queries ( write-query):

json

{
  "sql": "<generated SQL>"
}

Step 5: Handle Execution Result

On Success: Report to user:

text

## Execution Result
- Status: success
- Query Type: read / write
- Row Count: N
- Auto-Limited: true/false

### Results
[formatted table if applicable]

On Failure:

1. Parse the error message to identify error type (see Error Type table in Error Handling section below)

2. If error indicates SQL generation issue (wrong table name, wrong column, syntax error):

   • Explain to user: "SQL 执行失败，正在分析错误原因..."

   • Report the error and analysis: text

     ## Execution Result
     

• Status: failed
• Error: [error message]
• Analysis: [cause analysis]

   - Return to **Phase 1** with error context to regenerate SQL
3. If error indicates **user data issue** (constraint violation, permission issue, etc.):

   - Report the error and suggest fixes, but do not auto-regenerate

## Reference Files

- `references/scenarios.md` \- Query routing entry point (decision tree)
- `references/mcp-integration.md` \- How to use kwdb-mcp-server for schema discovery
- `references/ts-ddl.md` \- Time series DDL (CREATE DATABASE/TABLE with TAGS)
- `references/ts-downsampling.md` \- time\_bucket for fixed-interval downsampling
- `references/ts-interpolation.md` \- time\_bucket\_gapfill + interpolate for gap filling
- `references/ts-latest-value.md` \- first/last/last\_row for latest value queries
- `references/ts-window-events.md` \- TIME\_WINDOW, SESSION\_WINDOW, EVENT\_WINDOW, TWA, diff
- `references/relational.md` \- Standard SQL for relational tables
- `references/cross-model.md` \- JOIN between relational and time series
- `references/ts-functions.md` \- KWDB time-series function syntax reference
- `references/relational-functions.md` \- KWDB relational function syntax reference

## Guardrails

1. **Always verify table existence** when MCP is available
2. **Confirm column names** match actual schema before generating SQL
3. **Ask for time range** if user doesn't specify
4. **Add LIMIT clause** for queries without one (MCP auto-adds LIMIT 20, but you should be explicit)
5. **Mark assumed schema** when MCP is unavailable
6. **Handle ambiguous NL** by asking clarifying questions

## Error Handling (Authoritative Reference)

This Error Type table is used by:

- **Phase 4 Step 5** when SQL execution fails
- **When user reports** that generated SQL failed

When a user reports that generated SQL failed, diagnose and regenerate:

Error TypeLikely CauseFix`relation "xxx" does not exist`Wrong table nameAsk user to confirm table name, re-discover via MCP`column "xxx" not found`Wrong column nameUse MCP to re-read schema, update field mapping`syntax error`SQL syntax issueReview KWDB SQL syntax, check function parameter order`invalid interval`Wrong interval formatUse format like `'1h'`, `'1d'`, `'5m'` — not复合格式 like `'1d1h'`Overflow / out of rangeAggregation result too largeAdd filters to reduce result set size`ambiguous column reference`Column name exists in both joined tablesUse fully-qualified column names ( `table.column`)`permission denied`No write permissionReport to user, do not regenerate`duplicate key`Constraint violationReport to user, do not regenerate

When SQL fails:

1. Read the error message to identify the error type
2. If schema issue → re-run MCP discovery
3. If syntax issue → check `ts-functions.md` or `relational-functions.md` and relevant reference file
4. If data issue → ask user for clarification
5. Regenerate corrected SQL with explanation

## Schema Discovery via MCP

Use `read-query` tool to execute SHOW commands:

SQL CommandPurpose`SHOW DATABASES`List all databases`SHOW TABLES FROM {database_name}`List all tables in a database`SHOW CREATE TABLE {database_name}.{table_name}`Get table structure (columns, types, tags, comments)