[Week6 데이터 웨어하우스와 SQL 기본] TIL 22일차 - Redshift 사용하기

이번주에는 Redshift를 구축하고 Colab환경에서 이를 사용해보는 실습을 진행한다.

우선, AWS사의 Redshift를 구축해보자.

 

0) Redshift란?

Redshift는 클라우드 데이터 웨어하우스 서비스이다. (↔프로덕션 데이터베이스)

1.병렬 처리 아키텍처 사용 : 대량의 데이터에 대한 쿼리 성능을 향상시키며, 클러스터의 크기를 조정하여 확장성을 확보
2. 컬럼 기반 저장 : 필요한 컬럼만을 읽어오는데 효과적. 분석 쿼리에 적합하며, 데이터 압축 및 저장 공간을 최적화
3. SQL 호환성 : 기존 SQL 쿼리를 사용하여 데이터에 접근할 수 있음

 

1) Redshift 구축

1. 우선 내가 사용하려는 지역과 설정된 지역이 동일한지 확인한다.    Seoul

2. Amazon redshift를 검색하고 Clusters를 선택한다(본인의 경우 severless말고 일반 클러스터를 선택하였다.)

- 클러스터 식별자를 입력한다.    learnde
- 노드와 개수를 선택한다(RA3보다는 DC2가 더 저렴하다)

- 데이터베이스를 입력한다.    awsuser

- Redshift는 default 옵션을 사용할 경우 동일 네트워크 내에서만 통신이 가능하다, 외부(Colab)에서도 통신하게 하기 위해 configuration을 일부 변경해 주자.

Additional configuratione - use defaults 해제

Network&Security - Publicly accessible - Allow public connections to Amazon Redshift.

 

 

2) 실습 예제 소개

이번 실습에서는 웹페이지의 구매에 어떤 채널이 영향을 미치는지를 판단하여, 어떤 채널에 얼만큼의 광고를 할지를 알아보는 실습이다.

 

조건1. 사용자는 직접/특정 채널(링크)를 통해 구매페이지에 접속한다.

조건2. 세션은 다음과 같은 경우 생성된다. (따라서 사용자(1): 세션(N) 관계이다)

• 사용자가 외부링크나 직접방문할 경우 생성
• 사용자가 방문후 30분간 어떠한 작업을 수행하지 않다가 갑자기 수행하는 경우

조건3. 채널은 세션을 만들어낸 경유지(직접접속 direct이 아닌경우)로 인스타그램, 페이스북등의 광고을 게시한 플랫폼을 말한다.

 

테이블 목록

크게 두개의 테이블로 구성되어 있다.

user_session_channel
- userId - sessionId - channel	int varchar varchar

 

session_timestamp
- sessionId - ts	varchar timestamp

 

 

3) SQL 문법

sql 문법은 정해진 부분외에는 협의가 필요하다.

태이블/필드이름의 명명규칙(단수형 vs 복수형 // _ vs CamelCasing)

 

주석

• 한줄주석 --
• 여러줄 주석 /* */

DDL (Create/Alter/Rename/Drop)

• CREATE

CREATE TABLE 테이블명(
    컬럼명1 자료형 primary key,
    컬럼명2 자료형
);

프로덕션 DB의 경우 primary key의 uniqueness를 보장해준다.

그런데 데이터 웨어하우스는 uniqueness를 보장하지 않는다. 왜냐하면 DW의 경우 대량의 데이터를 저장하기 때문에 uniqueness를 보장하려면 너무 많은 데이터를 검수해야 하기 때문이다. primary key 표시를 해도 uniquness가 보장이 되지 않기 때문에 데이터웨어하우스 밖의 코드 차원에서 제약을 만들어줘야 한다.

 

- CTAS

테이블을 생성함과 동시에 내용을 채우는 기능. SELECT 문장을 통해 읽어들인 내용을 새로 만드는 테이블에 삽입한다.

CREATE TABLE 테이블명 AS SELECT ~

 

• DROP

DROP TABLE 테이블명;

이 경우, 테이블이 없으면 오류가 발생한다. 따라서 아래의 형태로 바꾸어 사용한다.

DROP TABLE IF EXISTS 테이블명;

 

• ALTER 

컬럼 수정

- 새로운 컬럼 추가(ADD)

ALTER TABLE 테이블명 ADD COLUMN 컬럼명 컬럼타입;

- 기존 컬럼 이름 변경(RENAME)

ALTER TABLE 테이블명 RENAME 기존컬럼명 TO 새컬럼명;

- 기존 컬럼 제거(DROP)

ALTER TABLE 테이블명 DROP COLUMN 기존컬럼명;

테이블 수정

- 테이블 이름 변경(RENAME)

ALTER TABLE 기존테이블명 RENAME TO 새테이블명

 

 

DML(Insert/Select/Update/Delete)

• INSERT INTO TABLE VALUES (value1, value2, ...)

데이터 웨어하우스의 경우 데이터의 양이 많기 때문에 INSERT 구문을 실행시키기 보다는 

파일(CSV)를 한번에 BULK로 COPY 하는 연산을 수행한다.

• UPDATE SET 컬럼1=값1, 컬럼2=값2 WHERE 조건
• DELETE FROM ~ // TRUNCATE
• SELECT FROM WHERE GROUPBY HAVING ORDERBY ~