윈도우 엘라스틱서치 + MSSQL 연동(window elasticsearch mssql logstash)

윈도우 환경에서 엘라스틱서치와 mssql을 연동하는 방법 입니다.

일반 윈도우OS에서도 가능하며, 윈도우서버도 가능 합니다.

먼저 아래 홈페이지로 이동하여 윈도우 버전의 엘라스틱서치(elasticsearch)와 로그스태쉬(logstash)를 받습니다.

#1. 엘라스틱 서치

https://www.elastic.co/kr/downloads/elasticsearch

#2. 로그 스태쉬

https://www.elastic.co/kr/downloads/logstash

그리고 로그스태쉬(logstash)가 Mssql 접속에 사용해야 될 라이브러리(jar) 파일을 받습니다.

#3. Mssql 커넥터 다운로드

https://docs.microsoft.com/ko-kr/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver15

여기까지 우리가 받은 파일은 3개 입니다.

1. 엘라스틱서치(elasticsearch) 윈도우 파일(zip)

2. 로그스태쉬(logstash) 윈도우 파일(zip)

3. Mssql 커넥터 파일 (zip)

3개 파일 전부 zip형태로 되어 있습니다. (2021년 5월 27일 기준)

해당 압축파일을 앞으로 운용할 디렉토리에 풀어줍니다.

이제 설정 방법입니다.

아래 단계에 맞추어 하여 보세요.

──────────── 설정 1단계 ────────────

로그스태쉬(logstash) 디렉토리로 이동하여 config디렉토리로 가 봅니다.

여기에는 logstash-sample.conf 파일이 존재 하는데 해당 파일을 logstash.conf로 복사하여 줍니다.

* 파일명은 나중에 원하는데로 바꾸셔도 됩니다.

이곳에 로그스태쉬가 동작하여 수집해야되는 데이터베이스의 정보를 입력합니다.

아래 형식을 잘 봐주세요.

* 파일 이름 : logstash.conf

input {
    jdbc {
        jdbc_driver_library => "jar파일이 위치한곳/어쩌고jre.jar"
        jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
        jdbc_connection_string => "jdbc:sqlserver://주소:포트;databaseName=db이름;"
        jdbc_user => "아이디"
        jdbc_password => "비밀번호"
        schedule => "*/5 * * * * *" #수집주기
        tracking_column => "track_date" #추적할 컬럼
        use_column_value => true  #sql_last_value 사용 유무
        type => "main_db"  #별칭
        tracking_column_type => "numeric"  #별칭
        statement => "쿼리"
    }
}
filter {
    mutate{
        copy => {"idx" => "[@metatdata][_id]"}
    }
}
output {
    elasticsearch {    
        hosts => "localhost:9200"
        index => "검색할 인덱스이름"
    }
}

먼저 jdbc_driver_library 값 입니다.

jdbc_driver_library 값은 로그태쉬가 데이터베이스에 사용 할 라이브러리 경로를 의미합니다.

"Mssql 커넥터 다운로드" 에서 받은 파일을 의미하며 아래처럼 jar 형식으로 되어 있습니다.

3개 파일중 원하는 파일을 jdbc_driver_library 에 경로까지 포함된 값을 넣어주면 됩니다.

파일에서 보이는 jre8, jre11, jre15 라는 의미는 자바 버전을 의미하며 무난하게 중간 버전인 11을 사용하는 것을 추천 드립니다.

만약 여기서 jre11을 jdbc_driver_library 값에 넣어주면 아래처럼 입력 할 수 있습니다.

* 예제 : jdbc_driver_library => "C:/elks/sql/mssql-jdbc-9.2.1.jre8.jar"

다음으로 schedule 값 입니다.

크론(crond) 형식으로 로그스태쉬(logstash)가 수집할 시간을 입력 할 수 있습니다.

5초 단위로 테이블을 확인하여 데이터를 가져오게 하려면 아래처럼 입력 할 수 있습니다.

* 예제 : schedule => "*/5 * * * * *"

tracking_column입니다.

tracking_column 에서는 로그스태쉬(logstash)가 추적할 컬럼을 의미 합니다.

해당 내용은 다시 설명 하겠습니다.

use_column_value 입니다.

use_column_value은 true 와 false 값을 받습니다.

해당 값이 참이면 sql_last_value을 사용할 수 있으며 거짓이면 사용 할 수 없습니다.

마찬 가지로 해당 내용은 다시 설명 하겠습니다.

다음으로 type입니다.

type은 해당 jdbc 영역에 별칭을 의미 합니다.

로그스태쉬(logstash)가 추적해야 되는 테이블이 여러개인 경우 사용 됩니다.

tracking_column_type 입니다.

