BigQuery 与 ClickHouse Cloud：等效和不同的概念
资源组织
ClickHouse Cloud 中资源的组织方式类似于 BigQuery 的资源层次结构。我们在下面根据显示 ClickHouse Cloud 资源层次结构的以下图表描述了具体的差异
组织
与 BigQuery 类似，组织是 ClickHouse 云资源层次结构中的根节点。您在 ClickHouse Cloud 帐户中设置的第一个用户会自动分配给该用户拥有的组织。该用户可以邀请其他用户加入该组织。
BigQuery 项目与 ClickHouse Cloud 服务
在组织内，您可以创建大致相当于 BigQuery 项目的服务，因为 ClickHouse Cloud 中存储的数据与服务相关联。ClickHouse Cloud 中有 几种可用的服务类型。每个 ClickHouse Cloud 服务都部署在特定区域，并且包括
- 一组计算节点（目前，开发层服务为 2 个节点，生产层服务为 3 个节点）。对于这些节点，ClickHouse Cloud 支持垂直和水平扩展，手动和自动两种方式。
- 一个对象存储文件夹，服务将所有数据存储在此文件夹中。
- 一个端点（或通过 ClickHouse Cloud UI 控制台创建的多个端点） - 用于连接到服务的服务 URL（例如，
https://dv2fzne24g.us-east-1.aws.clickhouse.cloud:8443）
BigQuery 数据集与 ClickHouse Cloud 数据库
ClickHouse 将表逻辑地分组到数据库中。与 BigQuery 数据集类似，ClickHouse 数据库是用于组织和控制对表数据访问的逻辑容器。
BigQuery 文件夹
ClickHouse Cloud 目前没有等效于 BigQuery 文件夹的概念。
BigQuery 时段预留和配额
与 BigQuery 时段预留类似，您可以在 ClickHouse Cloud 中 配置垂直和水平自动扩展。对于垂直自动扩展，您可以为服务的计算节点的内存和 CPU 内核设置最小和最大大小。然后，服务将在这些范围内根据需要进行扩展。这些设置在初始服务创建流程中也可用。服务中的每个计算节点都具有相同的大小。您可以使用 水平扩展更改服务内的计算节点数量。
此外，与 BigQuery 配额类似，ClickHouse Cloud 提供并发控制、内存使用限制和 I/O 调度，使用户能够将查询隔离到工作负载类别中。通过为特定工作负载类别设置共享资源（CPU 内核、DRAM、磁盘和网络 I/O）的限制，确保这些查询不会影响其他关键业务查询。并发控制可防止在并发查询数量较多的情况下线程过度订阅。
ClickHouse 在服务器、用户和查询级别跟踪内存分配的字节大小，从而允许灵活的内存使用限制。内存超额分配使查询能够使用超出保证内存的额外可用内存，同时确保其他查询的内存限制。此外，可以限制聚合、排序和连接子句的内存使用量，在超过内存限制时允许回退到外部算法。
最后，I/O 调度允许用户根据最大带宽、进行中的请求和策略限制工作负载类别的本地和远程磁盘访问。
权限
ClickHouse Cloud 控制用户访问 在两个位置，通过 云控制台 和通过数据库。控制台访问通过 clickhouse.cloud 用户界面进行管理。数据库访问通过数据库用户帐户和角色进行管理。此外，可以向控制台用户授予数据库中的角色，使控制台用户能够通过我们的 SQL 控制台 与数据库交互。
数据类型
ClickHouse 在数值方面提供了更细粒度的精度。例如，BigQuery 提供了数值类型 INT64、NUMERIC、BIGNUMERIC 和 FLOAT64。将其与 ClickHouse 进行对比，ClickHouse 为十进制、浮点数和整数提供了多种精度。使用这些数据类型，ClickHouse 用户可以优化存储和内存开销，从而加快查询速度并降低资源消耗。下面我们为每个 BigQuery 类型映射等效的 ClickHouse 类型
在 ClickHouse 类型有多个选项的情况下，请考虑数据的实际范围并选择最低所需的范围。此外，请考虑使用 适当的编解码器 以进行进一步压缩。
查询加速技术
主键和外键以及主键索引
在 BigQuery 中，表可以具有 主键和外键约束。通常，主键和外键用于关系数据库以确保数据完整性。主键值通常对于每一行都是唯一的，并且不为 NULL。每一行中的每个外键值都必须存在于主键表的相应主键列中，或者为 NULL。在 BigQuery 中，这些约束不会被强制执行，但查询优化器可能会使用此信息来更好地优化查询。
在 ClickHouse 中，表也可以具有主键。与 BigQuery 类似，ClickHouse 不会强制执行表的主键列值的唯一性。与 BigQuery 不同，表的数据存储在磁盘上， 按主键列排序。查询优化器利用此排序顺序来防止重新排序，最大程度地减少联接的内存使用量，以及为 limit 子句启用短路。与 BigQuery 不同，ClickHouse 会根据主键列值自动创建 （稀疏）主键索引。此索引用于加速包含主键列过滤器的所有查询。ClickHouse 目前不支持外键约束。
辅助索引（仅在 ClickHouse 中可用）
除了根据表的主键列值创建的主索引外，ClickHouse 还允许您在主键以外的列上创建二级索引。ClickHouse 提供了几种类型的二级索引，每种索引都适合不同的查询类型。
- 布隆过滤器索引:
- 用于加速包含等值条件（例如，=，IN）的查询。
- 使用概率数据结构来确定某个值是否存在于数据块中。
- 分词布隆过滤器索引:
- 类似于布隆过滤器索引，但用于分词字符串，适用于全文搜索查询。
- 最小-最大索引:
- 维护每个数据部分中列的最小值和最大值。
- 有助于跳过读取不属于指定范围的数据部分。
搜索索引
类似于 BigQuery 中的搜索索引，可以在 ClickHouse 表上为具有字符串值的列创建全文索引。
向量索引
BigQuery 最近引入了向量索引 作为预发布功能。同样，ClickHouse 对加速向量搜索用例的索引 也有实验性支持。
分区
与 BigQuery 一样，ClickHouse 使用表分区来提高大型表的性能和可管理性，方法是将表划分为更小、更易于管理的部分，称为分区。我们在这里详细描述了 ClickHouse 分区here。
聚类
通过聚类，BigQuery 会根据几个指定列的值自动对表数据进行排序，并将它们放在最佳大小的块中。聚类提高了查询性能，使 BigQuery 能够更好地估计运行查询的成本。使用聚类列，查询还可以消除对不必要数据的扫描。
在 ClickHouse 中，数据会根据表的磁盘上的主键列自动聚类，并在逻辑上组织成块，这些块可以通过利用主索引数据结构的查询快速定位或修剪。
物化视图
BigQuery 和 ClickHouse 都支持物化视图——基于转换查询针对基本表的结果的预计算结果，以提高性能和效率。
查询物化视图
可以直接查询 BigQuery 物化视图，或者由优化器用于处理对基本表的查询。如果对基本表的更改可能使物化视图失效，则会直接从基本表读取数据。如果对基本表的更改不会使物化视图失效，则其余数据将从物化视图中读取，并且仅从基本表中读取更改。
在 ClickHouse 中，只能直接查询物化视图。但是，与 BigQuery（其中物化视图在基本表发生更改后 5 分钟内自动刷新，但频率不高于每 30 分钟）相比，物化视图始终与基本表同步。
更新物化视图
BigQuery 通过对基本表运行视图的转换查询来定期完全刷新物化视图。在刷新之间，BigQuery 将物化视图的数据与新的基本表数据相结合，以提供一致的查询结果，同时仍然使用物化视图。
在 ClickHouse 中，物化视图是增量更新的。这种增量更新机制提供了高可扩展性和低计算成本：增量更新的物化视图专为基本表包含数十亿或数万亿行的情况而设计。ClickHouse 不会重复查询不断增长的基本表以刷新物化视图，而是仅从新插入的基本表行的值计算部分结果。此部分结果会在后台与之前计算的部分结果增量合并。与从整个基本表重复刷新物化视图相比，这大大降低了计算成本。
事务
与 ClickHouse 相比，BigQuery 支持在单个查询内或在使用会话时跨多个查询进行多语句事务。多语句事务允许您执行更改操作（例如，在一个或多个表上插入或删除行），并以原子方式提交或回滚更改。多语句事务已列入ClickHouse 2024 年路线图。
聚合函数
与 BigQuery 相比，ClickHouse 附带了更多内置聚合函数。
- BigQuery 附带18 个聚合函数 和4 个近似聚合函数。
- ClickHouse 拥有超过150 个预构建聚合函数，以及强大的聚合组合器，用于扩展预构建聚合函数的行为。例如，您可以简单地通过使用-Array 后缀 调用它们，将 150 多个预构建聚合函数应用于数组而不是表行。使用-Map 后缀，您可以将任何聚合函数应用于映射。使用-ForEach 后缀，您可以将任何聚合函数应用于嵌套数组。
数据源和文件格式
与 BigQuery 相比，ClickHouse 支持更多文件格式和数据源。
- ClickHouse 原生支持从几乎任何数据源加载 90 多种文件格式的数据。
- BigQuery 支持 5 种文件格式和 19 个数据源。
SQL 语言特性
ClickHouse 提供标准 SQL，并带有许多扩展和改进，使其更适合分析任务。例如，ClickHouse SQL支持 lambda 函数 和高阶函数，因此在应用转换时无需展开/爆炸数组。这是优于 BigQuery 等其他系统的巨大优势。
数组
与 BigQuery 的 8 个数组函数相比，ClickHouse 拥有超过 80 个内置数组函数，可以优雅而简单地对各种问题进行建模和解决。
ClickHouse 中的典型设计模式是使用groupArray 聚合函数将表的特定行值（临时）转换为数组。然后可以通过数组函数方便地处理它，并且结果可以通过arrayJoin 聚合函数转换回单个表行。
由于 ClickHouse SQL 支持高阶 lambda 函数，因此许多高级数组操作可以通过简单地调用其中一个高阶内置数组函数来实现，而无需像 BigQuery 中那样经常需要 将数组临时转换回表，例如用于过滤 或压缩 数组。在 ClickHouse 中，这些操作只是高阶函数arrayFilter 和arrayZip 的简单函数调用。
在下文中，我们提供了从 BigQuery 到 ClickHouse 的数组操作映射。
| BigQuery | ClickHouse |
|---|---|
| ARRAY_CONCAT | arrayConcat |
| ARRAY_LENGTH | length |
| ARRAY_REVERSE | arrayReverse |
| ARRAY_TO_STRING | arrayStringConcat |
| GENERATE_ARRAY | range |
为子查询中的每一行创建一个包含一个元素的数组
BigQuery
SELECT ARRAY
(SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3) AS new_array;
/*-----------*
| new_array |
+-----------+
| [1, 2, 3] |
*-----------*/
ClickHouse
groupArray 聚合函数
SELECT groupArray(*) AS new_array
FROM
(
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
)
┌─new_array─┐
1. │ [1,2,3] │
└───────────┘
将数组转换为一组行
BigQuery
UNNEST 运算符
SELECT *
FROM UNNEST(['foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred'])
AS element
WITH OFFSET AS offset
ORDER BY offset;
/*----------+--------*
| element | offset |
+----------+--------+
| foo | 0 |
| bar | 1 |
| baz | 2 |
| qux | 3 |
| corge | 4 |
| garply | 5 |
| waldo | 6 |
| fred | 7 |
*----------+--------*/
ClickHouse
ARRAY JOIN 子句
WITH ['foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred'] AS values
SELECT element, num-1 AS offset
FROM (SELECT values AS element) AS subquery
ARRAY JOIN element, arrayEnumerate(element) AS num;
/*----------+--------*
| element | offset |
+----------+--------+
| foo | 0 |
| bar | 1 |
| baz | 2 |
| qux | 3 |
| corge | 4 |
| garply | 5 |
| waldo | 6 |
| fred | 7 |
*----------+--------*/
返回一个日期数组
BigQuery
SELECT GENERATE_DATE_ARRAY('2016-10-05', '2016-10-08') AS example;
/*--------------------------------------------------*
| example |
+--------------------------------------------------+
| [2016-10-05, 2016-10-06, 2016-10-07, 2016-10-08] |
*--------------------------------------------------*/
ClickHouse
SELECT arrayMap(x -> (toDate('2016-10-05') + x), range(toUInt32((toDate('2016-10-08') - toDate('2016-10-05')) + 1))) AS example
┌─example───────────────────────────────────────────────┐
1. │ ['2016-10-05','2016-10-06','2016-10-07','2016-10-08'] │
└───────────────────────────────────────────────────────┘
返回一个时间戳数组
BigQuery
SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-07 00:00:00',
INTERVAL 1 DAY) AS timestamp_array;
/*--------------------------------------------------------------------------*
| timestamp_array |
+--------------------------------------------------------------------------+
| [2016-10-05 00:00:00+00, 2016-10-06 00:00:00+00, 2016-10-07 00:00:00+00] |
*--------------------------------------------------------------------------*/
ClickHouse
SELECT arrayMap(x -> (toDateTime('2016-10-05 00:00:00') + toIntervalDay(x)), range(dateDiff('day', toDateTime('2016-10-05 00:00:00'), toDateTime('2016-10-07 00:00:00')) + 1)) AS timestamp_array
Query id: b324c11f-655b-479f-9337-f4d34fd02190
┌─timestamp_array─────────────────────────────────────────────────────┐
1. │ ['2016-10-05 00:00:00','2016-10-06 00:00:00','2016-10-07 00:00:00'] │
└─────────────────────────────────────────────────────────────────────┘
过滤数组
BigQuery
需要通过UNNEST 运算符将数组临时转换为表
WITH Sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT [5, 10] AS some_numbers)
SELECT
ARRAY(SELECT x * 2
FROM UNNEST(some_numbers) AS x
WHERE x < 5) AS doubled_less_than_five
FROM Sequences;
/*------------------------*
| doubled_less_than_five |
+------------------------+
| [0, 2, 2, 4, 6] |
| [4, 8] |
| [] |
*------------------------*/
ClickHouse
arrayFilter 函数
WITH Sequences AS
(
SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL
SELECT [2, 4, 8, 16, 32] AS some_numbers
UNION ALL
SELECT [5, 10] AS some_numbers
)
SELECT arrayMap(x -> (x * 2), arrayFilter(x -> (x < 5), some_numbers)) AS doubled_less_than_five
FROM Sequences;
┌─doubled_less_than_five─┐
1. │ [0,2,2,4,6] │
└────────────────────────┘
┌─doubled_less_than_five─┐
2. │ [] │
└────────────────────────┘
┌─doubled_less_than_five─┐
3. │ [4,8] │
└────────────────────────┘
压缩数组
BigQuery
需要通过UNNEST 运算符将数组临时转换为表
WITH
Combinations AS (
SELECT
['a', 'b'] AS letters,
[1, 2, 3] AS numbers
)
SELECT
ARRAY(
SELECT AS STRUCT
letters[SAFE_OFFSET(index)] AS letter,
numbers[SAFE_OFFSET(index)] AS number
FROM Combinations
CROSS JOIN
UNNEST(
GENERATE_ARRAY(
0,
LEAST(ARRAY_LENGTH(letters), ARRAY_LENGTH(numbers)) - 1)) AS index
ORDER BY index
);
/*------------------------------*
| pairs |
+------------------------------+
| [{ letter: "a", number: 1 }, |
| { letter: "b", number: 2 }] |
*------------------------------*/
ClickHouse
arrayZip 函数
WITH Combinations AS
(
SELECT
['a', 'b'] AS letters,
[1, 2, 3] AS numbers
)
SELECT arrayZip(letters, arrayResize(numbers, length(letters))) AS pairs
FROM Combinations;
┌─pairs─────────────┐
1. │ [('a',1),('b',2)] │
└───────────────────┘
聚合数组
BigQuery
需要通过UNNEST 运算符将数组转换为表
WITH Sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
(SELECT SUM(x)
FROM UNNEST(s.some_numbers) AS x) AS sums
FROM Sequences AS s;
/*--------------------+------*
| some_numbers | sums |
+--------------------+------+
| [0, 1, 1, 2, 3, 5] | 12 |
| [2, 4, 8, 16, 32] | 62 |
| [5, 10] | 15 |
*--------------------+------*/
ClickHouse
arraySum、arrayAvg……函数，或作为arrayReduce 函数的参数的 90 多个现有聚合函数名称中的任何一个
WITH Sequences AS
(
SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL
SELECT [2, 4, 8, 16, 32] AS some_numbers
UNION ALL
SELECT [5, 10] AS some_numbers
)
SELECT
some_numbers,
arraySum(some_numbers) AS sums
FROM Sequences;
┌─some_numbers──┬─sums─┐
1. │ [0,1,1,2,3,5] │ 12 │
└───────────────┴──────┘
┌─some_numbers──┬─sums─┐
2. │ [2,4,8,16,32] │ 62 │
└───────────────┴──────┘
┌─some_numbers─┬─sums─┐
3. │ [5,10] │ 15 │
└──────────────┴──────┘