나만의 주소 검색엔진 만들기 (초기작업 + 삽질)

뮤·2022년 10월 22일
Logstashdockermysql삽질
1

삽질

목록 보기
6/11

회사일처럼 보이지만 제가 하고싶어서 시작한 저만의 작은 사이드프로젝트라 블로깅 할 수 있지롱

작은거맞나? 아닌거같은데(...)

아, 로그스태시에 넣으려면 SQL 짜야하는데?

그렇다(...) jdbc를 이용해서 로그스태시에 DB 데이터를 넣으려면 SQL을 적어줘야한다.

그래서 열심히 짜봤는데, 여러가지 변수가 존재했다.

  1. 도로명주소에 도로명주소 2가 붙을 경우 도로명주소1-도로명주소2 같은 형식이 된다.
  2. 지번주소에는 동, 면, 읍이 랜덤으로 들어가서 없을 경우 공백처리를 해야한다.
  3. 엘라스틱서치가 term 단위를 만들기 위해서는 공백을 기준으로 짜르니 합쳐서 넣어야한다.

그래서 짰다. 이건 금방 짠듯?

SELECT 
a.ZIP_NO AS zip_code,
CONCAT_WS(' ',SIDO,SIGUNGU,DORO,IF(BUILD_NO2 IS NOT NULL,CONCAT(BUILD_NO1,'-',BUILD_NO2),BUILD_NO1)) AS doro_address,
CONCAT_WS(' ',SIDO,SIGUNGU,NULLIF(DONG_NM,""),NULLIF(RI,""),IF(ZIBUN2 IS NOT NULL,CONCAT(ZIBUN1,'-',ZIBUN2),ZIBUN1)) AS zibun_address,
a.BUILD_NM AS build_name,
a.BUILD_NO_MANAGE_NO AS manage_code,
a.CREATED_AT AS created_at,
a.UPDATED_AT AS updated_at
FROM address a;

애초에 도로명주소나 지번주소를 한개 컬럼으로 관리를 하고싶었기에 이러한 구조를 사용했다.

업데이트 및 삭제를 하기 위해서 pk로는 건물 관리번호를 넣어줬고
생성 수정 날짜는 언젠가 써먹을 것 같아서 같이 넣어줬다.

도커가 알아서 테이블 만들고 넣어주면 좋겠다.

라는 생각으로 웹서핑을 계속 해봤다.

왜냐하면 DB를 초기화할 때 마다 SQL을 치고, LOAD DATA를 하는건 개발자같지 않아!
개발자라는 민족은 귀차니즘에 쩔어서 자동화하는 직업이란말이야!

사실 스택오버플로우에서 본 글을 기반으로 짰는데, 그 글이 지금 안보인다(....)

폴더구조는 위처럼 생겼고, 실제 도커에 마운트하는 volumes은 이렇게 생겼다.

version: '3.7'

services:
  database:
    image: mysql:latest
    environment:
      MYSQL_DATABASE: 'root'
      MYSQL_ROOT_PASSWORD: 'root'
    ports:
      - 3308:3306
    volumes:
      - /Users/Desktop/address/db/conf.d:/etc/mysql/conf.d
      - /Users/Desktop/address/db/initdb.d:/docker-entrypoint-initdb.d

이리저리 찾아보면서 신기한 것을 알았는데, mysql같은 경우에는 conf.d 파일 하나로 모든 것을 글로벌하게 관리를 한다는 것이였다.

그래서 저거 로컬에도 있다.

아마 path는 공통인 것 같은데, /usr/local/etc 속에 들어가있고 vi my.cnf < 로 들어가면 볼 수 있다.

디폴트는 이정도 밖에 없긴 하지만?

실제로 내가 선언할 때는 이렇게 적어놨다.

파일 이름 : my.cnf (mysql 공통 형식 선언?)

/Users/Desktop/address/db/conf.d:/etc/mysql/conf.d

[client]
default-character-set = utf8mb4
local_infile          = 1

[mysql]
default-character-set = utf8mb4
local_infile          = 1

[mysqld]
character-set-client-handshake = FALSE
character-set-server           = utf8mb4
collation-server               = utf8mb4_unicode_ci
local_infile                   = 1

솔직히 localinfile 이거 제대로 적용되는지 진짜 모르겠다 -- 안되는 것 같기도하고 안된다면 어디서 적용을 해야하는지 좀 누군가 알려줬으면 좋겠다;

파일 이름 : create_table.sql (도커 시작시 테이블 생성)

/Users/Desktop/address/db/initdb.d:/docker-entrypoint-initdb.d

여기는 500줄이 넘어서 다 올리는 것은 불가능하고, 대충 이렇게 적혀있다.

데이터베이스를 새롭게 생성하고, 해당 데이터베이스를 사용하고
파일 로드 권한을 풀어준 후 테이블을 생성한다.

대충 17개 만든다, 각 행정구역?별로 만들고 있음

CREATE DATABASE address;

use address;

SET GLOBAL local_infile=1;