tracking_column_type 에서는 로그스태쉬(logstash)가 추적할 컬럼의 자료형을 의미 합니다.

이제 가장 중요한 statement 입니다.

statement는 로그스태쉬가 조사 해야될 테이블(table)의 쿼리를 의미 합니다.

이해를 위해 로그스태쉬가 조사해야되는 테이블이 board테이블이라고 가정하여 보겠습니다.

board 테이블의 스키마는 아래와 같습니다.

* 테이블 : board

순서	이름	내용	형태	특성
1	idx	ID 값을 의미합니다.	Int	자동증가
2	title	제목 입니다.	varchar	Null 허용
3	contents	내용 입니다.	varchar	Null 허용
4	insert_date	날짜 입니다.	datetime	Null 허용하지 않음

위 내용에서 중요한 컬럼이 insert_date 컬럼 입니다.

insert_date컬럼을 대상으로 로그스태쉬가 조건을 설정하여 수집할 데이터를 기준하게 됩니다.

Null을 허용하지 않으므로 반드시 데이터는 채워져야 합니다!!

백문이 불여일견!

statement에 들어갈 내용(쿼리)을 바로 살펴보겠습니다.

SELECT *, DATEDIFF(SECOND,'1970-01-01',insert_date) as track_date 
    FROM tb_board 
WHERE
    (
        DATEDIFF(SECOND,'1970-01-01',insert_date) > :sql_last_value 
            AND 
        insert_date < getdate()
    ) 
order by insert_date asc

conf파일서 use_column_value 값을 true로 하였기 때문에 sql_last_value을 사용할 수 있게 되었습니다.

sql_last_value값은 로그스태쉬가 사용하는 날짜를 숫자로 변환한 값(유닉스 타임스탬프) 입니다.

conf파일서 tracking_column설정에서 추적할 컬럼 이름을 track_date를 주었습니다.

그러나 테이블에 track_date가 없기 때문에 insert_date를 숫자로 변환(유닉스 타임스탬프)하여 별칭을 붙여 주었습니다.

#중요!!!!

이제 조건절(where)을 살펴 봅니다.

변환된 track_date 값이 로그스태쉬가 사용하는 날짜 값(use_column_value) 보다 크면서,

다음 조건인 insert_date 값이 getdate() 함수를 통해 지금 날짜보다 작은 대상에 대하여 select 하게 하였으므로,

로그스태쉬에게 한번 가져온 값에 대해서 더 이상 가져오지 못하도록 하였습니다.

해당 조건절이 없다면 로그스태쉬는 board 테이블의 내용을 계속해서 가져오게 될 것 입니다.

만약 board테이블에 10개의 데이터가 존재 하면서 조건절이 없다면, 5초 단위로 데이터를 10개씩 가져와 엘라스틱 서치 인덱스에 어마어마한 데이터가 들어가게 될 것 입니다.

이제 위 내용을 토대로 샘플 logstash.conf 파일을 완성하여 봅니다.

* 파일 이름 : logstash.conf

input {
    jdbc {
        jdbc_driver_library => "jar파일이 위치한곳/어쩌고jre.jar"
        jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
        jdbc_connection_string => "jdbc:sqlserver://주소:포트;databaseName=db이름;"
        jdbc_user => "아이디"
        jdbc_password => "비밀번호"
        schedule => "*/5 * * * * *" #수집 주기!
        tracking_column => "track_date" #추적할 컬럼!
        use_column_value => true #sql_last_value 사용유무 
        type => "main_db" #해당 jdbc 별칭
        tracking_column_type => "numeric" # 추적할 컬럼 자료형!
        statement => "SELECT *, DATEDIFF(SECOND,'1970-01-01',insert_date) as track_date     FROM tb_board WHERE    (        DATEDIFF(SECOND,'1970-01-01',insert_date) > :sql_last_value             AND         insert_date < getdate()    ) order by insert_date asc"
    }
}
filter {
    mutate{
        copy => {"idx" => "[@metatdata][_id]"}
    }
}
output {
    elasticsearch {    
        hosts => "localhost:9200"
        index => "검색할 인덱스이름"
    }
}

──────────── 설정 2단계 ────────────

2단계에서는 엘라스틱서치 디렉토리로 이동하여 bin 폴더에 존재하는 elasticsearch.bat 파일을 실행하여 줍니다.

그리고 난 뒤에 http:localhost:9200으로 접속여부를 확인합니다.

만약 외부접속을 하려 하신다면 ssl설정을 통해 하시는 것을 추천드립니다.

* 해당 내용은 구글링하면 찾을 수 있습니다.

