kibana的Dev Tool中如何对es进行增删改查

           kinaba Dev Tool中对es(elasticSearch)举行增删改查

一、查询操作

查询语句基本语法

以下语句类似于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后跟过滤条件

 kibana的Dev Tool中如何对es进行增删改查

 

 

 

 

 

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": {}
  }
}

 

 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"
          }
        }
      ]
    }
  }
}

 

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’

GET xxx.yyy.topic/logs/_search

{

    
“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"}}
      ]
    }
  }
}

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’)

解析HOT原理

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"
      }
    }
    
  }
}

kibana的Dev Tool中如何对es进行增删改查

 kibana的Dev Tool中如何对es进行增删改查

 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"} }
  ]
  
}

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

 

二、修改操作

1、通过查询条件来限制修改局限的方式

update  xxx.yyy.topic  set  result=[{\”aWord\”:\”1哈哈哈哈\”,\”count\”:1,\”locations\”:[\”00:05-00:08\”,\”01:01-01:02\”]}]’

where update_time >= 1591200000000  and update_time <= 1591362000000

 

json串中带有特殊字符”,需要用\举行转义

POST  xxx.yyy.topic/logs/_update_by_query
{
   "query": {
    "bool": {
      "must": [
         {
          "range": {
              "update_time": {
                 "gte": 1591200000000,
                 "lte": 1591362000000
                       }
                   }
         }
        ]
      }
    },
    "script": {
         "source": "ctx._source['result']='[{\"aWord\":\"1哈哈哈哈\",\"count\":1,\"locations\":[\"00:05-00:08\",\"01:01-01:02\"]}]'"
    }
} 

 

 

 

 

2、用主键作作为条件修改的方式

update  xxx.yyy.topic  set  result='[{\”aWord\”:\”1哈哈哈哈\”,\”count\”:1,\”locations\”:[\”00:05-00:08\”,\”01:01-01:02\”]}’

where  _id=’xx-b2fc-43ca-afe7-77e3ff406ff9

 

注重:json中带有特殊字符,需要两个”””包起来


POST xxx.yyy.topic/logs/xx-b2fc-43ca-afe7-77e3ff406ff9/_update
{
   “doc”:{
    ”result”: “””[{“aWord”:”1哈哈哈哈”,”count”:1,”locations”:[“00:05-00:08″,”01:01-01:02”]}]“””
  }
}

 

POST xxx.yyy.topic/logs/xx-b2fc-43ca-afe7-77e3ff406ff9/_update

{  “doc”:{

    "result": "哈哈哈" }
}
 

 

原创文章,作者:28x29新闻网,如若转载,请注明出处:https://www.28x29.com/archives/14633.html