Redshift를 Superset에 연동하고 대시보드 만들기 - TIL230526

📚 KDT WEEK 8 DAY 5 TIL

• 다양한 시각화 툴
• Superset

---

 

🟥 다양한 시각화 툴

= 대시보드 혹은 BI(Business Intelligence)툴

• KPI(Key Performance Indicator), 지표, 중요한 데이터 포인트들을 데이터를 기반으로 계산/분석/표시하는 툴
• 소스가 되는 데이터의 품질이 중요하다.
• 데이터 분석이 쉬워지고 데이터 기반 의사결정이 가능해진다.

 

시각화 툴의 종류

사람들이 가장 많이 사용하는 툴 : Excel, Google Spredsheet

전문적인 툴 : Looker(구글), Tableau, Power BI, Apache Superset(오픈소스), AWS Quicksight

 

• Looker와 Tableau가 가장 많이 사용되는 추세지만 배우는데 시간 필요
  • Tableau가 가격이 싸고 더 투명하며 무료버전도 있음
  • Looker는 셀프서비스 대시보드를 만드는데 더 좋음

 

 

대시보드 만들어보기

DB는 Redshift사용

채널별 Monthly Active User 차트 : 입력 테이블(Dataset)은 anlytics.user_session_summary

Monthly Cohort 차트 

> 코호트(Cohort) ?

특정 속성을 기준으로 사용자 그룹을 나누는 것 ex) 사용자의 서비스 등록월이 같은 사람끼리 묶기

코호트를 기반으로 사용자 이탈률, 잔존률, 총 소비금액 등을 계산

MAU 자체 데이터만으로는 의미가 크지는 않고, MAU 기반으로 코호트 분석을 하는 것이 좋다.

 

구글 스프레드시트에서 시각화해보기

 

Redshift에 가지고있던 테이블을 사용하여 MAU 테이블을 하나 만들고, CSV 파일로 저장하였다.

 

Colab에서 CSV파일을 생성하는 모습

 

구글 시트로 가져와서 차트를 생성할 데이터를 드래그하고, 빨간 원 부분을 클릭하면 아래처럼 생성된다.

 

굉장히 빠르게 생성된다.

 

Cohort 데이터도 피봇 시각화를 한번 해보자.

 

아래는 Cohort 테이블을 만들기 위해 사용한 SQL 쿼리문이다.

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;

 

테이블에는 사용자가 첫 방문한 달(cohort_month) , 방문했던 달(visited_month), 사용자 아이디(userid)가 기록된다.

 

이걸 아까처럼 CSV 파일로 만들고..

Colab에서 CSV 파일로 저장하는 모습

 

구글 시트에서 열어서 피봇 테이블을 만들어보자

피봇 테이블은 데이터 선택(드래그) > 삽입(Insert) > 피봇 테이블(Pivot table)로 선택한다.

 

피봇 테이블을 생성하는 모습

 

위에서 만들기 버튼을 누르고 나면 행, 열, 값을 어떤 값으로 넣을지 구성할 수 있다.

 

행, 열, 값을 설정해주면 바로바로 시각화가 완성된다.

 

---

 

🟦 Superset 사용하기

 

Superset

 

 

• 다양한 형태의 visualization과 손쉬운 인터페이스 지원 및 공유 가능
• 엔터프라이즈 수준의 보안과 권한 제어 기능 제공
• SQLAlchemy와 연동되어 이를 지원하는 다양한 데이터베이스 지원
• Druid.io와 연동하여 실시간 연동도 가능
• Flask + React JS로 구성되며 sqlite를 메타데이터 DB로 사용

 

간단한 구조

 

• Database : 관계형 데이터베이스 ex) Redshift
• Dataset :  테이블
• Dashboard : 하나 이상의 chart로 구성

 

Superset을 사용하려면?

 

1.  Docker
2.  Preset.io에서 Starter 플랜 이용하기

 

Docker란?

 

특정 프로그램과 필요한 소프트웨어들을 하나의 패키지로 만드는 것 --> Docker Image

• Image는 다른 사람들과 공유 가능
• 다른 사람들과 공유하는 공유소 = Docker Registry(Docker Hub)
• Docker Image를 실행시킨 것 = Docker Container (Docker Engine 필요)

 

Preset.io에서 플랜을 이용하는 경우, 아래의 링크에서 가입하고 절차만 따라가면 쉽다.

https://preset.io/

Modern BI Powered by Open Source Apache Superset™

 

나는 Docker를 사용해서 오픈소스 Superset을 사용해보기로 했다.

 

➡️Docker Desktop 설치 및 설정

 

https://www.docker.com/products/docker-desktop/

Download Docker Desktop | Docker

 

윈도우의 경우 Docker Desktop을 사용하려면 아래의 추가 설정이 필요하다.

 

1. Hyper-V(가상디바이스) 설정하기

 

