ClickHouse DB

MergeTree 엔진이란?

• ClickHouse의 기본이자 핵심 스토리지 엔진
• 대용량 데이터를 컬럼 기반으로 저장하고 사용자가 지정한 정렬 키(ORDER BY)에 따라 자동 정렬하는 엔진
• 분석형 워크로드(OLAP)에 최적화된 설계
• 실시간 INSERT와 빠른 SELECT를 동시에 지원

MergeTree가 특별한 이유

• 가장 중요하고 가장 많이 사용되는 엔진
• 압축 알고리즘과 벡터화 처리가 모두 MergeTree에서 구현된다.
• 지속적인 백그라운드 최적화를 통해 쿼리 성능을 자동으로 향상시킨다

데이터 저장과 병합 원리

  INSERT 시점                    백그라운드 병합 후
┌──────────────┐                ┌─────────────────┐
│    Part 1    │                │                 │
│ (새 데이터)  │  =>  병합  =>  │    최적화된     │
├──────────────┤                │    단일 Part    │
│    Part 2    │                │                 │
│ (새 데이터)  │                └─────────────────┘
└──────────────┘

• INSERT마다 새로운 데이터 Part 생성 ⇒ 작은 파일들이 많아진다.
• 백그라운드에서 자동 병합 ⇒ 큰 파일로 합쳐져 성능 향상
• 압축과 정렬 최적화 ⇒ 쿼리 속도 대폭 개선

왜 "MergeTree"라는 이름인가?

• Merge - 작은 데이터 조각들을 큰 조각으로 병합
• Tree - 정렬된 데이터 자체가 거대한 색인(인덱스) 역할을 하여, B-Tree처럼 빠르게 원하는 데이터 범위를 찾아갈 수 있다
• MySQL의 B-Tree 인덱스와 유사하지만, 전체 데이터가 정렬된 상태로 저장된다

MySQL과의 차이점

ClickHouse ORDER BY의 핵심 역할

CREATE TABLE patient_visits (
    visit_date Date,
    patient_id String,
    hospital_id String,
    doctor_id String,
    diagnosis_code String
) ENGINE = MergeTree()
ORDER BY (patient_id, visit_date);

• ORDER BY (patient_id, visit_date)
  • 이것이 스토리지 구조를 결정한다.
• 물리적 데이터 정렬
  • 디스크에 실제로 이 순서로 저장된다
• 쿼리 성능 최적화
  • ORDER BY와 동일한 순서로 조회 시 극도로 빠르다.
  • 이미 정렬된 스토리지 구조에 따라 연속된 블록을 읽기 때문이다.
• 데이터 블록 건너뛰기
  • ORDER BY 컬럼 : 물리적 정렬로 자동으로 데이터 블록 건너뛰기
  • 기타 컬럼 : 스킵 인덱스 선언으로 블록 건너뛰기

ORDER BY 설계가 중요한 이유

• ✅ 매우 빠른 쿼리

  SELECT * FROM patient_visits
  WHERE patient_id = 'P001'
  AND visit_date >= '2024-01-01'
  ORDER BY patient_id, visit_date;
  

  • WHERE 절이 테이블 ORDER BY 순서와 일치하는 경우
    • 연속된 데이터 블록을 읽기 때문에 쿼리가 매우 빠르다.
    • WHERE 절의 첫 번째 조건이 테이블 ORDER BY의 첫 번째 컬럼과 일치
      • 1순위 컬럼만 또는 1, 2순위 컬럼 순서대로
  
  

• ❌ 느린 쿼리

  SELECT * FROM patient_visits
  WHERE doctor_id = 'D001'        -- ORDER BY에 doctor_id가 없음
  ORDER BY diagnosis_code;        -- ORDER BY와 완전히 다른 정렬
  

  • WHERE 절이 테이블 ORDER BY와 다른 패턴
    • ORDER BY에 없는 컬럼으로 필터링
    • ORDER BY 순서를 건너뛰는 필터링

최적 ORDER BY 설계

• 쿼리 패턴 분석과 ORDER BY 결정

  주요 쿼리 패턴	최적 ORDER BY	이유
  환자별 시계열 조회	(patient_id, visit_date)	환자 → 날짜 순 접근
  병원별 일일 분석	(hospital_id, visit_date)	병원 → 날짜 순 접근
  진단별 통계 분석	(icd10_primary, visit_date)	질병 → 날짜 순 접근
  의사별 진료 현황	(doctor_id, visit_date)	의사 → 날짜 순 접근

  
  

• 설계 우선순위

  1. 쿼리 빈도가 최우선 (가장 자주 사용되는 패턴)
  2. 카디널리티는 보조 고려사항 (동일한 중요도일 때)
  3. 최대 3-4개 컬럼까지만 사용 (너무 많으면 효과 감소)
  4. 실제 데이터 분포 확인해보기

• ORDER BY 설계 예시

  • ✅ 좋은 예 : 카디널리티 순서 고려

    ORDER BY (hospital_id, department_code, visit_date, patient_id)
    --         낮음(10개)      중간(50개)   높음(365일) 매우높음(10만명)
    

  • ❌ 나쁜 예 : 카디널리티 순서 잘못

    ORDER BY (patient_id, hospital_id, department_code, visit_date)
    --         매우높음     낮음           중간            높음
    

ClickHouse의 근본적 차이점

• ClickHouse 같은 분석 DB는 같은 이벤트가 여러 번 적재될 수 있음을 전제하기 때문에 Primary Key가 없고 중복 허용이 기본이다.
  • 다른 분석 DB 중 하나인 Snowflake는 유일성 제약이 있다.
• MySQL과 같은 RDBMS는 Primary Key로 유일성 강제하고 중복 시 오류가 나타나기 때문에 Primary Key 레벨에서 유일성이 보장된다

중복 데이터 문제와 해결책

• 문제 상황

  INSERT INTO patients_master VALUES ('P001', '김철수', '1985-03-15', ...);
  INSERT INTO patients_master VALUES ('P001', '김철수', '1985-03-15', ...);  -- 중복!
  

  • 같은 환자 정보가 여러 번 입력된다. (데이터 중복)
  
  

• 해결책 1 : ReplacingMergeTree + FINAL

  SELECT * FROM patients_master FINAL
  WHERE patient_id = 'P001';
  

  • ReplacingMergeTree로 중복 데이터를 백그라운드 작업으로 제거
  • 백그라운드 병합 작업 전이여도 FINAL 키워드로 최신 버전만 조회
  
  

• 해결책 2 : GROUP BY로 중복 제거

  SELECT
      patient_id,
      argMax(name, updated_at) as latest_name,
      argMax(phone, updated_at) as latest_phone
  FROM patients_master
  WHERE patient_id = 'P001'
  GROUP BY patient_id;
  

  • ReplacingMergeTree가 아닌 다른 엔진에서 중복을 제거하는 방법
  • GROUP BY + argMax( )로 수동으로 최신 버전 선택

    1. GROUP BY : 같은 patient_id끼리 그룹화

    2. argMax(값, 기준) : 기준이 최대인 행의 값을 선택

       P001 | 김철수 | 010-1111-1111 | 2024-01-01 10:00:00
       P001 | 김철수 | 010-2222-2222 | 2024-02-15 14:30:00  -- updated_at이 가장 큼
       P001 | 김철수 | 010-3333-3333 | 2024-01-10 09:15:00
       

       • argMax(name, updated_at) → "김철수"
       • argMax(phone, updated_at) → "010-2222-2222"

