SQLServer同步数据到ElasticSearch

window10环境下

1.中间工具：

JDK,JDBC,Logstash,ElasticSearch

JDBC:https://download.microsoft.com/download/3/7/9/379E727A-58F5-45E6-9D45-95D593399F93/sqljdbc_6.2.2.1_chs.exe

Logstash:https://artifacts.elastic.co/downloads/logstash/logstash-7.7.0.zip

ElasticSearch:官网下址https://www.elastic.co/cn/

2.JDK,JDBC,ElasticSearch安装

略过

3.Logstash安装与配置：jdbc.conf配置

input {
    jdbc {
     jdbc_driver_library => "D:\logstash-7.7.0\bin\jdbcconfig\mssql-jdbc-6.2.2.jre8.jar"
            jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
            jdbc_connection_string => "jdbc:sqlserver://192.168.0.135:1433;DatabaseName=test1;"
            jdbc_user => "sa"
            jdbc_password => "123456"
                     # schedule => 分 时 天 月 年  
                        # schedule => * 22  *  *  *     //will execute at 22:00 every day
            schedule => "* * * * *"
            jdbc_paging_enabled => true
            jdbc_page_size => 1000
            clean_run => false
            use_column_value => true
            #设置查询条件的字段
              tracking_column => FID
            record_last_run => true
            last_run_metadata_path => "D:\logstash-7.7.0\bin\jdbcconfig\FID.txt"
            #设置列名小写
              lowercase_column_names => false
            statement_filepath => "D:\logstash-7.7.0\bin\jdbcconfig\Load_User.sql"
            #索引的类型
              type => "advancelist"
    }
}

output {
    elasticsearch {
        hosts => ["192.168.0.238:9200"]
        index => "advancelist"
        document_id => "%{FID}"
    }
    stdout {
        #codec => json_lines
        #设置输出的格式
        codec => line {
            format => "FID: %{[FID]} FPersonName: %{[name]} FAddTime: %{[sex]}"
        }
    }
}

4.运行：cmd

.\logstash -f .\jdbcconfig\jdbc.conf --path.data=/jdbcconfig/

5.测试:延时在30秒左右

 

6.监控sql日志来做数据同步，是做主从，或异步查询常用的方案，附测试使用过的Logstash：https://download.csdn.net/download/atzqtzq/12419731

参考：https://www.cnblogs.com/rtwo/p/11014732.html