本文字数：11653；估计阅读时间：30 分钟

审校：庄晓东（魏庄）

本文在公众号【ClickHouseInc】首发

介绍

"简单是终级的精致。"- --列奥纳多·达·芬奇

虽然我们喜欢在 ClickHouse 为用户宣布新功能，但有时，这些看似简单和明显有价值的功能可能掩盖了实现它们所需的重大努力和复杂性。对于我们最近宣布通过 MySQL 协议支持 Looker Studio 和 Tableau online 等 BI 工具而言，这一点再合适不过了。对于用户来说，连接这些工具到 ClickHouse 的重要性是显而易见的，使他们可以使用熟悉的工具轻松构建 TB 级数据的可视化。

读者可能会想知道为什么我们选择通过 MySQL 接口支持这些工具。这个决定主要基于向用户提供产品的速度。像 Looker Studio、Quicksight 和 Tableau online 这样的在线工具不提供用户使用自定义驱动程序的选项，并且没有公开可用的 SDK。虽然我们继续与这些工具的供应商合作，开发官方的 ClickHouse 支持，但这条路不可避免地是一个长期项目。我们现有的 MySQL 支持提供了 90% 所需的支持。因此，增强这一点提供了满足用户需求并解锁这些工具与 Clickhouse 的强大功能的最短路径。

然而，支持这些工具需要 ClickHouse 内的多个团队进行一次旅程：从集成团队评估和测试这些工具与 ClickHouse 的 MySQL 接口的兼容性，到需要的 MySQL 语法支持和改进我们的代理以允许该协议在 Cloud 中使用。在本博客文章中，我们将探讨其中一些改进。

除了让我们有机会感谢更广泛的贡献之外，这个工作还突显了核心产品中的一些改进，用户可以在使用 BI 工具之外利用这些改进。具体来说，MySQL 语法的改进有望使来自 MySQL 或其他 OLTP 数据存储的新用户更顺利地采用。关于用户为什么要考虑将工作负载从 MySQL/Postgres 迁移到 ClickHouse，我们推荐最近的一篇博客文章（https://clickhouse.com/blog/migrating-data-between-clickhouse-postgres）。

一切从测试开始

在去年底发布 ClickHouse Cloud 之后，我们立即看到了对在 ClickHouse 中可视化数据的潜在需求。虽然诸如 Grafana 和 Superset 等工具提供了强大的仪表盘功能，但它们要么专注于特定用例，要么缺乏企业团队所需的功能成熟度。更重要的是，通常用户只想使用他们熟悉和高效的工具。为了满足用户的需求，我们着手评估完全支持 ClickHouse 中 MySQL 协议的工作量，目的是使诸如 Google 的 Looker Studio 和 Tableau online 等工具 "即插即用"。

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

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

