探索ClickHouse与Amazon S3结合使用的三种方法 | 亚马逊AWS官方博客

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

ClickHouse 简介

ClickHouse是一种快速的、开源的、用于联机分析（OLAP）的列式数据库管理系统（DBMS），由俄罗斯的Yandex公司开发，于2016年开源。ClickHouse作为交互式分析领域的后起之秀，发展速度非常快，目前在 GitHub 上已收获 14K Star。

ClickHouse主打顶尖的极致性能，每台服务器每秒钟可以处理数亿至数十亿多行或者是数十GB的数据。ClickHouse基于列式存储，通过SQL查询海量数据并实时生成分析报告。ClickHouse充分利用了所有可用的硬件优化技术，以尽可能快地处理每个查询。向量化的查询执行引入了SIMD处理器指令和运行时代码生成技术。列式存储的数据会提高CPU缓存的命中率。ClickHouse 概览文档中的图片清晰地展示了行式存储与列式存储在OLAP领域中的速度差距。

图1 – 行式存储

图2 – 列式存储

在分布式集群中，副本之间的数据读取会自动保持平衡，以避免增加延迟。同时，ClickHouse支持多主异步复制模式，这种情况下所有节点角色都是相等的，可以避免出现单点故障，单个节点或整个可用区的停机时间并不会影响系统的读写可用性。

在网络和应用分析，广告网络和实时出价，电信，电子商务和金融以及商业智能等领域，ClickHouse都有很好的支持与应用，更多信息请参考 ClickHouse官网。

ClickHouse 与对象存储

ClickHouse针对数据量和查询场景提供了不同的数据库和数据表引擎，此外它也可以使用多种多样的专用引擎或表函数（例如HDFS，Kafka，S3等）与许多外部系统进行通讯。在现代化的云架构中，对象存储是最重要的存储组成部分。2006 年，AWS 正式推出的第一个云服务也是 Amazon S3（Simple Storage Service），目前Amazon S3 已经成为事实上的云对象存储标准。

使用对象存储可以给数据分析系统带来诸多优势。首先，它可以使用数据湖架构中的原始数据。其次，对象存储可以为数据表数据提供高性价比且高可靠性的存储。针对S3目前ClickHouse已经上述对象存储的这两种用途。

ClickHouse 与 S3 结合的三种方法

1）通过MergeTree表引擎集成S3

前面提到ClickHouse提供了众多数据库和数据表引擎，这其中最强大的表引擎当属 MergeTree （合并树）引擎及合并树系列（*MergeTree）中的其他引擎。MergeTree 系列的引擎被设计用于插入海量的数据到一张表当中。数据可以以数据片段的形式一个接着一个的快速写入，数据片段在后台按照一定的规则进行合并。相比在插入时不断修改（重写）已存储的数据，这种策略会高效很多。

MergeTree 系列表引擎可以将数据存储在多块设备上。这对某些可以潜在被划分为“冷”“热”的表来说是很有用的。近期数据被定期的查询但只需要比较小的磁盘存储空间。相反，大量的、详尽的历史数据被用到的频率相对较少。ClickHouse可以将S3对象存储用于MergeTree表数据，这样针对“热”的数据，可以放置在快速的磁盘上（比如 NVMe 固态硬盘或内存中），“冷”的数据可以存放在S3对象存储中。在MergeTree 系列表引擎中使用S3的参考架构如下图所示：

2）通过S3表引擎集成

除了MergeTree表引擎，ClickHouse还直接提供了专用的S3表引擎，进一步加强了Amazon S3生态系统的集成，可以充分利用数据湖中已有的各种开放数据格式例如Parquet。通过以下语句就可以进行S3表引擎的创建：

ENGINE = S3(path, [aws_access_key_id, aws_secret_access_key,] format, structure, [compression])

3）通过S3表函数集成

ClickHouse提供表接口的方式对S3中的文件进行SELECT/INSERT操作，这种方式使用起来更加方便，可以快速与ClickHouse中已有的数据进行连接等操作。通过以下语句就可以使用S3表函数：

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

上述方式分别适用于不同的应用场景，可以根据具体情况进行单独或者结合使用。

此外，可以看到这里面还涉及到S3访问权限的安全问题。在ClickHouse 20.13之前的版本中，必须要在SQL或ClickHouse存储配置中提供AWS的访问密钥（Access Key和Secret Access Key）才能访问，这是既不安全也不方便的模式。但是在20.13版本中，ClickHouse提供了实用 IAM Role访问方式，解决了访问S3的安全性问题。

