将 S3 与 ClickHouse 集成

您可以将数据从 S3 插入到 ClickHouse，也可以将 S3 用作导出目标，从而实现与“数据湖”架构的交互。此外，S3 可以提供“冷”存储层，并协助分离存储和计算。在下面的章节中，我们将使用纽约市出租车数据集来演示在 S3 和 ClickHouse 之间移动数据、识别关键配置参数以及提供优化性能的提示的过程。

S3 表函数

s3 表函数允许您从 S3 兼容存储读取和写入文件。此语法的概要如下：

s3(path, [aws_access_key_id, aws_secret_access_key,] [format, [structure, [compression]]])

其中

• path — 带有指向文件的存储桶 URL。在只读模式下，它支持以下通配符：*、?、{abc,def} 和 {N..M}，其中 N、M 是数字，'abc'、'def' 是字符串。有关更多信息，请参阅关于 在路径中使用通配符 的文档。
• format — 文件的 格式。
• structure — 表的结构。格式为 'column1_name column1_type, column2_name column2_type, ...'。
• compression — 参数是可选的。支持的值：none、gzip/gz、brotli/br、xz/LZMA、zstd/zst。默认情况下，它将通过文件扩展名自动检测压缩。

在路径表达式中使用通配符允许引用多个文件，并为并行性打开了大门。

准备

在 ClickHouse 中创建表之前，您可能需要先仔细查看 S3 存储桶中的数据。您可以使用 DESCRIBE 语句直接从 ClickHouse 执行此操作

DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_*.gz', 'TabSeparatedWithNames');

DESCRIBE TABLE 语句的输出应该向您展示 ClickHouse 将如何自动推断 S3 存储桶中查看到的数据。请注意，它还会自动识别和解压缩 gzip 压缩格式

DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_*.gz', 'TabSeparatedWithNames') SETTINGS describe_compact_output=1

┌─name──────────────────┬─type───────────────┐
│ trip_id               │ Nullable(Int64)    │
│ vendor_id             │ Nullable(Int64)    │
│ pickup_date           │ Nullable(Date)     │
│ pickup_datetime       │ Nullable(DateTime) │
│ dropoff_date          │ Nullable(Date)     │
│ dropoff_datetime      │ Nullable(DateTime) │
│ store_and_fwd_flag    │ Nullable(Int64)    │
│ rate_code_id          │ Nullable(Int64)    │
│ pickup_longitude      │ Nullable(Float64)  │
│ pickup_latitude       │ Nullable(Float64)  │
│ dropoff_longitude     │ Nullable(Float64)  │
│ dropoff_latitude      │ Nullable(Float64)  │
│ passenger_count       │ Nullable(Int64)    │
│ trip_distance         │ Nullable(String)   │
│ fare_amount           │ Nullable(String)   │
│ extra                 │ Nullable(String)   │
│ mta_tax               │ Nullable(String)   │
│ tip_amount            │ Nullable(String)   │
│ tolls_amount          │ Nullable(Float64)  │
│ ehail_fee             │ Nullable(Int64)    │
│ improvement_surcharge │ Nullable(String)   │
│ total_amount          │ Nullable(String)   │
│ payment_type          │ Nullable(String)   │
│ trip_type             │ Nullable(Int64)    │
│ pickup                │ Nullable(String)   │
│ dropoff               │ Nullable(String)   │
│ cab_type              │ Nullable(String)   │
│ pickup_nyct2010_gid   │ Nullable(Int64)    │
│ pickup_ctlabel        │ Nullable(Float64)  │
│ pickup_borocode       │ Nullable(Int64)    │
│ pickup_ct2010         │ Nullable(String)   │
│ pickup_boroct2010     │ Nullable(String)   │
│ pickup_cdeligibil     │ Nullable(String)   │
│ pickup_ntacode        │ Nullable(String)   │
│ pickup_ntaname        │ Nullable(String)   │
│ pickup_puma           │ Nullable(Int64)    │
│ dropoff_nyct2010_gid  │ Nullable(Int64)    │
│ dropoff_ctlabel       │ Nullable(Float64)  │
│ dropoff_borocode      │ Nullable(Int64)    │
│ dropoff_ct2010        │ Nullable(String)   │
│ dropoff_boroct2010    │ Nullable(String)   │
│ dropoff_cdeligibil    │ Nullable(String)   │
│ dropoff_ntacode       │ Nullable(String)   │
│ dropoff_ntaname       │ Nullable(String)   │
│ dropoff_puma          │ Nullable(Int64)    │
└───────────────────────┴────────────────────┘

为了与我们基于 S3 的数据集交互，我们准备了一个标准的 MergeTree 表作为我们的目标。以下语句在默认数据库中创建一个名为 trips 的表。请注意，我们选择修改一些上述推断的数据类型，特别是不要使用 Nullable() 数据类型修饰符，这可能会导致一些不必要的额外存储数据和一些额外的性能开销

