Redshift Spectrum

 

 

Redshift와 S3을 사용하다보면..

 

S3의 굉장히 큰 데이터를 Redshift로 로딩할 때 다소 버거울 것입니다. 일단 비용이 많이 나오고 데이터의 질이 떨어질 수도 있죠.

 

그러면 데이터를 정제하고 Redshift로 옮기고 싶을텐데 그런 경우 사용할 수 있는 것이 Redshift Spectrum (혹은 Athena)입니다. S3의 테이블들을 external 테이블로 간주해 마치 Redshift안의 테이블처럼 SQL을 사용해 조작할 수 있도록 해줍니다.

 

 

 

 

 

Fact 테이블과 Dimension 테이블

매우 자주 듣게될 용어들 입니다.

 

Fact 테이블: 분석의 초점이 되는 양적 정보를 포함하는 중앙 테이블
일반적으로 매출 수익, 판매량 또는 이익과 같은 사실 또는 측정 항목을 포함하며 비즈니스 결정에 사용
Fact 테이블은 일반적으로 외래 키를 통해 여러 Dimension 테이블과 연결됨
보통 Fact 테이블의 크기가 훨씬 더 큼

Dimension 테이블: Fact 테이블에 대한 상세 정보를 제공하는 테이블
고객, 제품과 같은 테이블로 Fact 테이블에 대한 상세 정보 제공
Fact 테이블의 데이터에 맥락을 제공하여 사용자가 다양한 방식으로 데이터를 조각내고 분석 가능하게 해줌
Dimension 테이블은 일반적으로 primary key를 가지며, fact 테이블의 foreign key에서 참조
보통 Dimension 테이블의 크기는 훨씬 더 작음

 

