[TIL] 데이터분석 데브코스 22일차 - AWS 콘솔을 활용한 Redshift 론치/DDL/DML/SQL실습

학습목표

1. AWS 콘솔을 활용한 Redshift 론치 데모
2. DDL & DML
3. Google Colab으로 SQL 실습 환경
4. SELECT

 

 

 

AWS 콘솔을 활용한 Redshift 론치 데모

1. AWS 계정 생성

 

 

https://aws.amazon.com/ko/free/?gclid=CjwKCAjw7-SvBhB6EiwAwYdCASzdLIAIO6YeD6Y9g7mLkPAM0xTu-o0K55I5WfWwXYsJozwZ1U3LSBoCFEwQAvD_BwE&trk=bdc27120-6d06-4bae-9be1-22d07b8ec0a7&sc_channel=ps&ef_id=CjwKCAjw7-SvBhB6EiwAwYdCASzdLIAIO6YeD6Y9g7mLkPAM0xTu-o0K55I5WfWwXYsJozwZ1U3LSBoCFEwQAvD_BwE:G:s&s_kwcid=AL!4422!3!444218216120!e!!g!!aws%20console!10287751098!99328588021&all-free-tier.sort-by=item.additionalFields.SortRank&all-free-tier.sort-order=asc&awsf.Free%20Tier%20Types=*all&awsf.Free%20Tier%20Categories=*all

무료 클라우드 컴퓨팅 서비스 - AWS 프리 티어

 

 

 

2. [제품] - [데이터베이스] - [Amazon Redshift] 혹은 검색창에 'Redshift' 검색하기

 

 

 

3. Redshift 론치 예정 지역이 본인이 원하는 지역인지 확인한 후  [클러스터 생성] 클릭

 

 

 

실습 : Endpoint에 호스트 이름이 나오면 Redshift Cluster에 엑세스한다.

실제 엑세스는 Google Colab을 활용해서 DB생성

 

 

 

 

 

 

 

 

 

 

관계형 데이터베이스 예제1. 웹서비스 사용자/세션 정보

- 사용자 ID : 웹서비스에 등록된 사용자마다 부여하는 고유한 ID

- 세션 ID : 사용자의 활동을 트래킹하기 위해 세션마다 부여되는 ID

 

** 세션 : 사용자의 방문을 논리적인 단위로 나눈 것

(by Google Analytics) 논리적인 단위로 나누는 방법
1) 사용자가 외부 링크를 타고 오거나 직접 방문해서 올 경우 세션 생성
2) 사용자가 방문 후 30분간 interaction이 없다가 뭔가를 하는 경우 새롭게 세션 생성

- 한 명의 사용자가 여러 개의 세션을 가질 수 있음
- 세션을 생성할 때 마다 세션을 만들어 낸 접점(경유지)를 '채널'이란 이름으로 기록
- 세션이 생성된 시간도 기록

 

▶ 다양한 데이터 분석과 지표 설정 가능

- 마케팅, 사용자 트래픽 관련

- DAU, WAU, MAU 등의 주기별 Active User Chart

- Marketing Channel Attribution 분석 (ex. 어느 채널에 광고하는 것이 가장 효과적인지)

 

ex) 사용자 ID 100번이 총 3개의 세션을 갖는 예제

9:30AM - 구글 광고 타고 방문 → 세션(1)

9:31AM - 상품 광고 클릭

9:33AM - 상품 리뷰 페이지 클릭

9:45AM - 페이스북 광고 타고 방문 → 세션(2)

9:47AM - 다른 상품 광고 클릭

10:30AM - 네이버 광고 타고 방문 → 세션(3)

10:35AM - 상품 구매

 

▶ 위 예제처럼 3개의 채널이 최종 상품 구매에 관여한 경우, 어느 채널에 credit을 줄 건지를 결정하는 방법론

1. First Channel Attribution

: 3개의 채널을 처음 발견하게 해준 채널에 기여도를 몰아주는 방법

2. Last Channel Attribution

: 상품 구매를 만들어 낸 마지막 채널에 기여도를 몰아주는 방법

3. Multi Channel Attribution

: 상품 구매에 관여한 모든 채널들에 조금씩 기여도를 나눠주는 방법

 

pk : 어떤 레코드를 유일하게 지칭해주는 컬럼

두 개의 테이블을 연결해주는 키

 

 

 

 

 

 

 

 

 

 

 

SQL DDL (테이블 구조 정의 언어)

1. CREATE

: 테이블 생성

 

- CTAS : CREATE TABLE AS SELECT  테이블 정의와 동시에 값을 추가할 수 있음

- CREATE TABLE and then INSERT : (원래) 테이블 정의 따로, 값 추가 따로

 

- Primary key 속성을 지정할 수 있으나 무시됨

- 빅데이터 데이터 웨어하우스에서는 Primary key uniqueness가 지켜지지 않음(Redshift, Snowflake, BigQuery)

CREATE TABLE raw_data.user_session_channel(
	userid int, sessionid varchar(32) primary key, channel varchar(32)
);

 

 

 

2. DROP

: 테이블 자체를 삭제

 

DROP TABLE 테이블명;

→ 존재하지 않는 테이블을 지우려고 하는 경우 에러 발생

DROP TABLE IF EXISTS 테이블명;

→  테이블이 존재하면 삭제하고 존재하지 않으면 에러발생 없이 종료

 

** DELETE FROM : 조건에 맞는 레코드들을 지움(테이블 자체는 존재)

 

 

 