CREATE TABLE trips
(
    `trip_id` UInt32,
    `vendor_id` Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15),
    `pickup_date` Date,
    `pickup_datetime` DateTime,
    `dropoff_date` Date,
    `dropoff_datetime` DateTime,
    `store_and_fwd_flag` UInt8,
    `rate_code_id` UInt8,
    `pickup_longitude` Float64,
    `pickup_latitude` Float64,
    `dropoff_longitude` Float64,
    `dropoff_latitude` Float64,
    `passenger_count` UInt8,
    `trip_distance` Float64,
    `fare_amount` Float32,
    `extra` Float32,
    `mta_tax` Float32,
    `tip_amount` Float32,
    `tolls_amount` Float32,
    `ehail_fee` Float32,
    `improvement_surcharge` Float32,
    `total_amount` Float32,
    `payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
    `trip_type` UInt8,
    `pickup` FixedString(25),
    `dropoff` FixedString(25),
    `cab_type` Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
    `pickup_nyct2010_gid` Int8,
    `pickup_ctlabel` Float32,
    `pickup_borocode` Int8,
    `pickup_ct2010` String,
    `pickup_boroct2010` String,
    `pickup_cdeligibil` String,
    `pickup_ntacode` FixedString(4),
    `pickup_ntaname` String,
    `pickup_puma` UInt16,
    `dropoff_nyct2010_gid` UInt8,
    `dropoff_ctlabel` Float32,
    `dropoff_borocode` UInt8,
    `dropoff_ct2010` String,
    `dropoff_boroct2010` String,
    `dropoff_cdeligibil` String,
    `dropoff_ntacode` FixedString(4),
    `dropoff_ntaname` String,
    `dropoff_puma` UInt16
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(pickup_date)
ORDER BY pickup_datetime

请注意 pickup_date 字段上的 分区 的使用。通常，分区键用于数据管理，但稍后我们将使用此键来并行化写入 S3。

我们出租车数据集中的每个条目都包含一次出租车行程。此匿名数据由 S3 存储桶 https://datasets-documentation.s3.eu-west-3.amazonaws.com/ 下的 nyc-taxi 文件夹中的 20M 条记录压缩而成。数据采用 TSV 格式，每文件大约包含 1M 行。

从 S3 读取数据

我们可以将 S3 数据查询为源，而无需将其持久化到 ClickHouse。在以下查询中，我们采样 10 行。请注意，由于存储桶是公开可访问的，因此此处没有凭据

SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_*.gz', 'TabSeparatedWithNames')
LIMIT 10;

请注意，我们不需要列出列，因为 TabSeparatedWithNames 格式将列名编码在第一行中。其他格式，例如 CSV 或 TSV，将为该查询返回自动生成的列，例如 c1、c2、c3 等。

查询还支持 虚拟列，例如 _path 和 _file，它们提供有关存储桶路径和文件名的信息。例如

SELECT  _path, _file, trip_id
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_0.gz', 'TabSeparatedWithNames')
LIMIT 5;

┌─_path──────────────────────────────────────┬─_file──────┬────trip_id─┐
│ datasets-documentation/nyc-taxi/trips_0.gz │ trips_0.gz │ 1199999902 │
│ datasets-documentation/nyc-taxi/trips_0.gz │ trips_0.gz │ 1199999919 │
│ datasets-documentation/nyc-taxi/trips_0.gz │ trips_0.gz │ 1199999944 │
│ datasets-documentation/nyc-taxi/trips_0.gz │ trips_0.gz │ 1199999969 │
│ datasets-documentation/nyc-taxi/trips_0.gz │ trips_0.gz │ 1199999990 │
└────────────────────────────────────────────┴────────────┴────────────┘

确认此样本数据集中的行数。请注意，我们使用通配符进行文件扩展，因此我们考虑所有二十个文件。此查询将需要大约 10 秒，具体取决于 ClickHouse 实例上的内核数

SELECT count() AS count
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_*.gz', 'TabSeparatedWithNames');

┌────count─┐
│ 20000000 │
└──────────┘

虽然对于采样数据和执行即席查询很有用，但直接从 S3 读取数据并不是您想定期执行的操作。当准备好认真对待时，将数据导入到 ClickHouse 中的 MergeTree 表中。

使用 clickhouse-local

clickhouse-local 程序使您能够在不部署和配置 ClickHouse 服务器的情况下对本地文件执行快速处理。可以使用此实用程序执行任何使用 s3 表函数的查询。例如

clickhouse-local --query "SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_*.gz', 'TabSeparatedWithNames') LIMIT 10"

从 S3 插入数据

为了利用 ClickHouse 的全部功能，接下来我们将数据读取并插入到我们的实例中。我们将我们的 s3 函数与一个简单的 INSERT 语句结合使用来实现这一点。请注意，我们不需要列出我们的列，因为我们的目标表提供了所需的结构。这要求列以表 DDL 语句中指定的顺序出现：列根据它们在 SELECT 子句中的位置进行映射。插入所有 10m 行可能需要几分钟，具体取决于 ClickHouse 实例。下面我们插入 1M 行以确保快速响应。根据需要调整 LIMIT 子句或列选择以导入子集

INSERT INTO trips
   SELECT *
   FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_*.gz', 'TabSeparatedWithNames')
   LIMIT 1000000;

使用 ClickHouse Local 进行远程插入

如果网络安全策略阻止您的 ClickHouse 集群进行出站连接，您可以使用 clickhouse-local 潜在地插入 S3 数据。在下面的示例中，我们从 S3 存储桶读取数据，并使用 remote 函数将其插入到 ClickHouse 中

clickhouse-local --query "INSERT INTO TABLE FUNCTION remote('localhost:9000', 'default.trips', 'username', 'password') (*) SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_*.gz', 'TabSeparatedWithNames') LIMIT 10"

注意

要通过安全的 SSL 连接执行此操作，请使用 remoteSecure 函数。

导出数据

您可以使用 s3 表函数写入 S3 中的文件。这将需要适当的权限。我们将请求中传递所需的凭据，但请查看 管理凭据 页面以获取更多选项。

在下面的简单示例中，我们使用表函数作为目标而不是源。在这里，我们将 10,000 行从 trips 表流式传输到存储桶，指定 lz4 压缩和 CSV 输出类型

INSERT INTO FUNCTION
   s3(
       'https://datasets-documentation.s3.eu-west-3.amazonaws.com/csv/trips.csv.lz4',
       's3_key',
       's3_secret',
       'CSV'
    )
SELECT *
FROM trips
LIMIT 10000;

请注意，文件格式是如何从扩展名推断出来的。我们也不需要在 s3 函数中指定列 - 这可以从 SELECT 推断出来。

拆分大文件

您不太可能希望将数据导出为单个文件。大多数工具，包括 ClickHouse，由于并行性的可能性，在读取和写入多个文件时将实现更高的吞吐量性能。我们可以多次执行我们的 INSERT 命令，针对数据的子集。ClickHouse 提供了一种使用 PARTITION 键自动拆分文件的手段。

在下面的示例中，我们使用 rand() 函数的模数创建十个文件。请注意，结果分区 ID 在文件名中引用。这会产生十个带有数字后缀的文件，例如 trips_0.csv.lz4、trips_1.csv.lz4 等...

INSERT INTO FUNCTION
   s3(
       'https://datasets-documentation.s3.eu-west-3.amazonaws.com/csv/trips_{_partition_id}.csv.lz4',
       's3_key',
       's3_secret',
       'CSV'
    )
    PARTITION BY rand() % 10
SELECT *
FROM trips
LIMIT 100000;

或者，我们可以引用数据中的一个字段。对于此数据集，payment_type 提供了一个具有 5 个基数的自然分区键。

INSERT INTO FUNCTION
   s3(
       'https://datasets-documentation.s3.eu-west-3.amazonaws.com/csv/trips_{_partition_id}.csv.lz4',
       's3_key',
       's3_secret',
       'CSV'
    )
    PARTITION BY payment_type
SELECT *
FROM trips
LIMIT 100000;

利用集群

上述函数都限制为在单个节点上执行。读取速度将随 CPU 内核线性扩展，直到其他资源（通常是网络）饱和，从而允许用户垂直扩展。但是，这种方法有其局限性。虽然您可以通过在执行 INSERT INTO SELECT 查询时插入到分布式表中来缓解一些资源压力，但这仍然使单个节点读取、解析和处理数据。为了解决这一挑战并允许我们水平扩展读取，我们拥有 s3Cluster 函数。

接收查询的节点（称为发起者）创建到集群中每个节点的连接。确定需要读取哪些文件的 glob 模式解析为一组文件。发起者将文件分发到集群中的节点，这些节点充当工作者。这些工作者反过来，在完成读取时请求文件进行处理。此过程确保我们可以水平扩展读取。

s3Cluster 函数采用与单节点变体相同的格式，除了需要目标集群以指示工作节点

s3Cluster(cluster_name, source, [access_key_id, secret_access_key,] format, structure)

• cluster_name — 用于构建远程和本地服务器的地址和连接参数的集群名称。
• source — 文件或一堆文件的 URL。在只读模式下，它支持以下通配符：*、?、{'abc','def'} 和 {N..M}，其中 N、M 是数字，abc、def 是字符串。有关更多信息，请参阅 路径中的通配符。
• access_key_id 和 secret_access_key — 指定用于给定端点的凭据的密钥。可选。
• format — 文件的 格式。
• structure — 表的结构。格式为 'column1_name column1_type, column2_name column2_type, ...'。

与任何 s3 函数一样，如果存储桶不安全或您通过环境定义安全性（例如，IAM 角色），则凭据是可选的。但是，与 s3 函数不同，从 22.3.1 开始，结构必须在请求中指定，即不推断模式。

这个函数在大多数情况下将用作 INSERT INTO SELECT 的一部分。在这种情况下，你通常会插入一个分布式表。下面我们举一个简单的例子，其中 trips_all 是一个分布式表。虽然此表使用 events 集群，但读取和写入所用节点的 consistency 并不是必需的

INSERT INTO default.trips_all
   SELECT *
   FROM s3Cluster(
       'events',
       'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_*.gz',
       'TabSeparatedWithNames'
    )

插入操作将发生在 initiator 节点上。这意味着虽然读取操作将在每个节点上发生，但生成的行将被路由到 initiator 进行分发。在高吞吐量场景下，这可能会成为瓶颈。为了解决这个问题，请为 s3cluster 函数设置参数 parallel_distributed_insert_select。

S3 表引擎

虽然 s3 函数允许对存储在 S3 中的数据执行临时查询，但它们的语法却很繁琐。S3 表引擎允许你无需一遍又一遍地指定 bucket URL 和凭据。为了解决这个问题，ClickHouse 提供了 S3 表引擎。

CREATE TABLE s3_engine_table (name String, value UInt32)
    ENGINE = S3(path, [aws_access_key_id, aws_secret_access_key,] format, [compression])
    [SETTINGS ...]

• path — 包含文件的 bucket URL 和路径。在只读模式下支持以下通配符：*、?、{abc,def} 和 {N..M}，其中 N、M 是数字，'abc'、'def' 是字符串。有关更多信息，请参阅 此处。
• format — 文件的格式。
• aws_access_key_id, aws_secret_access_key - AWS 账户用户的长期凭据。你可以使用这些凭据来验证你的请求。该参数是可选的。如果未指定凭据，则使用配置文件中的值。有关更多信息，请参阅 管理凭据。
• compression — 压缩类型。支持的值：none, gzip/gz, brotli/br, xz/LZMA, zstd/zst。该参数是可选的。默认情况下，它将通过文件扩展名自动检测压缩。

读取数据

在下面的示例中，我们使用位于 https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/ bucket 中的前十个 TSV 文件创建了一个名为 trips_raw 的表。每个文件包含 1M 行

CREATE TABLE trips_raw
(
   `trip_id`               UInt32,
   `vendor_id`             Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15),
   `pickup_date`           Date,
   `pickup_datetime`       DateTime,
   `dropoff_date`          Date,
   `dropoff_datetime`      DateTime,
   `store_and_fwd_flag`    UInt8,
   `rate_code_id`          UInt8,
   `pickup_longitude`      Float64,
   `pickup_latitude`       Float64,
   `dropoff_longitude`     Float64,
   `dropoff_latitude`      Float64,
   `passenger_count`       UInt8,
   `trip_distance`         Float64,
   `fare_amount`           Float32,
   `extra`                 Float32,
   `mta_tax`               Float32,
   `tip_amount`            Float32,
   `tolls_amount`          Float32,
   `ehail_fee`             Float32,
   `improvement_surcharge` Float32,
   `total_amount`          Float32,
   `payment_type_`         Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
   `trip_type`             UInt8,
   `pickup`                FixedString(25),
   `dropoff`               FixedString(25),
   `cab_type`              Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
   `pickup_nyct2010_gid`   Int8,
   `pickup_ctlabel`        Float32,
   `pickup_borocode`       Int8,
   `pickup_ct2010`         String,
   `pickup_boroct2010`     FixedString(7),
   `pickup_cdeligibil`     String,
   `pickup_ntacode`        FixedString(4),
   `pickup_ntaname`        String,
   `pickup_puma`           UInt16,
   `dropoff_nyct2010_gid`  UInt8,
   `dropoff_ctlabel`       Float32,
   `dropoff_borocode`      UInt8,
   `dropoff_ct2010`        String,
   `dropoff_boroct2010`    FixedString(7),
   `dropoff_cdeligibil`    String,
   `dropoff_ntacode`       FixedString(4),
   `dropoff_ntaname`       String,
   `dropoff_puma`          UInt16
) ENGINE = S3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_{0..9}.gz', 'TabSeparatedWithNames', 'gzip');

请注意，使用了 {0..9} 模式来限制为前十个文件。创建后，我们可以像查询任何其他表一样查询此表

SELECT DISTINCT(pickup_ntaname)
FROM trips_raw
LIMIT 10;

┌─pickup_ntaname───────────────────────────────────┐
│ Lenox Hill-Roosevelt Island                      │
│ Airport                                          │
│ SoHo-TriBeCa-Civic Center-Little Italy           │
│ West Village                                     │
│ Chinatown                                        │
│ Hudson Yards-Chelsea-Flatiron-Union Square       │
│ Turtle Bay-East Midtown                          │
│ Upper West Side                                  │
│ Murray Hill-Kips Bay                             │
│ DUMBO-Vinegar Hill-Downtown Brooklyn-Boerum Hill │
└──────────────────────────────────────────────────┘

插入数据

S3 表引擎支持并行读取。仅当表定义不包含 glob 模式时才支持写入。因此，上面的表会阻止写入。

为了演示写入，创建一个指向可写入 S3 bucket 的表

CREATE TABLE trips_dest
(
   `trip_id`               UInt32,
   `pickup_date`           Date,
   `pickup_datetime`       DateTime,
   `dropoff_datetime`      DateTime,
   `tip_amount`            Float32,
   `total_amount`          Float32
) ENGINE = S3('<bucket path>/trips.bin', 'Native');

INSERT INTO trips_dest
   SELECT
      trip_id,
      pickup_date,
      pickup_datetime,
      dropoff_datetime,
      tip_amount,
      total_amount
   FROM trips
   LIMIT 10;

SELECT * FROM trips_dest LIMIT 5;

┌────trip_id─┬─pickup_date─┬─────pickup_datetime─┬────dropoff_datetime─┬─tip_amount─┬─total_amount─┐
│ 1200018648 │  2015-07-01 │ 2015-07-01 00:00:16 │ 2015-07-01 00:02:57 │          0 │          7.3 │
│ 1201452450 │  2015-07-01 │ 2015-07-01 00:00:20 │ 2015-07-01 00:11:07 │       1.96 │        11.76 │
│ 1202368372 │  2015-07-01 │ 2015-07-01 00:00:40 │ 2015-07-01 00:05:46 │          0 │          7.3 │
│ 1200831168 │  2015-07-01 │ 2015-07-01 00:01:06 │ 2015-07-01 00:09:23 │          2 │         12.3 │
│ 1201362116 │  2015-07-01 │ 2015-07-01 00:01:07 │ 2015-07-01 00:03:31 │          0 │          5.3 │
└────────────┴─────────────┴─────────────────────┴─────────────────────┴────────────┴──────────────┘

请注意，只能将行插入到新文件中。没有合并周期或文件拆分操作。写入文件后，后续插入将失败。用户这里有两个选择

• 指定设置 s3_create_new_file_on_insert=1。这将导致每次插入时创建新文件。一个数字后缀将被附加到每个文件的末尾，该后缀将为每次插入操作单调递增。对于上面的示例，后续插入将导致创建 trips_1.bin 文件。
• 指定设置 s3_truncate_on_insert=1。这将导致文件被截断，即在完成时仅包含新插入的行。

这两个设置的默认值为 0 - 从而强制用户设置其中一个。如果同时设置了两者，则 s3_truncate_on_insert 将优先。

关于 S3 表引擎的一些说明

• 与传统的 MergeTree 系列表不同，删除 S3 表不会删除底层数据。
• 此表类型的完整设置可以在 此处 找到。
• 在使用此引擎时，请注意以下注意事项
  • 不支持 ALTER 查询
  • 不支持 SAMPLE 操作
  • 没有索引的概念，即主索引或跳过索引。

管理凭据

在前面的示例中，我们已经在 s3 函数或 S3 表定义中传递了凭据。虽然这对于偶尔使用可能是可以接受的，但用户在生产环境中需要更明确的身份验证机制。为了解决这个问题，ClickHouse 提供了几种选项

• 在 config.xml 或 conf.d 下的等效配置文件中指定连接详细信息。下面显示了一个示例文件的内容，假设使用 debian 包进行安装。

  ubuntu@single-node-clickhouse:/etc/clickhouse-server/config.d$ cat s3.xml
  <clickhouse>
      <s3>
          <endpoint-name>
              <endpoint>https://dalem-files.s3.amazonaws.com/test/</endpoint>
              <access_key_id>key</access_key_id>
              <secret_access_key>secret</secret_access_key>
              <!-- <use_environment_credentials>false</use_environment_credentials> -->
              <!-- <header>Authorization: Bearer SOME-TOKEN</header> -->
          </endpoint-name>
      </s3>
  </clickhouse>
  

  这些凭据将用于任何请求，其中上述端点是请求 URL 的精确前缀匹配。此外，请注意此示例中声明授权标头的能力，作为访问和密钥的替代方案。可以在 此处 找到支持的设置的完整列表。

• 上面的示例强调了配置参数 use_environment_credentials 的可用性。此配置参数也可以在 s3 级别全局设置

  <clickhouse>
      <s3>
      <use_environment_credentials>true</use_environment_credentials>
      </s3>
  </clickhouse>
  

  此设置会尝试从环境中检索 S3 凭据，从而允许通过 IAM 角色进行访问。具体来说，执行以下检索顺序

  • 查找环境变量 AWS_ACCESS_KEY_ID、AWS_SECRET_ACCESS_KEY 和 AWS_SESSION_TOKEN
  • 在 $HOME/.aws 中执行检查
  • 通过 AWS 安全令牌服务获取临时凭据 - 即通过 AssumeRole API
  • 检查 ECS 环境变量 AWS_CONTAINER_CREDENTIALS_RELATIVE_URI 或 AWS_CONTAINER_CREDENTIALS_FULL_URI 和 AWS_ECS_CONTAINER_AUTHORIZATION_TOKEN 中的凭据。
  • 通过 Amazon EC2 实例元数据 获取凭据，前提是 AWS_EC2_METADATA_DISABLED 未设置为 true。
  • 可以使用相同的匹配规则，为特定端点设置相同的设置。

优化性能

有关如何使用 S3 函数优化读取和插入，请参阅 专用性能指南。

S3 存储调优

内部，ClickHouse merge tree 使用两种主要的存储格式：Wide 和 Compact。虽然当前的实现使用 ClickHouse 的默认行为（通过设置 min_bytes_for_wide_part 和 min_rows_for_wide_part 控制），但我们预计未来版本中 S3 的行为会发生变化，例如，更大的 min_bytes_for_wide_part 默认值，鼓励更 Compact 的格式，从而减少文件数量。现在，在使用 S3 独占存储时，你可能希望调整这些设置。

S3 后端 MergeTree

s3 函数和相关的表引擎允许我们使用熟悉的 ClickHouse 语法查询 S3 中的数据。但是，就数据管理功能和性能而言，它们是有限的。不支持主索引，不支持缓存，并且需要用户管理文件插入。

ClickHouse 认识到 S3 代表了一种有吸引力的存储解决方案，尤其是在对“较冷”数据查询性能要求不那么严格，并且用户希望分离存储和计算的情况下。为了帮助实现这一点，提供了支持将 S3 用作 MergeTree 引擎的存储。这将使你能够利用 S3 的可扩展性和成本优势，以及 MergeTree 引擎的插入和查询性能。

存储层级

ClickHouse 存储卷允许将物理磁盘抽象化为 MergeTree 表引擎。任何单个卷都可以由有序的磁盘集组成。虽然主要允许使用多个块设备进行数据存储，但这种抽象也允许其他存储类型，包括 S3。ClickHouse 数据部分可以根据存储策略在卷之间移动和填充率，从而创建存储层级概念。

存储层级解锁了热冷架构，其中最新数据（通常也是查询最多的数据）只需要在高性能存储上占用少量空间，例如 NVMe SSD。随着数据的老化，查询时间的 SLA 增加，查询频率也增加。这种长尾数据可以存储在较慢、性能较低的存储上，例如 HDD 或对象存储，例如 S3。

创建磁盘

要将 S3 bucket 用作磁盘，我们必须首先在 ClickHouse 配置文件中声明它。可以扩展 config.xml，或者最好在 conf.d 下提供一个新文件。下面显示了一个 S3 磁盘声明的示例

<clickhouse>
    <storage_configuration>
        ...
        <disks>
            <s3>
                <type>s3</type>
                <endpoint>https://sample-bucket.s3.us-east-2.amazonaws.com/tables/</endpoint>
                <access_key_id>your_access_key_id</access_key_id>
                <secret_access_key>your_secret_access_key</secret_access_key>
                <region></region>
                <metadata_path>/var/lib/clickhouse/disks/s3/</metadata_path>
            </s3>
            <s3_cache>
                <type>cache</type>
                <disk>s3</disk>
                <path>/var/lib/clickhouse/disks/s3_cache/</path>
                <max_size>10Gi</max_size>
            </s3_cache>
        </disks>
        ...
    </storage_configuration>
</clickhouse>

可以在 此处 找到与此磁盘声明相关的完整设置列表。请注意，可以使用与 管理凭据 中描述的相同方法管理凭据，即，可以将 use_environment_credentials 设置为 true，以在上述设置块中使用 IAM 角色。

创建存储策略

配置完成后，此“磁盘”可以由策略中声明的存储卷使用。对于下面的示例，我们假设 s3 是我们唯一的存储。这忽略了更复杂的冷热架构，其中数据可以根据 TTL 和填充率进行重新定位。

<clickhouse>
    <storage_configuration>
        <disks>
            <s3>
            ...
            </s3>
            <s3_cache>
            ...
            </s3_cache>
        </disks>
        <policies>
            <s3_main>
                <volumes>
                    <main>
                        <disk>s3</disk>
                    </main>
                </volumes>
            </s3_main>
        </policies>
    </storage_configuration>
</clickhouse>

创建表

假设你已配置磁盘以使用具有写入访问权限的 bucket，你应该能够创建如下表。为了简洁起见，我们使用 NYC 出租车列的子集，并将数据直接流式传输到 S3 后端表

CREATE TABLE trips_s3
(
   `trip_id` UInt32,
   `pickup_date` Date,
   `pickup_datetime` DateTime,
   `dropoff_datetime` DateTime,
   `pickup_longitude` Float64,
   `pickup_latitude` Float64,
   `dropoff_longitude` Float64,
   `dropoff_latitude` Float64,
   `passenger_count` UInt8,
   `trip_distance` Float64,
   `tip_amount` Float32,
   `total_amount` Float32,
   `payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(pickup_date)
