JSON 컬럼 활용을 위한 설문 테이블 통합

다음은 문제상황을 정의하고 해결방안을 도출한 과정입니다. A사는 설문 데이터를 저장하고 분석하는 요건을 진행하고 있습니다.

설문지 데이터를 수집하기 위한 최초 설계에서는 각 설문지마다 별도의 테이블을 생성하고 문항을 컬럼으로 정의하는 구조를 채택했으나, 설문 수가 증가함에 따라 테이블 수도 선형적으로 늘어나 ClickHouse의 Hard Limit에 도달할 위험이 발생했습니다. 현재 5개 설문에 5개 테이블이 존재하며 메타 정보 테이블까지 별도로 관리되고 있는데, 설문별로 문항 수와 형태가 상이하여 테이블 간 공통화할 수 있는 요소가 제한적입니다. 이를 해결하기 위해 모든 설문 데이터를 하나의 통합 테이블(One Big Table)로 전환하고, 설문 메타 정보는 공통 컬럼으로, 가변적인 설문 응답은 JSON 컬럼으로 저장하는 구조로 마이그레이션하고자 합니다.

• 1. 문제 상황의 이해
• 1.1 배경
• 1.2 해결 방향
• 1.3 현재 아키텍처의 구조적 한계
• 1.4 구체적인 문제점
• 2. 해결 방안: ClickHouse JSON과 View 활용
• 2.1 ClickHouse JSON 타입 개요
• 2.2 JSON 쿼리 패턴
• 패턴 1: 기본 필드 접근
• 패턴 2: 숫자 연산을 위한 타입 캐스팅
• 패턴 3: JSON 구조 탐색
• 패턴 4: 필드 존재 여부 확인
• 패턴 5: 조건부 필터링
• 패턴 6: Context 필드를 활용한 그룹화
• 패턴 7: 크로스 설문 통합 분석
• 패턴 8: 텍스트 검색
• 2.3 성능 최적화: Materialized View (제한적 활용)
• 2.4 마이그레이션 기간 한정: View의 임시 활용
• 2.5 JSON 타입 힌트로 성능 향상
• 3. 통합 테이블 설계 원칙 및 활용 방안
• 3.1 통합 테이블 스키마
• 3.2 설계 원칙
• 3.3 신규 설문 입수 (DDL 변경 불필요)
• 3.4 최적화 전략 비교
• 4. 마이그레이션의 실제
• 4.1 마이그레이션 SQL
• 4.2 데이터 정합성 검증
• 4.3 스토리지 비교
• 4.4 테이블/컬럼 수 비교
• 4.5 마이그레이션 단계별 계획
• 5. 정리
• 5.1 핵심 검증 결과
• 5.2 트레이드오프
• 5.3 권장사항
• 5.4 생성된 오브젝트 요약

1. 문제 상황의 이해

1.1 배경

설문지 데이터를 수집하기 위한 최초의 시스템 설계는 매 설문지마다 새로운 테이블을 생성하고, 각 문항이 테이블의 컬럼이 되는 구조였습니다. 예를 들어, "고객 만족도 조사"라는 설문이 5개 문항을 가지고 있다면 survey_customer_satisfaction이라는 테이블이 생성되고, q1, q2, q3, q4, q5 각각이 별도의 컬럼으로 정의되는 방식입니다.

이 설계는 초기에는 직관적이고 쿼리가 단순하다는 장점이 있었습니다. 그러나 서비스가 성장하면서 설문의 수가 지속적으로 증가하게 되었고, 이에 따라 테이블 수가 선형적으로 증가하는 문제가 발생했습니다. 현재 5개의 설문이 운영 중이라면 5개의 설문 응답 테이블이 존재하며, 여기에 설문지 메타 정보를 관리하는 테이블까지 별도로 존재합니다.

