Elasticsearch解决分页慢以及日志整合监控的方案

 

 

 

一、 安装

   安装包目录：/home/***/es/elasticsearch-6.5.4.rpm

   数据和日志目录：/home/***/es/elasticsearch
 

  安装后的配置目录;/etc/elasticsearch/

 安装后的启动目录 /usr/share/elasticsearch/bin/elasticsearch

方法和es查询转换：

Keyword	Sample	Elasticsearch Query String
And	findByNameAndPrice	{"bool" : {"must" : [ {"field" : {"name" : "?"}}, {"field" : {"price" : "?"}} ]}}
Or	findByNameOrPrice	{"bool" : {"should" : [ {"field" : {"name" : "?"}}, {"field" : {"price" : "?"}} ]}}
Is	findByName	{"bool" : {"must" : {"field" : {"name" : "?"}}}}
Not	findByNameNot	{"bool" : {"must_not" : {"field" : {"name" : "?"}}}}
Between	findByPriceBetween	{"bool" : {"must" : {"range" : {"price" : {"from" : ?,"to" : ?,"include_lower" : true,"include_upper" : true}}}}}
LessThanEqual	findByPriceLessThan	{"bool" : {"must" : {"range" : {"price" : {"from" : null,"to" : ?,"include_lower" : true,"include_upper" : true}}}}}
GreaterThanEqual	findByPriceGreaterThan	{"bool" : {"must" : {"range" : {"price" : {"from" : ?,"to" : null,"include_lower" : true,"include_upper" : true}}}}}
Before	findByPriceBefore	{"bool" : {"must" : {"range" : {"price" : {"from" : null,"to" : ?,"include_lower" : true,"include_upper" : true}}}}}
After	findByPriceAfter	{"bool" : {"must" : {"range" : {"price" : {"from" : ?,"to" : null,"include_lower" : true,"include_upper" : true}}}}}
Like	findByNameLike	{"bool" : {"must" : {"field" : {"name" : {"query" : "?*","analyze_wildcard" : true}}}}}
StartingWith	findByNameStartingWith	{"bool" : {"must" : {"field" : {"name" : {"query" : "?*","analyze_wildcard" : true}}}}}
EndingWith	findByNameEndingWith	{"bool" : {"must" : {"field" : {"name" : {"query" : "*?","analyze_wildcard" : true}}}}}
Contains/Containing	findByNameContaining	{"bool" : {"must" : {"field" : {"name" : {"query" : "?","analyze_wildcard" : true}}}}}
In	findByNameIn(Collection<String>names)	{"bool" : {"must" : {"bool" : {"should" : [ {"field" : {"name" : "?"}}, {"field" : {"name" : "?"}} ]}}}}
NotIn	findByNameNotIn(Collection<String>names)	{"bool" : {"must_not" : {"bool" : {"should" : {"field" : {"name" : "?"}}}}}}
Near	findByStoreNear	Not Supported Yet !
True	findByAvailableTrue	{"bool" : {"must" : {"field" : {"available" : true}}}}
False	findByAvailableFalse	{"bool" : {"must" : {"field" : {"available" : false}}}}
OrderBy	findByAvailableTrueOrderByNameDesc	{"sort" : [{ "name" : {"order" : "desc"} }],"bool" : {"must" : {"field" : {"available" : true}}}}

 

 

基于Windows环境

 

常见问题;

1、logstash -f ../mysqletc/mysql.conf   启动失败

[2020-02-14T12:07:09,610][ERROR][logstash.agent           ] Failed to execute action {:action=>LogStash::PipelineAction::Create/pipeline_id:main, :exception=>"LogStash::ConfigurationError", :message=>"Expected one of #, input, filter, output at line 1, column 1 (byte 1) after ", :backtrace=>["D:/liuqian/es/logstash-6.5.1/logstash-core/lib/logstash/compiler.rb:41:in `compile_imperative'", "D:/liuqian/es/logstash-6.5.1/logstash-core/lib/logstash/compiler.rb:49:in `compile_graph'", "D:/liuqian/es/logstash-6.5.1/logstash-core/lib/logstash/compiler.rb:11:in `block in compile_sources'", "org/jruby/RubyArray.java:2486:in `map'", "D:/liuqian/es/logstash-6.5.1/logstash-core/lib/logstash/compiler.rb:10:in `compile_sources'", "org/logstash/execution/AbstractPipelineExt.java:149:in `initialize'", "D:/liuqian/es/logstash-6.5.1/logstash-core/lib/logstash/pipeline.rb:22:in `initialize'", "D:/liuqian/es/logstash-6.5.1/logstash-core/lib/logstash/pipeline.rb:90:in `initialize'", "D:/liuqian/es/logstash-6.5.1/logstash-core/lib/logstash/pipeline_action/create.rb:42:in `block in execute'", "D:/liuqian/es/logstash-6.5.1/logstash-core/lib/logstash/agent.rb:92:in `block in exclusive'", "org/jruby/ext/thread/Mutex.java:148:in `synchronize'", "D:/liuqian/es/logstash-6.5.1/logstash-core/lib/logstash/agent.rb:92:in `exclusive'", "D:/liuqian/es/logstash-6.5.1/logstash-core/lib/logstash/pipeline_action/create.rb:38:in `execute'", "D:/liuqian/es/logstash-6.5.1/logstash-core/lib/logstash/agent.rb:317:in `block in converge_state'"]}

 

