Перейти к основному содержанию
Перейти к основному содержанию

Движок таблицы S3

Этот движок обеспечивает интеграцию с экосистемой Amazon S3. Он похож на движок HDFS, но поддерживает специфичные для S3 возможности.

Пример​

CREATE TABLE s3_engine_table (name String, value UInt32)
    ENGINE=S3('https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/test-data.csv.gz', 'CSV', 'gzip')
    SETTINGS input_format_with_names_use_header = 0;

INSERT INTO s3_engine_table VALUES ('one', 1), ('two', 2), ('three', 3);

SELECT * FROM s3_engine_table LIMIT 2;

┌─name─┬─value─┐
│ one  │     1 │
│ two  │     2 │
└──────┴───────┘

Создайте таблицу​

CREATE TABLE s3_engine_table (name String, value UInt32)
    ENGINE = S3(path [, NOSIGN | aws_access_key_id, aws_secret_access_key,] format, [compression], [partition_strategy], [partition_columns_in_data_file])
    [PARTITION BY expr]
    [SETTINGS ...]

Параметры движка​

  • path — URL бакета с путем к файлу. В режиме только для чтения поддерживаются следующие шаблоны: *, **, ?, {abc,def} и {N..M}, где N, M — числа, 'abc', 'def' — строки. Дополнительную информацию см. ниже.
  • NOSIGN — Если это ключевое слово указано вместо учетных данных, все запросы не будут подписываться.
  • format — Формат файла.
  • aws_access_key_id, aws_secret_access_key — Долгосрочные учетные данные пользователя аккаунта AWS. Их можно использовать для аутентификации запросов. Параметр необязателен. Если учетные данные не указаны, они берутся из конфигурационного файла. Дополнительную информацию см. в разделе Использование S3 для хранения данных.
  • compression — Тип сжатия. Поддерживаемые значения: none, gzip/gz, brotli/br, xz/LZMA, zstd/zst. Параметр необязателен. По умолчанию тип сжатия автоматически определяется по расширению файла.
  • partition_strategy – Варианты: WILDCARD или HIVE. WILDCARD требует наличия {_partition_id} в пути, который заменяется ключом партиционирования. HIVE не допускает шаблоны, предполагает, что путь — это корень таблицы, и генерирует директории партиций в стиле Hive с идентификаторами Snowflake в качестве имен файлов и форматом файла в качестве расширения. По умолчанию используется WILDCARD.
  • partition_columns_in_data_file — Используется только со стратегией партиционирования HIVE. Указывает ClickHouse, следует ли ожидать, что столбцы партиционирования будут записаны в файл данных. По умолчанию false.
  • storage_class_name — Варианты: STANDARD или INTELLIGENT_TIERING, позволяют указать AWS S3 Intelligent Tiering.

Кэш данных​

Движок таблиц S3 поддерживает кэширование данных на локальном диске. Параметры конфигурации файлового кэша и примеры использования приведены в этом разделе. Кэширование выполняется в зависимости от пути и ETag объекта хранилища, поэтому ClickHouse не будет читать устаревшую версию кэша.

Чтобы включить кэширование, используйте настройки filesystem_cache_name = '<name>' и enable_filesystem_cache = 1.

SELECT *
FROM s3('http://minio:10000/clickhouse//test_3.csv', 'minioadmin', 'minioadminpassword', 'CSV')
SETTINGS filesystem_cache_name = 'cache_for_s3', enable_filesystem_cache = 1;

Существует два способа задать кэш в конфигурационном файле.

  1. Добавьте следующий раздел в конфигурационный файл ClickHouse:
<clickhouse>
    <filesystem_caches>
        <cache_for_s3>
            <path>путь к каталогу кэша</path>
            <max_size>10Gi</max_size>
        </cache_for_s3>
    </filesystem_caches>
</clickhouse>
  1. повторно используйте конфигурацию кеша (и, следовательно, хранилище кеша) из секции ClickHouse storage_configuration, описанной здесь

PARTITION BY​

PARTITION BY — необязательный параметр. В большинстве случаев вам не нужен ключ партиционирования, а если он все же требуется, как правило, не нужен ключ с более мелкой детализацией, чем по месяцам. Партиционирование не ускоряет запросы (в отличие от выражения ORDER BY). Никогда не используйте слишком мелкое партиционирование. Не делите данные на партиции по идентификаторам или именам клиентов (вместо этого сделайте идентификатор или имя клиента первым столбцом в выражении ORDER BY).

