크로스테이블이란, 행과 열이 교차하는 부분에 숫자 데이터가 들어가는 테이블 크로스테이블 예시
크로스테이블은 사람들이 보기에는 매우 편하지만, 열을 늘리는 것이 간단하지 않으므로 데이터베이스에서는 다루기가 어렵다.
트랜잭션 테이블이란, 열방향으로 데이터가 증가하지 않고 행 방향으로 증가하는 테이블
트랜잭션 테이블 예시
데이터는 트랜잭션 테이블의 형태로 저장이 되어야 한다. 이트랜잭션 테이블의 형태에서, 보고서를 만들 때 사람들이 보기 쉬운 크로스 테이블로 변경을 해주어야 한다. 이 작업을크로스 집계라고 한다.
즉, 크로스 집계란 트랜잭션 테이블에서 크로스 테이블로 변경해주는 작업
이는 엑셀의 룩업 기능을 활용해 테이블을 결합하거나, 엑셀 피벗 테이블 기능을 활용할 수 있다.
또한 Tableau, Pandas(pivot_table), SQL(각 집계함수)를 이용해 활용할 수도 있다. 보통 데이터가 많은 경우는 SQL을 활용해 크로스 집계를 수행한다.
따라서, 아래와 같은데이터 집계의 프로세스(시각화 프로세스)가 구현된다.
🔥 데이터 집계 프로세스 (시각화 프로세스)
데이터 레이크(SQL이용해서)👉🏻데이터 마트(시각화 도구 이용해서)👉🏻크로스 테이블 혹은 대시보드
※ 마트의 크기에 따라 시스템 구성이 결정되며, 시각화 정보량은 마트의 크기와 Trade-Off 관계다.
02. 열 지향 스토리지에 의한 고속화
데이터베이스를 신속하게 집계하려면, 미리 집계에 적합한 형태로 변환하는 것이 중요하다. 이때, 데이터가 많으면 데이터를 가능한 한 작게 압축해 데이터를 여러 디스크에 분산해서 데이터 로드의 지연을 줄인다. 압축 시에는 어떤 식으로 압축할지 결정해야 하는데, 그 종류가 1) 열지향 데이터베이스와 2) 행지향 데이터베이스다.
🔥 만약 데이터 양이 5GB 내외라면? MySQL이나 PostgreSQL 등의 일반적 RDB를 데이터 마트에 사용한다. 이러한 RDB는 지연이 적고, 많은 수의 클라이언트가 동시 접속해도 성능 문제가 발생하지 않기 때문이다.
그러나, RDB는 메모리가 부족하면 성능이 저하되므로, 데이터의 양에 따라 마트를 어떤 것으로 쓸지 고민해야 한다.
2-1. 열지향 데이터 베이스
열지향 데이터 베이스란, 분석할 일부 칼럼만 집계할 수 있도록 열 단위로 데이터를 저장하는 DB
같은 열은 데이터가 주는 유사한 특징(비슷한 정보)이 있기 때문에, 압축이 수월하다는 장점이 있다.
※ 행지향 데이터베이스보다 평균 1/10로 압축이 가능하다.
2-2. 행지향 데이터 베이스
행지향 데이터 베이스란, 일반적 DB의 구조로, 인덱스를 넣어 데이터를 저장하는 DB
MySQL이나 Oracle DB가 행지향 DB에 해당한다.
이는 인덱스가 있어야, 원하는 레코드를 찾을 수 있어서 많은 디스크 I/O가 발생해 성능이 저하될 수 있다. 그리고 데이터 분석에서는 굳이 인덱스를 분리해 분석할 필요가 없다. 따라서 데이터가 많아 압축관리를 해야할 땐 인덱스에 의지하지 않는 열지향 데이터베이스를 많이들 사용한다고 한다.
2-3. MPP 데이터베이스
데이터를 빠르게 처리하기 위해, 데이터를 압축해 여러 디스크에 분산 저장을 해서, 데이터를 로드할 때 빠르게 하도록 설계한 데이터베이스다.
이는 데이터를 집계해 분석하는 환경에서 최적화돼있다. 데이터가 많은 경우에는 MPP 데이터베이스로 데이터 마트를 설계하기도 한다.
MPP 데이터베이스 활용 예시
문제: 1억 레코드로 이뤄진 테이블의 합계 계산 해결방법: 10만 레코드씩 1,000개 태스크로 분리해 수행. 이후 모든 결과를 모아 총 합계 계산
※ CPU 간 병목 이슈 발생 방지를 위해, 태스크의 양을 고루 분산하는 것이 중요하다.
2-4. MPP 데이터베이스와 열지향 데이터베이스
MPP 데이터베이스와 열 지향 스토리지 개념을 결합하면, 매우 빨라진다.
그러나 열지향 데이터베이스는, 대량의 데이터를 디스크에서 읽어서, 1 차례 당 쿼리 시간이 길다. 따라서 한 사람의 명령이 매우 클 경우 리소스를 모두 할당받아 다른 업무에 지장이 갈 수 있다. 따라서 리소스를 제한하는 등의 명령을 통해 MPP 데이터베이스를 수행한다.
마스터 테이블과 트랜잭션 테이블을 분리해서 저장하되, 마스터 테이블에 있는 내용정보를 받아, 트랜잭션 데이터에 연결한다.
디맨젼 테이블과 팩트 테이블은, 한 개의 팩트 테이블에 트랜잭션이 기록되고, 여러 개의 디맨젼 테이블이 데이터를 분류하기 위한 속성값으로 사용된다.
이처럼, 여러 개의 디맨젼 테이블이 한 개의 팩트 테이블에 붙어 있는 모습을 스타스키마라고 한다.
스타 스키마 설계가 중요한 이유? 팩트 테이블은 실시간 데이터이므로 시간이 지날수록 매우 많아진다. 따라서 디맨젼 테이블을 최대한 늘림과 동시에 팩트 테이블의 사이즈를 최소화하는 것이 디스크 I/O를 줄일 수 있는 방법이다.
이러한 형태는 다소 과거에 많이 쓰였으나, 가장 단순하며 충분히 효율적인 방법이라고 본다.
🔥 요즘은, 데이터 웨어하우스를 설계할 때 스타 스키마 형식을 활용해 웨어하우스를 설계하고, 마트를 만들 때는 이들을 결합해 비정규화 테이블을 만드는 것이 일반적이라고 한다.
3-1-2. 테이블 비정규화
비정규화란, 하나 이상의 테이블에 데이터를 중복해 배치하는 최적화 기법
시스템의 성능 향상, 개발 및 운영의 편의성 등을 위해정규화된 데이터 모델을 통합, 중복, 분리하는 과정으로, 의도적으로정규화원칙을 위배하는 행위라고 본다.
수천만 레코드 범위에 해당하는 크기라면, 앞서 만든 스타 스키마 데이터셋을 비정규화해서 RDB(관계형 데이터베이스) 에 싣는다.
수억 레코드가 존재한다면, 칼럼 단위로 열 지향 스토리지 형식으로 데이터를 저장해준다. 이렇게 되면 칼럼 수가 아무리 늘어나도 성능에 영향을 주지 않는다. 따라서, 팩트 테이블에 모든 칼럼을 넣되 열 지향으로 압축/분산 저장한다. 이렇게 되면 디스크 I/O의 증가가 억제될 수 있다. 즉, MPP 데이터베이스 형식으로 하나의 거대한 팩트 테이블을 생성해, 분산 및 압축해 관리하는 것이다.
3-2. 테이블 시각화
비정규화 테이블이 준비가 되었다면, 이것을 다차원 모델에 의해 시각화를 수행한다. 다차원 모델은 측정값과, 측정값을 설명하는 디멘젼 테이블로 구성돼 있다. 피벗 테이블과 비슷하다. 이처럼, BI 도구 등을 이용해 시각화를 수행할 수 있다.
