데이터 분석 환경 구축 - 05. Clickhouse 실시간 데이터 분석 테이블 생성하기

🚀 이 글에서 다룰 내용

1️⃣ Kafka와 연결된 ClickHouse 테이블 생성 (환경설정 포함)
2️⃣ 실습용 데이터셋 kafka produce
3️⃣ Kafka 테이블에서 데이터 셋 확인 방법

1. Kafka와 연결된 ClickHouse 테이블 생성

🔹 Kafka 데이터를 ClickHouse에서 처리하는 방식

ClickHouse는 Kafka와 직접 연결하여 실시간으로 데이터를 가져올 수 있는 Kafka 엔진을 제공합니다.
기본적으로 Kafka → Buffer 테이블 → MergeTree 테이블의 구조로 운영됩니다.

🔹 ClickHouse 설정 변경 (Kafka 사용을 위한 설정)

먼저, clickhouse-server의 환경설정을 수정해야 합니다.
ClickHouse 설정 파일 (config.xml)에서 Kafka를 사용하도록 설정합니다. 이전에 게시했던 포스트(https://jongwho.tistory.com/21 )에서 생성한 values.yaml을 수정합니다.

[데이터 분석 환경 구축 - 02. Clickhouse 설치 및 클러스터 구성

이번 글에서는 Kubernetes 환경에서 ClickHouse를 배포 및 실행하는 방법을 설명합니다.Docker Desktop의 내장 Kubernetes를 활용하고, Helm 및 Bitnami Helm 차트를 사용하여 간편하게 ClickHouse를 배포합니다.1. 사

jongwho.tistory.com](https://jongwho.tistory.com/21)

📌 1) values.yaml 수정

• line 273: defaultConfigurationOverrides 내 xml 형식의 코드 추가

<!-- config.xml -->
<clickhouse>
    <!-- Kafka -->
    <kafka>
      <debug>all</debug>
      <security_protocol>SASL_SSL</security_protocol>
      <sasl_mechanism>SCRAM-SHA-512</sasl_mechanism>
      <sasl_username>(유저 이름 입력)</sasl_username>
      <sasl_password>(password 입력)</sasl_password>

      <kafka_topic>
          <name>(kafka 토픽 입력)</name>
          <statistics_interval_ms>4000</statistics_interval_ms>
      </kafka_topic>

      <!-- Settings for consumer -->
      <consumer>
          <auto_offset_reset>smallest</auto_offset_reset>
          <kafka_topic>
              <name>(kafka 토픽 입력)</name>
              <fetch_min_bytes>100000</fetch_min_bytes>
          </kafka_topic>
      </consumer>
    </kafka>
    ...(이하 생략)
</clickhouse>

📌 2) ClickHouse 재 배포

helm upgrade clickhouse oci://registry-1.docker.io/bitnamicharts/clickhouse -f ./clickhouse/values.yaml -n clickhouse
# or
helm upgrade clickhouse bitnami/clickhouse -f ./clickhouse/values.yaml -n clickhouse

이후 k8s 내 clickhouse 서비스를 port-forward하여 접속할 수 있도록 합니다.

• 아래 명령어로 localhost:8123 으로 clickhouse에 접근 가능합니다.

kubectl port-forward svc/clickhouse 8123:8123 -n clickhouse

반응형

 

2. ClickHouse에서 Kafka 테이블 생성하기

Kafka에서 데이터를 가져와 ClickHouse에 저장하는 테이블을 생성합니다.

🔹 1) Kafka 엔진 테이블 생성

먼저, Kafka 토픽을 구독하는 데이터베이스 및 테이블을 생성합니다.

CREATE DATABASE IF NOT EXISTS streaming_db;

CREATE TABLE streaming_db.kafka_events (
    event_time DateTime,
    user_id UInt32,
    event_type String
) ENGINE = Kafka
-- kafka_broker_list에 콤마(,)로 broker 목록을 추가합니다.
SETTINGS kafka_broker_list = 'kafka-broker-headless.kafka.svc.cluster.local:9092',
         kafka_topic_list = 'events',
         kafka_group_name = 'clickhouse_consumer',
         kafka_format = 'JSONEachRow';

👉 설명

• ENGINE = Kafka: Kafka에서 직접 데이터를 가져옵니다.
• kafka_broker_list: Kafka 브로커 주소
• kafka_topic_list: 구독할 Kafka 토픽
• kafka_group_name: Kafka Consumer Group
• kafka_format = 'JSONEachRow': JSON 데이터를 Row 단위로 변환

🔹 2) Materialized View 및 테이블 생성

Buffer 테이블을 생성하여 clickhouse 서버 성능에 저하 없이 사용하도록 설정할 수 있습니다. 하지만 실습 중 빠른 데이터 확인을 위해 Materialized View를 생성하여 kafka - clickhouse 연결을 확인합니다.

📌 Materiazlied View 및 테이블 생성

