Athena 테이블 DDL 간단 정리 - YA-Hwang 기술 블로그

Athena 테이블 DDL에 대해 간단히 정리해본다.

데이터 타입

데이터 타입은 아래 링크에서 확인할 수 있다.

ARRAY & STRUCT 타입

특별한 경우로는 ARRAY와 STRUCT 타입이 있다.

[1,2,3] => array<int>

{"a": 100, "b": "HELLO"} => struct<a:int,b:string>

{"a": 3.54, "b":["a","b"]} => struct<a:float,b:array<string>>

[{"a": 15341, "b": "2.121}, {"a": 2341, "b": 3.232}] => array<struct<a:bigint,b:double>>

참고로, SELECT 문에서 직접 사용하려면 다음처럼 사용한다. ( UNLOAD를 통해 데이터 타입을 변환해서 저장할 때 유용하다. )

STRUCT는 ROW라는 함수를 사용하고 CAST를 통해 STRUCT의 타입을 정의해주어야 한다. ( string 대신 varchar를 사용 )

SELECT ARRAY[1,2,3]

SELECT CAST(ROW('HELLO', 100) AS ROW(name varchar, age int))

ARRAY 타입 사용 시 주의할 점

Athena에서는 보통 Parquet 타입을 많이 사용한다. Parquet 타입은 empty array를 허용하지 않는다.

UNLOAD 아테나 쿼리를 사용해 parquet로 압축하기 위해서는 배열의 길이 확인 후 empty array를 NULL로 변환해야 한다.

cardinality: 배열 길이 함수

-- 배열 길이 확인
SELECT cardinality(ARRAY[1,2,3]) 
=> 3

SELECT CASE WHEN cardinality(col) = 0 THEN NULL ELSE col END AS col

CSV 데이터

CSV 처리를 위한 OpenCSVSerDe - in Athena

skip.header.line.count : CSV의 header를 무시한다는 의미이다.

separatorChar : CSV 구분자

quoteChar : “ABC”와 같은 형태의 value를 처리하기 위함

CREATE EXTERNAL TABLE [ DB ].[ 테이블 ](
  `A` int, 
  `B` string,
  `C` float
)

ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
WITH SERDEPROPERTIES ( 
  'separatorChar'=','
  'quoteChar'='"', 
  'escapeChar'='\\', 
)
LOCATION
  's3://[ 버킷 ]/[ PREFIX ]'

TBLPROPERTIES (
  'skip.header.line.count'='1'
)

JSON 데이터

ignore.malformed.json : 오류 데이터는 무시한다는 의미

CREATE EXTERNAL TABLE [ DB ].[ 테이블 ](
  ...
  )
ROW FORMAT SERDE 
  'org.openx.data.jsonserde.JsonSerDe' 
WITH SERDEPROPERTIES ( 
  'ignore.malformed.json'='true') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'

LOCATION
    's3://[ 버킷 ]/[ PREFIX ]'

PARQUET 데이터

CREATE EXTERNAL TABLE [ DB ].[ 테이블 ](
  ...
  )

ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'

LOCATION
    's3://[ 버킷 ]/[ PREFIX ]'

압축 지원

아테나는 압축된 파일 읽기를 제공한다. 흥미로운 사실은 테이블에서 여러 압축 형식의 파일이 존재하더라도 사용 가능하다는 점이다.

그래서 테이블을 정의할 때 compression 값을 명시하지 않는다. ( 쓰기 작업엔 compression을 명시 )

parquet의 경우, 한 테이블에서 일부 snappy, 일부 gzip으로 압축된 파일을 활용하는 경우도 사용 가능하다.

쓰기 작업인 UNLOAD를 사용할 때는 compression 값을 지정한다.

parquet와 달리 JSON 및 TEXT(CSV 등)의 경우, gzip 압축방식을 변경할 수는 없다.

UNLOAD로 생성한 gzip 압축파일과 압축되지 않은 파일을 혼용하더라도 쿼리가 가능했다.

파티션 관리

파티션된 테이블 정의

파티션 컬럼은 PARTITIONED BY 라는 부분에만 작성해야 한다.

이렇게 작성해도 쿼리 가능한 컬럼을 의미한다.

테이블 컬럼 정의에도 사용할 경우, 중복 오류를 확인할 수 있다.

FAILED: SemanticException [Error 10035]: Column repeated in partitioning columns

CREATE EXTERNAL TABLE [ DB ].[ 테이블 ](
  ...
  )

PARTITIONED BY ( 
  `[ 파티션 ]` string,
)

ROW FORMAT SERDE 
    ...

테이블에 파티션 추가

ALTER TABLE [ DB ].[ 테이블 ] ADD IF NOT EXISTS 
PARTITION ([파티션] = '[ 값 ]') LOCATION 's3://[ 버킷 ]/[ PREFIX ]/[ 파티션 ]=[ 값 ]'

Projection Partitioning

파티션을 자동으로 추가하는 방법으로 파티션이 연속적인 숫자나 날짜일 때 사용한다.

아래는 파티션이 날짜인 경우 파티션명을 dt로 설정하고 적용한 예시이다.

CREATE EXTERNAL TABLE [ DB ].[ 테이블 ](
    ...
)

...

PARTITIONED BY ( 
  `dt` string
)

...

LOCATION
  's3://[ 버킷 ]/[ PREFIX ]'
TBLPROPERTIES (
  'projection.dt.format'='yyyy-MM-dd', 
  'projection.dt.interval'='1', 
  'projection.dt.interval.unit'='DAYS', 
  'projection.dt.range'='2023-01-01,NOW', 
  'projection.dt.type'='date',
  'storage.location.template'='s3://[ 버킷 ]/[ PREFIX ]/dt=${dt}'
)

References :