elasticsearch 6 java api 使用 logstash 读取MySQL数据创建索引

• 首先安装elasticsearch 我使用docker部署
  • docker-compose.yml 

version: '2.2'
services:
  elasticsearch:
    image: docker.elastic.co/elasticsearch/elasticsearch:6.7.1
    container_name: es01
    environment:
      - node.name=es01
      - cluster.name=docker-cluster
      - bootstrap.memory_lock=true
      - http.host=0.0.0.0
      - "transport.host=0.0.0.0"
      - network.host=127.0.0.1
      - "xpack.security.enabled=true"
      - "ES_JAVA_OPTS=-Xms256m -Xmx256m"
    ulimits:
      memlock:
        soft: -1
        hard: -1
    volumes:
      - esdata01:/home/docker/elasticsearch/data
    ports:
      - 9200:9200
      - 9300:9300
    networks:
      - esnet
  kibana:
    image: kibana:6.7.1
    container_name: kibana
    environment:
      SERVER_NAME: kibana
      ELASTICSEARCH_URL: http://127.0.0.1:9200
      XPACK_MONITORING_UI_CONTAINER_ELASTICSEARCH_ENABLED: "true"
      XPACK_SECURITY_ENABLED: "false"
      ELASTICSEARCH_SSL_VERIFY: "false"
    ports:
      - "5601:5601"
    networks:
      - esnet
volumes:
  esdata01:
    driver: local
  esdata02:
    driver: local

networks:
  esnet:

• 启动容器

 docker-compose up -d 

• 创建索引

通过logstash读取mysql数据创建索引信息

1. 下载logstash-7.0.0
2. 配置logstash.conf

cd logstash-7.0.0/config/

vim logstash.conf

 

•  logstash.conf配置信息

input {
    stdin {
    }
    jdbc {
      # mysql 数据库链接,shop为数据库名
      jdbc_connection_string => "jdbc:mysql://127.0.0.1:3369/db"
      # 用户名和密码
      jdbc_user => "root"
      jdbc_password => "root"
      # 驱动
      jdbc_driver_library => "/usr/local/logstash/mysqldoc/mysql-connector-java-8.0.8-dmr.jar"
      # 驱动类名
      jdbc_driver_class => "com.mysql.jdbc.Driver"
      jdbc_paging_enabled => "true"
      jdbc_page_size => "50000"
      # 执行的sql 文件路径+名称
      statement_filepath => "/usr/local/logstash/mysqldoc/drug.sql"
      # 设置监听间隔  各字段含义（由左至右）分、时、天、月、年，全部为*默认含义为每分钟都更新
      schedule => "* * * * *"
      # 索引类型
      type => "drug"
    }
}
 
filter {
    json {
        source => "message"
        remove_field => ["message"]
    }
}
 
output {
    elasticsearch {
        hosts => ["http://localhost:9200"]
        index => "hisappzscn"
        user  => "elastic"
        password => "ylkj2019s"
    }
    stdout {
        codec => json_lines
    }
}

•  drug.sql

select id as drugid,name as drugname,status,is_deleted,pinyin_code,alias,alias_pinyin from hisnew.sys_drug  where is_deleted='n' and status='y'

•  执行logstash创建索引

进入logstash目录执行下面命令

./bin/logstash -f config/logstash.conf

• 查看索引 

启动成功后可以ip:5601 查看kibana中es索引信息

 

•  springboot 中查询索引，创建索引，更新索引

pom.xml文件

 

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-actuator</artifactId>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.influxdb</groupId>
    <artifactId>influxdb-java</artifactId>
    <version>2.8</version>
</dependency>


<dependency>
    <groupId>org.elasticsearch.client</groupId>
    <artifactId>transport</artifactId>
    <version>6.7.1</version>
</dependency>



<dependency>
    <groupId>org.elasticsearch.client</groupId>
    <artifactId>elasticsearch-rest-high-level-client</artifactId>
    <version>6.7.1</version>
</dependency>

•  测试类

package com.shuzhi.es.demo;