ORDER BY pickup_datetime
SETTINGS storage_policy='s3_main'

INSERT INTO trips_s3 SELECT trip_id, pickup_date, pickup_datetime, dropoff_datetime, pickup_longitude, pickup_latitude, dropoff_longitude, dropoff_latitude, passenger_count, trip_distance, tip_amount, total_amount, payment_type FROM s3('https://ch-nyc-taxi.s3.eu-west-3.amazonaws.com/tsv/trips_{0..9}.tsv.gz', 'TabSeparatedWithNames') LIMIT 1000000;

根据硬件，后者的 1m 行插入可能需要几分钟才能执行。你可以通过 system.processes 表确认进度。随意将行数调整到 10m 的限制，并探索一些示例查询。

SELECT passenger_count, avg(tip_amount) AS avg_tip, avg(total_amount) AS avg_amount FROM trips_s3 GROUP BY passenger_count;

修改表

偶尔您可能需要修改特定表的存储策略。虽然这是可行的，但存在一些限制。新的目标策略必须包含先前策略的所有磁盘和卷，即，数据不会被迁移以满足策略更改。在验证这些约束时，卷和磁盘将通过其名称来识别，任何违反尝试都将导致错误。但是，假设您使用前面的示例，以下更改是有效的。

<policies>
   <s3_main>
       <volumes>
           <main>
               <disk>s3</disk>
           </main>
       </volumes>
   </s3_main>
   <s3_tiered>
       <volumes>
           <hot>
               <disk>default</disk>
           </hot>
           <main>
               <disk>s3</disk>
           </main>
       </volumes>
       <move_factor>0.2</move_factor>
   </s3_tiered>
