공부하는 개발 블로그

1. 기초 용어

1) 데이터웨어하우스 : 데이터의 장기 보관을 목적으로 하는 저장소

2) 데이터마트: 데이터웨어하우스에서 필요한 데이터만 추출한 것

3) 데이터레이크: raw data가 흘러들어오는 것에 비유하여, 데이터의 축적 장소

4) 애드혹 분석: 일회성 분석

2. 분산처리

열지향 테이블의 이점

1) 지역성: 같은 칼럼에서는 동일한 값이 반복되기도 함 -> 압축률을 높일 수 있다

2) 데이터 분석시 모든 열을 분석하는 것이 아니라, 일부 열만 이용. 이 경우 행지향 테이블의 경우 모든 레코드를 탐색해야한다. 데이터의 양이 클 경우 메모리가 부족할 수 있음. 열지향은 일부 열만 메모리에 올리기 때문에 효율적.

◆ 비정규화 테이블을 사용하는 이유:

정형 데이터: 스키마가 명확히 정해지는 데이터 : RDB가 강함

비정형 데이터: 스키마가 명확하지 않은 데이터 -> 이미지, 동영상 등 : NoSQL이 강함

스키마리스 데이터: 형식은 정해져있지만 칼럼수나 데이터형이 명확하지 않은 데이터

◆ Hadoop

1) HDFS(Hadoop Distributed File System): 분산 파일 관리 시스템 : 네임 노드(메타데이터 + 데이터 노드 관리) + 데이터 노드

2) YARN(Yet Another Resource Negotiator): 리소스 관리자 - 클러스터 자원 관리, 할당

3) MapReduce: 분산 데이터 처리 모듈

SQL-on-Hadoop : Hadoop에서 사용되는 쿼리 엔진. Hive, Tez/ Impala, Presto(대화형)

4) Hadoop Common : 하둡의 다른 모듈을 지원하기 위한 공통 컴포넌트 모듈

◆ Spark

MapReduce와 달리 대량의 메모리를 사용해서 고속화. 스트리밍 데이터에 적합

MapReduce는 적은 메모리로 디스크 I/O 많이 이용. Spark는 메모리에 다 올려버린다.

또한 데이터 구축의 파이프라인을 원샷으로 해결할 수 있다. 텍스트 데이터를 열 지향 스토리지로 변환하고 SQL로 집계하여 결과를 내보낼 수 있다.

데이터마트 구축의 파이프라인

비구조화 데이터 ->(데이터 구조화: Hive) -> 구조화 데이터(팩트 테이블, 디멘젼 테이블. 열 지향 스토리지) ->(데이터 집약: Presto) -> 비정규화 테이블

3) 데이터 마트의 구축

- 팩트 테이블: 추가(append), 치환(테이블 전체를 replace)

테이블 파티셔닝: 하나의 테이블을 여러 개의 파티션으로 나누어 파티션 단위로 데이터 업데이트 가능.

- 집계 테이블: 팩트 테이블을 집계한 것.

카디널리티: 각 칼럼이 가질 수 있는 값의 범위. 카디널리티가 작아야 집계 테이블이 작다.

- 스냅샷 테이블: 테이블 전체를 저장 / 이력 테이블: 변경된 데이터만 스냅샷

3. 빅데이터의 축적

1) 벌크형 vs. 스트리밍형

벌크형 : 크기가 너무 크지 않도록 분할해서 전송. 재실행이 가능하다는 장점

2) 스트리밍형: 계속 전송되어오는 작은 데이터를 위한 방법. 메시지 배송을 통함.

-클라이언트 : 메시지가 처음 생성되는 기기

-프런트엔드: 메시지를 먼저 받는 서버. 클라이언트와의 통신 프로토콜을 제대로 구현하는 역할.

클라이언트 -> 프런트엔드 -> 메시지 브로커

-메시지 브로커: 디스크에 빈번하게 쓰는 것은 비효율적. 중간 데이터 축적층.

푸시: 송신 측에서 메시지 브로커에 넣는 것

풀: 수신측이 데이터를 가져오는 것

- 메시지 라우팅: 메시지 브로커의 데이터는 여러 다른 소비자가 읽을 수 있다. 이를 통해 메시지가 복사되어 여러 경로로 분기될 수 있다.

2) 메시지 배송 : at least once 배송을 하고 중복을 확인한다.(offset/고유 id 이용)

3) 시계열 데이터의 최적화

- 시계열 인덱스

- 조건절 푸시다운: 테이블 파티셔닝에서 만들어진 통계를 이용함(최솟값, 최댓값)

- 시계열 테이블: 시계열 데이터를 이용한 테이블 파티셔닝

- 데이터마트를 이벤트 시간으로 정렬

4) 비구조화 데이터의 분산 스토리지: NoSQL 이용

