2018年7月19日工程

Elasticsearch SQL 简介（附示例示例）- 第 2 部分

作者

在《Elasticsearch SQL 简介》系列的第 1 部分中，我们对新的 Elasticsearch SQL 功能以及翻译 API 进行了入门介绍。这篇专栏文章通过探索更复杂的特性来继续这一系列。随后，我们将研究一些用户当前可能遇到的与 SQL 的初始版本相关的限制。最后，我们将透露一下路线图中的计划。

提醒一下，所有读者都可以执行 demo.elastic.co 上提供的任何示例，或者等待数据集在 Kibana 上可用。本系列中使用的所有数据集都已编制索引，相应 SQL 可通过 Kibana 控制台获得。对于每个示例，都提供了到 demo.elastic.co 的链接。或者，在我们的演示 Kibana 控制台中，所有查询都可以被视为单个线程。

复杂例子与 Elasticsearch 的优势

分组

Elasticsearch 的聚合框架能够概括潜在的数十亿个数据点，代表了堆栈中最强大和最流行的功能之一。从功能的角度来看，它与 SQL 中的 GROUP BY 运算符具有天然的等同性。除了提供 GROUP BY 功能的一些示例之外，我们将再次使用翻译 API 来显示等效的聚合。

“按出发地国家/地区查找飞往伦敦的航班的平均飞行时间。按国家/地区的字母顺序排序。”

在 demo.elastic.co 上亲自尝试

sql> SELECT AVG(FlightTimeHour) Avg_Flight_Time, OriginCountry FROM flights GROUP BY OriginCountry ORDER BY OriginCountry LIMIT 5;
 Avg_Flight_Time  | OriginCountry
------------------+---------------
9.342180244924574 |AE
13.49582274385201 |AR
4.704097126921018 |AT
15.081367354940724|AU
7.998943401875511 |CA

通过检查此查询的 DSL，可以看到它使用了复合聚合。

{
  "size": 0,
  "_source": false,
  "stored_fields": "_none_",
  "aggregations": {
    "groupby": {
      "composite": {
        "size": 1000,
        "sources": [
          {
            "3471": {
              "terms": {
                "field": "OriginCountry.keyword",
                "order": "asc"
              }
            }
          }
        ]
      },
      "aggregations": {
        "3485": {
          "avg": {
            "field": "FlightTimeHour"
          }
        }
      }
    }
  }
}

在 demo.elastic.co 上亲自尝试

我们也可以使用函数按在 select 中定义的别名字段分组。

“查找每月航班的数量和平均飞行时间。”

POST _xpack/sql
{
  "query":"SELECT COUNT(*), MONTH_OF_YEAR(timestamp) AS month_of_year, AVG(FlightTimeHour) AS Avg_Flight_Time FROM flights GROUP BY month_of_year"
}

在 demo.elastic.co 上亲自尝试

   COUNT(1)    | month_of_year | Avg_Flight_Time 
---------------+---------------+-----------------
1303           |5              |8.628949653846158
8893           |6              |8.520481551839334
2863           |7              |8.463433805045094

使用复合聚合有一个主要优势，即确保 GROUP BY 的实现可以针对更高的基数字段进行扩展，这提供了一种机制来流式传输特定聚合的所有代码段，类似于滚动浏览文档时的效果。复合聚合还可确保这一实现不会遇到与使用 Term 聚合时相同的内存限制。然而，这确实意味着我们目前无法按度量值对 GROUP BY 进行排序。例如，以下情况会导致错误：

POST _xpack/sql/translate
{
  "query":"SELECT AVG(FlightTimeHour) Avg_Flight_Time, OriginCountry FROM flights GROUP BY OriginCountry ORDER BY Avg_Flight_Time"
}

在 demo.elastic.co 上亲自尝试

组过滤

为了过滤组，我们可以使用 HAVING 运算符，它也可以使用 SELECT 子句中指定的别名。这对于一些 SQL 专家来说可能是常见的。在基于 RDBMS 的实施中，这通常是不可能的，因为 SELECT 是在 HAVING 之后执行的。这里，HAVING 子句使用在执行阶段声明的别名。然而，我们的分析器足够聪明，能够预测未来，并获取声明以在 HAVING 中使用。

“查找每个出发地城市的航班数量、平均距离和第 95 百分位距离，其中平均距离在 3000 到 4000 英里之间。”

在 demo.elastic.co 上亲自尝试