</policies>

ALTER TABLE trips_s3 MODIFY SETTING storage_policy='s3_tiered'

在这里，我们在新的 s3_tiered 策略中重用了主卷，并引入了一个新的热卷。这使用了默认磁盘，该磁盘仅由通过参数 <path> 配置的一个磁盘组成。请注意，我们的卷名和磁盘没有更改。对我们表的新插入将驻留在默认磁盘上，直到达到 move_factor * disk_size - 此时数据将被重定位到 S3。

处理复制

可以使用 ReplicatedMergeTree 表引擎来完成与 S3 磁盘的复制。有关详细信息，请参阅 使用 S3 对象存储在两个 AWS 区域之间复制单个分片 指南。

读 & 写

以下说明涵盖了 ClickHouse 与 S3 交互的实现。虽然通常仅供参考，但它可能有助于读者在 优化性能 时。

• 默认情况下，查询处理管道的任何阶段使用的最大查询处理线程数等于核心数。某些阶段比其他阶段更易于并行化，因此此值提供了一个上限。由于数据是从磁盘流式传输的，因此多个查询阶段可以同时执行。因此，用于查询的实际线程数可能会超过此值。可以通过设置 max_threads 进行修改。
• 默认情况下，S3 上的读取是异步的。此行为由设置 remote_filesystem_read_method 确定，默认设置为 threadpool 值。在服务请求时，ClickHouse 以条带形式读取颗粒。这些条带中的每一个都可能包含许多列。一个线程将逐个读取其颗粒的列。与其同步地执行此操作，而是先为所有列进行预取，然后再等待数据。与在每列上同步等待相比，这提供了显著的性能提升。在大多数情况下，您不需要更改此设置 - 请参阅 优化性能。
• 写入是并行执行的，最多有 100 个并发文件写入线程。max_insert_delayed_streams_for_parallel_write，其默认值为 1000，控制并行写入的 S3 blob 的数量。由于每个正在写入的文件都需要一个缓冲区 (~1MB)，因此这有效地限制了 INSERT 的内存消耗。在服务器内存较低的情况下，适当降低此值。

