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

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

虽然由于与 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 用户通常出于以下原因用 ClickHouse 替换或增强 Redshift

• **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 预配 - 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 未提供此数据集，因此可以使用优秀的以太坊 ETL 工具生成，已提交了一个支持ClickHouse 作为目标的 PR。或者，我们已在 s3 存储桶 s3://datasets-documentation/ethereum/ 中提供了此数据的快照，供我们的用户探索，以及在我们的公共游乐场中，该游乐场保持更新。数据的完整最新版本也可在 gcs 存储桶 gs://clickhouse_public_datasets/ethereum 中找到。

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

不幸的是，我们无法为此任务使用 COPY 命令，因为某些列无法加载（例如，字符串长度超过Redshift 65k 字符的限制）或加载时需要日期转换。因此，我们的示例使用了 Redshift 的通过 Spectrum 查询 S3 中数据的能力，并使用外部表公开 s3 文件，然后通过INSERT INTO SELECT（可以在其中选择列的子集并进行 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	139.94 GB	1.3
blocks	10.9 GiB	10.74 GiB	5.37 GB	2
traces	710.1 GiB	615.46 GiB	339.95	1.8
contracts	16.0 GiB	12.51 GiB	2.73 GB	4.6
总计	989.4 GiB	826.24 GiB	487.99 GiB	2

如所示，ClickHouse 比最佳 Redshift 模式更有效地压缩数据，对于此数据集，组合比率为 2 倍。

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

如所示，我们的 60 核 ClickHouse Cloud 节点平均速度是同类 Redshift 集群的 2.5 倍。 欢迎探索其他对比，其中 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
DATE	Date32
TIMESTAMP	DateTime，DateTime64
TIMESTAMPTZ	DateTime，DateTime64
GEOMETRY	地理数据类型
GEOGRAPHY	地理数据类型（功能尚不完善，例如没有坐标系 - 可以通过 函数 模拟）
HLLSKETCH	AggregateFunction(uniqHLL12, X)
SUPER	元组，嵌套数据结构，数组，JSON，映射
TIME	DateTime，DateTime64
TIMETZ	DateTime，DateTime64
VARBYTE**	String结合 位操作函数 和 编码函数

_{* ClickHouse 还支持无符号整数，并且具有扩展的取值范围，例如 UInt8, UInt32, UInt32 和 UInt64。
**ClickHouse 的 String 类型默认情况下是无限的，但可以使用 约束 限制为特定长度。}

当 ClickHouse 类型有多个选项时，请考虑数据的实际范围并选择最低要求。

ClickHouse 和 Redshift 支持常见的压缩算法，包括 ZSTD。除了将 增量编码应用于整数和日期序列 外，我们通常发现 ZSTD 是最广泛适用的压缩算法，并且在大多数情况下都能提供最佳结果。

在使用 COPY 命令复制数据时，Redshift 允许自动检测每列的最佳压缩算法，使用 COMPUPDATE ON 选项（对导入数据类型有一些限制，例如，不支持 Parquet）。我们通常发现这也会为大多数列建议 ZSTD，这与我们自己的发现一致。或者，用户可以使用 ANALYZE COMPRESSION 命令请求一个包含预计空间节省量的最佳模式。我们将这些建议应用于我们所有的表模式。

目前，ClickHouse 中的编解码器必须在创建表时指定。但是，这些编解码器可以组合使用（例如，CODEC(Delta, ZSTD)）。此外，ClickHouse 允许调整这些压缩算法，通常以牺牲压缩或解压缩速度为代价来提高空间节省率（例如，ZSTD(9) 比 ZSTD(3) 提供更高的压缩率，但代价是压缩速度较慢，但在查询时的解压缩性能基本保持一致）。这种增强的可调性帮助 ClickHouse 实现了更高的压缩率。

ClickHouse 和 Redshift 都具有“排序键”的概念，它定义了存储数据时的排序方式。Redshift 使用 SORTKEY 子句定义排序键。

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

相比之下，ClickHouse 使用 ORDER BY 子句指定排序顺序。

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

在大多数情况下，用户可以在 ClickHouse 中使用与 Redshift 相同的排序键列和顺序，假设您使用的是默认的 COMPOUND 类型。当数据添加到 Redshift 时，您应该运行 VACUUM 和 ANALYZE 命令以重新排序新添加的数据并更新查询计划程序的统计信息 - 否则，未排序的空间会增长。ClickHouse 不需要此类过程。

Redshift 支持一些用于排序键的便利功能。一个是自动排序键（使用 SORTKEY AUTO），这可能适合入门，但显式排序键可以确保在 排序键最优 时获得最佳性能和存储效率。另一个是 INTERLEAVED 排序键，它在排序键中对列子集给予相等的权重，以提高在查询使用一个或多个辅助排序列时的性能。ClickHouse 支持显式的 投影，它可以通过略微不同的设置达到相同的最终结果。

用户应该意识到，“主键”的概念在 ClickHouse 和 Redshift 中代表不同的含义。在 Redshift 中，主键类似于传统的 RDMS 概念，旨在执行约束。但是，它们在 Redshift 中没有严格执行，而是充当 查询计划程序 和 节点之间的数据分发 的提示。在 ClickHouse 中，主键表示用于构建 稀疏主键索引 的列，用于确保数据按磁盘顺序排列，最大化压缩同时避免主键污染和浪费内存。

