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

Предоставляет табличный интерфейс для выборки и вставки файлов в Amazon S3 и Google Cloud Storage. Эта табличная функция похожа на функцию hdfs, но предоставляет специфичные для S3 возможности.

Предоставляет интерфейс, подобный таблице, для выборки/вставки файлов в 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, а в качестве расширения — формат файла. По умолчанию WILDCARD
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.

GCS

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

• ‘https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/some_prefix/some_file_1.csv'
• ‘https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/some_prefix/some_file_2.csv'
• ‘https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/some_prefix/some_file_3.csv'
• ‘https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/some_prefix/some_file_4.csv'
• ‘https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/another_prefix/some_file_1.csv'
• ‘https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/another_prefix/some_file_2.csv'
• ‘https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/another_prefix/some_file_3.csv'
• ‘https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/another_prefix/some_file_4.csv'

Подсчитайте количество строк в файлах, названия которых оканчиваются на числа от 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} в пути к файлу на фактическое значение ключа партиционирования.

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

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

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.

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

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'))

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

​

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

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

• ‘https://s3-us-west-1.amazonaws.com/umbrella-static/top-1m-2018-01-10.csv.zip'
• ‘https://s3-us-west-1.amazonaws.com/umbrella-static/top-1m-2018-01-11.csv.zip'
• ‘https://s3-us-west-1.amazonaws.com/umbrella-static/top-1m-2018-01-12.csv.zip'

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