文件
一个表引擎,它为从文件进行 SELECT 和向文件进行 INSERT 提供类似于表的接口,类似于 s3 表函数。在处理本地文件时使用 file()
,在处理 S3、GCS 或 MinIO 等对象存储中的存储桶时使用 s3()
。
file
函数可以在 SELECT
和 INSERT
查询中使用,以从文件读取数据或写入文件。
语法
file([path_to_archive ::] path [,format] [,structure] [,compression])
参数
path
— 相对于 user_files_path 的文件路径。在只读模式下支持以下 通配符:*
、?
、{abc,def}
(其中'abc'
和'def'
为字符串)和{N..M}
(其中N
和M
为数字)。path_to_archive
- 相对于 zip/tar/7z 存档的路径。支持与path
相同的通配符。format
— 文件的 格式。structure
— 表的结构。格式:'column1_name column1_type, column2_name column2_type, ...'
。compression
— 在SELECT
查询中使用时的现有压缩类型,或在INSERT
查询中使用时的所需压缩类型。支持的压缩类型为gz
、br
、xz
、zst
、lz4
和bz2
。
返回值
用于读取或写入文件数据的表。
写入文件的示例
写入 TSV 文件
INSERT INTO TABLE FUNCTION
file('test.tsv', 'TSV', 'column1 UInt32, column2 UInt32, column3 UInt32')
VALUES (1, 2, 3), (3, 2, 1), (1, 3, 2)
结果,数据将写入文件 test.tsv
# cat /var/lib/clickhouse/user_files/test.tsv
1 2 3
3 2 1
1 3 2
将数据写入多个 TSV 文件(分区)
如果在将数据插入到类型为 file()
的表函数时指定 PARTITION BY
表达式,则将为每个分区创建一个单独的文件。将数据拆分为单独的文件有助于提高读取操作的性能。
INSERT INTO TABLE FUNCTION
file('test_{_partition_id}.tsv', 'TSV', 'column1 UInt32, column2 UInt32, column3 UInt32')
PARTITION BY column3
VALUES (1, 2, 3), (3, 2, 1), (1, 3, 2)
结果,数据将写入三个文件:test_1.tsv
、test_2.tsv
和 test_3.tsv
。
# cat /var/lib/clickhouse/user_files/test_1.tsv
3 2 1
# cat /var/lib/clickhouse/user_files/test_2.tsv
1 3 2
# cat /var/lib/clickhouse/user_files/test_3.tsv
1 2 3
从文件读取的示例
从 CSV 文件进行 SELECT
首先,在服务器配置中设置 user_files_path
并准备一个文件 test.csv
$ grep user_files_path /etc/clickhouse-server/config.xml
<user_files_path>/var/lib/clickhouse/user_files/</user_files_path>
$ cat /var/lib/clickhouse/user_files/test.csv
1,2,3
3,2,1
78,43,45
然后,从 test.csv
读取数据到一个表并选择其前两行
SELECT * FROM
file('test.csv', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32')
LIMIT 2;
┌─column1─┬─column2─┬─column3─┐
│ 1 │ 2 │ 3 │
│ 3 │ 2 │ 1 │
└─────────┴─────────┴─────────┘
将数据从文件插入到表中
INSERT INTO FUNCTION
file('test.csv', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32')
VALUES (1, 2, 3), (3, 2, 1);
SELECT * FROM
file('test.csv', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32');
┌─column1─┬─column2─┬─column3─┐
│ 1 │ 2 │ 3 │
│ 3 │ 2 │ 1 │
└─────────┴─────────┴─────────┘
从 table.csv
读取数据,该文件位于 archive1.zip
或/和 archive2.zip
中
SELECT * FROM file('user_files/archives/archive{1..2}.zip :: table.csv');
路径中的通配符
路径可以使用通配符。文件必须匹配整个路径模式,而不仅仅是后缀或前缀。有一个例外,如果路径引用的是现有目录并且没有使用通配符,则会将 *
隐式添加到路径中,以便选择目录中的所有文件。
*
— 代表除/
外的任意多个字符,包括空字符串。?
— 代表任意单个字符。{some_string,another_string,yet_another_one}
— 替换任何字符串'some_string'
、'another_string'
、'yet_another_one'
。这些字符串可以包含/
符号。{N..M}
— 代表任何大于或等于N
且小于或等于M
的数字。**
- 代表文件夹内所有文件,递归。
使用 {}
的构造类似于 remote 和 hdfs 表函数。
示例
假设存在以下具有相对路径的文件
some_dir/some_file_1
some_dir/some_file_2
some_dir/some_file_3
another_dir/some_file_1
another_dir/some_file_2
another_dir/some_file_3
查询所有文件中的总行数
SELECT count(*) FROM file('{some,another}_dir/some_file_{1..3}', 'TSV', 'name String, value UInt32');
另一种实现相同效果的路径表达式
SELECT count(*) FROM file('{some,another}_dir/*', 'TSV', 'name String, value UInt32');
使用隐式 *
查询 some_dir
中的总行数
SELECT count(*) FROM file('some_dir', 'TSV', 'name String, value UInt32');
如果您的文件列表包含以零开头的数字范围,请对每个数字单独使用大括号构造,或使用 ?
。
示例
查询名为 file000
、file001
、...、file999
的所有文件中的总行数
SELECT count(*) FROM file('big_dir/file{0..9}{0..9}{0..9}', 'CSV', 'name String, value UInt32');
示例
递归地查询 big_dir/
目录内所有文件中的总行数
SELECT count(*) FROM file('big_dir/**', 'CSV', 'name String, value UInt32');
示例
递归地查询 big_dir/
目录内所有文件夹中所有名为 file002
的文件中的总行数
SELECT count(*) FROM file('big_dir/**/file002', 'CSV', 'name String, value UInt32');
虚拟列
_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 file('data/path/date=*/country=*/code=*/*.parquet') where _date > '2020-01-01' and _country = 'Netherlands' and _code = 42;
设置
- engine_file_empty_if_not_exists - 允许从不存在的文件中选择空数据。默认情况下禁用。
- engine_file_truncate_on_insert - 允许在插入之前截断文件。默认情况下禁用。
- engine_file_allow_create_multiple_files - 允许在每次插入时创建一个新文件,如果格式有后缀。默认情况下禁用。
- engine_file_skip_empty_files - 允许在读取时跳过空文件。默认情况下禁用。
- storage_file_read_method - 从存储文件中读取数据的方法,其中之一:read、pread、mmap(仅适用于 clickhouse-local)。默认值:clickhouse-server 为
pread
,clickhouse-local 为mmap
。
另请参阅