springboot整合elasticsearch，以及logstash实现mysql数据同步到elastisearch（windows版）

springboot整合elasticsearch，以及logstash实现mysql数据同步到elastisearch（windows版）

1. 先到华为云镜像仓库(https://mirrors.huaweicloud.com/)下载elasticsearch和logstash，版本为6.4.3；因为我的springboot是2.1.6.RELEASE版本，注意版本不匹配可能会有问题。

2. 解压elastisearch之后，修改配置elasticsearch.yml文件，运行bin目录elasticsearch.bat
   cluster.name: elasticsearch
node.name: master
path.data: D:\elasticsearch\elasticsearch-6.4.3\data
path.logs: D:\elasticsearch\elasticsearch-6.4.3\logs
network.host: 127.0.0.1
http.port: 9200

3. 解压logstash之后,修改Gemfile的source地址为https://gems.ruby-china.org，安装logstash-input-jdbc和logstash-output-elasticsearch，执行
   logstash-plugin install --no-verify logstash-input-jdbc
logstash-plugin install --no-verify logstash-output-elasticsearch

4. 在logstash目录下新建mysql目录，用于存放同步文件（movie.sql, mysql-connector-jdbc-8.0.16.jar, last_run_id.txt），在config目录下添加logstash-mysql-es.conf, 添加内容

input {
  jdbc {
    jdbc_connection_string => "jdbc:mysql://localhost:3306/movie? useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai"
	jdbc_user => "root"
	jdbc_password => "123456"
	jdbc_driver_library => "./mysql/mysql-connector-java-8.0.16.jar"
	jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
	jdbc_paging_enabled => true
	jdbc_page_size => "5000"
	
	statement_filepath => "./mysql/jdbc.sql"
	schedule => "* * * * *"
	type => "movie"
	
	#禁止转换小写
	lowercase_column_names => false
	
	#增量同步
	record_last_run => true
	use_column_value => true
	tracking_column => "id"
	last_run_metadata_path => "./mysql/last_run_id.txt"
	clean_run => false
  }
}

filter {
#对数据进行处理，actorList 在elasticsearch的mappings是数组，数据库中是字符串，所以要进行切割
  ruby {
    code => "
		array1 = event.get('actorList').split(',')
		event.set('actorList', array1)
	"
  }
}

output {
  elasticsearch {
    hosts => ["http://localhost:9200"]
    index => "movie_index"
	document_type => "movie"
	document_id => "%{id}"
	# 如果elasticsearch存在mappings，则不需要这两条命令
	#template_overwrite => true
    #template => "./mysql/template.json"
  }
  
  #输出调试，正式环境注释 
  stdout {
      codec => json_lines
  } 
}

movie.sql

SELECT t.id as id, t.title as title, t.actor_list as actorList, t.douban_score as doubanScore, t.create_time as createTime FROM t_movie t

5. 执行启动logstash命令 ./logstash.bat -f logstash-mysql-es.conf，即可每分钟同步一次。
6. 在项目里面依赖 spring-boot-starter-data-elasticsearch, 在application.yml里面添加

spring:
   data:
     elasticsearch:
       cluster-nodes: localhost:9300  #如果是集群，用,分隔
       cluster-name: elasticsearch

7.添加代码，根据关键字查询 （title，actorList），高亮显示

QueryBuilder queryBuilder = new MultiMatchQueryBuilder(keyword, "title", "actorList.keyword");

        Pageable pageable = PageRequest.of(page - 1, size);
        SearchQuery searchQuery = new NativeSearchQueryBuilder()
                .withQuery(queryBuilder)
                .withHighlightBuilder(new HighlightBuilder().preTags("<span style='color:red'>").postTags("</span>"))
                .withHighlightFields(new HighlightBuilder.Field("title"), new HighlightBuilder.Field("actorList.keyword"))
                .withPageable(pageable)
                .build();

        AggregatedPage<MovieSearchDoc> response = elasticsearchTemplate.queryForPage(searchQuery, MovieSearchDoc.class, new SearchResultMapper() {
            @Override
            public <T> AggregatedPage<T> mapResults(SearchResponse searchResponse, Class<T> aClass, Pageable pageable) {
                SearchHits hits = searchResponse.getHits();
                SearchHit[] hitArr = hits.getHits();

                List<MovieSearchDoc> content = new ArrayList<>();
                if(hitArr != null && hitArr.length > 0) {

                    for (SearchHit hit: hitArr) {
                        Map<String, Object> sourceMap = hit.getSourceAsMap();
                        Integer id = (Integer) sourceMap.get("id");
                        String title = (String) sourceMap.get("title");
                        Double doubanScore = (Double) sourceMap.get("doubanScore");
                        List<String> actorList = (List<String>) sourceMap.get("actorList");
                        String createTime = (String) sourceMap.get("createTime");
                        MovieSearchDoc doc = new MovieSearchDoc();
                        doc.setId(id);
                        try {
                            doc.setCreateTime(new SimpleDateFormat("yyyy-MM-dd").parse(createTime));
                        } catch (ParseException e) {
                            e.printStackTrace();
                        }
                        doc.setDoubanScore(doubanScore);

                        Map<String, HighlightField> highlightMap = hit.getHighlightFields();
                        HighlightField titleField = highlightMap.get("title");

                        if(titleField != null) {
                            doc.setTitle(titleField.getFragments()[0].string());
                        } else {
                            doc.setTitle(title);
                        }
                        HighlightField actorListField = highlightMap.get("actorList.keyword");
                        if(actorListField != null) {
                            for (int i = 0; i < actorList.size(); i++) {
                                if(actorList.get(i).equals(keyword)) {
                                    actorList.set(i, actorListField.getFragments()[0].toString());
                                }
                            }
                            doc.setActorList(actorList);
                        } else {
                            doc.setActorList(actorList);
                        }

                        content.add(doc);
                    }
                }
                return new AggregatedPageImpl<T>((List<T>)content, pageable, searchResponse.getHits().getTotalHits());
            }
        });

检索结果


有不对的请指正