Импорт и экспорт в файлы

Елена Дворядкина

Импорт данных из файлов

ADQM поддерживает несколько способов импорта данных из файлов в таблицы.

FROM INFILE

Чтобы вставить в таблицу данные из локального файла или нескольких файлов сразу (поддерживаются в том числе сжатые файлы), можно использовать выражение FROM INFILE при выполнении запроса INSERT INTO в клиенте командной строки clickhouse-client. При этом файлы должны быть расположены в папке, из которой запущен clickhouse-client. Базовый синтаксис запроса:

INSERT INTO <table_name> [(<column_list>)] FROM INFILE '<file_name.ext>' [COMPRESSION '<compr_type>'] [FORMAT <input_format>];

В запросе с выражением FROM INFILE используются следующие аргументы.

<file_name.ext>

Имя файла с расширением, из которого будут загружаться данные в таблицу ADQM

<compr_type>

Метод сжатия файла. Возможные значения: none, gzip, deflate, br, xz, zstd, lz4, bz2. По умолчанию метод сжатия определяется по расширению файла

<input_format>

Формат импортируемых данных. Доступные для импорта данных форматы перечислены в статье Formats for Input and Output Data документации ClickHouse. По умолчанию формат автоматически определяется по расширению файла.

Примеры использования различных форматов для импорта данных JSON в зависимости от структуры входящих данных приведены в статье Тип данных JSON

Пример

  1. Создайте два локальных тестовых файла input_1.csv и input_2.csv с данными для последующей вставки в таблицы ADQM:

    1,"aaa",100
2,"bbb",200
3,"ccc",300
    4,"ddd",400
5,"eee",500
6,"fff",600

    Запустите консольный клиент clickhouse-client из папки, где находятся эти файлы.

  2. Создайте таблицу с соответствующей структурой:

    CREATE TABLE test_table (a Int32, b String, c Int32) ENGINE = MergeTree ORDER BY a;

  3. Выполните запрос для импорта данных одного из файлов в созданную таблицу:

    INSERT INTO test_table FROM INFILE 'input_1.csv';

  4. Проверьте, что данные загрузились в таблицу:

    SELECT * FROM test_table;
    ┌─a─┬─b───┬───c─┐
│ 1 │ aaa │ 100 │
│ 2 │ bbb │ 200 │
│ 3 │ ccc │ 300 │
└───┴─────┴─────┘

  5. Вставьте в таблицу данные из двух файлов сразу, указав input_*.csv в качестве имени файла:

    INSERT INTO test_table FROM INFILE 'input_*.csv';

  6. Проверьте результат импорта:

    SELECT * FROM test_table;
    ┌─a─┬─b───┬───c─┐
│ 1 │ aaa │ 100 │
│ 2 │ bbb │ 200 │
│ 3 │ ccc │ 300 │
│ 4 │ ddd │ 400 │
│ 5 │ eee │ 500 │
│ 6 │ fff │ 600 │
└───┴─────┴─────┘
┌─a─┬─b───┬───c─┐
│ 1 │ aaa │ 100 │
│ 2 │ bbb │ 200 │
│ 3 │ ccc │ 300 │
└───┴─────┴─────┘

Табличный движок File

Загружать данные в ADQM из файла можно используя табличный движок File, который хранит данные в файле. Данные, отредактированные непосредственно в файле на диске, автоматически обновляются в таблице типа File при выполнении запроса SELECT. При создании таблицы на основе этого движка для импорта данных из файла в качестве аргумента должен быть указан формат, поддерживаемый для входных данных — см. форматы в колонке Input таблицы Formats for Input and Output Data. Особенностью этого движка также является то, что путь к файлу для хранения данных указать явно нельзя — используется каталог, установленный через параметр Data path на странице конфигурации сервиса ADQMDB (по умолчанию /var/lib/clickhouse/).

Пример

  1. Создайте таблицу file_table с использованием движка File:

    CREATE TABLE file_table (name String, value UInt32) ENGINE=File(TabSeparated);

    ADQM создаст каталог /var/lib/clickhouse/data/default/file_table.

  2. Создайте вручную файл /var/lib/clickhouse/data/default/file_table/data.TabSeparated и заполните его тестовыми данными:

    $ sudo cat > /var/lib/clickhouse/data/default/file_table/data.TabSeparated
