s3 Table Function

Amazon S3 및 Google Cloud Storage에 있는 파일을 조회/삽입하기 위한 테이블과 유사한 인터페이스를 제공합니다. 이 테이블 함수는 hdfs function과 유사하지만, S3 전용 기능을 제공합니다.

클러스터에 여러 레플리카가 있는 경우 s3Cluster function을 대신 사용하여 INSERT 작업을 병렬화할 수 있습니다.

s3 table function을 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 Table Function은 GCS XML API와 HMAC 키를 사용하여 Google Cloud Storage와 연동합니다. 엔드포인트와 HMAC에 대한 자세한 내용은 Google 상호 운용성 문서를 참고하십시오.

GCS의 경우, access_key_id와 secret_access_key가 사용되는 위치에 각각 HMAC key와 HMAC secret을 넣으면 됩니다.

매개변수

s3 table function은 다음과 같은 일반 매개변수를 지원합니다:

Parameter	Description
url	파일 경로가 포함된 버킷 URL입니다. 읽기 전용 모드에서 다음 와일드카드를 지원합니다: *, **, ?, {abc,def}, {N..M}. 여기서 N, M은 숫자이고, 'abc', 'def'는 문자열입니다. 자세한 내용은 여기를 참고하십시오.
NOSIGN	자격 증명 대신 이 키워드를 제공하면 모든 요청이 서명되지 않습니다.
access_key_id and secret_access_key	지정된 엔드포인트에서 사용할 자격 증명을 나타내는 키입니다. 선택 사항입니다.
session_token	지정된 키와 함께 사용할 세션 토큰입니다. 키를 전달하는 경우 선택 사항입니다.
format	파일의 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는 와일드카드를 허용하지 않고, 경로를 테이블 루트로 가정하며, 파일 이름은 Snowflake ID, 확장자는 파일 포맷인 Hive 스타일의 파티션 디렉터리를 생성합니다. 기본값은 WILDCARD입니다.
partition_columns_in_data_file	선택적 매개변수입니다. HIVE 파티션 전략에서만 사용됩니다. 데이터 파일에 파티션 컬럼이 기록되어 있는지 ClickHouse에 알려 줍니다. 기본값은 false입니다.
storage_class_name	선택적 매개변수입니다. 지원되는 값: STANDARD 또는 INTELLIGENT_TIERING. AWS S3 Intelligent Tiering을 지정할 수 있습니다. 기본값은 STANDARD입니다.

GCS

GCS URL은 Google XML API의 엔드포인트가 JSON API와 다르기 때문에 다음 형식을 사용합니다:

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

and not https://storage.cloud.google.com.

인수는 named collections를 사용하여 전달할 수도 있습니다. 이 경우 url, access_key_id, secret_access_key, format, structure, compression_method는 동일한 방식으로 동작하며, 추가 매개변수도 지원됩니다:

Argument	Description
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입니다.

반환 값

지정된 파일에서 데이터를 읽고 쓰기 위해 지정한 구조를 가진 테이블입니다.

예시

S3 파일 https://datasets-documentation.s3.eu-west-3.amazonaws.com/aapl_stock.csv에 있는 테이블에서 첫 5개의 행을 조회합니다:

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.snappy 또는 *.parquet.zstd와 같은 이름을 가진 Parquet 파일은 ClickHouse에 혼란을 줄 수 있어 TOO_LARGE_COMPRESSED_BLOCK 또는 ZSTD_DECODER_FAILED 오류를 발생시킬 수 있습니다. 이는 실제로는 Parquet가 행 그룹(row group) 및 컬럼 수준에서 압축을 적용함에도 불구하고, ClickHouse가 전체 파일을 Snappy 또는 ZSTD로 인코딩된 데이터로 읽으려고 시도하기 때문입니다.

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 │
└─────────┘

팁

파일 목록에 선행 0이 포함된 숫자 범위가 있는 경우, 각 자릿수에 대해 중괄호 구문을 사용하거나 ?를 사용하세요.

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

아래는 my-test-bucket 디렉토리 내부의 모든 폴더에서 재귀적으로 test-data.csv.gz 파일의 데이터를 가져옵니다:

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>

프로덕션 환경에서는 named collections을 사용하는 것을 권장합니다. 예를 들면 다음과 같습니다:

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. 파티션 ID를 키로 사용하면 별도의 파일이 생성됩니다:

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. 버킷 이름에 파티션 ID를 사용하면 서로 다른 버킷에 각각 파일이 생성됩니다:

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

이는 일회성 접근이나 자격 증명을 쉽게 순환(로테이션)할 수 있는 경우에 적합합니다. 그러나 반복적인 접근이 필요하거나 자격 증명이 민감한 경우에는 장기적인 솔루션으로 권장되지 않습니다. 이러한 경우에는 역할 기반 접근을 사용하는 것이 좋습니다.

ClickHouse Cloud에서 S3에 대한 역할 기반 접근은 여기에 문서화되어 있습니다.

구성이 완료되면 roleARN을(를) extra_credentials 매개변수를 통해 s3 함수에 전달할 수 있습니다. 예를 들어 다음과 같습니다.

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

추가 예제는 여기에서 확인할 수 있습니다.

아카이브 작업

S3에 다음과 같은 URI를 갖는 여러 아카이브 파일이 있다고 가정합니다:

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

이러한 아카이브에서 데이터는 :: 를 사용하여 추출할 수 있습니다. 글롭(glob) 패턴은 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는 경로(/name=value/)에서 Hive 스타일 파티셔닝을 감지하고, 쿼리에서 파티션 컬럼을 가상 컬럼으로 사용할 수 있도록 허용합니다. 이 가상 컬럼들은 파티션 경로에 있는 컬럼과 동일한 이름을 가지되, 이름 앞에 _가 붙습니다.

예제

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 헤더를 포함해야 합니다. 이는 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.

Storage Settings

• s3_truncate_on_insert - INSERT 전에 파일을 잘라내어 비울 수 있습니다. 기본적으로 비활성화되어 있습니다.
• s3_create_new_file_on_insert - 형식에 접미사가 있을 경우 각 INSERT마다 새 파일을 생성할 수 있습니다. 기본적으로 비활성화되어 있습니다.
• s3_skip_empty_files - 데이터를 읽을 때 빈 파일을 건너뛸 수 있습니다. 기본적으로 활성화되어 있습니다.

중첩 Avro 스키마

파일 간에 구조가 서로 다른 중첩 레코드(예: 일부 파일에는 중첩 객체 안에 추가 필드가 있음)를 포함하는 Avro 파일을 읽을 때, ClickHouse는 다음과 같은 오류를 반환할 수 있습니다:

The number of leaves in record doesn't match the number of elements in tuple...

이는 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';

## Related           

- [S3 engine](../../engines/table-engines/integrations/s3.md)
- [Integrating S3 with ClickHouse](/integrations/s3)