sql> SELECT OriginCityName, ROUND(AVG(DistanceKilometers)) avg_distance, COUNT(*) c, ROUND(PERCENTILE(DistanceKilometers,95)) AS percentile_distance FROM flights GROUP BY OriginCityName HAVING avg_distance BETWEEN 3000 AND 4000;
OriginCityName | avg_distance  |       c       |percentile_distance
---------------+---------------+---------------+-------------------
Verona         |3078           |120            |7927
Vienna         |3596           |120            |7436
Xi'an          |3842           |114            |7964

为了实现 HAVING 功能，SQL Elasticsearch 利用 Bucket Selector 管道聚合，使用参数化 Painless 脚本过滤这些值。请注意下面的实例：如何自动选择 OriginCityName 字段的关键字变体进行聚合，而不是尝试使用标准文本变体，因为没有启用字段数据，这可能会失败。avg和percentile 度量汇总提供了与 SQL 变体等效的功能。

在 demo.elastic.co 上亲自尝试

{
  "size": 0,
  "_source": false,
  "stored_fields": "_none_",
  "aggregations": {
    "groupby": {
      "composite": {
        "size": 1000,
        "sources": [
          {
            "4992": {
              "terms": {
                "field": "OriginCityName.keyword",
                "order": "asc"
              }
            }
          }
        ]
      },
      "aggregations": {
        "4947": {
          "bucket_selector": {
            "buckets_path": {
              "a0": "5010",
              "a1": "5010"
            },
            "script": {
              "source": "params.v0 <= params.a0 && params.a1 <= params.v1",
              "lang": "painless",
              "params": {
                "v0": 3000,
                "v1": 4000
              }
            },
            "gap_policy": "skip"
          }
        },
        "5010": {
          "avg": {
            "field": "DistanceKilometers"
          }
        },
        "5019": {
          "percentiles": {
            "field": "DistanceKilometers",
            "percents": [
              95
            ],
            "keyed": true,
            "tdigest": {
              "compression": 100
            }
          }
        }
      }
    }
  }
}

文本运算符和相关性

与传统的 RDBMS （关系数据库）相比，Elasticsearch 作为搜索引擎的独特功能之一是，通过使用相关性计算考虑文本数据的属性，它能够在简单的是/否之外对匹配进行评分。扩展 SQL 语法允许我们暴露这个功能，并且超越传统 RDBMS 可能提供的功能。

因此，我们引入了两个新的运算符：QUERY（查询）和 MATCH（匹配）。对于熟悉 Elasticsearch 的人来说，这些等同于基础的 multi_match 和 query_string 运算符。Kibana 的用户已经非常熟悉 query _ string 运算符的能力，因为 Kibana 默认搜索栏的背后就是它。它提供智能解析功能，并允许自然语言风格的查询。本博客的内容不包括这两个运算符的详细描述，不过权威指南条目提供了对这些概念的详尽介绍。

例如，考虑以下几点:

“查找2018 - 06 - 06至2018 - 06 - 17期间进出 Kastrup 机场的所有延误航班，按日期排序。”

在 demo.elastic.co 上亲自尝试

Kastrup 机场实际上在哥本哈根，全称是“哥本哈根卡斯特鲁普机场”。使用 QUERY （查询）运算符，我们只需搜索 Kastrup。

sql> SELECT timestamp, FlightNum, OriginCityName, DestCityName FROM flights WHERE QUERY('Kastrup') AND FlightDelay=true AND timestamp > '2018-06-20' AND timestamp < '2018-06-27' ORDER BY timestamp;
       timestamp        |   FlightNum   |OriginCityName | DestCityName
------------------------+---------------+---------------+---------------
2018-06-21T01:46:28.000Z|57SWSLT        |Copenhagen     |Orlando
2018-06-21T07:28:07.000Z|X43J6GE        |Abu Dhabi      |Copenhagen
2018-06-21T13:36:31.000Z|7T04SK7        |Copenhagen     |Milan
2018-06-22T19:52:15.000Z|NXMN87D        |Mumbai         |Copenhagen
2018-06-23T08:05:02.000Z|YXHMDKV        |Copenhagen     |Oslo
2018-06-25T18:21:04.000Z|2R86JEZ        |Copenhagen     |Shanghai
2018-06-26T22:16:10.000Z|TCE99LO        |Copenhagen     |Edmonton

请注意，这里不需要指定字段。简单地使用查询运算符搜索 “kastrup” 就足够了。此外，请注意，我们有延误航班进和出 Kastrup。这里是 Elasticsearch 查询:

