博客 / 工程

优化分析工作负载：Redshift 与 ClickHouse 的比较

Dale McDiarmid

2023 年 3 月 23 日 - 59 分钟阅读

Amazon Redshift 是一个云数据仓库，为结构化和半结构化数据提供报告和分析功能。它旨在利用类似于 ClickHouse 的列式数据库原则来处理大数据集上的分析工作负载。作为 AWS 产品的一部分，它通常是 AWS 用户为其分析数据需求提供的默认解决方案。

虽然 Redshift 因其与 Amazon 生态系统的紧密集成而对现有 AWS 用户具有吸引力，但采用它来支持实时分析应用程序的 Redshift 用户发现他们需要针对此目的进行优化的解决方案。因此，他们越来越多地转向 ClickHouse，以受益于卓越的查询性能和数据压缩，无论是作为现有 Redshift 工作负载的替代品还是“加速层”部署。

在本博客中，我们将探讨用户将工作负载从 Redshift 迁移到 ClickHouse 的原因，提供压缩和查询性能提升的证据，并描述迁移数据的选项——批量迁移以及使用 AWS EventBridge、AWS Lambda 和 AWS Glue 持续追加新数据。

对于在 AWS 生态系统中投入巨资的用户来说，当面临数据仓库需求时，Redshift 是一个自然的选择。Redshift 在这个重要方面与 ClickHouse 不同——它针对需要复杂报告和分析查询的数据仓库工作负载优化其引擎。在所有部署模式中，以下两个限制使得 Redshift 难以用于实时分析工作负载

Redshift 为每个查询执行计划编译代码，这为首次查询执行增加了显著的开销（在我们的测试中高达 2 秒）。当查询模式可预测且编译后的执行计划可以存储在查询缓存中时，这种开销是合理的。但是，这为具有可变查询的交互式应用程序带来了挑战。即使 Redshift 能够利用此代码编译缓存，ClickHouse 在大多数查询中也更快——请参阅下面的“基准测试”和“ClickHouse 与 Redshift 查询比较”。
Redshift 将所有队列的并发数限制为 50，这（虽然对于 BI 来说足够）使其不适用于高并发分析应用程序。

相反，虽然 ClickHouse 也可用于复杂的分析查询，但它针对实时分析工作负载进行了优化，无论是支持应用程序还是充当仓库加速层。因此，Redshift 用户通常出于以下原因替换或增强 Redshift 与 ClickHouse

ClickHouse 实现更低的查询延迟，包括在各种查询模式下、高并发下以及在接受流式插入时。即使您的查询未命中缓存（这在面向用户的交互式分析中是不可避免的），ClickHouse 仍然可以快速处理它。
ClickHouse 对并发查询施加更高的限制，这对于实时应用程序体验至关重要。在 ClickHouse 中，无论是自托管还是云，您都可以扩展您的计算分配，以实现您的应用程序为每项服务所需的并发性。允许的查询并发级别是可在 ClickHouse 中配置的，ClickHouse Cloud 默认值为 1000。
ClickHouse 提供卓越的数据压缩，这允许用户减少其总存储量（从而降低成本），或者以相同的成本持久保存更多数据，并从其数据中获得更多的实时洞察。请参阅下面的“ClickHouse 与 Redshift 存储效率”。

用户还因 ClickHouse 对实时分析功能的广泛支持而赞赏它，例如

大量专门的分析函数，旨在缩短和简化查询语法，例如，聚合组合器和数组函数
旨在简化分析查询的 SQL 查询语法， 例如，ClickHouse 不在 SELECT 中强制使用别名
卓越的数据类型支持，包括超过 65k 个字符的字符串、枚举和数组，这些通常是分析查询模式所需的
卓越的文件和数据格式支持，与 Redshift 中更有限的选择相比，简化了分析数据的导入和导出，
卓越的联邦查询能力，支持针对各种数据湖和数据存储的即席查询，包括 S3、MySQL、Postgres、MongoDB、Delta Lake 等
二级索引和投影 - ClickHouse 支持二级索引，包括用于文本匹配的倒排索引，以及投影，以允许用户针对特定查询进行优化。

部署 Redshift 时，用户有多种选择，每种选择对于不同的工作负载都具有各自的优势和劣势

Redshift Serverless - Redshift 产品阵容的最新成员（2022 年 7 月全面上市），此产品提供存储和计算分离，并根据查询负载自动配置和扩展仓库容量。与 ClickHouse Cloud 类似，这是一个完全托管的产品，实例会自动升级。可用计算容量通过自定义 Redshift 处理单元 (RPU) 单位（大约两个虚拟 CPU 和 16 GB RAM）进行衡量，用户可以为此设置限制（默认为 128）。用户需要为存储的数据以及仓库处于活动状态时消耗的计算容量付费，粒度为 1 秒，最短计费周期为 1 分钟。虽然更改 RPU 限制需要相当长的停机时间，但此产品适用于性能不关键且工作负载可变且可能存在空闲时间的临时分析。但是，它不太适合高或可能无界且不可预测的查询工作负载，例如，对于负载基于用户数量的应用程序。此外，如果延迟至关重要，用户通常会倾向于以下预配置选项。

Redshift Provisioned - 原始 Redshift 产品，具有最新的添加和改进，为用户提供多种选择

DC2 节点 - 专为基于本地 SSD 存储的计算密集型数据仓库而设计，其中查询延迟至关重要。Redshift 建议这些用于小于 1TB 压缩的数据集。用户可以选择特定的节点大小和节点数量以增加总容量。
RA3 节点 - 这些节点提供更高的存储与计算比率，并在本地磁盘满后将数据卸载到 S3，使用称为托管存储的功能。此功能与 ClickHouse Cloud 的共享无架构相当，其中本地磁盘充当缓存，而 s3 提供无界存储容量。但是请注意，与 ClickHouse Cloud 不同，Redshift 中的数据仍然与特定节点相关联，从而阻止存储和计算完全独立地扩展和付费。存储成本与数据驻留的位置（即本地磁盘或 s3）无关，用户仅需为使用的托管存储付费。

在本文的其余部分，我们将展示 Redshift 用户如何批量迁移数据到 ClickHouse，以及如何在并行部署的新系统之间保持数据同步。

本博客文章中的示例使用 ClickHouse Cloud，它提供免费试用版，允许完成我们涵盖的场景。我们使用 sql.clickhouse.com 上的公开云环境，该环境总共有 720 GB 内存和 180 个 vCPU，分布在三个节点上（请注意，我们的基准测试仅使用一个节点）。所有说明也与运行最新版本的自托管 ClickHouse 部署兼容。

我们使用 BigQuery 公共项目中提供的以太坊加密货币数据集，我们在之前的文章 ClickHouse 与 BigQuery 比较：迁移数据以进行实时查询中使用了该数据集。与这篇文章类似，我们推迟在以后的博客文章中详细探讨此数据集，但建议阅读 Google 关于如何构建此数据集的博客以及关于查询此数据集和其他加密货币数据集的后续文章。阅读本博客文章不需要加密货币方面的经验，但对于那些感兴趣的人，以太坊简介提供了一个有用的概述。Google 记录了许多基于此数据集的出色查询，我们稍后将在博客中引用这些查询。我们已经整理了等效的 ClickHouse 和 Redshift 查询此处，欢迎贡献。