윈도우 파워셀을 관리자 권한으로 실행 후, 아래의 명령어를 입력하고 재부팅하면 된다.

Enable-WindowsOptionalFeature -Online -FeatureName Microsoft-Hyper-V -All

 

2. WSL2 설치

 

아래의 블로그를 참고하자

https://goddaehee.tistory.com/313

[ Windows ] docker desktop 설치하기 (WSL2 Ubuntu 활용)

 

➡️ Docker Desktop 설정

Superset을 Container에서 구동하기 위해서는 최소 8GB(윈도우의 경우)의 메모리를 할당해야 한다고 한다.

이는 Docker Desktop 설정에서 맥의 경우 간단하게 설정할 수 있지만, 윈도우의 경우는 조금 다르다.

 

조금 찾아보니, 기본 설정으로 윈도우 메모리의 80%가 잡혀있다고 한다.

설정을 바꿔주고 싶다면 .wslconfig 파일을 생성하거나 수정해야한다.

 

방법은 아래 블로그를 참고하자.

https://kibua20.tistory.com/63

WSL 2.0 Global configuration (.wslconfig) 을 통한 성능 향상

 

참고로, 비주얼 스튜디오가 있는 경우 터미널에서 code .wslconfig 를 통해 편하게 수정 가능하다. (경로는 위 블로그참고)

 

➡️ Superset 오픈소스 가져오기

터미널에서 폴더 하나를 만들고, 그 안에 git clone을 해보자.

 

git clone https://github.com/apache/superset.git

 

git clone이 완료되면 생성된 superset 폴더에 들어가서 다음의 명령어를 순서대로 실행한다.

명령어를 실행하기 전에, Docker Desktop이 실행되어 있어야 한다.

 

 docker-compose -f docker-compose-non-dev.yml pull
 docker-compose -f docker-compose-non-dev.yml up -- 실행

 

이 과정은 시간이 조금 걸린다.

 

과정이 완료되면 Docker Desktop이 아래처럼 나타난다.

 

superset이 잘 컨테이너에 들어간 모습이다.

 

superset_app의 Port(s) 항목에 보이는 로컬 호스트 서버 (8088:8088)에 접속하면..

 

로그인 화면이 보인다.

 

admin/admin 으로 로그인해보자.

 

Superset이 제공하는 UI를 확인할 수 있다.

 

정상적으로 화면이 나타나면 성공!

 

 

➡️Redshift Database 연결하기

 

Preset.io를 사용했든, Docker를 사용했든 거의 비슷한 구성의 UI를 볼 수 있을 것이다.

 

데이터베이스를 연결하기 위해 오른쪽 상단의 Settings 옵션에서 Database Connections를 클릭하자.

 

 

하이라이트된 부분을 누르면 된다.

 

다음 화면의 오른쪽 상단에서 +DATABASE 버튼을 눌러 데이터베이스를 추가하자. 

 

Docker로 접속한 경우 아래의 화면을 볼 수 있다.

 

Docker의 경우

 

Preset.io의 경우도 비슷하게 나오지만, 좀 더 자세하게 나온다.

Redshift를 연결하고 싶다면 Preset.io는 하단에 SUPPORTED DATABASES 옵션에서 찾을 수 있다.

나처럼 Docker의 경우 Redshift가 지원하는 PostgreSQL을 선택하면 된다.

 

다음으로 아래의 창에 정보를 입력하면 된다.

• Host : Redshift Workgroup의 엔드포인트
• Port : Redshift 포트인 5439
• Database : Redshift 네임스페이스의 데이터베이스 이름
• Username / Password : 접속할 사용자의 정보 (Redshift DB계정)

 

최하단의 SQLAlchemy URL을 사용할 수도 있다.

 

성공적으로 연결이 된다면 DB가 정상적으로 등록된 것을 확인할 수 있다.

 

 

➡️연결한 DB로 Dataset(차트) 만들기

 

상단 탭에서 Datasets을 누르고, 오른쪽 상단의 +DATASET 버튼을 눌러 생성해보자. 

 

Datasets을 눌렀을 때 보이는 화면

 

생성버튼을 누르면 아래의 이미지가 나타난다.

아까 연결했던 DATABASE를 선택하면 저장된 스키마들이 나타난다.

차트를 생성할 스키마와 테이블을 선택하고, 오른쪽 하단의 CREATE DATASET AND CREATE CHART 를 누르자. 

 

Dataset을 생성하는 모습

 

다음 화면으로 넘어가면 여러 가지 다양한 종류의 차트를 확인할 수 있다.

원하는 형태의 차트를 선택하고 하단의 CREATE NEW CHART를 눌러 생성해보자. 

 

나는 Line Chart를 골랐다.

 

차트를 생성하기 위해 X축과 Y축을 설정하는 화면을 볼 수 있다.

나는 MAU, 월별 실제 사용자를 시각화하기 위한 Line Chart를 그리려고 한다. 

 

