Табличная функция s3 - ClickHouse Documentation

Предоставляет интерфейс, подобный таблице, для выборки/вставки файлов в Amazon S3 и Google Cloud Storage. Эта табличная функция похожа на функцию hdfs, но предоставляет возможности, специфичные для S3. Если в вашем кластере несколько реплик, вместо этого можно использовать функцию s3Cluster, чтобы распараллелить вставку. При использовании s3 table function с INSERT INTO...SELECT данные считываются и вставляются в потоковом режиме. В памяти одновременно находится лишь несколько блоков данных, пока они непрерывно считываются из S3 и записываются в целевую таблицу.

Синтаксис

s3(url [, NOSIGN | access_key_id, secret_access_key, [session_token]] [,format] [,structure] [,compression_method],[,headers], [,extra_credentials], [,partition_strategy], [,partition_columns_in_data_file])
s3(named_collection[, option=value [,..]])

GCSТабличная функция S3 интегрируется с Google Cloud Storage через XML API GCS и HMAC-ключи. Подробнее о конечной точке и HMAC см. в документации Google по совместимости.Для GCS подставьте свои HMAC-ключ и HMAC-секрет там, где указаны access_key_id и secret_access_key.

Параметры Табличная функция s3 поддерживает следующие простые параметры:

Параметр	Описание
`url`	URL бакета с путём к файлу. В режиме только для чтения поддерживаются следующие подстановочные шаблоны: ``, `*`, `?`, `{abc,def}` и `{N..M}`, где `N`, `M` — числа, `'abc'`, `'def'` — строки. Подробнее см. здесь.
`NOSIGN`	Если указать это ключевое слово вместо учетных данных, запросы не будут подписываться.
`access_key_id` and `secret_access_key`	Ключи, задающие учетные данные для использования с указанной конечной точкой. Необязательно.
`session_token`	Токен сеанса для использования с указанными ключами. Необязателен при передаче ключей.
`format`	Формат файла.
`structure`	Структура таблицы. Формат: `'column1_name column1_type, column2_name column2_type, ...'`.
`compression_method`	Параметр необязателен. Поддерживаемые значения: `none`, `gzip` или `gz`, `brotli` или `br`, `xz` или `LZMA`, `zstd` или `zst`. По умолчанию метод сжатия определяется автоматически по расширению файла.
`headers`	Параметр необязателен. Позволяет передавать заголовки в запросе S3. Передавайте в формате `headers(key=value)`, например `headers('x-amz-request-payer' = 'requester')`.
`partition_strategy`	Параметр необязателен. Поддерживаемые значения: `wildcard` или `hive`. Для `wildcard` в пути требуется `{_partition_id}`, который заменяется ключом партиционирования. `hive` не допускает подстановочные шаблоны, предполагает, что путь является корнем таблицы, и создаёт директории с партициями в стиле Hive, где в качестве имён файлов используются Snowflake ID, а в качестве расширения — формат файла. По умолчанию используется настройка `file_like_engine_default_partition_strategy` (`wildcard` в настройках `compatibility` старше `26.6`, в противном случае — `hive`).
`partition_columns_in_data_file`	Параметр необязателен. Используется только со стратегией партиционирования `hive`. Указывает ClickHouse, следует ли ожидать, что столбцы партиции будут записаны в файл данных. Значение по умолчанию — `false`.
`extra_credentials`	Параметр необязателен. Используется для передачи `role_arn` для ролевого доступа в ClickHouse Cloud. Шаги настройки см. в Secure S3.
`storage_class_name`	Параметр необязателен. Поддерживаемые значения: `STANDARD` или `INTELLIGENT_TIERING`. Позволяет указать AWS S3 Intelligent Tiering. По умолчанию — `STANDARD`.

GCSURL GCS имеет следующий формат, так как конечная точка Google XML API отличается от JSON API:

  https://storage.googleapis.com/<bucket>/<folder>/<filename(s)>

а не ~~https://storage.cloud.google.com~~.

Аргументы также можно передавать с помощью именованных коллекций. В этом случае url, access_key_id, secret_access_key, format, structure, compression_method работают так же, и поддерживаются некоторые дополнительные параметры:

Аргумент	Описание
`filename`	добавляется к URL, если указан.
`use_environment_credentials`	включен по умолчанию; позволяет передавать дополнительные параметры через переменные окружения `AWS_CONTAINER_CREDENTIALS_RELATIVE_URI`, `AWS_CONTAINER_CREDENTIALS_FULL_URI`, `AWS_CONTAINER_AUTHORIZATION_TOKEN`, `AWS_EC2_METADATA_DISABLED`.
`no_sign_request`	по умолчанию отключен.
`expiration_window_seconds`	значение по умолчанию — 120.

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

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