在此示例中，我们假设我们的数据仅存在于 Redshift 中，并且我们不熟悉等效的 ClickHouse 以太坊模式。任何 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

我们对这些模式进行了基本优化，使用了适当的类型和编解码器以最大程度地减少存储。例如，我们没有将列设置为可为空，尽管它们在原始模式中是可为空的，因为对于大多数查询，无需区分默认值和空值。通过使用默认值，我们避免了与可为空相关的额外 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

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

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

表	文件数量	Parquet 大小 (GB)	Redshift Serverless (128 RPUs)	Redshift (2xdc2.8xlarge)
Blocks	128	10.9GiB	4.9s	18.4s
Contracts	128	16.0 GiB	2m 43.9s	22.5s
Transactions	128	252.4 GiB	4m 40s	10m 14.1s
Traces	128	710.1 GiB	5m 36.1s	29m 37.8s

细心的读者会注意到，我们所有类型的文件都有 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 中没有等效表示，此列导出为字符串。下面我们将它映射回 ClickHouse 中的数组类型。

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 亿行数据从 Redshift 传输到 ClickHouse。

表	行数	数据大小 (Parquet)	Redshift 导出	ClickHouse 导入
blocks	16629116	10.9GiB	4.9s	14.28 s
contracts	57394746	16.0 GiB	2m 43.9s	21.24 s
transactions	1874052391	252.4 GiB	4m 40s	5 分钟 15 秒
traces	6377694114	710.1 GiB	5m 36.1s	15 分钟 34 秒
总计	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 集中创建、运行和管理计划任务。

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

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

• 我们使用基于 cron 的计划，使用表达式 15 * * * ? * 每小时 15 分钟运行一次，即此执行没有灵活性。
• 计划利用 Redshift 数据 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)

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

在撰写本文时，ClickHouse 还没有内置的计划导入方法（提案正在讨论中）。我们将在下面探讨定期外部导入这些文件的方法。

为了初始的简单方法，并说明逻辑，以下 bash 脚本可以在导出完成后定期由 cron 作业运行。该脚本首先获取 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 作为 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 目标来实现这一点，因为此功能允许通过 HTTP 使用基本身份验证连接到外部服务。EventBridge 规则将由导出计划触发，并将以下查询发送到 ClickHouse，利用作业中公开的 $.time 变量（这是导出计划的时间）。但是，ClickHouse 中的更改数据查询必须通过 POST 请求发送。目前，EventBridge 中的规则目标将以 JSON 或带引号的字符串格式发送请求正文（查询）。ClickHouse 不支持此功能。我们正在探索支持官方 ClickHouse 事件源的可能性。

如果我们执行批量导入，然后计划上述导入和导出查询，我们将在批量加载完成和增量加载开始之间的时间段内不可避免地出现数据“空白”。为了解决这个问题，我们可以使用与我们在 BigQuery 数据之上使用 ClickHouse 提供实时查询博文中记录的技术相同（请参阅“填补空白”）。

上述方法假设一个批处理更新过程，需要正确编排导出和导入步骤。利用 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 命令所需。以下示例从预置集群读取，但可以修改为连接无服务器集群（如果需要）。

我们重点介绍了将提供的脚本部署到 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 支持此功能，而不是在脚本中。可以使用boto3 库检索这些凭据，前提是配置了所需的 IAM 权限。

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

对于大多数部署，ClickHouse 优越的数据压缩意味着您可以长时间以细粒度格式存储数据。对于我们特定的以太坊数据集，这可能没有特别的好处，因为我们可能需要为许多查询保留区块链的完整历史记录，例如，计算账户余额。

但是，有一些简单且可扩展的方法可以删除旧数据，如果您希望只在 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 实例是一个预置实例，由2 个 dc2.8xlarge 节点组成，总共提供 64 个核心和 488GB RAM。这是建议用于压缩后数据集小于 1TB 的计算密集型工作负载的节点类型。

此查询改编自此 Dune可视化。有关燃气费的解释，请参阅此处。我们将查询修改为使用 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 秒内完成了此查询。请注意，更简单的分位数语法以及我们如何使用 toStartOfWeek 函数（模式为 1）将星期一视为一周的开始。这提供了与 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.)

这两个函数都使用了百分位数的精确计算。在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 函数，因此我们也必须稍微修改查询以计算累积和。ClickHouse 在 76 毫秒内运行此查询，而 Redshift 在 250 毫秒内运行，尽管两个表都按 trace_type 排序。

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 进行实时分析。在后续文章中，我们将更详细地探讨此以太坊数据集。

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

我们推荐以下与以太坊和查询此数据集相关的资源。

• 如何将时间序列数据从 Google BigQuery 重放至 Pub/Sub
• Evgeny Medvedev 关于区块链分析的系列文章
• BigQuery 中的以太坊：用于智能合约分析的公共数据集
• 加密货币的 Awesome BigQuery 视图
• 如何在 BigQuery 中查询所有以太坊地址的余额
• 可视化以太坊平均成本随时间的变化
• 在 BigQuery 中绘制以太坊地址增长图表
• 使用 Google BigQuery 和 Google Data Studio 比较 8 个区块链的交易吞吐量
• 在 BigQuery 公共数据集中引入六种新的加密货币——以及如何分析它们
• dune.com，获取查询灵感