ClickHouse Cloud 与 BigQuery 对比

BigQuery 与 ClickHouse Cloud 有何不同

​

资源组织

ClickHouse Cloud 的资源组织方式与 BigQuery 的资源层级结构 类似。下图展示了 ClickHouse Cloud 的资源层级结构，下面将说明其中的具体差异：

​

组织

与 BigQuery 类似，组织是 ClickHouse Cloud 资源层级结构中的根节点。您在 ClickHouse Cloud 账户中设置的第一个用户会自动被分配到一个由其拥有的组织。该用户可以邀请其他用户加入该组织。

​

BigQuery Projects 与 ClickHouse Cloud 服务

在 组织 中，你可以创建与 BigQuery 项目大致对应的服务，因为 ClickHouse Cloud 中存储的数据是与服务关联的。ClickHouse Cloud 中提供多种服务类型。每个 ClickHouse Cloud 服务都会部署在特定区域，并包含：

1. 一组计算节点 (目前，Development 层级服务为 2 个节点，Production 层级服务为 3 个节点) 。对于这些节点，ClickHouse Cloud 支持垂直和横向扩缩容，既可手动进行，也可自动进行。
2. 一个 object storage 文件夹，服务会在其中存储所有数据。
3. 一个端点 (或通过 ClickHouse Cloud UI Console 创建的多个端点) - 即用于连接到该服务的服务 URL (例如：https://dv2fzne24g.us-east-1.aws.clickhouse.cloud:8443)

​

BigQuery 数据集与 ClickHouse Cloud 数据库

ClickHouse 会在逻辑上将表归入数据库中。与 BigQuery 数据集类似，ClickHouse 数据库也是用于组织表数据并控制其访问权限的逻辑容器。

​

BigQuery 文件夹

ClickHouse Cloud 目前没有与 BigQuery 文件夹对应的概念。

​

BigQuery Slot 预留和配额

与 BigQuery Slot 预留类似，你可以在 ClickHouse Cloud 中配置纵向和横向自动扩缩容。对于纵向自动扩缩容，你可以为服务中计算节点的内存和 CPU 核心设置最小和最大规格。随后，服务会在这些范围内按需扩缩容。这些设置在初始服务创建流程中也可配置。服务中的每个计算节点规格都相同。你还可以通过横向扩缩容调整服务中的计算节点数量。

此外，与 BigQuery 配额类似，ClickHouse Cloud 提供并发控制、内存使用限制和 I/O 调度，使你能够将查询隔离到不同的工作负载类别中。通过为特定工作负载类别中的共享资源 (CPU 核心、DRAM、磁盘和网络 I/O) 设置限制，可以确保这些查询不会影响其他关键业务查询。在高并发查询场景下，并发控制可防止线程过度订阅。

ClickHouse 会在服务器、用户和查询级别跟踪内存分配的字节大小，从而支持灵活的内存使用限制。内存 overcommit 允许查询使用超出保障内存之外的额外空闲内存，同时保证其他查询的内存限制。此外，还可以限制 aggregation、sort 和 join clauses 的内存使用，以便在超出内存限制时回退到外部算法。

最后，I/O 调度允许你根据最大带宽、进行中的请求数和策略，限制工作负载类别对本地和远程磁盘的访问。

​

权限

ClickHouse Cloud 通过两个层面控制用户访问：一是 Cloud Console，二是 数据库。控制台访问通过 clickhouse.cloud 用户界面进行管理。数据库访问则通过数据库用户账户和角色进行管理。此外，还可以为控制台用户授予数据库中的角色，使其能够通过我们的 SQL 控制台 与数据库交互。

​

数据类型

ClickHouse 在数值类型方面提供了更细粒度的精度选择。例如，BigQuery 提供数值类型 INT64, NUMERIC, BIGNUMERIC and FLOAT64。相比之下，ClickHouse 为小数、浮点数和整数提供了多种精度类型。借助这些数据类型，你可以优化存储和内存开销，从而获得更快的查询速度并降低资源消耗。下面列出了每种 BigQuery 类型对应的 ClickHouse 等效类型：

BigQuery	ClickHouse
ARRAY	Array(t)
NUMERIC	Decimal(P, S), Decimal32(S), Decimal64(S), Decimal128(S)
BIG NUMERIC	Decimal256(S)
BOOL	Bool
BYTES	FixedString
DATE	Date32 (范围更窄)
DATETIME	DateTime, DateTime64 (范围较窄，但精度更高)
FLOAT64	Float64
GEOGRAPHY	Geo Data Types
INT64	UInt8, UInt16, UInt32, UInt64, UInt128, UInt256, Int8, Int16, Int32, Int64, Int128, Int256
INTERVAL	不适用 - 可作为表达式使用 或 通过函数支持
JSON	JSON
STRING	String (bytes)
STRUCT	Tuple, Nested
TIME	DateTime64
TIMESTAMP	DateTime64

当 ClickHouse 类型有多个可选项时，请根据数据的实际范围，选择满足需求的最低类型。此外，也可以考虑使用合适的编解码器来进一步压缩。

​

查询加速技术

​

主键、外键和主索引

在 BigQuery 中，表可以具有主键和外键约束。通常，主键和外键用于关系型数据库中，以确保数据完整性。主键值通常对于每一行都是唯一的，且不为 NULL。每一行中的外键值都必须存在于主键表的主键列中，或者为 NULL。在 BigQuery 中，这些约束并不会被强制执行，但查询优化器可能会利用这些信息进一步优化查询。

在 ClickHouse 中，表也可以有主键。与 BigQuery 一样，ClickHouse 不会强制要求表的主键列值唯一。与 BigQuery 不同，表数据会按照主键列的顺序排序并存储在磁盘上。查询优化器会利用这种排序顺序来避免重新排序、尽量减少 join 的内存使用，并对 limit 子句启用短路。与 BigQuery 不同，ClickHouse 会根据主键列值自动创建 (稀疏) 主索引。该索引用于加速所有包含主键列过滤条件的查询。ClickHouse 当前不支持外键约束。

​

二级索引 (仅 ClickHouse 可用)

除了基于表主键列值创建的主索引外，ClickHouse 还允许在主键之外的列上创建二级索引。ClickHouse 提供了多种二级索引类型，分别适用于不同类型的查询：

• Bloom Filter Index:
  • 用于加速带有等值条件 (例如 =、IN) 的查询。
  • 使用概率型数据结构来判断某个值是否存在于数据块中。
• Token Bloom Filter Index:
  • 与 Bloom Filter Index 类似，但用于分词后的字符串，适合全文搜索查询。
• Min-Max Index:
  • 为每个数据分区片段维护某一列的最小值和最大值。
  • 有助于跳过读取不在指定范围内的数据分区片段。

​

搜索索引

与 BigQuery 中的搜索索引类似，ClickHouse 表中包含字符串值的列也可以创建全文索引。

​

向量索引

BigQuery 最近推出了作为 Pre-GA 功能的向量索引。同样，ClickHouse 也对用于加速向量搜索场景的索引提供 Experimental 支持。

​

分区

与 BigQuery 类似，ClickHouse 通过表分区将大型表拆分为更小、更易于管理的分区，从而提升性能和可管理性。有关 ClickHouse 分区的详细说明，请参见此处。

​

聚簇

使用聚簇后，BigQuery 会根据少数几个指定列的值自动对表数据进行排序，并将相近的数据集中存放在大小经过优化的块中。聚簇可提升查询性能，使 BigQuery 能更准确地估算查询运行成本。对于聚簇列，查询还可以避免扫描不必要的数据。

在 ClickHouse 中，数据会根据表的主键列自动在磁盘上聚簇，并在逻辑上组织为块；利用主索引数据结构的查询可以快速定位这些块，或将其剪枝。

​

Materialized views

BigQuery 和 ClickHouse 都支持 materialized views——即基于对 基表 执行转换查询所得结果预先计算并存储的结果，以提升性能和效率。

​

查询 materialized view

BigQuery 的 materialized view 可以直接查询，也可以由优化器用于处理针对基表的查询。如果对基表的更改可能导致 materialized view 失效，数据就会直接从基表读取。如果对基表的更改不会使 materialized view 失效，其余数据则从 materialized view 中读取，只有变更部分从基表读取。

在 ClickHouse 中，materialized view 只能直接查询。不过，与 BigQuery 相比 (BigQuery 会在基表发生更改后的 5 分钟内自动刷新 materialized view，但刷新频率不会高于每 30 分钟一次) ，materialized view 在 ClickHouse 中始终与基表保持同步。

更新 materialized view

BigQuery 会定期通过对基表运行该视图的转换查询，来完整刷新 materialized view。在两次刷新之间，BigQuery 会将 materialized view 中的数据与基表中的新数据结合起来，在继续使用 materialized view 的同时提供一致的查询结果。

在 ClickHouse 中，materialized view 采用增量更新。这种增量更新机制具有很高的可扩展性，同时计算成本也较低：增量更新的 materialized view 专为基表包含数十亿甚至数万亿行的场景而设计。ClickHouse 无需反复查询持续增长的基表来刷新 materialized view，而只需根据新插入基表的行中的值计算部分结果。该部分结果会在后台与先前计算出的部分结果逐步合并。与反复基于整个基表刷新 materialized view 相比，这种方式可显著降低计算成本。

​

事务

与 ClickHouse 不同，BigQuery 支持在单个查询内执行多语句事务；使用会话时，也支持跨多个查询执行多语句事务。多语句事务允许你执行数据更改操作，例如在一个或多个表中插入或删除行，并以原子方式提交或回滚这些更改。多语句事务已列入 ClickHouse 的 2024 年路线图。

​

聚合函数

与 BigQuery 相比，ClickHouse 提供了更多数量可观的内置聚合函数：

• BigQuery 提供了 18 个聚合函数 和 4 个近似聚合函数。
• ClickHouse 拥有超过 150 个预置聚合函数，以及强大的 聚合组合器，可用于扩展这些预置聚合函数的行为。比如，只需在调用时添加 -Array 后缀，就可以将这 150 多个预置聚合函数应用于数组，而不是表中的行。使用 -Map 后缀 时，你可以将任何聚合函数应用于 Map。使用 -ForEach 后缀 时，你可以将任何聚合函数应用于嵌套数组。

​

数据源和文件格式

与 BigQuery 相比，ClickHouse 支持的文件格式和数据源要丰富得多：

• ClickHouse 原生支持从几乎任何数据源加载采用 90 多种文件格式的数据
• BigQuery 支持 5 种文件格式和 19 种数据源

​

SQL 语言特性

ClickHouse 提供标准 SQL，并在此基础上增加了许多扩展和改进，使其更适合分析类任务。例如，ClickHouse SQL 支持 lambda 函数 和高阶函数，因此在进行转换时，无需对数组执行 unnest/explode。与 BigQuery 等其他系统相比，这是一个很大的优势。

​

数组

与 BigQuery 的 8 个数组函数相比，ClickHouse 拥有 80 多个内置数组函数，能够以优雅而简洁的方式对各种问题进行建模和求解。

ClickHouse 中一种典型的设计模式是使用 groupArray 聚合函数，将表中特定的行值 (临时) 转换为数组。随后便可通过数组函数方便地处理该数组，再通过 arrayJoin 函数将结果重新转换为单独的表行。

由于 ClickHouse SQL 支持高阶 lambda 函数，许多高级数组操作只需调用某个高阶内置数组函数即可完成，而不必像 BigQuery 中通常需要的那样，先将数组临时转换回表，例如对数组进行过滤或压缩 (zip) 。在 ClickHouse 中，这些操作分别只需简单调用高阶函数 arrayFilter 和 arrayZip。

下面我们给出从 BigQuery 到 ClickHouse 的数组操作映射：

BigQuery	ClickHouse
ARRAY_CONCAT	arrayConcat
ARRAY_LENGTH	length
ARRAY_REVERSE	arrayReverse
ARRAY_TO_STRING	arrayStringConcat
GENERATE_ARRAY	range

在子查询中为每一行创建一个仅包含单个元素的数组

BigQuery

ARRAY function

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

GENERATE_DATE_ARRAY 函数

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] |
 *--------------------------------------------------*/

range + arrayMap 函数

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

GENERATE_TIMESTAMP_ARRAY 函数

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

range + arrayMap 函数

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'] │
   └─────────────────────────────────────────────────────────────────────┘

过滤 Arrays

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 等函数，或现有 90 多个聚合函数名中的任意一个，作为 arrayReduce 函数的参数

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 │
   └──────────────┴──────┘