Примеры

Выбор первых 5 строк из таблицы в файле S3 https://datasets-documentation.s3.eu-west-3.amazonaws.com/aapl_stock.csv:

SELECT *
FROM s3(
   'https://datasets-documentation.s3.eu-west-3.amazonaws.com/aapl_stock.csv',
   'CSVWithNames'
)
LIMIT 5;

┌───────Date─┬────Open─┬────High─┬─────Low─┬───Close─┬───Volume─┬─OpenInt─┐
│ 1984-09-07 │ 0.42388 │ 0.42902 │ 0.41874 │ 0.42388 │ 23220030 │       0 │
│ 1984-09-10 │ 0.42388 │ 0.42516 │ 0.41366 │ 0.42134 │ 18022532 │       0 │
│ 1984-09-11 │ 0.42516 │ 0.43668 │ 0.42516 │ 0.42902 │ 42498199 │       0 │
│ 1984-09-12 │ 0.42902 │ 0.43157 │ 0.41618 │ 0.41618 │ 37125801 │       0 │
│ 1984-09-13 │ 0.43927 │ 0.44052 │ 0.43927 │ 0.43927 │ 57822062 │       0 │
└────────────┴─────────┴─────────┴─────────┴─────────┴──────────┴─────────┘

ClickHouse использует расширения файлов, чтобы определить формат данных. Например, предыдущую команду можно было выполнить и без CSVWithNames:

SELECT *
FROM s3(
   'https://datasets-documentation.s3.eu-west-3.amazonaws.com/aapl_stock.csv'
)
LIMIT 5;

ClickHouse также может определить метод сжатия файла. Например, если файл сжат и имеет расширение .csv.gz, ClickHouse автоматически его распакует.

Файлы Parquet с именами вроде *.parquet.snappy или *.parquet.zstd могут сбить ClickHouse с толку и вызвать ошибки TOO_LARGE_COMPRESSED_BLOCK или ZSTD_DECODER_FAILED. Это связано с тем, что ClickHouse попытается прочитать весь файл как данные, сжатые Snappy или ZSTD, хотя в Parquet сжатие применяется на уровне групп строк и столбцов.Метаданные Parquet уже содержат информацию о сжатии для каждого столбца, поэтому расширение файла здесь избыточно. В таких случаях можно просто использовать compression_method = 'none':

SELECT *
FROM s3(
  'https://<my-bucket>.s3.<my-region>.amazonaws.com/path/to/my-data.parquet.snappy',
  compression_format = 'none'
);

Использование

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

Подсчитайте количество строк в файлах, названия которых оканчиваются на числа от 1 до 3:

SELECT count(*)
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/my-test-bucket-768/{some,another}_prefix/some_file_{1..3}.csv', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32')

┌─count()─┐
│      18 │
└─────────┘

Подсчитайте общее количество строк во всех файлах в этих двух каталогах:

SELECT count(*)
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/my-test-bucket-768/{some,another}_prefix/*', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32')

┌─count()─┐
│      24 │
└─────────┘

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

Подсчитайте общее количество строк в файлах с именами file-000.csv, file-001.csv, … , file-999.csv:

SELECT count(*)
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/my-test-bucket-768/big_prefix/file-{000..999}.csv', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32');

┌─count()─┐
│      12 │
└─────────┘

Вставьте данные в файл test-data.csv.gz:

INSERT INTO FUNCTION s3('https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/test-data.csv.gz', 'CSV', 'name String, value UInt32', 'gzip')
VALUES ('test-data', 1), ('test-data-2', 2);

Вставьте данные из существующей таблицы в файл test-data.csv.gz:

INSERT INTO FUNCTION s3('https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/test-data.csv.gz', 'CSV', 'name String, value UInt32', 'gzip')
SELECT name, value FROM existing_table;

Glob ** можно использовать для рекурсивного обхода каталогов. Рассмотрим пример ниже — он рекурсивно извлечёт все файлы из каталога my-test-bucket-768:

SELECT * FROM s3('https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/**', 'CSV', 'name String, value UInt32', 'gzip');

Следующий запрос рекурсивно извлекает данные из всех файлов test-data.csv.gz в любой папке внутри каталога my-test-bucket:

SELECT * FROM s3('https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/**/test-data.csv.gz', 'CSV', 'name String, value UInt32', 'gzip');