3. ALTER

: 테이블 자체를 삭제

 

- 새로운 컬럼 추가

ALTER TABLE 테이블명 ADD COLUMN 필드명 필드타입;
-- [테이블명]은 [스키마명.테이블명] 이 될수도 있다.

 

- 기존 컬럼의 이름 변경

ALTER TABLE 테이블명 RENAME 원래필드명 to 새로운필드명;

 

- 기존 컬럼 제거

ALTER TABLE 테이블명 DROP COLUMN 필드명;

 

- 테이블명 변경

ALTER TABLE 원래테이블명 RENAME to 새테이블명;

 

 

 

 

 

 

 

 

 

SQL DML (테이블 데이터 조작 언어)

1. SELECT

: 테이블에서 레코드(혹은 레코드 개수)를 읽어오는데 사용

 

SELECT 필드명1, 필드명2, ...
FROM 테이블명 -- 테이블에서 레코드와 필드를 읽어옴
WHERE 조건 -- 레코드 선택 조건을 지정
GROUP BY 필드명1, 필드명2, ... -- 정보를 그룹 레벨에서 추출하는데 사용
ORDER BY 필드명 [ASC|DESC] -- 레코드 순서 결정(ASC는 디폴트값으로 생략 가능)
LIMIT 개수; -- 가져올 데이터 개수를 정해줌

 

- DAU, WAU, MAU 계산은 GROUP BY를 필요로 한다.

 

 

2. INSERT INTO

: 테이블에 레코드를 추가하는데 사용

 

 

3.UPDATE FROM

: 테이블 레코드의 필드 값 수정

 

 

4. DELETE FROM

: 테이블에서 레코드를 삭제

 

- 값을 정해주지 않으면 전체 레코드를 삭제

- TRUNCATE은 DELETE FROM과 동일한 기능을 함

DELETE FROM : transaction 사용 가능

TRUNCATE : transaction 사용 불가능

 

 

 

5. 타입 변환 함수

- DATE CONVERSION

1) CONVERT_TIMEZONE('변환하려는 지역의 타임존 이름', 타임스탬프)

2) select pg_timezone_names() : 타임존 이름 파악 가능

 

- DATE, TRANCATE : 타임스탬프를 인자로 받아서 날짜만 추출

- DATE_TRUNC : 첫번째 인자가 어떤 값을 추출하는지 지정(week, month, day....)

- EXTRACT or DATE_PART : 날짜시간에서 특정 부분의 값을 추출

DATEDIFF : 날짜 차이 연산

DATEADD : 날짜 더하기 연산

GET_CURRENT : 현재 시각

 

- TO_CHAR : 숫자 시간을 문자열로 변환

- TO_TIMESTAMP : 문자열 시간을 날짜 시간 타입으로 변환

 

 

 

6. Type Casting

ex)1/2 = ?

정수간의 연산은 정수가 되어야 하기 때문에 결과는 0이 된다.

- 분자 or 분모 중 하나를 float로 캐스팅해야 0.5가 된다.

- 다른 프로그래밍 언어에서도 (일반적으로는) 동일하게 적용

 

원하는 데이터 타입으로 캐스팅하는 방법

1) :: 오퍼레이터 사용

필드명::원하는데이터타입

 

2) cast 함수 사용

cast(필드명 as 원하는데이터타입)

 

 

 

 

 

데이터 품질 체크하기

• 현업에서 '깨끗한 데이터'란 존재하지 않음
• 실제 레코드 몇 개를 살펴봐야함
• 중복된 레코드들 체크하기
• 최근 데이터의 존재 여부 체크하기(freshness)
• PK uniqueness가 지켜지는지 체크하기
• 값이 비어있는 컬럼들이 있는지 체크하기
• 코딩의 unit test 형태로 만들어 매번 쉽게 체크 가능
• 중요한 테이블이 뭔지 파악하고 그것들의 메타 정보 관리가 중요함

 

Data Discovery Problem

• 무슨 테이블에 내가 원하고 신뢰할 수 있는 정보가 들어있나?
• 테이블에 대해 누구에게 질문 해야 하나?
• 문제 해결을 위한 다양한 오픈 소스와 서비스 출현
  • DataHub (LinkedIn), Amundsen(Lyft) 등
  • Select Star, DataFrame 등

 

 

 

 

 

 

 

 

 

 

 

Google Colab을 통해 SQL 실습해보기 - SELECT

Q. Colab은 Python Notebook인데 SQL 사용이 가능할까?

A. 사용법이 쉽지는 않지만 Colab과 같은 Python Notebook을 SQL editor처럼 사용할 수 있다. 

파이썬 코딩과 믹스해서 사용할 수 있다는 점에서 큰 장점이 존재한다.

 

일단 사용하려면 아래와 같은 코드를 실행해줘야 한다.

%load_ext sql

#ID와 PW를 자신의 환경에 맞게 수정
%sql postgresql://사용자ID:패스워드@Redshift호스트이름:포트번호/연결할접속DB이름

정상적으로 실행되면 'Connected: 사용자ID@접속DB' 가 출력된다.

 

 

쿼리 실행

%%sql

쿼리 작성

 

 

 

Python notebook에서 작성된 쿼리이기 때문에 Null 대신 None이 출력된다.

 

 

Pandas와 SQL을 연동하는 방법

변수명 = %sql 쿼리문
리턴값 = 변수명.DataFrame()