CDM 5.3버전 샘플데이터를 통한 OMOP-CDM 특징 알아보기 (R

 

 

 

안녕하세요. 씨앤텍 시스템즈 입니다.

 

 

이번 글은 OHDSI 측에서 배포해주는 샘플데이터를 통해

 

OMOP-CDM의 특징을 관계형 데이터베이스(Relational Database; RDB) 관점에서 알아보도록 하겠습니다.

 

 

---

 

 

먼저 관계형 데이터가 무엇인지에 대해서 알아보도록 하겠습니다.

 

 

 

 

1. 관계형 데이터 모델 (Relational Data Model)

   ① 정의

     l  데이터가 테이블 형태로 표현되며

     l  사용자가 데이터를 쉽게 다룰 수 있도록 질의어 (SQL; Structured Query Language) 제공

 

  ② 특징

     l  데이터는 정해진 엄격한 데이터 스키마(=structure)를 따라 데이터베이스 테이블에 저장

       - 스키마를 준수하지 않는 레코드는 추가할 수 없음

     l  데이터는 관계를 통해서 연결된 여러 개의 테이블에 분산됨

      - 중복된 데이터 허용하지 않음 (데이터 무결성)

 

 

 

 

비관계형 데이터 모델과 비교하면 좀 더 이해가 쉬울 것 같습니다.

 

 

 

 

2. NoSQL (비관계형 데이터베이스)

  ① 정의

     l  관계형 데이터베이스와 반대이므로 스키마와 관계가 없다고 보면 됨

     l  RDM에서의 테이블 -> NoSQL은 collection

     l  RDM에서의 레코드 -> NoSQL에서는 documents (JSON 데이터와 비슷한 형태)

     l  다른 구조의 데이터를 같은 컬렉션(SQL에서의 테이블)에 추가 가능

       - 데이터 중복 가능성 있음

 

  ② 특징

     l  관련 데이터를 동일한 컬렉션에 저장

       - 업데이트 시 해당 데이터가 들어있는 모든 컬렉션을 동시에 업데이트 해야함

       - 자주 변경되지 않는 데이터일 때 유용함

     l  Join 개념 없음

       - 컬렉션을 통해 데이터를 복제하여 각 컬렉션 일부분에 속하는 데이터를 정확히 산출

 

 

 

 

 

정리를 해보자면 아래와 같습니다.

 

 

 

 

<< SQL vs. NoSQL 비교 >>

	SQL	NoSQL
장점	엄격한 스키마가 있어 데이터 중복 없음 (무결성)	스키마가 없어 유연함 Join이 없어 속도 빠름
단점	Strict함 Join문으로 인한 복잡한 쿼리문 수직적 확장만 가능하여 처리량에 한계	업데이트 시 컬렉션에 중복되는 모든 문서가 동시에 업데이트 되어야 함
적재 적소	데이터가 자주 변경되는 경우 명확한 스키마가 중요한 경우	읽기처리를 자주하지만 데이터가 자주 변경되지 않을 때 막대한 양의 데이터를 처리해야 할 때
사례	페이스북 타임라인, 트위터, 텀블러(제한적으로 NoSQL 사용), Pinterest, Instagram	구글 Datastore 서비스, 넷플릭스
시사점	오랜 역사를 지녀 안정적 서비스 제공 가능 아직까지는 대부분의 업체는 RDMS 사용	서비스 구현 시 반드시 필요한 transaction과 indexing문제 해결 필요

                 *출처: 2019대한의료정보학회 ‘CDM 기반 관계형 데이터베이스’ & https://siyoon210.tistory.com/130

 

 

 

 

OMOP-CDM 역시 관계형 데이터 모델을 따르고 있다고 할 수 있겠는데요, 

 

https://github.com/OHDSI/CommonDataModel

 

위 주소로 가시면 OMOP-CDM 6.0 버전에 대한 관계형 데이터를 구축할 수 있는 모든 자료들이 공유가 되어 있습니다.

 

Clone or download를 클릭하여 Download ZIP을 클릭하여 다운받은 후 압축파일을 풀어주면 됩니다.

 

 

 

특히 Sql Server와 OMOP_CDM_v6.0.pdf 내용을 참고하였습니다.!

 

 

 

 

Sql Server 폴더에는 OMOP-CDM 6.0 버전 구축을 위한 모든 SQL문이 총 집합 되어 있습니다.

 

 

 

OMOP CDM sql server ddl.txt -> 테이블 생성을 위한 SQL 전체

OMOP CDM sql server pk indexes.txt -> 프라이머리 키 및 인덱스 생성을 위한 SQL 전체

OMOP CDM sql server constraints.txt -> 포린키 생성을 위한 SQL 전체

 

 

 

씨앤텍시스템즈에서는 CDM 5.3 버전 구축 시 mySQL(MariaDB)를 활용하였습니다.

 

 

 

---

 

 

 

5.3 버전 구축 시 순서는

  1) Create Database 

  2) OMOP CDM sql server ddl.txt 파일을 통해 테이블 생성

  3) 5.3버전 샘플데이터 적재

  4) OMOP CDM sql server pk indexes.txt 파일을 통해 pk 설정

  5) OMOP CDM sql server constraints.txt 파일을 통해 fk 설정

 

 

 

