CHAPTER2 빅데이터의 탐색

트랜잭션, 크로스, 피벗, 룩업 테이블

• 크로스 테이블

크로스 테이블은 보고서를 쓸떄 자주 사용되는 테이블로 행과 열에 범주가 들어가 테이블에는 숫자가 들어가게 되는 데이터다 즉 집계 데이터라고 생각하면 편하다. 때문에 사람들이 보기 편한 테이블이지만 데이터 베이스에서는 다루기 어렵다. 왜냐하면 데이터 베이스에 행을 추가하는 건 간단하지만 열을 추가하는 것은 힘들기 때문이다.

• 트랜잭션 테이블

트랜잭션 테이블은 행으로만 데이터가 늘어날 수 있도록 되어 있으며 사람이 보기는 어렵지만 데이터 베이스가 데이터를 적재하기는 간편하다. 아래 예시를 보면 알 수 있다.

• 피벗 테이블

트렌젝션 테이블에서 크로스 테이블로 변환하는 것을 크로스 집계라고 하며 크로스 집계는 피벗 테이블 기능을 이용하여 집계할 수 있다. 

피벗 테이블 기능은 구글 스프레드시트, 액셀 등 다양한 BI 도구에서 실행 가능하다.

• 룩업 테이블

데이터를 추가하는 것이 아닌 다른 테이블과 결합하고 싶을 수도 있는데 이때 사용하는 것이 룩업 테이블이다. 예를 들어 '상품ID'는 공통 칼럼으로 존재하고 각각 다른 테이블에 상품명과 카테고리가 따로 있어 합쳐서 보고 싶을 때 사용한다. 이러한 룩업 테이블은 고정된 트렌젝션 데이터와 달리 분석용도에 따라 자유로운 변경이 가능하다.  

도구에 따른 다양한 크로스 집계

• pandas에 의한 집계

house_df_org = pd.read_csv('house_price_train.csv')
house_df = house_df_org.copy()
house_df.head(3)
   Id  MSSubClass MSZoning  LotFrontage  LotArea Street Alley LotShape  \
0   1          60       RL         65.0     8450   Pave   NaN      Reg   
1   2          20       RL         80.0     9600   Pave   NaN      Reg   
2   3          60       RL         68.0    11250   Pave   NaN      IR1   

  LandContour Utilities  ... PoolArea PoolQC Fence MiscFeature MiscVal MoSold  \
0         Lvl    AllPub  ...        0    NaN   NaN         NaN       0      2   
1         Lvl    AllPub  ...        0    NaN   NaN         NaN       0      5   
2         Lvl    AllPub  ...        0    NaN   NaN         NaN       0      9   

  YrSold  SaleType  SaleCondition  SalePrice  
0   2008        WD         Normal     208500  
1   2007        WD         Normal     181500  
2   2008        WD         Normal     223500  

[3 rows x 81 columns]

house_df.pivot_table('SalePrice', 'SaleType', 'MSZoning', aggfunc = 'sum')
# 주택가격을 판매 유형과 판매유형으로 크로스 집계하여 출력함

• SQL에 의한 집계

SELECT date_trunc('month', "매출일"):: DATE AS '매출일',
	"점포 ID",
    "상품 ID",
    "고객 ID",
    sum('금액') AS "금액",
FROM "판매이력"
GROUP BY 1, 2, 3, 4;

매출일 | 점포 ID | 상품 ID | 고객 ID | 금액
--------------------------------------

SQL 실행 결과를 보면 트렌잭션 테이블인 것을 알 수 있는데 이처럼 'SQL로 데이터를 집계'하고 '다양한 BI 도구로 크로스 집계'함으로써 두 단계의 시각화 과정을 거치게 된다. 이를 통해 데이터 마트와 시각화 도구의 부하를 줄일 수 있다. 전자를 데이터 집계의 프로세스 후자를 시각화 프로세스라고 한다.

• 테이블의 종횡변환

테이블은 종방향 테이블(트랜잭션), 횡방향 테이블(크로스)로도 나뉘는데 이때 종에서 횡으로 변환하는 종횡변환을 '피벗' 그 바대인 횡종변환을 '언피벗'이라고 한다. 예시는 다음과 같다.

 

 일반적으로 SQL에서는 피벗, 언피벗이 용이하지 않아 파이썬에서 수행하며 다음과 같다. 결과를 예상해 보자.

피벗

vtable.pivot('uid', 'key', 'value')

언피벗

htable.melt('uid', var_name='key', value_name='value')

 

열 지향 스토리지에 의한 고속화

데이버테이스의 지연을 줄이기

데이터의 양이 증가하면 당연하지만 데이터 집계 시간 증가 만약 데이터 집계가 몇 분이나 걸린다면 적업 효율은 몇 배나 더 악화된다.

때문에 보통 다음과 같은 3 계층의 데이터 집계 시스템을 만든다.

데이터 레이크(수분, 수시간) -> 데이터 마트(수초) -> 시각화 도구

 

데이터 처리의 지연

데이터 마트를 만들 때는 가급적 지연이 적은 데이터 베이스가 있어야 하는데 크게 두 가지가 있는데 첫 번째가 모든 데이터를 메모리에 올리면 된다, 다만 그럴 경우 메모리를 엄청 늘려야 하는데 그게 굉장히 어려운 일이다. 때문에 데이터의 크기가 작다면 기본적인 RDB로도 데이터를 처리하고 운영하지만 RDB는 메모리가 부족할 경우 급격한 성능저하가 일어나기 때문에 디바이스 I/O가 발생한다. 

 

'압축'과 '분산'에 의한 지연 줄이기(MPP 기술)

문제점을 해결하기 위해서는 데이터를 가능한 한 작게 압축하고 그것을 여러 디스크에 분산함으로써 데이터 로드에 따른 지연을 줄일 수 있다. 보통 redshift나 구글 bigquery가 그러하며 데이터 마트에서도 많이 활용된다. 

 

행, 열 지향 데이터 베이스

데이터의 압축을 고려할 때는 꼭 열 지향 데이터 베이스를 알아야 한다.  보통 데이터 베이스는 레코드 단위로 읽고 쓰기에 최적화되어 있기 때문에 행지행 데이터베이스 라고 부르지만 데이터분석에서 사용되는 데이터 베이스는 칼럼 단위의 집계에 최적화되어 있기 때문에 열 지향 데이터라고 한다.

 행 지향 데이터 베이스는 대량의 트랜잭션을 지연 없이 처리하기에 굉장히 적합한데 이는 데이터의 끝에 새로운 레코드를 추가하기만 하면 되기 때문이다, 다만 원하는 정보를 찾기 위해서 데이터를 검색하거나 불러올 때는 인덱스를 이용해 데이터를 찾기 때문에 오래 걸린다는 단점이 있다. 따라서 인덱스에 의지 하지 않는 고속화 기술이 필요하다.

 열 지향 데이터 베이스는 일부 칼럼만 집계 대상이 된다. 컬럼 단위로 정리해 두기 땨문에 디스크 I/O도 줄이고 압축효율도 우수하다, 특히 같은 문자열 반복은 먀우 작게 압축이 가능하며 행 지향 DB대비 1/10 수준으로 압축이 가능하다 다만 구조상 집계하는 데는 고속이지만 저장하는 데는 레코드가 아닌 칼럼단위로 저장을 하기에 저장 부분에서는 시간이 오래 걸린다.

 

왼쪽이 행 지향 오른쪽이 열 지향 데이터 이미지다.

 

MPP 데이터베이스의 접근 방식

쿼리 지연을 줄이는 또 다른 방식은 MPP 아키텍처를 이용하는 것 행 지향 DB의 경우 보통 하나의 쿼리가 하나의 스레드에서 실행되나 열지향의 경우 CPU의 멀티코어를 활용해 효과적으로 지연을 줄인다.

다음 표를 보를 보고 정리하자

집계 시스템 종류	스토리지의 종류	최적의 레코드 수
RDB	행 지향	~수천만 정도
MPP 데이터 베이스	열 지향(하드웨어 일체형)	수억~
대화형 쿼리 엔진	열 지향(분산 스토리지에 보관)	수억~