该数据集由四个表组成

交易 - 来自帐户的加密签名指令，例如将货币从一个帐户转移到另一个帐户
区块 - 交易批次，其中包含链中前一个区块的哈希值
追踪 - 允许查询所有以太坊地址及其余额的内部交易
合约 - 在以太坊区块链上运行的程序

这些表代表完整数据的子集，并解决了最常见的查询，同时提供了大量数据。由于 AWS 未提供此数据集，因此可以使用出色的 Ethereum ETL 工具生成该数据集，该工具已提交 PR 以支持 ClickHouse 作为目标。或者，我们在 s3 存储桶 s3://datasets-documentation/ethereum/ 中提供此数据的快照，供我们的用户探索，并在我们的公共 Playground 中提供最新版本。数据的完整最新版本也可以在 gcs 存储桶 gs://clickhouse_public_datasets/ethereum 中找到。

在我们的示例中，我们假设数据已加载到 Redshift 中。对于有兴趣的读者，我们使用 BigQuery 的 EXPORT 功能将此数据从 BigQuery 导出到 S3 中的 Parquet 格式（Google 在其中维护最新副本）。完整的架构可以在此处找到，以及加载 Parquet 文件所需的完整步骤。选择 Parquet 是因为它代表了 Redshift 最有效的格式。

遗憾的是，我们无法对此任务使用 COPY 命令，因为有些列无法加载（例如，字符串长度超过 Redshift 65k 字符的限制）或需要在加载时进行日期转换。因此，我们的示例使用 Redshift 的通过 Spectrum 查询 S3 中数据的能力，其中外部表用于在通过 INSERT INTO SELECT 将数据插入最终表之前公开 s3 文件（可以在其中选择和 CAST 列的子集）。

使用 ANALYZE COMPRESSION 命令优化了架构，以识别最有效的编解码器。我们对该数据集使用了与我们之前的 BigQuery 文章中为查询建立的相同的排序键。

对于有兴趣了解数据加载的详细信息以及保持 ClickHouse 和 Redshift 同步的选项的用户，我们在下面提供了这些信息。但是，为了突出迁移到 ClickHouse 的价值，我们首先展示了上述数据集在两个系统中的各自大小的摘要。在撰写本文时，无法确定 Redshift 中数据的未压缩大小 - 因此我们仅测量两者的压缩大小。有关完整的表架构和使用的编解码器，请参阅下面的“将 Redshift 表迁移到 ClickHouse”部分。

此信息可以通过一个简单的查询获得。

SELECT "table", size, tbl_rows, unsorted, pct_used, diststyle  FROM SVV_TABLE_INFO WHERE "table" = 'blocks'

?column?    size    tbl_rows    unsorted    pct_used
blocks      11005   16629116    0           0.0005

预计“unsorted”字段的值为 0。如果不是，用户可以运行 VACUUM 命令在后台对任何未排序的行进行排序，并实现更优的压缩。为 size 返回的值以 MB 为单位，可以与 ClickHouse 中的压缩存储进行比较。还返回了分发样式，因为这会影响总大小。我们的表都已配置为 AUTO 值，Redshift 可以自由分配最佳分发样式并根据表大小进行调整。除了我们最小的表 blocks 之外，还选择了 EVEN 分发样式，这意味着数据以轮询方式发送到各个节点。我们为每个列应用了 ANALYZE COMPRESSION（请参阅下面的“压缩”）识别的最佳压缩算法。

下面我们捕获来自无服务器实例的 Redshift 存储统计信息。

表名	总行数	压缩大小	分发样式
blocks	16629116	10.74GB	AUTO(KEY(number))
contracts	57394746	12.51GB	AUTO(EVEN)
transactions	1874052391	187.53GB	AUTO(EVEN)
traces	6377694114	615.46GB	AUTO(EVEN)

可以使用查询 system.columns 表来查找 ClickHouse 中的压缩表大小。

SELECT
    table,
    formatReadableSize(sum(data_compressed_bytes)) AS compressed_size
FROM system.columns
WHERE database = 'ethereum'
GROUP BY table
ORDER BY sum(data_compressed_bytes) DESC

┌─table───────────┬─compressed_size─┬
│ traces          │ 339.95 GiB      │
│ transactions    │ 139.94 GiB      │
│ blocks          │ 5.37 GiB        │
│ contracts       │ 2.73 GiB        │
└─────────────────┴──────────────────

下面我们比较上述测量值，还将与 Parquet 进行比较，并计算 ClickHouse 与 Redshift 的存储比率。

表	Parquet 大小（使用 SNAPPY）	Redshift 大小（压缩后）	ClickHouse 大小（压缩后）	ClickHouse/Redshift 比率
transactions	交易	252.4 GiB	187.53 GiB	1.3
blocks	139.94 GB	区块	10.9 GiB	2
traces	10.74 GiB	5.37 GB	339.95	1.8
contracts	追踪	710.1 GiB	615.46 GiB	4.6
16.0 GB	合约	12.51 GiB	12.51 GiB	2

2.73 GB

989.4 GiB

826.24 GiB

如图所示，对于此数据集，ClickHouse 比最佳 Redshift 架构更有效地压缩数据，综合压缩率达到 2 倍。

为了提供查询性能的比较，我们对 2 节点 dc2.8xlarge 集群执行了 benchmarks.clickhouse.com 中详述的基准测试，该集群总共提供 64 个核心和 488GB RAM，并使用此处概述的步骤。AWS 建议将此节点类型用于压缩后小于 1TB 的数据集上的计算密集型工作负载。我们将以下结果与具有 60 个核心和 240GB RAM 的单个 ClickHouse Cloud 节点进行比较。此基准测试的完整方法在存储库中详细说明，该基准测试在 1 亿行 Web 分析数据集上运行 42 个查询。我们在下面展示这些结果，也可以从此处访问。

如图所示，我们的 60 核 ClickHouse Cloud 节点平均比同类 Redshift 集群快 2.5 倍。欢迎随意探索其他比较，其中 Redshift 集群的资源明显更高。

将 Redshift 表迁移到 ClickHouse