Примечание. В конфигурационном файле сервера можно указать пользовательские URL-маппер. Пример:

SELECT * FROM s3('s3://clickhouse-public-datasets/my-test-bucket-768/**/test-data.csv.gz', 'CSV', 'name String, value UInt32', 'gzip');

URL 's3://clickhouse-public-datasets/my-test-bucket-768/**/test-data.csv.gz' будет преобразован в 'http://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/**/test-data.csv.gz' Пользовательский маппер можно добавить в config.xml:

<url_scheme_mappers>
   <s3>
      <to>https://{bucket}.s3.amazonaws.com</to>
   </s3>
   <gs>
      <to>https://{bucket}.storage.googleapis.com</to>
   </gs>
   <oss>
      <to>https://{bucket}.oss.aliyuncs.com</to>
   </oss>
</url_scheme_mappers>

Для использования в производственной среде рекомендуется использовать именованные коллекции. Вот пример:

CREATE NAMED COLLECTION creds AS
        access_key_id = '***',
        secret_access_key = '***';
SELECT count(*)
FROM s3(creds, url='https://s3-object-url.csv')

Запись с партиционированием

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

Поддерживается только для запросов INSERT. wildcard: заменяет подстановочный шаблон {_partition_id} в пути к файлу на фактическое значение ключа партиционирования. Выбирается по умолчанию только при значении compatibility ниже 26.6; в противном случае значением по умолчанию является hive (см. настройку file_like_engine_default_partition_strategy). hive реализует секционирование в стиле Hive для чтения и записи. Файлы создаются в следующем формате: <prefix>/<key1=val1/key2=val2...>/<snowflakeid>.<toLower(file_format)>. Пример стратегии партиционирования hive

INSERT INTO FUNCTION s3(s3_conn, filename='t_03363_function', format=Parquet, partition_strategy='hive') PARTITION BY (year, country) SELECT 2020 as year, 'Russia' as country, 1 as id;

SELECT _path, * FROM s3(s3_conn, filename='t_03363_function/**.parquet');

   ┌─_path──────────────────────────────────────────────────────────────────────┬─id─┬─country─┬─year─┐
1. │ test/t_03363_function/year=2020/country=Russia/7351295896279887872.parquet │  1 │ Russia  │ 2020 │
   └────────────────────────────────────────────────────────────────────────────┴────┴─────────┴──────┘

Примеры стратегии партиционирования wildcard

Использование идентификатора партиции в ключе создаёт отдельные файлы:

INSERT INTO TABLE FUNCTION
    s3('http://bucket.amazonaws.com/my_bucket/file_{_partition_id}.csv', 'CSV', 'a String, b UInt32, c UInt32')
    PARTITION BY a VALUES ('x', 2, 3), ('x', 4, 5), ('y', 11, 12), ('y', 13, 14), ('z', 21, 22), ('z', 23, 24);

В результате данные записываются в три файла: file_x.csv, file_y.csv и file_z.csv.

Использование идентификатора партиции в имени бакета приводит к созданию файлов в разных бакетах:

INSERT INTO TABLE FUNCTION
    s3('http://bucket.amazonaws.com/my_bucket_{_partition_id}/file.csv', 'CSV', 'a UInt32, b UInt32, c UInt32')
    PARTITION BY a VALUES (1, 2, 3), (1, 4, 5), (10, 11, 12), (10, 13, 14), (20, 21, 22), (20, 23, 24);

В результате данные записываются в три файла в разных бакетах: my_bucket_1/file.csv, my_bucket_10/file.csv и my_bucket_20/file.csv.

Доступ к публичным бакетам

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

SELECT *
FROM s3(
   'https://datasets-documentation.s3.eu-west-3.amazonaws.com/aapl_stock.csv',
   NOSIGN,
   'CSVWithNames'
)
LIMIT 5;

Использование учетных данных S3 (ClickHouse Cloud)

Для закрытых бакетов можно передать в функцию aws_access_key_id и aws_secret_access_key. Например:

