6주차-2

데이터 웨어하우스와 SQL 기본에 대해 배우고 이를 바탕으로 데이터 분석에 대한 학습(2)

 

1. Redshift 론치 데모

AWS 콘솔을 통해 Redshift를 론치한다. 

 

Amazon Redshift Cluster 만들기

가장 저렴한 dc2.large 선택

redshift를 외부에서 액세스 가능하게 해야 함

default 값은 security 설정 때문에 같은 AWS 안의 서버에서만 접근 가능 -> Use defaults를 off 해줌

 

Network and Security 에서 

서브넷 그룹이 없다는 의미

 

---

https://docs.aws.amazon.com/ko_kr/redshift/latest/mgmt/managing-cluster-subnet-group-console.html

콘솔을 사용하여 클러스터 서브넷 그룹 관리 - Amazon Redshift

configurations - Create cluster subnet group

해결!!

Endpoint 를 통해 Redshift 에 엑세스

※ 서브넷, VPC 란

https://medium.com/harrythegreat/aws-%EA%B0%80%EC%9E%A5%EC%89%BD%EA%B2%8C-vpc-%EA%B0%9C%EB%85%90%EC%9E%A1%EA%B8%B0-71eef95a7098

[AWS] 가장쉽게 VPC 개념잡기

---

• serverless 로 전환

https://for-data-science.tistory.com/92

[SQL] Redshift severless 시작하기 w.Google Colab Can't load plugin 오류해결

 

클러스터와 서버리스의 장단점

※ Redshift 클러스터 정보

• 1 * dc2.large.instance : 160GB
• Host: learnde.cduaw970ssvt.ap-northeast-2.redshift.amazonaws.com
• Port number : 5439
• Database name : dev

 

---

2. 관계형 데이터 베이스

 

3. SQL

먼저 다수의 SQL 문을 실행한다면 세미콜론으로 분리 필요

 

SQL 주석

• -- : 인라인 한줄짜리 주석
• /* -- */ : 여러 줄에 걸쳐 사용 가능한 주석

1. SQL 키워드는 대문자를 사용한다던지 하는 나름대로의 포맷팅이 필요
2. 팀 프로젝트라면 팀에서 사용하는 공통 포맷이 필요
3. 테이블/필드 이름의 명명 규칙을 정하는 것이 중요
4. 단수형 vs 복수형 (User vs Users)
5. _ vs CamelCasing (user_session_channel vs UserSessionChannel)

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

CREATE TABLE

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

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

CTAS : CREATE TABLE table_name AS SELECT vs. CREATE TABLE and then INSERT

 

CREATE TABLE raw_data.user_session_channel (

userid int,

sessionid varchar(32) primary key,

channel varchar(32)

);

 

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

DROP TABLE

DROP TABLE table_name;  없는 테이블을 지우려고 하는 경우 에러를 냄

DROP TABLE IF EXISTS table_name vs DELETE FROM : 조건에 맞는 레코드들을 지움(테이블 자체는 존재)

 

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

ALTER TABLE
새로운 칼럼 추가 : ALTER TABLE 테이블이름 ADD COLUMN 필드이름 필드타입;

기존 칼럼 이름 변경 : ALTER TABLE 테이블이름 RENAME 현재필드이름 to 새필드이름

기존 칼럼 제거 : ALTER TABLE 테이블이름 DROP COLUMN 필드이름;

테이블 이름 변경 : ALTER TABLE 현재 테이블이름 RENAME to 새 테이블이름

 

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

레코드 질의 언어 : SELECT

SELECT FROM :  테이블에서 레코드와 필드를 읽어오는데 사용

WHERE를 사용해서 레코드 선택 조건을 지정

GROUP BY를 통해 정보를 그룹 레벨에서 뽑는데 사용하기도 함

ORDER BY를 사용해서 레코드 순서를 결정하기도 함

보통 다수의 테이블의 조인해서 사용하기도  함

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

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

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

DELETE FROM : 테이블에서 레코드를 삭제 vs TRUNCATE

 

4. 기억할 점

1. 현업에서는 깨끗한 데이터란 존재하지 않음

• 항상 데이터를 믿을 수 있는지 의심할 것 - 의데이터증
• 실제 레코드를 몇 개 살펴보는 것 만한 것이 없음 

2. 데이터 일을 한다면 항상 데이터의 품질을 의심하고 체크하는 버릇이 필요

• 중복된 레코드를 체크하기
• 최근 데이터의 존재 여부 체크
• Primary Key uniqueness 가 지켜지는지 체크
• 값이 비어있는 컬럼들이 있는지 체크
• 위의 체크는 코딩의 unit test 형태로 만들어 매번 쉽게 체크해 볼 수 있음

3. 어느 시점이 되면 너무나 많은 테이블들이 존재하게 됨

• 회사 성장과 밀접한 관련
• 중요 테이블들이 무엇이고 그것들의 메타 정보를 잘 관리하는 것이 중요해짐

4. 그 시점부터는 Data Discovery 문제들이 생겨남

• 무슨 테이블에 내가 원하고 신뢰할 수 있는 정보가 들어있나?
• 테이블에 대해 질문을 하고 싶은데 누구에게 질문을 해야하나?

5. 이 문제를 해결하기 위한 다양한 오픈 소스와 서비스들이 출현

• DataHub(Linkedin), Amundsen(Lyft)
• Select Star, DataFrame

5. SELECT

테이블에서 레코드들을 읽어오는데 사용

WHERE를 사용해 조건을 만족하는 레코드 

 

SELECT * FROM raw_data.user_session_channel LIMIT 10;

SELECT channel, COUNT(1) FROM raw_data.user_session_channel GROUP BY 1;

SELECT COUNT(1) FROM raw_data.user_session_channel;

SELECT COUNT(1) FROM raw_data.user_session_channel WHERE channel = 'Facebook';

SELECT CASE WHEN channel in ('Facebook', 'Instagram') THEN 'Social-Media' WHEN channel in ('Google', 'Naver') THEN 'Search-Engine' ELSE 'Something-Else' END channel_type FROM raw_data.user_session_channel;

 

6. NULL

값이 존재하지 않음을 나타내는 상수. (존재 하는지 안 하는지 모름)

NULL 이 사칙연산에 들어가는 순간 NULL로 바뀜

 

오름차순일 경우, NULL은 마지막에 위치함

내림차순일 경우, 처음에 위치함

이를 바꾸고 싶다면 NULLS FIRST / NULLS LAST

 

7. WHERE 

WHERE channel in (‘Google’, ‘Youtube’)

WHERE channel = ‘Google’ OR channel = ‘Youtube’

NOT IN

 

LIKE ( 대소문자 구별 ) ILIKE ( 대소문자 구별 X)

WHERE channel LIKE ‘G%’ -> ‘G*’

WHERE channel LIKE ‘%o%’ -> ‘*o*’

NOT LIKE or NOT ILIKE

 

8. 타입 변환

 

8. 생소한 방법

SELECT
    GETDATE(),
    CONVERT_TIMEZONE('America/Los_Angeles', GETDATE());
    
SELECT 1/2, 1/2::float;

-- 판다스 연동
result = %sql SELECT * FROM raw_data.user_session_channel
df = result.DataFrame()

df.head()

df.groupby(["channel"]).size()

df.groupby(["channel"])["sessionid"].count()