[AWS RedShift] 8. Redshift Spectrum으로 S3 외부 테이블 조작

📌 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;

• 다음과 같이 사용자의 각 성별에 해당하는 인원 수를 확인할 수 있다.