虽然我们对 MySQL 语法的支持现在已经足够用于 BI 工具，但仍然存在一些未解决的问题，例如 [1 https://github.com/ClickHouse/ClickHouse/issues/53066] [2 https://github.com/ClickHouse/ClickHouse/issues/53482]。虽然我们立志提供尽可能多的兼容性，但我们不太可能保证 100% 的兼容性。试图做到这一点可能会引入不受欢迎的依赖性和行为 [1] [2]。然而，我们致力于改进我们的支持，主要是通过努力确保 ANSI SQL 差异被最小化，并且我们欢迎问题和改进。

语法增强

为了保持示例简单，我们在下面使用了受欢迎的英国房价数据集。该数据集包含从 1995 年到撰写本文时在英国出售的每套房屋的一行记录。

SHOW COLUMNS

在我们支持任何其他 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

工具还旨在识别索引，以便在可能的情况下优化查询。这需要对 SHOW KEYS 语句提供支持。进一步了解 ClickHouse 和 MySQL 的细节。

SHOW INDEXES FROM uk_price_paid
FORMAT VerticalRow 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.

NULL 安全相等

一旦克服了这些初始的 "连接查询"，就会发现 BI 工具可以生成复杂的查询。例如，对于 "top 结果"，需要使用空值安全相等运算符（也称为 IS NOT DISTINCT FROM）进行连接。在 MySQL 中，空值安全相等运算符（<=>）用于连接和比较，用于处理 NULL 值，允许将两个表达式进行比较，同时将 NULL 值视为相等。这与常规的等于运算符（=）相反，后者将 NULL 视为未知值，不能直接比较 NULL。

MAKEDATE

尽管自 23.3 版以来 ClickHouse 就可以根据年份和日期值构造日期，但 MySQL 中函数大小写的差异意味着需要在 ClickHouse 中进行别名处理。这些小差异在测试过程中被证明是常见的，而且幸运的是，通常需要进行简单的修复。

假设我们希望找到平均而言，英国房屋的最便宜购买时间。使用这个，我们试图计算这个月的平均价格，对于最贵的年份。

WITH (SELECT toYear(date) AS yearFROM uk_price_paidGROUP BY yearORDER BY avg(price) DESCLIMIT 1) AS most_expensive_year,(SELECT toMonth(date) AS monthFROM uk_price_paidGROUP BY monthORDER BY avg(price) ASCLIMIT 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

STR_TO_DATE

在使用计算列转换字符串和日期时，QuickSight 使用 MySQL 的 STR_TO_DATE 函数发出查询。ClickHouse 的 parseDateTime 函数在 23.3 版中添加了此功能，允许用户在解析字符串时指定日期模式。尽管为 STR_TO_DATE 添加了别名，但用户应注意 ClickHouse 的实现略有不同。例如，考虑下面的修改语句，用于加载英国支付价格数据集（原始文档中的插入使用 parseDateTimeBestEffortUS）。

INSERT INTO uk_price_paid
WITHsplitByChar(' ', postcode) AS p
SELECTtoUInt32(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;

字符串函数 - REGEXP & INSTR(str,substr)

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

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

关于这些函数的示例，请考虑下面的计算，在英国每个区域中最受欢迎的车道名称。

SELECTsubstring(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.

TO_DAYS

在转换 DateTime 列时，在表列类型自省阶段需要使用 TO_DAYS 函数。这需要将函数 toDaysSinceYearZero 添加到 ClickHouse 并为 TO_DAYS 添加别名。此函数提供了自元年以来的日期天数。

以下是使用此查询来计算自世纪初以来房价上涨 10% 最快的地区。

SELECTdistrict,start_price,avg_price AS final_price,TO_DAYS(final_month::Date) - TO_DAYS(buy_date::Date) AS days_taken
FROM
(SELECTCAST('2000-01-01', 'Date') AS buy_date,district,round(avg(price)) AS start_priceFROM uk_price_paidWHERE toStartOfMonth(date) = buy_dateGROUP BY district
) AS start_price
INNER JOIN
(SELECTdistrict,toStartOfMonth(date) AS final_month,round(avg(price)) AS avg_priceFROM uk_price_paidWHERE toYear(date) >= 2000GROUP BYdistrict,final_monthORDER BYdistrict 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.

DATE_FORMAT

将日期转换为格式化的字符串是分析查询中的常见需求。尽管 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，由于相同的替换改进而受益。

SELECTDATE_FORMAT(date, '%b') AS month,DATE_FORMAT(date, '%W') AS day
FROM uk_price_paid
GROUP BYmonth,day
ORDER BYSTR_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 函数的样本。其他一些示例包括：

1. MySQL 的 STD 函数现在通过别名映射到 ClickHouse 中的 stddevPop 函数。

2. 支持在 DateTime 字符串中解析小数秒。

稍微复杂一点的内容…准备好的语句！

以上大多数是简单的添加和函数别名。朝着更好的兼容性迈出的较重要的一步是准备好的语句，这是 Tableau Online 所必需的。

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

准备好的语句支持是测试中的一个最佳示例，这导致了开发的线性方法。没有实现此功能，甚至无法连接到 Tableau online 以识别其他可能的不兼容性。

我们还要感谢 PX 公司的 Mark，在测试中提供了很多帮助并提供了宝贵的指导。

我们对准备好的语句的初始实现是第一次尝试，不是 100% 完成。Tableau Online 使用没有参数的准备好的语句，在查询中硬编码所有值，而不是使用问号；尽管这种实现选择不寻常，但它确实使我们能够最初跳过参数支持。目前，准备好的语句不被解析。相关的查询在 COM_STMT_PREPARE 阶段仅在内部存储具有特定 ID，并在收到 COM_STMT_EXECUTE 命令时执行。COM_STMT_CLOSE 命令启动清理。

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

还差一点

尽管上面的语法改进使 OSS ClickHouse 用户可以使用他们喜欢的工具使用 MySQL 协议，但还需要进一步的工作才能支持我们的 Cloud 用户。ClickHouse Cloud 中 ClickHouse 实例的本机接口没有公开到互联网。这将需要每个 Cloud 实例都有自己的公共 IP 地址 - 随着用户基数的增长，这是不可行的。此外，还有一些所需的网络接口功能，ClickHouse 不支持（也许永远不会支持）如连接群集上的连接负载均衡。因此，所有通信都通过我们的由 Istio 提供支持的代理层进行路由。

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

更多选择和可能性

鉴于上述改进，对 ClickHouse 的新用户可能会被诱惑只是使用他们熟悉的 MySQL SQL 编写查询。尽管这是支持的，并提供了一个简单的迁移路径来移动应用程序，但我们仍然建议用户在资源和时间允许的情况下将查询重写为 ClickHouse 本机语法。这有两个主要动机。

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

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

在 MySQL 语法中，这可能会写成如下形式：

SELECTuk.district,ukp.date AS most_expensive_year
FROM uk_price_paid AS ukp
INNER JOIN
(SELECTdistrict,MAX(price) AS max_priceFROM uk_price_paidWHERE 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：

SELECTdistrict,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 desktop 允许用户使用自定义驱动程序。因此，我们维护了一个 ClickHouse 驱动程序，确保使用了更优化的 ClickHouse 语法。

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

致谢

这一努力需要与我们的集成和核心团队以及更广泛的 ClickHouse 社区进行大量合作。我们要感谢以下所有人为做出贡献并使这一切成为可能。

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

结论

在本博文中，我们探讨了为了支持 MySQL 语法而需要进行的更改，以使我们的用户可以使用诸如 Looker 和 Tableau online 这样的 BI 工具与 ClickHouse 一起使用。除了涵盖我们的 Cloud 代理中的改进之外，我们还为那些希望将工作负载从 MySQL 迁移到 ClickHouse 的用户提供了一些一般性的指导。对于对 Looker studio 更感兴趣的读者，我们最近的公告博文提供了更多细节。

Meetup 活动讲师招募

我们正为成都&杭州活动招募讲师，如果你有独特的技术见解、实践经验或 ClickHouse 使用故事，非常欢迎你加入我们，成为这次活动的讲师，与大家分享你的经验。

点击此处或扫描下方二维码，立刻报名成为讲师！

征稿启示

面向社区长期正文，文章内容包括但不限于关于 ClickHouse 的技术研究、项目实践和创新做法等。建议行文风格干货输出&图文并茂。质量合格的文章将会发布在本公众号，优秀者也有机会推荐到 ClickHouse 官网。请将文章稿件的 WORD 版本发邮件至：Tracy.Wang@clickhouse.com

​​联系我们

手机号：13910395701

邮箱：Tracy.Wang@clickhouse.com

满足您所有的在线分析列式数据库管理需求