-- events 테이블 생성
CREATE TABLE streaming_db.events (
    event_time DateTime,
    user_id UInt32,
    event_type String
) ENGINE = MergeTree()
ORDER BY (event_time, user_id);

-- Materialized View 생성
CREATE MATERIALIZED VIEW streaming_db.events_mv TO streaming_db.events AS
SELECT * 
FROM streaming_db.kafka_events
;

해당 테이블 생성으로 Redpanda Console(Kafka ui)에서 Consumer group을 확인할 수 있습니다.

🔹 기타) Buffer 테이블

📌 Buffer 테이블이 필요한 이유?

Kafka에서 ClickHouse로 데이터를 바로 저장하면 MergeTree에 대한 빈번한 INSERT 작업이 발생하여 성능 저하가 발생할 수 있습니다.
이를 방지하려면 Buffer 테이블을 사용하여 RAM에 데이터를 저장한 후, 일정 조건이 충족되면 MergeTree로 배치 저장하는 방식이 필요합니다.

📌 Buffer 테이블 기본 개념

• 데이터를 RAM에 임시 저장 후 조건이 충족되면 자동 플러시 (Flush) 수행
• INSERT 속도 향상 및 MergeTree 테이블의 디스크 쓰기 부하 감소
• num_layers 값을 사용하여 병렬 버퍼링 가능
• 특정 시간(min_time), 행 수(min_rows), 크기(min_bytes) 조건을 만족하면 자동 저장

📌 Buffer 테이블 생성 (RAM 캐싱 활용)

CREATE TABLE streaming_db.buffer_events (
    event_time DateTime,
    user_id UInt32,
    event_type String
) ENGINE = Buffer(streaming_db, events, 1, 10, 100, 10000, 100000, 1000000, 10000000);

✅ 설명

• Buffer(database, 대상(flush) 테이블, 1, 10, 100, 10000, 100000, 1000000, 10000000)
  • 1: 단일 버퍼 사용 (num_layers)
  • 10: 최소 10초 후 자동 저장 (min_time)
  • 100: 최소 100개 행이 쌓이면 저장 (min_rows)
  • 10000: 최소 10KB 데이터가 쌓이면 저장 (min_bytes)
  • 10000000: 최대 10MB 데이터까지 저장 가능 (max_bytes)

✅ 데이터 흐름

Kafka → Buffer 테이블 (RAM) → MergeTree 테이블 (디스크 저장)

🚀 결과:

• Kafka에서 대량 데이터를 수집할 때 빠른 INSERT 속도 보장
• MergeTree 테이블로 저장할 때 일괄 처리(Batch Insert)로 성능 최적화

3. 실습용 데이터셋 생성(Kafka)

kafka 데이터 생성(in redpanda console)

📌 전체 조회 SQL

select event_time, user_id, event_type
from cluster('default', streaming_db.events_mv)
limit 10;

 

• 데이터 예시

{"event_time": "2025-03-20 10:00:00", "user_id": 101, "event_type": "page_view"}
{"event_time": "2025-03-20 10:01:00", "user_id": 102, "event_type": "click"}
{"event_time": "2025-03-20 10:02:00", "user_id": 103, "event_type": "purchase"}
{"event_time": "2025-03-20 10:03:00", "user_id": 104, "event_type": "page_view"}
{"event_time": "2025-03-20 10:04:00", "user_id": 105, "event_type": "click"}
{"event_time": "2025-03-20 10:05:00", "user_id": 106, "event_type": "purchase"}
{"event_time": "2025-03-20 10:06:00", "user_id": 107, "event_type": "page_view"}
{"event_time": "2025-03-20 10:07:00", "user_id": 108, "event_type": "click"}
{"event_time": "2025-03-20 10:08:00", "user_id": 109, "event_type": "purchase"}
{"event_time": "2025-03-20 10:09:00", "user_id": 110, "event_type": "page_view"}
{"event_time": "2025-03-20 10:10:00", "user_id": 111, "event_type": "click"}
{"event_time": "2025-03-20 10:11:00", "user_id": 112, "event_type": "purchase"}
{"event_time": "2025-03-20 10:12:00", "user_id": 113, "event_type": "page_view"}
{"event_time": "2025-03-20 10:13:00", "user_id": 114, "event_type": "click"}
{"event_time": "2025-03-20 10:14:00", "user_id": 115, "event_type": "purchase"}
{"event_time": "2025-03-20 10:15:00", "user_id": 116, "event_type": "page_view"}
{"event_time": "2025-03-20 10:16:00", "user_id": 117, "event_type": "click"}
{"event_time": "2025-03-20 10:17:00", "user_id": 118, "event_type": "purchase"}
{"event_time": "2025-03-20 10:18:00", "user_id": 119, "event_type": "page_view"}
{"event_time": "2025-03-20 10:19:00", "user_id": 120, "event_type": "click"}

• 조회 예시