[DB] .csv 파일 데이터 모델링과 java로 파일 가공하기

 

공공데이터에서 다운 받은 서울시 병의원 위치 정보.csv 파일을 이용하여 insert문을 만들고 데이터 넣는 과정을 정리하는 글입니다. 

 

 

 

◎ 데이터 모델링의 개념

데이터 모델링은 데이터베이스에서 데이터를 조직하고 구조화하는 과정입니다.

데이터 모델은 데이터의 의미, 관계, 제약을 설명하는 그래프 또는 다이어그램으로 표현됩니다. 데이터 모델링은 데이터베이스 설계, 데이터베이스 최적화, 데이터베이스 보안 등 데이터베이스 관리의 모든 단계에서 중요한 역할을 합니다.

 

 

 

◎ 데이터 모델링의 절차

1. 요구사항 분석 : 어떠한 업무를 시작하기 전에 해당하는 업무에 대해서 파악하는 단계
2. 개념적 설계 : 내가 하고자 하는 일의 데이터 간의 관계를 구상하는 단계
3. 논리적 설계 : 구체화된 업무중심의 데이터 모델을 만들어 내는 단계
4. 물리적 설계 : 최종적으로 데이터를 관리할 데이터 베이스를 선택하고, 선택한 데이터 베이스에 실제 테이블을 만드는 작업을 하는 단계

위의 순서로 데이터 베이스를 설계합니다.

 

 

 

◎ 서울 병의원 데이터 모델링

1.  요구사항 분석

서울시 병의원 데이터 모델링에서는 다음과 같이 요구사항을 정리할 수 있습니다.

1. 병원분류명이 총 몇 가지 인지?
2. 병원분류별로 몇 개씩 있는지?  ex) 의원 : x개 치과병원 : y개 한방병원 : z개 …
3. 병원 분류가 몇 가지인지?
4. 서울의 구별로 각 병원이 몇 개 있는지?  ex) 서울시 강서구 의원, 한방병원, 치과병원, ...
5. 구별로 병원이 가장 많은 구는?
6. 이비인후과(0), 외과(1), 내과(2), 소아과(3), 피부과, 성형외과 는 각 몇 개인지? (category)

 

2. 개념적 설계 

• 사용자의 요구사항을 분석한 후, 데이터베이스에 대한 추상적인 형태를 설계한다. 
• 데이터와 데이터 간의 관계를 ERD(Entity Relationship Diagram)로 표현한다. 

ERD에서 사용하는 기호와 의미는 위와 같다.

 

 

3. 논리적 설계

• 개발에 사용할 DBMS가 처리할 수 있는 데이터베이스의 논리적 구조를 설계를 한다. 
• ERD를 관계 데이터 모델의 릴레이션(테이블) 스키마로 변환한다. 
• key, 속성, 정규화, 관계 등을 작성한다.

 

seoul_hospital TABLE

 

컬럼명	설명	타입	비고
id(PK)		VARCHAR(8)	A1120837
address	전체 주소	VARCHAR(90)
district	서울특별시 00구	VARCHAR(15)	서울특별시 강남구
category	작성병원분류	VARCHAR(1)	A: 종합병원 B: 병원 C: 의원 D: 요양병원 E: 한방병원 G: 한의원 I: 기타 M: 치과병원 N: 치과의원 R: 보건소
emergency_room	응급실운영여부 1: 운영 2: 운영	INT	1 또는 2
name	해당 병원의 이름	VARCHAR(40)	가로수치과의원
subdivision	세부 분과 피부과, 성형외과, 외과, 내과, 소아과, 가정의학과, 치과 등	VARCHAR(10)	피부과 흉부외과 영상의학과

 

 

4. 물리적 설계

1. 테이블 생성하기

CREATE TABLE `likelion-db`.`seoul_hospital` (
  `id` VARCHAR(8) NOT NULL,
  `address` VARCHAR(90) NOT NULL COMMENT '전체 주소',
  `district` VARCHAR(15) NOT NULL COMMENT '서울시 00구',
  `category` VARCHAR(1) NOT NULL COMMENT '병원분류\nC의원\nE한방병원\nN치과의원\n\n',
  `emergency_room` INT NOT NULL COMMENT '응급실 운영여부\n운영 1\n안함 2\n',
  `name` VARCHAR(40) NOT NULL COMMENT '해당 병원명',
  `subdivision` VARCHAR(10) NULL COMMENT '세부 분과\n피부과, 성형외과, 외과, 내과, 소아과, 가정의학과, 치과 등\n',
  PRIMARY KEY (`id`));

seoul_hospital TABLE 생성

 

 

 

SELECT문을 통해서 Column이 생성됐음을 볼 수 있습니다.

 

 

 

 

2. 데이터 INSERT 하기

 

SQL문으로 INSERT 하는 방법

INSERT INTO `likelion-db`.`seoul_hospital`(`id`,`address`,`district`,`category`,`emergency_room`,`name`,`subdivision`)
VALUES('A1120837','서울특별시 금천구 벚꽃로 286 삼성리더스타워 111~114호 (가산동)','서울특별시 금천구','C',2,'가산기대찬의원',null);
SELECT * FROM `likelion-db`.seoul_hospital;

 

 

 