pk와 fk를 먼저 설정해두면 샘플데이터 넣을 때 문제가 발생할수도 있으므로 샘플데이터를 먼저 적재한 뒤 pk/fk를 설정하였습니다.

 

 

 

 

참고로 OHDSI에서 제공하는 샘플데이터는 구글 드라이브를 통해 제공되며 OHDSI측으로부터

허가요청-허가승인 이메일을 받아야 다운로드가 가능합니다.

 

 

 

---

 

 

지금부터 위와 같이 5.3버전을 구축하면서 발생한 문제점과 해결방법을 공유하도록 하겠습니다. 

 

1. concept_synonym 테이블

 

*문제: concept_synonym_name은 대소문자를 구분하지 못하여 중복 데이터로 인식함

 

 

cocnept_synonym 테이블에서 concept_id, concept_synonym_name, language_concept_id 

3개가 동시에 유니크키를 형성하는데 유니크키가 되려면 어느 하나라도 겹치는 데이터가 있으면 안됩니다.

 

 

가령 

72403-Haemarthrosis of the ankle and/or foot-4180186,

72403-Haemarthrosis of the ankle AND/OR foot-4180186,

 

 

위 두 데이터는 가운데 있는 synonym_name에서 and/or가 대소문자를 가린다면 다른 2개의 데이터이지만

대소문자를 구분하지 못한다면 중복 데이터로 인식하게 될 것입니다.

(mysql은 기본적으로 대소문자를 구분하지 못함)

 

 

*해결: 애초에 테이블 생성 시 대소문자를 구분하는 데이터 타입으로 변경해주었습니다.

varchar () = 대소문자 구분 안함 
varbinary () = 대소문자 구분 함

 

 

*유니크키 생성:

ALTER TABLE concept_synonym ADD CONSTRAINT uq_concept_synonym 

UNIQUE (concept_id, concept_synonym_name, language_concept_id);

 

 

 

 

 

 

2. provider, care_site, visit_occurrence,

payer_plan_period, concept_synonym 테이블

 

 

 

*문제: 위 테이블을 생성하고 fk를 설정 시 다음과 같은 에러 메시지를 확인할 수 있었습니다.

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails 

 

 

 

즉 부모 테이블에 존재하지 않는 데이터가 자식 테이블에 존재할 때 생기는 에러 메시지 입니다.

 

 

 

*검토: not exist를 활용한 쿼리문으로 이를 확인하여 보았습니다.

 

 

select column-name from child-table 

where not exists 

(select column-name from parent-table where child-table-column = parent-table-column);

  -> parent_table에 존재하지 않는 child-table column을 보여주어라

 

 

예) select o.provider_id from observation o

     where not exists (select p.provider_id from provider p where o.provider_id = p.provider_id);

 

 

 

 

예를 들어 provider 테이블에 프라이머리키인 provider_id는 숫자 1부터 시작하며

provider_id가 0인 row가 존재하지 않았습니다.

 

 

 

그러나 not exists 쿼리문을 날렸을 때 provider를 부모로 두는 자식 테이블인 observation 테이블에는

provider_id가 0인 row가 확인되었습니다. 

 

 

 

자식 테이블에서 provider_id가 0인 레코드가 다수 발견되었으므로 0인 데이터를 삭제하기 보다는 

부모 테이블에 provider_id가 0인 레코드를 넣어주는 것이 훨씬 간편하므로 insert 시켜주었습니다.

 

 

 

