Redshift 실습 (TIL 27)

석형원·2024년 5월 9일
TILredshift

TIL

목록 보기
27/52

✏️ 오늘 학습한 내용

1. Redshift 특징
2. Redshift Serverless 연결 및 실습

🔎 Redshift 특징

  • AWS의 Cloud 기반 빅데이터 웨어하우스 서비스
  • 2PB의 데이터까지 처리 가능
    • 최소 160GB로 시작해서 점진적으로 용량 증감 가능
  • Still OLAP
    • 응답속도가 빠르지 않기 때문에 프로덕션 데이터베이스로 사용불가
  • 컬럼 기반 스토리지
    • 레코드 별로 저장하는 것이 아니라 컬럼 별로 저장
    • 컬럼 별 압축이 가능하며 컬럼을 추가하거나 삭제하는 것이 아주 빠름
  • 벌크 업데이트 지원
    • 레코드가 들어있는 파일을 S3로 저장 후 COPY 커맨드로 Redshift로 일괄 복사
  • 고정 용량/비용 SQL 엔진
    • Redshift Serverless 옵션을 통해 가변 비용 옵션도 가능
  • 데이터 공유 기능 (Datashare) :
    • 다른 AWS 계정과 특정 데이터 공유 가능.
    • Snowflake의 기능을 따라함
  • 다른 데이터 웨어하우스 처럼 primary key uniqueness를 보장하지 않음

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

  • Postgresql 8.x와 SQL이 호환됨
    • 하지만 Postgresql 8.x의 모든 기능을 지원하지는 않음
    • 예를 들어 text 타입이 존재하지 않음
  • Postgresql 8.x를 지원하는 툴이나 라이브러리로 액세스 가능
    • JDBC/ODBC
  • 다시 한번 SQL이 메인 언어라는 점 명심
    • 그래서 데이터 모델링(테이블 디자인)이 아주 중요

📃 Redshift의 스케일링 방식

  • 용량이 부족해질 때마다 새로운 노드를 추가하는 방식으로 스케일링

  • Scale Out방식과 Scale Up 방식

    • dc2.large가 하나면 최대 0.16TB까지의 용량을 갖게됨
    • 공간이 부족해지면
      • dc2.large 한대를 더 추가 -> 총 0.32 TB (Scale Out)
      • 아니면 사양이 더 좋은 것으로 업그레이드 -> dc2.8xlarge 한대로 교체 (Scale Up)
    • Scale Out : 서버 증설
    • Scale Up : 서버 사양 올리기

  • 이를 Resizing이라 부르며 Auto Scaling 옵션을 설정하면 자동으로 이뤄짐

  • 이는 Snowflake나 BigQuery의 방식과는 굉장히 다름

    • 여기서는 특별히 용량이 정해져있지 않고 쿼리를 처리하기 위해 사용한 리소스에 해당하는 비용 지불
    • 즉, Snowflake와 BigQuery가 훨씬 더 유연하게 스케일하는 데이터베이스 기술
    • 장단점 : 비용의 예측이 불가능

  • Redshift의 가변 비용 옵션 -> Redshift Serverless

📃 Redshift 최적화

  • Redshift가 두 대 이상의 노드로 구성되는 경우

    • 한 테이블 내의 레코드들이 다수의 노드로 어떻게 분배가 될 것인지 지정

      • 분산 저장 방식

      • 또한, 노드 내에서 레코드의 순서를 정해줘야합니다.

BigQuery, Snowflake의 경우,
테이블의 크기, 특성에 따라 알아서 관리를 해주기에 개발자는 SQL만 실행을 해주면 됩니다.

하지만 Redshift의 경우,
적어도 고정 비용 옵션을 쓰는 경우, 이 모든 것들을 직접 다 컨트롤 해줘야합니다.

따라서, 최적화가 복잡해지기 시작하고
모든 빅데이터 프로세싱의 큰 문제인 Data skew가 발생할 확률이 높아집니다.

Data skew란?
어떤 테이블에 데이터를 3개의 노드를 나눠 저장하는 경우,
분배가 잘못되면 한 노드에 엄청나게 많은 양의 데이터가 저장이 됩니다.
그 결과,
분산 저장의 의미가 없게 되어 전체적인 처리 시간이 늘어나게 됩니다.

이는, Redshift 만의 문제가 아니라 Hadoop의 MapReduce, Hive, Presto, Spark
모두 동일한 이슈를 가지고 있습니다.
BigQuery, Snowflake에서만 엔진이 알아서 최적화해주는 기능을 제공하고 있습니다.

📃 Redshift의 레코드 분배와 저장 방식

  • Redshift가 두 대 이상의 노드로 구성되면 그 시점부터 테이블 최적화가 중요

    • 한 테이블의 레코드를 어떻게 다수의 노드로 분배할 것이냐가 관건

  • Diststyle, Distkey, Sortkey 세 개의 속성을 알고 옵션을 세팅해야합니다.

