넓고 얕은 데이터베이스 지식/RDBMS

Spring Data JPA + PostgreSQL을 이용하여 JSON 데이터 타입의 컬럼에 JSON 데이터를 저장하기

팡펑퐁 2023. 8. 19. 01:16
728x90
이 글은 이전 글의 내용과 이어집니다.
방법만 알고 싶으신 분들은 아래에 테스트 시작 부분부터 읽으시면 됩니다.

💡 요약 및 변경사항
Spring boot + Spring Data JPA 환경에서 엑셀 데이터를 파싱하고 데이터베이스에 저장해야 한다.

엑셀 데이터에는 여러 단체의 회원 정보가 담겨 있다.
초기 기획에서는 회원 정보에 대한 액셀 형식이 완전히 자유였지만 정확한 엑셀 파싱을 위해 어느 정도 형식을 지정하기로 결정됐다.

1. 첫 번째 행은 헤더이며 공백 없이 두 번째 행부터 데이터가 들어간다.
2. 첫 번째 칼럼은 이름(회원명)이며, 두 번째 칼럼은 전화번호여야 한다.
3. 나머지 칼럼은 앱에서 정의한 칼럼이나 직접 생성한 칼럼으로 구성할 수 있다.(주소, 학교명, 회사명, 직급 등)

예를 들어 A 단체의 경우 (이름, 전화번호, 주소, 나이, 회사명, 직급) 칼럼을 가진다면
B 단체의 경우 (이름, 전화번호, 학교명, 학년, 성적) 칼럼을 가질 수 있다.
따라서, 데이터베이스의 스키마를 따로 정의할 수 없다.

요구사항
단체 관리자는 처음 단체를 등록할 때 데이터베이스에 회원 정보를 저장한다.
대형 단체라면 정해진 엑셀 형식에 맞게 데이터를 넣고 업로드한다.
소규모 단체라면 앱을 통해 원하는 칼럼을 선택하여 회원 명단 정보에 들어갈 내용을 구성을 할 수 있다.
액셀을 업로드를 하든 앱에서 칼럼을 구성하든 모든 회원 정보 데이터의 첫 번째 칼럼은 이름 두 번째 칼럼은 전화번호이다.

이후에 회원이 단체의 소속임을 증명하기 위한 요청을 하면 회원 명단이 저장되어 있는 데이터베이스에서 이름과 전화번호를 가져와 인증 절차를 진행하고 확인이 되면 가입을 승인한다.
모든 단체의 회원 정보에 공통으로 들어가는 이름과 전화번호를 제외한 나머지 데이터는 스키마를 통일할 수 없기 때문에 적절한 방법을 생각해내야 한다.
  • 위의 상황을 고려해 보았을 때 회원 명단의 공통 속성인 이름과 전화번호를 제외한 나머지 데이터를 JSON 형식으로 저장하는 방식이 합리적이라고 생각했다.
    • 회원 검증에 필요한 이름과 전화번호를 별도의 칼럼으로 분리하는 이유는 조회 성능을 살리기 위해서이다.
      • 이 부분만 생각하면 mongoDB보다 rdbms에서의 하이브리드 방식이 더 적합해 보였다.
    • 나머지 데이터를 JSON 형식의 칼럼에 저장하는 이유는 스키마에 구애받지 않고 변경에 용이하게 저장할 수 있기 때문이다.
  • 따라서 일반적인 데이터 조회뿐만 아니라 JSON 데이터 조회 쿼리에서도 쓸만한 성능이 나올 수 있는 데이터베이스가 필요했다.
  • 고민 끝에 Spring Boot + Spring Data JPA와 높은 호환성을 가지면서 JSON 형식의 칼럼과 관련 쿼리 성능을 지원하는 PostgreSQL이 가장 적합하다는 결론을 내렸다.

 

 이번 테스트의 목표는 더미 회원 명단이 들어있는 엑셀 데이터를 파싱 하여 이름, 전화번호를 별도로 분리하고 나머지 칼럼은 JSON 형식으로 묶어 데이터베이스에 저장하는 것이다.

 

 