Для партиционирования по месяцам используйте выражение toYYYYMM(date_column), где date_column — это столбец с датой типа Date. Имена партиций здесь имеют формат "YYYYMM".

Стратегия партиционирования​

WILDCARD (по умолчанию): заменяет подстановочный шаблон {_partition_id} в пути к файлу фактическим ключом партиционирования. Чтение не поддерживается.

HIVE реализует партиционирование в стиле Hive для чтения и записи. Чтение реализовано с использованием рекурсивного glob-шаблона, что эквивалентно запросу SELECT * FROM s3('table_root/**.parquet'). При записи файлы создаются в следующем формате: <prefix>/<key1=val1/key2=val2...>/<snowflakeid>.<toLower(file_format)>.

Примечание: при использовании стратегии партиционирования HIVE настройка use_hive_partitioning не влияет на поведение.

Пример стратегии партиционирования HIVE:

arthur :) CREATE TABLE t_03363_parquet (year UInt16, country String, counter UInt8)
ENGINE = S3(s3_conn, filename = 't_03363_parquet', format = Parquet, partition_strategy='hive')
PARTITION BY (year, country);

arthur :) INSERT INTO t_03363_parquet VALUES
    (2022, 'USA', 1),
    (2022, 'Canada', 2),
    (2023, 'USA', 3),
    (2023, 'Mexico', 4),
    (2024, 'France', 5),
    (2024, 'Germany', 6),
    (2024, 'Germany', 7),
    (1999, 'Brazil', 8),
    (2100, 'Japan', 9),
    (2024, 'CN', 10),
    (2025, '', 11);

arthur :) select _path, * from t_03363_parquet;

    ┌─_path──────────────────────────────────────────────────────────────────────┬─year─┬─country─┬─counter─┐
 1. │ test/t_03363_parquet/year=2100/country=Japan/7329604473272971264.parquet   │ 2100 │ Japan   │       9 │
 2. │ test/t_03363_parquet/year=2024/country=France/7329604473323302912.parquet  │ 2024 │ France  │       5 │
 3. │ test/t_03363_parquet/year=2022/country=Canada/7329604473314914304.parquet  │ 2022 │ Canada  │       2 │
 4. │ test/t_03363_parquet/year=1999/country=Brazil/7329604473289748480.parquet  │ 1999 │ Brazil  │       8 │
 5. │ test/t_03363_parquet/year=2023/country=Mexico/7329604473293942784.parquet  │ 2023 │ Mexico  │       4 │
 6. │ test/t_03363_parquet/year=2023/country=USA/7329604473319108608.parquet     │ 2023 │ USA     │       3 │
 7. │ test/t_03363_parquet/year=2025/country=/7329604473327497216.parquet        │ 2025 │         │      11 │
 8. │ test/t_03363_parquet/year=2024/country=CN/7329604473310720000.parquet      │ 2024 │ CN      │      10 │
 9. │ test/t_03363_parquet/year=2022/country=USA/7329604473298137088.parquet     │ 2022 │ USA     │       1 │
10. │ test/t_03363_parquet/year=2024/country=Germany/7329604473306525696.parquet │ 2024 │ Germany │       6 │
11. │ test/t_03363_parquet/year=2024/country=Germany/7329604473306525696.parquet │ 2024 │ Germany │       7 │
    └────────────────────────────────────────────────────────────────────────────┴──────┴─────────┴─────────┘

Запросы к секционированным данным​

В этом примере используется рецепт для docker compose, который интегрирует ClickHouse и MinIO. Вы сможете воспроизвести те же запросы, используя S3, заменив endpoint и значения аутентификации.

Обратите внимание, что endpoint S3 в конфигурации ENGINE использует токен-параметр {_partition_id} как часть объекта S3 (имени файла) и что запросы SELECT выполняются по соответствующим именам объектов (например, test_3.csv).

Примечание

Как показано в примере, выполнение запросов к S3-таблицам, разбитым на партиции, в настоящее время напрямую не поддерживается, но может быть реализовано путём запроса отдельных партиций с использованием табличной функции S3.

Основной сценарий использования записи партиционированных данных в S3 — последующая передача этих данных в другую систему ClickHouse (например, при миграции с локальных систем в ClickHouse Cloud). Поскольку наборы данных ClickHouse часто очень велики, а надёжность сетевого соединения иногда оставляет желать лучшего, имеет смысл передавать наборы данных частями, то есть использовать партиционированную запись.

Создайте таблицу​

CREATE TABLE p
(
    `column1` UInt32,
    `column2` UInt32,
    `column3` UInt32
)
ENGINE = S3(
-- highlight-next-line
           'http://minio:10000/clickhouse//test_{_partition_id}.csv',
           'minioadmin',
           'minioadminpassword',
           'CSV')
