임상 메타데이터 검색엔진 서비스 구축

wampy 2026. 1. 11. 16:03

2026. 1. 11. 16:03

사내 임상 연구팀을 위한 임상 메타데이터 관리 시스템을 구축하는 과정에서,
검색 필터에 대한 요구사항은 점점 증가하게 될거라는 기획팀의 첨언을 받았고..

임상 데이터의 특성상 다음과 같은 제약이 존재했다.

질환군, 대상자, 샘플 등 도메인 구조가 매우 복잡
데이터 형식이 표준화되어 있지 않고 비정형 요소가 많음
연구원이 직접 검색 조건을 조합해야 하는 요구사항
데이터는 Write/Update가 거의 없고 Read 비중이 매우 높음

초기 시스템은 당연히 일반적인 RDB 기반 모델로 설계되어있었고.. 기본 메타 테이블은 다음과 같았다

STUDY (연구)

study_id	BIGINT	연구(임상) ID
study_code	VARCHAR(50)	화면/외부 식별자
study_name	NVARCHAR(200)	연구명
status	VARCHAR(20)	ACTIVE / CLOSED 등

SITE (기관)

site_id	BIGINT	기관/사이트 ID
study_id	BIGINT	소속 연구
site_code	VARCHAR(50)	외부 식별자
site_name	NVARCHAR(200)	기관명
region	NVARCHAR(100)	지역

SUBJECT (대상자)

subject_id	BIGINT	PK	내부 PK
study_id	BIGINT	FK(STUDY)	연구
site_id	BIGINT	FK(SITE)	기관
subject_code	VARCHAR(50)	UNIQUE	예: SUBJ-000124
status	VARCHAR(20)	NOT NULL	ACTIVE / DROPPED 등
sex	VARCHAR(10)	NULL	M/F/UNKNOWN
birth_year	INT	NULL	출생년도

SAMPLE (샘플)

sample_id	BIGINT	PK	내부 PK
study_id	BIGINT	FK(STUDY)	연구
site_id	BIGINT	FK(SITE)	기관
subject_id	BIGINT	FK(SUBJECT)	대상자
sample_code	VARCHAR(50)	UNIQUE	예: SMP-99101
sample_type	VARCHAR(30)	NOT NULL	blood/tissue/urine 등
collected_at	DATETIME2	NULL	채취일

고정 속성의 테이블은 이런식으로 되어있었고, 문제는 가변속성의 EAV 구조의 테이블이였는데

CLINICAL_ATTR (통합 속성 / EAV 단일 테이블)

attr_row_id	BIGINT	PK	속성 row PK
study_id	BIGINT	FK(STUDY)	연구 범위
entity_type	VARCHAR(20)	NOT NULL	SUBJECT / SAMPLE / COHORT 등
entity_id	BIGINT	NOT NULL	대상 엔티티 PK
attr_key	VARCHAR(100)	NOT NULL	속성 키 (예: hba1c, smoker)
value_type	VARCHAR(20)	NOT NULL	STRING/NUMBER/DATE/BOOL
value_string	NVARCHAR(1000)	NULL	문자열 값
value_number	FLOAT	NULL	숫자 값
value_date	DATETIME2	NULL	날짜 값
value_bool	BIT	NULL	불리언 값
unit	VARCHAR(30)	NULL	단위 (mg/dL 등)
source	VARCHAR(30)	NULL	입력원(장비/수기 등)
updated_at	DATETIME2	NOT NULL	수정일

데이터를 입력할때는 제법 유연한 구조였지만 필터링이 들어가는 경우 (요구사항이 추가됐을 때..) 무한 join을 맺어야 하는.. 복합적인 조인 지옥을 맞이했다

예를 들면 '대상자'에

흡연자가 아님 (smoker = false)
HbA1c >= 6.5
약물 metormin 복용

이라는 요구사항이 붙었을 때

SELECT s.subject_id, s.subject_code, s.sex, s.birth_year
FROM SUBJECT s
JOIN CLINICAL_ATTR a_smoker
  ON a_smoker.entity_type = 'SUBJECT'
 AND a_smoker.entity_id = s.subject_id
 AND a_smoker.attr_key = 'smoker'
 AND a_smoker.value_bool = 0
