Published on

엑셀 한 장이 스키마였다 — 2단 병합 헤더 리더와 4패스 로더

Authors
  • avatar
    Name
    Hyo814
    Twitter

엑셀 한 장이 스키마였다 — 2단 병합 헤더 리더와 4패스 로더

메타데이터 표준 용어가 엑셀로 관리됩니다. 네임스페이스, 클래스, 속성, 데이터 유형 — 이걸 DB에 넣는 관리 명령이 엑셀 파일 3개에 로더 3개로 흩어져 있었어요.

meta-namespace.xlsx  →  load_namespace
meta-class.xlsx      →  load_meta_class
meta-property.xlsx   →  load_property

표준 담당자가 관리하는 원본이 통합 워크북 한 개(Metadata_Terms.xlsx)로 바뀌면서 이 구조가 무너졌습니다. 시트가 34개, 그 안에 네임스페이스·클래스·속성·데이터 유형이 섞여 있어요.

로더 3개를 하나로 합치는 작업이었는데, 막상 들어가니 엑셀을 읽는 것부터가 문제였습니다.

1. pandas가 첫 줄에서 죽는다

기존 로더는 프로젝트 공용 Xls2Data(pandas 래퍼)를 썼습니다. 새 워크북에 물리자마자 예외가 났어요. 원본 셀을 그대로 찍어보면 이유가 보입니다.

sheet = openpyxl.load_workbook("Metadata_Terms.xlsx")["dct - Property"]
for i, row in enumerate(sheet.iter_rows(max_row=5, max_col=8, values_only=True), 1):
    print(i, row)

1 ('DCMI Metadata Terms - Property', None, None, None, None, None, None, None)
2 (None, None, None, None, None, None, None, None)
3 ('No.', 'Term Name', 'Namespace', 'URI', 'Label', None, 'Definition', None)
4 (None,  None,        None,        None,  'English', 'Korean', 'English', 'Korean')
5 (1, 'abstract', 'dct', 'http://purl.org/dc/terms/abstract', 'Abstract', '초록', ...)
  • 1행은 사람이 보라고 쓴 제목입니다.
  • 2행은 빈 줄.
  • 3·4행이 2단 병합 헤더예요. Label이 English/Korean 두 칸에 걸쳐 있고, 병합 구간에서는 첫 셀에만 값이 있습니다.
  • 데이터는 5행부터.

pd.read_excel()은 기본으로 0행을 헤더로 가정합니다. 그러면 헤더가 ['DCMI Metadata Terms - Property', None, None, None, ...]이 되고, None이 여러 개라 컬럼명이 중복돼 pandas가 예외를 뱉어요. header=[2,3]로 멀티인덱스를 시켜봐도 병합 때문에 상위 헤더가 Unnamed: 5_level_0 같은 걸로 채워져서, 결국 컬럼명을 손으로 다시 조립해야 합니다.

방법얻는 것포기하는 것판단
① Xls2Data를 고쳐서 헤더 행을 인자로 받게리더 하나로 통일load_xls가 다른 엑셀을 읽는 데 그대로 쓰고 있음. 공용 코드 수정이라 리스크가 이 작업 밖으로 나감기각
② pd.read_excel(header=[2,3]) + 컬럼 후처리pandas 유지병합 보정 코드가 결국 필요하고, Unnamed: 규칙에 의존기각
③ 표준 담당자에게 헤더를 1단으로 바꿔 달라고 요청코드가 제일 단순원본은 사람이 보는 문서. 2단 헤더는 가독성 때문에 있는 것이고, 우리 사정으로 바꿔 달라 할 근거가 약함기각
④ 이 워크북 전용 리더를 openpyxl로 따로공용 코드 무손상, 병합 규칙을 명시적으로리더가 하나 더 생김채택

④를 고른 결정적 이유는 ①의 부작용입니다. Xls2Data는 이 작업과 무관한 경로가 이미 쓰고 있었고, 거기까지 검증할 생각이 없으면 건드리면 안 되는 코드였어요. 전용 리더는 113줄로 끝났고, 터지면 이 워크북만 터집니다.

2. 병합 헤더 평탄화 — 상위를 오른쪽으로 이어 붙인다

핵심은 3·4행을 합쳐 Label(English) 같은 평탄한 이름을 만드는 것입니다.

