DoubleCloud 即将停止服务。迁移到 ClickHouse，并享受限时免费迁移服务。立即联系我们 ->->

博客 / 工程

MySQL 之旅

ClickHouse 团队

2023年10月5日

mysql journey darker path.png

“简洁是终极的复杂。” - 列奥纳多·达·芬奇

虽然我们喜欢向 ClickHouse 用户宣布新功能，但有时它们看似简单且价值明确，却掩盖了实现这些功能的一些重大努力和复杂性。对于我们最近宣布的通过 MySQL 协议支持 Looker Studio 和 Tableau 等 BI 工具，这一点再真实不过了。很明显，能够将这些工具连接到 ClickHouse 对我们的用户来说有多么宝贵，它使他们能够使用熟悉的工具在 TB 级别的数据上轻松构建可视化效果。

读者可能想知道为什么我们选择通过 MySQL 接口来支持这些工具。这一决定主要基于为用户提供服务的快速性。像 Looker Studio、Quicksight 和 Tableau 等仅在线工具不提供用户使用自定义驱动程序的选项，也没有公开可用的 SDK。虽然我们继续与这些工具的供应商合作开发官方的 ClickHouse 支持，但这始终是一个长期的项目。我们现有的 MySQL 支持提供了 90% 的现有支持需求。因此，增强它为满足用户需求并释放 ClickHouse 中这些工具的功能提供了最短的路径。

但是，支持这些工具需要 ClickHouse 内部多个团队的共同努力：从评估和测试这些工具与 ClickHouse 的 MySQL 接口的集成团队，到 ClickHouse 中需要进行的支持特定 MySQL 语法的更改，以及我们代理的后续改进，以便允许在 Cloud 中使用该协议。在这篇博文中，我们将探讨其中的一些改进。

除了让我们有机会感谢更广泛的贡献之外，这项工作还突出了核心产品的一些改进，用户可以在使用 BI 工具之外利用这些改进。具体来说，MySQL 语法的改进将有望为来自 MySQL 或其他 OLTP 数据存储的新用户带来更顺畅的采用体验。至于用户为什么可能要考虑将工作负载从 MySQL/Postgres 迁移到 ClickHouse，我们建议您阅读最近的一篇博文。

在去年年底宣布 ClickHouse Cloud 时，我们立即看到了对 ClickHouse 中数据可视化的潜在需求。虽然 Grafana 和 Superset 等工具提供了强大的仪表板功能，但它们要么侧重于特定用例，要么缺乏企业团队所需的特性成熟度。更重要的是，用户通常只想使用他们熟悉且高效的工具。本着满足用户需求的精神，我们着手评估在 ClickHouse 中更完整地支持 MySQL 协议的工作量，目的是使 Google 的 Looker Studio 和 Tableau 等工具“正常工作”。

截至撰写本文时，我们通过 MySQL 协议支持 Looker Studio 和 Tableau Online。正在进行的其他改进允许用户使用 AWS QuickSight。

为了实现这一点，需要进行一段时间的测试。由于 Looker 等工具通过查询构建器和用户交互（例如，应用过滤器）生成 SQL，因此这些测试工作发现 MySQL 语法支持存在重大差距。这被证明是一项重大且令人沮丧的工作，因为通常对表达式或结构缺乏支持会导致工具无法使用，从而暂停进一步的测试，直到问题解决。一旦解决，就会出现更多问题，没有明确的结束时间，这种形式的测试感觉像是一个无休止的递归循环。幸运的是，在 ClickHouse 核心团队和集成团队多次增强和协作之后，我们很高兴地宣布支持 ClickHouse OSS。下面我们将探讨其中一些增强功能，展示现在可能实现的功能。

虽然我们对 MySQL 语法的支持现在足以满足 BI 工具的需求，但仍然存在一些未解决的问题，例如[1] [2]。虽然我们渴望提供尽可能多的兼容性，但我们不太可能保证 100% 的兼容性。尝试这样做可能会引入不希望有的依赖项和行为[1] [2]。但是，我们致力于改进我们的支持，主要通过努力确保最大程度地减少 ANSI SQL 的差异，我们欢迎问题和改进。

