[ 프로젝트 세팅 ] ClickHouse + dbt (Windows, uv)

ClickHouse + dbt 프로젝트 세팅 방법을 남겨둔다.

이 세팅은 다음 링크의 내용을 따라 ClickHouse가 Windows 운영환경의 WSL에서 실행되고 있다는 것을 전제로 이어서 진행된다.

[ 맛보기 ] ClickHouse - OLAP 특화 데이터베이스

 

위 링크의 내용을 따라서 ClickHouse를 잘 설정했다면 다음 조건들을 만족할 것이다.

 

1. 브라우저 주소창에 localhost:8123로 접속하면 다음과 같은 화면이 나타남

 

2. DBeaver에 다음 정보로 연결이 됨

- Host: localhost

- Port: 8123

- Database/Schema: default

- Username: admin

- Password: supersecret

 

3. file 함수로 D:\clickhouse 경로의 파일을 읽을 수 있음

---

환경 설정

다음 과정을 따라 ClickHouse + dbt 프로젝트를 위한 환경을 설정한다.

uv의 활용에 익숙하지 않은 사람은 다음 링크를 확인하면 좋다.

[ 따라잡기 ] 현대적인 파이썬 프로젝트 준비 - uv 활용 dbt 프로젝트 준비

 

dbt 프로젝트 디렉토리를 만든다. 나의 경우는 D:\dbt 경로로 만들었다.

해당 경로에서 cmd를 열어 다음 명령어로 환경을 준비한다.

# uv 프로젝트 준비
uv init

# 가상환경 준비
uv venv .venv

# 패키지 설치
uv add dbt-clickhouse

 

위의 과정으로 문제없이 uv 환경의 프로젝트가 준비되었다면 dbt 프로젝트를 준비한다.

# ch_dbt_test라는 이름의 dbt 프로젝트 준비
uv run dbt init ch_dbt_test

 

위 명령어를 실행하면 다음 캡쳐와 같이 어떤 데이터베이스를 사용할건지 물어본다.

내 환경에는 dbt-duckdb와 dbt-clickhouse가 설치되어 있어서 두가지 선택지가 나오는데 하나의 패키지만 설치되어있다면 하나의 선택지만 나온다.

클릭하우스의 번호를 입력하고 엔터하면 프로젝트 생성이 완료된다.

 

dbt-duckdb 프로젝트였다면 이렇게 프로젝트 생성이 완료 되었을 때 C:\Users\<유저명>\.dbt경로에 profiles.yml이 생성되어 있을 것이다.

클릭하우스의 경우는 프로젝트 생성 마지막에 No sample profile found for clickhouse 메시지가 나오면서 C:\Users\<유저명>\.dbt 경로는 자동으로 만들어지지만 profiles.yml이 생성되지 않아서 직접 만들어줘야 한다.

 

다음과 같이 미리 설정한 값들로 profiles.yml을 새로 작성한다.

# C:\Users\<유저명>\.dbt\profiles.yml
ch_dbt:
  target: dev
  outputs:
    dev:
      type: clickhouse
      schema: default
      host: localhost
      port: 8123
      user: admin
      password: supersecret
      secure: false
      verify: false
      database: default
      threads: 1

 

그리고 dbt 프로젝트 디렉토리의 dbt_project.yml 파일의 다음 부분을 수정한다.

# D:\dbt\ch_dbt_test\dbt_project.yml
...

# 수정 전의 값
# profile: 'ch_dbt_test'

# 수정 후의 값
profile: 'ch_dbt'

...

 

이걸로 환경 설정은 완료되었다.

---

테스트

기본적으로 있는 example 모델이 잘 돌아가는지 바로 실행 해본다.

# 테스트 실행
uv run dbt run

잘 실행된 것 같고 DBeaver에서 example 모델이 생성한 테이블을 확인할 수 있다.

 

직접 모델을 만들어서 테스트 해볼까 싶어서 전에 만들어둔 LOG CREATER 데이터를 마운트 해둔 D:\clickhouse 경로에 넣어뒀다.

다음 저장소를 클론해서 동일한 로그를 만들어낼 수 있다.

GitHub - yejoonlee/GAME_LOG_CREATER