JOIN CLINICAL_ATTR a_hba1c
  ON a_hba1c.entity_type = 'SUBJECT'
 AND a_hba1c.entity_id = s.subject_id
 AND a_hba1c.attr_key = 'hba1c'
 AND a_hba1c.value_number >= 6.5
JOIN CLINICAL_ATTR a_med
  ON a_med.entity_type = 'SUBJECT'
 AND a_med.entity_id = s.subject_id
 AND a_med.attr_key = 'medication'
 AND a_med.value_string = 'metformin'
WHERE s.status = 'ACTIVE';

의 쿼리가 작성되며, 필터 1개가 추가할 떄마다 CLINICAL_ATTR < 테이블에 join 1개가 추가되는 기본 패턴이 되고만다..

여기에

blood 샘플 존재
blood 샘플의 tube = EDTA
QC PASS
vloume_ml >= 5

와 같은.. 필터링이 추가로 들어갔을 때는

SELECT DISTINCT s.subject_id, s.subject_code
FROM SUBJECT s
JOIN SAMPLE sp
  ON sp.subject_id = s.subject_id
 AND sp.sample_type = 'blood'
 AND sp.qc_status = 'PASS'

JOIN CLINICAL_ATTR a_smoker
  ON a_smoker.entity_type='SUBJECT' AND a_smoker.entity_id=s.subject_id
 AND a_smoker.attr_key='smoker' AND a_smoker.value_bool=0
JOIN CLINICAL_ATTR a_hba1c
  ON a_hba1c.entity_type='SUBJECT' AND a_hba1c.entity_id=s.subject_id
 AND a_hba1c.attr_key='hba1c' AND a_hba1c.value_number>=6.5
JOIN CLINICAL_ATTR a_med
  ON a_med.entity_type='SUBJECT' AND a_med.entity_id=s.subject_id
 AND a_med.attr_key='medication' AND a_med.value_string='metformin'

-- 샘플에 대한 필터링 조건 또 추가
JOIN CLINICAL_ATTR a_tube
  ON a_tube.entity_type='SAMPLE' AND a_tube.entity_id=sp.sample_id
 AND a_tube.attr_key='tube' AND a_tube.value_string='EDTA'
JOIN CLINICAL_ATTR a_vol
  ON a_vol.entity_type='SAMPLE' AND a_vol.entity_id=sp.sample_id
 AND a_vol.attr_key='volume_ml' AND a_vol.value_number>=5;

필터가 subject에 3개, sample에 2개 붙으면 → attr 조인만 5개 붙어야했고, 여기서 만약 SAMLE 조건이 붙는다면 결과 중복때문에 GROUP BY 가 붙어 성능과 가독성이 악화되는 사태가 발생하게 되는데..

이 RDB의 문제점들을 정리해보자면

1) 검색 쿼리 작성 난이도 증가

질환군 ↔ 대상자 ↔ 샘플 간 N:M 관계
하나의 검색 조건을 만족시키기 위해 다수 테이블 조인 필수
연구원이 요구하는 조건이 늘어날수록 쿼리는 기하급수적으로 복잡해짐

2) 성능 및 확장성 문제

조건이 늘어날수록 조인 수 증가 → 성능 저하
검색 조건 변경 시마다 SQL 수정 필요
비정형 데이터 처리에 적합하지 않음

데이터는 많아지는데 검색은 점점 느려지고 어려워짐에 따라 구조 자체가 검색에 적합하지 않다는 판단에 도달하였다.

특히 데이터 특성상 update는 거의 없고 (임상 실험 결과가 변경되는 경우는 매우 드뭄) 비정형 구조에 도메인 다양성이 큰데다가 무엇보다 개발하는 플랫폼의 요구사항 자체가 '필터링' 에 집중되어있기 떄문에, 검색 조건이 지속적으로 확장될 것을 고려해 근본적인 데이터 구조를 수정해보자는 의견이 나오게 되었고..

비정형 데이터임을 고려하여 유연한 스키마에 JSON 구조의 MongoDB도 함꼐 고려해보였으나

검색/필터링 < 쿼리에 한계가 있을 것을 고려해 ElasticSEarch를 선택하게 되었다.

검색과 필터링에 특화된 엔진
다양한 조건 조합(Query DSL)에 강점
인덱스 기반 구조로 성능 예측 가능
향후 검색 요구사항 확장에 유리

ES 도입의 목적은.. '조인 없는 검색' 즉 데이터 자체를 flat한 구조로 변환함에 있었는데 인덱스 설계 구조는 다음과 같았다.