将 S3 对象存储用作 ClickHouse 磁盘

如果您需要有关创建存储桶和 IAM 角色的分步说明，请参阅 “如何创建 AWS IAM 用户和 S3 存储桶”

配置 ClickHouse 将 S3 存储桶用作磁盘

以下示例基于安装为服务且具有默认 ClickHouse 目录的 Linux Deb 包。

1. 在 ClickHouse 的 config.d 目录中创建一个新文件来存储存储配置。

vim /etc/clickhouse-server/config.d/storage_config.xml

2. 添加以下内容进行存储配置；替换来自先前步骤的存储桶路径、访问密钥和密钥

<clickhouse>
  <storage_configuration>
    <disks>
      <s3_disk>
        <type>s3</type>
        <endpoint>https://mars-doc-test.s3.amazonaws.com/clickhouse3/</endpoint>
        <access_key_id>ABC123</access_key_id>
        <secret_access_key>Abc+123</secret_access_key>
        <metadata_path>/var/lib/clickhouse/disks/s3_disk/</metadata_path>
      </s3_disk>
      <s3_cache>
        <type>cache</type>
        <disk>s3_disk</disk>
        <path>/var/lib/clickhouse/disks/s3_cache/</path>
        <max_size>10Gi</max_size>
      </s3_cache>
    </disks>
    <policies>
      <s3_main>
        <volumes>
          <main>
            <disk>s3_disk</disk>
          </main>
        </volumes>
      </s3_main>
    </policies>
  </storage_configuration>
</clickhouse>

注意

<disks> 标签内的 s3_disk 和 s3_cache 标签是任意标签。可以将它们设置为其他内容，但相同的标签必须在 <policies> 标签下的 <disk> 选项卡中使用，以引用磁盘。<S3_main> 标签也是任意的，是将在 ClickHouse 中创建资源时用作标识存储目标的策略名称。

上述配置适用于 ClickHouse 22.8 或更高版本，如果您使用的是旧版本，请参阅 存储数据 文档。

有关使用 S3 的更多信息：集成指南：基于 S3 的 MergeTree

3. 更新文件的所有者为 clickhouse 用户和组

chown clickhouse:clickhouse /etc/clickhouse-server/config.d/storage_config.xml

4. 重新启动 ClickHouse 实例以使更改生效。

service clickhouse-server restart

测试

1. 使用 ClickHouse 客户端登录，如下所示

clickhouse-client --user default --password ClickHouse123!

2. 创建一个指定新的 S3 存储策略的表

CREATE TABLE s3_table1
           (
               `id` UInt64,
               `column1` String
           )
           ENGINE = MergeTree
           ORDER BY id
           SETTINGS storage_policy = 's3_main';

3. 显示该表是否使用正确的策略创建

SHOW CREATE TABLE s3_table1;

┌─statement────────────────────────────────────────────────────
│ CREATE TABLE default.s3_table1
(
    `id` UInt64,
    `column1` String
)
ENGINE = MergeTree
ORDER BY id
SETTINGS storage_policy = 's3_main', index_granularity = 8192
└──────────────────────────────────────────────────────────────

4. 将测试行插入表中

INSERT INTO s3_table1
           (id, column1)
           VALUES
           (1, 'abc'),
           (2, 'xyz');

INSERT INTO s3_table1 (id, column1) FORMAT Values

Query id: 0265dd92-3890-4d56-9d12-71d4038b85d5

Ok.

2 rows in set. Elapsed: 0.337 sec.

5. 查看这些行

SELECT * FROM s3_table1;