*해결: 

insert into provider (provider_id, provider_name, NPI, DEA, specialty_concept_id, care_site_id, year_of_birth, 

gender_concept_id, provider_source_value, specialty_source_value, specialty_source_concept_id, gender_source_value, gender_source_concept_id) 

VALUES (0, NULL, '3139083563', NULL, 0, 1, NULL, 0, '3139083563', NULL, 0, NULL, 0);

insert into care_site (care_site_id, care_site_name, place_of_service_concept_id, location_id, care_site_source_value, 

place_of_service_source_value) 

VALUES (0, NULL, 8717, NULL, '673314267', 'Inpatient Facility')

insert into visit_occurrence (visit_occurrence_id, person_id, visit_concept_id, visit_start_date, visit_start_datetime, visit_end_date, visit_end_datetime, visit_type_concept_id, provider_id, care_site_id, visit_source_value, visit_source_concept_id, admitting_source_concept_id, admitting_source_value, discharge_to_concept_id, discharge_to_source_value, preceding_visit_occurrence_id) 

VALUES (0, 1, 9201, '2010-03-13', NULL, '2010-03-13', NULL, 44818517, 0, 0, '196661176988406', 0, 0, '', 0, '', 0);

insert into payer_plan_period (payer_plan_period_id, person_id, payer_plan_period_start_date, 

payer_plan_period_end_date, payer_concept_id, payer_source_value, payer_source_concept_id, plan_concept_id, 

plan_source_value, plan_source_concept_id, sponsor_concept_id, sponsor_source_value, sponsor_source_concept_id, 

family_source_value, stop_reason_concept_id, stop_reason_source_value, stop_reason_source_concept_id) 

VALUES (0, 1, '2007-01-01', '2007-01-2', NULL, 'Medicare Part A', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

 

 

 

*포린키 생성:

ALTER TABLE visit_occurrence ADD CONSTRAINT fpk_visit_occur_provider

FOREIGN KEY (provider_id) REFERENCES provider (provider_id); 

ALTER TABLE visit_occurrence ADD CONSTRAINT fpk_visit_provider

FOREIGN KEY (provider_id) REFERENCES provider (provider_id);

 

ALTER TABLE visit_occurrence ADD CONSTRAINT fpk_visit_care_site 

FOREIGN KEY (care_site_id) REFERENCES care_site (care_site_id); 

ALTER TABLE visit_occurrence ADD CONSTRAINT fpk_visit_preceding 

FOREIGN KEY (preceding_visit_occurrence_id) REFERENCES visit_occurrence (visit_occurrence_id); 

ALTER TABLE procedure_occurrence ADD CONSTRAINT fpk_procedure_provider 

FOREIGN KEY (provider_id) REFERENCES provider (provider_id); 

ALTER TABLE drug_exposure ADD CONSTRAINT fpk_drug_provider 

FOREIGN KEY (provider_id) REFERENCES provider (provider_id);

ALTER TABLE drug_exposure ADD CONSTRAINT fpk_drug_visit 

FOREIGN KEY (visit_occurrence_id) REFERENCES visit_occurrence (visit_occurrence_id); 

ALTER TABLE device_exposure ADD CONSTRAINT fpk_device_provider 

FOREIGN KEY (provider_id) REFERENCES provider (provider_id); 

ALTER TABLE measurement ADD CONSTRAINT fpk_measurement_visit FOREIGN KEY (visit_occurrence_id) 

REFERENCES visit_occurrence (visit_occurrence_id); 

ALTER TABLE cost ADD CONSTRAINT fpk_visit_cost_period 

FOREIGN KEY (payer_plan_period_id) REFERENCES payer_plan_period (payer_plan_period_id); 

ALTER TABLE observation ADD CONSTRAINT fpk_observation_provider 

FOREIGN KEY (provider_id) REFERENCES provider (provider_id);

 

 

 

3. measurement, condition_occurrence 테이블

 

*문제: 2번과 동일한 에러인데 이 경우에는 자식 테이블에는 있지만 부모 테이블에 없는 경우가 1개가 아니라 다수

 

여기서 다수는 10개 정도가 아니라 몇백만개 였습니다.;

 

자식 테이블에 존재하는 레코드는 반드시 부모 테이블에도 존재해야 하는 것이 RDB의 룰이기 때문에

 

이 경우에는 샘플데이터 자체가 잘못되었다고 판단하고 해당 데이터를 삭제하는 클렌징 작업을 진행하였습니다.

 

 

*해결:  

 

 

1) 부모-자식 테이블의 최대값 확인

 예: select max(provider_id) from provider; -- 635456

       select max(provider_id) from measurement; --1724144

        -> 1부터 순서대로 넣지 않았을수도 있겠지만 이것만 봐도 대략 없는 데이터가 1백만개 정도일 것으로 추정... 

 

 

