本篇为 spring-ai-alibaba 学习系列第四十三篇

前面提到基础的用法是基于 BaseNl2SqlService 的 nl2sql 方法来使用

添加配置

使用前需添加对应配置，主要包括三部分

• 数据库配置：目前支持h2（适合学习使用）、mysql、postgreSql
• 向量存储配置：目前支持 SimpleVector（无需配置，仅适合学习使用），AnalyticDB
• 大模型配置

一份完整的配置如下

spring:
#  datasource:
#    url: ${NL2SQL_DATASOURCE_URL}
#    username: ${NL2SQL_DATASOURCE_USERNAME:root}
#    password: ${NL2SQL_DATASOURCE_PASSWORD:root}
#    driver-class-name: org.h2.Driver
#    type: com.alibaba.druid.pool.DruidDataSource
#  jpa:
#    hibernate:
#      ddl-auto: update
#    show-sql: true
#    properties:
#      hibernate:
#        format_sql: true
#        dialect: org.hibernate.dialect.H2Dialect
#    defer-datasource-initialization: true
#  sql:
#    init:
#      mode: always
#      schema-locations:
#        - classpath:sql/h2/schema-h2.sql
#        - classpath:sql/h2/product_schema.sql
#      data-locations:
#        - classpath:sql/h2/data-h2.sql
#        - classpath:sql/h2/product_data.sql
#      continue-on-error: true
#      separator: ;
#      encoding: utf-8
  ai:
    openai:
      base-url: https://dashscope.aliyuncs.com/compatible-mode
      api-key: ${AI_DASHSCOPE_API_KEY}
      model: qwen-max
      embedding:
        model: text-embedding-v4
    dashscope:
      api-key: ${AI_DASHSCOPE_API_KEY}
#    mcp:
#      server:
#        name: xiyan-server    # MCP服务器名称
#        version: 0.0.1           # 服务器版本号
    vectorstore:
      analytic:
        # 是否开启
        enabled: false
        # 基础配置
        collectName: ${VECTOR_COLLECTION_NAME}  # 向量集合名称
        regionId: ${REGION_ID}                  # 实例区域，如：cn-hangzhou
        dbInstanceId: ${INSTANCE_ID}            # AnalyticDB 实例 ID
        
        # 安全凭证（建议使用环境变量）
        accessKeyId: ${ACCESS_KEY_ID}           # 阿里云访问密钥ID
        accessKeySecret: ${ACCESS_KEY_SECRET}   # 阿里云访问密钥密码
        
        # 数据库访问凭证
        managerAccount: ${DB_MANAGER}           # 管理员账号
        managerAccountPassword: ${DB_PASSWORD}   # 管理员密码
        namespace: ${NAMESPACE}                 # 命名空间
        namespacePassword: ${NS_PASSWORD}       # 命名空间密码
        
        # 向量检索参数
        defaultTopK: 10                        # 返回结果数量
        defaultSimilarityThreshold: 0.01       # 相似度阈值(0.01-0.75)
        
  

# MyBatis Plus 配置
mybatis-plus:
#  configuration:
#    # 开启驼峰命名转换
#    map-underscore-to-camel-case: true
#    # SQL 日志打印（生产环境建议关闭或使用 slf4j）
#    log-impl: org.apache.ibatis.logging.slf4j.Slf4jImpl
#    # 设置全局缓存开关
#    cache-enabled: true
#    # 延迟加载的核心开关
#    lazy-loading-enabled: true
#    # 按需加载属性
#    aggressive-lazy-loading: false
#    # 设置默认执行器类型
#    default-executor-type: reuse
#    # 设置默认语句超时时间
#    default-statement-timeout: 30
#  global-config:
#    # 数据库配置
#    db-config:
#      # 主键类型（AUTO 自增，ASSIGN_ID 雪花算法，ASSIGN_UUID UUID）
#      id-type: auto
#      # 逻辑删除字段名
#      logic-delete-field: deleted
#      # 逻辑删除值（删除时设置的值）
#      logic-delete-value: 1
#      # 逻辑未删除值（正常时的值）
#      logic-not-delete-value: 0
#      # 字段验证策略
#      insert-strategy: not_null
#      update-strategy: not_null
#      where-strategy: not_null
#    # 关闭 MyBatis Plus 启动横幅
#    banner: false
#    # 是否开启 LOGO
#    enable-sql-runner: false
#  # Mapper XML 文件位置（如果有的话）
#  mapper-locations: classpath*:/mapper/**/*.xml
#  # 实体扫描，多个package用逗号或者分号分隔
#  type-aliases-package: com.alibaba.cloud.ai.entity

