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

raw_data.nps 테이블을 바탕으로 월별 NPS 계산

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

raw_data 스키마 내부에 nps 테이블 생성

%%sql

CREATE TABLE raw_data.nps (
    created_at timestamp,
    score smallint
);

파일 업로드 및 COPY 명령어 실행

주어진 nps 파일을 S3에 업로드 후 COPY 명령어를 통해 Redshift에 벌크 업데이트를 진행한다.

S3 버킷에 업로드

%%sql

COPY raw_data.nps
FROM 's3-file-path'
credentials 'aws_iam_role=arn:aws:iam::~'
delimiter ',' dateformat 'auto' timeformat 'auto' ignoreheader 1

COPY 확인

%%sql

SELECT * FROM raw_data.nps LIMIT 10;

/*
created_at	score
2019-06-11 13:57:36	10
2019-06-11 08:14:56	10
2019-06-11 22:03:13	9
2019-06-11 05:48:25	10
2019-06-11 04:18:56	10
2019-06-11 04:33:29	10
2019-06-11 09:34:12	9
2019-06-11 07:02:06	9
2019-06-11 21:11:22	10
2019-06-11 16:43:14	10
*/

월별 NPS 계산

NPS

이제 analytics 스키마 내부에 nps_summary라는 테이블을 만들어 월간 NPS를 만들 것이다.

analytics 스키마 생성

%%sql

CREATE SCHEMA analytics;

nps_summary 테이블 생성

%%sql

CREATE TABLE analytics.nps_summary (
    year int,
    month int,
    nps float
);

nps_summary 레코드 삽입

연도와 월을 created_at에서 가져오고, 이전에 사용했던 대로 월별 NPS를 계산하는 코드를 작성하였다. 달라진 것은 새로운 테이블에 INSERT로 삽입해 준다는 것이다.

%%sql

INSERT INTO analytics.nps_summary
SELECT
    EXTRACT(YEAR FROM created_at) AS year,
    EXTRACT(MONTH FROM created_at) AS month,
    ROUND(SUM(CASE WHEN score >= 9 THEN 1 WHEN score <= 6 THEN -1 END)::float*100/COUNT(1), 2)
FROM raw_data.nps
GROUP BY year, month

결과

%%sql

SELECT * FROM analytics.nps_summary ORDER BY month;

/*
year	month	nps
2019	1	2.36
2019	2	30.54
2019	3	52.91
2019	4	53.0
2019	5	54.52
2019	6	65.02
2019	7	64.51
2019	8	67.71
2019	9	37.95
2019	10	53.29
2019	11	61.29
2019	12	65.99
*/