┌─id─┬─column1─┐
│  1 │ abc     │
│  2 │ xyz     │
└────┴─────────┘

2 rows in set. Elapsed: 0.284 sec.

6. 在 AWS 控制台中，导航到存储桶，然后选择新的存储桶和文件夹。您应该看到如下内容

使用 S3 对象存储在两个 AWS 区域之间复制单个分片

提示

默认情况下，ClickHouse Cloud 使用对象存储，如果您正在运行 ClickHouse Cloud，则无需遵循此过程。

计划部署

本教程基于在 AWS EC2 中部署两个 ClickHouse Server 节点和三个 ClickHouse Keeper 节点。ClickHouse 服务器的数据存储是 S3。为了支持灾难恢复，使用了两个 AWS 区域，每个区域都有一个 ClickHouse Server 和一个 S3 存储桶。

ClickHouse 表在两个服务器之间复制，因此在两个区域之间复制。

安装软件

ClickHouse 服务器节点

在执行 ClickHouse 服务器节点上的部署步骤时，请参阅 安装说明。

部署 ClickHouse

在两个主机上部署 ClickHouse，在示例配置中这些主机命名为 chnode1、chnode2。

将 chnode1 放在一个 AWS 区域，将 chnode2 放在第二个区域。

部署 ClickHouse Keeper

在三个主机上部署 ClickHouse Keeper，在示例配置中这些主机命名为 keepernode1、keepernode2 和 keepernode3。keepernode1 可以部署在与 chnode1 相同的区域，keepernode2 与 chnode2 部署在一起，而 keepernode3 可以部署在任何区域，但与该区域中 ClickHouse 节点不同的可用区。

在执行 ClickHouse Keeper 节点上的部署步骤时，请参阅 安装说明。

创建 S3 存储桶

创建两个 S3 存储桶，一个放在您放置 chnode1 和 chnode2 的每个区域。

如果您需要有关创建存储桶和 IAM 角色的分步说明，请展开 创建 S3 存储桶和 IAM 角色 并按照说明进行操作

创建 S3 存储桶和 IAM 用户

本文演示了如何配置 AWS IAM 用户、创建 S3 存储桶以及配置 ClickHouse 将存储桶用作 S3 磁盘的基本知识。您应该与您的安全团队合作以确定要使用的权限，并将其视为起点。

创建 AWS IAM 用户

在以下步骤中，您将创建一个服务帐户用户（而不是登录用户）。

1. 登录到 AWS IAM 管理控制台。

2. 在 Users 菜单中，选择 Create user

3. 输入用户名，并将凭证类型设置为 Access key - Programmatic access，然后选择 Next: Permissions

4. 不要将用户添加到任何组；选择 Next: Tags

5. 除非您需要添加任何标签，否则选择 Next: Review

6. 选择 Create User

注意

可以忽略警告消息，该消息表明用户没有权限；权限将在下一部分中为该用户授予存储桶。

7. 现在已创建用户；单击 show 并复制访问密钥和密钥。

注意

将密钥保存在其他地方；这是仅提供机密访问密钥的时间。

8. 单击关闭，然后在用户屏幕中找到该用户。

9. 复制 ARN（Amazon Resource Name）并保存它以用于配置存储桶的访问策略。

创建 S3 存储桶

1. 在 S3 存储桶部分，选择 Create bucket

2. 输入存储桶名称，让其他选项保持默认设置

注意

存储桶名称必须在整个 AWS 中（而不仅仅是组织中）是唯一的，否则将发出错误。

3. 让 Block all Public Access 启用；不需要公共访问权限。

4. 选择页面底部的 Create Bucket

5. 选择该链接，复制 ARN，并保存它以用于配置存储桶的访问策略。

6. 创建存储桶后，在 S3 存储桶列表中找到新的 S3 存储桶并选择该链接

7. 选择 Create folder

8. 输入将是 ClickHouse S3 磁盘目标的文件夹名称，然后选择 Create folder

9. 该文件夹现在应显示在存储桶列表中

10. 选中新文件夹的复选框，然后单击 Copy URL。保存复制的 URL 以在下一部分的 ClickHouse 存储配置中使用。

11. 选择 Permissions 选项卡，然后单击 Bucket Policy 部分中的 Edit 按钮

12. 添加存储桶策略，如下例所示

{
  "Version" : "2012-10-17",
  "Id" : "Policy123456",
  "Statement" : [
    {
      "Sid" : "abc123",
      "Effect" : "Allow",
      "Principal" : {
        "AWS" : "arn:aws:iam::921234567898:user/mars-s3-user"
      },
      "Action" : "s3:*",
      "Resource" : [
        "arn:aws:s3:::mars-doc-test",
        "arn:aws:s3:::mars-doc-test/*"
      ]
    }
  ]
}

|Parameter | Description | Example Value |
|----------|-------------|----------------|
|Version | Version of the policy interpreter, leave as-is | 2012-10-17 |
|Sid | User-defined policy id | abc123 |
|Effect | Whether user requests will be allowed or denied | Allow |
|Principal | The accounts or user that will be allowed | arn:aws:iam::921234567898:user/mars-s3-user |
|Action | What operations are allowed on the bucket| s3:*|
|Resource | Which resources in the bucket will operations be allowed in | "arn:aws:s3:::mars-doc-test", "arn:aws:s3:::mars-doc-test/*" |

注意

您应该与您的安全团队合作以确定要使用的权限，将这些视为起点。有关策略和设置的更多信息，请参阅 AWS 文档：https://docs.aws.amazon.com/AmazonS3/latest/userguide/access-policy-language-overview.html

13. 保存策略配置。

然后，将配置文件放在 /etc/clickhouse-server/config.d/ 中。以下是一个存储桶的示例配置文件，另一个类似，突出的三行不同

<clickhouse>
  <storage_configuration>
     <disks>
        <s3_disk>
           <type>s3</type>
        <!--highlight-start-->
           <endpoint>https://docs-clickhouse-s3.s3.us-east-2.amazonaws.com/clickhouses3/</endpoint>
           <access_key_id>ABCDEFGHIJKLMNOPQRST</access_key_id>
           <secret_access_key>Tjdm4kf5snfkj303nfljnev79wkjn2l3knr81007</secret_access_key>
        <!--highlight-end-->
           <metadata_path>/var/lib/clickhouse/disks/s3_disk/</metadata_path>
        </s3_disk>

        <s3_cache>
           <type>cache</type>
           <disk>s3_disk</disk>
           <path>/var/lib/clickhouse/disks/s3_cache/</path>
           <max_size>10Gi</max_size>
        </s3_cache>
     </disks>
        <policies>
            <s3_main>
                <volumes>
                    <main>
                        <disk>s3_disk</disk>
                    </main>
                </volumes>
            </s3_main>
    </policies>
   </storage_configuration>
</clickhouse>

注意

许多本指南中的步骤会要求您将配置文件放置在 /etc/clickhouse-server/config.d/ 中。这是 Linux 系统中配置覆盖文件的默认位置。当您将这些文件放入该目录时，ClickHouse 将使用内容来覆盖默认配置。通过将这些文件放置在覆盖目录中，您将避免在升级期间丢失配置。

配置 ClickHouse Keeper