Line Chart를 생성하는 모습 1

 

X-AXIS를 눌러 X축의 원하는 필드 값을 지정해보자.

타임스탬프를 가진 필드를 정했더니 TIME GRAIN 옵션을 통해 연, 월, 일 등을 정할 수도 있다.

월별 사용자를 그릴 것이니 Month를 선택했다.

 

METRICS 옵션으로 Y축에 해당하는 값을 지정해보자.

보통 함수를 사용하게 되는데, 기본적으로 f(x) COUNT(*) 를 사용할 수 있었다.

하지만 이 테이블에는 사용자가 접속한 모든 기록이 남겨 있고, 나는 유니크한 사용자들로만 구성하고 싶기 때문에 함수의 수정이 필요했다.

 

함수의 수정은 아래 이미지처럼 CUSTOM SQL 옵션을 사용하면 된다.

 

유니크한 사용자를 원하므로, DISTINCT를 사용했다.

 

그리고 CREATE CHART 버튼을 눌렀더니 차트가 잘 생성된 걸 확인할 수 있다!

 

MAU Chart가 그려졌다!

 

추가로 DIMENSTIONS 옵션에 특정 필드 별로 라인을 더 그릴 수 있게 설정 가능하다.

나는 채널별로 라인을 추가로 더 구성해보았다.

 

여러 개의 라인이 생성되었다.

 

 

➡️ Superset으로 Cohort Pivot Table 생성하기

 

이번에는 이전에 생성한 Cohort 테이블을 사용하여, Pivot Table를 생성해보자.

내가 생성했던 cohort_summary 테이블안에는 Monthly Active User를 기반으로 사용자의 월별 잔존수를 나타내고 있다.

 

 

위에서 설명했던 방법으로 Dataset을 새로 생성한다.

 

pivot을 검색하여 쉽게 Pivot Table을 찾을 수 있다.

 

pivot을 검색하여 Pivot Table을 선택하고 차트를 생성하자.

 

cohort_month : 사용자가 처음 방문했던 날짜(월)

visited_month : 사용자가 방문했던 날짜(월)

 

아래 이미지에는 잘렸지만 이렇게 설정해주었다.

 

• Column : visited_month
• Rows : cohort_month
• TIME GRAIN : month
• METRICS : COUNT(*)

 

사용자가 처음 방문했던 달을 기준으로 다음 달에도 접속했는지 쉽게 알 수 있는 차트가 완성되었다.

 

완성된 Pivot Table의 모습

 

이번에는 Column을 cohort_month를 기준으로 몇 개월이나 차이나는지 쉽게 알아볼 수 있도록 바꿔보겠다.

CUSTOM SQL에 DATEDIFF(쉽게 시간과 시간을 뺄 수 있는 함수)를 이용할 것이다.

 

DATEDIFF(구분자, Start-Date, End-Date) 형식을 사용한다.

 

Column을 수정하는 경우, 수정하던 창 위쪽에 이름을 바꿀 수가 있다.

이 부분을 수정해주지 않고 수정하기 전의 이름을 계속 사용하면, 차트가 정상적으로 출력되지 않는다.

이름을 꼭 바꿔주자. 나는 My column이라고 만들었다.

 

아래 이미지를 보면, Column이 정상적으로 수정된 것을 확인할 수 있다.

 

새로운 Pivot Table이 잘 생성되었다.

 

---

 

🟩 Superset 대시보드 만들고 편집하기

 

만들었던 차트를 저장버튼을 눌러 저장했다면, Superset 위쪽 탭 Charts에 담겨있을 것이다.

내가 만들었던 차트를 찾아 불러오면 행과 열을 수정하던 화면으로 돌아올 수 있다.

 

나는 OWNER를 Superset Admin 필터로 해서 바로 찾았다.

 

대시보드에 넣을 차트의 이름을 클릭하여 들어가서, 오른쪽 상단의 SAVE 버튼을 누르자.

 

대시보드에 넣기 위해 ADD TO DASHBOARD에 내가 원하는 이름으로 넣고,

SAVE & GO TO DASHBOARD 버튼을 눌러 대시보드에 저장한다.

 

Save 버튼을 눌렀을 때 화면

 

나는 만들었던 두 개의 차트를 KPI Dashboard라는 대시보드에 넣었다.

오른쪽 상단의 EDIT DASHBOARD 버튼을 눌러 크기 조절과 위치를 조절할 수 있다.

 

두 개의 차트가 대시보드에 들어온 모습

 

차트를 드래그하여 크기 및 위치 조절을 자유롭게 할 수 있고,

오른쪽 탭의 LAYOUT ELEMENTS를 통해 텍스트나 구분자(Divider)를 넣을 수도 있다

 

LAYOUT ELEMENTS를 선택하니 여러 옵션이 있다.

 

대시보드가 완성되었다!

 

---