chat2BI（sql）实现

个人学习使用，参考PIGX-AI：https://ai.wiki.pig4cloud.com/doc/ai/func/database

Gitee同步：https://gitee.com/rickkkFang/my-notes/tree/master/72.AI

1、同步数据表

同步指定业务数据库表到AI模块的数据库表管理

在使用 Chat2BI 功能前，首先需要同步数据库表结构，这是整个功能的基础。系统需要了解您的数据表结构，才能正确理解自然语言查询并转换为准确的 SQL 语句。操作步骤：

1. 进入数据表同步界面

2. 选择需要同步的数据库连接

3. 点击”同步”按钮，系统将自动获取所有表结构信息

4. 同步完成后，您可以看到所有可用的数据表及其结构

定期同步数据表结构可确保 Chat2BI 使用最新的表结构信息，特别是在数据库结构发生变更后。

2、新增数据集

首先需要添加您想要分析的数据集，系统将自动处理数据结构，选中指定需要学习的数据源的数据表结构列表，数据集是AI系统进行知识检索和学习的基础数据单元。

1、保存和更新数据集：将数据集描述信息向量化 存储到内存向量库供快速检索 用于Chat2SQL场景的数据集匹配

@Override
public boolean saveOrUpdateData(AiDataEntity aiData) {
    baseMapper.insertOrUpdate(aiData);
    // 创建文本段落，包含数据源和描述信息
    TextSegment textSegment = TextSegment.textSegment(aiData.getDsName() + aiData.getDescription(),
            Metadata.from(Map.of(EmbedBizTypeEnums.Fields.type, EmbedBizTypeEnums.CHAT2SQL.getType(), TEMP_ID,aiData.getDataId())));
    // 添加到内存向量存储
    MemoryEmbeddingProvider.add(SecureUtil.md5(aiData.getDsName()), textSegment);
    return true;
}

2、数据集内容添加到内存向量存储

/**
 * 内存向量存储提供者
 * <p>
 * 提供基于内存的向量存储和检索功能，用于AI随航等轻量级场景 支持文本向量化、存储和相似度搜索，数据存储在内存中
 */
@Slf4j
@UtilityClass
public class MemoryEmbeddingProvider {
    /**
     * 添加文本段到向量存储（指定ID）
     * <p>
     * 将文本向量化后存储，支持后续的相似度搜索
     * @param id 文档ID
     * @param textSegment 要存储的文本段
     */
    public void add(String id, TextSegment textSegment) {
        // 获取默认的向量模型
        DimensionAwareEmbeddingModel embeddingModel = getEmbeddingModel();
        if (embeddingModel == null)
            return;
​
        // 文本向量化
        dev.langchain4j.model.output.Response<Embedding> embeddingResponse = embeddingModel.embed(textSegment.text());
        Embedding embedding = embeddingResponse.content();
        // 存储向量和原始文本
        EMBEDDING_STORE.add(id, embedding, textSegment);
    }
​
    /**
     * 获取默认的嵌入模型
     * <p>
     * 从数据库配置中获取默认的向量模型
     * @return 嵌入模型实例，如果没有配置则返回null
     */
    @Nullable
    private static DimensionAwareEmbeddingModel getEmbeddingModel() {
        AiModelMapper modelMapper = SpringUtil.getBean(AiModelMapper.class);
​
        AiModelEntity aiModelEntity = modelMapper.selectOne(Wrappers.<AiModelEntity>lambdaQuery()
            .eq(AiModelEntity::getDefaultModel, YesNoEnum.YES.getCode())
            .eq(AiModelEntity::getModelType, ModelTypeEnums.EMBEDDING.getType()), false);
​
        if (Objects.isNull(aiModelEntity)) {
            log.warn("没有默认的向量模型，请检查模型配置，AI随航无法使用");
            return null;
        }
​
        ModelProvider modelProvider = SpringUtil.getBean(ModelProvider.class);
        return modelProvider.getEmbeddingModel(aiModelEntity.getName());
    }
​
    /**
     * 相似度搜索（使用默认配置）
     * <p>
     * 使用系统默认的topK和最小分数进行搜索
     * @param text 搜索文本
     * @param filters 过滤条件
     * @return 搜索结果
     */
    public EmbeddingSearchResult<TextSegment> search(String text, Filter... filters) {
        AiKnowledgeProperties properties = SpringUtil.getBean(AiKnowledgeProperties.class);
        return search(text, properties.getInMemorySearch().getTopK(), properties.getInMemorySearch().getMinScore(),
                filters);
    }
​
    /**
     * 相似度搜索（自定义参数）
     * <p>
     * 将文本向量化后在向量库中进行相似度搜索
     * @param text 搜索文本
     * @param topK 返回结果数量
     * @param minScore 最低相似度分数
     * @param filters 过滤条件
     * @return 搜索结果
     */
    public EmbeddingSearchResult<TextSegment> search(String text, int topK, double minScore, Filter... filters) {
        // 获取嵌入模型
        DimensionAwareEmbeddingModel embeddingModel = getEmbeddingModel();
        if (embeddingModel == null)
            return null;
​
        // 搜索文本向量化
        Embedding embedding = getEmbeddingModel().embed(text).content();
​
        // 构建搜索请求
        EmbeddingSearchRequest.EmbeddingSearchRequestBuilder builder = EmbeddingSearchRequest.builder()
            .queryEmbedding(embedding)
            .minScore(minScore)
            .maxResults(topK);
​
        // 添加过滤条件
        for (Filter filter : filters) {
            builder.filter(filter);
        }
​
        // 执行向量搜索
        return EMBEDDING_STORE.search(builder.build());
    }
​
}