示例参考架构

接下来，我们将演示如何实现上述介绍的ClickHouse与S3结合的三种方法。演示的参考架构如下图所示，我们将ClickHouse环境部署在一个VPC私有子网中，然后通过VPC Enpoints内网的方式来访问S3中的数据。

在示例中，我们将使用纽约出租车数据，该数据分析是Kaggle竞赛的著名赛题之一，也是学习数据分析的经典练习案例，项目数据可以从 NYC网站上进行下载，这里选取了2020年6月的Yellow Taxi Trip Records数据。

以下示例的操作环境为AWS 中国（北京）区域。

1）创建S3存储桶

首先，在AWS 中国（北京）区域创建存储数据的S3存储桶，例如clickhouse-shtian。

2）下载数据并上传到S3存储桶中

首先，在 NYC网站上将2020年6月Yellow Taxi Trip Records数据下载下来，然后上传到刚刚创建的S3桶中。

3）创建并配置S3的VPC Enpoint

VPC Enpoint的创建和配置请参考 VPC文档，确保子网路由表中包含下图中第二条路有条目。

4）部署ClickHouse

示例操作系统为Amazon Linux 2，ClickHouse版本为20.13.1.5591，演示使用单点部署模式，实际使用环境建议部署集群模式，提升高可用的同时也增加性能。需要注意的是在创建EC2实例过程中需要配置IAM角色，可以参考文档适用于 Amazon EC2 的 IAM 角色进行设置，并确保这个角色具有S3桶的读写权限。

SSH登录到EC2实例上，然后下载对应版本的安装包，然后解压并安装。

wget https://github.com/ClickHouse/ClickHouse/releases/download/v20.13.1.5591-testing/clickhouse-client-20.13.1.5591.tgz
wget https://github.com/ClickHouse/ClickHouse/releases/download/v20.13.1.5591-testing/clickhouse-common-static-20.13.1.5591.tgz
wget https://github.com/ClickHouse/ClickHouse/releases/download/v20.13.1.5591-testing/clickhouse-common-static-dbg-20.13.1.5591.tgz
wget https://github.com/ClickHouse/ClickHouse/releases/download/v20.13.1.5591-testing/clickhouse-server-20.13.1.5591.tgz
tar -xzvf clickhouse-common-static-20.13.1.5591.tgz
sudo clickhouse-common-static-20.13.1.5591/install/doinst.sh
tar -xzvf clickhouse-common-static-dbg-20.13.1.5591.tgz
sudo clickhouse-common-static-dbg-20.13.1.5591/install/doinst.sh
tar -xzvf clickhouse-client-20.13.1.5591.tgz
sudo clickhouse-client-20.13.1.5591/install/doinst.sh
tar -xzvf clickhouse-server-20.13.1.5591.tgz
sudo clickhouse-server-20.13.1.5591/install/doinst.sh
       安装成功后，后看到如下提示： 
ClickHouse has been successfully installed.
Start clickhouse-server with:
 sudo clickhouse start
Start clickhouse-client with:
 clickhouse-client
       根据提示使用以下命令启动clickhouse-server服务： 
       sudo clickhouse start 
       执行命令clickhouse-client启动客户端，可以看到连接到服务器并 
        $ clickhouse-client 
ClickHouse client version 20.13.1.5591 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 20.13.1 revision 54443.
       5）配置ClickHouse实现通过MergeTree表引擎集成S3 
       创建并编辑ClickHouse配置文件，ClickHouse的主配置文件通常在/etc/clickhouse-server/config.xml，其他附加配置我们可以通过在/etc/clickhouse-server/config.d/添加xml文件来设置，也方便配置的扩展。 
       sudo vim /etc/clickhouse-server/config.d/merge-s3.xml 
       复制一下内容到文件中，其中use_environment_credentials表示通过IAM的角色、环境变量或者.aws中的安全配置来访问S3。注意替换endpoint部分对应的S3存储桶和路径： 
        <yandex>
  <storage_configuration>
    <disks>
        <type>s3</type>
        <endpoint>https://s3.cn-north-1.amazonaws.com.cn/clickhouse-shtian/mergetree/</endpoint>
        <use_environment_credentials>true</use_environment_credentials>
    </disks>
    <policies>
        <volumes>
            <disk>s3</disk>
          </main>
        </volumes>
    </policies>
  </storage_configuration>