class TermsWorkbook:
    HEADER_TOP_ROW = 3
    HEADER_SUB_ROW = 4
    DATA_START_ROW = 5

    def header(self, sheet_name):
        """3·4행을 합쳐 'Label(English)' 형태의 평탄한 컬럼명 목록을 만든다.

        상위 헤더는 병합 구간에서 첫 셀에만 값이 있으므로 오른쪽으로 이어 붙인다.
        """
        top, sub = list(self.workbook[sheet_name].iter_rows(
            min_row=self.HEADER_TOP_ROW, max_row=self.HEADER_SUB_ROW, values_only=True))

        columns, current = [], None
        for index in range(len(top)):
            value = top[index]
            if value is not None and str(value).strip():
                current = str(value).strip()          # 병합 시작 셀에서 갱신
            child = sub[index] if index < len(sub) else None
            if child is not None and str(child).strip() and current:
                name = f"{current}({str(child).strip()})"
            else:
                name = current                        # 하위 헤더가 없으면 상위만
            columns.append(self.COLUMN_ALIASES.get(name, name))
        return columns

current를 들고 오른쪽으로 흘리는 게 병합의 전부입니다. openpyxl은 read_only=True면 병합 정보를 안 주는데, 어차피 "값이 있으면 갱신, 없으면 유지"로 같은 결과가 나와서 병합 좌표를 읽을 필요가 없었어요.

실제로 나온 컬럼명은 이렇습니다.

['No.', 'Term Name', 'Namespace', 'URI', 'Label(English)', 'Label(Korean)',
 'Definition(English)', 'Definition(Korean)', 'Range Includes/Has Range',
 'Subproperty Of', 'Subclass Of', 'Instance Of', 'Type of Term',
 'Domain Includes/Has Domain']

시트 하나만 헤더가 다른 문제

kadif - Property 시트만 Domain/Has Domain이라는 축약형을 씁니다. 나머지는 전부 Domain Includes/Has Domain이에요.

COLUMN_ALIASES = {"Domain/Has Domain": "Domain Includes/Has Domain"}

원본 엑셀을 고치는 대신 리더에서 흡수했습니다. 원본은 다음 개정 때 표준 담당자가 다시 내려주는 파일이라, 우리가 고쳐도 되돌아옵니다. 코드에 별칭 한 줄을 남기는 쪽이 왕복 없이 끝나요.

3. 왜 4패스인가

용어 시트 32개에는 클래스도 있고 속성도 있고 데이터 유형도 있습니다. 문제는 속성의 Range가 클래스나 데이터 유형을 가리킨다는 것이었어요.

dct:title    Range → rdfs:Literal   (데이터 유형)
dcat:theme   Range → skos:Concept   (클래스)

즉 속성을 적재하려면 클래스와 데이터 유형이 이미 DB에 있어야 합니다. 그런데 시트 순서는 adms - Class, dcat - Class, ..., adms - Property, ..., rdfs - Datatype, xsd - Datatype 순이에요. 데이터 유형 시트가 속성 시트보다 뒤에 있습니다.

방법얻는 것포기하는 것판단
① 시트 순서를 규약으로 정하고 그대로 읽기코드 제일 단순엑셀 편집자가 시트를 끌어다 옮기면 조용히 깨짐. 원본이 우리 소유가 아님기각
② 한 번 읽고, 못 푼 range는 2차 패스에서 재시도패스 2개어떤 게 남을지 몰라 미해결 큐 관리가 붙음. 순환이면 안 끝남기각
③ range를 만날 때마다 대상 용어를 재귀로 먼저 적재순서 무관재귀 깊이·순환 가드 필요. prov는 실제로 상호 참조가 있음기각
④ 전 시트를 메모리로 읽고 유형별로 4패스순서 완전 무관, 흐름이 한눈에전 행을 메모리에 올림(853행이라 무의미한 비용)채택
def load(self, filepath):
    workbook = TermsWorkbook(filepath)
    self.load_namespaces(workbook)                        # pass 0
    if not self.namespace_ids:
        raise CommandError("Namespace sheet produced no rows")

    buckets = self.collect_terms(workbook)                # 전 시트 1회 읽기
    self.load_classes(buckets.pop(self.TYPE_CLASS, []))       # pass 1
    self.load_datatypes(buckets.pop(self.TYPE_DATATYPE, []))  # pass 2
    self.load_properties(buckets.pop(self.TYPE_PROPERTY, [])) # pass 3

순서를 파일 순서가 아니라 Type of Term 컬럼으로 잡은 게 요점입니다. 시트를 어떻게 옮기든 이 값은 안 바뀌어요.

순서 의존을 없앨 때, 입력의 순서를 규약으로 만들지 말고 입력 안에 이미 있는 값으로 순서를 다시 세울 수 있는지를 먼저 봅니다. 여기선 Type of Term이 그 값이었습니다.

메모리 걱정은 숫자를 보고 접었습니다.

총 853행 (class 184 / property 625 / datatype 35 / VEC 9)

853행이면 dict로 다 올려도 몇 MB입니다. 스트리밍 설계를 고민할 규모가 아니었어요.

4. 같은 용어가 두 시트에 있다

읽고 나서 알았는데, 중복 키가 6건 나왔습니다.