3、数据集字段评估

字段评估是确保 AI 正确理解您数据的关键步骤！使用大模型对原有数据表的字段注释进行矫正，能显著提高后续自然语言提问的识别准确率，评估能将数据库表字段进行正确的虚拟注释。

操作步骤：

1. 点击字段评估功能

2. PIG AI 会自动调用大模型对字段进行智能矫正

3. 对于枚举类字段，请手动维护和编辑【虚拟备注】以提高准确性

实现代码：

/**
 * AI字段智能评估 使用AI对表字段进行智能分析和评估，生成字段描述和使用建议
 * @param dsName 数据源名称
 * @param tableName 数据表名称
 * @return 评估结果
 */
@Operation(summary = "字段评估")
@SysLog("字段评估")
@PostMapping("/assess/{dsName}/{tableName}")
@PreAuthorize("@pms.hasPermission('knowledge_aiData_del')")
public R assess(@PathVariable String dsName, @PathVariable String tableName) {
    aiDataFieldService.assessTableField(dsName, tableName);
    return R.ok();
}
​
/**
 * 使用AI评估表字段质量
 * <p>
 * 异步分析表结构和字段信息 生成优化建议和改进的字段注释 更新虚拟注释供开发参考
 * @param dsName 数据源名称
 * @param tableName 表名
 */
@Override
@Async
    public void assessTableField(String dsName, String tableName) {
    // 获取JSON格式输出的AI助手
    Triple<ChatModel, AiAssistantService, String> jsonAssistantTriple = modelProvider.getAiJSONAssistant(null);
​
    AiDataTableEntity dataTableEntity = aiDataTableMapper.selectOne(Wrappers.<AiDataTableEntity>lambdaQuery()
        .eq(AiDataTableEntity::getDsName, dsName)
        .eq(AiDataTableEntity::getTableName, tableName));
    // 查询表结构Schema，将多个表结构信息转换为结构化文档，优先使用AI优化后的虚拟注释
    String dataSchema = aiDataService.queryTableSchema(List.of(dataTableEntity));
    // AI助手服务智能评估数据库表字段 根据字段名称、数据类型等信息，自动生成规范的中文注释
    AiSuggestionFieldResultDTO aiSuggestionFieldResultDTO = jsonAssistantTriple.getMiddle()
        .assessTableField(dataSchema);
    log.info("评估表字段结果：{}", aiSuggestionFieldResultDTO);
​
    for (AiSuggestionFieldResultDTO.AiSuggestionFieldDTO suggestion : aiSuggestionFieldResultDTO.getSuggestions()) {
        AiDataFieldEntity aiDataFieldEntity = new AiDataFieldEntity();
        aiDataFieldEntity.setModifyStatus(YesNoEnum.YES.getCode());
        aiDataFieldEntity.setModifyTime(LocalDateTime.now());
        aiDataFieldEntity.setVirtualComment(suggestion.getFieldSuggestionComment());
​
        // 更新字段的虚拟注释
        aiDataFieldMapper.update(aiDataFieldEntity,
                Wrappers.<AiDataFieldEntity>lambdaQuery()
                    .eq(AiDataFieldEntity::getTableId, dataTableEntity.getTableId())
                    .eq(AiDataFieldEntity::getFieldName, suggestion.getFieldName()));
    }
​
    // 更新表的虚拟注释
    dataTableEntity.setVirtualComment(aiSuggestionFieldResultDTO.getTableSuggestionComment());
    aiDataTableMapper.updateById(dataTableEntity);
}