</yandex>
       编辑/etc/clickhouse-server/config.xml，修改openSSL中的client配置，添加一行<caConfig>/etc/pki/tls/certs/ca-bundle.crt</caConfig>，设定SSL/TLS访问的CA证书。如果想使用S3的http端点，则无需配置此选项，但是会存在数据传输安全风险，因此建议使用上面的https的端点并进行如下配置。 
        <client> <!-- Used for connecting to https dictionary source and secured Zookeeper communication -->
            <loadDefaultCAFile>true</loadDefaultCAFile>
            <caConfig>/etc/pki/tls/certs/ca-bundle.crt</caConfig>
            <cacheSessions>true</cacheSessions>
            <disableProtocols>sslv2,sslv3</disableProtocols>
            <preferServerCiphers>true</preferServerCiphers>
            <!-- Use for self-signed: <verificationMode>none</verificationMode> -->
            <invalidCertificateHandler>
                <!-- Use for self-signed: <name>AcceptCertificateHandler</name> -->
                <name>RejectCertificateHandler</name>
            </invalidCertificateHandler>
        </client>
    </openSSL>
       此外，根据操作系统不同，caConfig选项可能不需要单独添加。实际测试在使用Ubuntu 18.04的时候，ClickHouse会自动找到CA证书的位置，无需额外配置。但是，在使用Amazon Linux 2操作系统时需要配置上述选项，否则ClickHouse找不到CA证书的位置，并且会报如下证书错误： 
        Error message: Poco::Exception. Code: 1000, e.code() = 0, e.displayText() = SSL Exception: error:1000007d:SSL routines:OPENSSL_internal:CERTIFICATE_VERIFY_FAILED (version 20.13.1.5591 (official build)) 
       重启clickhouse-server使配置文件生效： 
       sudo clickhouse restart 
       启动客户端clickhouse-client，创建MergeTree引擎的数据表，并选择定义好的S3存储策略： 
        CREATE TABLE default.s3mergetree
    `VendorID` UInt8,
    `VendorName` String
ENGINE = MergeTree
PARTITION BY VendorName
ORDER BY VendorID
SETTINGS storage_policy = 's3'
       插入2行测试数据： 
       INSERT INTO default.s3mergetree VALUES (1, 'Vendor1') (2, 'Vendor2') 
       然后查询这个数据表： 
       SELECT * 
       FROM default.s3mergetree 
       返回结果如下，查询成功： 
       可以看到ClickHouse中磁盘上保留了ClickHouse中数据存储结构，包括数据bin文件、分区信息、索引等内容。 
       但是实际上数据文件中并没有保存真实的数据，而是存储了S3数据的链接。 
       查看S3中的数据信息，数据文件是长这个样子的： 
       尽管原来在块存储中需要硬链接的合并、变异和重命名操作现在是在引用上操作的，S3数据完全没有被触及，但是通过查看上述文件结构发现这会导致另一个问题，就是针对这些数据并没有办法通过其他数据分析工具进行处理，因为ClickHouse本身也是采用的专有数据存储格式，这也是该方案的一个弊端，借助了MergeTree的好处但仅仅是使用S3做为存储。 
       6）配置ClickHouse实现通过专用表引擎集成S3 
       创建并编辑ClickHouse配置文件： 
       sudo vim /etc/clickhouse-server/config.d/table-s3.xml 
       复制一下内容到文件中： 
        <yandex>
        <endpoint>
        <endpoint>https://s3.cn-north-1.amazonaws.com.cn</endpoint>
           <use_environment_credentials>true</use_environment_credentials>
        </endpoint>
</yandex>
       重启clickhouse-server使配置文件生效： 
       sudo clickhouse restart 
       启动客户端clickhouse-client，创建S3引擎的数据表： 
        CREATE TABLE default.s3table
    `VendorID` UInt8,
    `tpep_pickup_datetime` DateTime,
    `tpep_dropoff_datetime` DateTime,
    `passenger_count` UInt8,
    `trip_distance` Float32,
    `RatecodeID` UInt8,
    `store_and_fwd_flag` String,
    `PULocationID` UInt8,
    `DOLocationID` UInt8,
    `payment_type` UInt8,
    `fare_amount` Float32,
    `extra` Float32,
    `mta_tax` Float32,
    `tip_amount` Float32,
    `tolls_amount` Float32,
    `improvement_surcharge` Float32,
    `total_amount` Float32,
    `congestion_surcharge` Float32