import lombok.extern.java.Log;
import org.apache.http.HttpHost;
import org.elasticsearch.action.admin.indices.get.GetIndexRequest;
import org.elasticsearch.action.admin.indices.get.GetIndexResponse;
import org.elasticsearch.action.delete.DeleteRequest;
import org.elasticsearch.action.delete.DeleteResponse;
import org.elasticsearch.action.get.GetRequest;
import org.elasticsearch.action.get.GetResponse;
import org.elasticsearch.action.index.IndexRequest;
import org.elasticsearch.action.index.IndexResponse;
import org.elasticsearch.action.search.SearchRequest;
import org.elasticsearch.action.search.SearchResponse;
import org.elasticsearch.action.update.UpdateRequest;
import org.elasticsearch.action.update.UpdateResponse;
import org.elasticsearch.client.RequestOptions;
import org.elasticsearch.client.RestClient;
import org.elasticsearch.client.RestHighLevelClient;
import org.elasticsearch.common.unit.TimeValue;
import org.elasticsearch.index.query.BoolQueryBuilder;
import org.elasticsearch.index.query.Operator;
import org.elasticsearch.index.query.QueryBuilder;
import org.elasticsearch.index.query.QueryBuilders;
import org.elasticsearch.search.SearchHit;
import org.elasticsearch.search.SearchHits;
import org.elasticsearch.search.builder.SearchSourceBuilder;
import org.elasticsearch.search.sort.FieldSortBuilder;
import org.elasticsearch.search.sort.ScoreSortBuilder;
import org.elasticsearch.search.sort.SortOrder;
import org.junit.Test;

import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;
import java.util.concurrent.TimeUnit;

/**
 * @Description es例子
 * @author springjson
 * @date  2019/5/29 16:16
 */
@Log
public class EsTest {

    /**
     * 检查索引是否存在
     *
     * @throws Exception
     */
    @Test
    public void existIndex() throws Exception {
        RestHighLevelClient client = new RestHighLevelClient(RestClient.builder(new HttpHost("127.0.0.1", 9200, "http")));//初始化
        GetIndexRequest request = new GetIndexRequest();
        request.indices("hisappzscn");
        boolean exists = client.indices().exists(request, RequestOptions.DEFAULT);
        System.out.println("索引hisappzscn:" + exists);
        client.close();
    }

    /**
     * 获取索引
     *
     * @throws Exception
     */
    @Test
    public void getIndex() throws Exception {
        RestHighLevelClient client = new RestHighLevelClient(RestClient.builder(new HttpHost("127.0.0.1", 9200, "http")));//初始化
        GetIndexRequest request = new GetIndexRequest().indices("hisappzscn");
        GetIndexResponse getIndexResponse = client.indices().get(request, RequestOptions.DEFAULT);
        System.out.println(Arrays.toString(getIndexResponse.getIndices()));
        client.close();
    }

    /**
     * 创建或者更新文档
     * @throws Exception
     */
    @Test
    public void insertDocument()throws Exception{
        RestHighLevelClient client = new RestHighLevelClient(RestClient.builder(new HttpHost("127.0.0.1", 9200, "http")));//初始化

        Map<String, Object> jsonMap = new HashMap<>();
        jsonMap.put("drugname", "当归2");
        jsonMap.put("pinyin_code", "dg");
        jsonMap.put("alias","当归");
        jsonMap.put("drugid","231112");
        IndexRequest request = new IndexRequest("hisappzscn", "doc", "2").source(jsonMap);
        IndexResponse indexResponse = client.index(request, RequestOptions.DEFAULT);

        System.out.println("id:"+indexResponse.getId());

        client.close();
    }

    /**
     * 获取文档
     * @throws Exception
     */
    @Test
    public void getDocument()throws Exception{
        RestHighLevelClient client = new RestHighLevelClient(RestClient.builder(new HttpHost("127.0.0.1", 9200, "http")));//初始化
        GetRequest request = new GetRequest( "hisappzscn","doc","1");
        GetResponse getResponse = client.get(request, RequestOptions.DEFAULT);

        System.out.println("数据如下："+getResponse.getSource().toString());

        client.close();
    }

    /**
     * 判断某一个文档是否存在
     * @throws Exception
     */
    @Test
    public void existDocument() throws Exception{
        RestHighLevelClient client = new RestHighLevelClient(RestClient.builder(new HttpHost("127.0.0.1", 9200, "http")));//初始化
        GetRequest request = new GetRequest( "hisappzscn","doc","2");
        boolean exists = client.exists(request, RequestOptions.DEFAULT);

        System.out.println("是否存在："+exists);

        client.close();
    }

    /**
     * 删除文档
     * @throws Exception
     */
    @Test
    public void deleteDocuemnt()throws Exception{
        RestHighLevelClient client = new RestHighLevelClient(RestClient.builder(new HttpHost("127.0.0.1", 9200, "http")));//初始化
        DeleteRequest request = new DeleteRequest("hisappzscn", "doc", "2");
        request.timeout(TimeValue.timeValueMinutes(2));
        DeleteResponse deleteResponse = client.delete(request, RequestOptions.DEFAULT);

        System.out.println(deleteResponse.getSeqNo());
        client.close();
    }