데이터 무결성 패턴

1. 애플리케이션 레벨 중복 방지

   • 삽입 전 존재 여부 확인

     SELECT count() FROM patients_master WHERE patient_id = 'P001';
     

     
     

   • 존재하지 않을 때만 INSERT

     INSERT INTO patients_master
     SELECT 'P001', '김철수', '1985-03-15', now()
     WHERE NOT EXISTS (
         SELECT 1 FROM patients_master WHERE patient_id = 'P001'
     );
     

     • 이 패턴은 데이터 삽입이 동시에 발생할 확률이 낮은 환경이나 배치 작업에 유용
     • 동시성이 높은 환경에서는 유일성을 보장하지 못할 수 있다.
       • 동시에 여러 곳에서 데이터가 삽입될 경우, 두 개의 삽입 작업이 모두 존재하지 않음으로 판단할 수 있다.
   
   
   

2. 고유 식별자 생성 패턴

   • UUID 활용한 유일성 보장

     INSERT INTO medical_records VALUES (
         generateUUIDv4(),
         'P001',
         now(),
         -- ... 기타 컬럼들
     );
     

     • generateUUIDv4() - 고유한 record_id 자동 생성
     
     

   • 타임스탬프 기반 복합 키

     INSERT INTO sensor_data VALUES (
         concat(device_id, '_', toString(toUnixTimestamp(now()))),
         device_id,
         now(),
         sensor_value
     );
     

     • 복합 유일키
       • concat(device_id, '_', toString(toUnixTimestamp(now())))

MergeTree 최적화 요소

• ORDER BY 설계

  • 가장 빈번한 쿼리 패턴에 맞춰 설계
  • 카디널리티 낮은 컬럼부터 배치
  • 3-4개 컬럼 이내로 제한
  
  

• 파티셔닝 전략

  • 날짜 기반 파티셔닝 (월별 권장)
  • 파티션당 적정 크기 유지 (1GB-10GB)
    • 너무 작으면 ⇒ 파티션 관리 오버헤드 증가
    • 너무 크면 ⇒ 쿼리 시 불필요한 데이터까지 스캔
  • 쿼리에서 파티션 키 활용하기
  
  

• 압축 최적화

  • 자주 조회되는 컬럼 : LZ4 (빠른 해제)
  • 보관용 데이터 : ZSTD (높은 압축률)
  • 타입별 최적 압축 코덱 선택

메모리 사용량 최적화 예시

-- 대용량 테이블 설정 예시
CREATE TABLE large_medical_data (
    -- ... 컬럼 정의
) ENGINE = MergeTree()
ORDER BY (patient_id, visit_date)
SETTINGS
    index_granularity = 8192,           -- 기본값, 메모리 사용량 조절
    merge_max_block_size = 8192,        -- 병합 시 블록 크기
    max_compress_block_size = 1048576,  -- 압축 블록 크기
    min_compress_block_size = 65536,    -- 최소 압축 블록 크기
    max_part_loading_threads = 4;       -- 파트 로딩 스레드 수

• 테이블 생성 시 SETTINGS 구문을 통해 세부 동작을 직접 제어하여 메모리 사용량을 튜닝할 수 있다
  • index_granularity (인덱스 간격)
  • merge_max_block_size (병합 시 메모리 사용량) 등

MergeTree 엔진 선택 가이드

• • 테이블 생성 시 SETTINGS 구문을 통해 index_granularity(인덱스 간격), merge_max_block_size(병합 시 메모리 사용량) 등 세부 동작을 직접 제어하여 메모리 사용량을 튜닝할 수 있다

실무에서 자주 하는 실수들

1. ORDER BY를 MySQL Primary Key처럼 사용

   • 문제 : ClickHouse는 유일성 보장 X
   • 해결 : 중복 데이터 처리 전략 필요
   
   

2. 너무 많은 컬럼을 ORDER BY에 포함

   • 문제 : 너무 많은 컬럼을 ORDER BY에 포함
   • 해결 : 3-4개 컬럼 이내 권장
   
   

3. 파티션을 너무 세밀하게 분할

   • 문제 : 일별 파티션은 보통 과도하다
   • 해결 : 월별 파티션이 일반적으로 최적
   
   

4. ReplacingMergeTree에서 FINAL 누락

   -- ✅ 올바른 사용
   SELECT * FROM patients_master FINAL WHERE patient_id = 'P001';
   
   -- ❌ 잘못된 사용
   SELECT * FROM patients_master WHERE patient_id = 'P001';  -- 중복 가능
   

   • 문제
     • 백그라운드 병합 전에 FINAL 없이는 중복 데이터가 조회된다.
   • 해결
     • 항상 FINAL 사용 (안전하지만 성능 저하)
     • 또는 병합 상태 확인 후 사용 (복잡하지만 최적화)

MergeTree 설계 핵심 원칙

1. 쿼리 패턴 먼저 분석 ⇒ ORDER BY 설계
2. 데이터 특성 파악 ⇒ 엔진 타입 선택
3. 생명주기 고려 ⇒ TTL 설정
4. 성능 모니터링 ⇒ 지속적 최적화

TTL (Time To Live) 기본 개념

• 자동 데이터 삭제 기능으로 법적 보관 기간 준수
• 의료법 준수
  • 의료기록 보관 의무 기간 후 자동 삭제
• 개인정보보호법 준수
  • 목적 달성 후 자동 파기
• 비용 절약
  • 오래된 데이터는 저렴한 스토리지로 이동

의료 데이터 TTL 설정 예시

CREATE TABLE patient_records (
    patient_id String,
    visit_date Date,
    personal_info String,
    medical_data Text,
    created_at DateTime DEFAULT now()
) ENGINE = MergeTree()
ORDER BY (patient_id, visit_date)
TTL
    created_at + INTERVAL 10 YEAR DELETE,           -- 10년 후 전체 행 삭제
    created_at + INTERVAL 3 YEAR TO DISK 'cold',    -- 3년 후 저비용 스토리지로 이동
    created_at + INTERVAL 1 YEAR GROUP BY patient_id -- 1년 후 환자별 집계 데이터로 변환
    SET
        personal_info = '',  -- 개인정보 삭제
        medical_data = 'ARCHIVED'  -- 의료정보 아카이브 처리

• 단계별 데이터 생명주기
  • 0-1년
    • 전체 데이터 유지 (핫 스토리지)
  • 1-3년

    • 개인정보 삭제, 의료정보만 유지

      created_at + INTERVAL 1 YEAR GROUP BY patient_id
      SET personal_info = '', medical_data = 'ARCHIVED'
      

      • 1년 후 환자별로 그룹화하면서 개인정보 삭제
        • 개인정보 ⇒ 빈 문자열
        • 의료정보 ⇒ "ARCHIVED"로 변경
  • 3-10년
    • 콜드 스토리지로 이동 (비용 절약)
    • TO DISK - 스토리지 이동
    • created_at + INTERVAL 3 YEAR TO DISK 'cold’
      • 3년 후 저비용 스토리지로 이동
  • 10년 후
    • DELETE - 완전 삭제
    • created_at + INTERVAL 10 YEAR DELETE
      • 10년 후 전체 행 삭제

