s3 테이블 함수

s3 테이블 함수

Amazon S3와 Google Cloud Storage의 파일을 조회/삽입할 수 있는 테이블 같은 인터페이스를 제공하는 테이블 함수예요. hdfs 함수와 비슷하지만 S3 특화 기능을 제공해요.

출처: 문서

본문

Amazon S3와 Google Cloud Storage의 파일을 조회/삽입할 수 있는 테이블 같은 인터페이스를 제공해요. 이 테이블 함수는 hdfs 함수와 비슷하지만 S3 특화 기능을 제공해요.

클러스터에 복제본이 여러 개라면 삽입을 병렬화하기 위해 대신 s3Cluster 함수를 사용할 수 있어요.

INSERT INTO...SELECT와 함께 s3 테이블 함수를 사용하면 데이터가 스트리밍 방식으로 읽히고 삽입돼요. S3에서 블록을 계속 읽어 대상 테이블로 밀어 넣는 동안 소수의 데이터 블록만 메모리에 상주해요.

문법 (Syntax)

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 테이블 함수는 GCS XML API와 HMAC 키를 사용해 Google Cloud Storage와 통합해요. 엔드포인트와 HMAC에 대한 자세한 내용은 Google interoperability 문서를 참고하세요. GCS의 경우 access_key_id와 secret_access_key를 볼 수 있는 곳에 HMAC 키와 HMAC 시크릿을 넣으세요.

