第七章 ES高级搜索—聚集查询（下）

今天药忘吃喽~ 2023-03-01 03:15 3阅读 0赞

# **一、管道聚集** #

管道聚集不是直接从索引中读取文档，而是在其他聚集的基础上再进行聚集运算。所以管道聚集可以理解为是在聚集结果上再次做聚集运算，比如求聚集结果中多个桶中某一指标的平均值、最大值等。要实现这样的目的，管道聚集都会包含一个名为buckets\_path的参数，用于指定访问其他桶中指标值的路径。buckets\_ path参数的值由三部分组成，即聚集名称、指标名称和分隔符。

聚集名称与聚集名称之间的分隔符是“>”，而聚集名称与指标名称之间的分隔符使用“.”。 按管道聚集运算来源分类，管道聚集可以分为基于父聚集结果和基于兄弟聚集结果两类。前者使用父聚集的结果并将运算结果添加到父聚集结果中，后者则使用兄弟聚集的结果并且结果会展示在自己的聚集结果中。

## **1、基于兄弟聚集**  ##

其于兄弟聚集的管道聚集包括avg\_bucket、max\_ bucket、min\_bucket、sum \_bucket、 stats\_bucket、extended\_ stats\_ bucket、percentiles\_bucket七种。如果将它们名称中的bucket去除，它们就与本章前面介绍的部分指标聚集同名了。事实上，它们不仅在名称上接近，而且在功能上也类似，只是聚集运算的范围由 整个文档变成了另一个聚集结果。以avg\_bucket为例， 它的作用是计算兄弟聚集结果中某一指标的平均值：

> POST /kibana\_sample\_data\_flights/\_search?filter\_path=aggregations
> 
> \{
> 
>   "aggs": \{
> 
>     "carriers": \{ 
> 
>       "terms": \{
> 
>         "field": "Carrier",
> 
>         "size": 10
> 
>       \},
> 
>       "aggs": \{
> 
>         "carrier\_stat": \{ 
> 
>           "stats": \{
> 
>             "field": "AvgTicketPrice"
> 
>           \}
> 
>         \}
> 
>       \}
> 
>     \},
> 
>     "all\_stat": \{
> 
>       "avg\_bucket": \{ 
> 
>         "buckets\_path": "carriers>carrier\_stat.avg"
> 
>       \}
> 
>     \}
> 
>   \}
> 
> \}

例中，最外层包含有两个名称分别为carriers和all\_stat的聚集，这两个聚集就是兄弟关系。 carries聚集是一个针对Carrier字段的terms聚集，Carrier字段保存的是航 班承运航空公司，所以这个聚集的作用是按航空公司将航班分桶。在这个聚集中嵌套了一个名为carrier\_stat的聚集，它是一个针对AvgTicketPrice字段的stats聚集，会按桶计算票价的最小值、平均值等统计数据。

all\_ stat聚集则是一个avg\_bucket管道聚集，在它的Path参数中指定了运算平均值的路径" carriers>carrier\_stat.avg "，即从兄弟聚集中查找carrier\_stat指标聚集，然后再用其中的avg字段参与平均值计算。 所以all\_ stat这计算出来的是四个航空公司平均票价的平均值，实际上就是所有航班的平均票价。尽管示例针对avg\_bucket管道聚集的检索，但使用其余六种基于兄弟的管道聚集的关键字值替换后，它们就变成了另一种合法的管道聚集了。

## **2、基于父聚集** ##

基于父聚集的管道聚集包括moving\_avg、moving\_fn、bucket\_script、bucket\_selector、bucket\_sort、derivative、cumulative\_sum、serial\_diff八种。

### **2.1、滑动窗口** ###

moving\_avg和moving\_fn这两种管道聚集的运算机制相同，都是基于滑动窗口( Siding Window)算法对父聚集的结果做新的聚集运算。滑动窗口算法使用一个具有固定宽度的窗口滑过一组数据， 在滑动的过程中对落在窗口内的数据做运算。moving\_avg管道聚集是对落在窗口内的父聚集结果做平均值运算，而moving\_fn管道聚集则可以对落在窗口内的父聚集结果做各种自定义的运算。

由于moving\_avg管道可以使用moving\_fn管道聚集实现，所以moving\_avg在Elaticearch版本6.4.0中已经被废止。由于使用滑动窗口运算时每次移动1个位置，这就要求moving\_avg和moving\_fn所在聚集桶与桶间隔必须固定，所以这两种管道聚集只能在histogam和date\_histogam聚集中使用：