더 큰 문제는 설문지마다 문항 수와 형태가 상이하다는 점입니다. 어떤 설문은 4개 문항의 간단한 척도 평가이고, 어떤 설문은 12개 문항에 자유 텍스트, 다중 선택, NPS 점수 등 다양한 형태를 포함합니다. 이로 인해 테이블 간 공통화할 수 있는 요소가 매우 제한적이며, 크로스 설문 분석 시 복잡한 UNION ALL 쿼리가 필요합니다.

1.2 해결 방향

이러한 상황에서 설문지 데이터를 One Big Table 형태로 통합해야 한다는 결론에 도달했습니다. 통합 테이블에는 다음 요소들이 포함되어야 합니다.

첫째, 설문의 메타 정보입니다. 어떤 설문인지 식별하는 survey_id, 설문 이름, 응답자 정보, 제출 시간 등 모든 설문에 공통으로 적용되는 필드들입니다.

둘째, 설문별 컨텍스트 정보입니다. 직원 설문의 "부서명", 이벤트 피드백의 "이벤트명"처럼 특정 설문에만 해당하는 분류 정보입니다.

셋째, 설문 응답 데이터입니다. 문항 수와 형태가 다양한 실제 응답 값들로, 이 부분은 JSON 컬럼으로 통합하여 스키마 유연성을 확보할 수 있습니다.

본 보고서는 이 해결 방향의 기술적 타당성을 검증하기 위해, 실제 ClickHouse Cloud 환경에서 5개의 레거시 설문 테이블을 생성하고, JSON 기반 통합 테이블로 마이그레이션하는 전 과정을 수행한 결과입니다.

1.3 현재 아키텍처의 구조적 한계

현재 설문 시스템은 각 설문지마다 독립적인 테이블을 생성하는 구조입니다. 설문지의 문항이 테이블의 컬럼으로 정의되어 있어, 5개 문항의 설문은 8개 컬럼(공통 필드 포함), 12개 문항의 설문은 16개 컬럼을 가진 테이블이 됩니다.

검증을 위해 테스트 환경에서 생성한 레거시 테이블 현황:

레거시 테이블 생성 DDL 예시:

1.4 구체적인 문제점

첫째, 테이블 수 증가로 인한 Hard Limit 위험입니다. ClickHouse Cloud는 서비스 티어에 따라 테이블 수에 제한이 있습니다. 현재 5개 설문에 5개 테이블이지만, 설문이 1,000개로 늘어나면 1,000개의 테이블이 필요합니다. 이는 단순히 제한에 걸리는 문제를 넘어서, ZooKeeper/Keeper의 메타데이터 관리 부담 증가, 백업 및 복구 복잡도 상승, 스키마 관리의 어려움 등 운영 전반에 영향을 미칩니다.

-- 현재 survey 스키마의 테이블 현황
SELECT name, engine, total_rows, formatReadableSize(total_bytes) as size
FROM system.tables WHERE database = 'survey' ORDER BY name

둘째, 크로스 설문 분석의 복잡성입니다. 여러 설문의 응답을 통합 분석하려면 UNION ALL을 사용해야 하며, 설문이 추가될 때마다 쿼리를 수정해야 합니다.

셋째, 운영 복잡도 증가입니다. 신규 설문 추가 시마다 DDL 실행, 입수 파이프라인 설정, 권한 부여, 백업 정책 설정 등 반복적인 작업이 필요합니다. 설문이 수백 개로 늘어나면 이러한 운영 부담이 기하급수적으로 증가합니다.

2. 해결 방안: ClickHouse JSON과 View 활용

2.1 ClickHouse JSON 타입 개요

ClickHouse 24.1+에서 도입된 새로운 JSON 타입은 기존 String 기반 JSON과 달리 컬럼 지향 저장을 지원합니다. JSON 내부의 각 경로(path)가 별도의 서브컬럼으로 저장되어 압축 효율과 쿼리 성능이 크게 향상됩니다.

핵심 특징:

