kibana Dev Tools--查询语句示例
kibana Dev Tools增删改查基础知识：https://www.cnblogs.com/bigfacecat-h/p/14490917.html
kibana Dev Tools查询示例：https://www.cnblogs.com/bigfacecat-h/p/elasticSearch.html
kibana Dev Tools修改示例：https://www.cnblogs.com/bigfacecat-h/p/14498221.html
kibana Dev Tools常用命令：https://www.cnblogs.com/bigfacecat-h/p/14500466.html
一、查询操作
查询语句基本语法
以下语句类似于mysql的： select * from xxx.yyy.topic where 条件1，条件2，...条件N
GET xxx.yyy.topic/logs/_search
{
"query": {
"bool": {
这里面是限制条件，不写则查所有数据
可以包含单个或多个限制条件
}
}
}
select * from xxx.yyy.topic where 条件1
GET xxx.yyy.topic/logs/_search { "query": {
这里只能是单个条件 } }
详细说明：
GET xxx.yyy.topic/logs/_search中
xxx.yyy.topic 对应字段_index 即索引字段 相当于mysql中的数据库名称
logs 对应字段_type 相当于mysql中的表名
_id 相当于mysql中的主键
_search 这表示执行查询操作
_source 相当于mysql表中的列的集合
bool体中是一个或多个基本查询的组合，可在bool里面使用以下参数：
must 文档中必须包含must后的条件
must_not 文档中必须不包含must_not后的条件
should 满足should后的任何一个条件
filter filter后跟过滤条件
1、select * from xxx.yyy.topic
GET xxx.yyy.topic/logs/_search { "query": { "bool": { "must": { "match_all": {} } } } }
或者
GET xxx.yyy.topic/logs/_search { "query": { "bool": {} } }
或者
GET xxx.yyy.topic/logs/_search { "query": { "match_all": {} } }
如果在index下只有一种_type，则在GET中可以不带_type
GET xxx.yyy.topic/_search { "query": { "bool": {} } }
查询仅返回某些字段
GET /index_bigfacecat_test/typeA/_search
{
"query": { "match_all": {} },
"_source": ["name", "address"]
}
通过脚本动态返回新定义字段
GET /index_bigfacecat_test/typeA/_search { "query": { "match_all": {} }, "_source": ["name", "dailywage","workdays"], "script_fields":{ "salary":{ "script":{ "lang":"painless", "source":"doc['workdays'].value * doc['dailywage'].value" } }, "vacation":{ "script":{ "lang":"painless", "source":"params['_source']['workdays'] * 0.3" } } } }
doc['workdays'].value 比 params['_source']['workdays'] 更快,上面代码执行结果图下图
2、 select * from xxx.yyy.topic where login = 'BigFaceCat' and pwd='123'
GET xxx.yyy.topic/logs/_search { "query": { "bool": { "must": [ { "match": { "login.keyword": "BigFaceCat" } }, { "match": { "pwd.keyword": "123" } } ] } } }
must的反查用 must_not ：表示即不是A，也不是B
3、查询语句 select * from xxx.yyy.topic where update_time > 1591200000000 and update_time<1591200000000
select * from xxx.yyy.topic where update_time between 1591200000000 and 1591200000000
GET xxx.yyy.topic/logs/_search
{ "query":{ "bool":{ "must":[ { "range":{ "update_time":{ "gte":1591200000000, "lte":1591362000000 } } } ] } } }
GET xxx.yyy.topic/logs/_search { "query":{ "bool":{ "filter":[ { "range":{ "update_time":{ "gte":1591200000000, "lte":1591362000000 } } } ] } } }
gte :表示 >=
lte : 表示<=
gt : 表示>
lt : 表示<
4、查询语句 select * from xxx.yyy.topic where update_time > 1591200000000 and update_time<1591200000000 and login='BigFaceCat'
{
"query":{
"bool":{
"must":[
{
"range":{
"update_time":{
"gte":1591200000000,
"lte":1591362000000
}
}
},
{
"match":{
"login.keyword":"BigFaceCat"
}
}
]
}
}
}
GET xxx.yyy.topic/logs/_search { "query": { "bool": { "must":{ "match":{"login.keyword":"BigFaceCat"}}, "filter": { "range": { "update_time": { "gte": 1591200000000, "lte": 1591362000000 } } } } } }
5、查询语句 select * from xxx.yyy.topic where login='BigFaceCat' or login='LittlteFaceCat'
GET xxx.yyy.topic/logs/_search { "query": { "bool": { "should": [ { "match": { "login.keyword": "BigFaceCat" }}, { "match": { "login.keyword": "LittlteFaceCat"}} ] } } }
select * from index_bigfacecat_test where sex = 'female' and ( address = '重庆3' or address = '重庆2')
GET /index_bigfacecat_test/typeA/_search { "query":{ "bool":{ "must":[ {"match":{"sex": "female"}}, {"bool": {"should":[ {"match":{"address":"重庆3"}}, {"match":{"address":"重庆2"}} ] } } ] } } }
6、select * from xxx.yyy.topic where login is null
GET xxx.yyy.topic/logs/_search { "query": { "bool": { "must_not": { "exists": { "field": "login" } } } } }
7、select * from xxx.yyy.topic where login is not mull
GET xxx.yyy.topic/logs/_search { "query": { "bool": { "must": { "exists": { "field": "login" } } } } }
GET xxx.yyy.topic/logs/_search { "query": { "bool": { "filter": { "exists": { "field": "login" } } } } }
8、select * from xxx.yyy.topic where login in ('BigFaceCat','LittlteFaceCat')
GET xxx.yyy.topic/logs/_search { "query": { "bool": { "must": [ { "match": { "login.keyword": "BigFaceCat" } }, { "match": { "login.keyword": "LittlteFaceCat" } } ] } } }
GET xxx.yyy.topic/logs/_search { "query": { "bool":{ "filter":{ "terms":{ "login":["BigFaceCat","LittleFaceCat"]} } } } }
GET xxx.yyy.topic/logs/_search
{
"query": {
"bool":{
"should": [
{ "term":{ "login":"BigFaceCat"} },
{ "term":{ "login":"LittleFaceCat"} }
]
}
}
}
terms : 后面可跟多个值
term : 后面只能有一个值
9、select call_id , record_id from xxx.yyy.topic where _id = 'eecd25747'
GET xxx.yyy.topic/logs/_search { "_source": ["call_id","record_id"], "query": { "match": { "_id": "eecd25747a"} } }
select call_id , record_id from xxx.yyy.topic where login='BigFaceCat' and pwd='123'
GET xxx.yyy.topic/logs/_search { "_source": ["call_id","record_id"], "query": { "bool": { "must": [ { "match": { "login.keyword": "BigFaceCat" } }, { "match": { "pwd.keyword": "123" } } ] } } }
10、聚合查询 select sum( talk_duration) as sum_of_talkDuration from xxx.yyy.topic
GET xxx.yyy.topic/logs/_search { "aggs": { "sum_of_talkDuration":{ "sum":{ "field": "talk_duration" } } } }
select sum( talk_duration) as sum_of_talkDuration from xxx.yyy.topic where end_time is not null
GET xxx.yyy.topic/logs/_search { "query": { "bool": { "must": [ {"exists":{"field":"end_time"}} ] } }, "aggs": { "sum_of_talkDuration":{ "sum":{ "field": "talk_duration" } } } }
11、select SUM( DISTINCT talk_duration) as sum_of_diffTalkDuration from xxx.yyy.topic
GET xxx.yyy.topic/logs/_search { "size":0, "aggs": { "sum_of_diffTalkDuration":{ "cardinality":{ "field": "talk_duration" } } } }
12、求平均值 SELECT AVG( record_duration ) as avg_of_talkDurtion FROM xxx.yyy.topic
GET xxx.yyy.topic/logs/_search { "size":0, "aggs": { "avg_of_talkDuration":{ "avg":{ "field": "talk_duration" } } }
13、求最大值 SELECT MAX( record_duration ) as max_of_talkDurtion FROM xxx.yyy.topic
GET xxx.yyy.topic/logs/_search { "size":0, "aggs": { "max_of_talkDuration":{ "max":{ "field": "talk_duration" } } }
14、对查询结果排序 select * from xxx.yyy.topic order by talk_duration desc
GET xxx.yyy.topic/logs/_search { "query": { "bool": { } }, "sort": [{ "talk_duration": "desc" }] }
GET xxx.yyy.topic/logs/_search { "query": { "bool": {} }, "sort": [ { "talk_duration": {"order": "desc"} } ] }
GET /index_bigfacecat_test/typeA/_search { "query": { "match_all": {} }, "_source": ["name", "address","class","age"], "sort":[ { "classa": "asc" }, { "age": "desc"} ] }
基于自定义脚本的排序
GET /index_bigfacecat_test/typeA/_search { "query": { "match_all": {} }, "sort":{ "_script":{ "type":"number", "script":{ "lang":"painless", #脚本语言类型为painless,painless支持Math.log`函数 "source":"doc['age'].value * params.workDays", "params":{ "workDays":5.1 } }, "order" : "asc" } } }
desc : 降序排序
asc : 升序排序
15、分页查询
GET xxx.yyy.topic/logs/_search { "query": { "bool": { } }, "sort": [{ "talk_duration": {"order": "desc"} }], "from": 2, "size": 3 }
from : 起始页
size : 按size条记录分页
如上查询：按每页3条记录分页，返回第2页
16、通过主键_id查询 select * from xxx.yyy.topic where _id = 'AXKRp4hXdhuuEZQaKj7n'
GET xxx.yyy.topic/logs/AXKRp4hXdhuuEZQaKj7n
17、通过主键_id查询某些字段 select phone_number,system_code, extension from xxx.yyy.topic where _id='AXKRp4hXdhuuEZQaKj7'
GET xxx.yyy.topic/logs/AXKRp4hXdhuuEZQaKj7n?_source=phone_number,system_code,extension
18、分组
select sex,count(*) from index_bigfacecat_test group by sex order by count(*);
GET /index_bigfacecat_test/typeA/_search { "query":{ "bool":{ } }, "size":0, "aggs":{ "groupName":{ "terms":{ "field":"sex.keyword" } } } }
按性别分组后求每组的平均工作天数
GET /index_bigfacecat_test/typeA/_search { "query":{ "bool":{ } }, "aggs":{ "groupName":{ "terms":{ "field":"sex.keyword" }, "aggs":{ "avg_workdays":{ "avg":{ "field":"workdays" } } } } } }
