Табличная функция file

Табличный движок, который предоставляет табличный интерфейс для выполнения SELECT из файлов и INSERT в файлы, аналогично табличной функции s3. Используйте file() при работе с локальными файлами и s3() при работе с бакетами в объектном хранилище, например S3, GCS или MinIO.

Функция file может использоваться в запросах SELECT и INSERT для чтения из файлов или записи в файлы.

Синтаксис

file([путь_к_архиву ::] путь [,формат] [,структура] [,сжатие])

Аргументы

Параметр	Описание
path	Относительный путь к файлу от user_files_path. В режиме только чтения поддерживает следующие шаблоны (globs): *, ?, {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 \{#cat-varlibclickhouseuser_filestesttsv}
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 \{#cat-varlibclickhouseuser_filestest_1tsv}
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

Примеры чтения из файла

SELECT из CSV-файла

Сначала задайте параметр 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');

Пример

Рекурсивно выполните запрос общего числа строк во всех файлах file002, находящихся в любых папках каталога big_dir/:

SELECT count(*) FROM file('big_dir/**/file002', 'CSV', 'name String, value UInt32');

Виртуальные столбцы

• _path — путь к файлу. Тип: LowCardinality(String).
• _file — имя файла. Тип: LowCardinality(String).
• _size — размер файла в байтах. Тип: Nullable(UInt64). Если размер файла неизвестен, значение равно NULL.
• _time — время последнего изменения файла. Тип: Nullable(DateTime). Если время неизвестно, значение равно NULL.

настройка use_hive_partitioning

Когда настройка use_hive_partitioning имеет значение 1, ClickHouse будет обнаруживать секционирование в стиле Hive в пути (/name=value/) и позволит использовать столбцы секций как виртуальные столбцы в запросе. Эти виртуальные столбцы будут иметь те же имена, что и в секционированном пути, но с префиксом _.

Пример

Использование виртуального столбца, создаваемого при секционировании в стиле Hive

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). Значение по умолчанию: pread для clickhouse-server, mmap для clickhouse-local.

См. также

• Виртуальные столбцы
• Переименование файлов после обработки