• 동적 스키마: 사전 정의 없이 다양한 구조의 JSON 저장 가능
• 서브컬럼 접근: column.path 문법으로 직접 접근
• 타입 추론: 저장 시점에 자동 타입 감지
• 선택적 타입 힌트: 자주 사용하는 필드에 명시적 타입 지정 가능

2.2 JSON 쿼리 패턴

패턴 1: 기본 필드 접근

-- 점(.) 표기법으로 JSON 필드에 직접 접근
SELECT
    respondent_id,
    responses.q1_overall_satisfaction as q1,
    responses.q5_feedback as feedback
FROM survey.survey_responses_unified
WHERE survey_id = 'SURV001'
LIMIT 5

패턴 2: 숫자 연산을 위한 타입 캐스팅

JSON에서 추출한 값은 Dynamic 타입이므로 집계 함수 사용 시 명시적 캐스팅이 필요합니다.

-- 방법 1: toUInt8OrZero + toString (안전한 변환, 실패 시 0 반환)
SELECT
    avg(toUInt8OrZero(toString(responses.q1_overall_satisfaction))) as avg_q1,
    avg(toUInt8OrZero(toString(responses.q2_service_quality))) as avg_q2
FROM survey.survey_responses_unified
WHERE survey_id = 'SURV001'

┌─avg_q1─┬─avg_q2─┐
│    3.0 │    3.0 │
└────────┴────────┘

-- 방법 2: 계산식에서의 캐스팅
SELECT
    respondent_id,
    toUInt8(toString(responses.q1_overall_satisfaction)) as q1_score,
    toUInt8(toString(responses.q2_service_quality)) as q2_score,
    (toUInt8(toString(responses.q1_overall_satisfaction)) +
     toUInt8(toString(responses.q2_service_quality))) / 2 as avg_score
FROM survey.survey_responses_unified
WHERE survey_id = 'SURV001'
LIMIT 3

┌─respondent_id─┬─q1_score─┬─q2_score─┬─avg_score─┐
│          1004 │        4 │        4 │       4.0 │
│          1005 │        4 │        4 │       4.0 │
│          1014 │        3 │        3 │       3.0 │
└───────────────┴──────────┴──────────┴───────────┘

패턴 3: JSON 구조 탐색

설문별로 어떤 문항(키)이 있는지 동적으로 조회할 수 있습니다.

-- 설문별 JSON 키(문항) 목록 조회
SELECT
    survey_id,
    survey_name,
    groupUniqArray(arrayJoin(JSONAllPaths(responses))) as question_keys
FROM survey.survey_responses_unified
GROUP BY survey_id, survey_name
ORDER BY survey_id

패턴 4: 필드 존재 여부 확인

-- IS NOT NULL로 필드 존재 여부 확인
SELECT
    survey_id,
    respondent_id,
    responses.q5_feedback IS NOT NULL as has_feedback,
    responses.q12_additional_comments IS NOT NULL as has_comments
FROM survey.survey_responses_unified
WHERE survey_id IN ('SURV001', 'SURV003')
LIMIT 4

패턴 5: 조건부 필터링

-- JSON 필드 값 기반 필터링
SELECT survey_id, respondent_id,
       toUInt8OrZero(toString(responses.q1_overall_satisfaction)) as score
FROM survey.survey_responses_unified
WHERE survey_id = 'SURV001'
  AND toUInt8OrZero(toString(responses.q1_overall_satisfaction)) >= 4
ORDER BY score DESC

┌─survey_id─┬─respondent_id─┬─score─┐
│ SURV001   │          1011 │     5 │
│ SURV001   │          1006 │     5 │
│ SURV001   │          1004 │     4 │
│ SURV001   │          1005 │     4 │
│ SURV001   │          1001 │     4 │
└───────────┴───────────────┴───────┘

패턴 6: Context 필드를 활용한 그룹화

