[데이터 웨어하우스 - 중급] 2. Redshift - ③ Redshift COPY 명령으로 테이블에 레코드 적재하기

COPY와 AWS IAM

COPY 명령어를 사용하여 raw_data 스키마의 3개 테이블에 레코드를 적재하고, 각 테이블을 CREATE TABLE 명령으로 raw_data 스키마 아래에 생성하는 작업을 진행하려고 합니다. 이 과정에서 S3 버킷을 먼저 생성하고, Redshift가 S3에 접근할 수 있도록 역할을 생성하여 Redshift 클러스터에 지정해야 합니다.

 

아래는 각 단계의 개요와 필요한 작업입니다.

 

① S3 버킷 생성

• AWS 웹 콘솔에서 S3 서비스로 이동하여 새로운 버킷을 생성합니다.
• 버킷 이름과 옵션을 설정하고, 필요한 경우 액세스 제어 및 구성을 구성합니다.

② Redshift에 S3 접근 권한을 부여하는 IAM 역할 생성

• AWS 웹 콘솔에서 IAM 서비스로 이동합니다.
• 역할을 생성하고, "Redshift - Customizable" 역할을 선택합니다.
• 역할에 S3에 액세스 할 수 있는 정책을 연결하고, 필요한 경우 세부 구성을 설정합니다.
• 역할을 생성한 후 역할 ARN을 기록해 둡니다.

③ Redshift 클러스터에 IAM 역할 지정

• AWS 웹 콘솔에서 Redshift 서비스로 이동합니다.
• 원하는 클러스터를 선택하고 "클러스터 속성 편집"을 선택합니다.
• "클러스터 세부 정보" 섹션에서 "IAM 역할" 필드에 이전에 생성한 IAM 역할의 ARN을 입력합니다.
• 변경 내용을 저장하고 업데이트된 클러스터 설정을 적용합니다.

④ 테이블 생성 및 데이터 적재

• SQL 클라이언트를 사용하여 Redshift 클러스터에 연결합니다.
• CREATE TABLE 문을 사용하여 각 테이블을 raw_data 스키마 아래에 생성합니다.
• COPY 문을 사용하여 S3에서 해당 테이블로 데이터를 복사합니다.

위의 단계를 순서대로 수행하면, S3 버킷을 생성하고 Redshift가 해당 버킷에 액세스 할 수 있는 IAM 역할을 만들어 클러스터에 지정할 수 있습니다. 그런 다음 CREATE TABLE 문으로 테이블을 생성하고, COPY 문을 사용하여 데이터를 테이블로 적재할 수 있습니다.

 

 

 

1. raw_data 테스트 테이블 만들기

아래의 SQL 문을 사용하여 raw_data 스키마에 세 개의 테이블을 생성할 수 있습니다. 이러한 테이블은 일반적으로 ETL(Extract, Transform, Load)을 통해 데이터 소스에서 복사되는 형태로 사용됩니다.

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
);

위의 SQL 문을 실행하면 'user_session_channel', 'session_timestamp', 'session_transaction' 세 개의 테이블이 'raw_data' 스키마에 생성됩니다. 각 테이블은 해당하는 열(컬럼)을 가지며, 'sessionid'를 주요 키(primary key)로 사용하고 있습니다.

 

 

 

2. Redshift에 S3 접근 권한 설정

Redshift가 앞서 만든 S3 버킷을 접근할 수 있어야 하기 때문에 AWS IAM(Identity and Access Management)을 이용해 이에 해당하는 역할 (Role)을 만들고 이를 Redshift에 부여해야 합니다.

 

 

2 - 1. Redshift의 S3 접근 권한용 IAM Role 만들기

1. AWS Management Console에서 IAM으로 이동합니다.
2. 왼쪽의 메뉴에서 "Roles"을 선택합니다.
3. "Create role" 버튼을 클릭하여 새로운 역할(Role)을 생성합니다.
4. "Select type of trusted entity" 단계에서 "AWS service"를 선택합니다.
5. "Choose the service that will use this role"에서 "Redshift - Customizable"를 선택합니다.
6. "Permissions" 단계에서 "Filter policies" 상자에서 "AmazonS3FullAccess"를 검색하여 결과를 확인합니다.
7. "AmazonS3FullAccess" 정책 왼쪽의 체크박스를 선택하고 "Next: Tags" 버튼을 클릭합니다.
8. (선택 사항) "Tags" 단계에서 역할에 태그를 추가할 수 있습니다.
9. "Review" 단계에서 역할의 이름을 "redshift.read.s3"와 같이 지정하고, 역할을 검토한 후 "Create role"을 클릭하여 역할을 생성합니다.

이제 역할이 성공적으로 생성되었습니다. 이 역할을 Redshift 클러스터에 연결하여 Redshift가 S3에 접근할 수 있도록 설정할 수 있습니다.

 

 

2 - 2. redshift.read.s3 역할을 Redshift 클러스터에 지정

1. Redshift 콘솔에서 해당 클러스터의 Default Namespace로 이동합니다.
2. "Security and encryption" 탭 아래에 있는 "Manage IAM roles" 버튼을 선택합니다.
3. "Manage IAM roles" 상자에서 "Associate IAM roles" 메뉴를 선택합니다.
4. 나열된 사용 가능한 IAM 역할 중에서 "redshift.read.s3" 역할을 선택합니다.
5. "Associate IAM roles" 버튼을 클릭하여 선택한 역할을 Redshift 클러스터에 연결합니다.

이제 Redshift 클러스터는 "redshift.read.s3" 역할을 가지고 S3에 접근할 수 있게 됩니다.

 

 

 

3. COPY 명령을 사용해 앞서 CSV 파일들을 테이블로 복사

COPY 명령을 사용하여 CSV 파일을 Redshift 테이블로 복사하는 방법은 다음과 같습니다

COPY raw_data.user_session_channel
FROM 's3://your-bucket/user_session_channel.csv'
CREDENTIALS 'aws_iam_role=arn:aws:iam::1234567890:role/redshift.read.s3'
DELIMITER ','
REMOVEQUOTES
IGNOREHEADER 1;

위의 예시는 'user_session_channel.csv' 파일을 'raw_data.user_session_channel' 테이블로 복사하는 경우입니다. 복사할 파일의 S3 경로와 Redshift에 지정한 IAM 역할의 ARN을 적절히 수정해주어야 합니다.

 

같은 방식으로 'session_timestamp' 및 'session_transaction' 테이블에 대해서도 COPY 명령을 실행하면 됩니다.

 

COPY 명령을 실행하는 동안 에러가 발생하면 'stl_load_errors' 테이블을 확인하여 자세한 내용을 확인할 수 있습니다.

 

 

 

4. analytics 테스트 테이블 만들기

analytics 스키마에 mau_summary 테이블을 생성하는 SQL 문은 다음과 같습니다.

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;

위의 SQL 문은 'raw_data.session_timestamp'와 'raw_data.user_session_channel' 테이블을 조인하여 'mau_summary' 테이블을 생성하는 것입니다. 결과는 월별로 그룹화된 'month' 컬럼과 해당 월의 고유한 사용자 수인 'mau' 컬럼으로 구성됩니다.

 

CTAS (Create Table As Select) 문을 사용하여 새로운 테이블을 생성하고 원하는 조인 및 집계 작업을 수행할 수 있습니다.