byted-bytehouse-ai-query

byted-bytehouse-ai-query

Description

ByteHouse AI Query Skill, providing the following core capabilities:

1. Text2SQL — Convert natural language query requirements into ByteHouse SQL.
2. List Tables — List all databases and tables under the target database/instance.
3. Execute SQL — Execute SQL queries and return results, with built-in non-DQL protection.
4. Multimodal vectorization and retrieval — Supports vectorization of text, images, and videos, as well as vector/hybrid retrieval.

Use this Skill when the following situations occur:

1. The user wants to query data in ByteHouse through natural language ("help me check the data volume of the xxx table", "see what databases and tables there are", etc.).
2. The user explicitly requests converting natural language to SQL (Text2SQL), or needs to execute SQL on ByteHouse.
3. The user wants to perform vectorized storage and retrieval of text/images/videos on ByteHouse.
4. The user mentions keywords related to ByteHouse, Volcano Engine cloud data warehouse, cloud-native data warehouse, or data warehouse queries.

🧭 Agent usage guide (important)

When the Agent undertakes the above tasks, it schedules the scripts in this Skill according to the following process:

1. Ensure the configuration is ready: first execute source scripts/export_config.sh, and inject the configuration from ~/.bytehouse_config.json into environment variables.
   • If ~/.bytehouse_config.json does not exist or is missing BYTEHOUSE_HOST / BYTEHOUSE_PASSWORD, proactively guide the user to provide it, and write the connection information returned by the user into this JSON file before source。
2. Get a full picture of the data: if the user has not explicitly specified the database or table, first use scripts/list_tables.py to query the database/table list, helping the user/Agent select the table to query.
3. Generate SQL: when natural language needs to be converted to SQL, use scripts/text2sql.py, passing the target table as a positional parameter.
4. Execute SQL: after obtaining the SQL, use scripts/execute_sql.py. By default, only DQL is allowed to be executed; non-DQL (INSERT/UPDATE/DROP, etc.) requires confirmation from the user first, then append --force。
5. Multimodal retrieval scenario: use scripts/embedding.py + scripts/search_client.py to complete vectorization and vector/hybrid retrieval.
6. Display results: by default, return the first 5 results; on exceptions, display the complete error. Sensitive fields such as keys and passwords must always be masked.

Agent Mustinitiate queries/execution through the above scripts; it is not allowed to assemble SQL on your own or connect directly to ByteHouse.

📁 File description