실무적 TTL 활용 패턴

• 로그 데이터 자동 삭제

  CREATE TABLE audit_logs (
      log_date Date,
      user_id String,
      action String,
      details Text
  ) ENGINE = MergeTree()
  ORDER BY log_date
  TTL log_date + INTERVAL 7 YEAR DELETE;  -- 감사 로그 7년 보관
  

  • 7년 후엔 법적으로 불필요 ⇒ 완전 삭제
  
  

• 임시 데이터 빠른 삭제

  CREATE TABLE temp_analysis (
      analysis_date Date,
      temp_data Text
  ) ENGINE = MergeTree()
  ORDER BY analysis_date
  TTL analysis_date + INTERVAL 30 DAY DELETE;  -- 30일 후 자동 삭제
  

  • 30일 후엔 분석 가치 없음 ⇒ 빠른 정리
  
  

• 개인정보 마스킹

  CREATE TABLE patient_analytics (
      record_date Date,
      patient_id String,
      name String,
      diagnosis_code String
  ) ENGINE = MergeTree()
  ORDER BY record_date
  TTL record_date + INTERVAL 5 YEAR
  GROUP BY patient_id
  SET name = 'MASKED';  -- 5년 후 이름 마스킹
  

  • 5년 전 : 김철수 | 당뇨 | 서울시 강남구
  • 5년 후 : MASKED | 당뇨 | MASKED

스킵 인덱스가 필요한 상황

• ORDER BY에 없는 컬럼으로 검색 ⇒ 전체 스캔 발생

  SELECT * FROM medical_records
  WHERE doctor_id = 'D001';  -- 
  

  • doctor_id가 ORDER BY에 없어서 모든 블록을 다 확인해야 한다
  
  

• 문자열 부분 검색 ⇒ 전체 스캔 발생

  SELECT * FROM medical_records
  WHERE patient_name LIKE '%김%';
  

  • "김"이 들어간 이름을 찾으려면 모든 환자 이름을 다 봐야 한다
  
  

• 범위가 넓은 숫자 검색 ⇒ 많은 블록 읽기

  SELECT * FROM medical_records
  WHERE total_cost BETWEEN 50000 AND 200000;
  

  • 5만원~20만원 사이 진료비를 찾으려면 모든 비용 데이터를 확인해야 한다.

스킵 인덱스 설정

CREATE TABLE medical_records_optimized (
    patient_id String,
    visit_date Date,
    doctor_id String,
    diagnosis_name String,
    patient_name String,
    total_cost Decimal(10,2),

    -- 스킵 인덱스들
    INDEX idx_doctor doctor_id TYPE set(100) GRANULARITY 3,
    INDEX idx_name patient_name TYPE ngrambf_v1(4, 256, 2, 0) GRANULARITY 1,
    INDEX idx_cost total_cost TYPE minmax GRANULARITY 2,
    INDEX idx_diagnosis diagnosis_name TYPE bloom_filter() GRANULARITY 1
) ENGINE = MergeTree()
ORDER BY (patient_id, visit_date);

• 각 인덱스 타입별 활용
  • set(100)
    • 의사 ID처럼 카디널리티가 낮은 값들에 사용
    • 종류가 적은 데이터용
  • ngrambf_v1
    • 환자 이름 부분 검색(LIKE '%김%')에 사용
    • 이름을 작은 조각으로 나눠서 저장 ("김철수" → "김철", "철수")
  • minmax
    • 비용처럼 숫자 범위 검색
    • 각 블록의 최솟값/최댓값 저장한다.
  • bloom_filter
    • 진단명처럼 정확한 매칭 검색에 사용
    • "위궤양"이라는 진단명이 있는지 빠르게 확인할 수 있다.
    • 있으면 블록 확인, 없으면 건너뛰기

스킵 인덱스 효과 측정

• 인덱스 적용 전후 성능 비교

  SELECT
      query,                   -- 어떤 쿼리인지
      read_rows,               -- 몇 개 행을 읽었는지
      read_bytes,              -- 몇 바이트를 읽었는지  
      query_duration_ms        -- 얼마나 오래 걸렸는지
  FROM system.query_log
  WHERE query LIKE '%medical_records%'
  AND event_time >= now() - INTERVAL 1 HOUR
  ORDER BY query_duration_ms DESC;
  

  
  

• 특정 쿼리의 실행 계획 확인

  EXPLAIN indexes = 1
  SELECT * FROM medical_records_optimized
  WHERE doctor_id = 'D001';
  

  • 스킵 인덱스를 사용했는지, 몇 개 블록을 건너뛰었는지 알 수 있다.
  
  

• 성능 개선 지표

  • read_rows
    • 읽은 행 수 (적을수록 좋음)
  • read_bytes
    • 읽은 바이트 수 (적을수록 좋음)
  • query_duration_ms
    • 쿼리 실행 시간 (짧을수록 좋음)

ARRAY JOIN의 핵심 원리

• MySQL에서는 JSON 배열을 처리하려면 복잡한 함수들을 써야 한다.

• ClickHouse는 배열을 테이블의 행으로 펼치는 ARRAY JOIN을 제공한다.

  -- 의료 기록에서 증상 배열을 개별 행으로 펼치기
  SELECT
      patient_id,
      visit_date,
      diagnosis_name,
      symptom_item
  FROM medical_records
  ARRAY JOIN symptoms AS symptom_item
  WHERE patient_id = 'P001';
  

  • ARRAY JOIN은 1개 행에 있던 배열의 요소들을 별도의 행로 펼쳐주는 기능이다.

  • 결과

    patient_id	visit_date	diagnosis_name	symptom_item
    P001	2024-02-01 09:00:00	위궤양	복통
    P001	2024-02-01 09:00:00	위궤양	소화불량

  
  

• ARRAY JOIN 없이 조회 시

  SELECT
      patient_id,
      visit_date,
      diagnosis_name,
      symptoms
  FROM medical_records
  WHERE patient_id = 'P001';
  

  • 결과

    patient_id	visit_date	diagnosis_name	symptoms
    P001	2024-02-01 09:00:00	위궤양	[’복통’, ’소화불량’]

하나의 테이블에 여러 배열이 있는 경우

CREATE TABLE medical_records_advanced (
    record_id String,
    patient_id String,
    visit_date DateTime,
    symptoms Array(String),           -- 증상 배열
    icd10_codes Array(String),        -- 진단코드 배열
    prescription_drugs Array(String), -- 처방약 배열
    lab_tests Array(String)           -- 검사항목 배열
) ENGINE = MergeTree()
ORDER BY (patient_id, visit_date);