在 demo.elastic.co 上亲自尝试

{
  "size": 1000,
  "query": {
    "bool": {
      "filter": [
        {
          "bool": {
            "filter": [
              {
                "bool": {
                  "filter": [
                    {
                      "query_string": {
                        "query": "Kastrup",
                        "fields": [],
                        "type": "best_fields",
                        "default_operator": "or",
                        "max_determinized_states": 10000,
                        "enable_position_increments": true,
                        "fuzziness": "AUTO",
                        "fuzzy_prefix_length": 0,
                        "fuzzy_max_expansions": 50,
                        "phrase_slop": 0,
                        "escape": false,
                        "auto_generate_synonyms_phrase_query": true,
                        "fuzzy_transpositions": true,
                        "boost": 1
                      }
                    },
                    {
                      "term": {
                        "FlightDelay": {
                          "value": true,
                          "boost": 1
                        }
                      }
                    }
                  ],
                  "adjust_pure_negative": true,
                  "boost": 1
                }
              },
              {
                "range": {
                  "timestamp": {
                    "from": "2018-06-20",
                    "to": null,
                    "include_lower": false,
                    "include_upper": false,
                    "boost": 1
                  }
                }
              }
            ],
            "adjust_pure_negative": true,
            "boost": 1
          }
        },
        {
          "range": {
            "timestamp": {
              "from": null,
              "to": "2018-06-27",
              "include_lower": false,
              "include_upper": false,
              "boost": 1
            }
          }
        }
      ],
      "adjust_pure_negative": true,
      "boost": 1
    }
  },
  "_source": {
    "includes": [
      "FlightNum",
      "OriginCityName",
      "DestCityName"
    ],
    "excludes": []
  },
  "docvalue_fields": [
    "timestamp"
  ],
  "sort": [
    {
      "timestamp": {
        "order": "asc"
      }
    }
  ]
}

对于一个刚开始使用 Elasticsearch 的用户来说，这代表了一个相对复杂的查询。我们有一个带有嵌套范围、术语限制和查询字符串运算符的布尔查询。对于从 SQL 迁移应用程序的用户来说，即使在考虑最终查询功能是否正确和最佳之前，这可能一直是一项相当艰巨的任务。实际的 query_string 运算符已经嵌套在过滤器中，因为不需要相关性（我们是按日期排序的），因此允许我们利用过滤器缓存，跳过评分并提高响应时间。

这些运算符的参数也在 SQL 中公开。最后一个示例说明了使用 MATCH 查询在多个字段中使用多个搜索项来限制结果。

“查找往返巴塞罗那的航班，包括巴塞罗那带有闪电的天气”

在 demo.elastic.co 上亲自尝试

出于示例的目的，我们还通过 Score() 函数进行排序并显示相关分数。

sql> SELECT Score(), timestamp, FlightNum, OriginCityName, DestCityName, DestWeather, OriginWeather FROM flights WHERE MATCH('*Weather,*City*', 'Lightning Barcelona', 'type=cross_fields;operator=AND') ORDER BY Score() DESC LIMIT 5;
    SCORE()    |       timestamp        |   FlightNum   |OriginCityName | DestCityName  |    DestWeather    |   OriginWeather
---------------+------------------------+---------------+---------------+---------------+-------------------+-------------------
6.990964       |2018-05-31T06:00:41.000Z|L637ISB        |Barcelona      |Santiago       |Rain               |Thunder & Lightning
6.990964       |2018-06-13T12:23:44.000Z|0GIHB62        |Barcelona      |Buenos Aires   |Clear              |Thunder & Lightning
6.9796515      |2018-06-14T21:04:51.000Z|7GEMEDR        |Barcelona      |Hyderabad      |Thunder & Lightning|Rain
6.9133706      |2018-05-31T01:58:51.000Z|ZTOD7RQ        |Barcelona      |Dubai          |Sunny              |Thunder & Lightning
6.9095163      |2018-06-06T14:02:34.000Z|QSQA5CT        |Barcelona      |Naples         |Rain               |Thunder & Lightning

我们使用通配符模式来指定要匹配的字段，并请求匹配为布尔 AND。交叉字段参数不要求术语全部出现在一个字段中，而是允许它们出现在不同的字段中，前提是两者都存在。给定数据的结构，这对于匹配至关重要。

我们这里的例子回到行对比组。然而，QUERY 和 MATCH 运算符也可以与 GROUP BY 一起使用 —— 有效地过滤聚合到 Elasticsearch。

