[TIL - 32일 차] 데이터 웨어하우스 관리와 고급 SQL과 BI 대시보드 (2)

Redshift 소개

Redshift 특징

Redshift 특징 (1)

• AWS에서 지원하는 데이터 웨어하우스 서비스
• 2 PB의 데이터까지 처리 가능
• Still OLAP : 응답 속도보다 처리 용량에 최적화되어 있음
• 컬럼 기반 스토리지 : 레코드 별로 저장하는 것이 아니라 컬럼 별로 저장하며, 컬럼 별 압축 가능

Redshift 특징 (2)

• 벌크 업데이트 지원 : 레코드가 들어있는 파일을 S3에 복사 후 COPY 커맨드로 Redshift로 일괄 복사 (BigQuery, Snowflake 등 다른 것도 동일)
• 고정 용량/비용 SQL 엔진 : 최근 가변 옵션도 제공 (Redshift Serverless)
• 데이터 공유 기능 (Datashare) : 다른 AWS 계정과 특정 데이터 공유 가능
• PK uniqueness를 보장하지 않음 (BigQuery, Snowflake 등 다른 것도 동일)

Redshift는 SQL 기반 관계형 데이터베이스

• Postgresql 8.x와 SQL 호환 : 모든 기능을 지원하지는 않음
• Postgresql 8.x를 지원하는 툴이나 라이브러리로 액세스 가능(JDBC/ODBC)
• SQL이 메인언어 임을 기억하자 => 데이터 모델링(테이블 디자인)이 중요

Redshift 스케일링 방식 (1)

• Scale Out : dc2.large를 사용하다가 공간이 부족해지면, dc2.large를 한 대 더 추가
• Scale Up : 더 좋은 사양의 서비스로 업그레이드, dc2.8xlarge 한 대로 교체

Redshift 스케일링 방식 (2)

Snowflake나 BigQuery의 방식과는 굉장히 다르다.

• Snowflake와 BigQuery는 가변 비용으로 사용한 리소스에 해당하는 비용 지불
• Snowflake와 BigQuery가 더 스케일한 데이터베이스 기술
• 비용 예측이 불가능하다는 단점 존재

RedShift 최적화

Redshift가 두 대 이상의 노드로 구성되면 한 테이블의 레코드들의 저장 방식은 개발자가 적절히 분산 저장되도록 지정해줘야 하므로 최적화가 복잡하다. 그러나 Snowflake, BigQuery는 이를 고려할 필요가 없다.

Redshift 레코드 분배와 저장 방식 (1)

• Distkey : 레코드 분배가 어떻게 이루어지는지 결정, all / even / key (default = even)
  • all : 모든 레코드가 모든 노드에 복제
  • even : round-robin 방식
  • key : 특정 컬럼의 값을 기준으로 분배, skew가 발생하지 않게 key를 잘 설정해야 함
• Diststyle : 레코드가 어떤 컬럼을 기준으로 배포되는지 나타냄
• Sortkey : 레코드가 한 노드 내에서 어떤 컬럼을 기준으로 정렬되는지 나타냄, 일반적으로 timestamp

Distkey

Redshift 레코드 분배와 저장 방식 예 - Distkey (key)

CREATE TABLE my_table (
 column1 INT,
 column2 VARCHAR(50),
 column3 TIMESTAMP,
 column4 DECIMAL(18,2)
) DISTSTYLE KEY DISTKEY(column1) SORTKEY(column3);

Redshift 벌크 업데이트 방식 - COPY SQL

벌크 업데이트 방식은 Redshift 뿐만 아니라 BigQuery, Snowflake 등 다른 데이터 웨어하우스 서비스도 동일하다.

1. 소스로부터 데이터 추출
2. S3에 업로드 (보통 Parquet 포맷을 선호)
3. COPY SQL로 S3에서 Redshift 테이블로 한 번에 복사

Redshift 기본 데이터 타입

• SMALLINT (INT2)
• INTEGER (INT, INT4)
• BIGINT (INT8)
• DECIMAL (NUMERIC)
• REAL (FLOAT4)
• DOUBLE PRECISION (FLOAT8)
• BOOLEAN (BOOL)
• CHAR (CHARACTER)
• VARCHAR (CHARACTER VARYING) : 영어는 1byte, 한/중/일어은 3 byte / PostgreSQL은 둘 다 1 byte(utf-8)
• TEXT (VARCHAR(256)) : 최대 65535 byte까지 담을 수 있음, default=256
• DATE
• TIMESTAMP

Redshift 고급 데이터 타입

• GEOMETRY
• GROGRAPHY
• HLLSKETCH
• SUPER

Redshift 설치

Redshift에 연결하기 위한 설정

• Admin 계정 / 패스워드 설정
• Publicly accessible On
• VPC 인바운드 규칙에 port를 5439(Redshift port), source를 0.0.0.0/0으로 설정

Redshift 연결 in Colab

