如何利用ClickHouse高速汇总查询MySQL数据

直连到MySQL的功能现在已被添加到ClickHouse中。所以可以直接从ClickHouse查询MySQL表中的数据。 ClickHouse是一个超高性能的海量数据快速查询的分布式实时处理平台，在数据汇总查询方面(如GROUP BY)，ClickHouse的查询更快，因此通常情况下在MySQL上进行聚合。 下面是在自己的虚拟环境中做的测试记录。既然是虚拟环境非生产环境，请看参考应用级别，而不是严格的测试。 正确准备和测试大约1000万个数据项。 执行脚本以创建样本数据。这是约500 MB。在MySQL中创建一个表并导入它。 由于似乎字符串数据比数字值更容易汇总，所以内容是数字的，但它们是可变的类型。 然后执行计算 *** 作。 用BLOB类型做GROUPBY似乎很慢。 您可以在ClickHouse上使用MySQL数据，只需在下面的表格中指定它，而不是表名。 启动ClickHouse客户端并进行测试。 如果您在启动时不添加--multiline选项，则不会放置多行查询，因此请继续。SELECT      data1,      COUNT(*) FROM mysql('localhost','mikage','testdata','mikage','')  GROUP BY data1 ┌─data1─┬─COUNT()─┐ │    4│1999013│ │    3│1998988│ │    2│1999993│ │    5│2001553│ │    1│2000453│ └───────┴─────────┘ 5 rows in set. Elapsed: 2.685 sec. Processed 10.00 million rows,40.00 MB(3.72 million rows/s.,14.90 MB/s.)  SELECT      data2,      COUNT(*) FROM mysql('localhost','mikage','testdata','mikage','')  GROUP BY data2 ┌─data2─┬─COUNT()─┐ │6    │  999786│ │8    │1001805│ │9    │1001438│ │3    │1000357│ │2    │1000648│ │4    │  998349│ │5    │  998889│ │10    │  999424│ │1    │1000530│ │7    │  998774│ └───────┴─────────┘ 10 rows in set. Elapsed: 2.692 sec. Processed 10.00 million rows,101.00 MB(3.71 million rows/s.,37.52 MB/s.)  SELECT      data3,      COUNT(*) FROM mysql('localhost','mikage','testdata','mikage','')  GROUP BY data3 -- 结果省略 100000 rows in set. Elapsed: 5.236 sec. Processed 10.00 million rows,138.89 MB(1.91 million rows/s.,26.52 MB/s.)  SELECT      data1,      uniqExact(data5) FROM mysql('localhost','mikage','testdata','mikage','')  GROUP BY data1 ┌─data1─┬─uniqExact(data5)─┐ │    4│          1811674│ │    3│          1812072│ │    2│          1812503│ │    5│          1814106│ │    1│          1813005│ └───────┴──────────────────┘ 5 rows in set. Elapsed: 12.944 sec. Processed 10.00 million rows,198.89 MB(772.55 thousand rows/s.,15.37 MB/s.)  -- ClickHouse有一个函数来粗略计算一个唯一的数字，所以让我们来计算一下。 在MySQL中，相当耗时的查询也可以在很短的时间内处理。 重复统计时，最好将数据复制到ClickHouse一次。 如果您复制它，后续查询将更快。 建议暂时使用StripeLog引擎。 如果您有一个主键，您可能还想要使用MergeTree表。这是在ClickHouse中最常用的引擎。 有必要用ORDER BY指定数据的排序顺序（即使有重复也没有问题）。 我会尝试以前的查询。以下是StripeLog引擎的测试结果。 测试耗时总结如下: 我认为这个错误很大，因为它在VM环境下仅测试了一次 从左边开始为，(1)MySQL中的时间 (2) ClickHouse从MySQL读取和处理数据的时间 (3) 在ClickHouse上处理复制数据的时间。 执行查询 MySQL处理时间 (秒) MySQL->ClickHouse处理时间 (秒) ClickHouse处理时间 (秒) groupby(data1) 3.22 2.685 0.071 groupby(data2) 4.01 2.692 0.177 groupby(data3) 212.82 5.236 0.779 groupby(data1)+uniq(data5) 183.56 12.944 1.725 groupby(data1)+uniq(data5)概算 (无此功能) 6.026 0.285当引用MySQL数据时，如果没有对应于ClickHouse的类型，它似乎是String类型。 没有相应的类型如Decimal类型，所以它也是String类型。 如果你想把它作为一个数值，精度将会改变，但是似乎有必要在MySQL端保持Double类型。 而且，Date和DateTime类型在MySQL和ClickHouse之间的范围也是不同的。 ClickHouse日期类型是1970 - 2038年之间。 如果有超出范围的数据，可能需要使其成为字符串类型，按年份，月份，日期分列，并将其作为数值复制。 参照源码如下：https://github.com/yandex/ClickHouse/blob/9965f5e357f1be610608a51dc7a41f89c2321275/dbms/src/TableFunctions/TableFunctionMySQL.cpp#L37MySQL类型 ClickHouse 类型 参考 tinyint    UInt8 / Int8 smallint    UInt16 / Int16 int / mediumint    UInt32 / Int32 bigint    UInt64 / Int64 float    Float32 double    Float64 dateDate    有可以表达的范围差异 datetime    DateTime有可以表达的范围差异 timestamp    DateTime binaryFixed    String 除上述以外    String