• 여러 배열을 동시에 처리하는 패턴

  -- 증상별 처방약 매핑 분석
  SELECT
      symptom,
      groupArray(DISTINCT drug) AS related_drugs,
      count() AS frequency
  FROM medical_records_advanced
  ARRAY JOIN
      symptoms AS symptom,
      prescription_drugs AS drug
  GROUP BY symptom
  ORDER BY frequency DESC;
  

  • 원본 결과

    patient_id	symptoms	prescription_drugs
    P001	['복통', '소화불량']	['제산제', '소화제']
    P002	['복통', '두통']	['진통제', '위장약']
    P003	['소화불량']	['소화제']

    
    

  • ARRAY JOIN 결과

    patient_id	symptom	drug
    P001	복통	제산제
    P001	소화불량	소화제
    P002	복통	진통제
    P002	두통	위장약
    P003	소화불량	소화제

    • 각 배열의 같은 인덱스에 있는 요소끼리 순서대로 묶여 새로운 행을 만든다.
    • 즉, 각 배열의 첫 번째끼리 매칭, 이후 두 번째끼리 매칭된다.
    
    

  • groupArray( ) 결과

    symptom	related_drugs	frequency
    복통	['제산제', '진통제']	2
    소화불량	['소화제']	2
    두통	['위장약']	1

    • groupArray(지정컬럼)
      • ARRAY JOIN으로 펼친 행들을 다시 지정컬럼 기준 그룹별로 묶어 배열화하는 것A
      • DISTINCT drug : 모을 때 중복 제거
      • groupArray(drug) : 같은 그룹 안의 모든 약 다시 배열로 모은다.
        • 복통에는 ‘제산제’, ‘진통제’가 처방되었다.
  
  

• 주의사항

  symptoms = ['복통', '소화불량', '속쓰림']  (3개)
  
  prescription_drugs = ['제산제', '소화제']   (2개)
  
  -- 결과: ('복통', '제산제'), ('소화불량', '소화제') 만 생성됨
  

  • 여러 배열을 ARRAY JOIN하면 각 배열의 첫 번째 요소끼리 묶이고, 다음으로 두 번째 요소끼리 묶여 순서대로 쌍으로 묶인다
  • 만약 배열 길이가 다르면, 가장 짧은 배열 길이를 기준으로 처리되므로 주의해야 한다.

LEFT ARRAY JOIN으로 빈 배열 처리

-- 처방약이 없는 환자도 포함하여 조회
SELECT
    patient_id,
    diagnosis_name,
    coalesce(drug, 'No Prescription') AS prescription_status
FROM medical_records_advanced
LEFT ARRAY JOIN prescription_drugs AS drug
WHERE visit_date >= '2024-02-01';

• LEFT ARRAY JOIN으로 빈 배열 포함시키고, coalescs() 함수로 null 값 처리를 한다.

기존 JOIN 방식의 한계

-- ICD-10 코드 마스터 테이블 생성
CREATE TABLE icd10_master (
    code String,
    korean_name String,
    english_name String,
    category String
) ENGINE = MergeTree()
ORDER BY code;

-- 샘플 데이터
INSERT INTO icd10_master VALUES
('K25.9', '위궤양, 상세불명', 'Gastric ulcer, unspecified', '소화기계'),
('K30', '기능성 소화불량', 'Functional dyspepsia', '소화기계'),
('J44.1', '급성 악화를 동반한 만성폐쇄성폐질환', 'COPD with acute exacerbation', '호흡기계'),
('J06.9', '급성 상기도감염, 상세불명', 'Acute upper respiratory infection', '호흡기계'),
('E11.9', '인슐린-비의존 당뇨병', 'Type 2 diabetes mellitus', '내분비계');

• 진료 기록 테이블 (매일 수천~수만 건 발생)

  record_id	patient_id	icd10_code	visit_date	cost
  R001	P001	K25.9	2024-01-01	50000
  R002	P002	K30	2024-01-01	30000
  R003	P003	J44.1	2024-01-01	80000
  R004	P004	K25.9	2024-01-01	45000
  …	…	...	…	…

  
  

• ICD-10 코드 마스터 테이블 (거의 변경되지 않음)

  code	korean_name	english_name	category
  K25.9	위궤양, 상세불명	Gastric ulcer	소화기계
  K30	기능성 소화불량	Functional dyspepsia	소화기계
  J44.1	만성폐쇄성폐질환	COPD	호흡기계
  E11.9	인슐린-비의존 당뇨병	Type 2 diabetes	내분비계

-- 진료비 통계
SELECT 
    mr.patient_id,
    icd.korean_name AS diagnosis_korean,  -- 한국어 병명이 필요
    mr.cost,
    mr.visit_date
FROM medical_records mr
JOIN icd10_master icd ON mr.icd10_code = icd.code
WHERE mr.visit_date >= '2024-01-01';

• 진료비 통계를 조회할 때마다, 이런 쿼리를 날린다.
• JOIN 연산은 실행할 때마다 매번 디스크 I/O와 테이블 스캔이 발생하여, 수백만 건의 데이터에 대한 연산 비용이 발생
• 성능 비교
  • JOIN 방식
    • 디스크 읽기 → 테이블 스캔 → 매칭 → 결과 (수십ms)
  • 딕셔너리 방식
    • 메모리에서 키로 바로 조회 (수μs)

딕셔너리(Dictionary)란

• 딕셔너리는 키-값 쌍으로 데이터를 저장하는 자료구조

• ClickHouse 딕셔너리는 자주 참조하는 데이터를 메모리에 미리 로드해두고, 키로 값을 빠르게 조회할 수 있게 해주는 시스템

  
  

• 딕셔너리가 필요한 이유

  • 딕셔너리의 핵심은 자주 참조하는 작은 테이블을 메모리에 올려두고, 키로 바로 찾는 것이다.
  • 일반적인 JOIN은 매번 디스크를 읽어야 하지만, 딕셔너리는 메모리에 캐시되어 극도로 빠르다
  
  

• 딕셔너리 동작 방식

  1. 딕셔너리 로드 (서버 시작 시 1회)

     • ClickHouse 메모리에 로드

       딕셔너리["K25.9"] = {
           korean_name: "위궤양, 상세불명",
           english_name: "Gastric ulcer",
           category: "소화기계"
       }
       딕셔너리["K30"] = {
           korean_name: "기능성 소화불량", 
           english_name: "Functional dyspepsia",
           category: "소화기계"
       }
       

  2. 빠른 조회 (매 쿼리마다)

     • JOIN 없이 메모리에서 키로 바로 조회

외부 딕셔너리 설정 파일

