Elasticsearch的'sql'

心已赠人 2022-01-26 09:17 182阅读 0赞

## Preface ##

之前已经说过es的一些基本知识，这里将学习一些es的"sql"，也就是es的DSL查询表达式。如同数据的查询语言->sql，es也有自己的查询语言，以json的格式。

在这里，我同样会采用简写的模式，而且会进一步的简写。我接下来的查询都会在\_index为"esb-inparam-2019-04-16"，\_type为"doc"的范围里面，所以，前缀都会是一样的，我只会给出查询表达式。

即类似这样的查询

curl -XGET http://172.22.0.153:9200/esb-inparam-2019-04-16/doc/_search -H 'Content-Type: application/json'
    -d '{"query":{"match":{"head":"sQUByPhoneNo"}}}'

我会简写，只给出表达式：

{"query":{"match":{"head":"sQUByPhoneNo"}}}

## 基本查询表达式 ##

### match ###

match是一个标准的查询，在一个字段上面使用它，它将会匹配该字段的精确值。

如：

{"match":{"head": "sTSNPubSnd2" }}

就是匹配所有的head字段值为sTSNPubSnd2的数据，结果同之前的:

GET /esb-inparam-2019-04-16/doc/_search?pretty&q=head:sQUByPhoneNo

相当于我们在数据库中进行 select *from table where head='sTSNPubSnd2'的查询。*

如果在一个全文字段上使用 `match` 查询，在执行查询前，它将用正确的分析器去分析查询字符串。

比如：

{
        "query": {
            "match": {
                "json_data": "aadmin yjboss"
            }
        }
    }

es会将"aadmin yjboss"这个字符串进行分词分成"aadmin","yjboss"，进而只要"json\_data"中匹配这两个中任意一个则满足条件。

`multi_match`可以在多个字段上匹配给定的值。如：

{"query":{
        "multi_match": {
            "query":    "sTSNPubSnd2",
            "fields":   [ "json_data", "head" ]
        }
    }}

就表示在json\_data或head这两个字段上，有一个字段包含这个值，即匹配该条数据。相当于我们sql的查询语句：

select  from table where json_data='sTSNPubSnd2' or head='sTSNPubSnd2'

### range ###

`range` 查询找出那些落在指定区间内的数字或者时间

如：查询@timestamp这个字段值在2019-04-16 06:00:00~06:30:00 这个区间的数据

{
        "query": {
            "range": {
                "@timestamp": {
                    "gte": "2019-04-16T06:00:00Z",
                    "lt": "2019-04-16T06:30:00.000Z"
                }
            }
        }
    }

tip：es的几种比较符号

*  `gt `大于
 *  `gte `大于等于
 *  `lt `小于
 *  `lte `小于等于

### term ###

`term`就表示精确匹配了。

与match不同的是，当查询值可以被分词时，match会对查询值进行分词，再进行匹配，而term则是完全的精确匹配。

如之前的同match查询head为sTSNPubSnd2的例子，就可以换用term：

{"query":{"term":{"head":"sQUByPhoneNo"}}}

结果是一样的。

使用`terms`可以对多个值进行匹配：如head为sQUByPhoneNo或sTSNPubSnd2的记录

{"query":{"terms":{"head":["sQUByPhoneNo","sTSNPubSnd2"]}}}

就如同在sql里面的：

select  from table where head='sQUByPhoneNo' or head='sTSNPubSnd2'

### exists ###

exists查询被用于查找那些指定字段中有值的文档。

如：查询有字段名为"json\_data"的记录

{
        "query": {
            "exists": {
                "field": "json_data"
            }
        }
    }

*这个就类似于sql的select *from table where json\_data is not null

他可以用于判断要查询的字段是否存在，比如我们不确定是这个字段名到底是json\_data还是jsonData，就可以exists先确认下。

## 查询验证 ##

以上都是一些比较重要的查询语法字段，并且都比较简单。但是，es的语法还是比较严格，可以通过es的验证api->\_validate/query来验证要查询的语句是否合法，加个后缀explain还能给出错误详细信息。验证语句如下：

GET /esb-inparam-2019-04-16/doc/_validate/query?explain {"query":{"head":"sQUByPhoneNo"}}

这句话就是验证 \{"query":\{"head":"sQUByPhoneNo"\}\} 这个查询语句是否正确，如这句话的结果为：

![4c63cda76fcc4ce58d92405254b11a33.png][]

也就是没得head这个查询表达式，需要有个start\_object。

## 排序 ##

通过`sort`可对查询结果进行排序，如同我们再数据库中的order by 关键字。

如对之前的term查询结果的@timestamp进行排序

{
        "query": {
            "term": {
                "head": "sTSNPubSnd2"
            }
        },
        "sort": {
            "@timestamp": {
                "order": "asc"
            }
        }
    }