跨索引搜索及别名

迄今为止，我们的查询只针对单个表/索引。如果我们复制航班索引，通过重新索引请求将文档复制到新的命名版本，我们可以同时查询这两个索引，前提是这两个索引具有相同的映射。映射中的任何差异都会导致查询在分析时出错。为了同时查询多个索引，用户可以将它们添加到 Elasticsearch 别名，或者在 WHERE 子句中使用通配符。例如，假设我有两个索引“航班”和“航班-2”，别名为“f_alias”:

POST /_aliases
{
    "actions" : [
       { "add" : { "index" : "flights-2", "alias" : "f_alias" } },
        { "add" : { "index" : "flights", "alias" : "f_alias" } }
    ]
}

以下内容在逻辑上是等效的：

sql> SELECT FlightNum, OriginCityName, DestCityName, DestWeather, OriginWeather FROM flights* ORDER BY timestamp DESC LIMIT 1;
   FlightNum   |OriginCityName | DestCityName  |  DestWeather  | OriginWeather
---------------+---------------+---------------+---------------+---------------
6UPDDGK        |Zurich         |Zurich         |Rain           |Hail
sql> SELECT FlightNum, OriginCityName, DestCityName, DestWeather, OriginWeather FROM f_alias ORDER BY timestamp DESC LIMIT 1;
   FlightNum   |OriginCityName | DestCityName  |  DestWeather  | OriginWeather
---------------+---------------+---------------+---------------+---------------
6UPDDGK        |Zurich         |Zurich         |Rain           |Hail

在 demo.elastic.co 上尝试。我们的演示环境包含这两个索引，并为用户提供了一个预先配置的别名来测试上述内容。

我们将来可能会放松上述要求，但是现在，这简化了这个初始版本中的逻辑。

JOIN

传统的 RDBMS SQL 中的实施 JOIN 允许来自单独表的行通过单个表格响应中的相关列进行组合。这允许对数据进行关系建模，并且与 Elasticsearch 中本机可用的选项相比，它代表了一个重要的主题。虽然 Elasticsearch SQL 目前没有公开任何 JOIN 运算符，但它确实允许用户利用嵌套文档，这提供了一对多的简单关系建模。对嵌套文档的查询对用户来说是透明的。为了展示这个功能，我们需要一个包含这些数据的索引。出于示例的目的，我们已经将“订单”索引加载到了demo.elastic.co。该索引的文档表示来自电子商务网站的订单，并包含 order_date、billing_city 和 customer_last_name 等字段。此外，“产品”字段包含订单中每个产品的嵌套子文档。例如：

{
          "billing_last_name": "Green",
          "billing_first_name": "Jason",
          "order_id": 519894,
          "products": [
            {
              "tax_amount": 0,
              "taxful_price": 22.99,
              "quantity": 1,
              "taxless_price": 22.99,
              "discount_amount": 0,
              "base_unit_price": 22.99,
              "discount_percentage": 0,
              "product_name": "Briefcase - black",
              "manufacturer": "Pier One",
              "min_price": 11.27,
              "created_on": "2016-11-08T04:16:19+00:00",
              "unit_discount_amount": 0,
              "price": 22.99,
              "product_id": 12733,
              "base_price": 22.99,
              "_id": "sold_product_519894_12733",
              "category": "Men's Accessories",
              "sku": "PI952HA0M-Q11"
            },
            {
              "tax_amount": 0,
              "taxful_price": 16.99,
              "quantity": 1,
              "taxless_price": 16.99,
              "discount_amount": 0,
              "base_unit_price": 16.99,
              "discount_percentage": 0,
              "product_name": "3 PACK - Boxer shorts - white/navy",
              "manufacturer": "Pier One",
              "min_price": 8.33,
              "created_on": "2016-11-08T04:16:19+00:00",
              "unit_discount_amount": 0,
              "price": 16.99,
              "product_id": 18370,
              "base_price": 16.99,
              "_id": "sold_product_519894_18370",
              "category": "Men's Clothing",
              "sku": "PI982AA0Y-A11"
            }
          ],
          "has_error": false,
          "customer_last_name": "Green",
          "currency": "EUR",
          "billing_first_name": "Jason",
          "shipping_country_code": "US",
          "email": "swagelastic@gmail.com",
          "day_of_week": "Tuesday",
          "geoip": {
            "continent_name": "North America",
            "city_name": "New York",
            "country_iso_code": "US",
            "location": {
              "lon": -73.9862,
              "lat": 40.7662
            },
            "region_name": "New York"
          },
          "payment_status": "FULLY_PAID",
          ...
}

