S3 表引擎
此引擎提供与 Amazon S3 生态系统的集成。此引擎类似于 HDFS 引擎,但提供了 S3 特定功能。
示例
CREATE TABLE s3_engine_table (name String, value UInt32)
ENGINE=S3('https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/test-data.csv.gz', 'CSV', 'gzip')
SETTINGS input_format_with_names_use_header = 0;
INSERT INTO s3_engine_table VALUES ('one', 1), ('two', 2), ('three', 3);
SELECT * FROM s3_engine_table LIMIT 2;
┌─name─┬─value─┐
│ one │ 1 │
│ two │ 2 │
└──────┴───────┘
创建表
CREATE TABLE s3_engine_table (name String, value UInt32)
ENGINE = S3(path [, NOSIGN | aws_access_key_id, aws_secret_access_key,] format, [compression])
[PARTITION BY expr]
[SETTINGS ...]
引擎参数
path
— 带有文件路径的存储桶 URL。在只读模式下支持以下通配符:*
,**
,?
,{abc,def}
和{N..M}
,其中N
,M
— 数字,'abc'
,'def'
— 字符串。有关更多信息,请参见 下文。NOSIGN
- 如果在凭据位置提供此关键字,则所有请求均不会被签名。format
— 文件的 格式。aws_access_key_id
,aws_secret_access_key
- AWS 帐户用户的长期凭据。您可以使用这些凭据来验证您的请求。该参数是可选的。如果未指定凭据,则使用配置文件中的凭据。有关更多信息,请参见 使用 S3 存储数据。compression
— 压缩类型。支持的值:none
,gzip/gz
,brotli/br
,xz/LZMA
,zstd/zst
。该参数是可选的。默认情况下,它将根据文件扩展名自动检测压缩。
数据缓存
S3
表引擎支持在本地磁盘上缓存数据。请查看此 部分 中的文件系统缓存配置选项和用法。缓存根据存储对象路径和 ETag 进行,因此 ClickHouse 不会读取过期的缓存版本。
要启用缓存,请使用设置 filesystem_cache_name = '<name>'
和 enable_filesystem_cache = 1
。
SELECT *
FROM s3('http://minio:10000/clickhouse//test_3.csv', 'minioadmin', 'minioadminpassword', 'CSV')
SETTINGS filesystem_cache_name = 'cache_for_s3', enable_filesystem_cache = 1;
在配置文件中有两种方法可以定义缓存。
- 将以下部分添加到 ClickHouse 配置文件
<clickhouse>
<filesystem_caches>
<cache_for_s3>
<path>path to cache directory</path>
<max_size>10Gi</max_size>
</cache_for_s3>
</filesystem_caches>
</clickhouse>
- 从 ClickHouse
storage_configuration
部分重复使用缓存配置(以及缓存存储),在此处介绍
PARTITION BY
PARTITION BY
— 可选。在大多数情况下,您不需要分区键,如果需要,您通常不需要比按月更细粒度地分区。分区不会加速查询(与 ORDER BY 表达式相反)。您永远不应该使用过于细粒度的分区。不要按客户标识符或名称对数据进行分区(相反,将客户标识符或名称设为 ORDER BY 表达式中的第一列)。
要按月进行分区,请使用 toYYYYMM(date_column)
表达式,其中 date_column
是包含日期类型的日期列 日期。此处的分区名称采用 "YYYYMM"
格式。
查询分区数据
此示例使用 docker compose 食谱,它集成了 ClickHouse 和 MinIO。您应该能够使用 S3 复制相同的查询,方法是替换端点和身份验证值。
请注意,ENGINE
配置中的 S3 端点使用参数标记 {_partition_id}
作为 S3 对象(文件名)的一部分,并且 SELECT 查询针对这些结果对象名称进行选择(例如,test_3.csv
)。
如示例所示,目前尚不支持直接从已分区的 S3 表中查询,但可以通过使用 S3 表函数查询各个分区来实现。
在 S3 中写入分区数据的首要用例是能够将这些数据传输到另一个 ClickHouse 系统(例如,从本地系统迁移到 ClickHouse Cloud)。由于 ClickHouse 数据集通常非常大,并且网络可靠性有时不完美,因此将数据集分成子集进行传输是有意义的,因此需要分区写入。
创建表
CREATE TABLE p
(
`column1` UInt32,
`column2` UInt32,
`column3` UInt32
)
ENGINE = S3(
'http://minio:10000/clickhouse//test_{_partition_id}.csv',
'minioadmin',
'minioadminpassword',
'CSV')
PARTITION BY column3
插入数据
insert into p values (1, 2, 3), (3, 2, 1), (78, 43, 45)
从分区 3 中选择
此查询使用 s3 表函数
SELECT *
FROM s3('http://minio:10000/clickhouse//test_3.csv', 'minioadmin', 'minioadminpassword', 'CSV')
┌─c1─┬─c2─┬─c3─┐
│ 1 │ 2 │ 3 │
└────┴────┴────┘
从分区 1 中选择
SELECT *
FROM s3('http://minio:10000/clickhouse//test_1.csv', 'minioadmin', 'minioadminpassword', 'CSV')
┌─c1─┬─c2─┬─c3─┐
│ 3 │ 2 │ 1 │
└────┴────┴────┘
从分区 45 中选择
SELECT *
FROM s3('http://minio:10000/clickhouse//test_45.csv', 'minioadmin', 'minioadminpassword', 'CSV')
┌─c1─┬─c2─┬─c3─┐
│ 78 │ 43 │ 45 │
└────┴────┴────┘
限制
您可能会尝试 Select * from p
,但如上所述,此查询将失败;请使用前面的查询。
SELECT * FROM p
Received exception from server (version 23.4.1):
Code: 48. DB::Exception: Received from localhost:9000. DB::Exception: Reading from a partitioned S3 storage is not implemented yet. (NOT_IMPLEMENTED)
虚拟列
_path
— 文件路径。类型:LowCardinality(String)
。_file
— 文件名。类型:LowCardinality(String)
。_size
— 文件的大小(以字节为单位)。类型:Nullable(UInt64)
。如果大小未知,则值为NULL
。_time
— 文件的最后修改时间。类型:Nullable(DateTime)
。如果时间未知,则值为NULL
。_etag
— 文件的 ETag。类型:LowCardinality(String)
。如果 etag 未知,则值为NULL
。
有关虚拟列的更多信息,请参见 此处。
实现细节
读取和写入可以并行进行
不支持
ALTER
和SELECT...SAMPLE
操作。- 索引。
- 零拷贝 复制是可能的,但不受支持。
零拷贝复制尚未准备好投入生产在 ClickHouse 22.8 及更高版本中,零拷贝复制默认情况下处于禁用状态。此功能不建议在生产环境中使用。
路径中的通配符
path
参数可以使用类似 bash 的通配符指定多个文件。要处理的文件必须存在并与整个路径模式匹配。文件的列表是在 SELECT
期间确定的(而不是在 CREATE
时)。
*
— 替换除/
之外的任意数量的任意字符,包括空字符串。**
— 替换包括/
在内的任意数量的任意字符,包括空字符串。?
— 替换任意单个字符。{some_string,another_string,yet_another_one}
— 替换字符串'some_string', 'another_string', 'yet_another_one'
中的任意一个。{N..M}
— 替换从 N 到 M(包括两个边界)的任意数字。N 和 M 可以有前导零,例如000..078
。
带有 {}
的构造类似于 远程 表函数。
如果文件列表包含带有前导零的数字范围,请使用带有大括号的构造分别针对每个数字,或者使用 ?
。
带有通配符的示例 1
创建包含名为 file-000.csv
、file-001.csv
、...、file-999.csv
的文件的表
CREATE TABLE big_table (name String, value UInt32)
ENGINE = S3('https://clickhouse-public-datasets.s3.amazonaws.com/my-bucket/my_folder/file-{000..999}.csv', 'CSV');
带有通配符的示例 2
假设我们在 S3 上有几个以 CSV 格式命名的文件,其 URI 如下所示
- 'https://clickhouse-public-datasets.s3.amazonaws.com/my-bucket/some_folder/some_file_1.csv'
- 'https://clickhouse-public-datasets.s3.amazonaws.com/my-bucket/some_folder/some_file_2.csv'
- 'https://clickhouse-public-datasets.s3.amazonaws.com/my-bucket/some_folder/some_file_3.csv'
- 'https://clickhouse-public-datasets.s3.amazonaws.com/my-bucket/another_folder/some_file_1.csv'
- 'https://clickhouse-public-datasets.s3.amazonaws.com/my-bucket/another_folder/some_file_2.csv'
- 'https://clickhouse-public-datasets.s3.amazonaws.com/my-bucket/another_folder/some_file_3.csv'
有几种方法可以创建一个包含所有六个文件的表
- 指定文件后缀范围
CREATE TABLE table_with_range (name String, value UInt32)
ENGINE = S3('https://clickhouse-public-datasets.s3.amazonaws.com/my-bucket/{some,another}_folder/some_file_{1..3}', 'CSV');
- 获取所有以
some_file_
为前缀的文件(两个文件夹中都不应存在具有此前缀的其他文件)
CREATE TABLE table_with_question_mark (name String, value UInt32)
ENGINE = S3('https://clickhouse-public-datasets.s3.amazonaws.com/my-bucket/{some,another}_folder/some_file_?', 'CSV');
- 获取两个文件夹中的所有文件(所有文件应满足查询中描述的格式和模式)
CREATE TABLE table_with_asterisk (name String, value UInt32)
ENGINE = S3('https://clickhouse-public-datasets.s3.amazonaws.com/my-bucket/{some,another}_folder/*', 'CSV');
存储设置
- s3_truncate_on_insert - 允许在插入文件之前截断文件。默认情况下禁用。
- s3_create_new_file_on_insert - 允许在每次插入时创建一个新文件,如果格式具有后缀。默认情况下禁用。
- s3_skip_empty_files - 允许在读取时跳过空文件。默认情况下禁用。
与 S3 相关的设置
以下设置可以在查询执行之前设置,也可以放入配置文件中。
s3_max_single_part_upload_size
- 使用单部分上传到 S3 上传对象的最大大小。默认值为32Mb
。s3_min_upload_part_size
- 在使用 S3 多部分上传 到 S3 时上传部分的最小大小。默认值为16Mb
。s3_max_redirects
- 允许的最大 S3 重定向跳转次数。默认值为10
。s3_single_read_retries
- 单次读取期间的最大尝试次数。默认值为4
。s3_max_put_rps
- 节流之前每秒的最大 PUT 请求速率。默认值为0
(无限制)。s3_max_put_burst
- 在达到每秒请求限制之前可以同时发出请求的最大次数。默认情况下(0
值)等于s3_max_put_rps
。s3_max_get_rps
- 节流之前每秒的最大 GET 请求速率。默认值为0
(无限制)。s3_max_get_burst
- 在达到每秒请求限制之前可以同时发出请求的最大次数。默认情况下(0
值)等于s3_max_get_rps
。s3_upload_part_size_multiply_factor
- 每当从单个写入到 S3 上传了s3_multiply_parts_count_threshold
个部分时,将s3_min_upload_part_size
乘以此因子。默认值为2
。s3_upload_part_size_multiply_parts_count_threshold
- 每当将此数量的部分上传到 S3 时,s3_min_upload_part_size
将乘以s3_upload_part_size_multiply_factor
。默认值为500
。s3_max_inflight_parts_for_one_file
- 限制可以为一个对象同时运行的 PUT 请求数量。其数量应有限制。值为0
表示无限制。默认值为20
。每个正在传输的部分都有一个大小为s3_min_upload_part_size
的缓冲区,用于前s3_upload_part_size_multiply_factor
个部分,当文件足够大时,缓冲区更大,请参阅upload_part_size_multiply_factor
。使用默认设置,一个上传的文件对于小于8G
的文件最多消耗320Mb
。对于更大的文件,消耗更大。
安全注意事项:如果恶意用户可以指定任意 S3 URL,则必须将 s3_max_redirects
设置为零以避免 SSRF 攻击;或者,必须在服务器配置中指定 remote_host_filter
。
基于端点的设置
以下设置可以在配置文件中为给定的端点指定(该端点将通过 URL 的确切前缀匹配)
endpoint
- 指定端点的前缀。必需。access_key_id
和secret_access_key
- 指定要与给定端点一起使用的凭据。可选。use_environment_credentials
- 如果设置为true
,S3 客户端将尝试从环境变量和 Amazon EC2 元数据中获取给定端点的凭据。可选,默认值为false
。region
- 指定 S3 地区名称。可选。use_insecure_imds_request
- 如果设置为true
,S3 客户端将在从 Amazon EC2 元数据中获取凭据时使用不安全的 IMDS 请求。可选,默认值为false
。expiration_window_seconds
- 检查基于到期时间的凭据是否已过期的宽限期。可选,默认值为120
。no_sign_request
- 忽略所有凭据,因此请求不会被签名。对访问公共存储桶很有用。header
- 将指定的 HTTP 标头添加到对给定端点的请求中。可选,可以多次指定。server_side_encryption_customer_key_base64
- 如果指定,则将设置访问使用 SSE-C 加密的 S3 对象所需的标头。可选。server_side_encryption_kms_key_id
- 如果指定,则将设置访问使用 SSE-KMS 加密 的 S3 对象所需的标头。如果指定空字符串,则将使用 AWS 托管的 S3 密钥。可选。server_side_encryption_kms_encryption_context
- 如果与server_side_encryption_kms_key_id
一起指定,则将设置给定的 SSE-KMS 加密上下文标头。可选。server_side_encryption_kms_bucket_key_enabled
- 如果与server_side_encryption_kms_key_id
一起指定,则将设置用于 SSE-KMS 的 S3 存储桶密钥的标头。可选,可以是true
或false
,默认为无(与存储桶级设置匹配)。max_single_read_retries
- 单次读取期间的最大尝试次数。默认值为4
。可选。max_put_rps
、max_put_burst
、max_get_rps
和max_get_burst
- 用于特定端点而不是每个查询的节流设置(见上文描述)。可选。
示例
<s3>
<endpoint-name>
<endpoint>https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/</endpoint>
<!-- <access_key_id>ACCESS_KEY_ID</access_key_id> -->
<!-- <secret_access_key>SECRET_ACCESS_KEY</secret_access_key> -->
<!-- <region>us-west-1</region> -->
<!-- <use_environment_credentials>false</use_environment_credentials> -->
<!-- <use_insecure_imds_request>false</use_insecure_imds_request> -->
<!-- <expiration_window_seconds>120</expiration_window_seconds> -->
<!-- <no_sign_request>false</no_sign_request> -->
<!-- <header>Authorization: Bearer SOME-TOKEN</header> -->
<!-- <server_side_encryption_customer_key_base64>BASE64-ENCODED-KEY</server_side_encryption_customer_key_base64> -->
<!-- <server_side_encryption_kms_key_id>KMS_KEY_ID</server_side_encryption_kms_key_id> -->
<!-- <server_side_encryption_kms_encryption_context>KMS_ENCRYPTION_CONTEXT</server_side_encryption_kms_encryption_context> -->
<!-- <server_side_encryption_kms_bucket_key_enabled>true</server_side_encryption_kms_bucket_key_enabled> -->
<!-- <max_single_read_retries>4</max_single_read_retries> -->
</endpoint-name>
</s3>
访问公共存储桶
ClickHouse 尝试从许多不同类型的来源获取凭据。有时,在访问一些公共存储桶时,它可能会产生问题,导致客户端返回 403
错误代码。可以使用 NOSIGN
关键字避免此问题,强制客户端忽略所有凭据,并且不签名请求。
CREATE TABLE big_table (name String, value UInt32)
ENGINE = S3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/aapl_stock.csv', NOSIGN, 'CSVWithNames');