# 数据库配置(非Agent模型的数据库配置）
chatbi:
  dbconfig:
    # 数据源配置
    url: ${JDBC_URL}        # 如：jdbc:mysql://host:port/database
    username: ${DB_USER:root}
    password: ${DB_PASSWORD:root}
    # 连接参数
    connectiontype: jdbc
    dialecttype: h2      # 可选：mysql、postgresql
    schema: ${DB_SCHEMA}    # PostgreSQL需要

# 日志配置
logging:
  level:
    com.alibaba.cloud.ai.mapper: debug
    org.springframework.jdbc: debug

数据预处理

需要将表数据和列数据存入向量存储中

表数据：表名、注释、外键、主键等信息，向量化部分为表注释，注释为空则向量化表名

列数据：列名、注释、字段类型、是否为主键、是否可以为空等信息，向量化部分为字段注释，注释为空则向量化列名

业务知识：提前将专业术语及业务知识解释等信息存入业务知识向量库中

代码调用

BaseNl2SqlService 的构造函数包含五个参数，部分参数有多个实现，所以需要手动指定

下面是一个以继承方式来使用 BaseNl2SqlService 的例子

@Service
public class SimpleNl2SqlService extends BaseNl2SqlService {

	@Autowired
	public SimpleNl2SqlService(@Qualifier("simpleVectorStoreService") BaseVectorStoreService vectorStoreService,
			@Qualifier("simpleSchemaService") BaseSchemaService schemaService, LlmService aiService,
			@Qualifier("dbAccessor") Accessor accessor, DbConfig dbConfig) {

		super(vectorStoreService, schemaService, aiService, accessor, dbConfig);
	}

    public void simpleNl2sql(String query) {
        super.nl2sql(query);
        ...
    }

}

流程拆解

BaseNl2SqlService 的 nl2sql 方法主要流程如下

1. 从向量存储中召回业务知识
2. 使用大模型从用户问题和业务知识中提取关键字
3. 根据用户问题从向量存储中召回表数据
4. 根据关键字从向量存储中召回列数据
5. 从表数据中获取外键，并通过外键扩展表和列
6. 合并表数据和列数据
7. 使用大模型筛选最终使用的表数据
8. 使用大模型参考用户问题和第七步中的表数据生成sql

提示词

提取关键字

将下述问题的关键语料抽取出来，直接以list形式输出，不要分析。
示例如下：
【问题】
查询2024年8月在北京，一级标签为"未成单"的人数。
【关键语料】
["2024年8月", "北京", "一级标签", "未成单", "人数"]

【问题】
Name movie titles released in year 1945. Sort the listing by the descending order of movie popularity. released in the year 1945 refers to movie_release_year = 1945;
【关键语料】
["movie titles", "released in year 1945", "movie popularity", "movie_release_year = 1945"]

【问题】
List all product name from Australia Bike Retailer order by product ID. Australia Bike Retailer is name of vendor
【关键语料】
["product name", "Australia Bike Retailer", "product ID", "name of vendor"]

【问题】
山东省济南市各车型（牵引车、载货车、自卸车、搅拌车）销量占比的月趋势
【关键语料】
["山东省", "济南市", "各车型", "牵引车", "载货车", "自卸车", "搅拌车", "销量占比", "月趋势"]

【问题】
{question}
【关键语料】

表筛选

你现在是一位数据分析师，你的任务是分析用户的问题和数据库schema，数据库schema包括表名、表描述、表之间的外键依赖，每张表中包含多个列的列名、列描述和主键信息，现在你需要根据提供的数据库信息和用户的问题，分析与用户问题相关的table，给出相关table的名称。
[Instruction]:
1. 排除与用户问题完全不相关的table
2. 保留可能对回答用户问题有帮助的表
3. 结果以json形式输出，用```json和```包围起来
4. 直接输出结果，不要做多余的分析

以下样例供你参考：

