s3 表函数
提供了一个类似表的接口来选择/插入 Amazon S3 和 Google Cloud Storage 中的文件。该表函数类似于 hdfs 函数,但提供特定于 S3 的功能。
如果您的集群中有多个副本,可以使用 s3Cluster 函数 来并行化插入。
使用 s3 表函数
与 INSERT INTO...SELECT
时,数据以流式方式读取和插入。在从 S3 持续读取数据并传送到目标表的同时,只有几个数据块驻留在内存中。
语法
S3 表函数通过使用 GCS XML API 和 HMAC 密钥与 Google Cloud Storage 集成。有关端点和 HMAC 的更多详细信息,请参阅 Google 互操作性文档。
对于 GCS,请在 access_key_id
和 secret_access_key
的位置替换为您的 HMAC 密钥和 HMAC 秘钥。
参数
s3
表函数支持以下普通参数:
参数 | 描述 |
---|---|
url | 带有文件路径的存储桶 URL。在只读模式下支持以下通配符:* ,** ,? ,{abc,def} 和 {N..M} ,其中 N ,M 是数字,'abc' ,'def' 是字符串。有关更多信息,请参见 这里。 |
NOSIGN | 如果在凭据位置提供该关键字,则所有请求都不会被签名。 |
access_key_id 和 secret_access_key | 指定用于给定端点的凭据的密钥。可选。 |
session_token | 用于给定密钥的会话令牌。传递密钥时可选。 |
format | 文件的 格式。 |
structure | 表的结构。格式为 'column1_name column1_type, column2_name column2_type, ...' 。 |
compression_method | 参数是可选的。支持的值:none ,gzip 或 gz ,brotli 或 br ,xz 或 LZMA ,zstd 或 zst 。默认情况下,它将通过文件扩展名自动检测压缩方法。 |
headers | 参数是可选的。允许在 S3 请求中传递头。以 headers(key=value) 格式传递,例如 headers('x-amz-request-payer' = 'requester') 。 |
参数也可以使用 命名集合 传递。在这种情况下,url
,access_key_id
,secret_access_key
,format
,structure
,compression_method
的工作方式相同,并且支持一些额外参数:
参数 | 描述 |
---|---|
filename | 如果指定,将附加到 URL。 |
use_environment_credentials | 默认为启用,允许使用环境变量 AWS_CONTAINER_CREDENTIALS_RELATIVE_URI ,AWS_CONTAINER_CREDENTIALS_FULL_URI ,AWS_CONTAINER_AUTHORIZATION_TOKEN ,AWS_EC2_METADATA_DISABLED 传递额外参数。 |
no_sign_request | 默认为禁用。 |
expiration_window_seconds | 默认值为 120。 |
返回值
一个具有指定结构的表,用于读取或写入指定文件中的数据。
示例
从 S3 文件 https://datasets-documentation.s3.eu-west-3.amazonaws.com/aapl_stock.csv
中选择前 5 行:
ClickHouse 使用文件名扩展名来确定数据格式。例如,我们可以在不使用 CSVWithNames
的情况下运行上一个命令:
ClickHouse 还可以确定文件的压缩方法。例如,如果文件以 .csv.gz
扩展名压缩,ClickHouse 会自动解压缩文件。
用法
假设我们在 S3 上有几个文件,其 URI 如下:
- 'https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/some_prefix/some_file_1.csv'
- 'https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/some_prefix/some_file_2.csv'
- 'https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/some_prefix/some_file_3.csv'
- 'https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/some_prefix/some_file_4.csv'
- 'https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/another_prefix/some_file_1.csv'
- 'https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/another_prefix/some_file_2.csv'
- 'https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/another_prefix/some_file_3.csv'
- 'https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/another_prefix/some_file_4.csv'
计算以数字 1 到 3 结尾的文件中的行数:
计算这两个目录中所有文件的总行数:
如果您的文件列表包含以零开头的数字范围,请为每个数字单独使用带花括号的结构,或使用 ?
。
计算名为 file-000.csv
,file-001.csv
,...,file-999.csv
的文件中的总行数:
将数据插入到文件 test-data.csv.gz
中:
将数据从现有表插入到文件 test-data.csv.gz
中:
Glob ** 可用于递归目录遍历。请考虑以下示例,它将递归获取 my-test-bucket-768
目录中的所有文件:
以下从 my-test-bucket
目录中的任何文件夹递归获取所有 test-data.csv.gz
文件:
注意:可以在服务器配置文件中指定自定义 URL 映射器。例如:
URL 's3://clickhouse-public-datasets/my-test-bucket-768/**/test-data.csv.gz'
将替换为 'http://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/**/test-data.csv.gz'
自定义映射器可以添加到 config.xml
中:
对于生产用例,建议使用 命名集合。以下是示例:
分区写入
如果在向 S3
表插入数据时指定了 PARTITION BY
表达式,则会为每个分区值创建一个单独的文件。将数据分成单独的文件有助于提高读取操作的效率。
示例
- 在键中使用分区 ID 创建单独的文件:
结果,数据被写入三个文件:file_x.csv
,file_y.csv
和 file_z.csv
。
- 在存储桶名称中使用分区 ID 创建不同存储桶中的文件:
结果,数据被写入不同存储桶中的三个文件:my_bucket_1/file.csv
,my_bucket_10/file.csv
和 my_bucket_20/file.csv
。
访问公共存储桶
ClickHouse 尝试从许多不同类型的源获取凭据。
有时,在访问某些公共存储桶时,可能会出现问题,导致客户端返回 403
错误。可以通过使用 NOSIGN
关键字避免此问题,强制客户端忽略所有凭据,并且不签名请求。
使用 S3 凭据 (ClickHouse Cloud)
对于非公共存储桶,用户可以向该函数传递 aws_access_key_id
和 aws_secret_access_key
。例如:
这适合一次性访问或凭据容易轮换的情况。但是,不建议作为长期解决方案以进行重复访问或凭据敏感的情况。在这种情况下,我们建议用户依赖基于角色的访问。
ClickHouse Cloud 中的 S3 的基于角色的访问文档在 这里。
配置完成后,可以通过 extra_credentials
参数将 roleARN
传递给 s3 函数。例如:
更多示例可以在 这里 找到。
处理归档
假设我们在 S3 上有几个归档文件,其 URI 如下:
- 'https://s3-us-west-1.amazonaws.com/umbrella-static/top-1m-2018-01-10.csv.zip'
- 'https://s3-us-west-1.amazonaws.com/umbrella-static/top-1m-2018-01-11.csv.zip'
- 'https://s3-us-west-1.amazonaws.com/umbrella-static/top-1m-2018-01-12.csv.zip'
可以使用 :: 从这些归档中提取数据。Glob 可以在 URL 部分和 :: 之后的部分中都使用(负责归档内部文件的名称)。
ClickHouse 支持三种归档格式: ZIP TAR 7Z 其中 ZIP 和 TAR 归档可以从任何支持的存储位置访问,但 7Z 归档只能从安装 ClickHouse 的本地文件系统读取。
插入数据
请注意,行只能插入到新文件中。没有合并周期或文件拆分操作。文件一旦写入,后续插入将失败。更多细节见 这里。
虚拟列
_path
— 文件的路径。类型:LowCardinality(String)
。在归档情况下,显示格式为:"{path_to_archive}::{path_to_file_inside_archive}"
。_file
— 文件名。类型:LowCardinality(String)
。在归档情况下,显示归档内的文件名。_size
— 文件的字节大小。类型:Nullable(UInt64)
。如果文件大小未知,则值为NULL
。在归档情况下,显示归档内文件的未压缩文件大小。_time
— 文件的最后修改时间。类型:Nullable(DateTime)
。如果时间未知,则值为NULL
。
Hive 风格的分区
当设置 use_hive_partitioning
为 1 时,ClickHouse 将在路径中检测 Hive 风格的分区 (/name=value/
) 并允许在查询中使用分区列作为虚拟列。这些虚拟列的名称与分区路径中的名称相同,但以 _
开头。
示例
使用 Hive 风格分区创建的虚拟列
访问请求者支付存储桶
要访问请求者支付存储桶,必须在任何请求中传递头 x-amz-request-payer = requester
。这可以通过将参数 headers('x-amz-request-payer' = 'requester')
传递给 s3 函数来实现。例如:
存储设置
- s3_truncate_on_insert - 允许在插入前截断文件。默认情况下禁用。
- s3_create_new_file_on_insert - 如果格式有后缀,则允许在每次插入时创建新文件。默认情况下禁用。
- s3_skip_empty_files - 允许在读取时跳过空文件。默认情况下启用。