SQLAlchemy가 업그레이드 되면서 Redshift와 충돌이 발생한다. 따라서 현재 downgrade 해줘야 접속이 가능하다.

%load_ext sql
!pip install SQLAlchemy==1.4.47
%sql postgresql://admin:"password":@"end-point"

Redshift 초기 설정

Redshift Schema

다른 관계형 데이터베이스와 동일한 구조이다. 목적에 맞게 카테고리(폴더)를 만들고, 내부에 테이블을 저장한다.

• 데이터베이스(DB) : DEV
• 스키마(Schema) : raw_data, analytics, adhoc, pii
• 테이블 : 스키마 내부에 저장되는 데이터

Redshift Schema

스키마(Schema) 설정

CREATE SCHEMA raw_data;
CREATE SCHEMA analytics;
CREATE SCHEMA adhoc;
CREATE SCHEMA pii;

모든 스키마를 확인하는 코드는 다음과 같다.

select * from pg_namespace;

사용자(User) 생성

CREATE USER name PASSWORD '...';

모든 사용자를 확인하는 코드는 다음과 같다.

select * from pg_user;

그룹(Group) 생성 / 설정 (1)

한 사용자는 다수의 그룹에 속할 수 있다. 그룹은 계승이 안되기 때문에 많은 그룹을 만들어야 하며 관리가 힘들어진다.

• 관리자를 위한 pii_users
• 데이터 분석가를 위한 analytics_authors
• 데이터 활용을 하는 개인을 위한 analytics_users

그룹

그룹(Group) 생성 / 설정 (2)

CREATE GROUP analytics_users;
CREATE GROUP analytics_authors;
CREATE GROUP pii_users;

ALTER GROUP analytics_authors ADD USER name;
ALTER GROUP analytics_users ADD USER name;
ALTER GROUP pii_users ADD USER name;

모든 그룹을 확인하는 코드는 다음과 같다.

select * from pg_group;

역할(Role) 생성 / 설정

역할은 그룹과 달리 계승 구조를 만들 수 있다. 사용자에게 부여될 수도 있고 다른 역할에 부여될 수도 있다. 한 사용자는 다수의 역할에 소속할 수 있다.

CREATE ROLE staff;
CREATE ROLE manager;
CREATE ROLE external;

GRANT ROLE staff TO name;
GRANT ROLE staff TO ROLE manager; # manager = manager + staff

모든 역할을 확인하는 코드는 다음과 같다.

select * from SVV_ROLES;

Redshift COPY 명령으로 테이블에 레코드 적재하기

COPY와 AWS IAM 학습을 바탕으로 테이블에 레코드를 적재하는 실습을 진행한다.

1. 각 테이블을 CREATE TABLE 명령으로 raw_data 스키마 밑에 생성
2. S3 버킷 생성 및 테이블의 입력이 되는 CSV 파일을 S3에 복사
3. Redshift가 S3 접근권한을 가지도록 설정

raw_data 테이블 생성

user_session_channel, session_timestamp, session_transaction 총 3개의 테이블을 생성한다.

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

S3 버킷 생성 및 파일 업로드

Redshift에 S3 접근 권한 설정 - AWS IAM

Redshift가 앞서 만든 S3 버킷에 접근할 수 있어야 하므로 AWS IAM을 이용해 이에 해당하는 역할을 만들고 Redshift에 부여해야 한다.

1. AWS IAM 페이지에서 Roles 선택
2. Create Role 선택
3. AWS service의 Redshift 선택 (Redshift에게 권한 부여)
4. Redshift - Customizable 선택
5. Filter policies에서 AmazonS3FullAccess를 선택
6. 강의에서는 이름을 'redshift.read.s3'으로 지정하고 생성
7. Redshift 콘솔로 돌아가 Default Namespace -> Security and encryption -> Manage IAM roles 선택
8. Associate IAM roles -> 생성했던 IAM roles 선택

COPY 명령으로 CSV 파일을 테이블로 복사

COPY SQL 문법은 아래 사이트를 확인하면 된다.

COPY - Amazon Redshift

csv 파일이기에 delimiter로는 콤마(,)로 지정하였고, 문자열이 따옴표로 둘러싸인 경우 제거하기 위해 removequotes를 지정한다. 또한 첫 라인(헤더)을 무시하기 위해 IGNOREHEADER 1을 지정한다. role을 지정하였지만, 여기서 다시 credentials를 사용하여 지정했던 role을 적어줘야 한다. 파일 경로를 수정하여 총 3번을 실행하면 원했던 결과물을 얻을 수 있을 것이다.

COPY raw_data.user_session_channel
FROM 's3://keeyong-test-bucket/test_data/user_session_channel.csv' # 파일 경로
credentials 'aws_iam_role=arn:aws:iam:xxxxxxx:role/redshift.read.s3'
delimiter ',' dateformat 'auto' timeformat 'auto' IGNOREHEADER 1 removequotes; # 첫 번째 line 무시, 따옴표 제거

만일 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;