설문별 컨텍스트 정보(부서, 이벤트명 등)를 별도 JSON 컬럼에 저장하고 GROUP BY에 활용합니다.

패턴 7: 크로스 설문 통합 분석

UNION ALL 없이 단일 쿼리로 여러 설문을 비교 분석합니다.

패턴 8: 텍스트 검색

-- LIKE 패턴으로 피드백 검색
SELECT respondent_id, toString(responses.q5_feedback) as feedback
FROM survey.survey_responses_unified
WHERE survey_id = 'SURV001'
  AND toString(responses.q5_feedback) LIKE '%만족%'

┌─respondent_id─┬─feedback──────────┐
│          1002 │ 매우 만족합니다    │
│          1012 │ 매우 만족합니다    │
│          1000 │ 매우 만족합니다    │
└───────────────┴───────────────────┘

2.3 성능 최적화: Materialized View (제한적 활용)

설문별로 MV를 생성하는 것은 권장하지 않습니다. 대신, 전체 설문을 아우르는 통합 분석 지표용 MV를 소수만 생성하여 활용합니다.

통합 MV 활용 쿼리:

-- 전체 설문의 일별 응답 추이를 빠르게 조회
SELECT
    response_date,
    sum(daily_responses) as total_responses,
    sum(unique_respondents) as total_respondents
FROM survey.mv_daily_survey_stats
WHERE response_date >= today() - 30
GROUP BY response_date
ORDER BY response_date

이 방식은 오브젝트 수를 최소화하면서도 대시보드나 모니터링에 필요한 핵심 지표를 빠르게 조회할 수 있게 합니다.

2.4 마이그레이션 기간 한정: View의 임시 활용

View 역시 오브젝트 수에 포함되므로, 설문별 호환성 View를 상시 운영하는 것은 권장하지 않습니다. 다만, 마이그레이션 기간 동안 기존 분석 쿼리를 즉시 수정하기 어려운 경우, 신규 유입 데이터 위주로 임시 View를 생성하여 쿼리 전환 시간을 확보할 수 있습니다.

임시 View 운영 원칙:

• 마이그레이션 기간(예: 4주) 동안만 유지
• 쿼리 전환이 완료된 설문부터 순차적으로 View 삭제
• 최종 목표는 모든 분석 쿼리가 통합 테이블을 직접 조회하는 것

2.5 JSON 타입 힌트로 성능 향상

스키마가 확정된 설문에는 JSON 서브컬럼에 타입을 명시하여 런타임 캐스팅을 제거할 수 있습니다.

타입 힌트 적용 후 쿼리 (캐스팅 불필요):

SELECT
    avg(responses.q1_overall_satisfaction) as avg_q1,
    avg(responses.q2_service_quality) as avg_q2
FROM survey.survey_responses_with_typed_json

┌─avg_q1─┬─avg_q2─┐
│    3.0 │    3.0 │
└────────┴────────┘

3. 통합 테이블 설계 원칙 및 활용 방안

3.1 통합 테이블 스키마

3.2 설계 원칙

원칙 1: 공통 필드의 명시적 분리

response_id, survey_id, respondent_id, submitted_at은 모든 설문에 공통으로 적용됩니다. 이들을 별도 컬럼으로 유지하면 ORDER BY와 WHERE 절에서 인덱스를 활용할 수 있습니다.

원칙 2: 컨텍스트와 응답의 분리

부서명이나 이벤트명처럼 분류에 사용되는 정보는 context JSON에, 실제 문항 응답은 responses JSON에 분리 저장합니다. 이를 통해 GROUP BY 성능을 최적화할 수 있습니다.

원칙 3: 파티셔닝 전략

toYYYYMM(submitted_at)으로 월별 파티션을 적용하여 시간 범위 쿼리 성능을 향상시킵니다.

3.3 신규 설문 입수 (DDL 변경 불필요)

새로운 설문이 추가되어도 테이블 구조 변경 없이 데이터를 입수할 수 있습니다.