为了使示例保持简单，我们使用了下面流行的英国房价数据集。它包含 1995 年至今在英国出售的每一套房子的记录。

在我们可以支持任何其他 MySQL 查询语法之前，需要支持基本的 DDL 发现操作。这些查询通常由 BI 工具在初始连接时作为架构和索引发现练习的一部分发出。第一个是SHOW COLUMNS，它允许发现表列。更多ClickHouse 和MySQL 细节。

SHOW COLUMNS FROM uk_price_paid FROM default LIKE'%'

┌─field─────┬─type────────────────────────────────────────────────────────────────────────────────┬─null─┬─key─────┬─default─┬─extra─┐
│ addr1 	│ String                                                                          	  │ NO   │ PRI SOR │ ᴺᵁᴸᴸ	 │   	 │
│ addr2 	│ String                                                                          	  │ NO   │ PRI SOR │ ᴺᵁᴸᴸ	 │   	 │
│ county	│ LowCardinality(String)                                                          	  │ NO   │     	   │ ᴺᵁᴸᴸ	 │   	 │
│ date  	│ Date                                                                            	  │ NO   │     	   │ ᴺᵁᴸᴸ	 │   	 │
│ district  │ LowCardinality(String)                                                          	  │ NO   │     	   │ ᴺᵁᴸᴸ	 │   	 │
│ duration  │ Enum8('unknown' = 0, 'freehold' = 1, 'leasehold' = 2)                           	  │ NO   │     	   │ ᴺᵁᴸᴸ	 │   	 │
│ is_new	│ UInt8                                                                           	  │ NO   │     	   │ ᴺᵁᴸᴸ	 │   	 │
│ locality  │ LowCardinality(String)                                                          	  │ NO   │     	   │ ᴺᵁᴸᴸ	 │   	 │
│ postcode1 │ LowCardinality(String)                                                          	  │ NO   │ PRI SOR │ ᴺᵁᴸᴸ	 │   	 │
│ postcode2 │ LowCardinality(String)                                                          	  │ NO   │ PRI SOR │ ᴺᵁᴸᴸ	 │   	 │
│ price 	│ UInt32                                                                          	  │ NO   │     	   │ ᴺᵁᴸᴸ	 │   	 │
│ street	│ LowCardinality(String)                                                          	  │ NO   │     	   │ ᴺᵁᴸᴸ	 │   	 │
│ town  	│ LowCardinality(String)                                                          	  │ NO   │     	   │ ᴺᵁᴸᴸ	 │   	 │
│ type  	│ Enum8('other' = 0, 'terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4) │ NO   │     	   │ ᴺᵁᴸᴸ	 │   	 │
└───────────┴─────────────────────────────────────────────────────────────────────────────────────┴──────┴─────────┴─────────┴───────┘

14 rows in set. Elapsed: 0.009 sec.

工具还旨在识别索引，以确保在可能的情况下优化查询。这需要支持SHOW KEYS 语句。更多ClickHouse 和MySQL 细节。

SHOW INDEXES FROM uk_price_paid
FORMAT Vertical

Row 1:
──────
table:     	uk_price_paid
non_unique:	1
key_name:  	PRIMARY
seq_in_index:  1
column_name:   addr1
collation: 	A
cardinality:   0
sub_part:  	ᴺᵁᴸᴸ
packed:    	ᴺᵁᴸᴸ
null:      	ᴺᵁᴸᴸ
index_type:	PRIMARY
comment:
index_comment:
visible:   	YES
expression:

4 rows in set. Elapsed: 0.007 sec.

一旦克服了这些初始的“连接查询”，很明显 BI 工具可以生成复杂的查询。例如，对于“顶级结果”，需要使用空值安全等于运算符（也称为IS NOT DISTINCT FROM）进行 JOIN。MySQL 中的空值安全等于运算符 (<=>) 用于连接和比较以处理空值，允许您比较两个表达式，同时将空值视为彼此相等。这与常规等于运算符 (=) 形成对比，后者将空值视为未知值，不能直接用于比较空值。

虽然从年和日值构造日期的功能在 ClickHouse 中已经存在从 23.3 版本开始，但 MySQL 中函数大小写不敏感的差异意味着这需要在 ClickHouse 中使用别名。这些细微的差异在测试过程中很常见，幸运的是，通常只需要简单的修复。

假设我们希望找到房屋平均购买价格最低的月份。使用此信息，我们将查看最昂贵年份的这个月的平均价格。

WITH (
    	SELECT toYear(date) AS year
    	FROM uk_price_paid
    	GROUP BY year
    	ORDER BY avg(price) DESC
    	LIMIT 1
	) AS most_expensive_year,
	(
    	SELECT toMonth(date) AS month
    	FROM uk_price_paid
    	GROUP BY month
    	ORDER BY avg(price) ASC
    	LIMIT 1
	) AS cheapest_month
SELECT round(avg(price))
FROM uk_price_paid
WHERE date = MAKEDATE(most_expensive_year, cheapest_month)

┌─round(avg(price))─┐
│        	499902  │
└───────────────────┘

1 row in set. Elapsed: 0.173 sec. Processed 85.49 million rows, 409.36 MB (493.15 million rows/s., 2.36 GB/s.)
Peak memory usage: 264.63 MiB

当使用将字符串和日期进行转换的计算列时，QuickSight 会发出包含 MySQL 的STR_TO_DATE 函数的查询。ClickHouse 中等效的 parseDateTime 函数是在 23.3 版本中添加的，它允许用户在解析字符串时指定日期模式。虽然已经为 STR_TO_DATE添加了别名，但用户需要注意 ClickHouse 的实现略有不同。例如，考虑下面用于加载英国房价数据集的修改后的语句（原始文档中的插入使用parseDateTimeBestEffortUS）。

INSERT INTO uk_price_paid
WITH
  splitByChar(' ', postcode) AS p
SELECT
   toUInt32(price_string) AS price,
   STR_TO_DATE(time, '%Y-%m-%d 00:00') AS date,
   --parseDateTimeBestEffortUS(time) AS date,
   p[1] AS postcode1,
   p[2] AS postcode2,
   transform(a, ['T', 'S', 'D', 'F', 'O'], ['terraced', 'semi-detached', 'detached', 'flat', 'other']) AS type,
   b = 'Y' AS is_new,
   transform(c, ['F', 'L', 'U'], ['freehold', 'leasehold', 'unknown']) AS duration, addr1, addr2, street, locality, town, district, county
FROM url('http://prod.publicdata.landregistry.gov.uk.s3-website-eu-west-1.amazonaws.com/pp-complete.csv', 'CSV', 'uuid_string String, price_string String, time String, postcode String, a String, b String, c String, addr1 String, addr2 String, street String, locality String, town String, district String, county String, d String, e String'
) SETTINGS max_http_get_redirects=10;

Looker Studio 所需的 MySQL REGEXP 函数提供了使用正则表达式的字符串匹配功能。这与 ClickHouse 中现有的匹配运算符类似，但存在一些关键差异 - 主要是 ClickHouse 使用re2 库而不是MySQL 的 ICU，这导致语法支持存在差异。

INSTR 函数提供了返回子字符串第一次出现索引的功能。在 ClickHouse 中实现此功能需要一个简单的等效 positionCaseInsensitive 函数的别名。

有关这些函数的示例，请考虑以下内容，它计算了英国最受欢迎的车道名称。

SELECT
  substring(street, 1, INSTR(street, 'LANE') - 2) AS lane,
  count(*) AS c
FROM uk_price_paid
WHERE street REGEXP '.*\\sLANE'
GROUP BY lane
ORDER BY c DESC
LIMIT 10

┌─lane───┬─────c─┐
│ CHURCH │ 35470 │
│ GREEN  │ 30077 │
│ MILL   │ 25847 │
│ SCHOOL │ 17642 │
│ PARK   │ 17099 │
│ CHAPEL │ 12407 │
│ SANDY  │ 10857 │
│ LONG   │  8888 │
│ BACK   │  8820 │
│ WOOD   │  7979 │
└────────┴───────┘

10 rows in set. Elapsed: 0.086 sec.

在转换 DateTime 列时，在表列类型内省阶段，需要 TO_DAYS 函数。这需要在 ClickHouse 中添加函数toDaysSinceYearZero 和 TO_DAYS 的别名。此函数提供了自公元 0 年以来日期的天数。

下面我们使用此查询来计算从本世纪初开始房价增长 10% 最快的地区。

SELECT
	district,
	start_price,
	avg_price AS final_price,
	TO_DAYS(final_month::Date) - TO_DAYS(buy_date::Date) AS days_taken
FROM
(
	SELECT
    	CAST('2000-01-01', 'Date') AS buy_date,
    	district,
    	round(avg(price)) AS start_price
	FROM uk_price_paid
	WHERE toStartOfMonth(date) = buy_date
	GROUP BY district
) AS start_price
INNER JOIN
(
	SELECT
    	district,
    	toStartOfMonth(date) AS final_month,
    	round(avg(price)) AS avg_price
	FROM uk_price_paid
	WHERE toYear(date) >= 2000
	GROUP BY
    	district,
    	final_month
	ORDER BY
    	district ASC,
    	final_month ASC
) AS over_time ON over_time.district = start_price.district
WHERE (avg_price * 0.1) >= start_price
ORDER BY days_taken ASC
LIMIT 1 BY district
LIMIT 10

┌─district──────────────────┬─start_price─┬─final_price─┬─days_taken─┐
│ SOMERSET WEST AND TAUNTON │  	133667    │ 	1350000 │    	305  │
│ SOMERSET              	│   56500     │  	685000  │   	1796 │
│ CITY OF LONDON        	│  	245099    │ 	7811526 │   	5053 │
│ HILLINGDON            	│  	125364    │ 	1401759 │   	5083 │
│ BASILDON              	│   97028     │ 	1840461 │   	5114 │
│ BUCKINGHAMSHIRE       	│  	201000    │ 	2346333 │   	5234 │
│ TRAFFORD              	│  	100800    │ 	1024667 │   	5265 │
│ MANCHESTER            	│   55875     │  	579583  │   	5479 │
│ IPSWICH               	│   62656     │  	876525  │   	5538 │
│ RUSHMOOR              	│  	109029    │ 	1285046 │   	5569 │
└───────────────────────────┴─────────────┴─────────────┴────────────┘

10 rows in set. Elapsed: 0.127 sec. Processed 28.50 million rows, 227.79 MB (223.69 million rows/s., 1.79 GB/s.)
Peak memory usage: 53.59 MiB.

将日期转换为格式化字符串是在分析查询中常见的需求。虽然 ClickHouse 通过formatDateTime 函数支持此功能，但 MySQL 语法公开了 Looker Studio 使用的 DATE_FORMAT 函数。除了需要一个简单的别名之外，MySQL 还支持其他格式替换（“a”、“b”、“c”、“h”、“i”、“k”、“l” “r”、“s”、“W”）。通过确保这些格式在 DATE_FORMAT 函数中得到支持，使用formatDateTime 的 ClickHouse 用户也能从中受益。在下面的示例中，我们计算了每一年中每个月购买房屋最受欢迎的一天。请注意我们如何重用前面描述的 STR_TO_DATE，它也受益于相同的替换改进。

SELECT
    DATE_FORMAT(date, '%b') AS month,
    DATE_FORMAT(date, '%W') AS day
FROM uk_price_paid
GROUP BY
    month,
    day
ORDER BY
    STR_TO_DATE(month, '%b') ASC,
    count() DESC
LIMIT 1 BY month

┌─month─┬─day────┐
│ Jan   │ Friday │
│ Feb   │ Friday │
│ Mar   │ Friday │
│ Apr   │ Friday │
│ May   │ Friday │
│ Jun   │ Friday │
│ Jul   │ Friday │
│ Aug   │ Friday │
│ Sep   │ Friday │
│ Oct   │ Friday │
│ Nov   │ Friday │
│ Dec   │ Friday │
└───────┴────────┘

12 rows in set. Elapsed: 0.205 sec. Processed 28.57 million rows, 57.14 MB (139.69 million rows/s., 279.37 MB/s.)
Peak memory usage: 1.31 MiB.

这只是我们兼容性工作中添加的许多 MySQL 函数的一个示例。其他一些示例包括

现在，MySQL STD 函数通过stddevPop 函数的别名在 ClickHouse 中映射。
支持在 DateTime 字符串中解析小数秒%f。

以上内容主要代表简单的添加和函数别名。为了实现更好的兼容性，我们进行的一项更重大的工作是预处理语句，Tableau Online 需要此功能。

我们集成团队的 Serge 决定撸起袖子，首次为 ClickHouse 做出贡献，并在核心团队的 Robert Schulze 的指导下，花费数天时间阅读 MySQL 服务器源代码、Go、Java 和 Rust 客户端驱动程序（官方协议文档有时不够清晰）。经过几轮审查，他完成了第一版。

预处理语句支持是测试中“阻碍因素”的最佳示例之一，导致开发采用线性方法。如果没有实现此功能，甚至无法连接到 Tableau Online 以识别其他可能的兼容性问题。

我们还要感谢来自 PX 的 Mark 的努力，他在测试方面提供了很大帮助，并提供了宝贵的指导。

我们对预处理语句的初始实现代表了第一版，尚未完全完成。Tableau Online 使用没有参数的预处理语句，将所有值硬编码到查询中，而不是使用问号；虽然这种实现选择并不常见，但它确实让我们最初能够跳过参数支持。目前，预处理语句不会被解析。关联的查询在 COM_STMT_PREPARE 阶段仅使用特定的 ID 在内部存储，并在收到 COM_STMT_EXECUTE 命令时执行。COM_STMT_CLOSE 命令启动清理。

然而，主要的挑战不是缺少命令支持 - 而是与处理 MySQL 接口中大多数“常规”查询的 COM_QUERY 不同，COM_STMT_EXECUTE 使用二进制协议而不是文本协议进行响应，并且它比其文本对应物复杂得多，并且在实现（不）准确性方面宽容度要低得多。

虽然上述语法改进使 OSS ClickHouse 用户能够使用他们喜欢的工具使用 MySQL 协议，但为了增强我们的云用户的能力，还需要进一步的工作。ClickHouse Cloud 中 ClickHouse 实例的本机接口不会公开暴露到互联网。这将要求每个云实例都有自己的公共 IP 地址 - 随着用户群的增长，这是不可行的。此外，还有一些所需的网络接口功能，ClickHouse 并不（也可能永远不会）支持，例如跨集群连接的负载均衡。出于这些原因，所有通信都通过我们由 Istio 提供支持的代理层进行路由。

为了在我们的 Istio 代理中完全支持 MySQL，我们也遇到了一些挑战。连接后，服务器必须发送第一个数据包，然后将连接升级到 TLS。我们更改了 Istio 代理，使其能够协调此类 TLS 升级过程。此外，许多 MySQL 客户端在 TLS 握手过程中不会发送SNI 信息，这对于进行路由决策是必需的。为了解决这个问题，我们在 mysql4<subdomain> 格式中创建了专用的数据库用户，其中用户名后缀是服务域名前缀。然后，代理可以在握手过程中提取此信息（用户名可用），并将其进一步传播以决定路由到哪个 ClickHouse 实例。

鉴于上述改进，ClickHouse 的新用户可能会倾向于简单地使用他们熟悉的 MySQL SQL 编写查询。虽然这得到了支持，并为迁移应用程序提供了简单的迁移路径，但我们仍然建议用户在资源和时间允许的情况下使用 ClickHouse 本机语法重写查询。这样做的主要动机有两个。

首先，ClickHouse 的分析函数通常允许以更简单的方式编写查询。

考虑以下结构：我们需要找到一个列的值，给定另一个不在 GROUP BY 中的列的最大值。更具体地说，对于房屋价格数据集，假设我们希望找到每个伦敦地区的最高价房屋的出售年份。

在 MySQL 语法中，这可能写成如下所示

SELECT
	uk.district,
	ukp.date AS most_expensive_year
FROM uk_price_paid AS ukp
INNER JOIN
(
	SELECT
    	district,
    	MAX(price) AS max_price
	FROM uk_price_paid
	WHERE town = 'LONDON'
	GROUP BY district
) AS uk ON (ukp.district = uk.district) AND (ukp.price = uk.max_price)
WHERE town = 'LONDON'
ORDER BY uk.district ASC
LIMIT 10

┌─uk.district──────────┬─most_expensive_year─┐
│ BARKING AND DAGENHAM │      	2016-12-14   │
│ BARNET           	   │      	2017-10-31   │
│ BEXLEY           	   │      	2014-07-17   │
│ BRENT            	   │      	2022-03-25   │
│ BROMLEY          	   │      	2019-08-09   │
│ CAMDEN           	   │      	2022-04-22   │
│ CITY OF BRISTOL  	   │      	2020-01-06   │
│ CITY OF LONDON   	   │      	2019-04-04   │
│ CITY OF WESTMINSTER  │      	2017-07-31   │
│ CROYDON          	   │      	2021-03-29   │
└──────────────────────┴─────────────────────┘

10 rows in set. Elapsed: 0.098 sec. Processed 56.99 million rows, 275.41 MB (580.03 million rows/s., 2.80 GB/s.)
Peak memory usage: 871.14 MiB.

在 ClickHouse 中，argMax 列大大简化了这一点，避免了我们的 INNER JOIN

SELECT
	district,
	CAST(argMax(date, price), 'Date') AS most_expensive_year
FROM uk_price_paid
WHERE town = 'LONDON'
GROUP BY district
ORDER BY district ASC
LIMIT 10

┌─district─────────────┬─most_expensive_year─┐
│ BARKING AND DAGENHAM │      	2016-12-14   │
│ BARNET           	   │      	2017-10-31   │
│ BEXLEY           	   │      	2014-07-17   │
│ BRENT            	   │      	2022-03-25   │
│ BROMLEY          	   │      	2019-08-09   │
│ CAMDEN           	   │      	2022-04-22   │
│ CITY OF BRISTOL  	   │      	2020-01-06   │
│ CITY OF LONDON   	   │      	2019-04-04   │
│ CITY OF WESTMINSTER  │        2017-07-31   │
│ CROYDON              │        2021-03-29   │
└──────────────────────┴─────────────────────┘

10 rows in set. Elapsed: 0.047 sec. Processed 28.50 million rows, 53.30 MB (603.85 million rows/s., 1.13 GB/s.)
Peak memory usage: 420.94 MiB.

除了使此类分析查询更易于编写之外，这些函数通常还允许 ClickHouse 更有效地执行查询，如执行时间和内存时间所示。

在某些情况下，用户将可以选择使用工具的本机 ClickHouse 集成，或者简单地恢复到 MySQL 接口和工具的现有驱动程序。在大多数情况下，我们建议用户尽可能使用前者，原因与编写查询时概述的原因相同 - 本机集成通常会为 ClickHouse 编写更有效的查询。

例如，考虑 Tableau。由于 Tableau Online 不允许在没有与供应商进行长时间合作的情况下贡献驱动程序，因此连接到 MySQL 代表了释放用户功能的最佳途径。相反，更传统的 Tableau 桌面允许用户使用自定义驱动程序。因此，我们维护了一个 ClickHouse 驱动程序，以确保使用更优化的 ClickHouse 语法。

如有疑问，请随时询问并联系我们的支持组织或通过我们的公共 Slack 频道。

这项工作需要与我们的集成和核心团队以及更广泛的 ClickHouse 社区进行大量合作。我们要感谢所有为实现这一目标做出贡献的人。

@JakeBamrah @ucasfl @rschu1ze @slvrtrn @yariks5s @vdimir @evillique @tpanetti

在这篇博文中，我们探讨了为充分支持 MySQL 语法而需要进行的更改，以便我们的用户能够使用 Looker 和 Tableau Online 等 BI 工具与 ClickHouse 协同工作。除了介绍云代理方面的改进之外，我们还为希望从 MySQL 迁移工作负载的用户提供了一些关于何时使用此语法支持的通用指导。对于对 Looker Studio 更感兴趣的读者，我们最近发布的公告博客文章提供了更多详细信息。

立即开始使用 ClickHouse Cloud 并获得 300 美元的积分。在 30 天试用期结束时，继续使用按需付费计划，或联系我们了解有关我们基于容量的折扣的更多信息。请访问我们的定价页面了解更多详情。

分享此文章