当 ClickHouse Keeper 单独运行（与 ClickHouse 服务器分离）时，配置是一个单独的 XML 文件。在本教程中，该文件是 /etc/clickhouse-keeper/keeper_config.xml。所有三个 Keeper 服务器使用相同的配置，只有一个设置不同；<server_id>。

server_id 指示要分配给使用配置文件的主机的 ID。在下面的示例中，server_id 是 3，如果您向下查看文件中的 <raft_configuration> 部分，您会看到服务器 3 的主机名为 keepernode3。这就是 ClickHouse Keeper 进程知道在选择领导者和其他活动时连接到哪些其他服务器的方式。

<clickhouse>
    <logger>
        <level>trace</level>
        <log>/var/log/clickhouse-keeper/clickhouse-keeper.log</log>
        <errorlog>/var/log/clickhouse-keeper/clickhouse-keeper.err.log</errorlog>
        <size>1000M</size>
        <count>3</count>
    </logger>
    <listen_host>0.0.0.0</listen_host>
    <keeper_server>
        <tcp_port>9181</tcp_port>
<!--highlight-next-line-->
        <server_id>3</server_id>
        <log_storage_path>/var/lib/clickhouse/coordination/log</log_storage_path>
        <snapshot_storage_path>/var/lib/clickhouse/coordination/snapshots</snapshot_storage_path>

        <coordination_settings>
            <operation_timeout_ms>10000</operation_timeout_ms>
            <session_timeout_ms>30000</session_timeout_ms>
            <raft_logs_level>warning</raft_logs_level>
        </coordination_settings>

        <raft_configuration>
            <server>
                <id>1</id>
                <hostname>keepernode1</hostname>
                <port>9234</port>
            </server>
            <server>
                <id>2</id>
                <hostname>keepernode2</hostname>
                <port>9234</port>
            </server>
<!--highlight-start-->
            <server>
                <id>3</id>
                <hostname>keepernode3</hostname>
                <port>9234</port>
            </server>
<!--highlight-end-->
        </raft_configuration>
    </keeper_server>
</clickhouse>

将 ClickHouse Keeper 的配置文件复制到位（记住设置 <server_id>）

sudo -u clickhouse \
  cp keeper.xml /etc/clickhouse-keeper/keeper.xml

配置 ClickHouse 服务器

定义集群

ClickHouse 集群定义在配置的 <remote_servers> 部分中。在本示例中，定义了一个集群 cluster_1S_2R，它由一个分片和两个副本组成。副本位于主机 chnode1 和 chnode2 上。

<clickhouse>
    <remote_servers replace="true">
        <cluster_1S_2R>
            <shard>
                <replica>
                    <host>chnode1</host>
                    <port>9000</port>
                </replica>
                <replica>
                    <host>chnode2</host>
                    <port>9000</port>
                </replica>
            </shard>
        </cluster_1S_2R>
    </remote_servers>
</clickhouse>

在使用集群时，定义宏来填充 DDL 查询中的集群、分片和副本设置非常有用。此示例允许您指定使用复制的表引擎，而无需提供 shard 和 replica 详细信息。当您创建表时，可以通过查询 system.tables 来查看如何使用 shard 和 replica 宏。

<clickhouse>
    <distributed_ddl>
            <path>/clickhouse/task_queue/ddl</path>
    </distributed_ddl>
    <macros>
        <cluster>cluster_1S_2R</cluster>
        <shard>1</shard>
        <replica>replica_1</replica>
    </macros>
</clickhouse>

注意

上面的宏是针对 chnode1 的，在 chnode2 上将 replica 设置为 replica_2。

禁用零拷贝复制

在 ClickHouse 22.7 及更低版本中，对于 S3 和 HDFS 磁盘，默认情况下将设置 allow_remote_fs_zero_copy_replication 设置为 true。对于此灾难恢复场景，应将此设置设置为 false，并且在版本 22.8 及更高版本中，默认情况下将其设置为 false。

出于两个原因，此设置应为 false：1) 此功能尚未准备好投入生产；2) 在灾难恢复场景中，数据和元数据都需要存储在多个区域中。将 allow_remote_fs_zero_copy_replication 设置为 false。

<clickhouse>
   <merge_tree>
        <allow_remote_fs_zero_copy_replication>false</allow_remote_fs_zero_copy_replication>
   </merge_tree>
</clickhouse>

ClickHouse Keeper 负责协调 ClickHouse 节点之间的数据复制。为了告知 ClickHouse ClickHouse Keeper 节点，请将配置文件添加到每个 ClickHouse 节点。

<clickhouse>
    <zookeeper>
        <node index="1">
            <host>keepernode1</host>
            <port>9181</port>
        </node>
        <node index="2">
            <host>keepernode2</host>
            <port>9181</port>
        </node>
        <node index="3">
            <host>keepernode3</host>
            <port>9181</port>
        </node>
    </zookeeper>
</clickhouse>

配置网络

在 AWS 中配置安全设置时，请参阅 网络端口列表，以便您的服务器可以相互通信，并且您可以与它们通信。

所有三个服务器都必须侦听网络连接，以便它们可以在服务器之间以及与 S3 之间进行通信。默认情况下，ClickHouse 仅侦听环回地址，因此必须更改此设置。这在 /etc/clickhouse-server/config.d/ 中配置。这是一个示例，它配置 ClickHouse 和 ClickHouse Keeper 以侦听所有 IPv4 接口。有关更多信息，请参阅文档或默认配置文件 /etc/clickhouse/config.xml。

<clickhouse>
    <listen_host>0.0.0.0</listen_host>
</clickhouse>

启动服务器

运行 ClickHouse Keeper

在每个 Keeper 服务器上运行适用于您操作系统的命令，例如

sudo systemctl enable clickhouse-keeper
sudo systemctl start clickhouse-keeper
sudo systemctl status clickhouse-keeper

检查 ClickHouse Keeper 状态

使用 netcat 向 ClickHouse Keeper 发送命令。例如，mntr 返回 ClickHouse Keeper 集群的状态。如果您在每个 Keeper 节点上运行该命令，您将看到一个节点是领导者，另外两个节点是跟随者

echo mntr | nc localhost 9181

zk_version      v22.7.2.15-stable-f843089624e8dd3ff7927b8a125cf3a7a769c069
zk_avg_latency  0
zk_max_latency  11
zk_min_latency  0
zk_packets_received     1783
zk_packets_sent 1783
# highlight-start
zk_num_alive_connections        2
zk_outstanding_requests 0
zk_server_state leader
# highlight-end
zk_znode_count  135
zk_watch_count  8
zk_ephemerals_count     3
zk_approximate_data_size        42533
zk_key_arena_size       28672
zk_latest_snapshot_size 0
zk_open_file_descriptor_count   182
zk_max_file_descriptor_count    18446744073709551615
# highlight-start
zk_followers    2
zk_synced_followers     2
# highlight-end

运行 ClickHouse 服务器

在每个 ClickHouse 服务器上运行

sudo service clickhouse-server start

验证 ClickHouse 服务器

当您添加 集群配置 时，定义了一个跨两个 ClickHouse 节点复制的单个分片。在此验证步骤中，您将检查 ClickHouse 启动时是否构建了集群，并使用该集群创建一个复制的表。

