Chat2DB体验记录

邢为栋

已于 2023-08-22 14:46:09 修改

阅读量789

点赞数

分类专栏： AI Bigdata 文章标签： clickhouse 数据库人工智能

于 2023-08-21 15:26:30 首次发布

本文链接：https://blog.csdn.net/xwd127429/article/details/132409452

版权

Bigdata 同时被 2 个专栏收录

79 篇文章 4 订阅

订阅专栏

7 篇文章 0 订阅

订阅专栏

官网文档：Chat2DB简介 | Chat2DB
Github：Chat2DB/README_CN.md at main · chat2db/Chat2DB

简介

Chat2DB 是一款有开源免费的多数据库客户端工具，支持windows、mac本地安装，也支持服务器端部署，web网页访问。和传统的数据库客户端软件Navicat、DBeaver 相比Chat2DB集成了AIGC的能力，能够将自然语言转换为SQL，也可以将SQL转换为自然语言，可以给出研发人员SQL的优化建议，极大的提升人员的效率，是AI时代数据库研发人员的利器，未来即使不懂SQL的运营业务也可以使用快速查询业务数据、生成报表能力。
也支持docker部署，部署方式详见 https://github.com/chat2db/Chat2DB#-docker-installation。

官方教程

使用教程 | Chat2DB
安装的时候，使用官网的安装包安装Chat2DB，发现功能不全，但是从github release下载安装包，安装之后功能正常，可能是官网的安装包有问题，毕竟目前Chat2DB更新速度比较快。
本文使用的Chat2DB版本：2.0.12。

支持的数据库

其中Redis和MongoDB是部分支持，Hbase、Elasticsearch、openGauss、TiDB、InfluxDB将在未来支持。

配置AI

点击左下角的配置按钮，打开配置页面：

选择自定义AI：

这里可以选择多种AI来源，其中Chat2DB AI是Chat2DB提供的AI，可以通过关注微信号获取Api Key，其他的按照AI源的要求配置即可。

AI SQL功能使用说明

Chat2DB包含一系列基于ChatGPT的AI SQL使用功能，主要包括自然语言转SQL、SQL解释、SQL优化和SQL转换。使用这些AI功能，可以将自然语言转换成本地查询SQL，而不仅仅是SQL查询伪代码；可以将SQL解释成自然语言，帮助用户理解复杂的SQL；可以针对慢SQL提供全方位的优化建议，提升查询效率；可以转换不同数据库类型的SQL语言，降低数据库迁移难度等等。

AI生成SQL

功能描述

输入自然语言，选中执行并传入查询相关的表结构信息，则会返回相应的SQL。
输入样例：
查询学生Bob的各科目成绩，并传入关联数据表score,student
输出样例：

SELECT chinese_score, math_score, english_score, science_score, humanities_score 
FROM score 
INNER JOIN student ON score.student_id = student.id 
WHERE student.name = 'Bob'

选择用于生成SQL的原始表，这些表及其属性会附加到Prompt中，可以让ChatGPT生成的SQL更准确。
调用ChatGPT API Prompt代码如下：

Map<String, List<TableColumn>> tableColumns = buildTableColumn(queryParam, queryRequest.getTableNames());
List<String> tableSchemas = tableColumns.entrySet().stream().map(
    entry -> String.format("%s(%s)", entry.getKey(),
                           entry.getValue().stream().map(TableColumn::getName).collect(
                               Collectors.joining(", ")))).collect(Collectors.toList());
String properties = String.join("\n#", tableSchemas);
String prompt = queryRequest.getMessage();
String promptType = StringUtils.isBlank(queryRequest.getPromptType()) ? PromptType.NL_2_SQL.getCode()
: queryRequest.getPromptType();
PromptType pType = EasyEnumUtils.getEnum(PromptType.class, promptType);
String ext = StringUtils.isNotBlank(queryRequest.getExt()) ? queryRequest.getExt() : "";
String schemaProperty = CollectionUtils.isNotEmpty(tableSchemas) ? String.format(
    "### 请根据以下table properties和SQL input%s. %s\n#\n### %s SQL tables, with their properties:\n#\n# "
    + "%s\n#\n#\n### SQL input: %s", pType.getDescription(), ext, dataSourceType,
    properties, prompt) : String.format("### 请根据以下SQL input%s. %s\n#\n### SQL input: %s",
                                        pType.getDescription(), ext, prompt);

