简介
“简洁是极致的 Sophistication。” - 莱昂纳多·达·芬奇
尽管我们喜欢为 ClickHouse 用户发布新功能，但有时它们表面上的简洁性和清晰的价值可能会掩盖使其成为可能所付出的巨大努力和复杂性。对于我们最近发布的公告，关于通过 MySQL 协议支持 Looker Studio 和 Tableau Online 等 BI 工具，情况更是如此。将这些工具连接到 ClickHouse 对我们的用户来说有多么有价值，这一点显而易见，因为他们可以使用熟悉的工具轻松地在 TiB 级数据上构建可视化。
读者可能想知道为什么我们选择通过 MySQL 接口支持这些工具。此决策主要基于为用户提供产品的速度。Looker Studio、Quicksight 和 Tableau Online 等仅限在线的工具不为用户提供使用自定义驱动程序的选项，也没有公开可用的 SDK。虽然我们继续与这些工具的供应商合作开发官方 ClickHouse 支持，但这条路线无疑是一个更长期的项目。我们现有的 MySQL 支持提供了 90% 的所需现有支持。因此，增强此功能为满足用户的需求并释放这些工具与 Clickhouse 的强大功能提供了最短的途径。
然而，支持这些工具需要 ClickHouse 内多个团队的共同努力：从集成团队评估和测试这些工具针对 ClickHouse 的 MySQL 接口，到 ClickHouse 中支持特定 MySQL 语法所需的更改，以及后来对我们代理的改进，以允许该协议在云中使用。在这篇博文中，我们将探讨其中的一些改进。
除了给我们一个机会感谢更广泛的贡献外，这项工作还突出了核心产品中的一些改进，用户可以在 BI 工具的使用之外利用这些改进。具体而言，MySQL 语法的改进有望使来自 MySQL 或其他 OLTP 数据存储的新用户更顺利地采用。关于用户可能希望考虑将工作负载从 MySQL/Postgres 迁移到 ClickHouse 的原因，我们推荐最近的一篇博文。
一切始于测试
在去年年底宣布 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] [2]。虽然我们渴望提供尽可能多的兼容性，但我们不太可能保证 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 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.
NULL 安全相等
一旦克服了这些初始的“连接查询”，很明显 BI 工具可以生成复杂的查询。例如，对于 “热门结果”，需要使用 NULL 安全相等 运算符（也称为 IS NOT DISTINCT FROM）进行 JOIN。MySQL 中的 NULL 安全相等运算符 (<=>) 用于连接和比较以处理 NULL 值，允许您比较两个表达式，同时将 NULL 值视为彼此相等。这与常规相等运算符 (=) 形成对比，后者将 NULL 视为未知值，不能用于直接比较 NULL。
MAKEDATE
虽然自 23.3 版本以来，ClickHouse 就具备从年份和日期值构造日期的能力，但 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
STR_TO_DATE
当使用转换字符串和日期的计算列时，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;
字符串函数 - REGEXP 和 INSTR(str,substr)
Looker Studio 需要的 MySQL REGEXP 函数 提供使用正则表达式的字符串匹配功能。这与 ClickHouse 中现有的 match 运算符相当，但存在一些 主要差异 - 主要是 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.
TO_DAYS
当转换 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.
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，它也受益于相同的替换改进。
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 函数的示例。其他一些示例包括
一些更大的东西……预处理语句！
以上主要代表简单的添加和函数别名。使我们朝着更好的兼容性迈进的更繁重的工作之一是 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 实例的本机接口未公开在 Internet 上。这将要求每个 Cloud 实例都有自己的公共 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 Desktop 允许用户使用自定义驱动程序。因此，我们维护了一个 ClickHouse 驱动程序，该驱动程序确保使用更优化的 ClickHouse 语法。
如有疑问，请随时咨询并联系我们的支持组织或通过我们的公共 Slack 频道。
特别鸣谢
这项工作需要我们的集成团队和核心团队以及更广泛的 ClickHouse 社区的密切合作。我们要感谢以下所有人员的贡献，使之成为可能。
@JakeBamrah @ucasfl @rschu1ze @slvrtrn @yariks5s @vdimir @evillique @tpanetti
结论
在这篇博文中，我们探讨了为了充分支持 MySQL 语法以允许我们的用户将 Looker 和 Tableau Online 等 BI 工具与 ClickHouse 一起使用而需要进行的更改。除了介绍我们 Cloud 代理的改进外，我们还为希望从 MySQL 迁移工作负载的用户提供了一些关于何时使用此语法支持的一般指导。对于对 Looker Studio 更感兴趣的读者，我们最近发布的公告博文提供了更多详细信息。