2) 샘플데이터 csv 파일을 xlsx 파일로 변환 

  - 다른 이름으로 저장하기 -> 파일형식 -> Excel 통합문서

 

 

3) 변환된 엑셀파일을 열어 1열 전체 선택한 뒤 데이터 - 텍스트 나누기 클릭

팝업창에서 다음 -> 마침 클릭

 

 

텍스트 나누기를 한 뒤 1행이 필터를 적용하여 내림차순으로 정리해줍니다. 

 

그리고 provider_id가 635456~1724144 사이에 있는 데이터를 엑셀 파일 상에서 삭제해 준 뒤

 

다시 csv 파일로 저장하여 import 하였습니다. 

 

delete 쿼리문으로 삭제할 경우 시간이 오래 걸리기 때문에 샘플 데이터 자체에 잘못된 데이터를 삭제해 주었습니다. 

 

텍스트 나누기 한 모습

 

 

*포린키 생성:

ALTER TABLE measurement ADD CONSTRAINT fpk_measurement_provider 

FOREIGN KEY (provider_id) REFERENCES provider (provider_id);

 

ALTER TABLE condition_occurrence ADD CONSTRAINT fpk_condition_provider

FOREIGN KEY (provider_id) REFERENCES provider (provider_id); 

ALTER TABLE condition_occurrence ADD CONSTRAINT fpk_condition_concept_s 

FOREIGN KEY (condition_source_concept_id) REFERENCES concept (concept_id);

 

 

 

 

4. visit_occurrence 테이블

 

*문제: visit_occurrence_id의 데이터 타입은 integer (숫자) 이지만 샘플데이터에는 문자+숫자인 데이터가 포함되어 있어 데이터 일관성 원칙을 지키고 있지 않음

 

 

*해결: Toad와 같은 프로그램을 활용해서 데이터 적재 시 자동으로 걸러주지만 

sql문으로 import하는 경우 3번과 같이 샘플데이터 클렌징 후 다시 적재하는 방법으로 해결합니다.

 

 

 

 

5. Standardized Vocabularies 영역

 

*1:1 관계임을 확인 필요

 

 

 

예를들어 concept과 domain 테이블 관계의 경우

domain의 domain_concept_id는 concept의 concept_id를 참조하는 포린키 입니다.

 

 

 

그런데 만약 domain_concept_id 모두 concept의 concept_id에 포함되어 있다면

domain이 concept에 완벽히 포함되는 (종속) 관계가 될 것입니다. 

 

 

 

이를 확인하기 위해서 right join을 사용해 보았습니다.

 

 

 

select count(c.concept_id) from concept c

right join domain d on d.domain_concept_id = c.concept_id

where d.domain_concept_id is null;

 

 

 

오른쪽 두번째 그림에서 B 부분이 0으로 나온다면 (위 쿼리문 결과값이 0이라면)

 

B는 A에 포함되는 다음과 같은 관계가 될 것입니다. 

 

결과는???

 

0입니다.

 

concept과 이러한 관계를 맺는 테이블은

 

domain,

vocabulary,

concept_class,

concept_relationship,

relationship,

concept_synonym,

concept_ancestor,

source_to_concept_map,

drug_strength

 

 입니다.

 

 

 

 

 

 

따라서 관계선 역시 3발 모양이 아닌 1개발을 갖고 있는 선으로 변경해주었습니다.

 

 

 

 

 

 

---

 

 

 

 

---

 

이상으로 관계형 데이터 모델을 따르고 있는 OMOP-CDM 5.3 버전 구축함으로써

 

관계형 데이터 베이스(RDB)에서 나타나는 특징들을 알아봄과 동시에

 

RDB 룰을 따름으로써 OMOP-CDM 5.3 버전 구축 시 발생하는 이슈들과 해결방법에 대해서 알아보았습니다.

 

---

 

 

 

감사합니다.