[숙제 - 32일 차] S3 -> Redshift, COPY 명령어로 데이터 적재하기

Redshift COPY 명령으로 테이블에 적재

Redshift Serverless 생성 및 접속

Default VPC 생성

이전에 모든 VPC를 삭제해서 Redshift를 생성할 때 선택할 VPC가 존재하지 않았다. 그래서 임의로 만들어서 하려고 했다가 오류가 발생해서 계속 검색하던 중 아래의 명령어로 default vpc를 만들 수 있다는 것을 알게 되었다.

aws ec2 create-default-vpc

기본 VPC - Amazon Virtual Private Cloud

Redshift Serverless 생성

default VPC를 생성하고, 다시 Redshift를 생성하려고 하니 네트워크 부분이 모두 자동으로 채워져 있었다. 그래서 네임 스페이스와 작업 그룹과 같은 이름만 지정해 주고 Redshift를 생성하였다.

Redshift Serverless 생성

Redshift Admin

Redshift의 네임 스페이스에 들어가 "관리자 보안 인증 정보 편집"에서 관리자 계정을 설정해 준다.

Redshift 접속을 위한 설정 - 퍼블릭 액세스, 인바운드 규칙

Redshift에 접속을 하기 위해서는 작업 그룹 > 네트워크 및 보안의 퍼블릭 액세스 가능을 "켜짐"으로 설정해야 한다. 또한 보안 그룹의 인바운드 규칙에 "5439 port, 0.0.0.0/0"을 설정해줘야 한다.

퍼블릭 액세스 켜기

인바운드 규칙 설정

Redshift 접속 With Colab

아래의 코드를 실행하고 세션을 다시 시작한다. 버전을 맞춰주지 않으면 redshift를 불러오는 도중 에러가 발생한다.

%load_ext sql

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

ID에는 admin, PW에는 본인이 설정했던 비밀번호, 호스트이름에는 작업 그룹의 엔드 포인트를 입력해 준다. 아무런 문구가 없다면 정상 연결 된 것이다.

%sql postgresql://ID:PW@ENDPOINT

AWS S3 버킷 생성 및 파일 업로드

S3 버킷 생성

sanseo-practice-bucket이라는 이름의 S3 버킷을 생성하였다. 생성할 때, 특별히 변경한 것은 없고, 이름만 설정해 주었다.

여기서 외부에서 S3에 접근하기 위해 "S3 버킷 > 권한 > 퍼블릭 액세스 차단 해제"를 진행해줘야 한다.

S3 bucket

퍼블릭 액세스 차단 해제

파일 업로드

강의에서 주어진 csv 파일을 드래그하여 S3에 업로드하였다.

Redshift 실습을 위한 설정

IAM ROLE 생성

Redshift에서 S3에 접근할 수 있도록 권한을 부여해야 하는데, 이를 위해 IAM ROLE를 추가해줘야 한다. IAM 페이지에 접속해서 다음과 같이 설정하고, 다음 페이지에서는 이름을 적절히 적고, "AmazonS3FullAccess" 정책을 설정하고 생성한다.

IAM ROLE (Redshift)

Redshift IAM ROLE 설정

Redshift 네임 스페이스 > 보안 및 암호화에서 IAM 역할 관리를 클릭해서 방금 생성한 ROLE를 설정해 준다.

IAM 역할 연결

COPY

지금까지 Redshift에 접속 및 S3와 연동하는 실습을 진행하였다. 이제 마지막으로 S3에 있는 csv 파일을 Redshift로 벌크 업데이트 하는 실습을 진행한다. 총 세 개의 테이블이 존재하지만, 방식은 동일하므로 간략하게 하나만 알아보도록 하자.

user_session_channel 테이블

s3-file-path에는 COPY하려는 데이터의 URI를 입력한다. S3 버킷에서 파일을 누르면 확인할 수 있다. 그리고 aws_iam_role=arn:aws:iam::~ 부분에는 이전에 S3FullAccess 정책을 설정했던 역할의 ARN을 적어주면 된다. 이것 또한 IAM ROLE에서 확인할 수 있다.

%%sql

-- 스키마 생성
CREATE SCHEMA raw_data;

-- 테이블 생성
CREATE TABLE raw_data.user_session_channel (
   userid integer ,
   sessionid varchar(32) primary key,
   channel varchar(32)
);

-- S3에서 Redshift로 COPY
COPY raw_data.user_session_channel
FROM 's3-file-path'
credentials 'aws_iam_role=arn:aws:iam::~'
delimiter ',' dateformat 'auto' timeformat 'auto' IGNOREHEADER 1 removequotes;

COPY 확인

SELECT 결과 올바르게 복사된 것을 확인하였다.

%%sql

SELECT * FROM raw_data.user_session_channel LIMIT 10;

/*
userid	sessionid	channel
198	ef9280fbc5317f17d480e4d4f61b3751	Naver
599	8be6adae5ae0e157014d7d250870f212	Google
191	c5f7756d9f92a8954884ec415f79d120	Instagram
236	3091d86c09a002e144f4ec506eada3d7	Instagram
64	34d94cf9ca228a78848313df32d668d1	Organic
654	fed537780f3f29cc5d5f313bbda423c4	Facebook
919	28891cb4ab421830acc36b1f5fd6c91e	Naver
957	7b86f36d139d8581d4b5a4f155ba431c	Naver
250	a62dd1eb9b15f8d11a8bf167591c2f17	Instagram
36	dbafb94acd0f5bb51afb63a64a03bf2f	Google
*/