> POST /kibana\_sample\_data\_flights/\_search?filter\_path=aggregations
> 
> \{
> 
>   "aggs": \{
> 
>     "day\_price": \{ 
> 
>       "date\_histogram": \{
> 
>         "field": "timestamp",
> 
>         "interval": "day"
> 
>       \},
> 
>       "aggs": \{
> 
>         "avg\_price": \{ 
> 
>           "avg": \{
> 
>             "field": "AvgTicketPrice"
> 
>           \}
> 
>         \},
> 
>         "smooth\_price": \{
> 
>           "moving\_fn": \{ 
> 
>             "buckets\_path": "avg\_price",
> 
>             "window": 10,
> 
>             "script": "MovingFunctions.unweightedAvg(values)"
> 
>           \}
> 
>         \}
> 
>       \}
> 
>     \}
> 
>   \}
> 
> \}

在示例中，最外层的父聚集day\_price是1个date\_histogam桶型聚集，它根据文档的timestamp字段按天将文档分桶。day\_price聚集包含avg\_price和smooth\_price两个子聚集，其中avg\_price聚集是一个求AvgTicketPrice字段在1个桶内平均值的avg聚集，而smooth\_price则是一个使用滑动窗口做平均值平滑的管道聚集，窗口宽度由参数window设置为10，默认值为5。

通过返回结果比较avg\_price与smooth\_price就会发现，后者由于经过了滑动窗口运算，数据变化要平滑得多。moving\_fn聚集包含一个用于指定运算脚本的script参数，在脚本中可以通过values访问buckets\_path参数指定的指标值。moving\_fn还内置了一个MovingFunctions类，包括多个运算函数：

*  max()
 *  min( )
 *  sum( )
 *  stdDev( ) 标准偏差
 *  unweightedAvg( ) 无加权平均值
 *  linearWeightedAvg( ) 线性加权移动平均值
 *  ewma( ) 指数加权移动平均值
 *  holt( ) 二次指数加权移动平均值
 *  holtWinters( ) 三次指数加权移动平均值

### **2.2、单桶运算** ###

目前我们学习的管道聚集会对父聚集结果中落在窗口内的多个桶做聚集运算，而bucket\_script、bucket\_selector、bucket\_sort这三个管道聚集则会针对父聚集结果中的每一个桶做单独的运算。其中，bucket\_script会对每个桶执行一段脚本， 运算结果会添加到父聚集的结果中，bucket\_selector同样也是执行一段脚本，但它执行的结果一定是布尔类型， 并且决定当前桶是否出现在父聚集的结果中；bucket\_sort则根据每桶中的具体指标值决定桶的次序。下面通过示例来说明这三种管道聚集的具体用法：

> POST /kibana\_sample\_data\_flights/\_search?filter\_path=aggregations
> 
> \{
> 
>   "aggs": \{
> 
>     "date\_price\_diff": \{
> 
>       "date\_histogram": \{
> 
>         "field": "timestamp",
> 
>         "fixed\_interval": "1d"
> 
>       \},
> 
>       "aggs": \{
> 
>         "stat\_price\_day": \{
> 
>           "stats": \{
> 
>             "field": "AvgTicketPrice"
> 
>           \}
> 
>         \},
> 
>         "diff": \{
> 
>           "bucket\_script": \{ 
> 
>             "buckets\_path": \{ 
> 
>               "max\_price": "stat\_price\_day.max",
> 
>               "min\_price": "stat\_price\_day.min"
> 
>             \},
> 
>             "script": "params.max\_price - params.min\_price"
> 
>           \}
> 
>         \},
> 
>         "gt990": \{
> 
>           "bucket\_selector": \{ 
> 
>             "buckets\_path": \{ 
> 
>               "max\_price": "stat\_price\_day.max",
> 
>               "min\_price": "stat\_price\_day.min"
> 
>             \},
> 
>             "script": "params.max\_price - params.min\_price > 990"
> 
>           \}
> 
>         \},
> 
>         "sort\_by": \{
> 
>           "bucket\_sort": \{ 
> 
>             "sort": \[
> 
>               \{
> 
>                 "diff": \{
> 
>                   "order": "desc"
> 
>                 \}
> 
>               \}
> 
>             \]
> 
>           \}
> 
>         \}
> 
>       \}
> 
>     \}
> 
>   \}
> 
> \}

在示例中同时应用这三种管道聚集，它们的聚集名称分别为diff、gt990和sort\_by。最外层的date\_price\_diff聚集是一个以天为固定间隔的date\_histogram聚集，其中又嵌套了包括上述三个聚集在内的子聚集。