SQL解释

功能描述

选中 SQL 右键解释SQL，在右侧弹窗中 AI 即可对 SQL 解释生成内容。
输入样例：

SELECT chinese_score, math_score, english_score, science_score, humanities_score 
FROM score 
INNER JOIN student ON score.student_id = student.id 
WHERE student.name = 'Bob'

输出样例：
这个SQL查询语句的目的是从名为"score"的表中选择"chinese_score"、“math_score”、“english_score”、"science_score"和"humanities_score"这五个列。同时，它使用INNER JOIN将"score"表与"student"表连接起来，连接条件是"score"表中的"student_id"列等于"student"表中的"id"列。最后，通过WHERE子句筛选出"student"表中"name"列等于’Bob’的记录。

SQL优化

功能描述

选中 SQL 右键优化SQL，在右侧弹窗中 AI 即可对 SQL 生成建议。
输入样例：

SELECT chinese_score, math_score, english_score, science_score, humanities_score 
FROM score 
INNER JOIN student ON score.student_id = student.id 
WHERE student.name = 'Bob'

输出样例：
优化建议如下： 1. 索引优化：确保score表的student_id列和student表的id列都有索引，以加快连接操作的速度。 2. 查询优化：考虑将WHERE子句中的条件student.name = 'Bob’改为使用student.id进行查询，因为student.id是一个索引列，查询速度可能更快。 3. 投影优化：如果只需要查询Bob的成绩，可以只选择需要的列，而不是选择所有的成绩列。这样可以减少数据的传输和处理量，提高查询效率。优化后的SQL查询如下： SELECT chinese_score, math_score, english_score, science_score, humanities_score FROM score INNER JOIN student ON score.student_id = student.id WHERE student.id = (SELECT id FROM student WHERE name = ‘Bob’)

SQL转化

功能描述

选中 SQL 右键转化SQL，在右侧弹窗中 AI 即可对当前 SQL 转换未其他数据库的 SQL 语法。

Chat2DB版本：2.0.12，该版本右键点击转化SQL后，直接进行了转化，没有发现可以输入转化提示的输入框。

仪表盘

目前功能比较简单，支持根据sql执行结果数据生成图表，图片只支持折线图、饼图和柱状图。
下图是Chat2DB自带的DEMO：

体验

对接ClickHouse

该版本Chat2DB还不支持查看ClickHouse表的DDL语句。

连接

配置连接，测试成功后，点击保存即可。

AI生成SQL

prompt添加表：bi_pub_hbd_res_profile_td
下面标记SQL正确的是SQL，是符合要求并可以执行的SQL。

-- 一共有多少提供者
-- SQL正确
SELECT COUNT(DISTINCT provider_id) FROM bi_pub_hbd_res_profile_td

-- 今年新增了多少提供者
SELECT COUNT(DISTINCT provider_id) AS new_providers
FROM bi_pub_hbd_res_profile_td
WHERE YEAR(create_time) = YEAR(CURRENT_DATE)

-- 今年新增了多少提供者，使用ClickHouse SQL语法
SELECT COUNT(DISTINCT provider_id) 
FROM bi_pub_hbd_res_profile_td 
WHERE YEAR(create_time) = YEAR(CURRENT_DATE)

-- 根据提供者首次上传时间，计算今年新增了多少提供者，使用ClickHouse SQL语法
SELECT COUNT(DISTINCT provider_id) AS new_providers
FROM bi_pub_hbd_res_profile_td
WHERE YEAR(provider_first_upload_time) = YEAR(NOW())


-- 统计每月发布的资料量，使用ClickHouse SQL语法
SELECT 
    toStartOfMonth(create_time) AS month,
    count(*) AS total_res_count
FROM 
    bi_pub_hbd_res_profile_td
GROUP BY 
    month
ORDER BY 
    month

