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

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

Если у вас несколько реплик в кластере, вы можете использовать функцию s3Cluster вместо этого для параллелизации вставок.

При использовании s3 табличной функции с INSERT INTO...SELECT данные читаются и вставляются в потоковом режиме. В памяти находится только несколько блоков данных, в то время как блоки постоянно читаются из S3 и отправляются в целевую таблицу.

Синтаксис

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

GCS

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

Для GCS подмените ваш HMAC ключ и HMAC секрет, где вы видите access_key_id и secret_access_key.

Параметры

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

Параметр	Описание
`url`	URL корзины с путем к файлу. Поддерживает следующие подстановочные символы в режиме только для чтения: ``, `*`, `?`, `{abc,def}` и `{N..M}`, где `N`, `M` — числа, `'abc'`, `'def'` — строки. Для получения дополнительной информации см. здесь.
`NOSIGN`	Если это слово указано вместо учетных данных, все запросы не будут подписаны.
`access_key_id` и `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 и расширением файла как форматом. По умолчанию `WILDCARD`
`partition_columns_in_data_file`	Параметр является необязательным. Используется только с стратегией партиционирования `HIVE`. Указывает ClickHouse, следует ли ожидать, что столбцы партиции будут записаны в файл данных. По умолчанию `false`.
`storage_class_name`	Параметр является необязательным. Поддерживаемые значения: `STANDARD` или `INTELLIGENT_TIERING`. Позволяет указать AWS S3 Intelligent Tiering. По умолчанию `STANDARD`.

GCS

URL GCS имеет следующий формат, так как конечная точка XML API Google отличается от 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 автоматически распакует файл.

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

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

Посчитайте количество строк в файлах, оканчивающихся на числа от 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;

Глоб ** может использоваться для рекурсивного обхода каталогов. Рассмотрим следующий пример, он будет извлекать все файлы из директории 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

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

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

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

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

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

Доступ к корзинам с оплатой запрашивающего

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

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 - позволяет пропускать пустые файлы при чтении. Включено по умолчанию.

Синтаксис​

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

Примеры​

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

Разделенная Запись​

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

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

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

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

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

Виртуальные Колонки​

Настройка use_hive_partitioning​

Доступ к корзинам с оплатой запрашивающего​

Настройки Хранения​

Связанные​