a     1
b     2

    Чтобы в дальнейшем иметь возможность экспортировать данные из таблицы в этот файл, назначьте ему владельца clickhouse следующей командой:

    $ sudo chown clickhouse:clickhouse /var/lib/clickhouse/data/default/file_table/data.TabSeparated

  3. Запросите данные из таблицы file_table:

    SELECT * FROM test_table;
    ┌─name─┬─value─┐
│ a    │     1 │
│ b    │     2 │
└──────┴───────┘

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

Для вставки данных из файла в таблицу можно также использовать табличную функцию file — см. пример в статье Табличные функции.

Перенаправление данных в командой строке

Импортировать данные из файла в таблицу можно напрямую в командой строке clickhouse-client, например:

$ clickhouse-client -q "INSERT INTO test_table FORMAT CSV" < input_2.csv

Экспорт данных в файлы

Экспортировать данные из ADQM в файлы можно перечисленными ниже способами.

INTO OUTFILE

Чтобы сохранить результаты запроса SELECT в файл, при выполнении этого запроса в клиенте командной строки clickhouse-client можно использовать выражение INTO OUTFILE. Базовый синтаксис:

SELECT <expr_list>
[FROM <table_name>]
...
INTO OUTFILE '<file_name.ext>' [AND STDOUT] [COMPRESSION '<compr_type>' [LEVEL <compr_level>]]
[FORMAT <output_format>];

В запросе с выражением INTO OUTFILE используются следующие аргументы.

<file_name.ext>

Имя файла с расширением, в который будут сохраняться результаты запроса SELECT. Файл будет создан в папке, из которой запущен clickhouse-client. Если файл с указанным именем уже существует, выполнение запроса завершится ошибкой

<compr_type>

Метод сжатия. Возможные значения: none, gzip, deflate, br, xz, zstd, lz4, bz2. По умолчанию метод сжатия определяется по расширению файла

<compr_level>

Уровень сжатия для указанного метода. Возможные значения — положительные целые числа в следующих диапазонах:

  • [1, 12] — для метода lz4;

  • [1, 22] — для метода zstd;

  • [1, 9] — для остальных методов сжатия.

<output_format>

Формат экспортируемых данных. Доступные для экспорта данных форматы перечислены в статье Formats for Input and Output Data документации ClickHouse. По умолчанию формат определяется по расширению файла, или используется формат TabSeparated, если формат не удалось установить по расширению файла.

Примеры использования различных форматов для вывода данных JSON представлены в статье Тип данных JSON

При использовании в запросе выражения AND STDOUT сохраняемые в файл данные также будут выведены на экран. Если применяется сжатие, данные на экране отобразятся в виде простого текста.

Пример запроса SELECT …​ INTO OUTFILE:

SELECT * FROM test_table INTO OUTFILE 'output.csv';

Табличный движок File

Еще один способ выгрузить данные из ADQM в файл — использовать табличный движок File. При создании таблицы с использованием этого движка для экспорта данных в файл (осуществляется при выполнении запросов INSERT) в качестве аргумента должен быть указан формат, поддерживаемый для выходных данных — см. форматы в колонке Output таблицы в статье Formats for Input and Output Data.

Пример

  1. Создайте таблицу file_table_out на движке File:

    CREATE TABLE file_table_out (name String, value UInt32) ENGINE=File(TabSeparated);

    ADQM создаст каталог /var/lib/clickhouse/data/default/file_table_out, где /var/lib/clickhouse/ — каталог для хранения данных ADQM, который можно изменить через параметр Data path на странице конфигурации сервиса ADQMDB.

  2. Заполните таблицу тестовыми данными:

    INSERT INTO file_table_out VALUES('a', 1), ('b', 2), ('c', 3);

  3. Убедитесь, что в каталоге /var/lib/clickhouse/data/default/file_table_out создан файл data.TabSeparated, который содержит данные из таблицы file_table_out:

    a     1
b     2
c     3

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

Для экспорта данных из ADQM в файл можно также использовать табличную функцию file — см. пример в статье Табличные функции.

Перенаправление данных в командой строке

Следующий запрос создаст файл redirected_output.csv в папке, из которой запущен clickhouse-client, и запишет в него результаты выполнения указанного запроса SELECT (если такой файл уже существует, данные будут перезаписаны):

$ clickhouse-client --query "SELECT * from test_table" --format CSV > redirected_output.csv
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней