[Week8 AWS 데이터웨어하우스] TIL 32일차 - Redshift와 S3실습

Ⅰ. Redshift의 특징

1. 고정 비용 옵션

160GB~2PB까지 가능

HDD대신 SSD를 사용하여 속도가 빠른 편(그래도 프로덕션 데이터베이스에 비해서는 속도가 느림)

컬럼기반 스토리지이기 때문에 컬럼별 압축이 가능하기 때문에, 압축률이 높고 성능이 좋은 편

벌크 업데이트 가능(레코드를 S3에 저장 후 COPY 옵션으로 일괄 적재)

데이터 공유 기능(AWS 특정 계정과 특정 데이터를 공유할 수 있다.) Snowflake도 가능

데이터 웨어하우스이기 때문에 primary key uniqueness를 보장하지 않음

Postgresql과 호환이 가능(JDBC/ODBC)으로 액세스 가능

 

2. 가변 비용 옵션(serverless)도 존재한다.

 

3. 비용

1) Dense  Storage (HDD)

저장에 집중

2) Dense Compute(SSD)

계산에 집중

3) Managed Storage(SSD)

저장+계산에 모두 집중

 

cf) 데이터 웨어하우스의 성능을 높이는 옵션(Auto-scaling)

Scale-up : 더 좋은 사양의 서버로 업그레이드

Scale-out : 서버를 한대 더 추가하는 것(보편적으로 해당 방식을 더 많이 사용)

 

4. 단점

최적화가 어려움(<->BigQuery, Snowflake는 저장과정을 자동적으로 처리)

레코드 분포에 SKEW 발생 : 어느 노드에 저장해야 하는지 사용자가 지정한다. 한쪽 노드로 데이터가 몰리면, 해당 노드만 처리 속도가 느려져 분산 처리의 효율성이 사라진다.

 

5. 분배 옵션

Diststyle 

레코드 분배가 어떻게 되는지를 선택하는 옵션

• all : 모든 노드에 모든 레코드를 적재(하나의 레코드가 모든 노드에 저장)
• even : round-robin 방식으로 하나씩 돌아가며 레코드에 적재(하나의 레코드가 하나의 노드에 저장)
• key : 특정 컬럼의 값을 기준으로 적재(보통, unique column 기준)
  • distkey

Sortkey

노드 내에서 어떤 컬럼을 기준으로 정렬하는지

보통 TIMESTAMP의 컬럼을 사용

 

적용

Create Table(
...
)DISTSTYLE 스타일이름 DISTKEY(컬럼명) SORTKEY(컬럼명);

보통 DISTKEY에는 자주 조회하는 컬럼을 기준으로 하는데, 레코드 분포에 SKEW가 발생하는 경우, 

 

 

Ⅱ. 데이터 웨어하우스의 벌크 업데이트 방식

1. 대용량의 데이터(csv, json, sql)를 압축률이 높은 parquet 파일로 만든다.

cf) Parquet은 분산형 데이터 처리 시스템을 위한 열 지향 형식의 파일 포맷 중 하나이다.

2. A Cron job or Schedular(ex. Airflow)를 활용해서 대용량 스토리지(S3)에 업로드한다.

3. 벌크 업데이트 : 대용량 스토리지에 저장된 파일을 COPY SQL로 Redshift로 한 번에 복사한다.

 

 

Ⅲ. 지원하는 데이터 타입

다른 건 Postgresql과 동일(char, varchar, text, json은 다름)

Postgresql의 경우 char, varchar, text는 utf-8 기준(한 글자는 한 char)

Redshift의 경우 byte단위(언어에 따라 다름. 한중일=3byte, 영문=1byte). 언어에 따라 저장할 수 있는 형식이 다름

Json의 경우 Redshift에서 제공하지 않기 때문에 Char로 받아와서 이후에 함수로 Parsing을 수행해야 한다.

 

고급 데이터 타입

1. GEOMETRY:공간 데이터를 저장하는 데 사용. 지리적인 형상이나 공간 데이터를 나타내기 위한 것으로, 포인트, 라인, 폴리곤 등의 다양한 지오메트리 타입을 다룰 수 있고, 지도상의 위치를 저장하거나 지리적인 영역을 정의하는 데 사용.
2. GEOGRAPHY: GEOMETRY보다, 더 넓은 지리적 영역을 다룬다. 지구상의 위치로 표현하며, 경위도 좌표를 사용.
3. HLLSKETCH: HyperLogLog(하이퍼로그로그) 스케치를 나타내는 것으로 대량의 고유한 값을 카운트하는 데 사용되는 확률적인 알고리즘. 대용량의 데이터 세트에서 고유한 값을 추정하는 데 효과적이며, 주로 대규모 데이터 분석 및 집계 작업에서 사용.
4. SUPER: 사용자 정의 데이터형을 만들 때 사용되는 일종의 placeholder(자리 표시자)

 

 

Ⅳ. Redshift 생성하기

free trial를 지원하는 Redshift serverless(가변옵션)을 이용하자.

(use default settings를 사용하여 간편하게 세팅하였다.)

 

이제 AWS redshift와 colab을 연결해 보자.

두 가지 작업이 필요한데, AWS의 엔드포인트 정보를 확인하고(Hostname, port, DBname) Access 권한을 설정해야 한다.

 

1. AWS 엔드포인트

- 퍼블릭 액세스 설정

Serverless 대시보드> 작업그룹> 네트워크 및 보안>편집>퍼블릭액세스가능 체크

퍼블릭 액세스 설정

 

- VPC 보안 그룹 설정 변경

현재는 같은 VPC 내의 서비스에만 접근이 가능한데, 외부에서 접근이 가능하게 설정해 보자.

Serverless 대시보드>작업그룹>네크워크 및 보안> VPC 보안 그룹> 인바운드 규칙> 인바운드 규칙 편집

 

다음과 같은 인바운드 규칙을 추가한다.

인바운드 규칙 추가(5439)

 

- 앤드포인트 복사

Serverless 대시보드> 작업그룹> 앤드 포인트 복사

앤드포인트 확인

 

 

2. Access 권한 설정

admin 계정으로 접근하거나, IAM의 권한을 설정한 새 계정으로 접근할 수 있다.

해당 실습에서는 admin계정으로 접근하도록 한다.

1) Admin Userid/password

Serverless 대시보드 > 네임스페이스 > 작업 > Edit admin credential 선택

 

사용자가 직접 암호를 설정할 수 있도록 다음의 과정을 선택한다.

admin, password 지정

2) Admin + IAM을 통해 다른 Account 계정 만들기

 

 

Ⅴ. Colab에 연결하기

!pip install ipython-sql==0.4.1
!pip install SQLAlchemy==1.4.49

%load_ext sql
%sql postgresql://어드민계정명:비밀번호@endpoint붙여넣기

%%sql
SQL문장실행

 

 

 

Ⅵ. Redshift 초기 설정하기

0. db설정, 정보 확인

Ⅳ 과정에서 dev 이름의 데이터베이스를 생성하였다.

다음에 생성할 것들에 대한 모든 정보는 특정 테이블에 기록되어 있다.

pg_namespace	생성된 스키마에 대한 정보를 가진 테이블 (schema이름, 해당 스키마를 소유한 사람, 권한을 가진 사용자의 id, 사용자의 권한 정보)
pg_user	생성된 사용자에 대한 정보를 가진 테이블
pg_group	생성된 그룹에 대한 정보를 가진 테이블
SVV_ROLES	생성된 역할에 대한 정보를 가진 테이블

 

보통 사용자, 테이블이 굉장히 많아지므로 그룹-스키마 별로 접근권한을 제어한다.

1. Schema

테이블을 저장하는 폴더의 역할로, 보통 다음의 4개의 스키마가 기본으로 있다.

- row_data : ETL 과정의 데이터를 모두 저장

- analytics : row_data에서 데이터 활용에 필요한 테이블, 테이블 조인 등의 결과를 저장

- adhoc : 개발/테스트용 테이블이 저장

- pii : 개인정보 관련 테이블이 저장 (액세스 할 수 있는 사람은 최소화 + 접근한 사람 로깅=추적 가능하게)

- 스키마 생성

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

 

2. 그룹/사용자 생성

- 사용자 생성

CREATE USER 사용자이름 PASSWORD '비밀번호';

 

- 그룹 생성(상속 불가)

보통 다음과 같은 이름으로 그룹을 생성하고 권한을 부여한다.

XXX_users : read

XXX_authors : read/write

pii_users: admin(개인정보 접근)

CREATE GROUP 그룹이름;

 

- 그룹에 사용자 추가

ALTER GROUP 그룹이름 ADD USER 사용자이름;

 

- 그룹에 스키마/테이블 접근 권한 지정

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA 스키마이름 TO 그룹이름;
or
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE 테이블이름 TO 그룹이름;

 

3. 역할

그룹의 경우, 계승이 불가능하기 때문에 모든 권한을 지정해야 한다.

역할의 경우 상속(계승)이 가능하기 때문에 한 역할에 있는 기능을 모두 새로운 역할에 부여하여 필요한 역할만 추가로 지정하면 된다.

- 역할 생성

CREATE ROLE 역할이름;

 

- 역할 부여(사용자, 그룹, 역할)

GRANT ROLE 역할 TO 사용자 or 그룹 or 역할;

 

Ⅶ. 벌크 업데이트 하기( at raw_data 스키마 )

📁raw_data 스키마에 COPY명령어를 사용해서 3개의 테이블을 통째로 복사해 보자.

 

1. CSV 파일을 AWS S3 버킷에 업로드

1) S3 버킷 생성

AmazonS3> 버킷> 버킷 만들기에서 Redshift와 동일한 지역에 버킷을 하나 생성한다.(나머지 구성은 기본 구성을 선택하였다.)

S3 버킷 생성

2) 파일 업로드 

🧺test_s3bucket

    📁test

        📍channel.csv

        📍session_timestamp.csv

        📍session_transaction.csv

        📍user_session_channel.csv

 

업로드된 파일에 접근하면, S3 URL을 확인할 수 있는데 해당 주소로 접근할 수 있다.

 

2. 권한 설정

1) Redshift가 S3에 접근할 수 있는 IAM 생성

IAM> 역할> 역할생성

신뢰할 수 있는 엔티티 유형 - AWS 서비스, 사용하례 - Redshift(Customizable) 선택

IAM 역할 생성

 

AmazonS3FullAccess과 같은 권한을 추가한다.

역할 이름은 redshift.read.s3로 명명한다.

역할 권한(정책) 추가

 

 

2) Redshift에 역할부여

Serverless대시보드> 네임스페이스 선택> 보안 및 암호화> IAM 역할관리

해당 페이지에서 IAM 역할 연결을 선택한다(아까 생성한 redshift.read.s3 역할에 연결)

IAM 역할 연결

 

3. COPY 명령어를 사용해서 row_data 스키마에 테이블을 복사

다음 주소에서 Redshift에서 사용되는 구문들을 참고해 보자.

COPY - Amazon Redshift

1) 테이블 생성

우선, COPY 할 테이블을 생성한다.(칼럼 등을 지정)

2) 벌크 업데이트

COPY 스키마.테이블이름 
[ 컬럼목록 ]
FROM 'S3업로드된파일 S3 URI'
credentials 'aws_iam_role=IAM ARN 붙여넣기'
delimiter ',' dateformat 'auto' timeformat 'auto' IGNOREHEADER 1 removequotes;

 

- S3업로드된 파일 S3 URI

S3버킷의 파일의 주소를 입력한다.

- IAM ARN부분

IAM> 역할> redshift.read.s3선택> ARN 복사 후 붙여 넣기를 수행한다.

IAM ARN 확인

- 마지막 문장

delimiter ', ' : csv 파일이므로 콤마를 기준으로 구분

IGNOREHEADER 1 : 첫 번째 줄은 헤더이므로 무시한다.

dateformat 'auto' timeformat 'auto' : date, time 형식은 여러 형식 중 redshift가 자동 지정하도록 한다.

removequotes : 따옴표 제거

 

4. COPY 에러

COPY 과정 중 오류가 발생하면,

stl_load_errors 테이블의 제일 최근 레코드를 확인하여 어떤 컬럼에서 오류가 발생하는지 확인해 보자.

SELECT *
FROM stl_load_errors
ORDER BY starttime DESC;

 

 

Ⅷ. (at analytics 스키마 ) CTAS 사용하기

CREATE TABLE analytics.mau_summary AS
SELECT ~
FROM ~