학습주제
Redshift Spectrum으로 S3 외부테이블 조작해보기
학습내용
S3에 굉장히 큰 데이터가 있는데 이를 Redshift로 로딩하기가 버겁다면 이를 외부 테이블로 설정해서 Redshift에서 조작이 가능하다.
S3에 굉장히 큰 데이터가 있고, Redshift로 로딩하는건 비용이 더 나올 수도 있음. 퀄리티가 떨어질 수도 있음. 이에 S3 파일들을 External 테이블로 간주해서 마치 레드쉬프트 내부 테이블로 간주해서 정제할 수 있음.
AWS 아테나를 쓰면 S3 파일 정제해서 레드쉬프트로 정제해서 올릴 수 있음.
- 차이점이라면 레드쉬프트 클러스터에서 접근하는게 아니라 외부에서 자체적으로
Fact, Dimension 테이블
Fact 테이블: 굉장히 큰 데이터, 분석의 초점이 되는 양적 정보를 포함하는 중앙 테이블. 수익, 판매량, 사용자의 기록 테이블. fact 테이블을 다양한 각도에서 분석할수 있게 할 수 있음. 외래키를 가지고, 디멘션 테이블과 연결
Dimension 테이블: Fact테이블에 대한 상세 정보. 특징, 성별, 살고있는 지역을 분석한 테이블. 가격, 카테고리에 따라 다양한 테이블 생성. 두 테이블 간 조인을 했을때 인사이트풀한 정보를 도출하는 경우가 많음. 의미있는 분석을 가능하게 해주는 정보.
JOIN key를 갖게 됨. 디멘션 테이블은 일반적으로 PK를 가짐.
스펙트럼과 무슨관계?
Fact를 레드쉬프트로 로딩하는게 비용적 문제가 됨. S3에 적재 해놓고 필요할 때마다 로딩해서 프로세스 후 클러스터에 저장. 디멘션만 클러스터에 올려넣고 연산함.
Fact 테이블: user_session_channel
유저와 채널에 대한 별도 정보. 수익정보, 채널이 어떤 특성을 갖는지, 주 연령대는 어떻게 되는지 - 디멘션 테이블
두 테이블을 조인해서 의미있는 분석을 할 수 있음
팩트 테이블: 오더 테이블. 사용자에 대한 ID , 상품에 대한 ID,
디멘션 테이블: 상품 정보 테이블, 사용자 정보 테이블
S3에 큰 팩트 테이블 존재
굳이 레드쉬프트로 조인하지 않고 적재하고 싶다
스펙트럼을 사용한다 (단 같은 지역에 S3, 클러스터)
외부 테이블
기존 개념임. 하이브에서 시작. 외부에 굉장히 큰 테이블이 있는경우 시스템으로 다 읽어오지 않고, 잠깐 읽고 쓰겠다.
HDFS에 있는 파일들.
SQL 명령을 써서 사용. CREATE EXTERNAL TABLE, S3 위치한 파일에 테이블 이름을 지정하고, 포맷을 기술.
csv, json, xml 등이 있음. 관계형 DB도 ODBC, JDBC 같은 외부 데이터 베이스도 가져옴
다시 외부에 저장할 수도 있고, 대부분은 내부 데이터 시스템에 생성한 테이블 저장.
성능 문제가 대두됨. 네트워크를 타고 외부에서 오는 경우 성능 저하가 있을 수 있음.
보통 읽기 전용으로 쓰임. delete한다고 외부 테이블이 삭제되진 않음.
사용 절차
같은 지역에 있어야 함.
external table로 처리
S3 는 fact 테이블이 되고
레드쉬프트는 디멘션 테이블이 됨.
비구조화된 큰 데이터 파일일 수 있음.
external 테이블만 놓이는 스키마있어야함
CREATE EXTERNAL SCHEMA external_schema
from data catalog
database 'myspectrum_db'
iam_role '.,,,'
create external database if not exists;외부데이터 베이스를 정의
저 명령어를 그대로 가져다 쓰면 됨.
저번 역할에 더해
iam_role을 지정. AWSGlueConsoleFullAccess 권한을 추가로 줘야함
ARN을 복사해 붙여넣기
AWS Glue
AWS가 갖고 있는 ETL 서비스. 에어플로우 같음.
AWS 내부들에서 최적화 되어 있음.
데이터 카탈로그
- 중요 컴포넌트. 외부테이블을 정의한순간 Glue 카탈로그 내에도 저장됨.
ETL 작업 - 드래그 앤 드롭 으로 작업 생성
모니터링 , 로그
글루가 알아서 오토 스케일리응 ㄹ해주고 돈을 냄.
깊게 알 필요는 없음. ETL 관점에서 많이 쓰이는 서비스는 아님. 스펙트럼, 아테나를 쓰려면 꼭 써야하는 서비스이지많 많이 쓰이진 않음.
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://jongwook-redshift-bucket/usc/';row format delimited - 한 줄이 한 레코드에 해당
fields terminated by ',' - csv 형식이다
stored as textfile - 텍스트 파일 형식이다
location 해당 폴더에 있는 모든 파일들을 하나의 테이블처럼 로딩하게됨.
다수의 파일이 있어도 다 로딩함. 파일중 하나라도 포맷이 다르면 에러가 남
스키마 만드는건 어드민 권한
디멘션 테이블
사용자에 관한 세부정보를 받는 디멘션 테이블
CTAS 사용
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
);random()은 0부터 1 사이의 임의의 실수 값을 반환하는 함수입니다.
random()*2는 0부터 2 사이의 임의의 실수 값을 반환합니다.
cast(expression as int)는 표현식의 값을 정수로 변환하는 명령입니다. 이 경우에는 실수 값을 정수로 변환합니다.
나이도 최소 18세 ~ 68세의 랜덤한 나이를 갖는 컬럼
FROM에선 유일한 userid만 가져오고, 나머지는 SELECT에서 자체 생성
외부테이블이 내부테이블 양 SQL 만들면됨
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;외부 테이블과 JOIN 할 수도 있있음
