TXT(CSV) 파일을 SQL(Sqlite)로 분석하기
Aug 11, 2024
목차
- 소스 데이터 얻기
- 파일 인코딩(to UTF8) 변경하기
- TXT(CSV) 파일을 Sqlite 에 테이블형태로 import 하기
- SQL 로 데이터 조회 및 분석하기
- 소스 데이터 얻기
전국의 모든 건축물을 대상으로 데이터를 분석해봐야 할 일이 생겼다. 예를 들면, 연면적 등으로 필터를 해서 건축물의 수를 집계해보고 싶었다. 우선 데이터를 구해야 한다.
결론부터 말하자면 건축물에 대한 데이터는 국가교통부에서 공공데이터 형태로 제공하고 있다. (https://open.eais.go.kr/)
우리는 전국 모든 건축물에 대한 데이터가 필요하니 메뉴에서 ‘대용량 제공 서비스’ 로 이동한다.
여기에서는 업무구분을 ‘건축물대장’ 으로 검색한다. 우리는 연면적 데이터가 필요하므로 ‘표제부’ 를 다운로드 한다.
- 파일 인코딩(to UTF8) 변경하기
다운로드 하면 .zip 파일 형태로 데이터가 받아진다. 그런데 압축 해제를 해보면 한글이 깨진다는 것을 알 수 있다.
UTF8 이 아닌 CP949 로 되어있어서 발생하는 문제로, 메모장 같은 어플리케이션에서 간단히 변경할 수 있다. (파일 - 저장 - 다른이름으로 저장 → 창에서 인코딩 변경)
그런데 이번 경우처럼 너무 큰 파일 (이 경우는 3GB 이상) 은 메모장으로 열 수 없다. 이럴 때에는 파워쉘을 사용하여 인코딩을 변경하면 된다. 파일이 있는 디렉토리에서 우클릭 - 터미널 열기 후 터미널에 아래와 같은 명령어를 입력한다.
Get-Content .\mart_djy_03.txt | Set-Content .\out.txt -Encoding UTF8그리고 다시 텍스트 파일을 열면 (메모장으로 안열리니 다른 방법으로 열어야 한다.) 한글이 정상화 되어있다.
- TXT(CSV) 파일을 Sqlite 에 테이블형태로 import 하기
SQL을 사용하기 위해서 DBMS 를 설치해 주어야 한다. 만일 선호하는 제품(MySQL, PostgreSQL, MsSQL 등)이 있으면 그것을 설치하면 되는데, sqlite 제품이 설치도 간단하고 혼자서 데이터 분석용으로 사용하기에는 가장 적합한 것 같다.
나는 intellij 를 사용하고 있으므로, 간단히 data source 로 추가해서 사용하면 된다. (https://www.jetbrains.com/help/idea/sqlite.html)
data source 를 추가하고, 파일을 import 하면 된다.
- SQL 로 데이터 조회 및 분석하기
쿼리 속도가 생각보다 느린 것 같긴 하지만, 혼자서 사용하기에는 충분해 보인다. 인코딩 문제로 여기까지 오는데 생각보다 오래 걸려서 그렇지 사실 이 정도 설정은 금방 할 수 있다. 이제 SQL 을 활용해서 데이터를 원하는대로 분석해보자.
하루가 지난 후, 이리저리 쿼리를 해보다가 뭔가 이상함을 발견했다. 분명 연면적(숫자형 이어야 한다) 으로 조건을 만들어서 조회했는데, 조회 결과가 다르게 나왔다. 30분을 삽질한 끝에, 데이터형이 TEXT로 들어간 것을 발견했다. 결론적으로는 처음에 파일 import 를 할 때 데이터형을 정해주어야 한다.
import 할때 이렇게 from 부분을 클릭해보면 데이터 타입을 편집할 수 있다. intellij 가 어느정도는 자동으로 잡아주는 것 같지만, 애매한 컬럼은 전부 TEXT 로 되어있다. 연면적은 정수가 아니고 실수(소수점 포함) 일 수 있으니 데이터 타입을 REAL 로 변경해주면 된다. 그렇게 데이터 타입 변경 후 다시 import 를 완료하면, 아래와 같이 정상적으로 쿼리가 동작함을 알 수 있다.
Share article