Diststyle

Diststyle은 레코드 분배가 어떻게 이뤄지는 지를 결정합니다.

한 테이블에 속성으로 지정이 되고,
그 속성에 맞게 테이블의 레코드들이 다수의 노드로 분배가 됨

  • all, even, key (default : even)

    • all : 모든 노드에 모든 레코드를 복제

    • even : Round Robin 형태로 노드 별로 돌아가면서 레코드를 넣음

    • key : 특정 컬럼의 값을 기준으로 레코드들이 다수의 노드로 분배

      보통 key 값으로 테이블의 Primary key를 사용합니다.

      테이블에서 특정 레코드들을 Unique하게 지칭하는 Column을
      기준으로 레코드들을 다수의 노드로 분배합니다.

Distkey

Distkey라는 속성은 레코드가 어떤 컬럼을 기준으로 배포되는지 나타냅니다.

  • Diststyle이 key인 경우에만 의미가 있습니다.

  • 분배 기준이 되는 컬럼의 이름을 지정

Sortkey

Sortkey는 레코드가 한 노드내에서 어떤 컬럼을 기준으로 정렬되는지 나타냅니다.

Sortkey는 컬럼 이름을 가지게 되고 그 컬럼의 값을 기준으로 레코드들이 오름차순으로 정렬됩니다.

  • 보통 타임스탬프 필드

위의 Diststyle과 Distkey를 통해 어떤 테이블에 들어있는 레코드들이
노드로 분배가 됩니다. 그럼 그 노드 안에서 어떻게 정렬이 되는가 하면
그 정렬 기준을 정해주는 것이 Sortkey입니다.

Diststyle이 key인 경우 장단점

장점

key를 기준으로 Group by를 하거나 다른 테이블과 join을 하는 경우,
데이터의 이동이 별로 없기 때문에 효율적입니다.

단점

만약 그 key의 분포가 Skew가 있으면,
그래서 특정 key를 갖는 레코드가 매우 많으면,
특정 노드에 데이터가 쏠려, 데이터 크기에 불균형이 발생할 수 있습니다.

  • Diststyle이 key인 경우 컬럼 선택이 잘못되면?

    • 레코드 분포에 Skew가 발생하게 됩니다.
      -> 따라서, 분산처리의 효율성이 사라지게 됩니다.

    • BigQuery, Snowflake는 이러한 속성을 개발자가 지정할 필요가 없습니다.

📃 Redshift의 레코드 분배와 저장 방식 예시

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

기본적으로 CREATE TABLE 문법을 다 작성하고 마지막에 속성을 적어줍니다.
Diststyle로 Key를 선택하였고,
Distkey의 값으로 column1을 선택하여 이를 기준으로 분배하였습니다,
그리고 column3인 timestamp를 기준으로 정렬해주었습니다.

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

보통 SQL의 경우 업데이트를 진행할 때, insert into 방식으로 진행하지만
이는 레코드 바이 레코드로 데이터를 적재하기에,
데이터가 굉장히 큰 경우 비효율적입니다.

그것보다 훨씬 좋은 방식은 데이터를 Parquet와 같은 Binary 포맷으로 압축하여,
클라우드 스토리지에 로딩하는 것입니다.
AWS의 경우 S3에 업로드할 때 Parquet 포맷을 선호하고,
압축한 데이터를 COPY SQL로 Redshift 테이블에 한번에 복사하여 벌크 업데이트를 진행합니다.

=> 이 방식이 일반적인 데이터 웨어하우스에서의 레코드 저장 방식입니다.

🔎 Redshift Serverless 연결 및 실습

Redshift Serverless의 경우 처음 설치하면 3달간 혹은 $300까지 무료로 사용 가능!
( 반드시 나중에 셧다운 해야하는 것을 잊지 말아야합니다!!! )

📃 Redshift 연결 테스트

( 생성한 Reshift Serverless의 namespace로 접근 )

  • 관리자 계정 비밀번호 설정

( 생성한 Reshift Serverless의 Workgroups로 접근 )

  • 퍼블릭 액세스 허용

  • VPC 인바운드 규칙 추가

  • 아래 3개 정보가 필요

    • Endpoint : default.xxx.xxx.redshift-serverless.amazonaws.com:xxx

    • Redshift Username : admin

    • Redshift Password : xxxxx

  • Google Colab 사용

    #sql extension을 google colab에 로딩
%load_ext sql

# 버전의 충돌로 인해 다운그레이드
!pip install ipython-sql==0.4.1
!pip install SQLAlchemy==1.4.47 

# Redshift와의 연결 확인
%sql postgresql://ID:PW@Endpoint