수정하는 것은 새로 파일을 써야하므로 갱신이 많은 곳에는 부적합

log와 같이 축적에 주로 사용

- KVS: dynamoDB

- wide column store: 2개 이상의 키. cassandra

-도큐먼트 스토어: 스키마리스 데이터 관리 가능. mongoDB

CAP

Consistency: 

Availiability: 장애 X

Partition-tolerance

- 데이터 수집의 파이프 라인:

클라이언트 -> 프런트엔드 -> 메시지 브로커 -> 소비자 -> 분산 스토리지 -> 중복 제거/데이터 구조화(열 지향 스토리지로 변환)-> 다시 분산 스토리지로

4. 워크플로우 관리

멱등:  동일한 태스크를 여러 번 실행해도 동일한 결과가 되는 것

◆ MapReduce:

1) 파일을 일정 크기로 나누어 작은 데이터인 스플릿을 만든다.

2) 나눈 데이터를 읽어 비슷한것 끼리 묶는다

3) Map: 처리한다

4) Reduce:다시 합한다.

◆ DAG:

태스크의 실행 순서를 DA로 나타내서 처리한다. 의존성 문제 해결

◆ 람다 아키텍처

실시간 분석을 지원하는 빅데이터 아키텍쳐

대량의 데이터를 실시간으로 분석하기 어려우므로, batch로 미리 만든 데이터와 실시간 데이터를 혼합해서 사용하는 방식.

배치 레이어: 모든 데이터가 거치는 레이어

서빙 레이어: 배치 처리 결과는 서빙 레이어를 통해 접근. -> 배치 뷰. 하지만 실시간 정보를 얻을 수 없다.

스피드 레이어: 스트림 처리를 위함. 실시간뷰

스피드 레이어의 실시간 뷰 결과는 나중에 배치 뷰로 치환된다.

데이터 =>1) 배치 레이어 -> 서빙 레이어 & 2) 스피드 레이어 => 쿼리.

카파 아키텍처: 배치 레이어/서빙레이어 제거. 대신, 메시지 브로커의 데이터 보관 기간을 늘려 재실행 가능게 함. 

1. 데이터베이스의 필요성

1) 대량의 데이터에서 빠른 검색.

2) 대량의 데이터를 메모리 내에서 관리하기 어렵다

3) 장애 복구

4) 병렬성 제어(동시 작업)

5) 데이터 무결성 보장

2. 인덱스

- <id, 주소> 형태

원하는 정보가 어느 주소에 위치하는지 빠르게 찾기 위함.

레코드가 고정 길이인 경우, 고정길이 * id를 하면 주소를 알 수 있지만, 버려지는 정보가 많을 수 있다.

인덱스를 이용하면 가변 길이를 이용하면서도 주소를 빨리 구할 수 있다.

- B+ tree : root/branch.leaf 블록이 존재.

리프 블록만 데이터를 가짐.

리프 블록에서 리프 블록으로 이동 가능. ->  등호, 전방일치 등 범위 검색 가능

branch 노드도 데이터를 갖는 경우: B- tree

-인덱스 병합: 여러 개의 인덱스를 사용하는 경우, 각 인덱스로 검색 -> AND/OR

- 업데이트 속도를 향상시키기 위해: 데이터가 추가될때마다 업데이트(ranom write) 하지 않고, 메모리나 파일에 모아두었다가 한번에 업데이트한다.(sequential write.) 예: MySQL innoDB

- B+ 트리의 인덱스 값이 변경된 경우, 리프 블록의 내용도 변경해야하는 경우가 존재한다 : 리프 분할

동시에 여러 클라이언트에서 하는 경우 문제가 생길 수 있음. -> LOCK 사용

파티션 테이블 : 사용자에게는 테이블이 하나지만 내부로는 복수의 테이블로 관리, 인덱스도 복수로 구분 -> 병렬 갱신 가능.

3. 테이블과 릴레이션

◆ - 참조 무결성:

정규화 이론

1) 제1 정규화: 테이블 구성에서 중복/반복/복합값을 포함한 구조 제거

2) 제2 정규화: 일부 열에 의해 결정되는 열 제거

3) 제3 정규화: 모든 열은 기본키에 의해 값이 하나로 결정되어야한다.

4. 장애와 대응

1) RAID

하나의 서버를 여러 개의 HDD에 탑재하고 동일한 데이터를 두 개 이상의 HDD에 분산하는 기술

RAID 0: 복수의 HDD에 데이터 기록, 읽고 쓰기. 이용 가능 용량 - 디스크 개수

RAID 1: 두 대의 HDD에 동일한 데이터 작성. 이용 가능 용량 - 디스크 수의 반절

RAID 5: 오류 정정 부호인 패리티 데이터와 함께 분산하는 방식. 이용가능 용량 - 디스크 수-1개

2) 복제

