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

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

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

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

​

Синтаксис

file([path_to_archive ::] path [,format] [,structure] [,compression])

Для запросов SELECT параметр path также может быть выражением, возвращающим Array(String):

file(['file1.csv', 'file2.csv'], 'CSV', 'column1 UInt32, column2 UInt32')

​

Аргументы

Параметр	Описание
path	Относительный путь к файлу из user_files_path или Array(String) путей в запросах SELECT. В режиме только для чтения поддерживаются следующие глоб-шаблоны: *, ?, {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.

Если аргумент structure опущен, ClickHouse определяет схему по самому формату. Для разных форматов используются разные имена столбцов и типы по умолчанию. Чтобы посмотреть схему для конкретного формата, используйте DESC с табличной функцией format.

Например:

DESC format(LineAsString, 'Hello\nWorld')

┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ line │ String │              │                    │         │                  │                │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

Возвращаемое значение

Таблица для чтения данных из файла или записи данных в файл.

​

Примеры записи в файл

​

Запись в файл в формате 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

​

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

​

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.

​

См. также

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