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

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 to cache directory</path>
            <max_size>10Gi</max_size>
        </cache_for_s3>
    </filesystem_caches>
</clickhouse>
  1. повторно используйте конфигурацию кэша (а значит, и хранилище кэша) из раздела storage_configuration ClickHouse, описанном здесь.

PARTITION BY​

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

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

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

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

HIVE реализует партиционирование в стиле Hive для чтения и записи. Чтение реализовано с использованием рекурсивного глобального шаблона, это эквивалентно 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, заменив значения конечной точки и аутентификации.

Обратите внимание, что конечная точка 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

Received exception from server (version 23.4.1):
Code: 48. DB::Exception: Received from localhost:9000. DB::Exception: Reading from a partitioned S3 storage is not implemented yet. (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.

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

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

  • Чтения и записи могут быть параллельными.

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

    Репликация без копирования не готова к производству

    По умолчанию репликация без копирования отключена в версии 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. Значение по умолчанию — 32Mb.
  • s3_min_upload_part_size — Минимальный размер части для загрузки во время многопоточной загрузки в S3 Multipart upload. Значение по умолчанию — 16Mb.
  • s3_max_redirects — Максимальное количество переходов перенаправления S3. Значение по умолчанию — 10.
  • s3_single_read_retries — Максимальное количество попыток во время одного чтения. Значение по умолчанию — 4.
  • s3_max_put_rps — Максимальное количество PUT-запросов в секунду до начала ограничения. Значение по умолчанию — 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 загружалась s3_multiply_parts_count_threshold частей из одной записи. Значение по умолчанию — 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 - Ограничивает количество запросов на загрузку, которые могут выполняться одновременно для одного объекта. Это число должно быть ограничено. Значение 0 означает неограниченно. Значение по умолчанию — 20. Каждая часть в процессе имеет буфер размером s3_min_upload_part_size для первых s3_upload_part_size_multiply_factor частей и больше, когда файл достаточно велик, см. upload_part_size_multiply_factor. С настройками по умолчанию один загруженный файл потребляет не более 320Mb для файла, который меньше 8G. Потребление больше для больших файлов.

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

Настройки на основе конечной точки​

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

  • endpoint — Указывает префикс конечной точки. Обязательный.
  • access_key_id и secret_access_key — Указывают учетные данные для использования с данной конечной точкой. Необязательный.
  • use_environment_credentials — Если установлено в true, клиент S3 попытается получить учетные данные из переменных окружения и метаданных Amazon EC2 для данной конечной точки. Необязательный, значение по умолчанию — false.
  • region — Указывает название региона S3. Необязательный.
  • use_insecure_imds_request — Если установлено в true, клиент S3 будет использовать небезопасный IMDS-запрос при получении учетных данных из метаданных Amazon EC2. Необязательный, значение по умолчанию — false.
  • expiration_window_seconds — Период грации для проверки, истекли ли учетные данные, основанные на времени действия. Необязательный, значение по умолчанию — 120.
  • no_sign_request - Игнорирует все учетные данные, поэтому запросы не подписываются. Полезно для доступа к публичным корзинам.
  • header — Добавляет указанный HTTP-заголовок к запросу к данной конечной точке. Необязательный, может быть указан несколько раз.
  • access_header - Добавляет указанный HTTP-заголовок к запросу к данной конечной точке в случаях, когда нет других учетных данных из другого источника.
  • 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 - Настройки ограничения (см. описание выше), которые нужно использовать для конкретной конечной точки вместо ограничения по запросу. Необязательный.

Пример:

<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:

Извлечение данных из этих архивов возможно с использованием ::. Глобальные шаблоны могут использоваться как в части 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 смотрите наш подробный гид.

См. также​