[AWS] AWS Redshift Spectrum으로 S3 외부 테이블 조작해보기

Redshift Spectrum

Redshift Spectrum은 Redshift의 확장 기능으로, S3 스토리지에 있는 파일들을 마치 테이블처럼 취급해 SQL로 조작할 수 있도록 만드는 기능이다. S3의 있는 파일을 Redshift로 옮겨와야할 때, 크기가 너무 크거나 너무 정제되지 않은 채로 업데이트할 경우 비용이나 용량 문제가 발생할 수 있다. 이럴 때, Redshift Spectrum을 이용해 파일에서 필요한 정보들만 redshift에 옮겨올 수 있다.

 

Fact 테이블과 Dimension 테이블

Spectrum을 이용하기에 앞서, 분석 대상 데이터를 크게 2가지 테이블로 나눌 수 있다는 점을 알아보자. 아래와 같이 분석의 메인이 되는 Fact 테이블과 상세 정보를 제공하는 Dimension 테이블로 나눌 수 있다.

• Fact 테이블: 분석의 초점이 되는 양적 정보를 포함하는 중앙 테이블
  • 일반적으로 매출 수익, 판매량 등 사실 또는 측정 항목을 포함
  • 비즈니스 결정에 사용
  • 일반적으로 외래 키(FK)를 통해 여러 Dimension 테이블과 연결됨
  • 보통 Fact 테이블의 크기가 훨씬 더 큼.
  • ex) Order 테이블: 사용자들의 상품 주문에 대한 정보가 들어간 테이블(상품 id, 주문 id, 사용자 id)
• Dimension 테이블: Fact 테이블에 대한 상세 정보를 제공하는 테이블
  • 고객, 제품 등과 같은 테이블로, Fact 테이블에 대한 상세 정보를 제공함.
  • 사용자가 다양한 방식으로 데이터를 조작내고 분석 가능하게 해줌
  • 일반적으로 PK를 가지며, Fact 테이블의 FK에서 참조함.
  • 보통 Dimension 테이블의 크기가 훨씬 더 작음
  • ex) User 테이블 / Product 테이블

일반적으로 규모가 작은 Dimension 테이블은 쉽게 Redshift에 적재하여 사용할 수 있지만, 크기가 큰 Fact 테이블은 다르다. Redshift Spectrum을 이용한다면 파일 형태로 S3에 남아있는 Fact 테이블을 굳이 Redshift에 적재하지 않고도 두 테이블을 조인해 사용할 수 있다.

 

Redshift Spectrum을 사용하기 위한 외부 테이블 용 스키마 설정 (+권한 추가)

외부 테이블을 전달받을 스키마를 만들어주자. 하지만 이 전에 외부 테이블에 대한 권한 설정이 필요하다. AWS IAM으로 이동해 기존에 생성한 redshift.read.s3(역할 생성 방법(역할 생성 방법) 역할로 이동하자. (또는 현재 적용할 redshift 클러스터에 적용된 역할로 이동)

권한 정책 섹션에서 '권한 추가' - '정책 연결'을 선택한다.

 

AWSGlueConsoleFullAccess를 선택하고 '권한 추가' 클릭하면 완료 된다. 여기서 AWS Glue에 대한 권한이 왜 필요하냐면, AWS Glue가 AWS Serverless ETL 서비스로서 아래와 같은 기능들을 제공하기 때문이다.

• 데이터 카탈로그: 데이터 소스 및 메타데이터 대상 검색 기능 제공 (S3 또는 AWS 서비스 상의 데이터 소스가 대상)
  • Redshift Spectrum의 경우에는 외부 테이블들이 그 대상이다.
• ETL 작업 생성: AWS Glue Studio
• 작업 모니터링 및 로그
• 서버리스 실행

즉, Redshift Spectrum으로 외부 테이블 생성 시 S3에 저장된 데이터를 쿼리하고, 외부 테이블의 메타데이터도 AWS Glue의 데이터 카탈로그에 저장하기 위해 AWS Glue에 대한 권한이 필요한 것이다.

 

권한 부여를 완료했다면, 아래 명령어를 통해 외부 테이블 전용 스키마를 생성한다. (필자는 Colab 환경을 사용했다.)

%%sql

-- AWSGlueConsoleFullAccess
CREATE EXTERNAL SCHEMA external_schema
from data catalog
database {원하는 DB 이름}
iam_role '{Redshift 계정에 연결된 역할의 ARN}'
create external database if not exists;

 

그리고 S3에 있는 파일 하나를 선택해 아래와 같이 외부 테이블을 생성해준다.

-- 예시
%%sql

CREATE EXTERNAL TABLE external_schema.user_session_channel(
   userid integer ,
   sessionid varchar(32),
   channel varchar(32)
)
row format delimited
fields terminated by ','
stored as textfile
location 's3://minhyeok-test-bucket/usc/';

 

이제 위와 같이 생성한 외부 테이블들과 Redshift 내부 테이블 모두를 SQL로 조작이 가능하다.