<?xml version="1.0"?>
<dictionaries>
    <dictionary>
        <!-- 1. 딕셔너리 식별 정보 -->
        <name>icd10_dict</name>  <!-- 딕셔너리 이름 (쿼리에서 사용) -->
        
        <!-- 2. 데이터 소스 설정 -->
        <source>
            <clickhouse>
                <host>localhost</host>         <!-- ClickHouse 서버 주소 -->
                <port>9000</port>              <!-- 포트 번호 -->
                <user>admin</user>             <!-- 데이터베이스 유저명 -->
                <password>1234</password>      <!-- 비밀번호 -->
                <db>medical</db>               <!-- 데이터베이스명 -->
                <table>icd10_master</table>    <!-- 소스 테이블명 -->
            </clickhouse>
        </source>
        
        <!-- 3. 메모리 저장 방식 -->
        <layout>
            <hashed />  <!-- 해시 테이블 방식 (O(1) 조회 속도) -->
        </layout>
        
        <!-- 4. 딕셔너리 구조 정의 -->
        <structure>
            <!-- 키(Key) 정의 -->
            <id>
                <name>code</name>  <!-- icd10_master.code 컬럼을 키로 사용 -->
            </id>
            
            <!-- 값(Value) 정의 - 여러 개 가능 -->
            <attribute>
                <name>korean_name</name>    <!-- 속성명 -->
                <type>String</type>         <!-- 데이터 타입 -->
            </attribute>
            <attribute>
                <name>english_name</name>
                <type>String</type>
            </attribute>
            <attribute>
                <name>category</name>
                <type>String</type>
            </attribute>
        </structure>
        
        <!-- 5. 갱신 주기 -->
        <lifetime>300</lifetime>  <!-- 300초(5분)마다 소스 테이블에서 다시 로드 -->
    </dictionary>
</dictionaries>

• config/icd10_dictionary.xml 파일 생성

외부 딕셔너리 설정 요소

• <name> - 딕셔너리 식별자

  • 쿼리에서 dictGet()이 참조할 이름
  • 여러 딕셔너리가 있을 때 구분하는 식별자
  
  

• <source> - 데이터 소스

  • ClickHouse 테이블뿐만 아니라 MySQL, PostgreSQL, HTTP, CSV 등도 가능

  • 외부 데이터베이스에서 데이터를 가져올 수도 있다

  • MySQL에서 데이터 가져오기

    <source>
        <mysql>
            <host>mysql-server.hospital.com</host>  <!-- MySQL 서버 주소 -->
            <port>3306</port>                       <!-- MySQL 포트 -->
            <user>clickhouse_user</user>            <!-- MySQL 유저명 -->
            <password>secure_password</password>    <!-- MySQL 비밀번호 -->
            <db>hospital_db</db>                    <!-- MySQL 데이터베이스명 -->
            <table>icd10_codes</table>              <!-- MySQL 테이블명 -->
            
            <!-- 선택사항: 커스텀 쿼리 -->
            <query>
                SELECT code, korean_name, english_name, category 
                FROM icd10_codes 
                WHERE is_active = 1
            </query>
        </mysql>
    </source>
    

  • HTTP API에서 가져오기

    <source>
        <http>
            <url>https://api.who.int/icd10/codes</url>
            <format>JSON</format>
            <headers>
                <header>
                    <name>Authorization</name>
                    <value>Bearer your-api-token</value>
                </header>
            </headers>
        </http>
    </source>
    

  
  

• <layout> - 메모리 저장 방식

  • <flat /> : 배열 방식 (키가 연속 숫자일 때)
  • <cache /> : LRU 캐시 방식 (메모리 절약)
  • <range_hashed /> : 범위 검색용
  • <hashed /> : 해시 테이블: 빠른 조회, 많은 메모리
  
  

• <structure> - 딕셔너리 구조

  • 키(id), 값(attribute) 설정
  • attribute는 name(속성명)과 type(데이터 타입) 설정 필요
  
  

• <lifetime> - 갱신 주기

  • 소스 테이블이 변경되면 딕셔너리도 자동 갱신
  • 0으로 설정하면 서버 재시작할 때만 갱신

dictGet( ) 함수

• dicGet( ) 사용 방법

  • dictGet('딕셔너리명', '속성명', 키값)

    SELECT 
        patient_id,
        icd10_code,
        dictGet('icd10_dict', 'korean_name', icd10_code) AS korean_name
    FROM medical_records;
    

  • 동작 과정

    1. medical_records에서 icd10_code 값("K25.9")을 읽는다
    2. 메모리의 icd10_dict에서 키 "K25.9" 검색
    3. 해당 키의 korean_name 속성 반환 ("위궤양, 상세불명")
  
  

• 여러 속성 조회

  SELECT 
      patient_id,
      dictGet('icd10_dict', 'korean_name', icd10_code) AS korean_name,
      dictGet('icd10_dict', 'category', icd10_code) AS category,
      dictGet('icd10_dict', 'english_name', icd10_code) AS english_name
  FROM medical_records;
  

  
  

• 배열 내 모든 값 조회

  SELECT 
      patient_id,
      icd10_codes,  -- ['K25.9', 'K30', 'J44.1']
      arrayMap(x -> dictGet('icd10_dict', 'korean_name', x), icd10_codes) AS korean_names
  FROM medical_records_advanced;
  

  1. ['K25.9', 'K30', 'J44.1'] 배열의 각 요소에 대해
  2. dictGet('icd10_dict', 'korean_name', x) 실행
  3. 결과를 새 배열로 조합 - ['위궤양, 상세불명', '기능성 소화불량', '만성폐쇄성폐질환']
  
  

• dictGet( ) 함수 장점

  • 속도
    • 메모리에서 O(1) 조회
  • 간편성
    • JOIN 문법 없이 간단 조회
  • 배열 지원
    • arrayMap과 조합 가능
  • NULL 처리
    • 키가 없으면 기본값 반환

WITH TOTALS로 집계와 소계 한 번에 처리

• WITH TOTALS란

  • 소계는 마지막에 전체 합계도 같이 보고 싶을 때 사용한다

  • MySQL에서는 소계를 구하려면 ROLLUP이나 별도 쿼리가 필요하다.

  • 그러나, ClickHouse는 WITH TOTALS로 한 번에 처리한다

  • 기본 문법

    SELECT 컬럼들...
    FROM 테이블
    GROUP BY 컬럼
    WITH TOTALS
    [ORDER BY ...]
    

    • GROUP BY와 함께 사용 - GROUP BY 없으면 의미 없다
    • 집계 함수와 함께 사용 - COUNT(), SUM(), AVG() 등

  • 동작 원리

    1. 일반적인 GROUP BY 실행
    2. 모든 그룹의 집계 함수 결과를 다시 집계
    3. "Totals" 행에 전체 합계 추가
  
  

• 사용 예시

  -- 부서별 환자 수와 전체 소계
  SELECT
      department,
      count() AS patient_count,
      sum(cost) AS total_revenue
  FROM medical_records
  GROUP BY department
  WITH TOTALS
  ORDER BY total_revenue DESC;
  

  • 결과

    department	patient_count	total_revenue
    응급의학과	1	180000.00
    정형외과	1	120000.00
    산부인과	1	85000.00
    내과	2	70000.00

    Totals :

    department	patient_count	total_revenue
    -	5	455000.00

    • 한 번의 쿼리로 그룹별 통계 + 전체 합계를 동시에 얻는다

LIMIT BY로 그룹별 상위 N개 조회