('class', 'skos', 'Concept')        -> ['dcat - Class', 'skos - Class']
('class', 'skos', 'ConceptScheme')  -> ['dcat - Class', 'skos - Class']
('class', 'dct',  'Location')       -> ['dcat - Class', 'dct - Class']
('class', 'foaf', 'Organization')   -> ['dcat - Class', 'foaf - Class']
('class', 'dct',  'PeriodOfTime')   -> ['dcat - Class', 'dct - Class']
('class', 'foaf', 'Person')         -> ['dcat - Class', 'foaf - Class']

전부 dcat - Class 시트가 자기가 참조하는 남의 네임스페이스 용어를 편의상 같이 실어놓은 것이었습니다. 표준 문서로서는 자연스러운데, 적재할 땐 "어느 행이 정본인가"를 정해야 해요.

row["is_owner"] = row["namespace"] == sheet_prefix   # 'skos' 행이 'skos - Class'에 있으면 소유

key = (row["type_of_term"], row["namespace"], row["name"])
previous = chosen.get(key)
if previous is None:
    chosen[key] = row
elif row["is_owner"] and not previous["is_owner"]:
    chosen[key] = row                                # 소유 시트 행이 이긴다

소유 네임스페이스 시트의 행을 정본으로 삼습니다. 시트 순서가 바뀌어도 승자가 안 바뀌게 하려고 만든 규칙이에요.

이 규칙에는 구멍이 있습니다

지금 코드는 is_owner가 갈릴 때만 교체합니다. 둘 다 소유가 아니거나 둘 다 소유면 먼저 읽힌 시트가 이겨요. 그러면 시트 순서 의존이 그대로 남습니다 — 이 4패스 구조가 없애려던 바로 그것이요.

지금 데이터에서는 6건 전부 dcat(비소유) 대 소유 시트라 문제가 안 됩니다. 하지만 "순서 의존을 없앴다"고 적어놓고 승자 규칙 한 갈래에 순서 의존이 남아 있는 건 자기모순이에요. 표준 개정으로 비소유 시트 두 개가 같은 용어를 싣는 순간 드러납니다.

정직하게 말하면 지금 이 코드는 "순서 의존을 없앴다"가 아니라 "현재 데이터에서는 순서에 안 걸린다" 입니다. 고치려면 동률일 때 결정적인 2차 기준(예: 컬럼이 더 많이 찬 행)을 넣거나, 최소한 동률을 로그로 남겨야 해요. 지금은 조용히 넘어갑니다.

5. range를 데이터 유형부터 보는 이유

속성의 range는 클래스 참조와 데이터 유형 참조 중 하나만 될 수 있습니다(모델에서 상호배타). 그래서 토큰 하나를 어느 쪽으로 볼지 정해야 해요.

def resolve_range(self, row):
    token = row["range"]
    if not token:
        return None, None

    metakeytype = MetaKeyTypeManager.get_object_by_name(token)   # 데이터 유형 먼저
    if metakeytype:
        return None, metakeytype

    range_class = MetaClassManager.get_object_by_name(token)     # 그다음 클래스
    if range_class is None:
        logger.warning("unresolved range=%r for %s:%s", token, row["namespace"], row["name"])
        self.stats["unresolved_range"] += 1
    return range_class, None

순서가 중요한 건 rdfs:Literal 같은 토큰이 양쪽 다로 해석될 수 있어서입니다. rdfs - Class 시트에도 있고 rdfs - Datatype 시트에도 있어요. 데이터 유형을 먼저 보면 이런 토큰이 데이터 유형으로 확정됩니다.

이 순서를 뒤집으면 dct:title의 range가 "Literal이라는 이름의 클래스"가 되고, 화면에는 "클래스 참조 속성" 으로 표시됩니다. 데이터 유형 컬럼은 계속 비어 있고요. 이 오적재를 정정한 얘기는 속성 range를 클래스 참조와 데이터 유형으로 분리한 기록에 따로 적었습니다.

6. --delete는 왜 순서가 정해져 있나

재적재를 위해 --delete를 뒀습니다. 삭제 순서가 코드에 박혀 있어요.

def clean_terms(self):
    """참조 방향의 역순으로 지운다."""
    for model in (MetaClassProperty, MetaClass, MetaKeyType, Namespace):
        _, per_model = model.objects.all().delete()
        for label, count in sorted(per_model.items()):
            logger.info("deleted %s=%s", label, count)

참조하는 쪽부터 지웁니다. 속성이 클래스·데이터 유형을 가리키고, 그것들이 네임스페이스를 가리키니까요.

여기서 한 번 밟았습니다. 적재 스크립트에서 삭제 단계를 init_dataset 뒤에 뒀더니, 방금 만든 기초 데이터까지 같이 날아갔어요.

# 잘못됨: 기초 데이터 만들고 → 지움
python manage.py init_dataset
python manage.py load_meta_terms --delete