一个关键字的asc排序，也可直接简写成 `"sort": "timestamp"`。

这个查询就如同sql里面的

select  from table where head='sTSNPubSnd2' order by @timestamp asc

同样也能支持多个字段的排序：

{
        "query": {
            "term": {
                "head": "sTSNPubSnd2"
            }
        },
        "sort": [
            {
                "@timestamp": {
                    "order": "desc"
                }
            },
            {
                "id": {
                    "order": "asc"
                }
            }
        ]
    }

## 部分匹配 ##

如同sql里面有like关键字，es也有类似这样的部分匹配功能。

### prefix ###

`prefix`用于前缀的查询，类似于sql的 like 'xxx%'。

比如：查询所有head 开头为'sTS'的记录

{
        "query":{
             "prefix":{
               "head":"sTS"
             }
        }
      }

### wildcard ###

`wildcard`是模糊匹配，类似于shell里面的匹配符。

上述例子同样可以用wildcard：

{
     "query":{
     "wildcard":{
     "head":"sTS"
             }
        }
      }

### regexp ###

`regexp`就是正则匹配了。

比如：

{
        "query":{
          "regexp":{
            "head":"s[0-9]+."
     }
     }
     }

>  
> 
> tip:
> 
> `prefix` 、 `wildcard` 和 `regexp` 查询是基于词操作的，如果用它们来查询 `analyzed` 字段，它们会检查字段里面的每个词，而不是将字段作为整体来处理。
> 
> 比方说包含 “Quick brown fox” （快速的棕色狐狸）的 `title` 字段会生成词： `quick` 、 `brown` 和 `fox` 。
> 
> 会匹配以下这个查询：
> 
>     { "regexp": { "title": "br." }}
> 
> 但是不会匹配以下两个查询：
> 
>     { "regexp": { "title": "Qu." }}  //在索引里的词是 quick 而不是 Quick 。
>     { "regexp": { "title": "quick br" }}  //quick 和 brown 在词表中是分开的。

## 组合多查询 ##

上述的查询表达式基本上都是比较简单，如同我们的sql的where条件后面仅仅跟了一两个查询条件。现实的查询需求从来都没有那么简单；它们需要在多个字段上查询多种多样的文本，并且根据一系列的标准来过滤。

我们执行一个复杂的sql查询，不管where后面跟再多的and or like等等，无非就是从表里面筛选出符合条件的结果。每一个条件都可以视为一个过滤器，通过层层的过滤，剔除糟粕，进而得到符合条件的结果。

![2f83e2656fd64faeaf5daa1403af3885.png][]

### bool过滤器 ###

一个 `bool` 过滤器由三部分组成：

*  `must` 表示必须匹配，等同sql里面的`and`
 *  `must not` 表示必须不匹配，等同sql里面的`not`
 *  `should` 表示至少有一个语句要匹配，等同sql里面的`or`

当然了，上述这三部分每个都是可选的，当我们需要多个过滤器时，只须将它们置入 `bool` 过滤器的不同部分即可。

比如：

{
      "query":{
        "bool":{
          "must":{
            "term":{
              "head":"sTSNPubSnd2"
            }
          },
          "must_not":{
            "term":{
              "clientip":"10.112.101.42"
            }
          },
          "should":[
            {
              "term":{"id":"000000"}
            },
            {
              "range":{"@timestamp":{"gt":"2019-04-16T06:30:00Z"}}
            }
          ]
        }
      }
    }

等同于sql:

SELECT
      *
    FROM
      TABLE
    WHERE head = 'sTSNPubSnd2'
      AND clientip != '10.112.101.42'
      AND (
        id = '000000'
        OR @timestamp > to_date ('yyyy-MM-ddThi24:mi:ssZ','2019-04-16T06:30:00Z')
      )

bool过滤器同样可以多层嵌套，比如对上述例子进行嵌套：

{
        "query": {
            "bool": {
                "must": [
                    {
                        "bool": {
                            "must_not": {
                                "term": {
                                    "clientip": "10.112.101.42"
                                }
                            }
                        }
                    },
                    {
                        "term": {
                            "head": "sTSNPubSnd2"
                        }
                    }
                ],
                "should": [
                    {
                        "bool": {
                            "must": {
                                "term": {
                                    "id": "000000"
                                }
                            }
                        }
                    },
                    {
                        "range": {
                            "@timestamp": {
                                "gt": "2019-04-16T06:30:00Z"
                            }
                        }
                    }
                ]
            }
        }
    }

[4c63cda76fcc4ce58d92405254b11a33.png]: /images/20220126/0f4c4cf402804479919f42674ffa5681.png
[2f83e2656fd64faeaf5daa1403af3885.png]: /images/20220126/f0430169a7fb42eeb1ffbd5597e7f732.png