-1) 단방향

마스터 -> 슬레이브 : 바이너리 로그 전송.

슬레이브는 바이너리 로그를 실행함으로써 마스터와 동일한 상태가 됨.

- 단방향/비동기(MySQL 채택)

슬레이브가 바이너리 로그를 받아서 실행을 완료하기 전까지는 동일하지 않음.

- 단방향/준동기화

슬레이브에 바이너리 로그가 도착할 때까지 마스터가 기다린 뒤 다시 실행 시작.

시간이 조금 더 걸림.

- 단방향/동기화

슬레이브에서 처리를 마칠때 까지 마스터가 기다림.

-2) 양방향

MySQL cluster : 데이터 노드라는 특수 서버에서 데이터를 가지고 있음.

여러 데이터 노드가 동일한 데이터 가짐.

한 데이터 노드 업데이트 -> 다른 데이터 노드에 동기화

3) 장애 대처: 현재는 슬레이브가 다운되면 새로 만들어 전체 복구하는 방법 채택

데이터양이 많을 수록 부하 증가.

5. 트랜잭션

1) 처리 중간에 멈춘 상태에서 데이터 복구

2) 무정지성 : 장애 발생 뒤에도 다시 정상가동을 도움(백업 이후의 처리에 대한 기록이 남음)

LSN(Log Sequence Number)가 증가하고 REDO 로그 생성.

commit마다 데이터를 변경하는 것이 아니라 redo 로그를 생성한뒤 한 번에 처리

sequential write은 처리가 빠르므로 부하 작음.

◆

샤딩:

같은 테이블 스키마를 가진 데이터를 다수의 데이터베이스에 분산하여 저장하는 방법

무결성

1. 영역 무결성

- 한 컬럼에 대해 NULL의 허용 여부와 타당한 데이터 값들을 지정합니다.

- 자료형(Data type), 규칙과 제약(Rules), 값 범위 등을 제한합니다.

2. 참조 무결성

- 기본 키와 참조 키 간의 관계가 항상 유지됨을 보장합니다.

- 참조되는 테이블의 행을 이를 참조하는 참조키가 존재하는 한 삭제될 수 없고, 기본키도 변경될 수 없습니다.

3. 개체 무결성

- 테이블에 있는 모든 행들이 유일한 식별자를 가질 것을 요구합니다.

출처: https://jwprogramming.tistory.com/53 [개발자를 꿈꾸는 프로그래머]

1. DB 구성

클라이언트 : SQL을 주는(요청을 하는) 프로그램

서버: SQL을 처리하는 프로그램

2. SQL

DDL : Data Definition Language: CREATE, DROP, ALTER

DML : Data Manipulation Language: SELECT, INSERT, DELETE

DCL : Data Control Language: COMMIT(실행 확정), ROLLBACK(실행 취소), GRANT(권한 부여), REVOKE(권한 제거)

3. SQL작성 규칙

- 마지막에 ;

- 대문자, 소문자 구분이 없다.

4. 이름 짓기 규칙

- 영문자, 숫자, 언더바(_)만 가능

- 첫글자는 영어

5. SQL 규칙

1) 집약과 정렬

- WHERE은 FROM 바로 뒤에 온다.

- GROUPBY 는 WHERE 뒤에 온다.

- FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY

: 실행 순서는 WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY.

(따라서 GROUP BY에는 약칭 사용 불가)

(따라서 ORDER BY에는 약칭 사용 가능/ 집약 함수 사용 가능)

- 주석은 -- 와 /* */

- GROUP BY: 행에 대한 조건

- HAVING: 그룹에 대한 조건

6. 연산자

- =와 <>

- IS NULL ( NULL은 비교 연산시 -> 불명(UNKNOWN)

- AVG

7. transaction

세트로 실행해야할 하나 이상의 갱신 처리 집합

BEGIN TRANSACTION;

COMMIT;

ACID 특성

Atomic(원자성): 트랜잭션이 끝난 시점에 모든 갱신 처리가 실행된 상태/모두 실행되지 않은 상태로 종료되는 것을 보증

Consistency(일관성): 트랜잭션의 처리는 데이터베이스의 제약(NOT NULL 등)을 지킨다.

Isolation(독립성): 트랜잭션간에 서로 간섭하지 않는다.

Durability(지속성): 트랜젝션이 종료되면 해당 시점의 데이터 상태가 지속된다.

8. 뷰

- ORDER BY 사용 불가

- DISTINCT, GROUP BY, HAVING 사용 안하는 경우, FROM에 테이블이 하나인 경우, 갱신 가능

9. 서브쿼리

- 마지막에 AS로 이름을 붙인다.

- 스칼라 서브쿼리: 값이 하나인 서브쿼리 -> SELECT문이나 WHERE에 사용 가능

SELECT goods_id FROM Goods

WHERE sell_price > (SELECT AVG(sell_price) FROM Goods);

- 상품 분류별 평균 판매 가격을 비교하자.

SELECT goods_id, goods_name

FROM Goods AS S1

WHERE sell_price > (SELECT AVG(sell_price) FROM Goods AS S2

WHERE S1.goods_class = S2.goods_class 

GROUP BY goods_classs);

10. 함수

1) 함수

