[데엔스터디3] 데이터엔지니어를 위한 SQL 실습
데이터엔지니어링 스타터 키트 -3주차 후기
아래의 내용은 직접 유료강의를 수강하고, 배운점 및 추가로 공부한 내용을 기록한 내용입니다.
프로그래머스에서 진행하는 실리콘밸리에서 날아온 데이터엔지니어링 스타터키트 강의추전!
0. SQL실습 전 기억해야할 점
현업에선 깨끗한 데이터란 존재하지 않는다 -> 데이터의 품질을 의심하고 체크하는 버릇이 필요하다!
- 중복되는 레코드 체크
- 최근 데이터의 존재여부 체크
- PK가 유니크한지 체크
- NULL값 있는지 체크
- 아예 unit 테스트 형태로 만들어서 매번 체크할 수 있도록
- data discovery 문제해결
점점 테이블이 많아지게 되서 데이터를 수집하고 통합하는 그 과정에 문제가 발생
어떤 테이블이 중요하고, 메타데이터가 어떤것이고, 어떤 테이블이 무슨역할을 하는지 등 관리 필요
이를 해결하기 위해 datahub 같은 오픈소스 툴을 사용하게 된다.
1. SQL 기본 : DDL (테이블 구조정의 언어)
테이블 자체를 생성/삭제/변경할 경우
1. CREATE
테이블생성
CRATE TALBE 스키마.테이블명 ( 컬럼명 타입 속성 );
그런데 데이터웨어하우스(bigquery,redshift 등)에서는 pk속성은 JUST 장식! -> PK의 유니크함이 지켜지지 않는다.
그래서 PK임에도 불구하고 데이터 중복이 발생할 수 있다.
CTAS : CREATE TABLE 테이블명 AS SELECT
테이블 구조 잡고 SELECT까지 동시에 할 수 있다.
이 쿼리를 주기적으로 실행하면 SUMMARY TABLE을 계속생성하는 데이터파이프라인 생성
2. DROP
테이블삭제
DROP TABLE 테이블명 ; -> 테이블이 존재하지 않으면 에러
DROP TABLE IF EXISTS 테이블명 ; -> 테이블이 존재할 경우에만 삭제, 존재하지 않아도 에러X
DELETE FROM : DROP은 테이블 자체를 삭제하지만, DELETE는 테이블자체는 존재하고, 레코드를 삭제
3. ALTER
테이블의 이름 변경, 컬럼명 삭제/변경/추가
2. SQL 기본 : DDL (테이블 구조정의 언어)
테이블 구조는 그대로 두고, 레코드를 추가/삭제/검색 하는 경우
1. SELECT
테이블에서 레코드(혹은 레코드수)를 검색
SELECT 필드이름1, 필드이름2
FROM 테이블이름
WHERE 선택조건
GROUP BY 필드이름
ORDER BY 필드이름
LIMIT N:
참고로, redshift의 LIKE는 대소문자를 구별한다. (ILIKE 예약어는 대소문자를 구별하지 않는다)
참고로, WHERE channel in (‘Google’, ‘Youtube’) -> WHERE channel = ‘Google’ OR channel = ‘Youtube’ 같음
2.INSERT/UPDATE/DELETE
레코드 추가/수정/삭제
3. SQL 기본 : 타입 Casting
date타입
- SELECT pg_timezone_names(); redshift가 지원해주는 모든 timezone 확인
- CONVERT_TIMEZONE('America/Los_Angeles', ts) : UTC저장 후 지역 timezone 적용
- DATE_TRUNC : date컬럼에서 month만, week만, day만 추출
타입변환
- 문자열(카테고리)→숫자 : category::int or cast(category as int)
- 날짜 → 문자열 : to_char
- 문자열 → 날짜 : to_timestamp
- 예약어를 컬럼명으로 사용하고 싶은경우 " " 를 붙인다
4. SQL 기본 : NULL처리
컬럼명 IS NULL , 컬럼명 IS NOT NULL
값이 존재하지 않음, 그래서 사칙연산을 해도 항상 null값이 나옴
where null을 쓸 경우 = 로 쓰면 에러가 아니라 0이 나옴, is로 구분해야한다
bool타입은 컬럼명 is True, 컬럼명 is False.
컬럼에 null값이 있을 경우에는 컬럼명 is True와 컬럼명 is not False는 다른 경우이다.
5. SQL 조인
2020.01.21 - DB JOIN 정리(INNER/LEFT/RIGHT/OUTER)
JOIN조건으로 매칭이 되는 레코드들만 MERGE되서 읽어온다.
주로 JOIN조건의 키는 PK인데,데이터웨어하우스(bigquery,redshift 등)에서는 pk속성은 유니크함이 지켜지지 않는다.
그래서 항상 JOIN을 하는 키가 유니크한지 체크해야한다.
- inner join : 양쪽 테이블에서 매칭이 되는 테이블만 리턴
- left join : 왼쪽 테이블의 모든 레코드를 리턴
- full join : 왼쪽과 오른쪽 테이블의 모든 레코드를 리턴, 없는 값은 NULL로 채워짐
- cross join : 모든 경우에 대해서 다 모든 레코드들의 조합
- self join : 동일한 테이블을 alias를 달리해서 자기자신과 조인함
6. SQL 심화
- CASE WHEN : 컬럼명을 다른값으로 바꿔줄 때 사용
- UNION과 UNION ALL : UNION은 중복을 제거해서 하나로 묶고, UNION ALL은 중복을 제거하지 않고 하나로 묶음
- NULLIF(a,b) : a의 값이 b와 같을 경우 null, 다를 경우 a를 그대로 사용
- COALECSE (a,b) : a가 null이 아니면 그대로, null이면 b로 대신 사용한다
개인적으로 이 스터디를 하면서 이렇게 수업관련 내용 뿐만 아니라 커리어 관련한 내용도 자주 이야기해주신다.
특히 이번시간 초반에 피드백의 중요성에 대해서 짚고 넘어가주셨는데, 행동관련 피드백을 하면서 해고했던 경험까지
공유해주셨다. 그리고 다시한번 정말 현명하시고 멋진 리더라는 생각이 들었다...나도 이렇게 멋진사람이 되어야지..
그리고 지난주부터 과제가 생겼다. 한두문제 퀴즈가 아니라 여러개고, 어느정도 난이도까지 있다.
그러다보니 일부러 복습할 시간도 갖고, 고민도 해보니까 그만큼 더 내 머릿속에 들어오는 것 같당. 만조쿠~