[DEV] 8주차. 데이터 웨어하우스 관리와 고급 SQL과 BI 대시보드(2)
1. Redshift 특징
- AWS에서 지원하는 데이터 웨어하우스 서비스
- 최소 160GB ~ 2PB 데이터까지 처리 가능
- 고정 비용으로 사용할 때!
- SSD 옵션 -> 상대적으로 처리속도가 빠름
- Still OLAP (OnLine Analytical Processing Engine)
- 속도보다는 큰 데이터를 처리하는 데 집중 -> 프로덕션 데이터베이스로 사용 불가
- 컬럼 기반 스토리지
- 레코드별로 저장하는 것이 아니라, 컬럼별로 저장함
- 컬럼별 압축이 가능 -> 컬럼 추가나 삭제가 아주 빠름
- 벌크 업데이트 지원
- 모든 데이터 웨어하우스가 갖고 있는 특징
- 레코드가 들어있는 파일을 S3로 복사 후
COPY커맨드로 Redshift로 일괄 복사
- 고정 용량/비용 SQL 엔진
- 최근 가변 비용 옵션도 제공 (Redshift Serverless)
- 데이터 공유 기능 (Datashare)
- 다른 AWS 계정과 특정 데이터 공유 가능
- Snowflake의 기능 (데이터로 매출을 올릴 수 있는 기능)
- 다른 데이터 웨어하우스처럼 primary key uniqueness를 보장하지 않음
- 프로덕션 데이터베이스들은 보장함
Redshift는 SQL 기반 관계형 데이터베이스
- PostgreSQL 8.x와 SQL이 호환됨
- but, 모든 기능을 지원하지는 않음
- ex) text타입이 존재하지 않음
- PostgreSQL 8.x를 지원하는 툴이나 라이브러리로 액세스 가능
- JDBC/ODBC
- 다시 한 번 SQL이 메인 언어라는 점 명심!
- 그래서 데이터 모델링 (테이블 디자인)이 아주 중요
Redshift 스케일링
- 용량이 부족해질 때마다 새로운 노드를 추가하는 방식으로 스케일링
- ex) Scale Out 방식과 Scale Up 방식
- Scale Out: 한 대 더 추가
- Scale Up: 사양이 더 좋은 것으로 교체
-
이를 Resizing이라 부르며, Auto Scaling 옵션을 설정하면 자동으로 이뤄짐
- 이는 Snowflake이나 BigQuery의 방식과는 굉장히 다름
- Redshift는 특별히 용량이 정해져있지 않고, 쿼리를 처리하기 위해 사용한 리소스에 해당하는 비용 지불
- 즉, Snowflake와 BigQuery가 훨씬 더 scalable한 데이터베이스 기술이라고 볼 수 있음
- 단점: 비용 예측이 불가능함
- Redshift에서도 가변비용 옵션 존재 : Redshift Serverless
Redshift 최적화: 매우 복잡
- 두 대 이상의 노드로 구성되면 한 테이블의 레코드들의 저장방식은
- 분산 저장되어야 함
- 여러 개의 레코드를 여러 개의 노드에 분산 저장
- 노드 내의 레코드 수가 크게 차이나면 processing에서 속도 차이가 크게 남
- 또, 한 노드 내에서는 순서가 정해져야 함
- 분산 저장되어야 함
- Snowflake, BigQuery는 클러스터가 몇 개의 노드로 구성되어 있는지 개발자가 알 수도 없고 알 필요도 없음
Redshift의 레코드 분배와 저장 방식
-
한 테이블의 레코드들을 어떻게 다수의 노드로 분배할 것인지!
-
Distkey, Diststyle, Sortkey 를 알아야 함!
Diststyle: 레코드 분배가 어떻게 이뤄지는지를 결정- all: 모든 레코드들이 모든 노드에 들어감
- even: default, 라운드 로빈 형태로 돌아가면서 레코드 하나씩 들어감
- key: 특정 컬럼의 값을 기준으로 분배 (특정 컬럼의 값이 같은 레코드들은 같은 노드로 들어감)
Distkey: 레코드가 어떤 컬럼을 기준으로 배포되는지 나타냄Diststyle이 key인 경우
Sortkey: 레코드가 한 노드 내에서 어떤 컬럼을 기준으로 정렬되는지 나타냄- 이는 보통 timestamp 필드가 됨
Diststyle이 key인 경우 컬럼 선택이 잘못되면- 레코드 분포에 Skew가 발생 -> 분산 처리의 효율성이 사라짐
CREATE TABLE my_table (
col1 INT,
col2 VARCHAR(50),
col3 TIMESTAMP,
col4 DECIMAL (18,2)
) DISTSYTLE KEY DISTKEY (col1) SORTKEY (col3);
- col1 값을 기준으로 분배되고, 노드 안에서 col3 값을 기준으로 정렬됨
- join을 많이하거나 group by를 많이 하는 키를 Distkey로 사용하는 것이 좋음
- but, skew가 된 key라면 그냥 default값인 even을 쓰는 것이 나음
Redshift 벌크 업데이트 방식 - COPY SQL
- Snowflake, BigQuery 모두 유사한 방식
Redshift 기본 데이터 타입
-
기본적으로 PostgreSQL에 있는 타입들
- SMALLINT (INT2)
- INTEGER (INT, INT4)
- BIGINT (INT8)
- DECIMAL (NUMERIC)
- REAL (FLOAT4)
- DOUBLE PRECISION (FLOAT8)
- BOOLEAN (BOOL)
- CHAR (CHARACTER)
- character 단위는 BYTE !
- JSON: CHAR로 받아온 뒤 JSON 함수로 parsing
- VARCHAR (CHARACTER VARYING)
- 최대 65,535 BYTE
- TEXT (VARCHAR(256))
- 최대 256 BYTE : 그다지 크지 않음
- DATE
- TIMESTAMP
고급 데이터 타입
- GEOMETRY
- GEOGRAPHY
- HLLSKETCH
- SUPER
2. Redshift 설치
- serverless는 3개월 혹은 $300 까지 무료
- 서울은 비싸기 때문에 보통 오레곤 / 버지니아 북부 선택
Google Colab 연결
1) 엔드포인트 확인
- [Workgroup] - [defalut-workgroup] - [Endpoint] 복사
2) admin 설정
- [Namespace] - [default-namespace] - [작업] - [Edit admin credentials]
- 계정 이름은
admin, 암호 설정
- 계정 이름은
3) 필요한 정보
- 엔드포인트
- 계정 이름
- 계정 암호
time out error
- pubilc access를 설정해야 함!
1) [Workgroup] - [defalut-workgroup] - [Network and security] - [퍼블릭 액세스 가능] 활성화!
2) [VPC 보안 그룹] - [인바운드 규칙 편집] - [규칙 추가] - 포트 번호 5439 를 0.0.0.0/0 에 오픈!
연결 완료
%load_ext sql
%sql postgresql://[계정이름]:[암호]@[엔드포인트]
- 버전 다운그레이드 필요!
!pip install ipython-sql==0.4.1
!pip install SQLAlchemy==1.4.49
- Restart Session 후 다시 위 코드 실행
3. Redshift 초기 설정
Redshift Schema
- 다른 기타 RDB와 동일한 구조
- 목적: 테이블이 어떤 스키마에 들어가있는지를 보고 context를 파악할 수 있도록 구성하면 됨
- pii (개인정보) 스키마는 되도록 사용을 안하는 것이 좋고, 꼭 사용해야 하는 경우에는 접근할 수 있는 사용자를 최소한으로 하여 조심스럽게 사용
스키마설정
- 스키마 생성:
CREATE SCHEMA- 실행하는 사람 권한이 admin이어야 함!
CREATE SCHEMA raw_data;
CREATE SCHEMA analytics;
CREATE SCHEMA adhoc;
CREATE SCHEMA pii;
- 모든 스키마 리스트
select * from pg_namespace;
사용자 생성
CREATE USER [name] PASSWORD '[pwd]';
- 모든 사용자 리스트
select * from pg_user;
그룹 생성 및 설정
- 사용자별로 테이블 접근 권한을 부여해야 함
- 테이블 수가 많아지면 테이블-사용자별로 정의하는 것이 불가능해짐
- 테이블별로 정하는 것이 아니라 스키마별로 권한을 정하고, 사용자별로 정하는 것이 아니라 그룹이라는 템플릿을 만들어 그룹별로 부여하는 것이 일반적
- 그룹-스키마 간의 접근 권한 관리
- 한 사용자는 다수의 그룹에 속할 수 있음
- 그룹은 계승이 되지 않음 -> 너무 많은 그룹을 만들게 되고 관리가 힘들어짐
- 예를 들어 아래와 같은 그룹이 존재
- admin을 위한
pii_users - 데이터 분석가를 위한
analytics_authors - 데이터 활용을 하는 개인을 위한
analytics_users
- admin을 위한
- 이때
analytics_users권한을 바탕으로analytics_authors만의 권한을 추가하여 그룹을 만들면 좋겠지만, 그렇게 할 수 없고analytics_authors의 권한을 처음부터 새로 작성해야 함
- 그래서 나온 것이 role!
- 그룹 생성:
CREATE GROUP - 그룹에 사용자 추가:
ALTER GROUP [그룹이름] ADD USER [사용자이름] - 모든 그룹 리스트:
SELECT * FROM pg_group;
역할 생성 및 설정
- 역할은 그룹과 달리 계승 구조를 만들 수 있음
- 역할은 사용자에게 부여될 수도 있고, 다른 역할에 부여될 수도 있음
- 한 사용자는 다수의 역할에 소속 가능함
CREATE ROLE staff;
CREATE ROLE manager;
CREATE ROLE external;
GRANT ROLE staff TO bokyung;
GRANT ROLE staff TO ROLE manager;
- 모든 역할 리스트
SELECT * FROM SVV_ROLES;
4. Redshift COPY 명령으로 레코드 적재
- 각 테이블을 raw_data 스키마 밑에 생성
- 각 테이블의 입력이 되는 CSV 파일을 S3로 먼저 복사
- S3 버킷부터 생성
- S3에서 해당 테이블로 복사하려면 Redshift가 S3 접근권한을 가져야 함
- S3를 접근할 수 있는 역할 생성 (IAM)
- 이 역할을 Redshift 클러스터에 지정 (Redshift)
raw_data 테스트 테이블 생성
- 보통 이런 테이블들은 ETL을 통해 데이터 소스에서 복사해오는 형태로 이루어짐
CREATE TABLE raw_data.user_session_channel (
userid integer,
sessionid varchar(32) primary key,
channel varchar(32)
);
CREATE TABLE raw_data.session_timestamp (
sessionid varchar(32) primary key,
ts timestamp
);
CREATE TABLE raw_data.session_transaction (
sessionid varchar(32) primary key,
refunded boolean,
amount int
);
- 세 테이블을 join하면 한 세션에 대한 모든 정보를 알 수 있음
S3 버킷 생성과 파일 업로드
- Redshift의 COPY SQL을 사용하여 3개의 테이블 내용 적재
- CSV 파일 다운로드
- AWS 콘솔에서 S3 bucket 만들고 업로드
Redshift에 S3 접근 권한 설정
- AWS IAM을 이용해 이에 해당하는 역할을 만들고, 이를 Redshift에 부여해야 함
-
IAM에서 역할 생성
-
Redshift에게 줄 권한임
-
Redshift가 갖게 될 권한 선택
-
[Redshift] - [namespace] - [default-namespace]
-
[IAM 역할 연결] - 생성한 역할 연결
S3의 CSV 파일을 테이블로 복사 (벌크 업데이트)
- COPY SQL 커맨드 사용
https://docs.aws.amazom.com/redshift/lastest/dg/r_COPY.html 참조
- CSV 파일 -> delimiter로
,사용 - 문자열이 따옴표로 둘러싸인 경우를 제거하기 위해
removequotes지정 - 첫번째 라인(헤더)를 무시하기 위해
IGNOREHEADER 1지정 - credentials에 앞서 Redshift에 지정한 Role 사용 -> 해당 Role의 ARN을 읽어와야 함
COPY raw_data.user_session_channel
FROM 's3://'
credentials 'aws_iam_role=[역할 ARN]'
delemiter ',' dateformat 'auto' timeformat 'auto' IGNOREHEADER 1 removequotes;
- COPY 명령 실행 중 에러가 발생하면 stl_load_errors 테이블의 내용을 보고 확인
SELECT * FROM stl_load_errors ORDER BY starttime DESC;
analytics 테스트 테이블 생성
- raw_data에 있는 테이블 조인해서 새로 만들기 (ELT)
- 간단하게는 CTAS로 가능
CREATE TABLE analytics.mau_summary AS
SELECT
TO_CHAR(A.ts, 'YYYY-MM') AS month,
COUNT(DISTINCT B.userid) AS mau
FROM raw_data.session_timestamp A
JOIN raw_data.user_session_channel B ON A.sessionid = B.sessionid
GROUP BY 1
ORDER BY 1 DESC;