其中，stat\_price\_day是一个根据AvgTicketPrice字段生成统计数据的stats聚集。diff是一个 bucket\_script管道聚集，它的作用是向最终聚集结果中添加 代表最大值与最小值之差的diff字段。它通过buckets\_path定义了两个参数max\_price和min\_price，并在script参数中通过脚本计算了这两个值的差作为最终结果，而这个结果将出现在整个聚集结果中。

gt990是一个bucket\_selector管道聚集，它的作用是筛选哪些桶可以出现在最终的聚集结果中。它也在buckets\_path中定义了相同的参数，不同的是它的script参数运算的不是差值，而是差值是否大于990，即“ params.max\_price - params.min\_price > 990 "。如果差值大于990即运算结果为true，那么当前桶将被选取到结果中，否则当前桶将不能在结果中出现。

sort\_by是一个bucket\_sort管道聚集，它的作用是给最终的聚集结果排序。它通过sort参数接收一组排序对象，在示例中是使用diff聚集的结果按倒序排序。所以示例整体的运算效果就是将那些票价最大值与最小值大于990的桶选取出来，并在桶中添加diff字段保存最大值与最小值的差值，并按diff字段值降序排列。

# **二、矩阵聚集** #

前面几节介绍的聚集都是针对一个字段做聚集运算，而矩阵聚集则是针对多 个字段做多种聚集运算，因此产生的结果是一个矩阵。该类型的聚集比较简单，目前只支持一种针对多个字段做统计运算的矩阵聚集。这个聚集目前还不稳定， 我们不予了解。

# **三、父子关系** #

Elasticsearch中的父子关系是单个索引内部文档与文档之间的一种关系，父文档与子文档同属一个索引并通过父文档id建立联系， 类似于关系型数据库中单表内部行与行之间的自关联，比如有层级关系的部门表中记录之间的关联。

## **1、join类型** ##

在Elasticsearch中并没有外键的概念，文档之间的父子关系通过给索引定义join类型字段实现。例如创建一个员工索引employes，定义个join类型的managemet字段用于确定员工之间的管理与被管理关系：

> PUT employees
> 
> \{
> 
>   "mappings": \{
> 
>     "properties": \{
> 
>       "management": \{ 
> 
>         "type": "join",
> 
>         "relations": \{ 
> 
>           "manager": "member"
> 
>         \}
> 
>       \}
> 
>     \}
> 
>   \}
> 
> \}

在示例中，management字段的数据类型被定义为join，同时在该字段的relations参数中定义父子关系为manager与member，其中manager为父而member为子，它们的名称可由用户自定义。文档在父子关系中的地位，是在添加文档时通过join类型字段指定的。还是以employes索引为例，在向employees索引中添加父文档时，应该将mangement字段设置为manager；而添加子文档时则应该设置为member。具体如下:

> PUT /employees/\_doc/1
> 
> \{
> 
>   "name": "tom",
> 
>   "management": \{ 
> 
>     "name": "manager"
> 
>   \}
> 
> \}
> 
> PUT /employees/\_doc/2?routing=1
> 
> \{
> 
>   "name": "smith",
> 
>   "management": \{ 
> 
>     "name": "member",
> 
>     "parent": "1"
> 
>   \}
> 
> \}
> 
> PUT /employees/\_doc/3?routing=1
> 
> \{
> 
>   "name": "john",
> 
>   "management": \{ 
> 
>     "name": "member",
> 
>     "parent": "1"
> 
>   \}
> 
> \}

在示例中，编号为1的文档其management字段通过name参数设置为manager，即在索引定义父子关系中处于父文档的地位，而编号为2和3的文档其management字段则通过name参数设置为member，并通过parent参数指定 了它的父文档为编号1的文档。

在使用父子关系时，要求父子文档必须要映射到同一分片中，所以在添加子文档时routing参数是必须要设置的。显然父子文档在同一分片可以提升在检索时的性能，可在父子关系中使用的查询方法有has\_child、has\_parent和parent\_id查询，还有parent和children两种聚集。

## **2、has\_child查询** ##

has\_child查询是根据子文档检索父文档的一种方法，它先根据查询条件将满足条件的子文档检索出来，在最终的结果中会返回具有这些子文档的父文档。例如，如果想检索smith的经理是谁，可以：

> POST /employees/\_search
> 
> \{
> 
> "query": \{
> 
> "has\_child":\{
> 
> "type": "member",
> 
> "query":\{
> 
> "match":\{
> 
> "name": "smith"
> 
> \}
> 
> \}
> 
> \}
> 
> \}
> 
> \}