PARTITION BY column3

Вставка данных​

INSERT INTO p VALUES (1, 2, 3), (3, 2, 1), (78, 43, 45)

Выборка из партиции 3​

Совет

Этот запрос использует табличную функцию s3

SELECT *
FROM s3('http://minio:10000/clickhouse//test_3.csv', 'minioadmin', 'minioadminpassword', 'CSV')

┌─c1─┬─c2─┬─c3─┐
│  1 │  2 │  3 │
└────┴────┴────┘

Выбор данных из партиции 1​

SELECT *
FROM s3('http://minio:10000/clickhouse//test_1.csv', 'minioadmin', 'minioadminpassword', 'CSV')

┌─c1─┬─c2─┬─c3─┐
│  3 │  2 │  1 │
└────┴────┴────┘

Выбор из партиции 45​

SELECT *
FROM s3('http://minio:10000/clickhouse//test_45.csv', 'minioadmin', 'minioadminpassword', 'CSV')

┌─c1─┬─c2─┬─c3─┐
│ 78 │ 43 │ 45 │
└────┴────┴────┘

Ограничение​

Логично попробовать выполнить Select * from p, но, как отмечалось выше, этот запрос завершится с ошибкой; используйте приведённый выше запрос.

SELECT * FROM p

Получено исключение от сервера (версия 23.4.1):
Код: 48. DB::Exception: Получено от localhost:9000. DB::Exception: Чтение из партиционированного хранилища S3 еще не реализовано. (NOT_IMPLEMENTED)

Вставка данных​

Обратите внимание, что строки можно вставлять только в новые файлы. Здесь нет циклов слияния или операций разбиения файлов. После того как файл записан, последующие вставки завершатся с ошибкой. Чтобы этого избежать, вы можете использовать настройки s3_truncate_on_insert и s3_create_new_file_on_insert. Подробнее см. здесь.

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

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

