📒 AWS(5) - RedShift(2)

📌 사용자 그룹 권한 설정

	analytics_authors	analayics_users	pii_users	admin
raw_data Tables	Read	Read	X	Read, Write
analytics Tables	Read, Write	Read	X	Read, Write
adhoc Tables	Read, Write	Read, Write	X	Read, Write
pii Tables	X	X	Read	Read, Write

위 표 처럼 권한을 설정해 보자.

• analytics_authors

GRANT ALL ON SCHEMA analytics TO GROUP analytics_authors;
GRANT ALL ON ALL TABLES IN SCHEMA analytics TO GROUP analytics_authors;

GRANT ALL ON SCHEMA adhoc to GROUP analytics_authors;
GRANT ALL ON ALL TABLES IN SCHEMA adhoc TO GROUP analytics_authors;

GRANT USAGE ON SCHEMA raw_data TO GROUP analytics_authors;
GRANT SELECT ON ALL TABLES IN SCHEMA raw_data TO GROUP analytics_authors;

• analytics_users

GRANT USAGE ON SCHEMA analytics TO GROUP analytics_users;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO GROUP analytics_users;

GRANT ALL ON SCHEMA adhoc to GROUP analytics_users;
GRANT ALL ON ALL TABLES IN SCHEMA adhoc TO GROUP analytics_users;

GRANT USAGE ON SCHEMA raw_data TO GROUP analytics_users;
GRANT SELECT ON ALL TABLES IN SCHEMA raw_data TO GROUP analytics_users;

• pii_users

GRANT USAGE ON SCHEMA pii TO GROUP pii_users;
GRANT SELECT ON ALL TABLES IN SCHEMA pii TO GROUP pii_users;

---

📌 Redshift Spectrum - S3 외부 테이블 조작

• Fact Table : 분석의 초점이 되는 양적 정보를 포함하는 중앙 테이블
  • 일반적으로 매출 수익, 판매량 또는 이익과 같은 사실 또는 측정 항목을 포함하며 비즈니스 결정에 사용한다.
  • Fact Table은 일반적으로 외래 키를 통하여 여러 Dimension Table과 연결된다.
  • 보통 Fact Table의 크기가 훨씬 크다.
• Dimension Table : Fact Table에 대한 상세 정보를 제공하는 테이블
  • 고객, 제품과 같은 테이블로 Fact Table에 대한 상세 정보 제공
  • Fact Table의 데이터에 맥락을 제공하여 사용자가 다양한 방식으로 데이터를 조각내고 분석 가능하게 해준다.
  • Dimension Table은 일반적으로 Primary Key를 가지며, Fact Table의 Foreign Key에서 참조한다.
  • 보통 Dimension Table의 크기는 훨씬 작다.

• 외부 테이블(External Table)
  • DB 엔진이 외부에 저장된 데이터를 마치 내부 테이블처럼 사용하는 방법
    -> 외부 테이블은 외부에 저장된 대량의 데이터를 DB내부로 복사하고 쓰는 것이 아닌 임시 목적으로 사용하는 방식이다.
  • SQL 명령어로 DB에 외부 테이블 생성이 가능하다.
    -> 데이터를 새로 만들거나 하는 것이 아니라 참조만 하게 된다.
    -> 외부 테이블은 CSV, JSON, XML과 같은 파일 형식 뿐만 아니라 ODBC or JDBC 드라이버를 통해 액세스하는 원격 DB와 같은 다양한 데이터 소스에 대해 사용 가능하다.
  • 외부 테이블을 사용하여 데이터 처리 후 결과를 DB에 적재하는데 사용 가능하다.
    -> 예를 들어, 외부 테이블을 사용하여 로그 파일을 읽고 정제된 내용을 DB Table에 적재 가능하다.
  • 외부 테이블은 보안 및 성능 문제에 대해 신중한 고려가 필요하다.
  • HIVE등에서 처음 시작한 개념으로 이제는 대부분의 빅 데이터 시스템에서 사용된다.

• Redshift Spectrum 사용방식
  • S3에 있는 파일들을 마치 테이블처럼 SQL로 처리 가능
    • S3파일들을 외부 테이블들로 처리하면서 Redshift 테이블과 조인 가능
    • S3 외부 테이블들은 보통 Fact Table이 되고 Redshift Table은 Dimension Table이 된다.
  • Redshift Cluster가 필요하다.
    • S3와 Redshift Cluster는 같은 Region에 있어야한다.
  • S3 Fact 데이터를 외부 테이블로 정의해야 한다.

• IAM 정책 추가
  

---

📌 Redshift Spectrum - S3 외부 테이블 조작 실습

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

CREATE EXTERNAL SCHEMA external_schema
FROM data catalog
database 'myspectrum_db'
iam_role 'arn~~~'
CREATE external database if not exists;

• 외부 Fact Table 정의
  • S3에 usc라는 폴더를 각자 S3 버킷 하위에 만든다.
  • usc 폴더에 user_session_channel.csv 파일 복사
  • 아래 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://~~~';

• 내부 Dimension Table 정의

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

• Fact + Dimension Table JOIN

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;