ENGINE = S3('https://s3.cn-north-1.amazonaws.com.cn/clickhouse-shtian/yellow_tripdata_2020-06.csv', CSVWithNames)
       然后进行基本的查询： 
        SELECT
    VendorID,
    tpep_pickup_datetime,
    tpep_pickup_datetime,
    passenger_count,
    tolls_amount,
    total_amount,
    congestion_surcharge
FROM default.s3table
LIMIT 10
       返回结果如下，查询成功： 
       需要注意的是，插入数据在这种情况下也是支持的，但是如果表是使用单文件定义的（如本示例），那么插入会覆盖当前文件的内容。如果是使用通配符的方式进行定义（如*.CSV），在插入数据的时候会写到*.CSV，目前已经将问题反馈提交到ClickHouse开源社区。因此，建议目前使用这种方法时，只去查询S3中的数据。 
       该方案的优势在于对于已有的数据湖中的数据，比如各种开放数据格式CSV、Parquet等，都可以通过ClickHouse进行查询，无需作出额外的改动，趋于LakeHouse这样的新架构。 
       7）配置ClickHouse实现通过专用表函数集成S3 
       在步骤6中的配置etc/clickhouse-server/config.d/table-s3.xml对S3专用表函数也是生效的，所以直接在客户端clickhouse-client继续进行查询即可： 
        SELECT
    VendorID,
    tpep_pickup_datetime,
    tpep_pickup_datetime,
    passenger_count,
    tolls_amount,
    total_amount,
    congestion_surcharge
FROM s3('https://s3.cn-north-1.amazonaws.com.cn/clickhouse-shtian/yellow_tripdata_2020-06.csv', CSVWithNames, 'VendorID UInt8,tpep_pickup_datetime DateTime,tpep_dropoff_datetime DateTime,passenger_count UInt8,trip_distance Float32,RatecodeID UInt8,store_and_fwd_flag String,PULocationID UInt8,DOLocationID UInt8,payment_type UInt8,fare_amount Float32,extra Float32,mta_tax Float32,tip_amount Float32,tolls_amount Float32,improvement_surcharge Float32,total_amount Float32,congestion_surcharge Float32')
LIMIT 10
       返回结果如下，数据查询成功： 
       除了单独使用S3表函数，还可以和其他MergeTree表进行连接，例如我们可以使用以下SQL将S3表函数和步骤5中创建的表进行JOIN查询。 
        SELECT
    VendorName,
    VendorID,
    tpep_pickup_datetime,
    tpep_pickup_datetime,
    passenger_count,
    tolls_amount,
    total_amount,
    congestion_surcharge
FROM s3('https://s3.cn-north-1.amazonaws.com.cn/clickhouse-shtian/yellow_tripdata_2020-06.csv', CSVWithNames, 'VendorID UInt8,tpep_pickup_datetime DateTime,tpep_dropoff_datetime DateTime,passenger_count UInt8,trip_distance Float32,RatecodeID UInt8,store_and_fwd_flag String,PULocationID UInt8,DOLocationID UInt8,payment_type UInt8,fare_amount Float32,extra Float32,mta_tax Float32,tip_amount Float32,tolls_amount Float32,improvement_surcharge Float32,total_amount Float32,congestion_surcharge Float32') AS s3
INNER JOIN default.s3mergetree ON s3.VendorID = s3mergetree.VendorID
WHERE s3mergetree.VendorID = 1
LIMIT 10
       返回结果如下，数据查询成功： 
       该方案同样发挥了数据湖的价值，可以和已有的各种开放数据格式CSV、Parquet等数据进行连接，扩展了数据仓库的使用范围。 
       通过上述演示，可以基本实现不同应用场景下的ClickHouse和S3结合。由于ClickHouse是开源项目，所以和S3的集成和更丰富的特性还在逐步完善中。 
       本文首先简单介绍了ClickHouse及其特性和使用场景，然后介绍了通过与Amazon S3存储的结合，可以为数据分析系统带来的优势：成本优化以及数据湖的应用。接下来，我们又介绍了ClickHouse和S3集成的三种方案，并通过具体示例来展示了各方案的具体实现方法和优劣势。 
       参考资料： 
       https://altinity.com/blog/clickhouse-and-s3-compatible-object-storage 
       https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/mergetree/#table_engine-mergetree-multiple-volumes 
       https://clickhouse.tech/docs/en/engines/table-engines/integrations/s3/ 
       https://clickhouse.tech/docs/en/sql-reference/table-functions/s3/