在示例中，has\_child查询的type参数需要设置为父子关系中子文档的名称member，这样has\_child查询父子关系时就限定在这种类型中检索；query参数则设置了查询子文档的条件，即名称为smith。最终结果会根据smith所在文档，通过member对应的父子关系检索它的父文档。

## **3、has\_parent查询** ##

has\_parent查询与has\_child查询正好相反，是通过父文档检索子文档的一种方法。在执行流程上，has\_parent查询先将满足查询条件的父文档检索出来，但在最终返回的结果中展示的是具有这些父文档的子文档。例如，如果想查看tom的所有下属，可以按示例请求：

> POST /employees/\_search
> 
> \{
> 
>   "query": \{
> 
>     "has\_parent": \{ 
> 
>       "parent\_type": "manager",
> 
>       "query": \{ 
> 
>         "match": \{ 
> 
>           "name": "tom"
> 
>         \}
> 
>       \}
> 
>     \}
> 
>   \}
> 
> \}

has\_parent查询在结构上与has\_child查询基本相同，只是在指定父子关系时使用的参数是parent\_type而不是type。

## **4、parent\_id查询** ##

parent\_id查询与has\_parent查询的作用相似，都是根据父文档检索子文档。 不同的是，has\_parent可以通过query参数设置不同的查询条件；而parent\_id查询则只能通过父文档id做检索。例如，查询id为1的子文档：

> POST /employees/\_search
> 
> \{
> 
>   "query": \{
> 
>     "parent\_id": \{ 
> 
>       "type": "member",
> 
>       "id": 1
> 
>     \}
> 
>   \}
> 
> \}

## **5、children聚集** ##

如果想通过父文档检索与其关联的所有子文档就可以使用children聚集。同样以employess索引为例，如果想要查看tom的所有下属就可以按示例的方式检 索:

> POST /employees/\_search?filter\_path=aggregations
> 
> \{
> 
>   "query": \{
> 
>     "term": \{
> 
>       "name": "tom"
> 
>     \}
> 
>   \},
> 
>   "aggs": \{
> 
>     "members": \{
> 
>       "children": \{ 
> 
>         "type": "member"
> 
>       \},
> 
>       "aggs": \{
> 
>         "member\_name": \{
> 
>           "terms": \{
> 
>             "field": "name.keyword",
> 
>             "size": 10
> 
>           \}
> 
>         \}
> 
>       \}
> 
>     \}
> 
>   \}
> 
> \}

在示例中，query参数设置了父文档的查询条件，即名称字段name为tom的文档，而聚集查询members中则使用了children聚集将它的子文档检索出来，同时还使用了一个嵌套聚集member\_name将子文档name字段的词项全部展示出来了。

## **6、parent聚集** ##

parent聚集与children聚集正好相反，它是根据子文档查找父文档，parent聚集在Elasrticsearch版本6.6以后才支持。例如通过name字段为smith的文档，在找该文档的父文档：

> POST /employees/\_search?filter\_path=aggregations
> 
> \{
> 
>   "query": \{
> 
>     "match": \{
> 
>       "name": "smith"
> 
>     \}
> 
>   \},
> 
>   "aggs": \{
> 
>     "who\_is\_manager": \{
> 
>       "parent": \{ 
> 
>         "type": "member"
> 
>       \},
> 
>       "aggs": \{
> 
>         "manager\_name": \{
> 
>           "terms": \{
> 
>             "field": "name.keyword",
> 
>             "size": 10
> 
>           \}
> 
>         \}
> 
>       \}
> 
>     \}
> 
>   \}
> 
> \}

# **四、嵌套类型** #

前面所说的对象类型虽然可按JSON对象格式保存结构化的对象数据，但由于Lucene并不支持对象类型，所以Elastiesearch在存储这种类型的字段时会将它们平铺为单个属性。例如：

> PUT colleges/\_doc/1
> 
> \{
> 
>   "address": \{ 
> 
>     "country": "CN",
> 
>     "city": "BJ"
> 
>   \},
> 
>   "age": 10
> 
> \}

在示例中的colleges文档，address字段会被平铺为address.country和address.city两个字段存储。这种平铺存储的方案在存储单个对象时没有什么问题，但如果在存储数组时会丢失单个对象内部字段的匹配关系。例如：

