개발을 하다 보면 이상과 현실이 다른 경우를 많이 보게 됩니다. 특히 내가 원해서 스스로 개발을 하는 경우가 아닌, 남이 시켜서 하는 경우에는 더더욱 그러합니다. 이번에는 정말 이 상황에 딱 들어맞는 작업을 하게 돼서 포스팅으로 남겨두고자 합니다.
무엇을 해야 하는가?
제가 해야 되는 작업은 듣기에는 참 간단한 작업입니다. 어떤 표 형식의 데이터가 있습니다. 이 데이터를 DB에 올리기만 하면 됩니다. 이 DB 역시 아주 특수한 DB가 아니라 단순한 MySQL데이터베이스입니다.
그러면 무엇이 문제인가?
그런데...
- 일부 csv 형식 데이터는 전부 EUC-KR 인코딩으로 되어 있습니다.
- 파일이름은 전부 한글, 띄어쓰기와 특수문자(별표 등)가 들어가있습니다.
- 이것들은 전부 압축파일형태로 전송되었는데 확장자는 .egg였습니다.
- 이건 다행히 재요청해서 .zip형식으로 된 데이터를 받을 수 있었습니다.
- 어떤 경우 데이터의 절반정도는 NULL값입니다.
- 물론 NULL값을 표시하는 방법은 데이터마다 다 다릅니다.
- 어떤 경우에는 그냥 빈 문자열이고, 어떤 경우에는 특수한 문자열을 사용합니다.
- 당연하게도 ID는 고유값이 아니라서 UNIQUE constraint를 사용할 수가 없습니다.
- 그렇다고 ID 말고 다른 unique한 컬럼이 있는 것도 아닙니다.
- 한두 가지 데이터의 경우 ID가 숫자이지만 나머지 경우에는 문자열입니다.
- 컬럼 값이 어떤 경우에는 숫자고, 어떤 경우에는 문자열입니다.
- 실제로 어떤지는 직접 검사해 봐야 알 수 있습니다.
- 문자열일 경우에는
'나"가 들어가있는 경우도 있습니다.
- 엑셀 파일에는 한 파일에 시트가 적어도 네 개는 들어있습니다.
- 모든 시트의 형식은 서로 다릅니다.
- 맨 위 header부분에는 두 개 이상의 컬럼을 묶어서 공통 제목을 붙여놓은 경우가 대부분입니다.
- 물론 row들도 여러 개를 묶어서 왼쪽에 공통 row를 붙여놓았습니다.
- 어떤 시트는 단순히 출력 시 표지를 위한, 아무 내용이 없고 제목만 써 있는 시트입니다.
- 어떤 시트는 다른 시트의 내용 중 일부를 뽑아놓은 요약 정리 시트입니다.
- 어떤 시트에는 엑셀 수식이 들어가있습니다.
- 당연하게도 정규화는 되어있지 않습니다.
- 심지어 1NF도 되어 있지 않아서, 한 컬럼에 값이 여러 개 들어갑니다.
- 그중 일부는 각 값으로 검색이 가능해야 합니다.
- 어떤 파일은 확장자가 hwp입니다. (한컴)한글파일의 표 형식으로 데이터가 들어있습니다.
- 어떤 파일은 pdf입니다. 한글파일을 pdf로 그대로 뽑은 것입니다.
- 심지어 표 그림이 그려진 이미지파일도 있습니다.
- 정확히 똑같은 데이터가 서로 다른 형식으로, 서로 다른 이름으로 존재합니다.
- 같은 대상을 가리키는 데이터인데 표기 방법이 다릅니다.
- 심지어 대부분 똑같은데 일부 데이터만 다릅니다.
- 규칙은 없습니다.
- 일부 데이터는 랜덤하게 컬럼 한두 개가 빠져있습니다. 그래서 그 다음 컬럼이 전부 왼쪽으로 밀립니다.
- 데이터 1000 개 중 한두 개 그런 row가 있었습니다.
- 이건 다행히 정말로 한두 개밖에 없어서 그냥 손으로 수정할 수 있었습니다. (아마 데이터파일 두세 개를 복사-붙여넣기로 하나로 합치려다가 실수한 것 같습니다.)
그 외에도 디테일한 문제가 여럿 있지만 구체적인 데이터와 등을 블로그와 같은 공개된 곳에 밝힐 수가 없는 상황이어서 이정도만 정리하도록 하겠습니다.
그래서 어떻게 해결했는가?
이미지/pdf/한글 파일로 되어 있는 자료들은 아직 해결을 못 했습니다. 한글파일정도는 어떻게 표를 그대로 엑셀에 복붙하면 될 것도 같기는 합니다만, 문제는 제가 mac을 쓰고 있어 한글 파일 자체를 열기가 참 번거롭다는 점입니다. 그래서 이건 일단 미뤄두고 있습니다.
나머지 문제는 사실 파일 형식은 일관적으로 csv/xlsx이지만 그 내용의 형식이 비일관적인 문제라서 어떻게든 해결할 수 있을 걸로 보였습니다.
한참 고민한 끝에 - 진짜로 한 이틀 넘게 고민했습니다 - 먼저 데이터의 형식을 정의하는 아래와 같은 간단한 언어를 만들었습니다. 이렇게 함으로써 데이터의 이름, 형식, 설명 등이 컴퓨터에서 처리하기 용이한 형태로 규격화됩니다.
Data Name
---
AttributeName Type Description
...
---
이 언어를 사용하면 예를 들어 이름, 학번, 학과를 가지는 학생이라는 형식을 아래와 같이 표현할 수 있습니다.
student
---
name str 이름
number int 학번
department str 학과
---
Python에서는 여러 줄 문자열을 지원합니다. 그래서 저는 이걸 단순히 Python 파일에 문자열로 넣었습니다. 그리고 적당한 파서를 작성하여 이 언어로 작성된 형식을 파이썬 객체로 컴파일하였습니다. 위 형식을 파이썬 객체로 컴파일하면 아래와 같이 변환됩니다.
from config import Schema, Column
student = Schema(
columns = [
Column("name",'str','이름'),
Column("number",'int','학번'),
Column("department",'str','학과'),
]
)
이때 config 라이브러리 및 Schema, Column 클래스는 제가 작성한 클래스입니다.
위 문법은 보는 바와 같이 매우 간단하여 별도의 컴파일러-컴파일러 등을 사용하지 않고 그냥 파이썬의 기본 문자열 처리 함수만을 이용하여 구현했습니다.
이후에는 Schema 클래스와 Column클래스를 조작함으로써 다양한 작업을 수행하였습니다.
예를 들어
- 데이터를 보기 좋게 formatting하여 출력
- Schema에 해당하는 데이터베이스의 Create, Insert (IGNORE) SQL을 자동으로 생성
- 빈 값을 자동으로 SQL에서 사용할 수 있는 NULL로 치환
- 데이터 오류 검사
등이 있습니다.
그리고 특히 DB에 저장하기는 해야겠는데 굳이 그 값으로 검색할 일은 없는 속성들이 있습니다. 그런 속성들의 경우 속성 이름을 하이픈(-)으로 줄 경우 그 속성들을 자동으로 하나의 배열로 묶은 후 JSON 문자열 형식으로 저장하도록 구성했습니다. 이러면 DB구조가 훨씬 간단해져서 관리도 편리할 뿐만 아니라, 수십 개나 되는 불필요한 속성들의 이름과 타입을 정하려고 고생하지 않아도 되어서 좋습니다.
결과적으로, 이렇게 함으로써 워크플로우가 훨씬 간단해졌습니다. 원래대로라면 각 파일을 읽어서 DB에 작성하는 파서를 수십 개나 작성했어야 합니다. 이는 사실 개발이라기보다 막노동에 가깝습니다. 그러나 약간의 공을 들여서 이런 파서를 구성함으로써 다음과 같은 편안한 워크플로우를 구성할 수 있게 되었습니다.
- 위 문법을 통해 데이터 형식 정의
- 컴파일하여 파이썬 스크립트 자동 생성
- 파이썬 스크립트를 실행하여 자동으로 데이터베이스 초기화 및 새로운 테이블 생성
- 파일을 row단위로 읽은 후(이것 역시 자동화되어있음) 약간의 예외처리를 수행
- 예외처리를 해준 row로부터 자동으로 SQL 생성
- DB에서 실행해주면 완료
특히 SQL을 생성할 때 효율적인 처리를 위하여 여러 ROW를 하나의 쿼리에서 처리하도록 구성했습니다. 이렇게 하면 데이터베이스와 통신하는 비용이 상대적으로 줄어들어 속도가 빨라집니다. 여기에 추가로 약간의 데이터베이스 튜닝을 수행했고, 처리 속도를 초당 10만 개까지 끌어올렸습니다.
위 프로세스 중 볼드체로 표기된 1번과 4번만 데이터 형식마다 한 번씩 해 주어야 하는 작업이고, 나머지는 공통된 자동화 스크립트로 처리됩니다.
이러한 방법을 통하여 데이터 타입에 종속적인 파일을 최소화하고, 공통 코드를 이용하여 작업을 효율적으로 처리할 수 있었습니다. 지금은 아직 작업이 진행 중이고, 그 내용을 공개해도 되는지 알 수 없어 코드를 여기 올릴 수는 없습니다. 그러나 추후 공개해도 된다는 허락을 받으면 소스 코드도 한번 정리해서 올리려고 합니다.
결론
- 파이프라인을 구축하는 게 손이 많이 가기는 하지만, 한번 잘 구축해놓으면 그 다음부터는 손을 하나도 안 대고 작업이 가능합니다.
- 잘만 해놓으면 속도를 비약적으로 향상시킬 수 있습니다.
- IT를 하는 사람이라면 좋은 회사, IT에 대한 이해도가 높은 회사에 취직해야 하는 이유를 알 수 있었습니다.