Redshift	Redshift 和 ClickHouse 都构建在列式存储之上，因此在这两个系统中处理表是相似的。
数据类型	在 ClickHouse 和 Redshift 之间移动数据的用户会立即注意到，ClickHouse 提供了更广泛的类型范围，这些类型的限制也更少。虽然 Redshift 要求用户指定可能的字符串长度（即使是可变的），但 ClickHouse 通过将字符串存储为字节而不进行编码，从而消除了用户的这种限制和负担。因此，ClickHouse String 类型没有限制或长度规范要求。*
此外，用户可以利用数组、元组和枚举——Redshift 中缺少作为一等公民（尽管可以使用 SUPER 模拟数组/结构体），并且是用户常见的挫败感。ClickHouse 还允许持久化聚合状态，无论是在查询时还是甚至在表中。这将使数据能够被预聚合，通常使用物化视图，并可以显着提高常见查询的查询性能。	下面我们映射每个 Redshift 类型的等效 ClickHouse 类型*
Redshift	ClickHouse*
SMALLINT	Int8
INTEGER	Int32
BIGINT	Int64
DECIMAL	UInt128、UInt256、Int128、Int256、Decimal(P, S)、Decimal32(S)、Decimal64(S)、Decimal128(S)、Decimal256(S) - （可能的高精度和范围）
REAL	Float32
DOUBLE PRECISION**	Float64
BOOLEAN	Bool
CHAR	String、FixedString
VARCHAR	String、FixedString
String	DATE
Date32	TIMESTAMP
DateTime、DateTime64	TIMESTAMPTZ
DateTime64(9) with Timezone	GEOMETRY
Geo Data Types	String、FixedString
GEOGRAPHY	String、FixedString
Geo Data Types（开发程度较低，例如没有坐标系 - 可以使用函数模拟）**	HLLSKETCH

_{AggregateFunction(uniqHLL12, X)}
_SUPER

Tuple、Nested、Array、JSON、Map

无直接等效项 - 可以使用 DateTime 或 DateTime64 模拟

TIMETZ

无直接等效项 - 可以使用 DateTime64(9) with Timezone 模拟

String 结合 Bit 和 Encoding 函数

CREATE TABLE some_table(...) SORTKEY (column1, column2)

* ClickHouse 另外支持具有扩展范围的无符号整数，即 UInt8、UInt32、UInt32 和 UInt64。

CREATE TABLE some_table(...) ENGINE = MergeTree ORDER BY (column1, column2)

**ClickHouse 的 String 类型默认不受限制，但可以使用约束将其限制为特定长度。

当出现 ClickHouse 类型的多个选项时，请考虑数据的实际范围并选择最低的必需范围。

用户应注意，“主键”概念在 ClickHouse 和 Redshift 中代表不同的含义。在 Redshift 中，主键类似于传统的 RDMS 概念，旨在强制执行约束。然而，在 Redshift 中，它们并非严格强制执行，而是充当查询规划器和节点间数据分布的提示。在 ClickHouse 中，主键表示用于构建稀疏主索引的列，用于确保数据在磁盘上按顺序排列，从而最大限度地提高压缩率，同时避免主索引污染和浪费内存。

在此示例中，我们假设我们的数据仅存在于 Redshift 中，并且我们不熟悉等效的 ClickHouse Ethereum 模式。可以使用以下查询检索任何 Redshift 表的当前模式

SHOW TABLE <schema>.<table>

例如，对于 blocks 表

CREATE TABLE public.blocks (
        number bigint NOT NULL ENCODE zstd distkey,
        hash character(66) ENCODE zstd,
        parent_hash character(66) ENCODE zstd,
        nonce character(18) ENCODE zstd,
        sha3_uncles character(66) ENCODE zstd,
        logs_bloom character(514) ENCODE zstd,
        transactions_root character(66) ENCODE zstd,
        state_root character(66) ENCODE zstd,
        receipts_root character(66) ENCODE zstd,
        miner character(42) ENCODE zstd,
        difficulty numeric(38, 0) ENCODE az64,
        total_difficulty numeric(38, 0) ENCODE az64,
        SIZE bigint ENCODE zstd,
        extra_data CHARACTER varying(66) ENCODE zstd,
        gas_limit bigint ENCODE zstd,
        gas_used bigint ENCODE zstd, 
        timestamp timestamp WITHOUT TIME ZONE ENCODE RAW,
        transaction_count bigint ENCODE zstd,
        base_fee_per_gas bigint ENCODE zstd,
PRIMARY KEY (number)) DISTSTYLE AUTO SORTKEY (timestamp);

完整的 Redshift 模式可以在此处找到。对于某些表，在插入 Redshift 之前，已从原始数据集中删除了某些列，因为它们的长度超过了 Redshift 字符串的最大长度 65k，例如 transactions 的 input 列。

下面显示了 blocks 表的模式以及在 ClickHouse 中创建等效表的语句。如果未指定编解码器，则 ZSTD(1) 将用作压缩算法，因为这是 ClickHouse Cloud 中的默认设置。

CREATE TABLE blocks
(
    `number` UInt32 CODEC(Delta(4), ZSTD(1)),
    `hash` String,
    `parent_hash` String,
    `nonce` String,
    `sha3_uncles` String,
    `logs_bloom` String,
    `transactions_root` String,
    `state_root` String,
    `receipts_root` String,
    `miner` String,
    `difficulty` Decimal(38, 0),
    `total_difficulty` Decimal(38, 0),
    `size` UInt32 CODEC(Delta(4), ZSTD(1)),
    `extra_data` String,
    `gas_limit` UInt32 CODEC(Delta(4), ZSTD(1)),
    `gas_used` UInt32 CODEC(Delta(4), ZSTD(1)),
    `timestamp` DateTime CODEC(Delta(4), ZSTD(1)),
    `transaction_count` UInt16,
    `base_fee_per_gas` UInt64
)
ENGINE = MergeTree
ORDER BY timestamp

我们对这些模式进行了基本优化，使用了适当的类型和编解码器以最大限度地减少存储。例如，尽管原始模式中列是 Nullable 的，但我们没有将列设为 Nullable，因为对于大多数查询而言，无需区分默认值和 Null 值。通过使用默认值，我们避免了与 Nullable 相关的额外 UInt8 列开销。否则，我们保留了许多默认设置，包括使用与 Redshift 相同的 ORDER BY 键。

您可以运行额外的查询来识别数据范围和基数，从而让您选择最佳的 ClickHouse 类型。“使用模式和编解码器优化 ClickHouse”博客更深入地探讨了这个主题。我们将模式优化的完整分析留待以后专门针对此数据集的博客文章中进行。

SELECT
 MAX(number) AS max_number,
 MIN(number) AS min_number,
 MAX(size) AS max_size,
 MIN(size) AS min_size
FROM blocks

max_number    min_number    max_size    min_size
16547585      0             1501436     514

Getting data from Redshift to ClickHouse

Redshift 支持通过 UNLOAD 命令将数据导出到 S3。数据反过来可以使用 s3 表函数导入到 ClickHouse 中。这种“枢轴”步骤方法具有许多优点

Redshift UNLOAD 功能支持通过标准 SQL 查询过滤导出数据的子集。
Redshift 支持导出为 Parquet、JSON 和 CSV 格式以及多种压缩类型 - 所有这些都受 ClickHouse 支持。
S3 支持对象生命周期管理，允许在数据导出并导入到 ClickHouse 后在指定时间段后删除数据。
导出自动生成多个文件，允许并行导出，每个文件限制为最大 6.2GB。这对 ClickHouse 有利，因为它允许并行导入。这种并行化是在切片级别完成的，每个切片生成一个或多个文件。
AWS 不收取将数据卸载到 S3 的费用，前提是 RedShift 和存储桶位于同一区域。但是，用户仍将为数据导出查询（如果使用 Redshift Serverless）所消耗的计算资源以及 S3 中的存储成本付费。