• SKILL.md — This file, the main skill document.
• requirements.txt — Python dependency list.
• scripts/text2sql.py — Text2SQL conversion script.
• scripts/list_tables.py — List databases or tables under a specified database.
• scripts/execute_sql.py — Execute SQL query script, with built-in DQL protection.
• scripts/client.py — Common module for the ByteHouse connection client.
• scripts/embedding.py — Multimodal vectorization script.
• scripts/search_client.py — ByteHouse vector retrieval client.
• scripts/export_config.sh — From ~/.bytehouse_config.json load configuration into environment variables (depends on jq）。

Configuration description

The configuration file is located at ~/.bytehouse_config.json. If the file exists and is non-empty, use the configuration in the file directly; otherwise, ask the user for the required connection information (you can send this document to the user for reference ByteHouse Connection Information Retrieval Guide), and once obtained, write it to a JSON file to avoid repeated asking. When the user switches ByteHouse clusters, update this file as well.

{
   "BYTEHOUSE_HOST": "<ByteHouse-host>",
   "BYTEHOUSE_PORT": "8123",
   "BYTEHOUSE_USER": "bytehouse",
   "BYTEHOUSE_PASSWORD": "<ByteHouse-password>",
   "BH_ARK_API_KEY": "<火山引擎方舟 API 密钥>",
   "BH_ARK_BASE_URL": "https://ark.cn-beijing.volces.com/api/v3",
   "BH_EMBEDDING_MODEL": "doubao-embedding-vision-251215"
}

• BYTEHOUSE_HOST(host address) and BYTEHOUSE_PASSWORD(password)must be provided by the user。
• BH_ARK_API_KEY is only used for multimodal vectorization; it can be ignored in other scenarios.
• For other fields, it is recommended to keep the default values.

Usage Restrictions

1. Non-DQL Protection: If the SQL generated by Text2SQL is an INSERT / UPDATE / DELETE / DROP or other DML / DDL statement, the Agent must block execution first, show the user the specific SQL, and explicitly ask whether to confirm.
   • execute_sql.py When encountering non-DQL, it reports an error by default and requires confirmation.
   • Only after the user explicitly agrees may the --force parameter be attached to force execution, for example:

     python3 scripts/execute_sql.py "DROP TABLE xxx" --force
     

2. Result Presentation: By default, display the first 5 results that match the query conditions; on exception, display the complete error message.
3. No Speculation: For data / asset related questions, always run SQL execution before answering; do not guess answers based on context.
4. Sensitive Information Protection: Do not directly output sensitive information such as passwords or Keys; if display is truly necessary, mask it.

Prerequisites

• Python 3.8+
• jq（export_config.sh is required for parsing JSON; if not installed, you can brew install jq or sudo apt install jq）
• Python dependencies: see requirements.txt, can be installed via pip install -r requirements.txt installation
• Valid ByteHouse connection information (saved in ~/.bytehouse_config.json）

🚀 Quick Start

1. Load ByteHouse connection information

# 从配置文件读取配置，导出到环境变量
source scripts/export_config.sh

2. List databases and tables

# 列出所有数据库
python3 scripts/list_tables.py --databases

# 列出指定数据库的表
python3 scripts/list_tables.py --database tpcds

3. Text2SQL

# 执行 Text2SQL
python3 scripts/text2sql.py "get count of all call centers" "tpcds.call_center"

Returns:

SELECT COUNT(*) AS call_center_count FROM tpcds.call_center;

4. Execute SQL query

python3 scripts/execute_sql.py "SELECT * FROM tpcds.call_center LIMIT 5"
python3 scripts/execute_sql.py "SELECT count(*) FROM tpcds.store_sales" --format pretty

5. Complete workflow: Text2SQL + Execute

# 1. 生成 SQL
SQL=$(python3 scripts/text2sql.py "get count of call centers" "tpcds.call_center")

# 2. 执行 SQL
python3 scripts/execute_sql.py "$SQL"

6. Multimodal vectorization and retrieval

Multimodal related scripts:

• scripts/embedding.py — Multimodal vectorization module
• scripts/search_client.py — ByteHouse vector retrieval client

# 从脚本目录调用，或将 scripts/ 加入 sys.path
from search_client import ByteHouseMultimodalSearch

# 初始化客户端
search = ByteHouseMultimodalSearch(connection_type="http")

# 创建表
search.create_multimodal_table("my_index")

# 插入文档
search.insert_document(
    "my_index",
    doc_id=1,
    content_type="text",
    content="ByteHouse 多模态检索",
    title="介绍",
)

# 向量检索（返回结果中已过滤掉 0 维向量）
query_embedding = search.embedding.encode_text("云原生数据仓库")
results = search.vector_search("my_index", query_embedding=query_embedding, top_k=10)

Reference documentation:

• ByteHouse Vector Retrieval SQL Documentation
• Volcano Engine Multimodal Vectorization API Documentation

💻 Programmatic Invocation

Text2SQL + Execute integrated

import subprocess
import json


def ai_query(natural_language: str, tables: list, config: dict = None) -> str:
    """调用 Text2SQL 并执行查询"""
    cmd = ["python3", "scripts/text2sql.py", natural_language] + tables
    if config:
        cmd.extend(["--config", json.dumps(config)])

    sql_result = subprocess.run(cmd, capture_output=True, text=True)
    sql = sql_result.stdout.strip()

    if not sql:
        return f"Text2SQL failed: {sql_result.stderr}"

    result = subprocess.run(
        ["python3", "scripts/execute_sql.py", sql],
        capture_output=True,
        text=True,
    )
    return result.stdout


if __name__ == "__main__":
    print(ai_query("get count of call centers", ["tpcds.call_center"]))

API Reference

Text2SQL Request Parameters

Parameter	Type	Required	Description
systemHints	string	No	System prompt, default is "TEXT2SQL"
input	string	Yes	Natural language query
knowledgeBaseIDsString	string[]	No	Knowledge base ID list, default ["*"]
tables	string[]	Yes	List of table names to query
config	object	No	Custom configuration
config.reasoningModel	string	No	Custom model ID
config.reasoningAPIKey	string	No	Custom API Key
config.url	string	No	Custom API URL