Redshift serverless 생성하고 S3의 csv 파일 Redshift에 COPY하기 - TIL230523

📚 KDT WEEK 8 DAY 2 TIL

• Redshift 알아보기
• Redshift Severless 생성 실습
• Redshift COPY 실습

---

 

🟥 Redshift

 

AWS에서 지원하는 데이터 웨어하우스 서비스

 

• 최소 160GB ~ 2PB까지 처리 가능
• 응답속도가 빠르지 않아 프로덕션 데이터베이스로는 부적합
• 레코드 별로 저장하는 것이 아니라 컬럼별로 저장함
  • 컬럼별 압축이 가능하며 컬럼을 추가하거나 삭제하는 것이 빠르다
• 벌크 업데이트(S3에서 레코드파일을 일괄복사 가능) 지원 - COPY SQL
• 고정 용량/비용 엔진, 가변 비용 옵션(Redshift Serverless)도 지원
• 데이터 공유 기능(Datashare)으로 다른 AWS 계정과 데이터 공유 가능
• 데이터 웨어하우스이므로 Primary Key Uniqueness를 보장하지 않는다.
• SQL 기반 

 

Redshift의 스케일링 방식

용량이 부족해질 때마다 새로운 노드를 추가하는 방식

 

ex) dc2.large를 사용중일 때

• Scale Out : dc2.large를 하나 더 추가
• Scale Up : dc2.8xlarge로 업그레이드
• Auto Scaling : Dense Compute, Managed Storage 옵션에서만 제공, 자동으로 업그레이드

 

Redshift 최적화가 복잡하다.

고정비용 옵션, 두 대이상의 노드로 구성될 경우 레코드의 분산 저장 이슈가 발생할 수 있다.

Snowflake와 BigQuery의 경우 알아서 최적화 처리를 해준다.

 

* 한 테이블의 레코드들을 어떤 방법으로 다수의 노드에 분배할지 정하는 옵션들 *

• Diststyle
  • All : 모든 레코드를 모든 노드에 동일하게 넣는다.
  • Even : 레코드을 하나씩 돌아가면서 모든 노드에 분배한다.
    • 데이터 셔플링 문제
  • Key : 특정 컬럼을 기준으로 노드에 분배하는 방식
    • JOIN이나 GROUP BY를 많이 사용하는 KEY를 지정하는 것이 효율적이다.
    • 하지만 Data Skewed(비대칭) 문제가 나타나면 분산 저장의 효율이 떨어질 수 있다.
    • 따라서, 특정 KEY의 레코드 값이 많은 경우에는 비효율적이다.

Diststyle의 옵션들

 

• Distkey : 어떤 컬럼을 기준으로 배포되는지 나타낸다.
  • Diststyle이 Key인 경우에만 해당한다
• Sortkey : 레코드가 한 노드 내에서 어떤 컬럼을 기준으로 정렬되는지 나타낸다.
  • 보통 타임스탬프 필드가 해당된다.

 

ex)

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

 

---

 

🟦 Redshift Serverless 실습

 

아마존에서 계정을 생성하고 Redshift를 검색하여 접속한다.

오른쪽 하단의 Try Redshift Serverless ~ 버튼을 클릭하여 생성한다.

학습 실습용이므로 더 적은 비용이 나가는 리전(오레곤)을 선택하여 실습하였다.

 

생성버튼을 누른 뒤, 별다른 설정없이 구성 저장버튼을 눌러 생성을 시작한다.

 

생성 중 나오는 화면. Serverless에서는 가변 비용임을 기억하자.

생성이 완료되면, 네임스페이스 구성 > default 에서 새로운 admin 유저를 생성해보자.

관리자 사용자 암호 변경을 사용하면 된다.

 

새 관리자 사용자 이름을 admin으로 넣자

 

그리고 Redshift에 접속하기 위한 엔드 포인트를 가져와야 한다.

 

작업 그룹 구성 > default > 엔드포인트를 복사하자

 

admin 유저 정보와 엔드포인트가 있다면 이제 Redshift에 접속해보자.

Google Colab에서 접속하려면 Redshift 퍼블릭 액세스 활성과 VPC 포트설정이 필요하다.

 

1. 퍼블릭 액세스 설정

작업 그룹 구성 > default > 네트워크 및 보안 > 편집버튼을 누르자.

 

퍼블릭 액세스 가능을 체크하고 저장하면 된다.

 

2. VPC 보안 그룹에 Redshift 포트 추가하기

 

아까 퍼블릭 액세스 설정의 본 첫 번째 이미지 화면에서, VPC 보안 그룹을 선택해서 들어오면 아래처럼 보인다.

 

하단 탭의 '인바운드 규칙' 에서 '인바운드 규칙 편집' 버튼을 누른다.

 

두 번째 인바운드 규칙이 새로 추가한 규칙이다. 5439 포트는 Redshift 포트에 해당한다.

이로써 Redshift를 통해 들어오는 트래픽에 대해서는 허용하도록 설정했다.

 

Google Colab을 통해 Redshift 접속하기

모자이크된 부분이 엔드포인트가 들어간 부분이다.

접속할 때는 postgresql://admin:password@엔드포인트 형태로 접속하면 된다.