要将数据从 Redshift 表导出到 S3 存储桶中的文件，请确保您已创建存储桶并授予 Redshift 访问权限。我们可以使用 UNLOAD 命令从 Redshift 表导出数据。可以使用 SELECT 语句将导出限制为列的子集

UNLOAD ('SELECT * FROM some_table')
TO 's3://my_bucket_name/some_table_'
iam_role 'arn:aws:...' PARQUET
ALLOWOVERWRITE

Exporting Data from Redshift to S3

我们使用面向列的 Parquet 文件格式进行导出，因为在存储效率和导出速度（比其他格式快 2 倍）方面，这是一个不错的选择，并且针对 ClickHouse 的读取进行了优化。此操作所花费的时间取决于分配给 Redshift 集群的资源（和切片）以及区域位置。我们对 S3 和 Redshift 使用相同的区域进行导出，以最大限度地提高吞吐量和降低成本。下表显示了为预置型和无服务器型（限制为 128 个 RPU）Redshift 集群导出的每个表的导出时间。鉴于块数据较小，我们使用设置 MAXFILESIZE 将 Parquet 文件大小限制为 100MB。这允许 Redshift 并行导出以及协助 ClickHouse 导入。对于所有其他表，我们依赖默认文件分区，它使用 6.2GB 作为上限创建多个文件。

表	文件数量	Parquet 大小 (GB)	Redshift Serverless (128 个 RPU)	Redshift (2xdc2.8xlarge)
Blocks	128	10.9GiB	4.9 秒	18.4 秒
Contracts	128	追踪	2 分 43.9 秒	22.5 秒
Transactions	128	交易	4 分 40 秒	10 分 14.1 秒
Traces	128	10.74 GiB	5 分 36.1 秒	29 分 37.8 秒

细心的读者会注意到，所有类型的文件都有 128 个。这是因为 Redshift 在切片级别进行并行化（这似乎相当于 UNLOAD 的 RPU），每个切片至少生成一个文件，如果文件超过 6.2GB，则生成多个文件。这里的无服务器实例具有明显更多的资源（大约 256 个内核）可用，这归因于更快的导出时间。

我们使用 s3 函数将数据从 S3 加载到 ClickHouse 中。我们在运行以下 INSERT INTO blocks 表之前预先创建了表。

SET parallel_distributed_insert_select = 1
INSERT INTO blocks
SELECT * FROM s3Cluster('default', 'https://dalem-bucket.s3.eu-west-1.amazonaws.com/export/blocks/*.parquet')

0 rows in set. Elapsed: 14.282 sec. Processed 16.63 million rows, 19.26 GB (1.16 million rows/s., 1.35 GB/s.)

我们使用函数 s3Cluster，它是 s3 函数的分布式变体。这允许在 ClickHouse Cloud 中充分利用集群资源进行读取和写入。设置 parallel_distributed_insert_select=1 确保插入是并行化的，并且数据从读取数据的同一节点插入到该节点，跳过写入时的发起程序节点。我们不提供身份验证密钥，因为我们使用的存储桶是公共的，但支持私有存储桶。

在某些情况下，您需要将导出的 Parquet 列映射到等效的 ClickHouse 数据类型。例如，Redshift 不支持数组。对于诸如 function_sighashes 之类的列（原始数据集中的数组），这些列在 Redshift 中表示为 SUPER 类型。这在 Parquet 中没有等效的表示形式，并且此列导出为 String。下面我们将此列映射回 ClickHouse 中的 Array 类型。

INSERT INTO contracts
SELECT
    address,
    bytecode,
    replaceAll(ifNull(function_sighashes, '[]'), '"', '\'') AS function_sighashes,
    is_erc20,
    is_erc721,
    block_timestamp,
    block_number,
    block_hash