도메인 단위별 인덱스 설계
- clinical_subject : 대상자 (검색화면)
- clinical_sample : 샘플 (샘플 목록/필품질/보관조건 등)
- clinical_cohort: 질환군/코호트 (코포트/ 대상자 집합 등)

변환 원칙

원칙 1) “조회 단위(화면 단위)”로 문서를 만든다

대상자 검색 화면 → subject 문서 1장
샘플 검색 화면 → sample 문서 1장
코호트 관리 화면 → cohort 문서 1장

원칙 2) 속성은 “타입별 맵(Map)”으로 flat하게 만든다

EAV의 가장 큰 문제는 “키가 유동적”이라 매핑이 불안정해지는 것인데,
ES에서는 아래처럼 정리해서 해결한다.

attrsKw : 문자열/카테고리/enum → keyword
attrsNum : 숫자 → double
attrsDate : 날짜 → date
attrsBool : bool → boolean

즉, DB row 단위 속성들을 문서 내부의 typed map으로 접어 넣는다.

원칙 3) join 제거를 위해 필요한 스냅샷을 문서에 포함한다

sample 문서에 subjectSnapshot(성별/나이/중요 임상수치)을 함께 넣어
“샘플 검색 → 대상자 조건 필터”가 조인 없이 가능하도록 한다.

Subject 문서 생성 로직 (clinical_subject_v1)

입력 데이터 소스

SUBJECT 고정 필드
CLINICAL_ATTR where entity_type='SUBJECT' and entity_id=subject_id
SAMPLE 목록 (subject_id로)
CLINICAL_ATTR where entity_type='SAMPLE' and entity_id in (sample_ids) (옵션: subject 문서에 샘플을 nested로 넣는 경우)

변환 과정 (단계별)

Step A) subject 고정 필드 매핑

subjectId, studyId, siteId, sex, birthYear, enrolledAt, status …

Step B) subject 속성(EAV) → typed map 으로 변환

예)

(SUBJECT, 124, "hba1c", NUMBER, 7.2)
→ attrsNum["hba1c"] = 7.2
(SUBJECT, 124, "medication", STRING, "metformin")
→ attrsKw["medication"] = "metformin"
(SUBJECT, 124, "smoker", BOOL, false)
→ attrsBool["smoker"] = false
(SUBJECT, 124, "last_visit", DATE, 2025-11-28)
→ attrsDate["last_visit"] = "2025-11-28"

Step C) 샘플 요약값 생성(검색/정렬 최적화)

sampleSummary.totalCount
sampleSummary.types (distinct)
sampleSummary.latestAt (MAX(collected_at))

Step D) (선택) subject 문서에 samples를 nested로 포함

대상자 페이지에서 “샘플 조건”으로 필터링 해야 하면 nested가 유용
샘플이 너무 많으면 subject 문서가 비대해지므로 sample 인덱스를 분리해서 해결하기도 함
(너가 이미 둘 다 운영하는 구조로 잡았으니 상황에 따라 선택)

Sample 문서 생성 로직 (clinical_sample_v1)

입력 데이터 소스

SAMPLE 고정 필드
CLINICAL_ATTR where entity_type='SAMPLE' and entity_id=sample_id
SUBJECT 고정 필드(스냅샷)
CLINICAL_ATTR where entity_type='SUBJECT' and entity_id=subject_id (스냅샷에 필요한 일부만)

변환 과정

Step A) sample 고정 필드 매핑

sampleId, sampleType, collectedAt, storage_temp_c, qc_status…

Step B) sample 속성 → typed map

tube(STRING) → attrsKw.tube
volume_ml(NUMBER) → attrsNum.volume_ml
expired_at(DATE) → attrsDate.expired_at

Step C) subjectSnapshot 생성

샘플 검색 화면에서 자주 걸리는 대상자 조건만 “스냅샷”으로 포함

sex, birthYear
hba1c, bmi 같은 주요 수치
medication 같은 주요 키워드

이렇게 하면:

“QC PASS + blood 샘플 + 대상자 hba1c>=6.5” 같은 검색을 sample 인덱스 단독으로 처리 가능해짐.