Fact 테이블과 Dimension 테이블을 함께 사용한다면 더 가치있는 Insight를 얻을 수 있을 것입니다. 그래서 Fact 테이블과 Dimension 테이블은 서로 PK와 FK로 JOIN이 가능하고 가능해야합니다. (보통은 Dimension 테이블의 Primary Key가 Fact 테이블의 Foreign Key 형태로 참조되는 구조가 될 것입니다.

 

굉장히 크기가 큰 Fact 테이블들을 JOIN을 위해 전부 Redshift에 적재시키면 비용과 시간 측면에서 매우 불합리하지만 AWS Spectrum을 사용하면 그럴 필요가 없어집니다. 크기가 작은 Dimension 테이블들만 Redshift에 적재하고 데이터 레이크 or 스토리지인 S3의 Fact 테이블과 JOIN하면 그만이기 때문입니다.

 

 

 

 

 

 

 

 

 

Fact 테이블과 Dimension 테이블의 예 (1)

Fact 테이블: 앞서 사용했던 user_session_channel

 

Dimension 테이블: 사용자나 채널에 대한 정보로 상대적으로 크기가 작음
앞서 존재하지 않았지만 user_session_channel 테이블에 사용된 사용자나 채널에 대한 정보
user
channel

 

 

 

 

Fact 테이블과 Dimension 테이블의 예 (2)

Fact 테이블: Order 테이블. 사용자들의 상품 주문에 대한 정보가 들어간 테이블

 

Dimension 테이블:

Product 테이블. Order 테이블에 사용된 상품에 대한 정보
User 테이블. Order 테이블에서 상품 주문을 한 사용자에 대한 정보

 

 

 

 

 

 

 

 

 

 

 

 

Redshift Spectrum 사용 유스 케이스

S3에 대용량 Fact 테이블이 파일(들)로 존재
Redshift에 소규모 Dimension 테이블이 존재
Fact 테이블을 Redshift로 적재하지 않고 위의 두 테이블을 조인하고 싶다면?

 

이 때 사용할 수 있는 것이 Redshift Spectrum
이는 별도로 설정하거나 론치하는 것이 아니라 Redshift의 확장 기능으로 사용하고 그만큼 비용 부담

 

 

 

 

 

 

 

 

 

 

외부 테이블(External Table)이란?

외부 테이블은 모든 데이터 웨어하우스에 존재하는 개념입니다.

 

● 데이터베이스 엔진이 외부에 저장된 데이터를 마치 내부 테이블처럼 사용하는 방법
○ 외부 테이블은 외부(보통 S3와 같은 클라우드 스토리지)에 저장된 대량의 데이터를 데이터베이스 내부로 복사하고 쓰는 것이 아니라 임시 목적으로 사용하는 방식

● SQL 명령어로 데이터베이스에 외부 테이블 생성 가능
○ 이 경우 데이터를 새로 만들거나 하는 것이 아니라 참조만 하게 됨
○ 외부 테이블은 CSV, JSON, XML과 같은 파일 형식 뿐만 아니라 ODBC 또는 JDBC 드라이버를 통해 액세스하는 원격 데이터베이스와 같은 다양한 데이터 소스에 대해 사용 가능

● 외부 테이블을 사용하여 데이터 처리 후 결과를 데이터베이스에 적재하는데 사용가능
○ 예를 들어, 외부 테이블을 사용하여 로그 파일을 읽고 정제된 내용을 데이터베이스 테이블에 적재 가능

● 외부 테이블은 보안 및 성능 문제에 대해 신중한 고려가 필요
● 이는 Hive등에서 처음 시작한 개념으로 이제는 대부분의 빅 데이터 시스템에서 사용됨

 

 

 

 

 

 

 

 

 

 

Redshift Spectrum 사용 방식

● S3에 있는 파일들을 마치 테이블처럼 SQL로 처리 가능
○ S3 파일들을 외부 테이블들(external table)로 처리하면서 Redshift 테이블과 조인 가능
○ S3 외부 테이블들은 보통 Fact 테이블들이 되고 Redshift 테이블들은 Dimension 테이블

● 이를 사용하려면 Redshift 클러스터가 필요
○ S3와 Redshift 클러스터는 같은 region에 있어야함

● S3 Fact 데이터를 외부 테이블(External Table)로 정의해야함

 

 

 

 

 

 

 

실습의 순서

1. 외부 테이블용 스키마 생성

 

2. 내부 Dimension 테이블 생성

 

3. 외부 Fact 테이블 생성

○ Redshift의 S3 접근용 IAM Role에 권한 추가 (AWSGlueConsoleFullAccess)

○ S3 버킷에 새 폴더 만들고 user_session_channel.csv 파일 업로드

○ 위 파일을 바탕으로 외부 테이블 생성

 

4. 내부 테이블과 외부 테이블 조인

 

 

 

 

 

1. Redshift Spectrum 실습을 위한 외부 테이블용 스키마 설정

● 먼저 앞서 만든 redshift.read.s3 ROLE에 AWSGlueConsoleFullAccess 권한 지정이 필요
● 다음으로 아래 SQL을 실행하여 외부 테이블용 스키마 생성

 

CREATE EXTERNAL SCHEMA external_schema
from data catalog
database 'myspectrum_db'
iam_role 'arn:aws:iam::1234~1234:role/redshift.read.s3'
create external database if not exists;

 

첫번째 줄, external_schema라는 이름의 외부 테이블 전용 스키마를 만듭니다.

 

세번째 줄, 그런데 그전에 외부 테이블들이 정의되는 외부 데이터베이스 myspectrum_db를 생성해야 합니다.

 

네번째 줄(중요), 우리가 지난 실습에서 devjon-test-bucket이라는 이름의 S3 버킷을 만들고 redshift.read.s3라는 S3 Full Access가 가능한 Role(역할)을 만들어 Redshift Serverless 클러스터에 할당해줬던 것을 기억하실 겁니다.

 

그 당시 만들었던 redshift.read.s3는 S3 버킷에 Full Access가 가능한 역할만 있었는데 Redshift Spectrum을 사용하려면 AWSGlueConsoleFullAccess 권한도 필요합니다. 그 권한을 역할로 지정한 뒤 복사하여 iam_role에 적어주셔야 합니다.

 

 

 

 

 

 

 

 

 

 

 

 

 

AWS Glue란?

 

AWS Glue는 Airflow와 컨셉은 비슷하지만 AWS 기능과 굉장히 타이트하게 커플링되어있기 때문에 AWS Glue로 만든 기능은 AWS 바깥에서는 거의 힘을 발휘하지 못합니다.

 

● AWS Glue는 AWS의 Serverless ETL 서비스로 아래와 같은 기능 제공

데이터 카탈로그:
a. AWS Glue Data Catalog는 데이터 소스(S3) 및 대상의 메타데이터(데이터에 대한 데이터)를 대상으로 검색 기능을 제공. 이는 주로 S3나 다른 AWS 서비스 상의 데이터 소스를 대상으로 함 (Redshift Spectrum의 경우에는 외부 테이블들)

 

간단하게 말하면 외부 테이블을 정의하면 AWS Glue가 그에 대한 데이터들 저장하고 검색 기능을 제공한다는 것입니다.

 

상단의 쿼리에서 둘째줄 'from data catalog'를 적어주는 이유입니다'

 

ETL 작업 생성: AWS Glue Studio
a. 간단한 드래그 앤 드롭 인터페이스를 통해 ETL 작업 생성 가능
b. 사용자는 데이터 소스 및 대상을 선택하고 데이터 변환 단계를 정의하는 스크립트 생성

 

작업 모니터링 및 로그:
a. AWS Glue 콘솔을 통해 사용자는 ETL 작업의 실행 상태 및 로그를 모니터링 가능

 

서버리스 실행:
a. AWS Glue는 서버리스 아키텍처를 사용하므로 사용자는 작업을 실행하는 데 필요한 인프라를 관리할 필요가 없음 (Auto Scaling)

 

 

Redshift Spectrum과 AWS Athena를 사용하려면 반드시 사용해야 합니다만 Glue를 깊게 아실 필요는 없습니다. ETL 관점에서는 많이 사용되는 서비스는 절대 아닙니다.

 

 

 

 

2. Redshift Spectrum 실습을 위한 외부 Fact 테이블 정의

S3에 usc라는 폴더를 각자 S3 버킷 밑에 만들고
그 폴더로 user_session_channel.csv 파일을 복사
다음으로 아래 SQL을 실행 (이런 형태의 명령은 Hive/Presto/SparkSQL에서 사용됨)

 

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://keeyong-test-bucket/usc/';

 

 

 

 

 

 

3. Redshift Spectrum 실습을 위한 내부 Dimension 테이블

테스트를 위해 user 테이블을 하나 raw_data 스키마 밑에 생성

 

CREATE TABLE raw_data.user_property AS
SELECT
 userid, 
 CASE WHEN cast (random() * 2 as int) = 0 THEN 'male' ELSE 'female' END gender,
 (CAST(random() * 50 as int)+18) age
FROM (
 SELECT DISTINCT userid
 FROM raw_data.user_session_channel
);

 

 

 

 

 

 

 

 

4. Redshift Spectrum Fact + Dimension 테이블 조인

SELECT gender, COUNT(1)
FROM external_schema.user_session_channel usc
JOIN raw_data.user_property up ON usc.userid = up.userid
GROUP BY 1;