FROM s3Cluster('default', 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/ethereum/contracts/*.parquet')

0 rows in set. Elapsed: 21.239 sec. Processed 57.39 million rows, 58.11 GB (2.70 million rows/s., 2.74 GB/s.)

我们对每个表重复了此练习，记录了以下时间。使用此方法，我们能够在不到 35 分钟的时间内将大约 1TB 和 83.2 亿行数据从 Redshift 传输到 ClickHouse。

表	行数	数据大小 (Parquet)	Redshift 导出	ClickHouse 导入
blocks	16629116	10.9GiB	4.9 秒	14.28 秒
contracts	57394746	追踪	2 分 43.9 秒	21.24 秒
transactions	1874052391	交易	4 分 40 秒	5 分 15 秒
traces	6377694114	10.74 GiB	5 分 36.1 秒	15 分 34 秒
16.0 GB	83.2 亿	990GB	13 分 5 秒	21 分 25 秒

上述方法对于批量加载静态数据集或动态语料库的历史数据非常有效。但是，它没有解决 Redshift 表不断接收新数据，需要将其导出到 ClickHouse 的情况。

在本博客的其余部分，我们假设

数据是仅追加且不可变的。没有选择性更新行的要求，尽管删除旧数据是预期之内且在下面描述的。
数据上存在时间维度或递增的数字标识符，允许识别要复制到 ClickHouse 的新行。

这些假设与我们常见的迁移到 ClickHouse 的实时分析数据集的要求一致。例如，当用户选择在 Redshift 和 ClickHouse 之间保持数据同步时，他们通常会根据时间维度同步最新的数据集。我们的示例数据集本身就满足这些属性，我们使用块时间戳进行同步。

最简单的解决方案是调度定期 UNLOAD 查询，以识别任何新行并将这些新行导出到 S3 以插入到 ClickHouse 中。此方法易于实施和维护。它假设我们的数据是不可变的，仅添加行，并且具有可用于识别新数据的属性（通常是时间戳）。对于我们的示例，假设我们每小时调度一次最近 60 分钟数据的导出。这进一步假设新行将实时插入，没有延迟。在大多数情况下，情况并非如此，新行与当前时间存在一定的延迟和偏移。每次我们运行导出时，都需要从偏离当前时间的窗口导出行。例如，假设我们确信我们的数据将在 15 分钟内可用。我们反过来将导出从 <scheduled_time>-75mins 到 <scheduled_time>-15mins 的所有行。

此方法依赖于数据在 15 分钟内可靠地插入到 Redshift 中。

Redshift 支持原生计划查询功能，但它不是我们目的的可行选择。首先，它不支持引用偏移计算所需的计划时间的能力。其次，此功能仅为预置型集群提供，而非无服务器集群。对于希望执行独立于计划时间的定期表导出的用户，使用 Redshift 原生调度可能就足够了。例如，数据集较小的用户可以定期导出所有行并覆盖整个数据集，但这对于较大的数据集是不切实际的。

Amazon EventBridge 是一种无服务器事件总线，可连接 AWS 中的应用程序和服务。对于我们的目的，我们特别关注 Amazon EventBridge Scheduler，它允许我们通过 API 集中创建、运行和管理计划任务。

Using Amazon EventBridge

为了使用 EventBridge 调度程序调度 UNLOAD 查询，请配置适当的执行角色，并确保计划执行的角色具有 Redshift Data API 的正确权限，运行 ExecuteStatement 命令的能力，以及运行 UNLOAD 查询以将数据导出到 S3 的权限。为了帮助调试，用户还需要在 SQS 中创建死信队列 (DLQ) 队列的权限，如果发生故障，消息将发送到该队列。可以使用控制台、SDK 或 AWS CLI 创建计划。我们的计划取决于 Amazon EventBridge 针对 Redshift Data API 运行 ExecuteStatement 命令的能力。我们在下面展示了一个计划的创建，该计划导出从 <scheduled_time>-75mins 到 <scheduled_time>-15mins 窗口的所有行。

此计划的以下重要组成部分

我们使用基于 cron 的计划，使用表达式 15 * * * ? * 每小时 15 分钟定期运行，即此执行没有灵活性。
该计划利用 Redshift Data API 和 ExecuteCommand 端点。此 API 的 JSON 负载如下所示

{
  "Database": "dev",
  "ClusterIdentifier": "redshift-cluster-1",
  "Sql": "UNLOAD ('SELECT * FROM blocks WHERE timestamp > \\'<aws.scheduler.scheduled-time>\\'::timestamp - interval \\'75 minutes\\' AND timestamp < \\'<aws.scheduler.scheduled-time>\\'::timestamp - interval \\'15 minutes\\'') TO 's3://datasets-documentation.s3.eu-west-3.amazonaws.com/ethereum/incremental/blocks/<aws.scheduler.execution-id>' iam_role 'arn:aws:iam::925472944448:role/RedshiftCopyUnload' PARQUET MAXFILESIZE 200MB ALLOWOVERWRITE",
  "DbUser": "awsuser"
}

在这里，我们针对 default 工作组中预置型集群中的 dev 数据库执行操作。如果这是一个无服务器集群，我们将指定 WorkgroupName 而不是 ClusterIdentifier。DbUser 假设使用临时凭证。EventBridge 还支持使用 AWS Secrets Manager 进行身份验证。
上述负载使用 UNLOAD 命令将 blocks 表中满足特定时间范围的所有行导出到 Parquet 格式的专用 s3 存储桶。我们通过 [上下文属性 <aws.scheduler.scheduled-time> 注入计划时间（实际执行时间可能会有所不同），并在我们的 WHERE 子句中执行数据数学运算以转移到所需的时间范围，即 WHERE timestamp > '<aws.scheduler.scheduled-time>'::timestamp - interval '75 minutes' AND timestamp < '<aws.scheduler.scheduled-time>'::timestamp - interval '15 minutes'
上下文 <aws.scheduler.execution-id> 用于为导出的文件提供前缀。这将对于每个计划调用都是唯一的，从而避免文件冲突。
我们选择 SQS DLQ 以在发生故障时发送事件。

为了测试此计划，我们可以将一行插入到我们的 blocks 表中，并调整时间戳以匹配下一个周期，然后等待导出。

INSERT INTO blocks(number,hash,parent_hash,nonce,sha3_uncles,logs_bloom,transactions_root,state_root,receipts_root,miner,difficulty,total_difficulty,size,extra_data,gas_limit,gas_used,timestamp,transaction_count,base_fee_per_gas) VALUES(99999999,'','','','','value','','','','',0,58750003716598356000000,74905,'',30000000,13141664,'2023-03-13 18:00:00',152,326697119799)

Objects

我们之前的导出文件以 execution id 作为前缀。虽然这避免了冲突，但它不允许我们使用文件名来识别文件涵盖的时间范围。因此，必须扫描所有这些文件以识别要导入的行。随着文件数量的增长，用户应使文件过期，以避免此查询的成本不断增长。

在撰写本文时，ClickHouse 没有内置的调度导入方法（提案正在讨论中）。我们在下面探讨了定期从外部导入这些文件的选项。

对于最初的简单方法，为了说明逻辑，可以在导出完成后定期通过 cron 作业运行以下 bash 脚本。此脚本首先获取 ClickHouse 中的当前最大日期，然后发出 INSERT INTO blocks SELECT * FROM s3(<bucket with export files>) WHERE timestamp > ${max_date} 查询。此示例处理 blocks 表，但可以轻松地适应其他表。此方法的优点是它可以独立于导出运行，并且可以根据需要经常运行，但假设任何容器或自我管理的环境中都可使用 clickhouse-client。我们将调度留给读者作为练习。

#!/bin/bash

max_date=$(clickhouse-client --query "SELECT toInt64(toStartOfHour(toDateTime(max(block_timestamp))) + toIntervalHour(1)) AS next FROM ethereum.transactions");
Clickhouse-client –query "INSERT INTO blocks SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/ethereum/incremental/blocks/*.parquet') WHERE timestamp >= ${max_date}"

AWS Lambda 是亚马逊作为 Amazon Web Services 的一部分提供的事件驱动型无服务器计算平台。它是一种计算服务，可运行代码以响应事件，并自动管理该代码所需的计算资源。

此服务可用于定期执行以下简单的 python 脚本，该脚本在 Python 中复制了上述 bash 逻辑。

import requests
import json
CLICKHOUSE_HOST = '<host inc port>'
CLICKHOUSE_PASSWORD = '<password>'
CLICKHOUSE_TABLE = blocks'
TIME_COLUMN = 'timestamp'

def lambda_handler(event, context):
   s = requests.Session()
   s.auth = ('default', CLICKHOUSE_PASSWORD)
   response = s.get(f'https://{CLICKHOUSE_HOST}',
                    params={'query': f'SELECT max({TIME_COLUMN}) as max FROM {CLICKHOUSE_TABLE} FORMAT JSONEachRow'})
   max_time = response.json()['max']
   print(max_time)
   insert_query = f"INSERT INTO {CLICKHOUSE_TABLE} SELECT * FROM " \
                  f"s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/ethereum/incremental/blocks/*.parquet') " \
                  f"WHERE timestamp > '{max_time}'"
   response = s.post(f'https://{CLICKHOUSE_HOST}/?', params={'query': ''}, data=insert_query)
   return f'done. written {json.loads(response.headers["X-ClickHouse-Summary"])["written_rows"]} rows'

此脚本可以打包并上传到 AWS Lambda，以及打包的 requests 依赖项。AWS EventBridge 反过来可以配置为按所需的间隔调度 Lambda 函数 - 我们在下面展示了这一点

此导入不仅依赖于 ClickHouse 中的最新时间戳，因此可以独立于早期的导出运行，而且还通过 INSERT INTO SELECT 将所有工作卸载到 ClickHouse。此脚本包含 ClickHouse 集群的用户名和密码。我们建议此代码的任何生产部署都应增强为利用 AWS Secrets Manager 来安全地存储和检索这些凭据。

我们没有使用 AWS EventBridge 来处理导入，原因如下。最初，我们打算使用 EventBridge API 目标来实现此目的，因为此功能允许通过 Basic Auth 连接到外部服务。EventBridge 规则将由导出计划触发，并将以下查询发送到 ClickHouse，利用作业中公开的 $.time 变量（这是导出的计划时间）。但是，ClickHouse 中的数据更改查询必须通过 POST 请求发送。目前，EventBridge 中的规则目标将以 JSON 或带引号的字符串格式发送请求正文（查询）。ClickHouse 不支持这一点。我们正在探索支持官方 ClickHouse Event Source 的可能性。

如果我们执行批量导入，然后调度上述导入和导出查询，那么在批量加载完成和增量加载开始之间的期间，我们总是会在数据中存在“空白”。为了解决这个问题，我们可以使用与我们的使用 ClickHouse 为 BigQuery 数据提供实时查询服务博客文章中记录的相同技术（请参阅“填补空白”）。

上述方法假设批处理更新过程，该过程需要正确编排导出和导入步骤。利用 AWS Glue，我们可以避免此两步过程，并将此逻辑封装在单个 ETL 作业中。

AWS Glue 是一种无服务器数据集成服务，使用户可以轻松地在多个源之间提取、转换和加载数据。虽然这将允许用户将数据从 Redshift 移动到 ClickHouse，并且可能无需编写代码，但 AWS Glue 尚不支持 ClickHouse 的连接器。但是，它确实支持执行 Python shell 脚本的能力，因此我们可以使用 boto3 库从 Redshift 读取行，并使用 clickhouse-connect 库将数据发送到 ClickHouse，从而在系统之间流式传输数据。

可以在此处找到经过测试的实现此概念的 python 脚本。这会识别 ClickHouse 中的当前最大日期，然后再从 Redshift 请求大于此时间的行。该脚本分页浏览结果，制定批量以提高效率，然后再将它们插入到 ClickHouse 中。一旦所有行都被使用和插入，脚本就会完成。

AWS Glue 需要将 IAM 角色与脚本的执行关联起来。除了标准的权限之外，请确保此角色具有访问您的 Redshift 集群的权限，并且能够使用临时凭证，因为 ExecuteStatement 命令需要。下面的示例从预置型集群读取数据，但可以修改为连接无服务器集群 i 如果需要。

我们在下面重点介绍了将提供的脚本部署到 AWS Glue 的命令。您需要指定 additional-python-modules 参数，以确保安装 clickhouse-connect 依赖项（boto3 默认可用）。

aws glue create-job --name clickhouse-import  --role AWSGlueServiceRoleRedshift --command '{"Name" :  "pythonshell", "ScriptLocation" : "s3://<bucket_path_to_script>"}' --default-arguments '{"--additional-python-modules", "clickhouse-connect"}'

{
    "Name": "clickhouse-import"
}

aws glue start-job-run --job-name "clickhouse-import"
{
    "JobRunId": "jr_a1fbce07f001e760008ded1bad8ee196b5d4ef48d2c55011a161d4fd0a39666f"
}

AWS Glue 原生支持通过简单的 cron 表达式调度这些脚本。再次建议用户将 ClickHouse 集群凭据存储在 AWS Secret Manager 中，AWS Glue 支持 AWS Secret Manager 而不是脚本中。可以使用 boto3 库检索这些凭据，前提是配置了所需的 IAM 权限。

相同的方法也可以在 AWS Lambda 函数中实现，或者使用 AWS Glue spark 或流式 ETL 作业。

对于大多数部署，ClickHouse 卓越的数据压缩意味着您可以长期以粒度格式存储数据。对于我们特定的 Ethereum 数据集，这可能不是特别有益，因为我们可能需要保留区块链的完整历史记录以进行许多查询，例如，计算帐户余额。

但是，如果希望仅在 ClickHouse 中保留子集，则可以使用简单且可扩展的方法来删除旧数据。例如，可以使用 TTL 功能在行或列级别使 ClickHouse 中的旧数据过期。通过按日期对表进行分区可以提高效率，从而可以有效删除设定间隔的数据。为了便于示例，我们修改了 blocks 表的模式，以按月分区。反过来，使用 TTL 功能可以有效地使超过五年的行过期。设置 ttl_only_drop_parts 确保仅当部件中的所有行都过期时才删除该部件。

CREATE TABLE blocks
(
...
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(timestamp)
ORDER BY timestampcoindesk
TTL timestamp + INTERVAL 60 MONTH DELETE
SETTINGS ttl_only_drop_parts=1

分区既可以正面和负面地影响查询，应将其更多地视为数据管理功能，而不是用于优化查询性能的工具。

此数据集值得一篇完整的博客文章来介绍可能的查询。 etherium-etl 工具的作者发布了一系列精彩的博客，重点介绍了有关此数据集的见解。在以后的博客中，我们将介绍这些查询，并展示如何将它们转换为 ClickHouse 语法，以及如何显着加速某些查询。在这里，我们从流行的加密可视化站点 dune.com 中提取了类似的查询。

一些重要的考虑因素

表模式可在此处找到。我们对两者使用相同的排序键，并使用前面描述的 Redshift 优化类型。
Redshift 和 ClickHouse 都提供查询结果缓存功能。我们明确禁用这些功能以进行这些测试，以提供缓存未命中时的性能测量。Redshift 还受益于首次执行后编译和缓存查询计划。鉴于此缓存是无限的，因此我们按照 Amazon 的建议，将每个查询运行两次。
我们使用 psql 客户端向 Redshift 发出查询。尽管时间与 UI 一致，但此客户端未获得官方支持。用户还可以使用 RSQL 客户端来发出查询。
这些查询使用与我们之前的基准测试相同的硬件执行
- ClickHouse Cloud 节点具有 60 个内核和 240GB RAM。
- Redshift 实例是一个预置实例，由 2xdc2.8xlarge 节点组成，总共提供 64 个内核和 488GB RAM。这是推荐的节点类型，适用于压缩后小于 1TB 的数据集的计算密集型工作负载。

本周已从 Dune 可视化中改编而来。有关 Gas 的说明，请参阅此处。我们修改查询以使用 receipt_gas_used 而不是 gas_used。在我们的预置型 Redshift 集群中，此查询执行时间为 66.3 秒。

SELECT
  date_trunc('week', block_timestamp) AS time,
  SUM(receipt_gas_used) AS total_gas_used,
  AVG(receipt_gas_used) AS avg_gas_used,
  percentile_cont(.5) within GROUP (
    ORDER BY
      receipt_gas_used
  ) AS median_gas_used
FROM
  transactions
WHERE
  block_timestamp >= '2015-10-01'
GROUP BY
  time
ORDER BY
  time ASC
LIMIT
  10;

        time         | total_gas_used | avg_gas_used | median_gas_used
---------------------+----------------+--------------+-----------------
 2015-09-28 00:00:00 |  695113747    |      27562    |      21000.0
 2015-10-05 00:00:00 |  1346460245   |      29208    |      21000.0
 2015-10-12 00:00:00 |  1845089516   |      39608    |      21000.0
 2015-10-19 00:00:00 |  1468537875   |      33573    |      21000.0
 2015-10-26 00:00:00 |  1876510203   |      37293    |      21000.0
 2015-11-02 00:00:00 |  2256326647   |      37741    |      21000.0
 2015-11-09 00:00:00 |  2229775112   |      38535    |      21000.0
 2015-11-16 00:00:00 |  1457079785   |      28520    |      21000.0
 2015-11-23 00:00:00 |  1477742844   |      29497    |      21000.0
 2015-11-30 00:00:00 |  1796228561   |      34517    |      21000.0
(10 rows)

Time: 66341.393 ms (01:06.341)

相比之下，我们的 60 核 ClickHouse Cloud 节点在 17 秒内完成此查询。请注意更简单的 quantile 语法，以及我们如何使用模式为 1 的 toStartOfWeek 函数来将星期一视为一周的开始。这提供了与 dune.com 和 Redshift 一致的结果。

SELECT
    toStartOfWeek(block_timestamp, 1) AS time,
    SUM(receipt_gas_used) AS total_gas_used,
    round(AVG(receipt_gas_used)) AS avg_gas_used,
    quantileExact(0.5)(receipt_gas_used) AS median_gas_used
FROM transactions
WHERE block_timestamp >= '2015-10-01'
GROUP BY time
ORDER BY time ASC
LIMIT 10

┌───────time─┬─total_gas_used─┬─avg_gas_used─┬─median_gas_used─┐
│ 2015-09-28 │  695113747     │     27562    │          21000  │
│ 2015-10-05 │  1346460245    │     29208    │          21000  │
│ 2015-10-12 │  1845089516    │     39609    │          21000  │
│ 2015-10-19 │  1468537875    │     33573    │          21000  │
│ 2015-10-26 │  1876510203    │     37294    │          21000  │
│ 2015-11-02 │  2256326647    │     37742    │          21000  │
│ 2015-11-09 │  2229775112    │     38535    │          21000  │
│ 2015-11-16 │  1457079785    │     28520    │          21000  │
│ 2015-11-23 │  1477742844    │     29498    │          21000  │
│ 2015-11-30 │  1796228561    │     34518    │          21000  │
└────────────┴────────────────┴──────────────┴─────────────────┘

10 rows in set. Elapsed: 17.287 sec. Processed 1.87 billion rows, 14.99 GB (108.39 million rows/s., 867.15 MB/s.)

这两个函数都利用 Percentiles 的精确计算。 Redshift 和 ClickHouse 中等效的估计函数（可能足以用于可视化）提供了提高性能的可能性。不幸的是，对于 Redshift，此函数受到集群大小的限制，从而导致我们的 128 RPU 无服务器实例出现以下错误

SELECT
  date_trunc('week', block_timestamp) AS time,
  SUM(receipt_gas_used) AS total_gas_used,
  AVG(receipt_gas_used) AS avg_gas_used,
  APPROXIMATE percentile_disc(.5) within GROUP (
    ORDER BY
      receipt_gas_used
  ) AS median_gas_used
FROM
  transactions
WHERE
  block_timestamp >= '2015-10-01'
GROUP BY
  time
ORDER BY
  time ASC
LIMIT
  10;

ERROR:  1036
DETAIL:  GROUP BY limit for approximate percentile_disc exceeded.
The number of groups returned by the GROUP BY clause exceeds the limit for your cluster size. Consider using percentile_cont instead. (pid:13074)

对于 ClickHouse，此查询在不到 1.7 秒内返回，这是一个巨大的改进。

SELECT
    toStartOfWeek(block_timestamp,1) AS time,
    SUM(receipt_gas_used) AS total_gas_used,
    round(AVG(receipt_gas_used)) AS avg_gas_used,
    quantile(0.5)(receipt_gas_used) AS median_gas_used
FROM transactions
WHERE block_timestamp >= '2015-10-01'
GROUP BY time
ORDER BY time ASC
LIMIT 10

┌───────time─┬─total_gas_used─┬─avg_gas_used─┬─median_gas_used─┐
│ 2015-09-28 │  695113747     │     27562    │          21000  │
│ 2015-10-05 │  1346460245    │     29208    │          21000  │
│ 2015-10-12 │  1845089516    │     39609    │          21000  │
│ 2015-10-19 │  1468537875    │     33573    │          21000  │
│ 2015-10-26 │  1876510203    │     37294    │          21000  │
│ 2015-11-02 │  2256326647    │     37742    │          21000  │
│ 2015-11-09 │  2229775112    │     38535    │          21000  │
│ 2015-11-16 │  1457079785    │     28520    │          21000  │
│ 2015-11-23 │  1477742844    │     29498    │          21000  │
│ 2015-11-30 │  1796228561    │     34518    │          21000  │
└────────────┴────────────────┴──────────────┴─────────────────┘

10 rows in set. Elapsed: 1.692 sec. Processed 1.87 billion rows, 14.99 GB (1.11 billion rows/s., 8.86 GB/s.)

我们从 dune.com 可视化中改编了这个查询。我们删除了 now() 限制，因为我们的数据具有固定的上限。由于 Redshift 不支持窗口 RANGE 函数，因此我们也被迫稍微修改查询以计算累积总和。尽管两个表都按 trace_type 排序，但 ClickHouse 运行此查询的时间为 76 毫秒，而 Redshift 为 250 毫秒。

SELECT
  date_trunc('week', block_timestamp) AS time,
  COUNT(*) AS created_contracts,
  sum(created_contracts) OVER (
    ORDER BY
    time rows UNBOUNDED PRECEDING
  ) AS cum_created_contracts
from
  traces
WHERE
  trace_type = 'create'
GROUP BY
  time
ORDER BY
  time ASC
LIMIT
  10;
        time        | created_contracts | cum_created_contracts
---------------------+-------------------+-----------------------
 2015-08-03 00:00:00 |              139 |                   139
 2015-08-10 00:00:00 |              204 |                   343
 2015-08-17 00:00:00 |              189 |                   532
 2015-08-24 00:00:00 |              204 |                   736
 2015-08-31 00:00:00 |              266 |                  1002
 2015-09-07 00:00:00 |              252 |                  1254
 2015-09-14 00:00:00 |              293 |                  1547
 2015-09-21 00:00:00 |              274 |                  1821
 2015-09-28 00:00:00 |              129 |                  1950
 2015-10-05 00:00:00 |              143 |                  2093
(10 rows)

Time: 236.261 ms

SELECT
    toStartOfWeek(block_timestamp, 1) AS time,
    count() AS created_contracts,
    sum(created_contracts) OVER (ORDER BY time ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_created_contracts
FROM traces
WHERE trace_type = 'create'
GROUP BY time
ORDER BY time ASC
LIMIT 10

┌───────time─┬─created_contracts─┬─cum_created_contracts─┐
│ 2015-08-03 │              139  │                  139  │
│ 2015-08-10 │              204  │                  343  │
│ 2015-08-17 │              189  │                  532  │
│ 2015-08-24 │              204  │                  736  │
│ 2015-08-31 │              266  │                  1002 │
│ 2015-09-07 │              252  │                  1254 │
│ 2015-09-14 │              293  │                  1547 │
│ 2015-09-21 │              274  │                  1821 │
│ 2015-09-28 │              129  │                  1950 │
│ 2015-10-05 │              143  │                  2093 │
└────────────┴───────────────────┴───────────────────────┘

10 rows in set. Elapsed: 0.076 sec. Processed 58.08 million rows, 290.39 MB (767.20 million rows/s., 3.84 GB/s.)

原始 BigQuery 查询记录为 Awesome BigQuery 视图的一部分，并在此处讨论，在 Redshift 中执行时间为 428 毫秒。ClickHouse 查询运行时间为 87 毫秒。使用投影，此查询可以进一步优化，使其运行时间少于 10 毫秒。

WITH ether_emitted_by_date AS (
  SELECT
    date(block_timestamp) AS date,
    SUM(value) AS value
  FROM
    traces
  WHERE
    trace_type IN ('genesis', 'reward')
  GROUP BY
    DATE(block_timestamp)
)
SELECT
  date,
  SUM(value) OVER (
    ORDER BY
      date ASC ROWS BETWEEN UNBOUNDED PRECEDING
      AND CURRENT ROW
  ) / POWER(10, 18) AS supply
FROM
  ether_emitted_by_date
LIMIT
  10;

    date    |   supply
------------+----------------
 1970-01-01 | 72009990.49948
 2015-07-30 | 72049301.59323
 2015-07-31 | 72085493.31198
 2015-08-01 | 72113195.49948
 2015-08-02 | 72141422.68698
 2015-08-03 | 72169399.40573
 2015-08-04 | 72197877.84323
 2015-08-05 | 72225406.43698
 2015-08-06 | 72252481.90573
 2015-08-07 | 72279919.56198
(10 rows)

Time: 428.202 ms

带投影和不带投影的 ClickHouse

WITH ether_emitted_by_date AS
    (
        SELECT
            date(block_timestamp) AS date,
            SUM(value) AS value
        FROM traces
        WHERE trace_type IN ('genesis', 'reward')
        GROUP BY DATE(block_timestamp)
    )
SELECT
    date,
    SUM(value) OVER (ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / POWER(10, 18) AS supply
FROM ether_emitted_by_date
LIMIT 10
┌───────date─┬────────────supply─┐
│ 1970-01-01 │ 72009990.49948001 │
│ 2015-07-30 │ 72049301.59323001 │
│ 2015-07-31 │  72085493.31198   │
│ 2015-08-01 │  72113195.49948   │
│ 2015-08-02 │  72141422.68698   │

10 rows in set. Elapsed: 0.087 sec. Processed 18.08 million rows, 379.73 MB (207.34 million rows/s., 4.35 GB/s.)

-- add projections

ALTER TABLE traces ADD PROJECTION trace_type_projection (
                 SELECT trace_type,
                 toStartOfDay(block_timestamp) as date, sum(value) as value GROUP BY trace_type, date
                 )
ALTER TABLE traces MATERIALIZE PROJECTION trace_type_projection

-- re-run query
WITH ether_emitted_by_date AS
    (
     SELECT
         date,
         sum(value) AS value
     FROM traces
     WHERE trace_type IN ('genesis', 'reward')
     GROUP BY toStartOfDay(block_timestamp) AS date
    )
SELECT
    date,
    sum(value) OVER (ORDER BY date ASC) / power(10, 18) AS supply
FROM ether_emitted_by_date

3 rows in set. Elapsed: 0.009 sec. Processed 11.43 thousand rows, 509.00 KB (1.23 million rows/s., 54.70 MB/s.)

这是一个从 dune.com 可视化修改而来的查询，用于估计以太坊的总市值。在这里，我们使用了 CoinDesk 提供的固定价格 1577.88，因为我们的数据是快照，最新日期为 2023-02-14 19:34:59。在我们的 Redshift 预置实例中，此查询失败，如下所示（在查询 UI 中也会发生）。

SELECT
  120529053 - SUM(eb.base_fee_per_gas * et.gas) / 1e18 -- missing  ETH2 rewards for now, awaiting beacon chain data, using estimated 1600 ETH staking issuance /day for now
  + COUNT(eb.number) * 1600 /(24 * 60 * 60 / 12) AS eth_supply
FROM
  transactions et
  INNER JOIN blocks eb ON eb.number = et.block_number
WHERE
  et.block_timestamp >= '2022-09-29'
)
SELECT
  (eth_supply * 1577.88) / 1e9 AS eth_mcap
FROM
  eth_supply;

ERROR:  Numeric data overflow (addition)
DETAIL:
  -----------------------------------------------
  error:  Numeric data overflow (addition)
  code:     1058
  context:
  query:    4602282
  location:  numeric_bound.cpp:180
  process:   query10_500_4602282 [pid=31250]
  -----------------------------------------------

在我们的 60 核 ClickHouse Cloud 节点上，此查询运行时间为 3.2 秒。

WITH eth_supply AS
    (
        SELECT (120529053 - (SUM(eb.base_fee_per_gas * et.receipt_gas_used) / 1000000000000000000.)) + ((COUNT(eb.number) * 1600) / (((24 * 60) * 60) / 12)) AS eth_supply
        FROM transactions AS et
        INNER JOIN blocks AS eb ON eb.number = et.block_number
        WHERE et.block_timestamp >= '2022-09-29'
    )
SELECT (eth_supply * 1577.88) / 1000000000. AS eth_mcap
FROM eth_supply
┌───────────eth_mcap─┐
│ 251.42266710943835 │
└────────────────────┘

1 row in set. Elapsed: 3.220 sec. Processed 191.48 million rows, 2.30 GB (59.47 million rows/s., 713.69 MB/s.)

此值与 dune.com 计算的值一致。可以在此处找到完整的示例查询集。欢迎贡献！

在本博客文章中，我们探讨了如何将数据从 Redshift 移动到 ClickHouse，以加速实时分析的查询。我们展示了多种加载数据并使其保持同步的方法，以及如何利用 ClickHouse 在此数据之上进行实时分析。在以后的文章中，我们将更详细地探讨此 Ethereum 数据集。

同时，我们已在公共 ClickHouse 部署中提供此数据集以供探索 (sql.clickhouse.com) 和 gcs 存储桶 gs://clickhouse_public_datasets/ethereum。欢迎您通过下载免费的开源版本的 ClickHouse 并自行部署，或启动 ClickHouse Cloud 免费试用版来尝试。ClickHouse Cloud 是一种基于 ClickHouse 的完全托管的无服务器产品，您可以在其中轻松开始构建实时应用程序，而无需担心部署和管理基础设施。

我们推荐以下关于 Ethereum 和查询此数据集的资源。