通常，查询这些文档需要用户理解为什么我们会对产品字段使用嵌套数据类型，并且还需要理解嵌套查询句法。然而，使用 Elasticsearch SQL，我们能够查询这些嵌套的文档，就好像每个文档都代表一个单独的行及其母文档的字段一样（也就是说，我们有效地将结构展平以便呈现）。考虑上面有两种产品的订单。查询时，当从产品子文档请求字段时，这将显示为两行。如果需要，每行还可以包含母订单的字段。例如：

“查找订单 518894 中航班使用的账单名称和购买的产品。”

sql> SELECT billing_last_name, billing_first_name, products.price, products.product_id FROM orders WHERE order_id=519894;
billing_last_name|billing_first_name|products.price |products.product_id
-----------------+------------------+---------------+-------------------
Green            |Jason             |16.984375      |18370
Green            |Jason             |22.984375      |12733

在 demo.elastic.co 上亲自尝试

_translate API 将显示如何使用嵌套查询构建此查询:

{
  "size": 1000,
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "order_id": {
              "value": 519894,
              "boost": 1
            }
          }
        },
        {
          "nested": {
            "query": {
              "match_all": {
                "boost": 1
              }
            },
            "path": "products",
            "ignore_unmapped": false,
            "score_mode": "none",
            "boost": 1,
            "inner_hits": {
              "ignore_unmapped": false,
              "from": 0,
              "size": 99,
              "version": false,
              "explain": false,
              "track_scores": false,
              "_source": false,
              "stored_fields": "_none_",
              "docvalue_fields": [
                "products.product_id",
                "products.price"
              ]
            }
          }
        }
      ],
      "adjust_pure_negative": true,
      "boost": 1
    }
  },
  "_source": {
    "includes": [
      "billing_last_name",
      "billing_first_name"
    ],
    "excludes": []
  },
  "sort": [
    {
      "_doc": {
        "order": "asc"
      }
    }
  ]
}

在 demo.elastic.co 上亲自尝试

相反，如果我们只查询母字段，我们只能看到一行:

“查找订单 518894 中航班使用的账单名称”

sql> SELECT billing_last_name, billing_first_name FROM orders WHERE order_id=519894;
billing_last_name|billing_first_name
-----------------+------------------
Green            |Jason

在 demo.elastic.co 上亲自尝试

缺了什么和将要发生什么...

您在任何实验中可能会遇到的当前限制:

跨表/索引查询 — 如果索引的映射相同，这是可能的。任何差异当前都会在查询时导致错误。
JOIN — 如上所强调，我们只支持使用嵌套文档的有限 JOIN。
GROUP BY 中的分组排序 — 如上所述，这一限制是从 Elasticsearch 复合聚合继承而来的
嵌套的 SELECT 子句 — 这些是支持深入研究 BI 工具的常用机制。虽然使用多个索引的嵌套 SELECT 语句相当于一个 JOIN，但是使用相同索引的语句可能会被重写和执行。这是我们将来可能会考虑的事情。

我们计划在未来版本中处理的一些 elasticsearch 能力包括:

进行多级 GROUP BY的能力（例如，“按月查找平均延迟超过 2 小时的航班路线（出发地和目的地）
暴露 Elasticsearch 地理空间运算符
INTERVAL 类型支持（即能够根据两个日期和时间之间的差异进行操作。）
增强的数据/时间数学和字符串功能
支持分组中的直方图
跨索引和跨集群的搜索，且映射不相同

感谢您读到这里，请继续关注 Elasticsearch SQL 系列简介中 的后续版本！

Elasticsearch Platform

ELK Stack

Elastic Cloud

可观测性

安全性

搜索

按行业

按解决方案

客户聚焦

开发人员

保持联系

学习

帮助

了解 Elastic 的最新动态

Elasticsearch SQL 简介（附示例示例）- 第 2 部分

复杂例子与 Elasticsearch 的优势

分组

组过滤

文本运算符和相关性

跨索引搜索及别名

JOIN

缺了什么和将要发生什么...

关注我们

关于我们

加入我们

新闻稿

合作伙伴

信任和安全性

投资者关系

卓越奖

关于我们

加入我们

新闻稿

合作伙伴

信任和安全性

投资者关系

卓越奖