[TIL] ETL, ELT, Redshift, 데이터 분석/처리용 고급 SQL, BI대시보드 (2)

이원진·2023년 5월 23일
Amazon RedshiftTIL데브코스
0

데브코스

목록 보기
32/54
post-thumbnail
post-custom-banner

학습내용

  1. Redshift 소개

  2. Redshift 설치

  3. Redshift 초기 설정

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

1. Redshift 소개

  • 특징

    • 컬럼 기반 스토리지

      • 레코드 별로 저장하는 것이 아닌, 컬럼 별로 저장

      • 컬럼 별 압축이 가능하며, 컬럼 추가/삭제가 아주 빠름

    • 벌크 업데이트 지원

      • 레코드가 들어있는 파일을 AWS S3로 복사 후 COPY 커맨드로 일괄 복사

      • Parquet과 같은 바이너리 포맷 선호

    • 고정 용량/비용 SQL 엔진

      • 최근 가변 비용 옵션도 제공(Redshift Serverless)

    • 데이터 공유 기능(Datashare)

      • 다른 AWS 계정과 특정 데이터 공유 가능

    • 다른 데이터 웨어하우스들과 마찬가지로, primary key uniqueness 지원 X

    • PostgreSQL 8.X의 일부 기능 사용 가능

      • JDBC, ODBC 등 PostgreSQL을 지원하는 툴 혹은 라이브러리로 접근 가능

  • 옵션과 가격 정책

    • Dense Storage

    • Dense Compute

    • Managed Store

  • 스케일링 방식

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

    • 이를 Resizing이라고 부르며, Auto Scaling 옵션 설정 시 자동으로 이루어짐

    • 이는 가변용량인 Snowflake와 BigQuery의 방식과는 다름

  • 레코드 분배와 저장 방식

    • 두 대 이상의 노드로 구성할 경우, 한 테이블의 레코드를 분산 저장해야 함

      • 한 노드 내에서는 순서를 지정해줘야 함

      • 테이블 최적화가 중요

    • Diststyle, Distkey, Sortkey

      • Diststyle: 레코드 분배가 어떻게 이루어지는지 결정

        • even(default), all, key

        • diststyle = key 인 경우, 컬럼 선택이 잘못되면 레코드 분포에 skew 발생

          • 분산 처리의 효율성이 사라짐

          • BigQuery, Snowflake에서는 이를 시스템이 자동으로 선택

      • Distkey: 레코드가 어떤 컬럼을 기준으로 배포되는지를 나타냄

      • Sortkey: 레코드가 한 노드 내에서 어떤 컬럼을 기준으로 정렬되는지를 나타냄

        • 보통 타임스탬프 필드 사용

    CREATE TABLE my_table (
        column1 INT,
        column2 VARCHAR(50),
        column3 TIMESTAMP,
        column4 DECIMAL(18, 2),
) DISTSTYLE KEY DISTKEY(column1) SORTKEY(column3);
    • column1 값 기준으로 분배, 같은 노드 내에서는 column3 기준으로 정렬

  • 기본 데이터 타입

    • 대부분 PostgreSQL의 데이터 타입을 따라감

    • VARCHAR(CHARACTER VARYING): UTF-8이 아닌 바이트 타입

  • 고급 데이터 타입

    • GEOMETRY

    • GEOGRAPHY

    • HLLSKETCH

    • SUPER


2. Redshift 설치

  • Redshift Serverless 처음 설치 시 3달 간 혹은 300달러까지 무료

  • 데모

    1. AWS 계정으로 로그인

    2. Redshift Serverless 선택하고 Free Trial, $300, expiration 확인

      • Free Trial 만료 전에 삭제하기

      • cluster는 Free Trial 제공 X

      • 한국, 일본 등의 리전은 비싸기 때문에 오레곤 사용 권장

      • 별도 설정할 필요 없이 "구성 저장" 버튼 클릭해 생성

    3. Redshift 연결

      • Google Colab 사용

      • Endpoint, Redshift Username, Redshift Password 3가지 정보 사용

        • Endpoint: 작업 그룹 -> default 클릭해서 확인

          • 퍼블릭 액세스 설정


          • VPC에서 인바운드 규칙 추가해 5439 포트를 모든 외부 IP(0.0.0.0/0)에 오픈



        • Username, Password: 네임스페이스 -> default 클릭해서 설정