> PUT colleges/\_doc/2
> 
> \{
> 
>   "address": \[
> 
>     \{
> 
>       "country": "CN",
> 
>       "city": "BJ"
> 
>     \},
> 
>     \{
> 
>       "country": "US",
> 
>       "city": "NY"
> 
>     \}
> 
>   \],
> 
>   "age": 10
> 
> \}

示例中的college文档在实际存储时，会被拆解为“address. country": \["CN.US"\]”和address. city":\["BJ" ,"NY"\]”两个数组字段。这样一来，单个对象内部，country字段和city字段之间的匹配关系就丢失了。换句话说，使用CN与NY作为共同条件检索的文档时，上述文档也会被检索出来，这在逻辑上就出现 了错误：

> POST colleges/\_search
> 
> \{
> 
>   "query": \{
> 
>     "bool": \{ 
> 
>       "must": \[
> 
>         \{ 
> 
>           "match": \{ 
> 
>             "address.country": "CN"
> 
>           \}
> 
>         \},
> 
>         \{ 
> 
>           "match": \{ 
> 
>             "address.city": "NY"
> 
>           \}
> 
>         \}
> 
>       \]
> 
>     \}
> 
>   \}
> 
> \}

在示例中使用了bool组合查询，要求country字段为CN而city字段为NY。这样的文档显然并不存在，但由于数组中的对象被平铺为两个独立的数组字段，文档仍然会被检索出来。

## **1、nested类型** ##

为了解决对象类型在数组中丢失内部字段之间匹配关系的问题，Elasticsearch提供了一种特殊的对象类型nested。这种类型会为数组中的每一个对象创建一个单独的文档，以保存对象的字段信息并使它们可检索。由于这类文档并不直接可见，而是藏置在父文档之中，所以这类文档可以称为为隐式文档或嵌入文档。还是以colleges索引为例，我们把原有的索引删除，将它的address字段设置为nested类型：

> PUT colleges
> 
> \{
> 
>   "mappings": \{
> 
>     "properties": \{
> 
>       "address": \{ 
> 
>         "type": "nested"
> 
>       \},
> 
>       "age": \{ 
> 
>         "type": "integer"
> 
>       \}
> 
>     \}
> 
>   \}
> 
> \}

然后重新存入文档1和2，当字段被设置为nested类型后，再使用原来查询中的bool组合查询就不能检索出来了。这是因为对nested类型字段的检索实际 上是对隐式文档的检索，在检索时必须要将检索路由到隐式文档上，所以必须使用专门的检索方法。也就是说，现在即使将原来查询中的查询条件设置为CN和BJ也不会检索出结果。

nested类型字段可使用的检索方法包括DSL的nested查询，还有聚集查询中的nested和 reverse\_nested两种聚集。

## **2、nested查询** ##

nested查询只能针对nested类型字段，需要通过path参数指定nested类型字段的路径，而在query参数中则包含了针对隐式文档的具体查询条件。例如：

> POST /colleges/\_search
> 
> \{
> 
>   "query": \{
> 
>     "nested": \{ 
> 
>       "path": "address",
> 
>       "query": \{ 
> 
>         "bool": \{
> 
>           "must": \[
> 
>             \{
> 
>               "match": \{
> 
>                 "address.country": "CN"
> 
>               \}
> 
>             \},
> 
>             \{
> 
>               "match": \{
> 
>                 "address.city": "NY"
> 
>               \}
> 
>             \}
> 
>           \]
> 
>         \}
> 
>       \}
> 
>     \}
> 
>   \}
> 
> \}

在示例中再次使用CN与NY共同作为查询条件，但由于使用nested类型后会将数组中的对象转换成隐式文档，所以在nested查询中将不会有文档返回了。将条件更换为CN和BJ，则有文档返回。

## **3、nested聚集** ##

nested聚集是一个单桶聚集，也是通过path参数指定nested字段的路径，包含在path指定路径中的隐式文档都将落入桶中。所以nested字段保存数组的长度就是单个文档落入桶中的文档数量，而整个文档落入桶中的数量就是所有文档nested字段数组长度的总和。有了nested聚集，就可以针对nested数组中的对象做各种聚集运算，例如：

> POST /colleges/\_search?filter\_path=aggregations
> 
> \{
> 
>   "aggs": \{
> 
>     "nested\_address": \{
> 
>       "nested": \{ 
> 
>         "path": "address"
> 
>       \},
> 
>       "aggs": \{
> 
>         "city\_names": \{
> 
>           "terms": \{
> 
>             "field": "address.city.keyword",
> 
>             "size": 10
> 
>           \}
> 
>         \}
> 
>       \}
> 
>     \}
> 
>   \}
> 
> \}