    /**
     * 更新文档
     * @throws Exception
     */
    @Test
    public void updateDocument()throws Exception{
        RestHighLevelClient client = new RestHighLevelClient(RestClient.builder(new HttpHost("127.0.0.1", 9200, "http")));//初始化
        Map<String, Object> jsonMap = new HashMap<>();
        jsonMap.put("name", "当归2");
        jsonMap.put("pinyin_code", "di");
        jsonMap.put("drugid","231112");
        UpdateRequest request = new UpdateRequest("hisappzscn", "doc", "2").doc(jsonMap);
        UpdateResponse updateResponse = client.update(request, RequestOptions.DEFAULT);
        System.out.println(updateResponse.getId());
        client.close();
    }


    /**
     * 条件搜索
     *
     * @throws Exception
     */
    @Test
    public void searchDocument() throws Exception {
        RestHighLevelClient client = new RestHighLevelClient(RestClient.builder(new HttpHost("127.0.0.1", 9200, "http")));//初始化
        SearchRequest request = new SearchRequest();
        request.indices("hisappzscn");
        String keyWord = "di";

        SearchSourceBuilder sourceBuilder = new SearchSourceBuilder();
        //多条件设置
        sourceBuilder.query(filter(keyWord));
        // 排序start
        sourceBuilder.sort(new ScoreSortBuilder().order(SortOrder.DESC));
        sourceBuilder.sort(new FieldSortBuilder("_uid").order(SortOrder.ASC));
        // 排序end
        sourceBuilder.from(0);
        sourceBuilder.size(50);
        sourceBuilder.timeout(new TimeValue(60, TimeUnit.SECONDS));
        request.source(sourceBuilder);

        SearchResponse searchResponse = client.search(request, RequestOptions.DEFAULT);
        SearchHits hits = searchResponse.getHits();

        SearchHit[] searchHits = hits.getHits();

        System.out.println("SearchHit:" + searchHits.length);

        for (SearchHit hit : searchHits) {
            Map<String, Object> datas = hit.getSourceAsMap();
            System.out.println(datas.toString());
        }

        client.close();
    }

    private BoolQueryBuilder filter(String keyWord) {
        BoolQueryBuilder booleanQuery = QueryBuilders.boolQuery();
        QueryBuilder pinying = QueryBuilders.queryStringQuery(keyWord)
                .field("pinyin_code").defaultOperator(Operator.AND);

        float aliasBoost = 5;
        QueryBuilder alias1 = QueryBuilders.matchQuery("alias1", keyWord).operator(Operator.AND).boost(aliasBoost);
        QueryBuilder alias2 = QueryBuilders.matchQuery("alias2", keyWord).operator(Operator.AND).boost(aliasBoost);
        QueryBuilder alias3 = QueryBuilders.matchQuery("alias3", keyWord).operator(Operator.AND).boost(aliasBoost);
        QueryBuilder alias4 = QueryBuilders.matchQuery("alias4", keyWord).operator(Operator.AND).boost(aliasBoost);
        QueryBuilder alias5 = QueryBuilders.matchQuery("alias5", keyWord).operator(Operator.AND).boost(aliasBoost);

        booleanQuery.should(pinying);
        booleanQuery.should(alias1);
        booleanQuery.should(alias2);
        booleanQuery.should(alias3);
        booleanQuery.should(alias4);
        booleanQuery.should(alias5);


        if (keyWord.length() > 1) {
            QueryBuilder prefx = QueryBuilders.queryStringQuery(keyWord + "*").field("drugname.prefix")
                    .field("drugname.prefix").field("drugname.pinyin").field("ddrugname.SPY").field("drugname.fpy")
                    .field("drugname.iks").boost(40).defaultOperator(Operator.AND);

            QueryBuilder suffix = QueryBuilders.queryStringQuery("*" + keyWord).field("drugname.prefix")
                    .field("drugname.prefix").field("drugname.pinyin").field("ddrugname.SPY").field("drugname.fpy")
                    .field("drugname.iks").boost(30).defaultOperator(Operator.AND);

            QueryBuilder fullLike = QueryBuilders.queryStringQuery("*" + keyWord + "*").field("drugname.prefix")
                    .field("drugname.prefix").field("drugname.pinyin").field("ddrugname.SPY").field("drugname.fpy")
                    .field("drugname.iks").boost(20).defaultOperator(Operator.AND);
            booleanQuery.should(prefx);
            booleanQuery.should(suffix);
            booleanQuery.should(fullLike);

        }

        QueryBuilder name = QueryBuilders.queryStringQuery(keyWord + "*")
                .field("drugname").defaultOperator(Operator.AND).boost(15);

        booleanQuery.should(name);
        return booleanQuery;
    }


}

• 输出查询结果