-- 统计每个学科的资料数据量，使用ClickHouse SQL语法
-- SQL正确
SELECT subject_id, COUNT(*) AS data_count
FROM bi_pub_hbd_res_profile_td
GROUP BY subject_id

SQL解释和优化

SQL1：

SELECT subject_id, COUNT(*) AS data_count
FROM bi_pub_hbd_res_profile_td
GROUP BY subject_id

SQL1解释：
这个SQL查询语句是从表bi_pub_hbd_res_profile_td中选择subject_id，并计算每个subject_id的记录数量。然后使用GROUP BY子句按subject_id分组，以获取每个subject_id的数据计数。
SQL1优化：
根据提供的表属性和SQL输入，以下是一些建议优化的方法： 1. 索引优化：对于经常用于过滤和连接的列，可以考虑创建索引以提高查询性能。在bi_pub_hbd_res_profile_td表中，可能需要对subject_id列创建索引。 2. 分区优化：如果表的数据量很大，可以考虑根据日期或其他相关列进行分区。这样可以在查询时只扫描特定分区，提高查询效率。 3. 聚合优化：在bi_pub_hbd_res_profile_td表中，使用了GROUP BY子句进行聚合操作。如果数据量很大，可以考虑使用近似聚合函数（如APPROX_COUNT_DISTINCT）来代替COUNT(*)，以减少计算量。 4. 数据类型优化：对于表中的列，可以根据实际情况选择合适的数据类型。使用更小的数据类型可以减少存储空间和I/O操作。 5. 数据库配置优化：根据实际情况，可以调整ClickHouse数据库的配置参数，如查询并发数、内存限制等，以提高整体性能。请注意，以上建议是基于提供的信息进行的推测，实际优化方法可能需要根据具体情况进行调整和测试。
SQL2:

SELECT toStartOfMonth(toDateTime(create_mth_first_day)) AS __timestamp,
       count(DISTINCT if(upload_pass_rate < 0.3, provider_id, NULL)) AS "上传人(<30%通过率)",
       count(DISTINCT if(upload_pass_rate >= 0.3
                         and upload_pass_rate < 0.6, provider_id, NULL)) AS "上传人(30%-60%通过率)",
       count(DISTINCT if(upload_pass_rate >= 0.6, provider_id, NULL)) AS "上传人(>=60%通过率)"
FROM
  (SELECT toDate(concat(substr(create_time, 1, 7), '-01')) AS create_mth_first_day,
          '' AS stage_name,
          '' AS subject_name,
          '' AS commercial_level_extend_name,
          provider_id,
          provider_first_upload_time,
          count(DISTINCT if(status IN ('P4_1_0', 'P4_1', 'P4_2'), res_id, NULL)) AS upload_pass_cnt,
          count(DISTINCT res_id) AS upload_cnt,
          count(DISTINCT if(status IN ('P4_1_0', 'P4_1', 'P4_2'), res_id, NULL)) / count(DISTINCT res_id) AS upload_pass_rate
   FROM
     (select *,
             case
                 when stage_id = 2 then '2小学'
                 when stage_id=3 then '3初中'
                 when stage_id=4 then '4高中'
                 when stage_id=6 then '6中职'
                 else '其他'
             end AS stage_name,
             case
                 when subject_id = 1 then '01语文'
                 when subject_id=2 then '02数学'
                 when subject_id=3 then '03英语'
                 when subject_id=4 then '04物理'
                 when subject_id=5 then '05化学'
                 when subject_id=6 then '06生物'
                 when subject_id=7 then '07政治(道法)'
                 when subject_id=8 then '08历史'
                 when subject_id=9 then '09地理'
                 when subject_id=10 then '10科学'
                 when subject_id=11 then '11历史与社会(人文地理)'
                 when subject_id=12 then '12信息技术'
                 when subject_id=13 then '13音乐'
                 when subject_id=14 then '14美术'
                 when subject_id=15 then '15体育与健康'
                 when subject_id=16 then '16通用技术'
                 when subject_id=17 then '17劳动技术'
                 when subject_id=18 then '18拓展'
                 when subject_id=19 then '19心理健康'
                 when subject_id=20 then '20综合实践活动'
                 when subject_id=21 then '21日语'
                 when subject_id=22 then '22俄语'
                 when subject_id=23 then '23书法'
                 when subject_id=24 then '24社会'
                 when subject_id=25 then '25职教专业课'
                 when subject_id=26 then '26藏语'
                 else '其他'
             end AS subject_name
      from bi.bi_pub_hbd_res_profile_td
      WHERE source_application_id in ('ewt.pro',
                                      'ewt.speed',
                                      'qbm',
                                      'rbm',
                                      'xiaoxue.zxxk',
                                      'zxxk',
                                      'ewangtong',
                                      'jx.ekt',
                                      'zy.usercenter',
                                      'zxxk.paper',
                                      'zy.yfyb',
                                      'oms',
                                      'zy.ccw') )
   GROUP BY substr(create_time, 1, 7),
            stage_name,
            subject_name,
            commercial_level_extend_name,
            provider_id,
            provider_first_upload_time) AS virtual_table