4、根据聊天类型处理选择对应处理规则

数据库聊天类型Enum： DATABASE_CHAT(-2L, "databaseChat");

/**
 * 处理数据库聊天请求
 * <p>
 * 主要流程： 1. 如果用户未指定数据集，自动匹配合适的数据集 2. 获取数据集的表结构信息 3. 使用AI模型将自然语言转换为SQL 4.
 * 执行SQL并返回结果，支持图表展示
 * @param chatMessageDTO 聊天上下文信息
 * @return AI响应结果流，包含查询结果和可选的图表信息
 */
@Override
public Flux<AiMessageResultDTO> process(ChatMessageDTO chatMessageDTO) {
​
    // 处理mcp，如果用户没有传递，则根据用户语义查询一个 data 数据集
    if (Objects.isNull(chatMessageDTO.getExtDetails())
            || Objects.isNull(chatMessageDTO.getExtDetails().getDataId())) {
        Flux<AiMessageResultDTO> just = autoChoice(chatMessageDTO);
        if (just != null)
            return just;
    }
​
    Long dataId = chatMessageDTO.getExtDetails().getDataId();
    // 获取数据集下所有的表结构信息，用于AI理解数据结构
    String tableSchemas = aiDataService.queryDataSchema(dataId);
    chatMessageDTO.getExtDetails().setDataId(dataId);
    chatMessageDTO.getExtDetails().setFuncName(chat2SqlFunctionCalling.functionName());
    ChatMessageContextHolder.set(chatMessageDTO);
​
    // 更新record 记录
    AiChatRecordEntity recordEntity = new AiChatRecordEntity();
    recordEntity.setRecordId(chatMessageDTO.getMessageKey());
    recordEntity.setExtDetails(JSONUtil.toJsonStr(chatMessageDTO.getExtDetails()));
    chatRecordMapper.updateById(recordEntity);
​
    Triple<ChatModel, AiAssistantService, String> jsonAssistantTriple = modelProvider
        .getAiJSONAssistant(chatMessageDTO.getModelName());
​
    ChatMemory chatMemory = chatMemoryAdvisorProvider.get(chatMessageDTO.getConversationId());
​
    String render = PromptBuilder.render("chat2db.st", Map.of("tableSchema", tableSchemas, "userInput",
            chatMessageDTO.getContent(), "tenantId",
            Objects.nonNull(TenantContextHolder.getTenantId()) ? TenantContextHolder.getTenantId() : StrUtil.EMPTY,
            systemTime, DateUtil.now()));
​
    ChatRequest chatRequest = buildChatRequest(chatMemory, chat2SqlFunctionCalling, render,
            jsonAssistantTriple.getRight());
    ChatResponse chatResponse = jsonAssistantTriple.getLeft().chat(chatRequest);
​
    String repair = JSONRepairUtil.repair(chatResponse.aiMessage().text());
    log.info("json chatResponse: {}", chatResponse.aiMessage().text());
​
    // 使用参数调用原有的函数逻辑
    ToolExecutionRequest toolExecutionRequest = ToolExecutionRequest.builder()
        .name(chat2SqlFunctionCalling.functionName())
        .arguments(repair)
        .id(chatMessageDTO.getConversationId())
        .build();
​
    R<String> resultR = chat2SqlFunctionCalling.execute(toolExecutionRequest);
    String result = StrUtil.isBlank(resultR.getData()) ? resultR.getMsg() : resultR.getData();
    chatMemory.add(AiMessage.from(chatResponse.aiMessage().text() + StrUtil.LF + result));
​
    ChatMessageDTO resultChatMessageDTO = ChatMessageContextHolder.get();
​
    AiMessageResultDTO aiMessageResultDTO = new AiMessageResultDTO(result);
    if (Objects.nonNull(resultChatMessageDTO) && Objects.nonNull(resultChatMessageDTO.getExtDetails())
            && Objects.nonNull(resultChatMessageDTO.getExtDetails().getChartType())) {
        aiMessageResultDTO.setChartType(resultChatMessageDTO.getExtDetails().getChartType());
        aiMessageResultDTO.setChartId(resultChatMessageDTO.getExtDetails().getChartId());
    }
    return Flux.just(aiMessageResultDTO);
}

5、数据库聊天规则处理器核心流程解析

1. 数据集自动匹配阶段