CREATE TABLE update_address ( 
`ZIP_NO` VARCHAR(5) NULL COMMENT '우편번호',
`SIDO` VARCHAR(20) NULL COMMENT '시도',
`SIDO_ENG` VARCHAR(40) NULL COMMENT '시도(영문)',
`SIGUNGU` VARCHAR(20) NULL COMMENT '시군구',
`SIGUNGU_ENG` VARCHAR(40) NULL COMMENT '시군구(영문)',
`EUPMYUN` VARCHAR(20) NULL COMMENT '읍면',
`EUPMYUN_ENG` VARCHAR(40) NULL COMMENT '읍면(영문)',
`DORO_CD` VARCHAR(12) NULL COMMENT '도로명코드',
`DORO` VARCHAR(80) NULL COMMENT '도로명',
`DORO_ENG` VARCHAR(80) NULL COMMENT '도로명(영문)',
`UNDERGROUND_YN` CHAR(1) NULL COMMENT '지하여부',
`BUILD_NO1` DECIMAL(5,0) NULL COMMENT '건물번호본번',
`BUILD_NO2` DECIMAL(5,0) NULL COMMENT '건물번호부번',
`BUILD_NO_MANAGE_NO` VARCHAR(25) NULL COMMENT '건물관리번호',
`DARYANG_NM` VARCHAR(40) NULL COMMENT '다량배달처명',
`BUILD_NM` VARCHAR(200) NULL COMMENT '시군구용건물명',
`DONG_CD` VARCHAR(10) NULL COMMENT '법정동코드',
`DONG_NM` VARCHAR(20) NULL COMMENT '법정동명',
`RI` VARCHAR(20) NULL COMMENT '리명',
`H_DONG_NM` VARCHAR(40) NULL COMMENT '행정동명',
`SAN_YN` VARCHAR(1) NULL COMMENT '산여부',
`ZIBUN1` DECIMAL(4,0) NULL COMMENT '지번본번',
`EUPMYUN_DONG_SN` VARCHAR(2) NULL COMMENT '읍면동일련번호',
`ZIBUN2` DECIMAL(4,0) NULL COMMENT '지번부번' ,
`ZIP_NO_OLD` VARCHAR(4) NULL COMMENT '구우편번호' ,
`ZIP_SN` VARCHAR(2) NULL COMMENT '우편일련번호',
`CREATED_AT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`UPDATED_AT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ) 
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1;

파일 이름 : load_data.sql (도커 시작시 데이터 로드 실행)

/Users/Desktop/address/db/initdb.d:/docker-entrypoint-initdb.d

잘보면 도커 내부로 연결되는 path가 엄청 특이한데, 엔트리포인트에 초기화시키는 db 정보에 넣으면 들어간다, 짱신기함

아 그리고 첫 실행때 파일 로드 권한이 오픈이 안되는지, 한번 껐다가 다시 키면 쫙하고 들어간다(....)
최초 실행에 들어가면 좋겠는데 지금 두번째 실행에만 들어가고 있어서 조금 아쉽달까?

여기도 똑같이 겁나 많아서(....) 일부만 올린다.

USE address;
SET GLOBAL local_infile= true;

LOAD DATA LOCAL INFILE "var/lib/seoul.txt" INTO TABLE seoul CHARACTER SET 'UTF8MB4' FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES
(@ZIP_NO, @SIDO, @SIDO_ENG, @SIGUNGU, @SIGUNGU_ENG,
@EUPMYUN, @EUPMYUN_ENG,
@DORO_CD, @DORO, @DORO_ENG,
@UNDERGROUND_YN, @BUILD_NO1, @BUILD_NO2,
@BUILD_NO_MANAGE_NO, @DARYANG_NM,@BUILD_NM,
@DONG_CD, @DONG_NM,@RI, @H_DONG_NM, @SAN_YN,
@ZIBUN1, @EUPMYUN_DONG_SN, @ZIBUN2, @ZIP_NO_OLD, @ZIP_SN)
SET
ZIP_NO = NULLIF(@ZIP_NO,'0'),
SIDO = NULLIF(@SIDO,'0'),
SIDO_ENG = NULLIF(@SIDO_ENG,'0'),
SIGUNGU = NULLIF(@SIGUNGU,'0'),
SIGUNGU_ENG = NULLIF(@SIGUNGU_ENG,'0'),
EUPMYUN = NULLIF(@EUPMYUN,'0'),
EUPMYUN_ENG = NULLIF(@EUPMYUN_ENG,'0'),
DORO_CD = NULLIF(@DORO_CD,'0'),
DORO = NULLIF(@DORO,'0'),
DORO_ENG = NULLIF(@DORO_ENG,'0'),
UNDERGROUND_YN = NULLIF(@UNDERGROUND_YN,'0'),
BUILD_NO1 = NULLIF(@BUILD_NO1,'0'),
BUILD_NO2 = NULLIF(@BUILD_NO2,'0'),
BUILD_NO_MANAGE_NO = NULLIF(@BUILD_NO_MANAGE_NO,'0'),
DARYANG_NM = NULLIF(@DARYANG_NM,'0'),
BUILD_NM = NULLIF(@BUILD_NM,'0'),
DONG_CD = NULLIF(@DONG_CD,'0'),
DONG_NM = NULLIF(@DONG_NM,'0'),
RI = NULLIF(@RI,'0'),
H_DONG_NM = NULLIF(@H_DONG_NM,'0'),
SAN_YN = NULLIF(@SAN_YN,'0'),
ZIBUN1 = NULLIF(@ZIBUN1,'0'),
EUPMYUN_DONG_SN = NULLIF(@EUPMYUN_DONG_SN,'0'),
ZIBUN2 = NULLIF(@ZIBUN2,'0'),
ZIP_NO_OLD = NULLIF(@ZIP_NO_OLD,'0'),
ZIP_SN  = NULLIF(@ZIP_SN,'0');