WHERE create_mth_first_day >= toDate('2023-05-21')
  AND create_mth_first_day < toDate('2023-08-21')
  AND ((provider_first_upload_time < toString(create_mth_first_day)))
GROUP BY toStartOfMonth(toDateTime(create_mth_first_day))
ORDER BY "上传人(<30%通过率)" ASC
LIMIT 10000;

SQL2解释：
该SQL查询的目的是统计每个月上传资源的供应商数量，并按照上传人的通过率进行排序。查询的数据来源于bi_pub_hbd_res_profile_td表，该表包含了资源的相关信息，如创建时间、通过率等。查询的结果按照月份进行分组，并统计每个月上传资源的供应商数量，同时根据上传人的通过率进行排序。具体的查询逻辑如下： 1. 首先，从bi_pub_hbd_res_profile_td表中选择所需的列，并根据条件筛选出符合要求的数据。其中，选择了create_time、stage_id、subject_id、commercial_level_extend_name、provider_id、provider_first_upload_time等列，同时根据source_application_id进行了筛选。 2. 接着，将create_time转换为月份的第一天，并将结果命名为create_mth_first_day。同时，根据stage_id和subject_id将对应的名称转换为相应的字符串。 3. 统计每个月上传资源的供应商数量。计算通过率的方法是，统计通过审核的资源数量（status为’P4_1_0’、‘P4_1’、‘P4_2’）除以总的上传资源数量。 4. 将结果按照月份、阶段名称、学科名称、商业级别扩展名称、供应商ID和供应商首次上传时间进行分组。 5. 在虚拟表中，根据条件筛选出符合要求的数据。其中，筛选条件为create_mth_first_day大于等于’2023-05-21’且小于’2023-08-21’，并且provider_first_upload_time小于create_mth_first_day。 6. 最后，按照月份进行分组，并统计每个月上传资源的供应商数量，并按照上传人的通过率进行排序。最多返回10000条结果。总结起来，该SQL查询的目的是统计每个月上传资源的供应商数量，并按照上传人的通过率进行排序。
SQL2优化：
优化建议： 1. 在bi_pub_hbd_res_profile_td表上创建索引，以加快查询速度。 2. 考虑将子查询中的CASE语句转换为JOIN操作，以提高查询性能。 3. 考虑将子查询中的WHERE条件移至外部查询中，以减少子查询的数据量。 4. 考虑将子查询中的DISTINCT操作替换为GROUP BY操作，以提高查询效率。 5. 考虑将子查询中的COUNT(DISTINCT …)操作替换为SUM(DISTINCT …)操作，以减少计算量。 6. 考虑将子查询中的字符串拼接操作改为使用CONCAT函数，以提高性能。 7. 考虑将子查询中的IN操作替换为JOIN操作，以提高查询效率。 8. 考虑在bi_pub_hbd_res_profile_td表上创建分区，以提高查询性能。 9. 考虑将查询结果存储在临时表中，以减少查询时间。 10. 考虑使用并行查询来加快查询速度。

其他参考：
https://chatdatabase.github.io/