табличная функция 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 [,..]])

Параметры

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

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

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

Выбор первых 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',
   NOSIGN,
   '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 │
└────────────┴─────────┴─────────┴─────────┴─────────┴──────────┴─────────┘

Предположим, что на 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', NOSIGN, '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/*', NOSIGN, 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32')

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

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

SELECT count(*)
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/my-test-bucket-768/big_prefix/file-{1..4}.csv', NOSIGN, '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/**', NOSIGN, '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', NOSIGN, 'CSV', 'name String, value UInt32', 'gzip');

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

SELECT * FROM s3('s3://clickhouse-public-datasets/my-test-bucket-768/**/test-data.csv.gz', NOSIGN, '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} в пути к файлу на фактическое значение ключа партиционирования. Выбирается по умолчанию, если путь содержит {_partition_id}.

Если partition_strategy не задана, путь с другим glob не использует стратегию партиционирования и игнорирует PARTITION BY. Путь без glob использует hive, когда file_like_engine_default_partition_strategy имеет значение hive; в противном случае стратегия партиционирования не используется.

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_strategy='wildcard')
    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_strategy='wildcard')
    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;

Для закрытых бакетов можно передать в функцию 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:

• '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',
   NOSIGN
);

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

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

Это указание для 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 бакету, во все запросы необходимо добавлять заголовок 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'))

┌

Настройка s3_base позволяет передавать относительный URL функции s3. Если задана настройка s3_base, а аргумент функции не содержит схемы, URL разрешается относительно базового URL в соответствии с RFC 3986, по тем же правилам, что и в настройке url_base функции url. Абсолютные URL передаются без изменений.

Настройка также применяется к движку таблицы S3 и табличным функциям, использующим конфигурацию s3 (s3Cluster, gcs, oss). Для движка таблицы S3 разрешённый URL материализуется в сохранённом определении таблицы, поэтому после создания таблица не зависит от значения s3_base.

Пример

SET s3_base = 's3://clickhouse-public-datasets/';
SELECT count() FROM s3('hits_compatible/hits.csv', NOSIGN);

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

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

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

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

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

Пример

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

• S3 engine
• Integrating S3 with ClickHouse