📃 Redshift 초기 설정

스키마(Schema) 설정

# 모든 스키마를 리스트
select * from pg_namespace;

# 스키마 생성
CREATE SCHEMA raw_data;

사용자(User) 설정

select * from pg_user;

CREATE USER user_name1 PASSWORD 'xxx';

Group 생성 / 설정

  • 한 사용자는 다수의 그룹에 속할 수 있습니다.

  • Redshift 그룹의 문제는 계승이 안된다는 점

    • 너무 많은 그룹을 만들게 되고 관리가 힘들어집니다.

      한 그룹이 보유한 모든 권한을 포함하고 거기에 조금 더 많은 권한을 가진 그룹을 만드려할 때, 계승이 안되므로 일일히 권한 추가를 해줘야합니다.

# 모든 그룹 리스트
select * from pg_group;

# 그룹 생성
CREATE GROUP group_name;

# 그룹에 사용자 등록
ALTER GROUP group_name ADD USER user_name;

스키마를 만들고 테이블을 만들고 사용자를 만드는 순간,
사용자 별로 테이블 엑세스 권한을 줘야합니다.

수 많은 사용자들과 수 많은 테이블 사이에서 일일히 권한을
줄 수는 없기에,

테이블 단위가 아닌 스키마 단위로,
사용자 단위가 아닌 사용자 그룹 단위로,
권한을 부여하는 것이 바람직합니다.

역할(Role) 생성 / 설정

  • 역할은 그룹과 달리 계승 구조를 만들 수 있습니다.

  • 역할은 사용자에게 부여될 수도 있고 다른 역할에 부여될 수도 있습니다.

  • 한 사용자는 다수의 역할에 소속 가능합니다.

# 모든 역할을 리스트
select * from SVV_ROLES;

# 역할 생성
CREATE ROLE staff;
CREATE ROLE manager;

# 역할을 사용자에게 부여
GRANT ROLE staff TO user_name;
# 한 역할에 있는 기능을 새로운 역할에 부여 (계승) 
GRANT ROLE staff TO ROLE manager;

📃 Redshift COPY 명령어

COPY 명령어를 통해 테이블에 레코드를 적재해보겠습니다.

  1. 각 테이블을 CREATE TABLE 명령어로 raw_data 스키마 밑에 생성
  2. 각 테이블의 입력이 되는 CSV파일을 먼저 S3로 복사
  3. Redshift에 S3 접근 권한 부여 (IAM -> Redshift)
  4. COPY 명령어를 통해 Redshift 테이블에 레코드를 적재

테이블 생성

CREATE TABLE raw_data.user_session_channel (
   userid integer ,
   sessionid varchar(32) primary key,
   channel varchar(32)
);

S3 버킷 생성

  • csv 파일 생성

    먼저, 데이터 소스인 csv파일을 이러한 형식으로 만들어줍니다.

  • S3 bucket 생성

    그 후에, S3 bucket을 하나 생성해주고,
    bucket 내부에 csv파일을 업로드해줍니다.

    s3://bucket_name/folder_name/업로드한_csv파일

  • Redshift의 S3 접근 권한 설정

    IAM에서 redshift.read.s3란 이름의 역할을 생성하여 S3FullAccess 정책을 설정

    Redshift에 IAM 등록

COPY 명령어 사용

COPY SQL 명령어를 사용해 S3에 로딩한 csv파일을 벌크 업데이트

  • csv 파일이기에 delimiter로 콤마(,)를 지정

  • csv 파일에서 문자열이 따옴표로 둘러싸인 경우 제거하기 위해
    removequotes 지정
    ex) ""sample_text""

  • csv 파일의 첫번째 라인(헤더)를 무시하기 위해 IGNOREHEADER 1을 지정

  • credentials에 Redshift에 지정한 IAM의 Role을 사용
    ( 이때, 해당 Role의 ARN을 읽어와야합니다. )

# COPY
COPY raw_data.user_session_channel
FROM 's3://redshift-test12/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;

# 만약, COPY 명령 실행 중 에러날 경우
# stl_load_errors 테이블을 확인

SELECT * FROM stl_load_errors ORDER BY starttime DESC;

analytics 테스트 테이블 생성

analaytics라는 스키마를 생성하고
raw_data에 있는 테이블을 조인해서 새로 생성 (ELT)

# 스키마 생성
CREATE SCHEMA analaytics;

# CTAS로 Table 생성과 동시에 JOIN
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;
profile
석형원
데이터 엔지니어를 꿈꾸는 거북이, 한걸음 한걸음
이전 포스트

데이터 웨어하우스 관리와 고급 SQL, BI 대시보드 (TIL 26)

다음 포스트

Redshift 고급 기능 실습 (TIL 28)

0개의 댓글