• 验证集群是否存在

  show clusters
  

  ┌─cluster───────┐
  │ cluster_1S_2R │
  └───────────────┘
  
  1 row in set. Elapsed: 0.009 sec. `
  

• 使用 ReplicatedMergeTree 表引擎在集群中创建一个表

  create table trips on cluster 'cluster_1S_2R' (
   `trip_id` UInt32,
   `pickup_date` Date,
   `pickup_datetime` DateTime,
   `dropoff_datetime` DateTime,
   `pickup_longitude` Float64,
   `pickup_latitude` Float64,
   `dropoff_longitude` Float64,
   `dropoff_latitude` Float64,
   `passenger_count` UInt8,
   `trip_distance` Float64,
   `tip_amount` Float32,
   `total_amount` Float32,
   `payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4))
  ENGINE = ReplicatedMergeTree
  PARTITION BY toYYYYMM(pickup_date)
  ORDER BY pickup_datetime
  SETTINGS storage_policy='s3_main'
  

  ┌─host────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
  │ chnode1 │ 9000 │      0 │       │                   1 │                0 │
  │ chnode2 │ 9000 │      0 │       │                   0 │                0 │
  └─────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
  

• 了解前面定义的宏的用途

  shard 和 replica 宏 前面定义，在下面的突出显示行中，您可以查看每个 ClickHouse 节点上替换值的具体位置。此外，还使用了值 uuid；uuid 未在宏中定义，因为它是由系统生成的。

  SELECT create_table_query
  FROM system.tables
  WHERE name = 'trips'
  FORMAT Vertical
  

  Query id: 4d326b66-0402-4c14-9c2f-212bedd282c0
  
  Row 1:
  ──────
  create_table_query: CREATE TABLE default.trips (`trip_id` UInt32, `pickup_date` Date, `pickup_datetime` DateTime, `dropoff_datetime` DateTime, `pickup_longitude` Float64, `pickup_latitude` Float64, `dropoff_longitude` Float64, `dropoff_latitude` Float64, `passenger_count` UInt8, `trip_distance` Float64, `tip_amount` Float32, `total_amount` Float32, `payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4))
  # highlight-next-line
  ENGINE = ReplicatedMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}')
  PARTITION BY toYYYYMM(pickup_date) ORDER BY pickup_datetime SETTINGS storage_policy = 's3_main'
  
  1 row in set. Elapsed: 0.012 sec.
  
  注意

  您可以通过设置 default_replica_path 和 default_replica_name 来自定义 zookeeper 路径 'clickhouse/tables/{uuid}/{shard}。文档 在此。

测试

这些测试将验证数据是否正在两个服务器之间复制，以及它是否存储在 S3 Bucket 中，而不是本地磁盘上。

• 从纽约市出租车数据集添加数据

  INSERT INTO trips
  SELECT trip_id,
         pickup_date,
         pickup_datetime,
         dropoff_datetime,
         pickup_longitude,
         pickup_latitude,
         dropoff_longitude,
         dropoff_latitude,
         passenger_count,
         trip_distance,
         tip_amount,
         total_amount,
         payment_type
     FROM s3('https://ch-nyc-taxi.s3.eu-west-3.amazonaws.com/tsv/trips_{0..9}.tsv.gz', 'TabSeparatedWithNames') LIMIT 1000000;
  

• 验证数据是否存储在 S3 中。

  此查询显示磁盘上的数据大小以及用于确定使用哪个磁盘的策略。

  SELECT
      engine,
      data_paths,
      metadata_path,
      storage_policy,
      formatReadableSize(total_bytes)
  FROM system.tables
  WHERE name = 'trips'
  FORMAT Vertical
  

  Query id: af7a3d1b-7730-49e0-9314-cc51c4cf053c
  
  Row 1:
  ──────
  engine:                          ReplicatedMergeTree
  data_paths:                      ['/var/lib/clickhouse/disks/s3_disk/store/551/551a859d-ec2d-4512-9554-3a4e60782853/']
  metadata_path:                   /var/lib/clickhouse/store/e18/e18d3538-4c43-43d9-b083-4d8e0f390cf7/trips.sql
  storage_policy:                  s3_main
  formatReadableSize(total_bytes): 36.42 MiB
  
  1 row in set. Elapsed: 0.009 sec.
  

  检查本地磁盘上的数据大小。从上面来看，存储的数百万行数据在磁盘上的大小为 36.42 MiB。这应该在 S3 上，而不是本地磁盘上。上面的查询还告诉我们数据和元数据存储在本地磁盘上的位置。检查本地数据

  root@chnode1:~# du -sh /var/lib/clickhouse/disks/s3_disk/store/551
  536K  /var/lib/clickhouse/disks/s3_disk/store/551
  

  检查每个 S3 Bucket 中的 S3 数据（总数未显示，但在插入后两个 Bucket 中都存储了大约 36 MiB）

S3Express

S3Express 是亚马逊 S3 中一种新的高性能、单个可用区存储类。

您可以参考此 博客，了解我们使用 S3Express 测试 ClickHouse 的经验。

注意

S3Express 将数据存储在单个 AZ 中。这意味着在 AZ 发生故障时，数据将不可用。

S3 磁盘

创建使用 S3Express Bucket 支持的存储的表涉及以下步骤

1. 创建一个 Directory 类型的 Bucket
2. 安装适当的 Bucket 策略，以授予 S3 用户所有必需的权限（例如，简单地允许无限制访问 "Action": "s3express:*"）
3. 配置存储策略时，请提供 region 参数

存储配置与普通 S3 相同，例如可能如下所示

<storage_configuration>
    <disks>
        <s3_express>
            <type>s3</type>
            <endpoint>https://my-test-bucket--eun1-az1--x-s3.s3express-eun1-az1.eu-north-1.amazonaws.com/store/</endpoint>
            <region>eu-north-1</region>
            <access_key_id>...</access_key_id>
            <secret_access_key>...</secret_access_key>
        </s3_express>
    </disks>
    <policies>
        <s3_express>
            <volumes>
                <main>
                    <disk>s3_express</disk>
                </main>
            </volumes>
        </s3_express>
    </policies>
</storage_configuration>

然后，在新存储上创建一个表

CREATE TABLE t
(
    a UInt64,
    s String
)
ENGINE = MergeTree
ORDER BY a
SETTINGS storage_policy = 's3_express';

S3 存储

S3 存储也受支持，但仅适用于 Object URL 路径。示例

SELECT * FROM s3('https://test-bucket--eun1-az1--x-s3.s3express-eun1-az1.eu-north-1.amazonaws.com/file.csv', ...)

它还需要在配置中指定 Bucket 区域

<s3>
    <perf-bucket-url>
        <endpoint>https://test-bucket--eun1-az1--x-s3.s3express-eun1-az1.eu-north-1.amazonaws.com</endpoint>
        <region>eu-north-1</region>
    </perf-bucket-url>
</s3>

备份

可以将备份存储在上面创建的磁盘上

BACKUP TABLE t TO Disk('s3_express', 't.zip')

┌─id───────────────────────────────────┬─status─────────┐
│ c61f65ac-0d76-4390-8317-504a30ba7595 │ BACKUP_CREATED │
└──────────────────────────────────────┴────────────────┘

RESTORE TABLE t AS t_restored FROM Disk('s3_express', 't.zip')

┌─id───────────────────────────────────┬─status───┐
│ 4870e829-8d76-4171-ae59-cffaf58dea04 │ RESTORED │
└──────────────────────────────────────┴──────────┘