• LIMIT BY란

  • 그룹별 상위 N개가 필요한 경우 사용한다

  • MySQL에서는 그룹별 상위 N개를 구하려면 복잡한 윈도우 함수가 필요하다.

  • 그러나, ClickHouse는 LIMIT BY로 한 번에 처리한다

  • 기본 문법

    SELECT 컬럼들...
    FROM 테이블
    [WHERE 조건]
    ORDER BY 정렬기준
    LIMIT N BY 그룹컬럼
    

    • ORDER BY 필수 - 정렬 없으면 어떤 N개를 가져올지 모른다
    • LIMIT N BY 그룹 기준
      • 그룹 기준은 어떤 컬럼으로 그룹을 나눌지
      • N은 각 그룹에서 몇 개씩 가져올지

  • 동작 원리

    1. ORDER BY로 정렬
    2. 그룹 컬럼별로 데이터 묶기
    3. 각 그룹에서 상위 N개씩만 선택
  
  

• 사용 예시

  -- 각 환자별 최근 진료 기록 2개씩만 조회
  SELECT
      patient_id,
      visit_date,
      diagnosis_name,
      cost
  FROM medical_records
  ORDER BY patient_id, visit_date DESC
  LIMIT 2 BY patient_id;
  

  • 결과

    patient_id	visit_date	diagnosis	cost
    P001	2024-02-01	위염	40000
    P001	2024-01-15	두통	25000
    P002	2024-02-10	완치	30000
    P002	2024-01-20	재진	50000

    • 각 그룹(patient_id)별로 N개씩만 가져온다

FINAL 키워드와 중복 제거 완벽 이해

• ReplacingMergeTree에서 FINAL의 필요성

  • 앞서 학습한 ReplacingMergeTree는 백그라운드에서 중복을 제거하므로, 실시간으로는 중복된 데이터가 보일 수 있다

  • FINAL은 지금 당장 중복 제거해서 최신 데이터만 보는 명령어이다

  • 데이터의 최종적인 정확성이 필요할 때 사용하는 핵심 키워드

  • 기본 문법

    SELECT 컬럼들...
    FROM 테이블명 FINAL
    [WHERE 조건]
    

    • 테이블명 바로 뒤 - FROM table_name FINAL
    • ReplacingMergeTree에서만 의미 있음 - 다른 엔진에서는 효과 없음
    • 성능 비용 - 실시간 중복 제거라서 느릴 수 있음

  • 동작 원리

    1. 테이블에서 데이터 읽기
    2. 중복된 키 찾기
    3. 최신 버전만 남기고 나머지 제거
    4. 결과 반환
  
  

• 중복 입력 테스트

  -- 환자 마스터 데이터 중복 입력 시뮬레이션
  INSERT INTO patients_master VALUES
  ('P001', '김철수', '1985-03-15', 'M', '010-1111-1111', now());
  INSERT INTO patients_master VALUES
  ('P001', '김철수(수정)', '1985-03-15', 'M', '010-2222-2222', now());
  

  • FINAL 없이 조회 (중복 표시됨)

    SELECT * FROM patients_master WHERE patient_id = 'P001';

    patient_id	name	phone	updated_at
    P001	김철수	010-1111-1111	2024-01-01 10:00:00
    P001	김철수(수정)	010-2222-2222	2024-01-01 11:00:00

    • 아직 병합 전이라면, 수정 전 데이터까지 조회된다.
    
    

  • FINAL로 조회 (최신 버전만 표시)

    SELECT * FROM patients_master FINAL WHERE patient_id = 'P001';

    patient_id	name	phone	updated_at
    P001	김철수(수정)	010-2222-2222	2024-01-01 11:00:00

  
  
  

• 성능을 고려한 중복 제거 패턴

  -- 방법 1: FINAL
  SELECT * FROM patients_master FINAL WHERE patient_id = 'P001';
  
  -- 방법 2: GROUP BY
  SELECT
      patient_id,
      argMax(name, updated_at) AS latest_name,
      argMax(phone, updated_at) AS latest_phone,
      argMax(address, updated_at) AS latest_address,
      max(updated_at) AS last_updated
  FROM patients_master
  WHERE patient_id = 'P001'
  GROUP BY patient_id;
  

  • FINAL은 편리하지만 테이블 전체를 스캔해서 중복을 제거하기에 메모리 사용량이 많아 성능 비용이 발생한다.
  • 그렇기 때문에 대용량 데이터나 성능이 중요한 경우 GROUP BY + argMax()를 사용하는 것이 더 좋을 수 있다

특화 구문 조합 활용

• WITH TOTALS + LIMIT BY 조합

  SELECT department, patient_id, cost, visit_date
  FROM medical_records
  ORDER BY department, cost DESC
  LIMIT 2 BY department
  WITH TOTALS;
  

  • 부서별 TOP 환자 + 전체 통계 조회
  • 상위 선택 + 통계를 한 번에
    • ORDER BY department, cost DESC로 부서별로 묶고, 각 부서 내에서 비용 높은 순으로 정렬
    • LIMIT 2 BY department로 각 부서별로 상위 2명씩만 선택
    • WITH TOTALS로 선택된 데이터들의 전체 합계도 같이 표시
  
  
  

• FINAL + LIMIT BY 조합

  SELECT patient_id, name, phone, updated_at
  FROM patients_master FINAL
  ORDER BY patient_id, updated_at DESC
  LIMIT 1 BY patient_id;
  

  • 각 환자별 최신 정보만 조회
  • 중복 제거 + 그룹별 선택을 한 번에
    • FINAL로 중복 제거해서 최신 데이터만 가져옴
    • ORDER BY patient_id, updated_at DESC로 환자별로 묶고, 최신 순으로 정렬
    • LIMIT 1 BY patient_id로 각 환자별로 최신 1개씩만 선택

핵심 배열 처리 함수

• arraySort( )

  arraySort(배열)  -- 배열 요소를 정렬
  

  
  

• arraySlice( )

  arraySlice(배열, 시작위치, 개수)  -- 배열 일부만 처리
  

  • 배열이 너무 클 때 일부만 처리해서 메모리 절약
  
  

• has( ) vs hasAny( )

  has(배열, 값)           -- 특정 값이 있는지 확인
  hasAny(배열1, 배열2)    -- 배열1에 배열2의 요소 중 하나라도 있는지
  

  • true / false 반환
  
  

• arrayFilter( ) vs arrayMap( )

  arrayFilter(x -> 조건, 배열)   -- 조건에 맞는 요소만 필터링
  arrayMap(x -> 변환식, 배열)    -- 각 요소를 변환
  

  • ['복통', '감기', '호흡곤란']에서 심각한 증상만 → ['호흡곤란']
  • ['타이레놀', '부루펜'] → ['타이레놀 200mg', '부루펜 200mg']

배열 처리 함수 예시

