SQL : DDL과 DML, DDL과 DML이 무엇인지 알아보자
데이터 엔지니어가 기억할 점(1)
- 현업에서 깨끗한 데이터란 존재하지 않음
> 항상 데이터를 믿을 수 있는지 의심할 껏! → 의(疑)데이터증
> 실제 레코드를 몇 개 살펴보는 것 만한 것이 없음 → 노가다
- 데이터 일을 한다면 항상 데이터의 품질을 의심하고 체크하는 버릇이 필요
> 중복된 레코드들 체크하기
> 최근 데이터의 존재 여부 체크하기 (freshness)
> Primary key uniqueness가 지켜지는지 체크하기
> 값이 비어있는 컬럼들이 있는지 체크하기
> 위의 체크는 코딩의 unit test 형태로 만들어 매번 쉽게 체크해볼 수 있음
데이터 엔지니어가 기억할 점(2)
- 어느 시점이 되면 너무나 많은 테이블들이 존재하게 됨
> 회사 성장과 밀접한 관련
> 중요 테이블들이 무엇이고 그것들의 메타 정보를 잘 관리하는 것이 중요해짐
- 그 시점부터는 Data Discovery 문제들이 생겨남
> 무슨 테이블에 내가 원하고 신뢰할 수 있는 정보가 들어있나?
> 테이블에 대해 질문을 하고 싶은데 누구에게 질문을 해야하나?
- 이 문제를 해결하기 위한 다양한 오픈소스와 서비스들이 출현
> DataHub (LinkedIn), Amundsen (Lyft), ...
> Select Star, DataFrame, …
SQL DDL - 테이블 구조 정의 언어 (1)
- CREATE TABLE
- Primary key 속성을 지정할 수 있으나 무시됨
> Big Data 데이터웨어하우스에서는 지켜지지 않음 (Redshift, Snowflake, BigQuery)
- CTAS
> CREATE TABLE schema_name.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 schema_name.table_name;
ㄴ 없는 테이블을 지우려고 하는 경우 에러를 냄
> DROP TABLE IF EXISTS table_name; vs DELETE FROM
ㄴ 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를 통해 정보를 그룹 레벨에서 뽑는데 사용하기도 함
ㄴ DAU, WAU, MAU 계산은 GROUP BY를 필요로 함
> ORDER BY를 사용해서 레코드 순서를 결정하기도 함
> 보통 다수의 테이블의 조인해서 사용하기도 함
SQL DML - 테이블 레코드 조작 언어 (2)
- 레코드 수정 언어:
> INSERT INTO: 테이블에 레코드를 추가하는데 사용 (COPY)
> UPDATE FROM: 테이블 레코드의 필드 값 수정
> DELETE FROM: 테이블에서 레코드를 삭제 vs. TRUNCATE
기본 SQL, 기본 SQL 문법과 명령을 알아보자
IN & LIKE/ILIKE
SELECT COUNT(1)
FROM raw_data.user_session_channel
WHERE channel in
('Google','Facebook');SELECT DISTINCT channel
FROM raw_data.user_session_channel
WHERE channel ILIKE '%o%';SELECT COUNT(1)
FROM raw_data.user_session_channel
WHERE channel ilike 'Google' or
channel ilike 'Facebook';SELECT DISTINCT channel
FROM raw_data.user_session_channel
WHERE channel NOT ILIKE '%o%';
INSERT INTO vs. COPY (Bulk Update)
- INSERT INTO is slower than COPY, INSERT INTO가 COPY보다 느리다.
> COPY는 일괄 삽입 매커니즘이다.
- INSERT INTO table_name SELECT * FROM …
> 필드의 유형을 제어하려는 경우 CTAS(CREATE TABLE table_name AS SELECT)보다 낫다.
ㄴ 하지만 varchar 길이를 일치시키는 것이 어려울 수 있다.
ㄴ Snowflake와 BigQuery는 문자열 유형을 지원한다
NULL
- 값이 존재하지 않음을 의미
> 0이나 비어있는 string과는 다름을 분명히 인지
- IS NULL or IS NOT NULL
> = NULL or <> NULL or != NULL
- Boolean 타입의 필드도 “IS TRUE” 혹은 “IS FALSE”로 비교
> AA Is Not False == AA Is True the same?
> A: True, False, NULL -> WHERE A is True, WHERE A is not FALSE
- LEFT JOIN시 매칭되는 것이 있는지 확인하는데 아주 유용
- NULL 값을 다른 값으로 변환하고 싶다면
> COALESCE를 사용
> NULLIF
- NULL로 나누면? vs. 0으로 나누면?
JOIN, 다수의 테이블을 합쳐 더 완전한 정보를 알아내는 방법인 JOIN에 대해 알아보자
6종류의 JOIN
JOIN 문법
SELECT A.*, B.*
FROM raw_data.table1 A
____ JOIN raw_data.table2 B ON A.key1 = B.key1 and A.key2 = B.key2
-- INNER(default), FULL, LEFT, RIGHT, CROSS
WHERE A.ts >= '2019-01-01';
JOIN 시 고려해야할 점
- 스타 스키마에서는 항상 필요
- 먼저 중복 레코드가 없고 Primary Key의 uniqueness가 보장됨을 체크
- 조인하는 테이블들간의 관계를 명확하게 정의
> One to one
■ 완전한 one to one: user_session_channel & session_timestamp
■ 한쪽이 부분집합이 되는 one to one: user_session_channel & session_transaction
> One to many? (order vs order_items)
■ 이 경우 중복이 더 큰 문제됨 -> 증폭!!
> Many to one?
■ 방향만 바꾸면 One to many로 보는 것과 사실상 동일.
> Many to many?
■ 이런 경우는 많지 않으며 이는 one to one이나 one to many로 바꾸는 것이 가능하다면 변환하여 조인하는 것이 덜 위험
- 어느 테이블을 베이스로 잡을지 (From에 사용할지) 결정해야 함
고급 SQL, 조금 더 복잡한 SQL 문법과 명령해 대해 알아보자
SQLs to go over
- UNION, EXCEPT
- COALESCE, NULLIF
- DELETE FROM vs. TRUNCATE
- WINDOW: ROW_NUMBER, FIRST_VALUE, LAST_VALUE
- SUBQUERY
- JSON parsing function
UNION, EXCEPT
- UNION (합집합)
○ 여러개의 테이블들이나 SELECT 결과를 하나의 결과로 합쳐줌
○ UNION vs. UNION ALL
■ UNION은 중복을 제거
- EXCEPT (MINUS)
○ 하나의 SELECT 결과에서 다른 SELECT 결과를 빼주는 것이 가능
- INTERSECT (교집합)
○ 여러 개의 SELECT문에서 같은 레코드들만 찾아줌
COALESCE, NULLIF
- COALESCE(Expression1, Expression2, …):
○ 첫번째 Expression부터 값이 NULL이 아닌 것이 나오면 그 값을 리턴하고 모두 NULL이면
NULL을 리턴한다.
○ NULL값을 다른 값으로 바꾸고 싶을 때 사용한다.
- NULLIF(Expression1, Expression2):
○ Expression1과 Expression2의 값이 같으면 NULL을 리턴한다
DELETE FROM vs. TRUNCATE
- DELETE FROM table_name (not DELETE * FROM)
○ 테이블에서 모든 레코드를 삭제
○ vs. DROP TABLE table_name
○ WHERE 사용해 특정 레코드만 삭제 가능:
■ DELETE FROM raw_data.user_session_channel WHERE channel = ‘Google’
- TRUNCATE table_name도 테이블에서 모든 레코드를 삭제
○ DELETE FROM은 속도가 느림
○ TRUNCATE이 전체 테이블의 내용 삭제시에는 여러모로 유리
○ 하지만 두가지 단점이 존재
■ TRUNCATE는 WHERE을 지원하지 않음
■ TRUNCATE는 Transaction을 지원하지 않음
WINDOW: ROW_NUMBER, FIRST_VALUE, LAST_VALUE
- Syntax:
○ function(expression) OVER ( [ PARTITION BY expression] [ ORDER BY expression ] )
- Useful functions:
○ ROW_NUMBER, FIRST_VALUE, LAST_VALUE
○ Math functions: AVG, SUM, COUNT, MAX, MIN, MEDIAN, NTH_VALUE
SUBQUERY
- SELECT를 하기 전에 임시 테이블을 만들어서 사용하는 것이 가능
○ 임시 테이블을 별도의 CREATE TABLE로 생성하는 것이 아니라 SELECT 문의 앞단에서 하나의 SQL 문으로 생성
- 문법은 아래와 같음 (channel이라는 임시 테이블을 생성)
WITH channel AS (
select DISTINCT channel from raw_data.user_session_channel
),
temp AS (select ...),
...
SELECT *
FROM channel c
JOIN temp t ON c.userId = t.userId
JSON parsing function
- JSON의 포맷을 이미 아는 상황에서만 사용가능한 함수
'Data Engineering > 실리콘밸리에서 날아온 데이터 엔지니어링 스타터 키트' 카테고리의 다른 글
| [3주차] ETL (0) | 2023.08.30 |
|---|---|
| [2주차] Assignment (0) | 2023.08.25 |
| [2주차] SQL for Data Engineers(1) (3) | 2023.08.22 |
| [1주차] Assignment (0) | 2023.08.18 |
| [1주차] RedShift 소개 (0) | 2023.08.13 |