테스트 시작

더미 회원 데이터

공공 데이터 포털에서 받은 엑셀 데이터를 일부 변형하여 더미 데이터로 활용했다.

  • 더미 회원 엑셀 데이터이다.
  • 위의 첫 번째 칼럼인 이름과 두 번째 칼럼인 전화번호를 제외한 나머지 칼럼은 JSON 형식으로 변환해 저장할 것이다.

 

 

JsonConverter

import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;

import java.util.HashMap;
import java.util.Map;

public class JsonConverter {
    private static final ObjectMapper objectMapper = new ObjectMapper();

    // Map -> json
    public static String jsonConvert(Map<String, Object> map) throws JsonProcessingException {
        return objectMapper.writeValueAsString(map);
    }

    //json -> Map
    public static Map<String, Object> mapConvert(String json) throws JsonProcessingException {
        TypeReference<HashMap<String, Object>> typeRef =
                new TypeReference<HashMap<String, Object>>() {};

        return objectMapper.readValue(json, typeRef);
    }
}
  • Key와 Value로 구성된 Map 데이터를 json 형식으로 양방향 변환하는 컨버터이다.
  • 이 예제에서는 Map -> json 변환 메서드(jsonConvert)만 사용한다.
  • 특별한 내용이 없어 설명을 넣지 않았다.

 

 

build.gradle

// JSONB 데이터 매핑
    implementation group: 'io.hypersistence', name: 'hypersistence-utils-hibernate-55', version: '3.5.0'
  • json 데이터를 Varchar와 같은 문자열 타입이 아닌 별도의 JSON 타입으로 PostgreSQL에 저장하면 내부에서 자동으로 B-tree 인덱싱을 하여 저장하기 때문에 빠른 JSON 데이터 조회가 가능해진다고 한다.
    • PostgreSQL에서는 JSON 타입의 칼럼에 데이터를 저장하면 별도의 인덱스 설정 없이 JSON 데이터가 인덱싱 되는 것이다. 
  • 문제는 Hibernate 5 이하는 JSON 객체 타입을 지원하지 않는다는 것이다.
    • 참고로 Hibernate 6부터는 JSON 타입의 객체를 자바 프로젝트에서 다루는 것을 정식으로 지원한다.
    • 그런데 Hibernate 6은 Spring Boot 3.0 이상부터 지원한다.
  • 나는 Spring boot 2.x 버전을 사용 중이기 때문에 위처럼 의존성을 추가해 외부 라이브러리를 사용하여 데이터베이스에 저장할 때 JSON 타입으로 저장할 수 있게 했다.

 

 

ExcelData Entity

import io.hypersistence.utils.hibernate.type.json.JsonBinaryType;
import lombok.AccessLevel;
import lombok.NoArgsConstructor;
import org.hibernate.annotations.Type;
import org.hibernate.annotations.TypeDef;

import javax.persistence.*;

@Entity
@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class) // Jsonb 타입으로 저장
@NoArgsConstructor(access = AccessLevel.PROTECTED)
public class ExcelData {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    private String phone;

    @Type(type = "jsonb") // Jsonb 타입으로 저장
    @Column(columnDefinition = "jsonb") // Jsonb 타입으로 저장
    private String jsonData;

    private ExcelData(String name, String phone, String jsonData) {
        this.name = name;
        this.phone = phone;
        this.jsonData = jsonData;
    }

    public static ExcelData createEntityOf(String name, String phone, String jsonData) {
        return new ExcelData(name, phone, jsonData);
    }
}
  • 'Jsonb 타입으로 저장'이라는 주석이 있는 애너테이션은 JPA를 이용해 PostgreSQL의 jsonb 타입으로 데이터를 저장할 수 있게 한다.
    • 위의 외부 라이브러리를 추가를 한 후에 가능하다.
    • jsonb란 PostgreSQL에서 json 데이터 타입을 가리키는 타입 이름이다.
  • 위 코드대로 데이터를 저장하게 되면 테이블의 칼럼은 id, name, phone, jsonData로 이뤄지며 jsonData의 타입은 위에 언급한 대로 jsonb 타입이 된다.

 