• arraySort( ) 예시

  SELECT
      arraySort(symptoms) AS symptom_combination,
      count() AS frequency,
      groupArray(DISTINCT patient_id) AS affected_patients
  FROM medical_records_advanced
  WHERE length(symptoms) >= 2  -- 2개 이상 증상이 있는 경우만
  GROUP BY symptom_combination
  HAVING frequency >= 2
  ORDER BY frequency DESC;
  

  • 증상 동시 발생 패턴 분석으로 어떤 증상들이 자주 같이 나타나는지 분석하는 쿼리

  • 원본 데이터

    patient_id	symptoms
    P001	['복통', '소화불량']
    P002	['복통', '두통']
    P003	['소화불량', '복통']
    P004	['감기']
    P005	['복통', '두통']

    
    

  • 동작 과정

    1. WHERE 필터링 - 2개 이상 증상이 있는 경우만 필터링

       patient_id	symptoms
       P001	['복통', '소화불량']
       P002	['복통', '두통']
       P003	['소화불량', '복통']
       P005	['복통', '두통']

       • P004 제외
    2. arraySort()로 증상 배열 정렬

       patient_id	symptoms
       P001	['복통', '소화불량']
       P002	['두통', '복통']
       P003	['복통', '소화불량']
       P005	['두통', '복통']

    3. GROUP BY - 증상 배열 기준으로 그룹화
       • 그룹 1
         • ['복통', '소화불량'] → P001, P003
       • 그룹 2
         • ['두통', '복통'] → P002, P004
    4. 집계 함수 - count()
    5. 집계 함수 - groupArray()
       • 각 그룹 안에 있는 환자 ID들을 배열로 모으기 위해 사용
         • ['복통', '소화불량'] 그룹
           • P001, P003 ⇒ ['P001', 'P003']
         • ['두통', '복통'] 그룹
           • P002, P004 ⇒ ['P002', 'P004']
    6. HAVING

       symptom_combination	frequency	affected_patients
       ['복통', '소화불량']	2	['P001', 'P003']
       ['두통', '복통']	2	['P002', 'P005']

  
  
  

• has( ), hasAny( ) 예시

  SELECT
      patient_id,
      visit_date,
      prescription_drugs,
      CASE
          WHEN has(prescription_drugs, '와파린') AND hasAny(prescription_drugs, ['아스피린', '이부프로펜'])
          THEN '출혈 위험 - 상호작용 주의'
          ELSE '안전'
      END AS interaction_warning
  FROM medical_records_advanced
  WHERE length(prescription_drugs) >= 2;
  

  • 처방약 상호작용 위험 검사로 와파린과 함께 먹으면 위험한 약이 처방되었는지 확인하는 쿼리

  • 원본 데이터

    patient_id	visit_date	prescription_drugs
    P001	2024-01-01	['타이레놀', '소화제']
    P002	2024-01-02	['와파린', '아스피린', '비타민']
    P003	2024-01-03	['와파린', '소화제']
    P004	2024-01-04	['이부프로펜']
    P005	2024-01-05	['와파린', '이부프로펜', '감기약']
    P006	2024-01-06	['타이레놀', '비타민']

    
    

  • 동작 과정

    1. WHERE 필터링 - 처방약이 2개 이상인 환자만 필터링

       patient_id	visit_date	prescription_drugs
       P001	2024-01-01	['타이레놀', '소화제']
       P002	2024-01-02	['와파린', '아스피린', '비타민']
       P003	2024-01-03	['와파린', '소화제']
       P005	2024-01-05	['와파린', '이부프로펜', '감기약']
       P006	2024-01-06	['타이레놀', '비타민']

       • P004 제외
    2. CASE WHEN - has(), hasAny()로 배열 검사
       • 와파린이 있는데 이부프로펜 or 아스피린까지 함께 있는지 체크

       patient_id	prescription_drugs	경고 메시지
       P001	['타이레놀', '소화제']	안전
       P002	['와파린', '아스피린', '비타민']	출혈 위험 - 상호작용 주의
       P003	['와파린', '소화제']	안전
       P005	['와파린', '이부프로펜', '감기약']	출혈 위험 - 상호작용 주의
       P006	['타이레놀', '비타민']	안전

  
  
  

• arrayFilter( ) 예시

  SELECT
      patient_id,
      symptoms,
      arrayFilter(x -> x IN ('호흡곤란', '가슴통증', '의식저하', '고열'), symptoms) AS serious_symptoms
  FROM medical_records_advanced
  WHERE length(arrayFilter(x -> x IN ('호흡곤란', '가슴통증', '의식저하', '고열'), symptoms)) > 0;
  

  • 심각한 증상만 필터링으로 응급상황이 필요한 환자를 식별 하는 쿼리

  • 원본 데이터

    patient_id	symptoms
    P001	['복통', '소화불량', '어지러움']
    P002	['호흡곤란', '가슴통증', '두통']
    P003	['감기', '기침', '콧물']
    P004	['의식저하', '고열', '복통']
    P005	['두통', '목아픔']
    P006	['호흡곤란', '어지러움']

    
    

  • 동작 과정

    1. arrayFilter()로 각 환자별로 심각한 증상만 추출
       • '호흡곤란', '가슴통증', '의식저하', '고열'

       patient_id	symptoms	arrayFilter 결과
       P001	['복통', '소화불량', '어지러움']	[ ]
       P002	['호흡곤란', '가슴통증', '두통']	['호흡곤란', '가슴통증']
       P003	['감기', '기침', '콧물']	[ ]
       P004	['의식저하', '고열', '복통']	['의식저하', '고열']
       P005	['두통', '목아픔']	[ ]
       P006	['호흡곤란', '어지러움']	['호흡곤란']

    2. WHERE 필터링 - 심각한 증상이 1개 이상인 환자만

       patient_id	symptoms	serious_symptoms
       P002	['호흡곤란', '가슴통증', '두통']	['호흡곤란', '가슴통증']
       P004	['의식저하', '고열', '복통']	['의식저하', '고열']
       P006	['호흡곤란', '어지러움']	['호흡곤란']

       • P001, P003, P005 제외
  
  
  

• arrayMap( ) 예시

  SELECT
      patient_id,
      prescription_drugs,
      arrayMap(x -> concat(x, ' 200mg'), prescription_drugs) AS drugs_with_dosage
  FROM medical_records_advanced;
  

  • 약물 투여량 계산으로 모든 처방약에 표준 용량(200mg)을 자동 추가 하는 쿼리

  • 원본 데이터

    patient_id	prescription_drugs
    P001	['타이레놀', '소화제']
    P002	['와파린', '아스피린', '비타민']
    P003	['이부프로펜', '감기약']
    P004	['항생제']
    P005	['타이레놀', '소화제', '진통제']

    
    

  • 동작 과정

    1. arrayMap()으로 각 약물명에 '200mg' 문자열 추가

       patient_id	prescription_drugs	drugs_with_dosage
       P001	['타이레놀', '소화제']	['타이레놀 200mg', '소화제 200mg']
       P002	['와파린', '아스피린', '비타민']	['와파린 200mg', '아스피린 200mg', '비타민 200mg']
       P003	['이부프로펜', '감기약']	['이부프로펜 200mg', '감기약 200mg']
       P004	['항생제']	['항생제 200mg']
       P005	['타이레놀', '소화제', '진통제']	['타이레놀 200mg', '소화제 200mg', '진통제 200mg']

기타 유용한 배열 처리 함수들

• arrayExists( )

  arrayExists(x -> 조건, 배열)  -- 조건 만족 요소 존재 여부 (true/false 반환)
  

  • 배열에 조건을 만족하는 요소가 하나라도 있는지 확인
  • arrayExists(x -> x = '복통', ['복통', '두통']) → true
  • 예시 : "위험한 증상이 하나라도 있나?" 체크할 때
  
  