【DB_ID】 station_weather
# Table: train
[
(id:TEXT, 火车编号, Primary Key.),
(train_number:TEXT, 火车车次, Examples: [56701]),
(name:TEXT, 火车的名称),
(origin:TEXT, 出发站, Examples: [Kanniyakumari, Chennai, Trivandrum]),
(destination:TEXT, 到达站, Examples: [Kanniyakumari, Chennai, Trivandrum]),
(time:TEXT, 发车时间, Examples: [4:49, 22:10, 21:49]),
(interval:TEXT, 火车的运行频率, Examples: [Daily])
]
# Table: station
[
(id:TEXT, 车站编号, Primary Key),
(network_name:TEXT, 车站所属网络的名称, Examples: [Croxley, Chorleywood, Cheshunt]),
(services:TEXT, 提供的服务),
(local_authority:TEXT, 负责该车站区域的地方当局, Examples: [Three Rivers, Chiltern, Broxbourne])
]
# Table: route
[
(train_id:TEXT, 火车编号),
(station_id:TEXT, 车站编号)
]
# Table: weekly_weather
[
(station_id::TEXT, 车站编号),
(day_of_week:TEXT, 星期, Examples: [Tuesday, Monday, Wednesday]),
(high_temperature:INT, 最高气温, Examples: [59, 55, 58]),
(low_temperature:INT, 最低气温, Examples: [54, 52, 55]),
(precipitation:DOUBLE, 降水量, Examples: [50.0, 90.0, 70.0]),
(wind_speed_mph:INT, 风速, Examples: [22, 14, 13])
]
【Foreign keys】
route.station_id=station.id
route.train_id=train.id
weekly_weather.station_id=station.id

【问题】
How many different services are provided by all stations?
【Answer】
```json
["station"]
```

===============
【DB_ID】 hr_1
# Table: regions
[
(REGION_ID:TEXT, Primary Key),
(REGION_NAME:TEXT)
]
# Table: countries
[
(COUNTRY_ID:TEXT, Primary Key),
(COUNTRY_NAME:TEXT),
(REGION_ID:TEXT)
]
# Table: departments
[
(DEPARTMENT_ID:TEXT, Primary Key),
(DEPARTMENT_NAME:TEXT, department name. Examples: [Treasury, Shipping, Shareholder Services]),
(MANAGER_ID:TEXT),
(LOCATION_ID:TEXT)
]
# Table: jobs
[
(JOB_ID:TEXT, Primary Key),
(JOB_TITLE:TEXT),
(MIN_SALARY:INT, min salary. Examples: [4000, 8200, 4200]),
(MAX_SALARY:INT, max salary. Examples: [9000, 16000, 15000])
]
# Table: employees
[
(EMPLOYEE_ID:TEXT),
(FIRST_NAME:TEXT, Examples: [Peter, John, David]),
(LAST_NAME:TEXT, Examples: [Taylor, Smith, King]),
(EMAIL:TEXT),
(PHONE_NUMBER:TEXT, Examples: [650.509.4876]),
(HIRE_DATE:DATE, Examples: [1987-10-01]),
(JOB_ID:TEXT),
(SALARY:TEXT, Examples: [2500, 10000, 9000]),
(COMMISSION_PCT:DOUBLE, Examples: [0, 0.3, 0.25]),
(MANAGER_ID:TEXT),
(DEPARTMENT_ID:TEXT)
]
# Table: job_history
[
(EMPLOYEE_ID:TEXT),
(START_DATE:DATE, Examples: [1999-01-01]),
(END_DATE:DATE, Examples: [1999-12-31]),
(JOB_ID:TEXT),
(DEPARTMENT_ID:TEXT)
]
# Table: locations
[
(LOCATION_ID:TEXT),
(STREET_ADDRESS:TEXT),
(POSTAL_CODE:TEXT, Examples: [YSW 9T2, M5V 2L7, 99236]),
(CITY:TEXT, Examples: [Whitehorse, Venice, Utrecht]),
(STATE_PROVINCE:TEXT, Examples: [Yukon, Washington, Utrecht]),
(COUNTRY_ID:TEXT)
]
【Foreign keys】
countries.REGION_ID=regions.REGION_ID
employees.JOB_ID=jobs.JOB_ID
employees.DEPARTMENT_ID=departments.DEPARTMENT_ID
job_history.JOB_ID=jobs.JOB_ID
job_history.DEPARTMENT_ID=departments.DEPARTMENT_ID
job_history.EMPLOYEE_ID=employees.EMPLOYEE_ID
locations.COUNTRY_ID=countries.COUNTRY_ID

【问题】
display the full name (first and last name ) of employee with ID and name of the country presently where (s)he is working.
【Answer】
```json
["employees", "departments", "countries", "locations"]
```

===============
{schema_info}

【问题】
{question}

【参考信息】
{evidence}

【Answer】

sql生成

现在你是一个{dialect}生成师，需要阅读一个客户的问题，参考的数据库schema，根据参考信息的提示，生成一句可执行的SQL。
注意：
1、不要select多余的列。
2、生成的SQL用```sql 和```包围起来。
3、不要在SQL语句中加入注释！！！

【数据库schema】
{schema_info}

【参考信息】
{evidence}

【问题】
{question}

【SQL】