KWDB Text2SQL AIoT

KWDB Text-to-SQL Skill

Query Type Routing

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

Quick Reference

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:

生成的 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):

{
  "sql": ""
}

For write queries (write-query):

{
  "sql": ""
}

Step 5: Handle Execution Result

On Success: Report to user:

## 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:

     ## 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:

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: