[DEV] 8주차. 데이터 웨어하우스 관리와 고급 SQL과 BI 대시보드(5)
1. 시각화 툴
- 대시보드 혹은 BI (Business Intelligence) 툴이라고 부르기도 함
- KPI (Key Performance Indicator), 지표, 중요한 데이터 포인트들을 데이터 기반으로 계산/분석/표시해주는 툴
- 테이블 형태로 알아보기 쉽게 요약해주는 것도 시각화
- 결정권자들로 하여금 데이터 기반 의사결정을 가능하게 함
- 데이터 기반 결정 (Data-Driven Decision)
- 데이터 참고 결정 (Data-Informed Decision)
- 현업 종사자들이 데이터 분석을 쉽게 할 수 있도록 해줌
툴 종류
- Excel, Google Spreadsheet
- Python : EDA에 더 적합
- Looker (Google)
- 다른 사람이 만든 대시보드를 가져와서 나에게 맞게 수정할 수 있음 (Tableau에 비해 매우 쉬움)
- 대시보드 디스커버리 이슈가 발생할 수 있음
- LookML이 자체 언어로 데이터 모델을 만드는 것으로 시작
- 내부 고객뿐만 아니라 외부 고객을 위한 대시보드 작성 가능
- 다양한 기능 제공
- 다른 사람이 만든 대시보드를 가져와서 나에게 맞게 수정할 수 있음 (Tableau에 비해 매우 쉬움)
- Tableau (세일즈포스)
- 다양한 제품군 보유. 일부는 사용이 무료
- 제대로 배우려면 시간이 꽤 필요하지만 강력한 대시보드 작성 가능
- 만들어 놓은 후 조금씩 수정하는 것이 어려움 (일부의 전문가만 대시보드 생성 가능)
- Power BI (MS)
- Apache Superset (오픈소스)
- Mode Analytics
- KPI 대시보드라기 보다는 EDA 툴에 가까움
- ReDash
- Looker 혹은 Tableau가 가장 많이 사용되는 추세
- 두 툴 모두 처음 배우는 데 시간이 필요함
- 중요한 점은 셀프 서비스 대시보드를 만드는 것!
- 데이터 민주화, 데이터 탈중앙화 -> 현업 인력이 직접 대시보드를 만들 수 있도록
- 이런 측면에서는 Looker가 더 좋은 선택이지만, 가격이 상당히 비쌈
2. Superset
- Airbnb에서 시작된 오픈소스
- 상용화 서비스도 시작됨
- https://preset.io 에서 무료 계정 생성 후 실습
- 다양한 형태의 시각화와 손쉬운 인터페이스 지원
- 대시보드 공유 지원
- 엔터프라이즈 수준의 보안과 권한 제어 기능 제공
- SQLAlchemy와 연동
- 다양한 DB 지원
- Druid.io와 연동하여 실시간 데이터의 시각화도 가능
- API와 플러그인 아키텍처 제공으로 인한 확장성이 좋음
Superset 구조와 용어
- Flask와 React JS로 구성됨
- 기본으로 sqlite를 메타데이터 DB로 사용
- sqlite: 병렬성이 떨어짐
- 보통 PostgreSQL이나 MySQL을 설치하여 사용
- Redis를 캐싱 레이어로 사용
- SQLAlchemy가 백엔드 DB 접근에 사용됨
- Database == 관계형 데이터베이스 (ex: Redshift 등 DB 엔진)
-
Dataset == 테이블
- Dashboard는 하나 이상의 chart로 구성
3. 실습할 대시보드
두 개의 차트로 하나의 대시보드 생성
- Database로 Redshift 사용
- 채널별 Monthly Active User (MAU) 차트
- 입력 테이블(Dataset)은 analytics.user_session_summary
- Monthly Cohort 차트
- 입력 테이블은 analytics.cohort_summary
MAU 차트 입력: user_session_summary
CREATE TABLE analytics.user_session_summary AS
SELECT usc.*, t.ts
FROM raw_data.user_session_channel usc
LEFT JOIN raw_data.session_timestamp t ON t.sessionid = usc.sessionid;
- session 단의 완벽한 정보를 갖게 만든 테이블
구글 스프레드시트로 MAU 시각화
SELECT
LEFT(ts, 7) "month",
COUNT(DISTINCT userid) mau
FROM analytics.user_session_summary
GROUP BY 1
ORDER BY 1;
- 위 결과를 다운받아 mau.csv로 저장
- 이 파일을 구글 스프레드시트로 로딩
- 차트 기능을 이용하여 시각화
Cohort 분석
- 코호트
- 특정 속성을 바탕으로 나뉘어진 사용자 그룹
- 보통 속성은 사용자의 서비스 등록 월
- 코호트 분석
- 코호트를 기반으로 사용자의 이탈률, 잔존률, 총 소비 금액 등을 계산
- 코호트 기반 사용자 잔존률 (Retention)
- 보통 월 기반으로 시각화해서 보는 것이 일반적
- 우리 서비스가 얼마나 사용자들에게 의미가 있는지
Cohort 차트 입력: cohort_summary
CREATE TABLE analytics.cohort_summary as
SELECT cohort_month, visited_month, cohort.userid -- 사용자마다 방문한 달 수만큼 레코드 생성됨
FROM (
SELECT userid, date_trunc('month', MIN(ts)) cohort_month -- 처음 방문한 달
FROM raw_data.user_session_channel usc
JOIN raw_data.session_timestamp t ON t.sessionid = usc.sessionid
GROUP BY 1
) cohort
JOIN (
SELECT DISTINCT userid, date_trunc('month', ts) visited_month -- 방문한 달
FROM raw_data.user_session_channel usc
JOIN raw_data.session_timestamp t ON t.sessionid = usc.sessionid
) visit ON cohort.cohort_month <= visit.visited_month and cohort.userid = visit.userid;
구글 스프레드 시트로 해보는 코호트 시각화
SELECT
DATEDIFF(month, cohort_month, visited_month) month
cohort_month,
COUNT(userid) users
FROM analytics.cohort_summary
GROUP BY 1, 2
ORDER BY 1, 2;
- 위 결과를 다운받아 cohort.csv로 저장
- 구글 스프레드시트로 로딩
- 피봇 테이블 기능을 사용하여 시각화
4. Superset 설치
- Docker 이용 설치
- Superset 오픈소스를 그대로 쓰는 형태
- Preset.io 무료 starter 플랜
- 회사 이메일 필요
- Superset 오픈소스 기반으로 변경된 버전
- 오픈소스 버전과 크게 다르지 않음
Preset.io
학교 계정으로 무료 체험 시작 & workspace 설치
- workspace: 하나의 superset 설치한 것이라고 생각하면 됨
- 하나의 워크스페이스에 다수의 데이터베이스 연결 가능
- 다수의 워크스페이스를 다수의 사용자 팀 간에 공유할 수 있고, 권한 부여할 수 있음
Redshift 연결
sql lab
chart - add dataset
Docker Superset 실행
1) https://superset.apache.org/docs/installation/installing-superset-using-docker-compose/ 설치 문서 참조
2) Superset Github repo 클론
- git clone https://github.com/apache/superset.git
3) superset 폴더로 이동
- cd superset
4) Docker Engine 실행
5) 아래 두 개 명령 실행
- docker-compose -f docker-compose-non-dev.yml pull
- docker-compose -f docker-compose-non-dev.yml up
- pull한 docker 컨테이너 실행!
- 특정 버전을 다운로드하려면 아래를 먼저 실행
- git checkout 1.4.0
6) http://localhost:8088으로 웹 UI 로그인
- admin:admin 사용
docker-compose -f docker-compose-non-dev.yml up 으로 컨테이너 실행!
<localhost:8088> 웹 UI 접속
docker engine에서 superset 실행됨
exec: "docker-credential-desktop.exe": executable file not found in $PATH ERROR
~/.docker/config.json 파일에서
credsStore -> credStore 로 변경!
no matching manifest for linux/arm64/v8 in the manifest list entries ERROR
- mac m1인 경우
- superset 폴더 내 docker-compose.yml, docker-compose-non-dev.yml에서
- service 안의 superset 내용에
platform: linux/amd64추가
5. Redshift 연결하고 MAU 차트 생성
Redshift 연결
[Database Connections] 선택
오픈소스 버전에는 Redshift가 없기 때문에, Redshift와 호환이 되는 PostgreSQL 선택
Datasets 추가
사용할 table 선택하여 업로드
MAU 차트 생성
- 이름: MAU
- X-Asis: ts
- Time grain: month
- Metics
- COUNT_DISTINCT(userid)
- Dimension: channel
dataset 선택 & line chart 선택
위의 설정대로 컬럼 끌어오기!
이 차트는 한 개의 채널만 시각화한 것
채널 별로 차트를 보고 싶다면 DIMENSIONS에 channel 컬럼 추가
6. Cohort 차트 생성
Datasets 추가
dataset에
cohort_summary 테이블 추가
chart - pivot table 선택
Cohort 차트 생성 (1)
- 이름: Monthly Cohort
- Columns: visited_month
- Rows: cohort_month
- Time Grain: Month
- Metrics: COUNT(*)
Cohort 차트 (2)
- 위에서 만든 Monthly Cohort 차트에서
Columns만 변경- 컬럼: [년도, 월] -> [cohort month 이후 몇 번째 달인지]
- 일관성이 있고, 보기가 편해짐 (Retention 계산이 쉬워짐)
- 이름: Monthly Cohort
- Columns: DATEDIFF(month, cohort_month, visited_month)
- Rows: cohort_month
- Time Grain: Month
- Metrics: COUNT(*)
차트 저장과 동시에 대시보드 저장
[Edit Dashboard] 으로 길이, 위치 등 조정 가능
MAU 차트 추가
chart - MAU 차트에서 대시보드로 SAVE
7. NPS 차트 생성
- NPS: ‘주변에 추천하겠는가?’ 질문을 기반으로 한 만족도 조사에서 10, 9점 고객(promoter)의 비율에서 0-6점 고객(detractor)의 비율을 뺀 것
- 이름: NPS
- X-AXIS: create_at
- TIME GRAIN: Month
- METRICS:
ROUND ((COUNT(CASE WHEN score >= 9 THEN 1 END) - COUNT(CASE WHEN score < 6 THEN 1 END))::float/COUNT(1) * 100, 2)
KPI Dashboard
8. 정리
- 클라우드 웨어하우스의 기능은 대동소이
- 다양한 관점에서 봤을 때 Snowflake, BigQuery, Redshift 순으로 추천
- 가변비용 모델이 일반적으로 고정비용 모델보다 더 큰 데이터 처리 가능
- 작은 회사이고 비용이 중요하다면 Redshift Serverless가 더 좋음
- 데이터 활용이 늘어나면 데이터 품질 유지와 개인정보 보호가 중요
- 데이터 민주화와 데이터 탈중앙화는 거스를 수 없는 트렌드
- 데이터 거버넌스는 데이터 품질 유지와 개인정보 보호를 보장하기 위한 프로세스
- 대시보드
- 기술적인 분석 중심 대시보드 기술과 아닌 것 존재
- 후자: Tableau, Looker, Power BI, Superset 등 -> KPI/지표 대시보드
- 전자: Mode Analytics, Python Notebook, Excell 등
- 기술적인 분석 중심 대시보드 기술과 아닌 것 존재