在示例中，nested\_address是一个nested聚集的名称，它会将address字段的隐式文档归入一个桶中。而嵌套在nested\_address聚集中的city\_names聚集则会在这个桶中再做terms聚集运算，这样就将对象中city字段所有的词项枚举出来了。

## **4、reverse\_nested聚集** ##

**reverse\_nested聚集用于在隐式文档中对父文档做聚集，**所以这种聚集必须作为nested聚集的嵌套聚集使用。例如：

> POST /colleges/\_search?filter\_path=aggregations
> 
> \{
> 
>   "aggs": \{
> 
>     "nested address": \{ 
> 
>       "nested": \{ 
> 
>         "path": "address"
> 
>       \},
> 
>       "aggs": \{
> 
>         "city names": \{
> 
>           "terms": \{
> 
>             "field": "address.city.keyword",
> 
>             "size": 10
> 
>           \},
> 
>           "aggs": \{
> 
>             "avg\_age\_in\_city": \{
> 
>               "reverse\_nested": \{\},
> 
>               "aggs": \{ 
> 
>                 "avg\_age": \{ 
> 
>                   "avg": \{ 
> 
>                     "field": "age"
> 
>                   \}
> 
>                 \}
> 
>               \}
> 
>             \}
> 
>           \}
> 
>         \}
> 
>       \}
> 
>     \}
> 
>   \}
> 
> \}

在示例中，city\_ names聚集也是将隐式文档中city字段的词项全部聚集出来。不同的是在这个聚集中还嵌套了一个名为avg\_age\_in\_city的聚集，这个聚集就是个reverse\_ nested聚集。它会在隐式文档中将city字段具有相同词项的文档归入一个桶中，而avg\_age\_in\_city聚集嵌套的另外一个名为avg\_age的聚集，它会 把落入这个桶中文档的age字段的平均值计算出来。所以从总体上来看，这个聚 集的作用就是将在同一城市中大学的平均校龄计算出来。

## **五、使用SQL语言**  ##

Elasticsearch在Basic授权中支持以SQL语句的形式检索文档，SQL语句在 执行时会被翻译为DSL执行。从语法的角度来看，Elasticsearch中的SQL语句与RDBMS中的SQL语句基本一致， 所以对于有数据库编程基础的人来说大大降低了使用Elaticsearch的学习成本。Elasticsearch提供了多种执行SQL语句的方法，可使用类似\_search样的REST接口执行也可以通过命令行执行。它甚至还提供了JDBC和ODBC驱动来执行SQL语句，但JDBC和ODBC属于Platinum (白金版)授权需要付费，所以本小节将只介绍\_sql接口。

## **1、sql接口** ##

在早期版本中，Elasticsearch执行SQL的REST接口为\_xpack/sql，但在版本7以后这个接口已经被废止而推荐使用\_sql接口。例如：

> POST \_sql?format=txt
> 
> \{
> 
> "query":""" 
> 
> select DestCountry, OriginCountry,AvgTicketPrice 
> 
> from kibana\_sample\_data\_flights 
> 
> where Carrier = 'Kibana Airlines' 
> 
> order by AvgTicketPrice desc""" 
> 
> \}

在示例中，\_sql接口通过query参数接收SQL语句，而SQL语句也包含有select、 from、where、 orderby等子句。\_sql接口的URL请求参数format定义了返回结果格式。比如在示例中定义了返回结果格式为txt。除了txt以外，\_sqI接口还支持csv、json、tsv、yaml等等格式。

示例中的请求会将所有航空公同为Kibana Airines的航班文档检索出来，并以文本表格的形式返回。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjYxNDU4_size_16_color_FFFFFF_t_70][]

对于总量比较大的SOL查询，sql接口还支持以游标的形式实现分页。当\_sql接口的请求参数中添加了fetch\_size参数，sql接口在返回结来时就会根据fetch\_size参数设置的大小返回相应的条数，并在返回结果中添加游标标识。具体来说，当请求\_sql接口时设置的form为json时，返回结果中会包含cursor属性；而其他情况下则会在响应中添加Cursor报头。例如还是执行示例中的SOL，但是加入分页支持：

> POST \_sql?format=json
> 
> \{
> 
>   "query": """ 
> 
> select DestCountry, OriginCountry,AvgTicketPrice 
> 
> from kibana\_sample\_data\_flights 
> 
> where Carrier = 'Kibana Airlines' 
> 
> order by AvgTicketPrice desc 
> 
> """,
> 
>   "fetch\_size": 10
> 
> \}

