📌 Redshift Spectrum으로 S3 외부 테이블 조작해 보자
1. Redshift IAM role에 AWSGlueConsoleFullAccess 권한 추가
- 먼저 이전 포스트에서 만든
redshift.read.s3 역할(role)에 추가적으로AWSGlueConsoleFullAccess권한을 주어야 한다.
[AWS RedShift] 6. 벌크 업데이트 (Bulk Update) 구현 - 이전 포스트 참고
IAM 대시 보드에서역할->redshift.read.s3를 선택해 준다. 그러면 다음과 같이 해당 역할에 부여된 권한 정책 목록이 뜨는데 이때권한 추가->정책 연결을 선택해 준다. (기존 있는 정책을 검색 후 선택 추가할 것이기 때문에인라인 정책 생성이 아닌정책 연결을 선택해 주는 것이다.)
AWSGlueConsoleFullAccess정책을 검색한 후권한 추가버튼을 통해 추가해 준다.
❓ AWS Glue란
AWS에서 제공하는Serverless ETL서비스이다.AWS Glue는AWS에 최적화된 형태로ETL을 사용하고 싶다면 적합하지만 외부에서는 사용할 수 없다.- Glue가 제공하는 기능은 다음과 같다.
- 데이터 카탈로그
- 데이터 소스 및 대상의 메타 데이터를 대상으로 검색 기능 제공
- S3나 다른 AWS 서비스 데이터 소스를 대상으로 함
- 외부 테이블을 정의하는 순간 그 정보가
Redshift뿐만 아니라AWS Glue내의데이터 카탈로그에도 저장이 되어야 한다.- ETL
- 간단한 드래그 앤 드롭으로 ETL 작업 생성 가능
- 데이터 소스 및 대상을 선택하고 변환 단계를 정의하는 스크립트 생성
- 작업 모니터링 및 로그
- Serverless 실행
2. 외부 테이블용 스키마 생성
외부 테이블의 데이터들을 저장할 스키마를 따로 생성해 준다.CREATE구문을 통해 생성하면 된다.- 이때
DATABASE는 해당 스키마가 생성되는DATABASE이다. CREATE EXTERNAL DATABASE IF NOT EXISTS이 구문을 통해 만약EXTERNAL DATABASE가 존재하지 않으면 새로 생성해 주도록 설정할 수 있다.
CREATE EXTERNAL SCHEMA EXTERNAL_SCHEMA
FROM DATA CATALOG
DATABASE 'myspectrum_db'
iam_role 'iam role의 arn'
CREATE EXTERNAL DATABASE IF NOT EXISTS;3. 스키마 내에 외부 Fact 테이블 생성
usc라는 폴더를S3 bucket밑에 생성한다.
- 이후 폴더로
csv 파일을 업로드한다.
- 다음과 같은 SQL 구문으로 2에서 만든 외부 테이블용 스키마
EXTERNAL_SCHEMA에Fact 테이블을 생성해 줄 수 있다.
(주로 이런 형태는 Hive/Presto/SparkSQL에서 사용됨) - 해당 SQL 구문을 사용하면
LOCATION뒤에 써 준 주소 내에 있는 모든 파일을 테이블로 읽어 준다. - 그렇기 때문에 하나라도 format이 다르면 오류가 발생하게 된다.
- 특정 폴더 밑에 있는 파일들을 대상으로 테이블을 만드는 것이기 때문에 테이블 생성과 동시에 레코드들이 로딩되는 형태이다.
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 -- 해당 파일이 textfile로 저장되어 있다
LOCATION 'S3 bucket 주소/usc/' --해당 폴더 밑에 있는 파일들이다4. 내부 Dimension 테이블 생성
- 테스트를 위해 사용자에 관한
user_property라는 테이블을raw_data스키마 밑에 생성한다. - 꼭 내부 테이블과 JOIN 할 필요는 없다. 필요에 따라 외부 테이블의 데이터만 사용해 주어도 된다.
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
);5. 내부 Dimension 테이블과 외부 Fact 테이블을 활용해 데이터 추출
- 외부 데이터의 테이블이 제대로 생성되었는지 확인하기 위해 3, 4 단계에서 생성한 데이터를 이용해 보자.
- 사용자들의 성별로 몇 명이나 있는지 확인해 본다.
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;
- 다음과 같이 사용자의 각 성별에 해당하는 인원 수를 확인할 수 있다.
송의 개발 LOG