파라미터 (Parameters)

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, deflate, brotli 또는 br, xz 또는 LZMA, zstd 또는 zst, lz4, bz2, snappy. 기본적으로 파일 확장자로 압축 방법을 자동 감지해요. snappy의 경우 와이어 포맷은 snappy_mode 설정(기본은 basic`)으로 선택돼요.
headers 선택 사항이에요. S3 요청에 헤더를 전달할 수 있어요. headers(key=value) 형식으로 전달하세요. 예: headers('x-amz-request-payer' = 'requester').
partition_strategy 선택 사항이에요. 지원 값: wildcard 또는 hive. wildcard는 경로에 {_partition_id}가 필요하며 파티션 키로 치환돼요. hive는 와일드카드를 허용하지 않으며 경로를 테이블 루트로 가정하고, Snowflake ID를 파일 이름, 파일 포맷을 확장자로 하는 Hive 스타일 분할 디렉터리를 생성해요. 명시적 전략이 없으면 {_partition_id}가 있는 경로는 wildcard를 사용해요. 다른 글로브가 있는 경로는 파티션 전략 없음이며 PARTITION BY를 무시해요. 글로브가 없는 경로는 file_like_engine_default_partition_strategy가 hive일 때 hive를 사용하고, 그 외에는 파티션 전략 없음을 사용해요.
partition_columns_in_data_file 선택 사항이에요. hive 파티션 쓰기에서 데이터 파일 안에 파티션 컬럼 값이 포함된 경우, 해당 컬럼들을 지정하는 쉼표 구분 목록이에요.

GCS — Google XML API의 엔드포인트가 JSON API와 다르므로 GCS URL 형식은 다음과 같아요:

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이에요.

반환값 (Returned value)

지정된 파일의 데이터를 읽거나 쓸 수 있는, 지정된 구조를 가진 테이블이에요.

예시 (Examples)

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

ClickHouse는 파일 이름 확장자로 데이터의 포맷을 결정해요. 예를 들어 앞선 명령을 CSVWithNames 없이도 실행할 수 있어요:

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

ClickHouse는 파일의 압축 방법도 결정할 수 있어요. 예를 들어 파일이 .csv.gz 확장자로 압축되어 있다면 ClickHouse가 파일을 자동으로 압축 해제해요.

*.parquet.snappy나 *.parquet.zstd 같은 이름의 Parquet 파일은 ClickHouse를 혼란시켜 TOO_LARGE_COMPRESSED_BLOCK 또는 ZSTD_DECODER_FAILED 오류를 일으킬 수 있어요.

이것은 ClickHouse가 실제로는 Parquet이 행 그룹과 컬럼 수준에서 압축을 적용하는데도 전체 파일을 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'
);

사용법 (Usage)

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

파일 목록에 0으로 시작하는 숫자 범위가 들어 있다면, 각 자릿수마다 중괄호 구성을 사용하거나 ?를 사용하세요.

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;

** 글로브는 재귀 디렉터리 탐색에 사용할 수 있어요. 다음 쿼리는 my-test-bucket-768 아래의 some_file_1.csv라는 모든 파일을 읽어요:

SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/my-test-bucket-768/**/some_file_1.csv', NOSIGN, 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32');

중괄호를 **와 결합해 여러 파일 이름을 재귀적으로 일치시킬 수 있어요:

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

같은 재귀 패턴이 s3:// URL에서도 동작해요:

SELECT * FROM s3('s3://datasets-documentation/my-test-bucket-768/**/some_file_1.csv', NOSIGN, 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32');

config.xml에 사용자 정의 URL 매퍼를 추가할 수 있어요:

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

파티션 쓰기 (Partitioned Write)

파티션 전략 (Partition Strategy)

INSERT 쿼리에서만 지원돼요.

wildcard: 파일 경로의 {_partition_id} 와일드카드를 실제 파티션 키로 치환해요. 경로에 {_partition_id}가 있으면 기본으로 선택돼요.

partition_strategy를 설정하지 않으면, 다른 글로브가 있는 경로는 파티션 전략 없음이며 PARTITION BY를 무시해요. 글로브가 없는 경로는 file_like_engine_default_partition_strategy가 hive일 때 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 파티션 전략 예시

키에 파티션 ID를 사용하면 별도의 파일이 생겨요:

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 세 개 파일에 데이터가 쓰여요.

버킷 이름에 파티션 ID를 사용하면 서로 다른 버킷에 파일이 생겨요:

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 세 파일에 데이터가 쓰여요.

공개 버킷 접근 (Accessing public buckets)

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 역할 기반 접근은 여기에 문서화되어 있어요.

구성이 끝나면 extra_credentials 파라미터를 통해 roleARN을 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'))

role_arn과 함께 선택 사항인 external_id도 제공할 수 있어요. AWS STS AssumeRole 호출의 ExternalId 파라미터로 전달되며, 역할의 신뢰 정책이 공유 시크릿을 요구하게 해서 혼동된 대리인 문제를 완화해요. 예를 들어:

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

추가 예시는 여기에서 찾을 수 있어요.

아카이브 작업 (Working with archives)

S3에 다음과 같은 URI를 가진 여러 아카이브 파일이 있다고 가정해볼게요:

::를 사용해 이 아카이브에서 데이터를 추출할 수 있어요. 글로브는 URL 부분과 :: 뒤의 부분(아카이브 안 파일 이름 담당) 모두에서 사용할 수 있어요.

SELECT *
FROM s3(
 'https://s3-us-west-1.amazonaws.com/umbrella-static/top-1m-2018-01-1{0..2}.csv.zip :: *.csv',
 NOSIGN
);

ClickHouse는 세 가지 아카이브 포맷을 지원해요: ZIP, TAR, 7Z.

ZIP과 TAR 아카이브는 지원되는 모든 스토리지 위치에서 접근할 수 있지만, 7Z 아카이브는 ClickHouse가 설치된 로컬 파일시스템에서만 읽을 수 있어요.

데이터 삽입 (Inserting Data)

행은 새 파일에만 삽입될 수 있다는 점을 유의하세요. 머지 사이클이나 파일 분할 연산은 없어요. 파일이 한 번 쓰이면 이후 삽입은 실패해요. 자세한 내용은 여기를 참고하세요.

가상 컬럼 (Virtual Columns)

  • _path — 파일의 경로예요. 타입: LowCardinality(String). 아카이브의 경우 "{path_to_archive}::{path_to_file_inside_archive}" 형식으로 경로를 보여줘요.
  • _file — 파일의 이름이에요. 타입: LowCardinality(String). 아카이브의 경우 아카이브 안 파일의 이름을 보여줘요.
  • _size — 바이트 단위의 파일 크기예요. 타입: Nullable(UInt64). 파일 크기를 알 수 없으면 값은 NULL이에요. 아카이브의 경우 아카이브 안 파일의 압축되지 않은 크기를 보여줘요.
  • _time —

더 알아보기 (Learn more)