在示例的请求中，为了能够在返回结果中直接看到cursor值，我们将format设置为json，可以看到：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjYxNDU4_size_16_color_FFFFFF_t_70 1][]

> POST \_sql?format=json
> 
> \{ "cursor":"k8OvAwFaAXN4RkdsdVkyeDFaR1ZmWTI5dWRHVjRkRjkxZFdsa0RYRjFaWEo1UVc1a1JtVjBZMmdCRkVWTlZUVmhiazFDYjBGQ1lVVXlNM0ppTjNFeEFBQUFBQUFBQzR3V1QwbHJRV2RrUkdGUlZtbHdTR1pUVTA4dGVtZFpadz09/w8DAWYLRGVzdENvdW50cnkBC0Rlc3RDb3VudHJ5AQdrZXl3b3JkAQAAAWYNT3JpZ2luQ291bnRyeQENT3JpZ2luQ291bnRyeQEHa2V5d29yZAEAAAFmDkF2Z1RpY2tldFByaWNlAQ5BdmdUaWNrZXRQcmljZQEFZmxvYXQAAAABBw=="\}

而在上面的请求中，参数cursor就是第一个请求返回结果中的cursor值，反复执行请求，Elasticsearch就会将第一次请求的全部内容以每次10个的数量全部迭代出来。在请求完所有数据后，应该使用\_sql/close接口将游标关闭以释放资源。

> POST \_sql/close
> 
> \{ "cursor": "k8OvAwFaAXN4RkdsdVkyeDFaR1ZmWTI5dWRHVjRkRjkxZFdsa0RYRjFaWEo1UVc1a1JtVjBZMmdCRkV4alZUWmhiazFDYjBGQ1lVVXlNM0p3Y205eUFBQUFBQUFBQzZrV1QwbHJRV2RrUkdGUlZtbHdTR1pUVTA4dGVtZFpadz09/w8DAWYLRGVzdENvdW50cnkBC0Rlc3RDb3VudHJ5AQdrZXl3b3JkAQAAAWYNT3JpZ2luQ291bnRyeQENT3JpZ2luQ291bnRyeQEHa2V5d29yZAEAAAFmDkF2Z1RpY2tldFByaWNlAQ5BdmdUaWNrZXRQcmljZQEFZmxvYXQAAAABBw=="
> 
> \}

除了fetch\_size以外还有些可以在\_sql接口请求体中使用的参数，如下：

query需要执行的SQL语句，必须要设置的参数。

fetch\_size默认1000，每次返回的行数。

filter默认none，使用DSL 设置过滤器。

request\_timeout默认90s，请求超时时间。

page\_timeout默认45s，分页超时时间。

tume\_zone默认Z，时区。

field\_multi\_value\_leniency默认false，如果一个字段返回多个值时是否忽略。

在这些参数中，filter可以使用DSL对文档做过滤，支持DSL中介绍的所有查询条件。query中的SQL语句在翻译为DSL后，会与filter中的DSL查询语句共同组合到bool查询中。其中SQL语句生成的DSL将出现在must子句，而filter中的DSL则出现在filter子句中。来想要查看SQL语句翻译后的DSL。可以使用\_sql/translate执行相同的请求，在返回结果中就可以看到翻译后的DSL了。

## **2、SQL语法** ##

Elasticsearch支持传统关系型数据库SQL语句中的查询语句，但并不支持DML、DCL句。换句话说，它只支持SELECT语句，不支持INSERT、UPDATE、DELETE语句"。SELECT语句以外，Elasticsearch还支持DESCRIBE和SHOW语句。

### **1.1、SELECT语句** ###