.sql파일로 DB에 INSERT 하는 방법

 

1. .sql 파일을 생성한 후 저장합니다.

2. WorkBench에서 .sql 스크립트 파일을 불러옵니다. (File - Open SQL Script...)

3. 불러온 .sql 스크립트를 실행합니다. (번개모양 클릭)

 

.sql 스크립트

 

 

 

INSERT INTO 반복 없이 작성하는 방법

INSERT INTO를 반복적으로 계속 쓰면 시간 소요가 많이 됩니다.

INSERT INTO `likelion-db`.`seoul_hospital`(`id`,`address`,`district`,`category`,`emergency_room`,`name`,`subdivision`)
VALUES
('B1100027','서울특별시 강남구 도산대로 118 (논현동 신사빌딩 2층)
','서울특별시 강남구','C',2,'365엠씨의원',null),
('A1117873','서울특별시 강남구 도산대로 118 (논현동 신사빌딩 2층)
','서울특별시 강남구','C',2,'365엠씨의원',null);

 

 

 

◎ JAVA로 파일 가공하기

 

파일에서 필요한 정보 가져오기

• Line을 읽고 parsing을 통해 필요한 정보만 가져온다.
• 인터페이스 Parser를 만들어 여러 종류의 Parser를 생성할 수 있다. 

 

 

class LineReader - 파일을 읽어 리스트 배열에 넣는 클래스

public class LineReader {
    List<String> readLines(String filename) throws IOException {
        List<String> result = new ArrayList<>();
        BufferedReader br = new BufferedReader(new FileReader(filename));
        String str;
        while((str = br.readLine()) != null){
            result.add(str);
        }
        br.close();
        return result;
    }

    public static void main(String[] args) throws IOException {
        LineReader lr = new LineReader();
        String filename = "/Users/daon/Downloads/서울시 병의원 위치 정보.csv";
        List<String> strings = lr.readLines(filename);
        System.out.println(strings.size());
    }
}

처음엔 String List를 반환하도록 했습니다. filename을 한 줄씩 읽어와서 List에 추가합니다.

하지만 List<String>뿐만 아니라 List<Hospital> 혹은 List<PopultaionStat>를 추가하고 싶어서 Parser라는 인터페이스를 만들고 구현체인 HospitalParser을 만들어야 합니다.

 

 

 

 

다음으로 parser 인터페이스와 구현체를 작성했습니다.

 

 

interface Parser<T> - 파싱 인터페이스

public interface Parser<T> {
    T parse(String str);
}

 

class HospitalParser - 파싱 구현체

public class HospitalParser implements Parser<Hospital> {
    @Override
    public Hospital parse(String str) {
        String[] splitted = str.split(","); // 병원 정보.csv에서 , 로 데이터를 구분
        return new Hospital(splitted[0]);
    }
}

 

 

 

이제 새로운 Parser를 추가하고 싶다면 구현체를 하나 더 만들면 됩니다.

LineReader를 Generic을 사용하여 수정하였습니다. Generic을 사용한 이유는 Hospital 객체를 반환하기 위함입니다.

 

 

 

class LineReader<T> - 파일을 읽어 여러 타입의 배열로 만들 수 있는 클래스

public class LineReader<T> {
    Parser<T> parser;
    boolean isRemoveColumnName = true;

    public LineReader(Parser<T> parser) {
        this.parser = parser;
    }

    public LineReader(Parser<T> parser, boolean isRemoveColumnName) {
        this.parser = parser;
        this.isRemoveColumnName = isRemoveColumnName;
    }

    public List<T> readLines(String fileName) throws IOException {
        List<T> result = new ArrayList<>();
        BufferedReader br = new BufferedReader(new FileReader(fileName));

        String str;
        
        if (isRemoveColumnName) {
            br.readLine();
        }
        while ( (str = br.readLine()) != null) {
            result.add(parser.parse(str));
        }
        return result;
    }
}

filename을 받아 한 줄씩 읽어오며 Parsing 하는 LineReader 클래스는 제네릭<T>를 사용해서 Hospital 클래스 및 다른 객체를 사용할 수 있습니다.

 

 

 

다음은 Hospital 도메인입니다. 우선 id만 저장해보겠습니다. 생성자와 getter를 만들어줍니다.

 

 

 

class Hospital - 병원 정보를 담는 클래스

public class Hospital {
    private String id;
    
    public Hospital(String id) {
        this.id = id;
    }
    
    public String getId() {
        return id;
    }
}

 

 

 

마지막으로 Main에서 기능을 잘 수행하는지 테스트 해보겠습니다.

 

 

 

class Main - 기능 실행을 위한 클래스

public class Main {
    public static void main(String[] args) throws IOException {

        LineReader<Hospital> hospitalLineReader = new LineReader<>(new HospitalParser());
        String fileName = "/Users/daon/Downloads/서울시 병의원 위치 정보.csv";
        List<Hospital> hospitals = hospitalLineReader.readLines(fileName);

        System.out.println(hospitals.size());
        for (Hospital hospital : hospitals) {
            System.out.println(hospital.getId());
        }
    }
}

 

 

실행 결과

19039
"A1120837"
"A1104130"
"B1100027"
...