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])
s3(named_collection[, option=value [,..]])
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'
— 字符串。有关更多信息,请参见 此处。GCSGCS URL 格式如下,因为 Google XML API 的端点与 JSON API 不同
https://storage.googleapis.com/<bucket>/<folder>/<filename(s)>
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
。默认情况下,它将通过文件扩展名自动检测压缩方法。
参数也可以使用 命名集合 传递。在这种情况下,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 会自动解压缩文件。
用法
假设我们在 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 结尾的文件中的行数
SELECT count(*)
FROM s3('https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/{some,another}_prefix/some_file_{1..3}.csv', 'CSV', 'name String, value UInt32')
┌─count()─┐
│ 18 │
└─────────┘
计算这两个目录中所有文件中的总行数
SELECT count(*)
FROM s3('https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/{some,another}_prefix/*', 'CSV', 'name String, value UInt32')
┌─count()─┐
│ 24 │
└─────────┘
如果您的文件列表包含带有前导零的数字范围,请使用带大括号的构造分别为每个数字使用,或使用 ?
。
计算名为 file-000.csv
、file-001.csv
、...、file-999.csv
的文件的总行数
SELECT count(*)
FROM s3('https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/big_prefix/file-{000..999}.csv', 'CSV', 'name String, value 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;
Glob ** 可用于递归目录遍历。考虑以下示例,它将递归地从 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')
分区写入
如果在将数据插入 S3
表时指定 PARTITION BY
表达式,则会为每个分区值创建一个单独的文件。将数据拆分为单独的文件有助于提高读取操作的效率。
示例
- 在密钥中使用分区 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 上有几个存档文件,其 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'
可以使用 :: 从这些存档中提取数据。Globs 可以在 URL 部分以及 :: 后面的部分(用于存档内文件的名称)中使用。
SELECT *
FROM s3(
'https://s3-us-west-1.amazonaws.com/umbrella-static/top-1m-2018-01-1{0..2}.csv.zip :: *.csv'
);
虚拟列
_path
— 文件路径。类型:LowCardinalty(String)
。如果是存档,则显示格式为:“{存档路径}::{存档内文件路径}”的路径。_file
— 文件名。类型:LowCardinalty(String)
。如果是存档,则显示存档内文件的名称。_size
— 文件大小(字节)。类型:Nullable(UInt64)
。如果文件大小未知,则值为NULL
。如果是存档,则显示存档内文件的解压缩文件大小。_time
— 文件的最后修改时间。类型:Nullable(DateTime)
。如果时间未知,则值为NULL
。
Hive 风格的分区
当将 use_hive_partitioning
设置为 1 时,ClickHouse 将在路径中检测到 Hive 风格的分区(/name=value/
),并允许在查询中使用分区列作为虚拟列。这些虚拟列将与分区路径中的列名相同,但以 _
开头。
示例
使用使用 Hive 风格的分区创建的虚拟列
SET use_hive_partitioning = 1;
SELECT * from s3('s3://data/path/date=*/country=*/code=*/*.parquet') where _date > '2020-01-01' and _country = 'Netherlands' and _code = 42;
存储设置
- s3_truncate_on_insert - 允许在插入文件之前清空文件。默认情况下禁用。
- s3_create_new_file_on_insert - 允许在每次插入时创建一个新文件,前提是格式带有后缀。默认情况下禁用。
- s3_skip_empty_files - 允许在读取时跳过空文件。默认情况下禁用。
另请参见