PUT clinical_subject_v1
{
  "mappings": {
    "dynamic": false,
    "properties": {
      "subjectId": {"type":"keyword"},
      "studyId": {"type":"keyword"},
      "siteId": {"type":"keyword"},
      "cohortIds": {"type":"keyword"},

      "status": {"type":"keyword"},
      "sex": {"type":"keyword"},
      "birthYear": {"type":"integer"},
      "enrolledAt": {"type":"date"},
      "updatedAt": {"type":"date"},

      "attrsKw": {"type":"object","dynamic":true},
      "attrsNum": {"type":"object","dynamic":true},
      "attrsDate":{"type":"object","dynamic":true},
      "attrsBool":{"type":"object","dynamic":true},

      "sampleSummary": {
        "properties": {
          "totalCount": {"type":"integer"},
          "types": {"type":"keyword"},
          "latestAt": {"type":"date"},
          "qcPassCount": {"type":"integer"}
        }
      },

      "sampleSignals": {
        "properties": {
          "hasBlood": {"type":"boolean"},
          "hasEdtaTube": {"type":"boolean"},
          "maxVolumeMl": {"type":"double"}
        }
      }
    }
  }
}

PUT clinical_sample_v1
{
  "mappings": {
    "dynamic": false,
    "properties": {
      "sampleId": {"type":"keyword"},
      "studyId": {"type":"keyword"},
      "siteId": {"type":"keyword"},

      "subjectId": {"type":"keyword"},
      "cohortIds": {"type":"keyword"},

      "sampleType": {"type":"keyword"},
      "collectedAt": {"type":"date"},
      "qcStatus": {"type":"keyword"},
      "qcScore": {"type":"double"},

      "storageTempC": {"type":"double"},
      "storageLocation": {"type":"keyword"},

      "attrsKw": {"type":"object","dynamic":true},
      "attrsNum": {"type":"object","dynamic":true},
      "attrsDate":{"type":"object","dynamic":true},
      "attrsBool":{"type":"object","dynamic":true},

      "subjectSnapshot": {
        "properties": {
          "sex": {"type":"keyword"},
          "birthYear": {"type":"integer"},
          "status": {"type":"keyword"},

          "attrsKw": {"type":"object","dynamic":true},
          "attrsNum": {"type":"object","dynamic":true},
          "attrsDate":{"type":"object","dynamic":true},
          "attrsBool":{"type":"object","dynamic":true}
        }
      },

      "updatedAt": {"type":"date"}
    }
  }
}

PUT clinical_cohort_v1
{
  "mappings": {
    "dynamic": false,
    "properties": {
      "cohortId": {"type":"keyword"},
      "studyId": {"type":"keyword"},
      "name": {"type":"keyword"},
      "description": {"type":"text"},
      "version": {"type":"integer"},
      "status": {"type":"keyword"},

      "criteria": {
        "properties": {
          "humanText": {"type":"text"},
          "dsl": {"type":"object","dynamic":true}
        }
      },

      "stats": {
        "properties": {
          "subjectCount": {"type":"integer"},
          "sampleCount": {"type":"integer"},
          "updatedAt": {"type":"date"}
        }
      },

      "createdAt": {"type":"date"},
      "updatedAt": {"type":"date"}
    }
  }
}

아까 RDB에서 self-join 5개였던 요구사항:

smoker=false
hba1c>=6.5
medication=metformin
blood sample
tube=EDTA
qc=PASS
volume>=5

ES에서 처리할 때

GET clinical_sample_v1/_search
{
  "query": {
    "bool": {
      "filter": [
        {"term": {"sampleType": "blood"}},
        {"term": {"qcStatus": "PASS"}},
        {"term": {"attrsKw.tube": "EDTA"}},
        {"range": {"attrsNum.volume_ml": {"gte": 5}}},

        {"term": {"subjectSnapshot.attrsBool.smoker": false}},
        {"range": {"subjectSnapshot.attrsNum.hba1c": {"gte": 6.5}}},
        {"term": {"subjectSnapshot.attrsKw.medication": "metformin"}}
      ]
    }
  }
}

'업무 기록' 카테고리의 다른 글

테스트코드 잘 작성하기.. (0)	2026.02.04
사내 efk 서버 구축 - 1 (0)	2026.01.11
kafka 기반 로그 스트리밍 시스템 구축하기 (0)	2026.01.04
대용량 출력 최적화 과정 (0)	2026.01.02
다중 로그인 제어 적용하기 (0)	2024.06.29

modern life