접속에 성공하게 된다면 아무런 문제없이 구문이 실행된다.

 

---

 

🟧 Redshift 초기 설정

 

1. Schema

사용 목적에 맞게 Redshift schema(폴더)를 생성하여 테이블을 저장한다.

CREATE SCHEMA raw_data; -- ETL 결과 데이터용
CREATE SCHEMA analytics; -- ELT 결과 데이터용

SELECT * FROM pg_namespace; -- 모든 스키마 리스트하기

 

2. User

CREATE USER user1 PASSWORD 'password'; -- 새로운 유저 생성하기

SELECT * FROM pg_user; -- 모든 유저 리스트하기

 

3. GROUP

권한을 사용자마다 부여하지 않고, 그룹으로 묶어 효율적으로 부여하기 위해 사용한다.

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

 

CREATE GROUP 그룹이름; -- 그룹생성
ALTER GROUP 그룹이름 ADD USER 사용자이름; -- 그룹에 유저넣기

SELECT * FROM pg_group; -- 모든 그룹 리스트하기

 

4. ROLE

그룹처럼 사용자에게 권한을 부여하는데 사용된다.

하지만 그룹과 달리 역할은 다른 역할에 계승할 수 있는 구조로 사용할 수 있다.

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

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

 

CREATE ROLE 역할이름; -- 새로운 역할 생성
GRANT ROLE 역할이름 TO 유저이름; -- 기존 역할에 유저넣기
GRANT ROLE 역할이름1 TO ROLE 역할이름2; -- 기존 역할1을 새로운 역할2에 모든 권한 계승

SELECT * FROM SVV_ROLES; -- 모든 역할 리스트하기

 

Colab 실습

---

SCHEMA 실습

직접 만든 스키마 외에도 기본적인 스키마들이 여러 개 나타났다.

 

USER 실습

 유저 또한 생성했던 user1과 admin 말고도 다른 유저를 확인할 수 있다.

 

GROUP 실습

 

ROLE 실습

---

 

 

---

 

🟩 Redshift 벌크업 업데이트 + 실습

 

아래와 같은 사전 작업이 필요하다.

 

1. 임의의 스키마 밑으로 새로운 테이블 몇 개를 만든다.
2. 입력이 될 csv 파일들을 만들고 S3 버킷에 업로드한다.
3. Redshift가 S3 버킷에 접근할 수 있도록 권한 설정해주기 ▶ AWS IAM 활용

 

IAM을 통해 새로운 s3 접근 역할 생성하기

 

1. IAM 역할만들기를 클릭하여 Redshift -Customizable 옵션으로 하나 생성한다.

AWS IAM > 역할(role) > 역할 만들기

 

2. 다음버튼을 누르고 S3에 관한 권한을 연결하면 된다.

Read 권한만 주고싶다면 readonly 옵션을 찾아 줘도 무방하다.

 

Redshift에 역할부여

IAM으로 생성한 역할을 Redshift에 부여해보자.

역할을 부여할 Redshift 클러스터의 해당 네임스페이스에 들어가서 설정하면 된다.

 

Redshift 클러스터 > 네임스페이스 > default > 보안 및 암호화 > IAM 역할 관리

 

아까 만들었던 역할 연결하면 끝!

 

---

 

COPY 실습해보기

 

S3 버킷에 csv 파일 데이터를 넣는 과정은 생략하고, (매우 직관적이므로)

바로 Colab에서 Redshift에 연결되어있는 상태에서 테이블을 만들고, COPY 커맨드를 써보았다.

 

테이블을 새로 만들어서 S3로 부터 데이터를 넣고, 확인하는 작업이다.

순서대로 테이블 만들기, S3에서 데이터 가져와 넣기, 넣은 데이터 값 확인하기 이다.

여기서 COPY 커맨드를 설명하자면,

• COPY ~ : 어떤 테이블에 넣을지 명시한다.
• FROM ~ : 어디에서 가져올지 적는데, 나는 S3 버킷 > test-data 폴더 > 해당 csv 파일 을 넣었다.
• credentials ~ : Redshift뿐만아니라 여기에도 S3 제어 역할을 넣어주어야한다.
  • aws_iam_role = 다음으로 해당 역할의 ARN 값을 넣으면 된다. ARN 값은 역할 정보에 적혀있다.
• delimiter ',' : 데이터가 어떤 기준으로 분리되어 저장되었는지 적는데, CSV이므로 ,(콤마) 이다.
• dateforamt 'auto' timeforamt 'auto' : 해당 포맷을 자동으로 알아서 인식하고 가져온다.
• IGNOREHEADER 1 : CSV 파일의 첫 번째 행은 컬럼명이므로 이 구문으로써 제외할 수 있다.
• removequotes : 데이터의 따옴표를 제거하고 가져온다.

 

COPY SQL 문법 참고 레퍼런스 : https://docs.aws.amazon.com/redshift/latest/dg/r_COPY.html

COPY - Amazon Redshift

 

+++ COPY 명령 실행 중 오류가 발생한다면, 가장 최근에 생성된 에러 확인하는 명령어

SELECT * FROM stl_load_errors ORDER starttime DESC;