3. Redshift 초기 설정

  • 다른 관계형 DB와 동일한 스키마(구조)

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

  • admin 권한이 있는 사용자만 설정 가능

  • SELECT * FROM pg_namespace;: 모든 스키마 리스트

  • 사용자(User) 생성

    • CREATE USER username PASSWORD '...';

    • SELECT * FROM pg_user;: 모든 사용자 리스트

  • 그룹 생성/설정

    • 한 사용자는 다수의 그룹에 속할 수 있음

    • 그룹은 계승이 불가능하기 때문에 수가 많아지면 관리가 힘들어짐

    • 스키마, 그룹 별로 나눠서 접근 권한 부여

      • admin: pii_users

      • 데이터 분석가: analytics_authors

      • 데이터 활용하는 개인: analytics_users

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

-- 그룹에 사용자 추가
ALTER GROUP analytics_users ADD USER user1;
ALTER GROUP analytics_authors ADD USER user1;
ALTER GROUP pii_users ADD USER user1;

    • SELECT * FROM pg_group;: 모든 그룹 리스트

  • 역할(Role) 생성/설정

    • 역할은 그룹과 달리 계승 가능

    • 사용자에게 부여될 수도, 다른 역할에 부여될 수도 있음

    • 한 사용자는 다수의 역할에 속할 수 있음

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

-- 사용자, 다른 역할에 역할 부여
GRANT ROLE staff TO user1;
GRANT ROLE staff TO ROLE manager;

    • SELECT * FROM SVV_ROLES;: 모든 역할 리스트

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

  1. CREATE TABLE 명령어로 3개의 테이블을 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
);

  2. S3에 각 테이블의 입력이 되는 CSV 파일 업로드

    • CSV 파일

      • user_session_channel.csv

      • session_timestamp.csv

      • session_transaction.csv

    • S3 버킷 생성

      • s3://username-test-bucket/test_data/user_session_channel.csv

        • 버킷 이름을 "username-test-bucket"으로 설정

        • test_data라는 폴더 생성

        • 각 CSV 파일 업로드

    • Redshift에 S3 접근 권한 부여

      • IAM에서 역할 생성

        • IAM -> 역할 -> 역할 만들기

        • "AWS 서비스" 선택 -> 사용 사례 드롭박스에 Redshift 입력한 뒤, Redshift-Customizable 선택

        • 권한 정책 박스에 "S3" 입력해 AmazonS3FullAccess 체크

        • 역할 이름을 "redshift.read.s3"로 지정하고 역할 생성

          • 생성한 역할의 페이지로 들어가서 ARN 확인 가능

      • 생성한 역할 Redshift 클러스터에 지정

        • Redshift -> default 네임스페이스 -> 보안 및 암호화 -> IAM 역할 관리

        • IAM 역할 관리 박스에서 "IAM 역할 연결" 선택 -> redshift.read.s3 역할 연결

  3. COPY 커맨드를 사용해 CSV 파일을 테이블로 복사

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

    • CSV 파일이기 때문에 delimiter는 콤마(,)로 지정

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

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

    • credentials에 Redshift Role의 ARN 지정

    • COPY 명령 실행 중 에러 발생 시 stl_load_errors 테이블 확인

      • SELECT * FROM stl_load_errors ORDER BY starttime DESC;

  4. analytics 스키마에 새로운 테이블 생성

    • raw_data의 테이블을 조인해서 새로운 테이블 생성(ELT)

    • CTAS로 간단하게 대체 가능

    CREATE TABLE analytics.mau_summart 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 month,
ORDER BY month;

메모

  • 테이블 생성 시 스키마 지정하지 않으면 public 스키마로 들어감

profile
이원진
이전 포스트

[TIL] ETL, ELT, Redshift, 데이터 분석/처리용 고급 SQL, BI 대시보드 (1)

다음 포스트

[TIL] ETL, ELT, Redshift, 데이터 분석/처리용 고급 SQL, BI대시보드 (3)

post-custom-banner

0개의 댓글