if (Objects.isNull(chatMessageDTO.getExtDetails())
        || Objects.isNull(chatMessageDTO.getExtDetails().getDataId())) {
    Flux<AiMessageResultDTO> just = autoChoice(chatMessageDTO);
    if (just != null)
        return just;
}

原理：如果用户没有指定具体的数据集，系统会通过向量相似度搜索自动匹配合适的数据集。使用嵌入向量搜索CHAT2SQL类型的数据集，找到语义最相似的数据集。

2. 表结构获取

Long dataId = chatMessageDTO.getExtDetails().getDataId();
String tableSchemas = aiDataService.queryDataSchema(dataId);

原理：获取指定数据集的表结构信息，这些信息将作为上下文提供给AI模型，帮助其理解数据库结构。

3. 上下文设置与记录更新

chatMessageDTO.getExtDetails().setFuncName(chat2SqlFunctionCalling.functionName());
ChatMessageContextHolder.set(chatMessageDTO);
​
// 更新record 记录
AiChatRecordEntity recordEntity = new AiChatRecordEntity();
recordEntity.setRecordId(chatMessageDTO.getMessageKey());
recordEntity.setExtDetails(JSONUtil.toJsonStr(chatMessageDTO.getExtDetails()));
chatRecordMapper.updateById(recordEntity);

原理：设置函数调用名称，保存聊天记录到数据库，便于后续追踪和分析。

4. AI模型调用准备

Triple<ChatModel, AiAssistantService, String> jsonAssistantTriple = modelProvider
    .getAiJSONAssistant(chatMessageDTO.getModelName());
​
ChatMemory chatMemory = chatMemoryAdvisorProvider.get(chatMessageDTO.getConversationId());

原理：获取AI模型实例、助手服务和模型格式信息，同时获取对话记忆以保持上下文连贯性。

5. 提示词渲染

String render = PromptBuilder.render("chat2db.st", Map.of("tableSchema", tableSchemas, "userInput",
        chatMessageDTO.getContent(), "tenantId",
        Objects.nonNull(TenantContextHolder.getTenantId()) ? TenantContextHolder.getTenantId() : StrUtil.EMPTY,
        systemTime, DateUtil.now()));

原理：使用模板引擎渲染提示词，将表结构、用户输入、租户ID等变量注入到预定义的提示词模板中。

6. 构建聊天请求

ChatRequest chatRequest = buildChatRequest(chatMemory, chat2SqlFunctionCalling, render,
        jsonAssistantTriple.getRight());

原理：据不同的模型提供商（OpenAI或其他）构建相应格式的聊天请求，构建包含JSON Schema的聊天请求，确保AI模型输出符合预期的结构化格式。

7. AI模型调用与JSON修复

ChatResponse chatResponse = jsonAssistantTriple.getLeft().chat(chatRequest);
String repair = JSONRepairUtil.repair(chatResponse.aiMessage().text());

原理：调用AI模型进行自然语言到SQL的转换，然后修复可能存在的JSON格式问题。

8. 函数调用执行

ToolExecutionRequest toolExecutionRequest = ToolExecutionRequest.builder()
    .name(chat2SqlFunctionCalling.functionName())
    .arguments(repair)
    .id(chatMessageDTO.getConversationId())
    .build();
​
R<String> resultR = chat2SqlFunctionCalling.execute(toolExecutionRequest);

原理：将AI生成的SQL参数传递给函数调用器chat2SqlFunctionCalling，执行实际的数据库查询操作。Chat2SQL函数调用实现类该类实现了将自然语言转换为SQL语句并执行的功能 支持SQL查询结果的表格展示和图表生成

9. 结果处理与图表支持

String result = StrUtil.isBlank(resultR.getData()) ? resultR.getMsg() : resultR.getData();
chatMemory.add(AiMessage.from(chatResponse.aiMessage().text() + StrUtil.LF + result));
// 使用TransmittableThreadLocal实现跨线程传递聊天消息上下文， 支持在异步任务和线程池场景下保持消息上下文的正确传递
AiMessageResultDTO aiMessageResultDTO = new AiMessageResultDTO(result);
if (Objects.nonNull(resultChatMessageDTO) && Objects.nonNull(resultChatMessageDTO.getExtDetails())
        && Objects.nonNull(resultChatMessageDTO.getExtDetails().getChartType())) {
    aiMessageResultDTO.setChartType(resultChatMessageDTO.getExtDetails().getChartType());
    aiMessageResultDTO.setChartId(resultChatMessageDTO.getExtDetails().getChartId());
}

原理：处理查询结果，支持图表展示功能，将结果添加到对话记忆中。