跳到主要内容
跳到主要内容

s3 表函数

提供了一个类似表的接口来选择/插入 Amazon S3Google Cloud Storage 中的文件。该表函数类似于 hdfs 函数,但提供特定于 S3 的功能。

如果您的集群中有多个副本,可以使用 s3Cluster 函数 来并行化插入。

使用 s3 表函数INSERT INTO...SELECT 时,数据以流式方式读取和插入。在从 S3 持续读取数据并传送到目标表的同时,只有几个数据块驻留在内存中。

语法

GCS

S3 表函数通过使用 GCS XML API 和 HMAC 密钥与 Google Cloud Storage 集成。有关端点和 HMAC 的更多详细信息,请参阅 Google 互操作性文档

对于 GCS,请在 access_key_idsecret_access_key 的位置替换为您的 HMAC 密钥和 HMAC 秘钥。

参数

s3 表函数支持以下普通参数:

参数描述
url带有文件路径的存储桶 URL。在只读模式下支持以下通配符:***?{abc,def}{N..M},其中 NM 是数字,'abc''def' 是字符串。有关更多信息,请参见 这里
NOSIGN如果在凭据位置提供该关键字,则所有请求都不会被签名。
access_key_idsecret_access_key指定用于给定端点的凭据的密钥。可选。
session_token用于给定密钥的会话令牌。传递密钥时可选。
format文件的 格式
structure表的结构。格式为 'column1_name column1_type, column2_name column2_type, ...'
compression_method参数是可选的。支持的值:nonegzipgzbrotlibrxzLZMAzstdzst。默认情况下,它将通过文件扩展名自动检测压缩方法。
headers参数是可选的。允许在 S3 请求中传递头。以 headers(key=value) 格式传递,例如 headers('x-amz-request-payer' = 'requester')
GCS

GCS URL 格式为:Google XML API 的端点与 JSON API 不同:

而不是 ~~https://storage.cloud.google.com~~。

参数也可以使用 命名集合 传递。在这种情况下,urlaccess_key_idsecret_access_keyformatstructurecompression_method 的工作方式相同,并且支持一些额外参数:

参数描述
filename如果指定,将附加到 URL。
use_environment_credentials默认为启用,允许使用环境变量 AWS_CONTAINER_CREDENTIALS_RELATIVE_URIAWS_CONTAINER_CREDENTIALS_FULL_URIAWS_CONTAINER_AUTHORIZATION_TOKENAWS_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 如下:

计算以数字 1 到 3 结尾的文件中的行数:

计算这两个目录中所有文件的总行数:

提示

如果您的文件列表包含以零开头的数字范围,请为每个数字单独使用带花括号的结构,或使用 ?

计算名为 file-000.csvfile-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 表达式,则会为每个分区值创建一个单独的文件。将数据分成单独的文件有助于提高读取操作的效率。

示例

  1. 在键中使用分区 ID 创建单独的文件:

结果,数据被写入三个文件:file_x.csvfile_y.csvfile_z.csv

  1. 在存储桶名称中使用分区 ID 创建不同存储桶中的文件:

结果,数据被写入不同存储桶中的三个文件:my_bucket_1/file.csvmy_bucket_10/file.csvmy_bucket_20/file.csv

访问公共存储桶

ClickHouse 尝试从许多不同类型的源获取凭据。 有时,在访问某些公共存储桶时,可能会出现问题,导致客户端返回 403 错误。可以通过使用 NOSIGN 关键字避免此问题,强制客户端忽略所有凭据,并且不签名请求。

使用 S3 凭据 (ClickHouse Cloud)

对于非公共存储桶,用户可以向该函数传递 aws_access_key_idaws_secret_access_key。例如:

这适合一次性访问或凭据容易轮换的情况。但是,不建议作为长期解决方案以进行重复访问或凭据敏感的情况。在这种情况下,我们建议用户依赖基于角色的访问。

ClickHouse Cloud 中的 S3 的基于角色的访问文档在 这里

配置完成后,可以通过 extra_credentials 参数将 roleARN 传递给 s3 函数。例如:

更多示例可以在 这里 找到。

处理归档

假设我们在 S3 上有几个归档文件,其 URI 如下:

可以使用 :: 从这些归档中提取数据。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 函数来实现。例如:

存储设置