ES库与MySql比较
如何将数据库中的数据同步到ES中
由于数据量不大,首先采用logstash同步数据库数据
5.6版本的logstash由于数据量过大可能会同步数据时失败
编写查出需要字段的sql语句
在logstash下新建mysqletc(随意起)文件件
在文件夹下放入mysql的jar包,与配置文件
input {
jdbc {
# mysql jdbc connection string to our backup databse
jdbc_connection_string => "jdbc:mysql://127.0.0.1:3306/searchroom?characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false"
# the user we wish to excute our statement as
jdbc_user => "root"
jdbc_password => "zxf123123"
# the path to our downloaded jdbc driver
jdbc_driver_library => "D:\kaifaplugins\logstash-6.7.2\mysqletc\mysql-connector-java-6.0.6.jar"
# the name of the driver class for mysql
jdbc_driver_class => "com.mysql.jdbc.Driver"
#是否要分页
jdbc_paging_enabled => "true"
jdbc_page_size => "50"
#以下对应着要执行的sql的绝对路径。
#statement_filepath => ""
statement => "select
a.id, a.id AS houseId, a.title, a.price, a.area, a.create_time, a.last_update_time,
a.city_en_name, a.region_en_name, a.direction, a.distance_to_subway,
b.subway_line_name, b.subway_station_name, a.street, a.district, b.description,
b.layout_desc, b.traffic, b.round_service, b.rent_way, (SELECT GROUP_CONCAT(name SEPARATOR ',') from house_tag WHERE a.id = house_id) as tags
from
house a LEFT JOIN house_detail b on a.id = b.house_id where status != 3"
#定时字段 各字段含义(由左至右)分、时、天、月、年,全部为*默认含义为每分钟都更新(测试结果,不同的话请留言指出)
schedule => "* * * * *"
}
}
output {
elasticsearch {
#ESIP地址与端口
hosts => "127.0.0.1:9200"
#ES索引名称(自己定义的)
index => "searchroom"
#自增ID编号,与sql语句id对上
document_id => "%{id}"
document_type => "house"
}
stdout {
#以JSON格式输出
codec => json_lines
}
}
执行命令
logstash -f …/mysqletc/mysql.conf
数据同步完成
创建站内搜索引擎工程
添加所需依赖
<dependency>
<groupId>org.springframework.data</groupId>
<artifactId>spring-data-elasticsearch</artifactId>
<version>LATEST</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
添加配置,编写启动类,添加搜索类型,持久类
server:
port: 9004 #端口
spring:
application:
name: search-service
cloud:
nacos:
discovery:
server-addr: 127.0.0.1:8848
data:
elasticsearch:
cluster-nodes: 127.0.0.1:9300 # es的地址
ES查找服务层
此查找条件为city_en_name为bj,并且关键字出现在title等字段中的document
ES的持久层类型 (用户增删改ES的数据)
package com.zxf.dao;
import com.zxf.pojo.HouseIndex;
import org.springframework.data.elasticsearch.repository.ElasticsearchCrudRepository;
import org.springframework.stereotype.Repository;
@Repository
public interface HouseSearchDao extends ElasticsearchCrudRepository<HouseIndex, Long> {
}
对应ES类型
ik_smart:对查询语句或存储数据最小切分
例:
ik_max_word:对查询语句或存储数据最细切分
例:
类型