在过去的几年ClickHouse一直在快速的增长，也受到大量开发者的认可，但长久以来，并没有特别趁手的工具产品来访问和管理，所以，在完成MySQL支持之后，NineData选择优先支持ClickHouse。本文将介绍，如何使用NineData帮助开发者，通过GUI的方式访问和管理ClickHouse数据库。

ClickHouse简介

ClickHouse凭借着其出色的分析查询性能，尤其是在日志处理上的优势，非常有效的解决了关系型数据库在海量数据场景下的分析查询短板问题。其语法与使用习惯也与当下最流行的数据库MySQL类似，迭代速度和对社区的响应也非常快，在过去的几年，ClickHouse收到大量开发者的认可。经过几年社区快速增长之后，在2021年ClickHouse Inc的成立也让该分析型数据库进入了开源与商业化一起发展的阶段。从目前的数据来看，未来ClickHouse还将是快速增长的分析型数据库之一。

使用NineData访问ClickHouse

整个使用过程也较为简单。首先，需要在NineData数据源页面创建ClickHouse数据源。需要注意的是：

NineData使用http/https协议访问和管理ClickHouse，所以这里需要填写对应的http/https端口（而不是Native的端口）

如果使用了SSL加密（ClickHouse Cloud的强制要求），则需要打开下面SSL加密选项（如果没有开启SSL加密，则无需打开该选项）

"接入地域"可以选择一个尽可能离服务器近一些的地域，可以适当增加访问速度

使用NineData访问ClickHouse

在填好连接信息之后，可以点击“连接测试”按钮，验证是否可以正常访问ClickHouse，如果信息都正确的话，则会提示上图右上角的“连接成功”信息。在完成数据源配置之后，就可以通过SQL窗口功能访问该数据源了。

NIneData正常访问ClickHouse

使用导航树查看数据库内部的对象

下图即为NineData所提供的ClickHouse导航对象树。较详细的展示了ClickHouse数据库内部的对象，除了常见的表对象外，NineData还提供了视图、字典、ROW POLICY等数据库对象，在服务器层面，则支持了用户、角色、QUOTA、自定义函数等对象的展示。相比于同类的产品，例如，Arctype、DBeaver、DataGrip、阿里云DMS、ClickHouse自己提供的local play等（注：目前最新的Navicat Premium 16版本还不支持ClickHouse），NineData支持是最完整，最细致的。另外，NineData导航树还支持表对象的搜索功能，可以帮助开发者方便的搜索到关注的数据表。

NineData所提供的ClickHouse导航对象树

导航树功能对比：NineData VS 其他

NineData的导航树提供了丰富而完整的数据库对象，对比常用的数据库GUI工具，NineData支持更加完整，体验更加友好。具体的对比如下：

NineData的导航树提供了丰富而完整的数

导航树功能对比：NineData VS 其他

说明：

Arctype是一个小型的数据库管理工具，前一段时间刚刚加入了ClickHouse Inc并不再更新原来的产品

local play是指ClickHouse自带的数据管理工具

DataGrid则是有JetBrains提供的数据库管理工具

DBeaver是一个第三方的开源数据库访问工具

DMS是阿里云数据库的一站式数据管理平台

智能的SQL补全提升效率

NineData提供了强大的SQL提示、补全功能，可以最大程度的帮助开发者更叫高效的完成SQL编写。例如，在如下的截图案例中，左图中，NineData会根据光标的位置和语法结构识别出，这里需要填写数据表名/视图名，则优先推荐该类对象。在右图中，NineData则通过语法结构，对象别名等信息，准确的推荐最可能匹配的列信息。

NineData提供了强大的SQL提示、补全

使用NineData保存常用的SQL

通常，对于运营人员来说，需要经常关注线上的业务数据；对于开发人员也需要经常关注线上系统的运行数据情况。NineData则可以通过“保存 SQL”功能，在线保存这些经常需要使用的SQL，用以完成一些常用数据库数据查询。

另外，NineData还支持全量的SQL运行历史保存，一方面用户可以使用该功能追溯实际行了哪些功能；也可以使用该功能，在历史SQL中找出常用的SQL。

小结

通过这篇介绍，可以看到如何使用NineData快速简单的访问与管理ClickHouse实例。

另外，NineData除了可以支持以GUI的方式访问和管理ClickHouse之外，NineData还支持MySQL到ClickHouse的数据迁移/同步/复制功能，相比ClickHouse内置的集成引擎，NineData很好的解决了上游MySQL DDL变更时的链路稳定性以及复制时对象映射等问题，可以帮助用户高性能、高效率、高稳定性的完成从MySQL到ClickHouse的数据复制。

---