# 고침: 지우고 → 기초 데이터 만들고 → 적재
python manage.py load_meta_terms --delete
python manage.py init_dataset
python manage.py load_meta_terms -p ./Metadata_Terms.xlsx

MetaClass 삭제는 DatasetMetaClass·DatasetMetadata·MetaClassInstance로 연쇄됩니다. 재적재로는 안 돌아오는 데이터라, 모델별 삭제 건수를 로그로 남기게 해뒀어요. delete()가 돌려주는 두 번째 값이 그 내역입니다.

deleted std_data.MetaClassProperty=625
deleted std_data.MetaClass=184
...

7. 같은 파일 안에서 upsert 전략이 갈려 있다

클래스·데이터 유형은 이렇게 씁니다.

def upsert_by_namespace_name(self, model, row, defaults):
    """update_or_create 대신 first()+setattr — 기존 중복 행에서 터지지 않게."""
    instance = (model.objects
                .filter(namespace_id=defaults["namespace_id"], name=row["name"])
                .order_by("id").first())
    if instance:
        for field, value in defaults.items():
            setattr(instance, field, value)
        instance.save(update_fields=list(defaults.keys()))
        return False
    model.objects.create(name=row["name"], **defaults)
    return True

그런데 속성만 update_or_create를 씁니다.

_, created = MetaClassProperty.objects.update_or_create(
    namespace_id=namespace_id, name=row["name"], defaults=defaults
)

MetaClassProperty에는 (namespace, name) 유니크 제약이 없습니다.

class Meta:
    db_table = "dataset_metaclass_property"
    indexes = [models.Index(fields=["name"])]     # 인덱스만, unique 아님

즉 레거시 데이터에 같은 (namespace, name) 속성이 둘 이상 있으면 MultipleObjectsReturned로 터집니다. 바로 위에서 "기존 중복 행에서 터지지 않게" 하려고 헬퍼를 만들어놓고, 정작 행 수가 가장 많은(625건) 속성에는 안 쓴 거예요.

지금은 --delete 후 재적재하는 경로가 주력이라 안 터지고 있습니다. 하지만 업데이트 모드(u)로 돌리는 운영 환경에서 중복이 하나라도 생기면 그때 드러납니다. 헬퍼를 속성에도 적용하거나, (namespace, name)에 유니크 제약을 거는 게 맞아요. 후자가 근본이지만 기존 데이터 정리가 선행돼야 해서 아직 못 걸었습니다.

8. 검증

항목결과
워크북 시트34개 중 범례(Attributes) 제외 33개 적재 대상 (Namespace 1 + 용어 시트 32)
읽은 행853행 (class 184 / property 625 / datatype 35 / VEC 9)
중복 키6건, 전부 소유 시트 행 채택
네임스페이스23행
삭제한 코드로더 3종 + 대응 테스트 3종 + 엑셀 3개 = 1,476줄 삭제 / 95줄 추가
테스트test_load_meta_terms·test_map_term_relations 재작성, test_metaclass_property_validation 신설

로더 통합의 실제 이득은 기능이 아니라 삭제한 줄 수였습니다. 같은 엑셀 파싱·정리·업서트 로직이 세 파일에 복제돼 있었고, 셋이 조금씩 달랐어요.

9. 남은 것

  • Vocabulary Encoding Scheme 9건은 적재하지 않습니다. 어느 모델에 넣을지 정하지 못했어요. 경고가 아니라 정보 로그로 남깁니다 — 결정이 안 된 것과 잘못된 것은 로그 레벨을 나눠야 나중에 로그를 읽을 수 있습니다.
  • 중복 승자 규칙의 동률 케이스(4절). 현재 데이터에서만 안전합니다.
  • 속성 업서트의 MultipleObjectsReturned 가능성(7절).
  • prov 네임스페이스는 용어 간 상호 참조가 있어서, 관계 매핑(map_term_relations)에서 순환을 따로 다뤄야 했습니다.
  • MetaValueType 테이블은 여전히 비어 있습니다. 워크북에 대응 시트가 없어요.

정리

  • 공용 파서를 고치기 전에 그걸 누가 쓰고 있는지 센다. 전용 리더 113줄이 공용 코드 수정보다 쌌다.
  • 순서 의존을 없앨 땐 입력 안에 이미 있는 값으로 순서를 다시 세운다. 파일 순서를 규약으로 만들면 남이 깬다.
  • "메모리에 다 올린다"를 겁내기 전에 행 수를 센다. 853행이면 고민 대상이 아니다.
  • 삭제는 참조 역순으로, 그리고 삭제 건수를 남긴다. 연쇄 삭제는 재적재로 안 돌아온다.
  • 그리고, 같은 파일 안에서 전략이 갈려 있으면 대개 나중에 넣은 쪽이 이유 없이 갈린 것이다. 이번엔 내가 그랬다.