위처럼하면 나오는 결과물

모든 데이터가 예쁘게 들어가있는 모습을 확인할 수 있다.

아...데이터가 많으니까...잘 안들어가네....?

맨 처음에는 한 테이블에 다 모아서 로그스태시에 밀어넣으려고 했다.

그리고 결과는 ^^;; 위에 사진처럼 힙 메모리가 폭발해가지고 로그스태시가 강제종료당하더라(....)

그럼 어떻게할까...하다가 파이프라인을 만들면 되겠다는 생각이 들었다.

각 행정구역별로 파일을 만들어서, 파이프라인을 만들어서 docker-compose에 values에 넣었다.

그래서 로그를 확인해보면 제대로 올라간 것을 확인할 수 있었다.

그리고 그것을 한번에 다 돌려봤다.

또 터짐ㅋㅋㅋㅋㅋㅋ

java.lang.NullPointerException: null
TypeError: can't convert nil into an exact number
java.lang.OutOfMemoryError: Java heap space

로그스태시에 대한 지식이 제대로 없어서 모르겠는데, 이게 파이프라인으로 여러개를 분리하더라도
결국은 한개의 JVM 위에서 돌아가는 것 같다.

왜냐하면 파이프라인을 만들고 나눠놓는다고 conf 파일만큼 JVM을 할당하면 16개가 올라가야하는데
이것도 솔직히 말이 안되고... 그래서 좀 찾아봤는데 이런 옵션 값을 찾긴 했다.

https://www.elastic.co/guide/en/logstash/7.17/jvm-settings.html

아예 JVM의 메모리 할당 용량을 확장시키는 옵션으로 보였는데,
어떻게 적용을 해야하는지, 옵션값을 어떻게 넣어야하는지를 모르겠어서 적용을 못했다(ㅠㅠ)

그으래서 어떻게 넣어서 테스트를 해보긴 해야하는데, 고민이다..

솔직히 한번 데이터를 넣기만하고, 한달단위로 갱신되는 것들을 업데이트 or 제거만 하면 되서 맨 처음에 넣는 것이 제일 문제다.

근데 맨 처음에 넣어야하는 데이터가 600만개쯤 되다보니 정말 마음대로 안되는 것 같다 ㅋㅋ

그래서 지금 어떻게 넣고 있는데?

    jdbc {
        jdbc_driver_library => "/usr/share/logstash/mysql-connector-java-8.0.28.jar"
        jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
        jdbc_connection_string => "jdbc:mysql://database:3306/address?useSSL=false&allowPublicKeyRetrieval=true"
        jdbc_user => "root"
        jdbc_password => "root"
        schedule => "36 * * * *"
        jdbc_fetch_size => 300000
        use_column_value => true
        tracking_column => "updated_at"
        tracking_column_type => "timestamp"
        statement =>  "
        SELECT a.ZIP_NO AS zip_code,
        CONCAT_WS(' ',SIDO,SIGUNGU,DORO,IF(BUILD_NO2 IS NOT NULL,CONCAT(BUILD_NO1,'-',BUILD_NO2),BUILD_NO1)) AS doro_address,
        CONCAT_WS(' ',SIDO,SIGUNGU,NULLIF(DONG_NM,''),NULLIF(RI,''),IF(ZIBUN2 IS NOT NULL,CONCAT(ZIBUN1,'-',ZIBUN2),ZIBUN1)) AS zibun_address,
        a.BUILD_NM AS build_name,
        a.BUILD_NO_MANAGE_NO AS manage_code,
        a.CREATED_AT AS created_at,
        a.UPDATED_AT AS updated_at
        FROM main_address a
        LIMIT 1500000,2000000"
    }

그냥 LIMIT를 걸어서 쪼개가지고 넣고있다.

대략 100만개까지는 들어간다, 한 15분~17분정도 걸리긴 하지만?

600만개니까 6번을 돌리면 되는데, 이게 너무 싫어가지고 어떻게 한번에 다 넣어보려고 했는데...
안되니까 너-무 짜증난다, 오늘 한 12시간 넘게 이걸 붙잡고 있었는데

이게 전문적으로 배운게 아니고 지식이 없는데 막 우격다짐으로 하려니까 이러는듯 (...)

내일은 서버작업좀 해봐야겠다, , ,

profile
뮤
물류 서비스 Backend Software Developer
이전 포스트

오늘의 삽질일기

다음 포스트

나만의 주소 검색엔진 만들기 (ELK STACK)

0개의 댓글