ExcelDataRepository

import org.springframework.data.jpa.repository.JpaRepository;
import test.excelparser.excel.entity.ExcelData;

public interface ExcelDataRepository extends JpaRepository<ExcelData, Long> {
}
  • JpaRepository를 이용한다.

 

 

저장 테스트

@Test
public void save() throws Exception {
    FileInputStream excelFile = new FileInputStream("/엑셀/데이터/경로/파일명.xlsx");

    ExcelSheetHandler excelSheetHandler = ExcelSheetHandler.readExcel(excelFile); // (1)

    List<String> header = excelSheetHandler.getHeader(); // (2)

    Map<String, Object> jsonData = new HashMap<>(); // (2-1)

    List<List<String>> rowDataList = excelSheetHandler.getRows(); // (3)

    List<ExcelData> excelDataList = new ArrayList<>(); // (4)

    for (List<String> row : rowDataList) { // (5)
        for (int i = 2; i < header.size(); i++) { // 이름(0), 전화번호(1) 제외 모든 데이터를 json으로 변환
            jsonData.put(header.get(i), row.get(i));
        }
        String convertedJson = JsonConverter.jsonConvert(jsonData); // 엑셀 데이터 -> Json 매핑

        ExcelData excelData = ExcelData.createEntityOf(row.get(0), row.get(1), convertedJson); // 이름, 전화번호, jsonData

        excelDataList.add(excelData);
    }

    excelDataRepository.saveAll(excelDataList); // (6)
}
  • 이제 데이터베이스에 저장을 해보자.
  • 간단하게 요약하면 엑셀 데이터 -> 자바에서 엑셀 파싱 -> 파싱 데이터를 엔티티 객체로 매핑 -> json 데이터를 저장하는 필드를 Map -> jsonb 타입으로 변환 -> db에 저장 순이다.
  • (1) ExcelSheetHandler로 엑셀 데이터를 파싱 한다.
    • 이 클래스에 대한 자세한 설명은 이 링크를 클릭하면 확인할 수 있다.
  • (2) excelSheetHandler.getHeader() 메서드는 첫 번째 행인 헤더를 리스트로 가져오는 역할을 한다.
    • 이 메서드를 통해 엑셀 데이터의 맨 처음 row인 헤더를 header 리스트에 넣는다.
    • (2-1) 파싱 한 엑셀 데이터를 자바 객체로 매핑할 jsonData라는 이름의 컬렉션 객체를 생성한다.
  • (3) excelSheetHandler.getRows() 메서드는 헤더를 제외한 두 번째 행부터 마지막 행까지의 요소를 2차원 배열에 저장하여 리턴한다.
  • (4) 엑셀 데이터를 파싱 후 자바 객체로 매핑한 데이터가 들어갈 리스트이다.
  • (5) 각 row를 돌면서 파싱 된 엑셀 데이터를 Map 객체에 매핑한다.
    • 이중 for 문의 안쪽 for 문에서 이름, 전화번호를 제외한 모든 데이터는 Map 객체로 변환되어 저장된다.
      • i가 2부터인 이유는 0은 이름, 1은 전화번호이기 때문이다.
      • Map 객체의 key에는 header가, value에는 해당 key에 맞는 Cell 데이터가 들어간다.
    • 이름과 전화번호, jsonData(Map 객체)는 createEntityOf() 메서드를 작동할 때 파라미터 값으로 함께 들어가 ExcelData가 만들어진다. 
  • (6) Json 데이터를 데이터베이스에 저장한다.

 

결과

  • 처음 의도대로 name, phone, json_data로 분리되어 저장됨을 확인할 수 있다.
  • 사진을 자세히 보면 json_data 컬럼명 왼쪽에 json 타입임이 명시되어 있다.
  • 다음 글에서는 json 데이터 조회 속도를 테스트할 예정이다.

 

 

 

참고

뤼튼

728x90