Для получения дополнительной информации о виртуальных столбцах см. здесь`.

Детали реализации​

  • Операции чтения и записи могут выполняться параллельно.

  • Не поддерживаются:

    • Операции ALTER и SELECT...SAMPLE.
    • Индексы.
    • Репликация zero-copy возможна, но не поддерживается.
    Репликация zero-copy не готова для промышленной эксплуатации

    Репликация zero-copy по умолчанию отключена в ClickHouse, начиная с версии 22.8. Не рекомендуется использовать эту функцию в промышленной эксплуатации.

Подстановочные шаблоны в пути​

Аргумент path может задавать несколько файлов, используя подстановочные шаблоны в стиле bash. Для обработки файл должен существовать и полностью совпадать с шаблоном пути. Список файлов определяется во время выполнения SELECT (а не в момент CREATE).

  • * — Заменяет любое количество любых символов, кроме /, включая пустую строку.
  • ** — Заменяет любое количество любых символов, включая /, включая пустую строку.
  • ? — Заменяет ровно один любой символ.
  • {some_string,another_string,yet_another_one} — Заменяет любую из строк 'some_string', 'another_string', 'yet_another_one'.
  • {N..M} — Заменяет любое число в диапазоне от N до M включительно. N и M могут иметь ведущие нули, например 000..078.

Конструкции с {} аналогичны табличной функции remote.

Примечание

Если перечень файлов содержит диапазоны чисел с ведущими нулями, используйте конструкцию с фигурными скобками отдельно для каждой цифры или используйте ?.

Пример с подстановочными шаблонами 1

Создадим таблицу с файлами file-000.csv, file-001.csv, ... , file-999.csv:

CREATE TABLE big_table (name String, value UInt32)
    ENGINE = S3('https://clickhouse-public-datasets.s3.amazonaws.com/my-bucket/my_folder/file-{000..999}.csv', 'CSV');

Пример с подстановочными знаками 2

Предположим, у нас есть несколько файлов в формате CSV со следующими URI в S3:

Есть несколько способов создать таблицу, включающую все шесть файлов:

  1. Указать диапазон суффиксов имён файлов:
CREATE TABLE table_with_range (name String, value UInt32)
    ENGINE = S3('https://clickhouse-public-datasets.s3.amazonaws.com/my-bucket/{some,another}_folder/some_file_{1..3}', 'CSV');
  1. Возьмите все файлы с префиксом some_file_ (в обеих папках не должно быть каких-либо лишних файлов с таким префиксом):
CREATE TABLE table_with_question_mark (name String, value UInt32)
    ENGINE = S3('https://clickhouse-public-datasets.s3.amazonaws.com/my-bucket/{some,another}_folder/some_file_?', 'CSV');
  1. Возьмите все файлы из обеих папок (все файлы должны соответствовать формату и схеме, описанным в запросе):
CREATE TABLE table_with_asterisk (name String, value UInt32)
    ENGINE = S3('https://clickhouse-public-datasets.s3.amazonaws.com/my-bucket/{some,another}_folder/*', 'CSV');

Настройки хранилища​

  • s3_truncate_on_insert - позволяет обрезать файл перед вставкой данных в него. По умолчанию отключено.
  • s3_create_new_file_on_insert - позволяет создавать новый файл при каждой вставке, если формат имеет суффикс. По умолчанию отключено.
  • s3_skip_empty_files - позволяет пропускать пустые файлы при чтении. По умолчанию включено.

Настройки, связанные с S3​

Следующие настройки могут быть заданы перед выполнением запроса или помещены в файл конфигурации.

  • s3_max_single_part_upload_size — Максимальный размер объекта для загрузки в S3 одним запросом (single-part upload). Значение по умолчанию — 32 МБ.
  • s3_min_upload_part_size — Минимальный размер части, загружаемой при многокомпонентной загрузке (S3 Multipart upload). Значение по умолчанию — 16 МБ.
  • s3_max_redirects — Максимальное количество переходов при перенаправлениях S3. Значение по умолчанию — 10.
  • s3_single_read_retries — Максимальное количество попыток однократного чтения. Значение по умолчанию — 4.
  • s3_max_put_rps — Максимальная частота запросов PUT в секунду до начала ограничения (throttling). Значение по умолчанию — 0 (без ограничений).
  • s3_max_put_burst — Максимальное количество запросов, которые могут быть выполнены одновременно до достижения лимита запросов в секунду. По умолчанию (значение 0) равно s3_max_put_rps.
  • s3_max_get_rps — Максимальная частота запросов GET в секунду до начала ограничения. Значение по умолчанию — 0 (без ограничений).
  • s3_max_get_burst — Максимальное количество запросов, которые могут быть выполнены одновременно до достижения лимита запросов в секунду. По умолчанию (значение 0) равно s3_max_get_rps.
  • s3_upload_part_size_multiply_factor — Умножать s3_min_upload_part_size на этот коэффициент каждый раз, когда было загружено s3_multiply_parts_count_threshold частей из одной операции записи в S3. Значение по умолчанию — 2.
  • s3_upload_part_size_multiply_parts_count_threshold — Каждый раз, когда в S3 загружено такое количество частей, s3_min_upload_part_size умножается на s3_upload_part_size_multiply_factor. Значение по умолчанию — 500.
  • s3_max_inflight_parts_for_one_file — Ограничивает количество запросов PUT, которые могут выполняться параллельно для одного объекта. Рекомендуется ограничить это значение. Значение 0 означает отсутствие ограничений. Значение по умолчанию — 20. Каждая активная (in-flight) часть имеет буфер размером s3_min_upload_part_size для первых s3_upload_part_size_multiply_factor частей и больше, когда файл достаточно большой, см. upload_part_size_multiply_factor. При настройках по умолчанию один загружаемый файл потребляет не более 320 МБ памяти для файла размером менее 8 ГБ. Для более крупных файлов потребление больше.

Соображения безопасности: если злоумышленник может указывать произвольные URL-адреса S3, параметр s3_max_redirects должен быть установлен в ноль, чтобы избежать атак SSRF, либо в конфигурации сервера должен быть задан remote_host_filter.

Параметры для отдельных endpoint​

Следующие параметры могут быть указаны в конфигурационном файле для заданного endpoint (который будет сопоставляться по точному префиксу URL):

  • endpoint — Задает префикс endpoint. Обязательный параметр.
  • access_key_id и secret_access_key — Задают учетные данные для использования с заданным endpoint. Необязательные параметры.
  • use_environment_credentials — Если установлено в true, клиент S3 попытается получить учетные данные из переменных окружения и метаданных Amazon EC2 для заданного endpoint. Необязательный параметр, значение по умолчанию — false.
  • region — Задает имя региона S3. Необязательный параметр.
  • use_insecure_imds_request — Если установлено в true, клиент S3 будет использовать небезопасный IMDS-запрос при получении учетных данных из метаданных Amazon EC2. Необязательный параметр, значение по умолчанию — false.
  • expiration_window_seconds — Дополнительный интервал при проверке, истекли ли учетные данные с ограниченным сроком действия. Необязательный параметр, значение по умолчанию — 120.
  • no_sign_request - Игнорирует все учетные данные, чтобы запросы не подписывались. Полезно для доступа к публичным бакетам.
  • header — Добавляет указанный HTTP-заголовок к запросу к заданному endpoint. Необязательный параметр, может быть указан несколько раз.
  • access_header - Добавляет указанный HTTP-заголовок к запросу к заданному endpoint в случаях, когда нет других учетных данных из какого-либо другого источника.
  • server_side_encryption_customer_key_base64 — Если указан, будут установлены необходимые заголовки для доступа к объектам S3 с шифрованием SSE-C. Необязательный параметр.
  • server_side_encryption_kms_key_id - Если указан, будут установлены необходимые заголовки для доступа к объектам S3 с шифрованием SSE-KMS. Если указана пустая строка, будет использован управляемый AWS ключ S3. Необязательный параметр.
  • server_side_encryption_kms_encryption_context - Если указан вместе с server_side_encryption_kms_key_id, будет установлен соответствующий заголовок контекста шифрования для SSE-KMS. Необязательный параметр.
  • server_side_encryption_kms_bucket_key_enabled - Если указан вместе с server_side_encryption_kms_key_id, будет установлен заголовок для включения ключей бакета S3 для SSE-KMS. Необязательный параметр, может быть true или false, по умолчанию не задан (соответствует настройке на уровне бакета).
  • max_single_read_retries — Максимальное количество попыток при одном чтении. Значение по умолчанию — 4. Необязательный параметр.
  • max_put_rps, max_put_burst, max_get_rps и max_get_burst - Настройки ограничения скорости (см. описание выше), применяемые к конкретному endpoint вместо настроек на запрос. Необязательные параметры.

Пример:

<s3>
    <endpoint-name>
        <endpoint>https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/</endpoint>
        <!-- <access_key_id>ACCESS_KEY_ID</access_key_id> -->
        <!-- <secret_access_key>SECRET_ACCESS_KEY</secret_access_key> -->
        <!-- <region>us-west-1</region> -->
        <!-- <use_environment_credentials>false</use_environment_credentials> -->
        <!-- <use_insecure_imds_request>false</use_insecure_imds_request> -->
        <!-- <expiration_window_seconds>120</expiration_window_seconds> -->
        <!-- <no_sign_request>false</no_sign_request> -->
        <!-- <header>Authorization: Bearer SOME-TOKEN</header> -->
        <!-- <server_side_encryption_customer_key_base64>BASE64-ENCODED-KEY</server_side_encryption_customer_key_base64> -->
        <!-- <server_side_encryption_kms_key_id>KMS_KEY_ID</server_side_encryption_kms_key_id> -->
        <!-- <server_side_encryption_kms_encryption_context>KMS_ENCRYPTION_CONTEXT</server_side_encryption_kms_encryption_context> -->
        <!-- <server_side_encryption_kms_bucket_key_enabled>true</server_side_encryption_kms_bucket_key_enabled> -->
        <!-- <max_single_read_retries>4</max_single_read_retries> -->
    </endpoint-name>
</s3>

Работа с архивами​

Предположим, что у нас есть несколько файлов-архивов со следующими URI в S3:

Извлечение данных из этих архивов можно выполнять с помощью синтаксиса ::. Глоб-шаблоны (globs) могут использоваться как в части URL, так и в части после :: (отвечающей за имя файла внутри архива).

SELECT *
FROM s3(
   'https://s3-us-west-1.amazonaws.com/umbrella-static/top-1m-2018-01-1{0..2}.csv.zip :: *.csv'
);
Примечание

ClickHouse поддерживает три формата архивов: ZIP TAR 7Z Архивы ZIP и TAR могут считываться из любого поддерживаемого хранилища, тогда как архивы 7Z читаются только из локальной файловой системы, на которой установлен ClickHouse.

Доступ к общедоступным бакетам​

ClickHouse пытается получить учетные данные из множества различных источников. Иногда это может приводить к проблемам при доступе к некоторым общедоступным бакетам, в результате чего клиент возвращает код ошибки 403. Этой проблемы можно избежать, используя ключевое слово NOSIGN, которое заставляет клиент игнорировать все учетные данные и не подписывать запросы.

CREATE TABLE big_table (name String, value UInt32)
    ENGINE = S3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/aapl_stock.csv', NOSIGN, 'CSVWithNames');

Оптимизация производительности​

Подробнее об оптимизации производительности функции s3 см. в нашем подробном руководстве.

См. также​