s3 表函数

提供了一种类似表格的接口，用于在 Amazon S3 和 Google Cloud Storage 中选择/插入文件。此表函数类似于 hdfs 函数，但提供了 S3 特定的功能。

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

在使用 s3 表函数 与 INSERT INTO...SELECT 时，数据以流式方式读取和插入。只有少量数据块驻留在内存中，同时数据块会从 S3 持续读取并推送到目标表。

语法

s3(url [, NOSIGN | access_key_id, secret_access_key, [session_token]] [,format] [,structure] [,compression_method],[,headers], [,partition_strategy], [,partition_columns_in_data_file])
s3(named_collection[, option=value [,..]])

GCS

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`	文件的格式。
`结构`	表的结构。格式为 `'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')`。
`partition_strategy`	参数是可选的。支持的值：`WILDCARD` 或 `HIVE`。`WILDCARD` 需要路径中的 `{_partition_id}`，该 ID 将被分区键替换。`HIVE` 不允许通配符，假定路径是表根目录，并生成带有 Snowflake ID 作为文件名和文件格式作为扩展名的 Hive 样式分区目录。默认值为 `WILDCARD`
`partition_columns_in_data_file`	参数是可选的。仅与 `HIVE` 分区策略一起使用。告诉 ClickHouse 是否期望在数据文件中写入分区列。默认值为 `false`。
`storage_class_name`	参数是可选的。支持的值：`STANDARD` 或 `INTELLIGENT_TIERING`。允许指定 AWS S3 Intelligent Tiering。默认值为 `STANDARD`。

GCS

GCS URL 的格式如下，因为 Google XML API 的端点与 JSON API 不同

  https://storage.googleapis.com/<bucket>/<folder>/<filename(s)>

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

也可以使用命名集合传递参数。在这种情况下，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 行

SELECT *
FROM s3(
   'https://datasets-documentation.s3.eu-west-3.amazonaws.com/aapl_stock.csv',
   'CSVWithNames'
)
LIMIT 5;

┌───────Date─┬────Open─┬────High─┬─────Low─┬───Close─┬───Volume─┬─OpenInt─┐
│ 1984-09-07 │ 0.42388 │ 0.42902 │ 0.41874 │ 0.42388 │ 23220030 │       0 │
│ 1984-09-10 │ 0.42388 │ 0.42516 │ 0.41366 │ 0.42134 │ 18022532 │       0 │
│ 1984-09-11 │ 0.42516 │ 0.43668 │ 0.42516 │ 0.42902 │ 42498199 │       0 │
│ 1984-09-12 │ 0.42902 │ 0.43157 │ 0.41618 │ 0.41618 │ 37125801 │       0 │
│ 1984-09-13 │ 0.43927 │ 0.44052 │ 0.43927 │ 0.43927 │ 57822062 │       0 │
└────────────┴─────────┴─────────┴─────────┴─────────┴──────────┴─────────┘

注意

ClickHouse 使用文件名扩展名来确定数据的格式。例如，我们可以运行前一个命令而无需 CSVWithNames

SELECT *
FROM s3(
   'https://datasets-documentation.s3.eu-west-3.amazonaws.com/aapl_stock.csv'
)
LIMIT 5;

ClickHouse 还可以确定文件的压缩方法。例如，如果文件使用 .csv.gz 扩展名压缩，ClickHouse 将自动解压缩该文件。

注意

名称类似于 *.parquet.snappy 或 *.parquet.zstd 的 Parquet 文件可能会混淆 ClickHouse 并导致 TOO_LARGE_COMPRESSED_BLOCK 或 ZSTD_DECODER_FAILED 错误。这是因为 ClickHouse 会尝试将整个文件作为 Snappy 或 ZSTD 编码的数据读取，而实际上 Parquet 在行组和列级别应用压缩。

Parquet 元数据已经指定了每列的压缩，因此文件扩展名是多余的。在这种情况下，您可以只使用 compression_method = 'none'

SELECT *
FROM s3(
  'https://<my-bucket>.s3.<my-region>.amazonaws.com/path/to/my-data.parquet.snappy',
  compression_format = 'none'
);

用法

假设我们在 S3 上有几个具有以下 URI 的文件

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

SELECT count(*)
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/my-test-bucket-768/{some,another}_prefix/some_file_{1..3}.csv', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32')

┌─count()─┐
│      18 │
└─────────┘

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

SELECT count(*)
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/my-test-bucket-768/{some,another}_prefix/*', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32')

┌─count()─┐
│      24 │
└─────────┘

提示

如果您的文件列表包含带有前导零的数字范围，请为每个数字分别使用大括号构造，或使用 ?。

计算名为 file-000.csv、file-001.csv、...、file-999.csv 的文件的总行数

SELECT count(*)
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/my-test-bucket-768/big_prefix/file-{000..999}.csv', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32');

┌─count()─┐
│      12 │
└─────────┘

将数据插入到文件 test-data.csv.gz

INSERT INTO FUNCTION s3('https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/test-data.csv.gz', 'CSV', 'name String, value UInt32', 'gzip')
VALUES ('test-data', 1), ('test-data-2', 2);

从现有表将数据插入到文件 test-data.csv.gz

INSERT INTO FUNCTION s3('https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/test-data.csv.gz', 'CSV', 'name String, value UInt32', 'gzip')
SELECT name, value FROM existing_table;

可以使用 ** 进行递归目录遍历。考虑下面的示例，它将从 my-test-bucket-768 目录递归地获取所有文件

SELECT * FROM s3('https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/**', 'CSV', 'name String, value UInt32', 'gzip');

以下从 my-test-bucket 目录内的任何文件夹中递归地获取所有 test-data.csv.gz 文件

SELECT * FROM s3('https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/**/test-data.csv.gz', 'CSV', 'name String, value UInt32', 'gzip');

注意。可以在服务器配置文件中指定自定义 URL 映射器。示例

SELECT * FROM s3('s3://clickhouse-public-datasets/my-test-bucket-768/**/test-data.csv.gz', 'CSV', 'name String, value UInt32', 'gzip');

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

<url_scheme_mappers>
   <s3>
      <to>https://{bucket}.s3.amazonaws.com</to>
   </s3>
   <gs>
      <to>https://{bucket}.storage.googleapis.com</to>
   </gs>
   <oss>
      <to>https://{bucket}.oss.aliyuncs.com</to>
   </oss>
</url_scheme_mappers>

对于生产用例，建议使用命名集合。这是一个例子


CREATE NAMED COLLECTION creds AS
        access_key_id = '***',
        secret_access_key = '***';
SELECT count(*)
FROM s3(creds, url='https://s3-object-url.csv')

分区写入

分区策略

仅支持 INSERT 查询。

WILDCARD（默认）：将文件路径中的 {_partition_id} 通配符替换为实际的分区键。

HIVE 实现 hive 样式的分区用于读取和写入。它使用以下格式生成文件：<prefix>/<key1=val1/key2=val2...>/<snowflakeid>.<toLower(file_format)>。

HIVE 分区策略示例

INSERT INTO FUNCTION s3(s3_conn, filename='t_03363_function', format=Parquet, partition_strategy='hive') PARTITION BY (year, country) SELECT 2020 as year, 'Russia' as country, 1 as id;

SELECT _path, * FROM s3(s3_conn, filename='t_03363_function/**.parquet');

   ┌─_path──────────────────────────────────────────────────────────────────────┬─id─┬─country─┬─year─┐
1. │ test/t_03363_function/year=2020/country=Russia/7351295896279887872.parquet │  1 │ Russia  │ 2020 │
   └────────────────────────────────────────────────────────────────────────────┴────┴─────────┴──────┘

WILDCARD 分区策略的示例

在键中使用分区 ID 会创建单独的文件

INSERT INTO TABLE FUNCTION
    s3('http://bucket.amazonaws.com/my_bucket/file_{_partition_id}.csv', 'CSV', 'a String, b UInt32, c UInt32')
    PARTITION BY a VALUES ('x', 2, 3), ('x', 4, 5), ('y', 11, 12), ('y', 13, 14), ('z', 21, 22), ('z', 23, 24);

结果，数据被写入三个文件：file_x.csv、file_y.csv 和 file_z.csv。

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

INSERT INTO TABLE FUNCTION
    s3('http://bucket.amazonaws.com/my_bucket_{_partition_id}/file.csv', 'CSV', 'a UInt32, b UInt32, c UInt32')
    PARTITION BY a VALUES (1, 2, 3), (1, 4, 5), (10, 11, 12), (10, 13, 14), (20, 21, 22), (20, 23, 24);

结果，数据被写入不同存储桶中的三个文件：my_bucket_1/file.csv、my_bucket_10/file.csv 和 my_bucket_20/file.csv。

访问公共存储桶

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

SELECT *
FROM s3(
   'https://datasets-documentation.s3.eu-west-3.amazonaws.com/aapl_stock.csv',
   NOSIGN,
   'CSVWithNames'
)
LIMIT 5;

使用 S3 凭据（ClickHouse Cloud）

对于非公共存储桶，用户可以将 aws_access_key_id 和 aws_secret_access_key 传递给该函数。例如

SELECT count() FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/mta/*.tsv', '<KEY>', '<SECRET>','TSVWithNames')

这适用于一次性访问或在可以轻松轮换凭据的情况下。但是，我们不建议将其作为重复访问或凭据敏感情况下的长期解决方案。在这种情况下，我们建议用户依赖基于角色的访问。

ClickHouse Cloud 中 S3 的基于角色的访问记录在此处。

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

SELECT count() FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/mta/*.tsv','CSVWithNames',extra_credentials(role_arn = 'arn:aws:iam::111111111111:role/ClickHouseAccessRole-001'))

可以在此处找到更多示例

使用归档文件

假设我们在 S3 上有几个具有以下 URI 的归档文件

可以使用 :: 提取这些归档文件中的数据。通配符可以同时用于 URL 部分以及 :: 之后的部分（负责归档文件内的文件名）。

SELECT *
FROM s3(
   'https://s3-us-west-1.amazonaws.com/umbrella-static/top-1m-2018-01-1{0..2}.csv.zip :: *.csv'
);

注意

ClickHouse 支持三种归档格式：ZIP TAR 7Z 虽然 ZIP 和 TAR 归档可以从任何受支持的存储位置访问，但只能从安装 ClickHouse 的本地文件系统读取 7Z 归档。

插入数据

请注意，行只能插入到新文件中。没有合并周期或文件拆分操作。一旦文件写入，后续插入将失败。更多详细信息请参阅此处。

虚拟列

_path — 文件路径。类型：LowCardinality(String)。对于归档文件，路径格式为："{path_to_archive}::{path_to_file_inside_archive}"
_file — 文件名。类型：LowCardinality(String)。对于归档文件，显示归档文件内的文件名。
_size — 文件大小（以字节为单位）。类型：Nullable(UInt64)。如果文件大小未知，则值为 NULL。对于归档文件，显示归档文件内文件的未压缩大小。
_time — 文件的最后修改时间。类型：Nullable(DateTime)。如果时间未知，则值为 NULL。

use_hive_partitioning 设置

这是 ClickHouse 在读取时解析 hive 风格分区文件的提示。它对写入没有影响。对于对称的读取和写入，请使用 partition_strategy 参数。

当设置 use_hive_partitioning 为 1 时，ClickHouse 将检测路径中的 Hive 风格分区 (/name=value/)，并允许在查询中使用分区列作为虚拟列。这些虚拟列将与分区路径中的名称相同，但以 _ 开头。

示例

SELECT * FROM s3('s3://data/path/date=*/country=*/code=*/*.parquet') WHERE date > '2020-01-01' AND country = 'Netherlands' AND code = 42;

访问 requester-pays 存储桶

要访问 requester-pays 存储桶，必须在任何请求中传递一个 header x-amz-request-payer = requester。通过将参数 headers('x-amz-request-payer' = 'requester') 传递给 s3 函数来实现。例如

SELECT
    count() AS num_rows,
    uniqExact(_file) AS num_files
FROM s3('https://coiled-datasets-rp.s3.us-east-1.amazonaws.com/1trc/measurements-100*.parquet', 'AWS_ACCESS_KEY_ID', 'AWS_SECRET_ACCESS_KEY', headers('x-amz-request-payer' = 'requester'))

┌───num_rows─┬─num_files─┐
│ 1110000000 │       111 │
└────────────┴───────────┘

1 row in set. Elapsed: 3.089 sec. Processed 1.09 billion rows, 0.00 B (353.55 million rows/s., 0.00 B/s.)
Peak memory usage: 192.27 KiB.

存储设置

s3_truncate_on_insert - 允许在插入文件之前截断文件。默认禁用。
s3_create_new_file_on_insert - 允许在每次插入时创建新文件（如果格式具有后缀）。默认禁用。
s3_skip_empty_files - 允许在读取时跳过空文件。默认启用。

嵌套 Avro 模式

在读取包含嵌套记录的 Avro 文件时，这些记录在不同文件中有所不同（例如，某些文件在嵌套对象内有一个额外的字段），ClickHouse 可能会返回一个错误，例如

记录中的叶子节点数量与元组中的元素数量不匹配...

这是因为 ClickHouse 期望所有嵌套记录结构都与相同的模式匹配。
要处理这种情况，您可以

使用 schema_inference_mode='union' 合并不同的嵌套记录模式，或者
手动对齐您的嵌套结构并启用
use_structure_from_insertion_table_in_table_functions=1.

性能说明

schema_inference_mode='union' 在非常大的 S3 数据集上可能需要更长的时间，因为它必须扫描每个文件以推断模式。

示例

INSERT INTO data_stage
SELECT
    id,
    data
FROM s3('https://bucket-name/*.avro', 'Avro')
SETTINGS schema_inference_mode='union';

## Related \{#related}

- [S3 engine](../../engines/table-engines/integrations/s3.md)
- [Integrating S3 with ClickHouse](/integrations/s3)

语法​

返回值​

示例​

用法​

分区写入​

分区策略​

访问公共存储桶​

使用 S3 凭据（ClickHouse Cloud）​

使用归档文件​

插入数据​

虚拟列​

use_hive_partitioning 设置​

访问 requester-pays 存储桶​

存储设置​

嵌套 Avro 模式​

语法

返回值

示例

用法