그렇지 않고 단순 테스트목적으로 외부 개방을 허용하신다면, 엘라스틱 서치 디렉토리에 config 디렉토리로 이동하여 elasticsearch.yml 파일을 편집기로 수정하여 줍니다.

* 파일 이름 : elasticsearch.yml

cluster.name: 원하는클러스터이름
network.host: 0.0.0.0
http.port: 9200
discovery.seed_hosts: ["127.0.0.1", "[::1]"]

다음으로 로그스태쉬를 실행합니다.

마찬가지로 로그스태쉬에서 bin 디렉토리까지 이동한 뒤에 아래 처럼 만들어준 conf파일이 동작 하도록 명령에 -f 옵션을 추가하여 실행 되도록 하여 줍니다.

logstash.bat -f ../config/logstash.conf

로그 내용이 다를 수 있습니다 ! 그러나 에러가 없으면 성공 한 것 입니다. ^^

마지막으로 http:localhost:9200/_cat/indices?v 로 접속하여 로그스태쉬가 잘 동작하였는지 확인하여 줍니다.

여기서 저는 테이블 2개를 가져오도록 하였습니다.

여기까지 오류가 나지 않았지만, 데이터가 없거나 테이블보다 갯수가 적을수가 있습니다.

그럴 때는 날짜 값을 잘 확인 해 보아야 합니다.

그러므로 statement값에 존재하는 쿼리를 꼭 이해하여야 합니다!

* 이건 설명으로 해결이 안되므로 statement의 쿼리를 계속보면서 이해셔야 합니다.

로그스태쉬에게 테이블을 여러개 추적하도록 변경하는 것은 어렵지 않습니다.

logstash.conf에서 jdbc항목을 추가한 뒤에 type을 서로 다르게 표기하면 됩니다.

아래 샘플을 살펴보겠습니다.

input {
    #기존의 테이블!
    jdbc {
        #생략..
        type => "main_db"  #별칭!
    }
    
    #여기 jdbc를 한개 더 추가하여 줍니다.
    jdbc {
        #생략..
        type => "sub_db" #별칭!
    }
}


output {
    if [type] ==  "main_db"{  #별칭이 서로 맞아야 합니다!
        elasticsearch {    
            hosts => "localhost:9200"
            index => "main_db"
        }
    }
    if [type] ==  "sub_db"{  #별칭이 서로 맞아야 합니다!
        elasticsearch {    
            hosts => "localhost:9200"
            index => "sub_db"
        }
    }
}

input과 output에 각각 항목이 추가되었습니다.

input에서 jdbc내용을 하나 더 채운뒤에 각각 type이라는 항목에 사용할 고유 이름을 입력하여 줍니다.

그리고 output에서 if 조건문을 활용하여 해당 type에 따라 어떠한 인덱스로 표기될 지 정하여 줍니다.

로그스태쉬와 엘라스틱서치, MSSQL에서의 연동에서의 핵심 부분은 statement의 내용을 얼마나 잘 작성하느냐 입니다.

이상으로 윈도우(window) 환경에서 엘라스틱서치(elasticsearch), MSSQL, 로그스태쉬(logstash) 연동에 대해서 살펴 보았습니다.

궁금한 점이나 틀린 부분은 언제든 연락주세요! : )

저작자표시 변경금지

'엘라스틱서치(Elasticsearch)' 카테고리의 다른 글

엘라스틱서치 and or절 생성시 유의사항(Elasticsearch should minimum_should_match ) (8)	2021.07.01
엘라스틱서치 노리 한글검색(Elasticsearch nori) 방법 (0)	2021.06.22
윈도우 엘라스틱서치 + 노리설치(window elasticsearch nori) (0)	2021.06.08
윈도우 엘라스틱서치 + MongoDB 연동(window elasticsearch Mongodb monstache) (2)	2021.05.28
엘라스틱 서치 기초 질의문 정리(Elasticsearch query) (0)	2021.03.09

* 위 에니메이션은 Html의 캔버스(canvas)기반으로 동작하는 기능 입니다. Html 캔버스 튜토리얼 도 한번 살펴보세요~ :)

* 직접 만든 Html 캔버스 애니메이션 도 한번 살펴보세요~ :)

Input type Text

방문해 주셔서 감사합니다! 항상 행복하세요!

윈도우 엘라스틱서치 + MSSQL 연동(window elasticsearch mssql logstash)

'엘라스틱서치(Elasticsearch)' 카테고리의 다른 글

댓글

티스토리툴바

방문해 주셔서 감사합니다! 항상 행복하세요!

윈도우 엘라스틱서치 + MSSQL 연동(window elasticsearch mssql logstash)

'엘라스틱서치(Elasticsearch)' 카테고리의 다른 글

관련글

댓글

티스토리툴바