# 파일 옮긴 결과 확인
cd D:\clickhouse
dir /s /b
# 출력 결과
# D:\clickhouse\log_creater
# D:\clickhouse\log_creater\20250501
# D:\clickhouse\log_creater\20250502
# D:\clickhouse\log_creater\20250503
# D:\clickhouse\log_creater\20250504
# D:\clickhouse\log_creater\20250505
# D:\clickhouse\log_creater\20250501\gamelog_20250501.parquet
# D:\clickhouse\log_creater\20250501\item_20250501.parquet
# D:\clickhouse\log_creater\20250501\login_20250501.parquet
# D:\clickhouse\log_creater\20250502\gamelog_20250502.parquet
# D:\clickhouse\log_creater\20250502\item_20250502.parquet
# D:\clickhouse\log_creater\20250502\login_20250502.parquet
# D:\clickhouse\log_creater\20250503\gamelog_20250503.parquet
# D:\clickhouse\log_creater\20250503\item_20250503.parquet
# D:\clickhouse\log_creater\20250503\login_20250503.parquet
# D:\clickhouse\log_creater\20250504\gamelog_20250504.parquet
# D:\clickhouse\log_creater\20250504\item_20250504.parquet
# D:\clickhouse\log_creater\20250504\login_20250504.parquet
# D:\clickhouse\log_creater\20250505\gamelog_20250505.parquet
# D:\clickhouse\log_creater\20250505\item_20250505.parquet
# D:\clickhouse\log_creater\20250505\login_20250505.parquet

 

다음과 같이 스테이징 모델을 작성해서 다시 실행해본다.

# D:\dbt\ch_dbt_test\models\bronze\stg_gamelog.sql
{{ config(
    materialized='table',
    schema='bronze',
) }}

SELECT *
FROM file('./log_creater/*/gamelog_*.parquet', Parquet)

# D:\dbt\ch_dbt_test\models\bronze\stg_item.sql
{{ config(
    materialized='table',
    schema='bronze',
) }}

SELECT *
FROM file('./log_creater/*/item_*.parquet', Parquet)

# D:\dbt\ch_dbt_test\models\bronze\stg_login.sql
{{ config(
    materialized='table',
    schema='bronze',
) }}

SELECT *
FROM file('./log_creater/*/login_*.parquet', Parquet)

# 명령어 실행 위치는 D:\dbt\
uv run dbt run

기존에 있던 2개의 example 모델과 함께 3개의 모델이 6.73초만에 성공적으로 실행된 것을 확인할 수 있다.

 

DBeaver에서도 모델에 따른 테이블이 생성된 것을 확인할 수 있다.

---

리텐션 지표 집계 만들기

위의 스테이징과 함께 다음 매크로 및 모델들로 리텐션 지표를 집계하는 과정을 진행 해본다.

 

우선 리텐션 결과를 만들기 위한 datediff_join 모델을 만든다

# D:\dbt\ch_dbt_test\models\silver\datediff_join.sql
{{ config(
    materialized='table',
    schema='silver',
) }}

select JOINDATE
	, dateDiff('day', toDate(JOINDATE), toDate(DATE)) as diff
	, count(distinct UID) as CNT
from `default_bronze`.stg_login
group by JOINDATE, DATE
order by JOINDATE, DATE

 

다음으로는 리텐션 기간을 조정할수도 있고 편의성을 위한 매크로를 작성한다.

# D:\dbt\ch_dbt_test\macros\retention_columns.sql
{% macro generate_retention_columns(max_day) %}
    {% set sql_parts = [] %}

    {% for day in range(1, max_day + 1) %}
        {% set part %}
            , MAX(CASE WHEN diff = {{ day }} THEN CNT ELSE NULL END)
              / MAX(CASE WHEN diff = 0 THEN CNT ELSE NULL END) * 100 AS D{{ day }}
        {% endset %}
        {% do sql_parts.append(part.strip()) %}
    {% endfor %}

    {{ sql_parts | join('\n') }}
{% endmacro %}

 

마지막으로 리텐션 지표의 모델을 작성한다.

# D:\dbt\ch_dbt_test\models\gold\retention.sql
{{ config(
    materialized='table',
    schema='gold',
) }}

select JOINDATE
	, MAX(CASE WHEN diff=0 then CNT else NULL end) as NRU
	{{ generate_retention_columns(4) }}
from {{ref('datediff_join')}}
group by JOINDATE
order by JOINDATE

 

이후 dbt run 결과는 다음과 같다.

---

로그 크리에이터로 만든 깔끔하고 단순한 데이터라 그런가 생각보다 트러블슈팅 할 거리가 나타나지 않고 순탄하게 진행할 수 있었다.

앞으로 여기에 Superset을 붙이거나 Kestra로 플로우를 만들어 돌리는 등의 실습을 해보면 좋을듯 하다.