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

By
Dale McDiarmid

在《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 能力包括:

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