原因：mysql.conf字符集需要UTF-无BOM 保存

参照https://blog.youkuaiyun.com/Crazy_T_B/article/details/79422602

2、logstash数据同步问题

logstash -f ../mysqletc/mysql.conf

 

logstash -f ../mysqletc/mysql.conf -t 可以检测mysql.conf文件是否有问题

 

Could not index event to Elasticsearch. {:status=>400, :action=>["index", {:_id=>"%{id}", :_index=>"commodity", :_type=>"doc", :_routing=>nil}, #<LogStash::Event:0x37b7b861>], :response=>{"index"=>{"_index"=>"commodity", "_type"=>"doc", "_id"=>"%{id}", "status"=>400, "error"=>{"type"=>"illegal_argument_exception", "reason"=>"Rejecting mapping update to [commodity] as the final mapping would have more than 1 type: [commodity, doc]"}}}}

{"price":1,"stock":50,"@version":"1","@timestamp":"2020-02-14T06:21:00.131Z","skuid":2,"type":"doc","brand":"伊利","name":"qq","category":"饮料"}

 

原因：是因为 commodity 索引中已经有type=“commodity”的类型数据，而logstash同步过来的type类型是 doc,违背了es6.x 只允许一个类型的规则；

 

 

3、数据库有两条数据，实际只同步了一条；

 原因是logstash-input-jdbc同步的配置文件，output部分的 #自增ID

document_id => "%{id}" 没有出现在查询的sql列上；

 

4、head插件安装

 

5、kibana安装

只需要修改kibana.yml 配置文件，然后启动 ./bin/kibana.bat

参照 https://www.cnblogs.com/DidiLiu/articles/10843248.html

参照 https://www.elastic.co/guide/cn/kibana/current/connect-to-elasticsearch.html

 

汉化 https://blog.youkuaiyun.com/Gekkoou/article/details/80956191

 

可以通过kibana关注elasticsearch中的数据；参照https://www.jianshu.com/p/4d65ed957e62

 

6、logstash-input-jdbc同步MySQL数据到Elasticsearch

参照 https://blog.youkuaiyun.com/qq_16436555/article/details/91360718

参照 https://blog.youkuaiyun.com/huiyunfei/article/details/89496407

目前是全量同步，索引数据每分钟刷新一次，增量同步怎么处理？？

 

增量同步，需要调整.conf文件，以及修改查询语句，

 

SELECT id,title, create_time,content FROM t_blog WHERE id > :sql_last_value

其中这里的id必须是整型的，否则station_parameter.txt 记录的值不会更新

参见https://blog.youkuaiyun.com/opera95/article/details/78553743

 

7、springboot集成elasticsearch

@Document 注解会自动创建索引；

参照 https://blog.youkuaiyun.com/huiyunfei/article/details/89496407

 

8、logstash如果不用默认索引类型doc,那就指定output类型  document_type => "esblog"

常用命令：

 

9、repositories.enabled: true 什么含义？？

初始化ElasticsearchRepository，否则不能引用

 

10、jdbc同步过来的document结构比 java api 插进来的多3个字段

@timestamp @version type ？？？

 

11、Java API ElasticsearchRepository 支持根据单个字段模糊查询

接口只要继承 ElasticsearchRepository 类，比如 CRUD 和搜索相关的实现。类似于 JPA 读取数据，参照 https://www.cnblogs.com/Alandre/p/7055838.html

https://www.cnblogs.com/guozp/p/8686904.html

1. 分词的原理

 

 

 

13、Elasticsearch Java Client 版本区别及起步(5.X 和6.X)

 

参见 https://blog.youkuaiyun.com/weixin_30568591/article/details/95162651

 

1. logstash 同步MySQL时，updated_at > '2020-02-22 21:51:54'  时间字段比数据库多8个小时；

原因： #use_column_value => true 必须注释掉；

 

http://localhost:9200/esblog/_search   //查看该索引有哪些数据

http://localhost:9200/esblog/_mapping   //查看该索引的结构

 

PUT http://127.0.0.1:9200/commodity  // 新建索引 详细可参考 https://www.cnblogs.com/quanxiaoha/p/11532487.html