- || : 문자열 연결( 'abc' 'de -> 'abcde'

- LENGTH : 문자열 길이

- REPLACE(<col>, <from>, <to>)

- SUBSTRING(<col> FROM <start> TO <counts>)

- CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP

- EXTRACT(<datetime type> FROM <date>) : 날짜 요소 추출

- CAST(<pre> AS <post>) : 형변환

- COALESCE(<data1> <data2> ...): 처음으로 NULL이 아닌 값 반환

2) 술어

 - LIKE , %

- BETWEEN:

SELECT * FROM <table> WHERE sell_price BETWEEN 10 AND 100;

- IN: 서브쿼리 사용 가능

SELECT * FROM <table> WHERE goods_id IN (1,2,3);

SELECT * FROM <table> WHERE goods_id IN (SELECT goods_id FROM Storegoods

WHERE sell_price > 500);

- EXIST: 조건을 만족하는 레코드가 존재하는가?

SELECT * FROM <table> WHERE EXISTS (SELECT goods_id FROM Storegoods AS TS

WHERE TS.sell_price > 500

AND TS.store_id = '00C');

3) CASE

검색 case문

SELECT goods_name, 

CASE WHEN goods_class  = '의류' THEN 'A:' || goods_class

 WHEN goods_class  = '사무용품' THEN 'B:' || goods_class

 WHEN goods_class  = '주방용품' THEN 'C:' || goods_class

 ELSE NULL

END AS abc_class

FROM Goods;

단순 case문

SELECT goods_name,

CASE goods_class  WHEN '의류' THEN 'A:' || goods_class

 goods_class WHEN '사무용품' THEN 'B:' || goods_class

 goods_class WHEN '주방용품' THEN 'C:' || goods_class

 ELSE NULL

END AS abc_class

FROM Goods;

4) 집합 연산

<SELECT문>

UNION

<SELECT문>

 - 열의 수와 데이터 타입이 동일할것

- UNION ALL은 중복 허용

- INTERSECT

- EXCEPT: 차집합

5) JOIN

INNER JOIN, OUTER JOIN

FROM 뒤에 온다. FROM과 한몸!

FROM -> JOIN -> WHERE

11. 윈도우 함수

<윈도우 함수> OVER (PARTITION BY <columns> ORDER BY <columns>)

PARTITION BY 생략 가능

1) RANK : 순위

SELECT goods_name, RANK() OVER (PARTITION BY goods_class ORDER BY sell_price)

FROM Goods;

2) DENSE_RANK: 중복이 있어도 후순위를 건너뛰지 않음.

3) ROW_NUMBER: 순위에 상관 없이 연속 번호

4) 집약 함수 사용

-1) SUM

SELECT goods_name, SUM(sell_price) OVER (ORDER BY goods_id) AS current_sum

FROM Goods;

누적 합계

-2) 이동평균

SELECT goods_name, SUM(sell_price) OVER (ORDER BY goods_id ROWS 2 PRECEDING) AS current_sum

FROM Goods;

앞의 2 행을 포함한 이동평균.

FOLLOWING 사용 가능

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

12. GROUPIN 함수

1) ROLLUP : 그룹별 값 -> 그룹을 통합한 값

2) GROUPIN

3) CUBE

4) SETS

CREATE DATABASE <>;

CREATE TABLE <>;

DROP TABLE <>;

◆ ALTER TABLE <> ADD COLUMN <column name> <datatype> <constraint>;

◆ ALTER TABLE <> DROP COLUMN <>;

◆ ALTER TABLE <> RENAME TO <>; RENAME TABLE <> TO <>;

◆ SELECT '상수' AS <sangsu column name> FROM <>;

◆ SELECT DISTINCT <> FROM <>;

◆ SELECT DISTINCT COUNT(goods_classify) FROM GOODS; 행수를 센 뒤에 중복 제거

◆ SELECT SUM(DISTINCT sell_price) FROM GOODS; 중복 제거 뒤 합.

◆

INSERT INTO <table name> (<column names>) VALUES (<values>);

INSERT INTO <table name> VALUES (<values>);

INSERT INTO <table name> (<column names>)

SELECT <columns> FROM <table name>;

DELETE <> FROM <> WHERE ;

UPDATE <table name> SET <column name> = <value> WHERE <>;

UPDATE <table name> SET sell_price = sell_price * 10;

CREATE VIEW <>  (<view colums>)

AS

<select 문>;