> SELECT语句用于查询文档，基本语法格式如下所示:
> 
> SELECT select \_expr，
> 
> \[ FROM table\_name」
> 
> \[WHERE condtion\]
> 
> \[ GROUP BY grouping\_element\]
> 
> \[ HAVING condition J
> 
> \[ ORDER BY expression \[ASC|DESC\]
> 
> \[ LIMIT\[ count\]\]

通过示例可以看出，Elastiesearch的SELECT语句跟普通SQL几乎没有什么区别，支持SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY及LIMTT子句。

SELECT子句中可以使用星号或文档字段名称列表，FROM子句则指定要检索的索引名称，而WHERE子句则设定了检索的条件。一般的SQL查询使用这三个子句就足够了，而GROUP BY和HAVING子句则用于分组，ORDER BY子句用于排序，而LIMIT一般则可以用于分页。和传统SQL语句非常接近。

### **1.2、DESCRIBE语句** ###

DESCRIBE语句用于查看一个索引的基础信息，在返回结果中一般会包含column、type、mapping三个列，分别对应文档的字段名称、传统数据库类型及 文档字段中的类型。例如要查看索引的基本信息：

> POST \_sql?format=txt
> 
> \{
> 
> "query":"describe kibana\_sample\_data\_flights"
> 
> \}

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjYxNDU4_size_16_color_FFFFFF_t_70 2][]

### **1.3、SHOW语句** ###

SHOW语句包括三种形式，即**SHOW COLUMNS****、****SHOW FUNCTIONS****和****SHOW TABLES**。

**SHOW COLUMNS****用于查看一个索引中的字段情况，**它的作用与DESCRIBE语句完全一样，其至连返回结果都是一样的。

**SHOW FUNCTIONS**用于返回在Elastiesearch SQL中支持的所有函数，返回结果中包括MIN、MAX、COUNT 等常用的聚集函数。

最后，SHOW TABLES用看Elaticecearch中所有的索引。

> POST \_sql?format=txt
> 
> \{
> 
> "query":"show columns in kibana\_sample\_data\_flights" 
> 
> \}
> 
> POST \_sql?format=txt
> 
> \{
> 
> "query":"show functions" 
> 
> \}
> 
> POST \_sql?format=txt
> 
> \{
> 
> "query":"show tables" 
> 
> \}

这三种形式都支持使用LIKE子句过滤返回结果，LIKE子句在用法上与SQL语句中的LIKE类似。例如，“show functions like 'a%"将只返回以a开头的函数。

### **1.4、操作符与函数** ###

Elasticsearch SQL中支持的操作符与函数有100多种，这些操作符大多与普通SQL语言一致，所以这里只介绍一些与普通SQL语句不一样的地方。

先来看一下比较操作符。一般等于比较在SQL中使用等号“=”，这在Elasticsearch SQL中也成立。但是Elasticsearch SQL还引人了另一个等号比较“< = >”，这种等号可以在左值为null时不出现异常。

LIKE操作符，在LIKE子句中可以使用%代表任意多个字符，而使用下划线\_代表单个字符。Elasticsearch SQL不仅支持LIKE子句，还支持通过RLIKE子句以正则表达式的形式做匹配，这大大扩展了SQL语句模糊匹配的能力。

尽管使用LIKE和RLIKE可以实现模糊匹配，但它离全文检索还差得很远。SQL语句的WHERE子句一般都是使用字段整体值做比较，而没有使用词项做匹配的能力。为此Elasticsearch SQL提供了MATCH和OUERY 两个函数，以实现在SQL做全文检索。例如在示例中的两干请求分别使用match和query函数，它们的作用都是检索DestCounty字段为CN的文档：

> POST \_sql?format=txt
> 
> \{
> 
> "query":""" 
> 
> select DestCountry, OriginCountry,AvgTicketPrice,score() 
> 
> from kibana\_sample\_data\_flights 
> 
> where match(DestCountry,'CN') 
> 
> """
> 
> \}
> 
> POST \_sql?format=txt
> 
> \{
> 
> "query":""" 
> 
> select DestCountry, OriginCountry,AvgTicketPrice,score() 
> 
> from kibana\_sample\_data\_flights 
> 
> where query('DestCountry:CN') 
> 
> """
> 
> \}

在示例中的两个请求的select子句中都使用了SCORE函数，它的作用是获取检索的相关度评分值。

Elasticsearch SQL支持传统SQL中的聚集函数，这包括MAX、MIN、AVG、COUNT、SUM等。同时，它还支持一些Elasticsearch特有的聚集函数，这些聚集函数与Elasticsearch聚集查询相对应。这包括 FIRST/FIRST\_VALUE和LAST/LAST VALUE，可用于查看某个字段首个和最后一个非空值；PERCENTILE和PERCENTILE RANK用于百分位聚集，KURTOSIS、SKEWNESS、STDDEV\_ POP、SUM \_OF SQUARES和VAR\_ POP可用于运算其他统计聚集。除了以上这些函数和操作符，Elasticsearch SQL还定义了一组用于日期、数值以及字符串运算的函数。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjYxNDU4_size_16_color_FFFFFF_t_70]: /images/20230209/f0f95c5b9a3d4659a7e8d026e2db4d88.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjYxNDU4_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200721232540789.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjYxNDU4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjYxNDU4_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200721232446602.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NjYxNDU4,size_16,color_FFFFFF,t_70