입수된 데이터 확인:

SELECT survey_id, survey_name, respondent_id, context, responses
FROM survey.survey_responses_unified
WHERE survey_id = 'SURV006'

3.4 최적화 전략 비교

4. 마이그레이션의 실제

4.1 마이그레이션 SQL

각 레거시 테이블의 데이터를 통합 테이블로 이관합니다. toJSONString과 map 함수를 사용하여 컬럼들을 JSON으로 변환합니다.

고객 만족도 설문 마이그레이션:

직원 만족도 설문 마이그레이션 (context에 department 포함):

4.2 데이터 정합성 검증

건수 비교:

SELECT
    survey_id, survey_name, source_table, count() as unified_count
FROM survey.survey_responses_unified
GROUP BY survey_id, survey_name, source_table
ORDER BY survey_id

값 비교 (레거시 vs 통합):

-- 레거시 테이블
SELECT respondent_id, q1_overall_satisfaction, q2_service_quality, q5_feedback
FROM survey.survey_customer_satisfaction ORDER BY respondent_id LIMIT 3

-- 통합 테이블
SELECT respondent_id,
       responses.q1_overall_satisfaction as q1,
       responses.q2_service_quality as q2,
       responses.q5_feedback as feedback
FROM survey.survey_responses_unified
WHERE survey_id = 'SURV001' ORDER BY respondent_id LIMIT 3

4.3 스토리지 비교

┌─category─────────────┬─total_rows─┬─compressed_size─┐
│ Legacy Tables (Sum)  │         75 │ 9.34 KiB        │
│ Unified Table        │         78 │ 11.24 KiB       │
└──────────────────────┴────────────┴─────────────────┘

통합 테이블은 JSON 키 이름이 반복 저장되어 약 20% 정도 크기가 증가하지만, 테이블 수가 5개에서 1개로 80% 감소하는 이점이 훨씬 큽니다.

4.4 테이블/컬럼 수 비교

┌─category─────────────┬─total_columns─┐
│ Legacy Tables Total  │            52 │
│ Unified Table        │             9 │
└──────────────────────┴───────────────┘

4.5 마이그레이션 단계별 계획

5. 정리

5.1 핵심 검증 결과

본 POC에서 5개의 레거시 설문 테이블(총 75건, 52개 컬럼)을 JSON 기반 단일 통합 테이블(9개 컬럼)로 성공적으로 마이그레이션했습니다.

5.2 트레이드오프

개선되는 점:

• 테이블 Hard Limit 문제 근본 해결
• 신규 설문 추가 시 DDL 변경 불필요
• 크로스 설문 분석이 단일 쿼리로 가능
• 설문 메타데이터 중앙 관리

비용:

• 쿼리에 타입 캐스팅 필요 (MV/View로 해소 가능)
• JSON 키 저장으로 스토리지 약간 증가
• 기존 쿼리 수정 필요 (호환성 View로 점진적 전환)

5.3 권장사항

첫째, 마이그레이션을 진행합니다. Hard Limit 리스크 대비 쿼리 복잡도 증가는 MV와 View로 충분히 보완 가능합니다.

둘째, 자주 분석하는 설문에는 Materialized View를 적용합니다. JSON 파싱 오버헤드를 제거하고 레거시 쿼리 형태를 유지할 수 있습니다.

셋째, 점진적 전환을 수행합니다. 호환성 View를 활용하여 기존 분석 쿼리를 수정 없이 유지하면서, 검증 후 단계적으로 신규 쿼리로 전환합니다.

넷째, 문항 메타데이터 테이블을 추가로 관리합니다. 각 설문의 문항 정의(question_id, question_text, question_type)를 별도 테이블로 관리하여 JSON 구조의 가시성을 보완합니다.

5.4 생성된 오브젝트 요약

실행 환경: ClickHouse Cloud (ap-northeast-2)