SELECT count() FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/mta/*.tsv', '<KEY>', '<SECRET>','TSVWithNames')

Это подходит для разового доступа или для случаев, когда учётные данные можно легко сменить. Однако в качестве долгосрочного решения для регулярного доступа или при работе с конфиденциальными учётными данными это не рекомендуется. В таких случаях мы рекомендуем использовать ролевой доступ. Ролевой доступ к S3 в ClickHouse Cloud описан здесь. После настройки roleARN можно передать в функцию s3 через параметр extra_credentials. Например:

SELECT count() FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/mta/*.tsv','CSVWithNames',extra_credentials(role_arn = 'arn:aws:iam::111111111111:role/ClickHouseAccessRole-001'))

Необязательный external_id также можно указать вместе с role_arn. Он передаётся как параметр ExternalId в вызове AWS STS AssumeRole и позволяет политике доверия роли требовать общий секрет, что снижает риск проблемы «confused deputy». Например:

SELECT count() FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/mta/*.tsv','CSVWithNames',extra_credentials(role_arn = 'arn:aws:iam::111111111111:role/ClickHouseAccessRole-001', external_id = 'my-external-id'))

Другие примеры можно найти здесь

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

Предположим, что у нас есть несколько архивных файлов со следующими 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.

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

Обратите внимание: строки можно вставлять только в новые файлы. Циклы слияния и операции разбиения файлов не поддерживаются. После записи файла все последующие вставки завершатся ошибкой. Подробнее см. здесь.

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

_path — Путь к файлу. Тип: LowCardinality(String). Для архива показывает путь в формате: "{path_to_archive}::{path_to_file_inside_archive}"
_file — Имя файла. Тип: LowCardinality(String). Для архива показывает имя файла внутри архива.
_size — Размер файла в байтах. Тип: Nullable(UInt64). Если размер файла неизвестен, значение — NULL. Для архива показывает несжатый размер файла внутри архива.
_time — Время последнего изменения файла. Тип: Nullable(DateTime). Если время неизвестно, значение — NULL.

Настройка use_hive_partitioning

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

SELECT * FROM s3('s3://data/path/date=*/country=*/code=*/*.parquet') WHERE date > '2020-01-01' AND country = 'Netherlands' AND code = 42;

Доступ к requester-pays бакетам

Чтобы получить доступ к requester-pays бакету, во все запросы необходимо добавлять заголовок x-amz-request-payer = requester. Для этого в функцию s3 передаётся параметр headers('x-amz-request-payer' = 'requester'). Например:

SELECT
    count() AS num_rows,
    uniqExact(_file) AS num_files
FROM s3('https://coiled-datasets-rp.s3.us-east-1.amazonaws.com/1trc/measurements-100*.parquet', 'AWS_ACCESS_KEY_ID', 'AWS_SECRET_ACCESS_KEY', headers('x-amz-request-payer' = 'requester'))

┌───num_rows─┬─num_files─┐
│ 1110000000 │       111 │
└────────────┴───────────┘

1 row in set. Elapsed: 3.089 sec. Processed 1.09 billion rows, 0.00 B (353.55 million rows/s., 0.00 B/s.)
Peak memory usage: 192.27 KiB.

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

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

Вложенные схемы Avro

При чтении файлов Avro, содержащих вложенные записи, которые различаются от файла к файлу (например, в некоторых файлах внутри вложенного объекта есть дополнительное поле), ClickHouse может вернуть ошибку вида:

Количество листьев в записи не соответствует количеству элементов в кортеже…

Это происходит потому, что ClickHouse ожидает, что структуры всех вложенных записей будут соответствовать одной и той же схеме. Чтобы обработать такой сценарий, можно:

Использовать schema_inference_mode='union' для объединения разных схем вложенных записей, или
Вручную привести вложенные структуры к одному виду и включить use_structure_from_insertion_table_in_table_functions=1.

Примечание о производительностиschema_inference_mode='union' может работать медленнее на очень больших наборах данных в S3, поскольку для определения схемы нужно просканировать каждый файл.

Пример

INSERT INTO data_stage
SELECT
    id,
    data
FROM s3('https://bucket-name/*.avro', 'Avro')
SETTINGS schema_inference_mode='union';

## Связанные материалы           

- [Движок S3](/reference/engines/table-engines/integrations/s3)
- [Интеграция S3 с ClickHouse](/integrations/connectors/data-ingestion/AWS/integrating-s3-with-clickhouse)

​Синтаксис

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

​Примеры

​Использование

​Запись с партиционированием

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

​Доступ к публичным бакетам

​Использование учетных данных S3 (ClickHouse Cloud)

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

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

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

​Настройка use_hive_partitioning

​Доступ к requester-pays бакетам

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

​Вложенные схемы Avro

Синтаксис

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

Примеры

Использование

Запись с партиционированием

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

Доступ к публичным бакетам

Использование учетных данных S3 (ClickHouse Cloud)

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

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

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

Настройка use_hive_partitioning

Доступ к requester-pays бакетам

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

Вложенные схемы Avro