• indexOf( )

  indexOf(배열, 값)  -- 요소의 위치 찾기 (1부터 시작, 없으면 0)
  

  • 배열에서 특정 값의 첫 번째 위치를 반환
  • indexOf(['복통', '두통', '복통'], '두통') → 2
  • 예시 : "특정 약물이 처방 순서상 몇 번째인가?" 확인할 때
  
  
• arrayCount( )

  arrayCount(x -> 조건, 배열)  -- 조건 만족 요소 개수
  

  • 배열에서 조건을 만족하는 요소의 개수를 세기
  • arrayCount(x -> length(x) > 2, ['복통', '두통', '소화불량']) → 2 (복통, 소화불량)
  • 예시 : "심각한 증상이 몇 개나 되나?" 셀 때
  
  
• arrayDistinct( )

  arrayDistinct(배열)  -- 중복 제거된 배열 반환
  

  • 배열에서 중복을 제거하고 고유한 요소들만 반환
  • arrayDistinct(['복통', '두통', '복통']) → ['복통', '두통']
  • 예시 : "중복된 진단코드 제거" 할 때
  
  
• arrayUniq( )

  arrayUniq(배열)  -- 고유 요소 개수
  

  • 배열에서 중복을 제거한 후의 개수를 반환
  • arrayUniq(['복통', '두통', '복통']) → 2
  • 예시 : "환자가 앓은 고유 질병 수" 셀 때
  
  
• arrayReduce( )

  arrayReduce(집계함수, 배열)  -- 배열에 집계함수 적용
  

  • 배열의 모든 요소에 집계 함수를 적용
  • arrayReduce('sum', [10, 20, 30]) → 60
  • arrayReduce('avg', [10, 20, 30]) → 20
  • 예시 : "배열 내 수치들의 합계/평균" 구할 때

실무 팁과 성능 최적화

• 효율적인 쿼리 작성 패턴

  • ❌ 비효율적 : 큰 배열을 매번 JOIN

    SELECT count()
    FROM medical_records mr
    ARRAY JOIN symptoms AS symptom
    WHERE symptom = '복통';
    

    • 문제점
      • 메모리 사용량 증가 - 모든 증상을 개별 행으로 펼친다
      • 불필요한 데이터 생성 - "복통" 하나 찾으려고 전체 배열 펼치기
    
    

  • ✅ 효율적 : has( ) 함수 사용

    SELECT count()
    FROM medical_records
    WHERE has(symptoms, '복통');
    

    • 장점
      • 메모리 절약 - 배열을 펼치지 않고 바로 검색
      • 빠른 처리 - O(n) 배열 스캔으로 즉시 판정
      • 간단한 로직 - 복잡한 JOIN 없이 한 번에 처리
      • ARRAY JOIN 방식 대비 5-10배 빠르다
    
    
    

  • ❌ 비효율적 : 딕셔너리 없는 반복 JOIN

    SELECT mr.*, icd.korean_name
    FROM medical_records mr
    JOIN icd10_master icd ON mr.icd10_primary = icd.code;
    

    • 문제점
      • 디스크 I/O 반복 - 매번 테이블을 디스크에서 읽는다
      • JOIN 연산 비용 - 해시 테이블 생성 + 매칭 과정 반복
      • 캐시 비효율 - 같은 코드를 수백 번 조회해도 매번 JOIN
    
    

  • ✅ 효율적 : 딕셔너리 활용

    SELECT
        *,
        dictGet('icd10_dict', 'korean_name', icd10_primary) AS korean_name
    FROM medical_records;
    

    • 장점
      • 메모리 캐시 - 딕셔너리가 메모리에 존재
      • O(1) 조회 - 해시 테이블로 즉시 검색
      • 중복 제거 - 같은 코드 재조회 시 캐시에서 바로 반환
      • JOIN 방식 대비 10-50배 빠르다
  
  
  

• 메모리 사용량 최적화

  • 대용량 배열 처리 시 메모리 절약

    SELECT
        patient_id,
        arraySlice(symptoms, 1, 3) AS top_symptoms  -- 상위 3개만 처리
    FROM medical_records_advanced
    WHERE length(symptoms) > 10;
    

    • 환자별로 증상이 많을 때 (10개 이상)
    • arraySlice() 함수 활용
      • 배열의 일부분만 추출하여 메모리 절약
    • 활용 시나리오
      • 환자별 주요 증상 TOP 3만 분석
      • 처방약 중 핵심 약물만 체크
      • 검사 결과 중 이상치만 확인
    
    
    

  • 스트리밍 방식으로 큰 결과셋 처리

    SELECT *
    FROM medical_records
    ORDER BY visit_date
    LIMIT 1000000
    SETTINGS max_memory_usage = 1000000000;  -- 1GB 제한
    

    • 대용량 데이터 조회 시 메모리 부족 방지
    • SETTINGS 옵션 설명
      • max_memory_usage - 쿼리 실행 시 최대 메모리 사용량 제한
      • 1000000000 - 1GB (1,000,000,000 bytes)
      • 효과 - 메모리 한계 도달 시 자동으로 디스크 사용
      • 동작 방식
        1. 메모리에서 처리 (빠름)
        2. 메모리 1GB 도달 시
        3. 임시 파일로 스왑 (느리지만 안전)
        4. 스트리밍으로 결과 반환

ClickHouse SQL 핵심 요약

실무에서 자주 하는 실수들

1. 불필요한 ARRAY JOIN 남발

   • has(), arrayExists() 함수로 대체 가능한 경우가 많다.
   • 판별 기준은 "배열 요소별 상세 분석"이 아니라 "존재 여부만 확인"이면 has() 계열 사용하는 것이 좋다
   
   

2. FINAL을 모든 쿼리에 사용

   • 성능 저하 원인, 정말 필요한 경우만 사용
   
   

3. 딕셔너리 설정 없이 반복 JOIN

   • 자주 조회하는 마스터 테이블은 반드시 딕셔너리로 구성
   • 기준
     • 조회 빈도 >100회/시간 → 딕셔너리 필수
     • 조회 빈도 >마스터 테이블 크기 < 10만 건 → 딕셔너리 적합
     • 변경 빈도 < 1회/일 → 딕셔너리 적합
   • 예시
     • ICD-10 진단코드
     • 약품 마스터
     • 부서/지역 코드
     • 고객 등급 코드

최적화 팁

• "찾기"가 목적이면 → has(), hasAny(), arrayExists() 계열 함수 사용
• "변환"이 목적이면 → dictGet() 또는 arrayMap() 활용
• "분석"이 목적이면 → ARRAY JOIN + 집계 함수 조합
• 자주 쓰는 마스터 테이블 → 반드시 딕셔너리로 구성 (조회 빈도 > 100회/시간)
• 배열 크기 > 10개 → arraySlice()로 메모리 사용량 주의
• 대용량 결과셋 → SETTINGS max_memory_usage 제한 설정
• ReplacingMergeTree → 정말 필요한 경우만 FINAL 사용
• 마스터 테이블 크기 < 10만 건 → 딕셔너리 최적화 대상