侧边栏壁纸
博主头像
孔子说JAVA博主等级

成功只是一只沦落在鸡窝里的鹰,成功永远属于自信且有毅力的人!

  • 累计撰写 352 篇文章
  • 累计创建 135 个标签
  • 累计收到 10 条评论

目 录CONTENT

文章目录

ES教程18-ES使用SQL查询的局限性

孔子说JAVA
2022-10-23 / 0 评论 / 0 点赞 / 82 阅读 / 4,586 字 / 正在检测是否收录...
广告 广告

ElasticSearch-SQL(后文简称es-sql)是ElasticSearch的一个插件,使用者可以使用sql进行es数据的查询,但有一定的局限性,没有原生的Query DSL那么强大,对于嵌套属性和某些函数的支持并不怎么好,但是平时用来查询下数据基本够用了。

1、text类型字段限制

text类型的字段是无法用来进行比较、排序、聚合或模糊搜索的,因为它们的实际值(actual value)取决于分词器。因此,ElasticSearch提供了keyword字段用于存储字段的精确值(exact value)。

  • 在大多数情况下,默认设置由多字段(multi field)支持的ElasticSearch的两种字符串类型,即以多种方式索引同一字符串。例如,既将其作为搜索的text索引,又作为对排序和聚合的keyword索引。

  • 由于SQL需要精确的值,因此当遇到text字段时,Elasticsearch SQL将搜索可用于比较、排序和聚合的多字段(multi field)。为此,它将搜索可以找到的未归一化(not normalized )的第一个keyword,并将其用作字段原始的精确值(exact value)。

1.1 错误示例

text类型比较错误示例

POST /_sql?format=txt
{
  "query": " SELECT * FROM city where birthDate > '2000-01-01'  "
}

11111

text类型排序错误示例

POST /_sql?format=txt
{
  "query": " SELECT * FROM city order by name  "   
}

11112

text类型聚合错误示例

POST /_sql?format=txt
{
  "query": " SELECT count(name) FROM city "  
}

11113

1.2 解决方案

创建额外的关键字映射,即对text类型的字段提供多种索引模式,使同一个字段的值,一个分词,一个不分词(为该字段设置不分词的索引方式,即type=keyword)。

PUT test/_mapping
{
 "properties":{
   "id":{
     "type": "text",
     "fields": {
        "keyword": { 
            "type": "keyword"
        }
    }
  }
} 

创建成功后,索引中的字段如下:

image-1666327163058

此时再用错误示例中的SQL语句进行查询操作就不会报错了。

2、substring和聚合

使用substring的情况下做聚合,例如查询生日在2020年2月,并且日期大于2号的人员的平均工资:

POST /_sql?format=txt
{
  "query": "select avg(salary) from city where substring(birthDate,1,7) = '2020-02' and substring(birthDate,9,2) > '02' "    
}

11114

解决方案:

  1. 将使用substring的字段设置为keyword类型

  2. 若字段为text类型,可创建额外的关键字映射,参考 1.2 解决方案

  3. 使用MATCH/QUERY查询

使用MATCH函数查询birthDate为2020-05-01的记录。

POST /_sql?format=txt
{
  "query": "SELECT address,createTime FROM city WHERE MATCH(birthDate,'2020-05-01') LIMIT 10"
}

使用QUERY函数查询birthDate中包含2020的记录。

POST /_sql?format=txt
{
  "query": "SELECT address,createTime FROM city WHERE QUERY('birthDate:2020') LIMIT 10"
}

3、嵌套字段的标量函数

WHERE和ORDER BY子句中不允许使用嵌套字段的标量函数。除比较和逻辑运算符外,Elasticsearch SQL不支持在 WHERE 和 ORDER BY子句中的嵌套字段之上使用标量函数。

如下语句是不支持的:

SELECT * FROM test_emp WHERE LENGTH(dep.dep_name.keyword) > 5;
SELECT * FROM test_emp ORDER BY YEAR(dep.start_date);

下述语句支持:

SELECT * FROM test_emp WHERE dep.start_date >= CAST('2020-01-01' AS DATE) OR dep.dep_end_date IS NULL;

4、多嵌套字段

Elasticsearch SQL不支持多嵌套文档,因此查询不能引用一个索引中的多个嵌套字段。这适用于多级嵌套字段,但也适用于在同一级别上定义的多个嵌套字段。例如下述索引中:

       column         |     type      |    mapping
----------------------+---------------+-------------
nested_A              |STRUCT         |NESTED
nested_A.nested_X     |STRUCT         |NESTED
nested_A.nested_X.text|VARCHAR        |KEYWORD
nested_A.text         |VARCHAR        |KEYWORD
nested_B              |STRUCT         |NESTED
nested_B.text         |VARCHAR        |KEYWORD

nested_A并且nested_B不能同时使用,也不能与nested_A/nested_B和nested_A.nested_X组合使用。在这种情况下,Elasticsearch SQL将显示一条错误消息。

5、归一化keyword字段

Elasticsearch中的keyword字段可以通过定义normalizer来归一化。Elasticsearch SQL不支持此类字段。

6、分页嵌套的内部命中

当选择一个嵌套字段,分页不会按预期起作用,Elasticsearch SQL至少会返回页面大小的记录。这是因为嵌套查询在Elasticsearch中的工作方式:将返回根嵌套字段以及匹配的内部嵌套字段,分页发生在根嵌套文档上,而不是其内部hits上。

7、聚合排序的限制

执行聚合(GROUP BY)时,Elasticsearch SQL依靠 Es 的 composite 聚合来支持分页结果。但这种类型的聚合有局限性:

  1. 排序字段必须是聚合桶中的字段,ES SQL CLI突破了这种限制,但上限不能超过512行,否则在sorting阶段会抛异常。推荐搭配Limit子句使用(指定所需的前N个结果),如:
SELECT * FROM test GROUP BY age ORDER BY COUNT(*) LIMIT 100;
  • 上述语句可能和没有LIMIT的情况下得到相同的查询结果,但是如果超过了最大大小(10000),将返回异常,因为Elasticsearch SQL无法追踪(和排序)所有返回的结果。

此外,在ORDER BY中使用的聚合必须仅是纯聚合函数。不能使用标量函数或运算符,排序条件不支持Scalar函数或者简单的操作符运算。聚合后的复杂字段(比如包含聚合函数)也是不能用在排序条件上的。因此不能使用组合两个或多个聚合函数的复杂列进行排序。以下是一些不允许的查询示例:

SELECT age, ROUND(AVG(salary)) AS avg FROM test GROUP BY age ORDER BY avg;

SELECT age, MAX(salary) - MIN(salary) AS diff FROM test GROUP BY age ORDER BY diff;

8、使用sub-select

在某种程度上,支持使用sub-selects(SELECT X FROM (SELECT Y)):Elasticsearch SQL可以将任何的sub-selects“扁平化”为单个的子选择 。

SELECT * FROM (SELECT first_name, last_name FROM emp WHERE last_name NOT LIKE '%a%') WHERE first_name LIKE 'A%' ORDER BY 1;

  first_name   |   last_name
---------------+---------------
 Alejandro     |McAlpine
 Anneke        |Preusig
 Anoosh        |Peyn
 Arumugam      |Ossenbruggen

上面的查询是可能的,因为它等价于:

SELECT first_name, last_name FROM emp WHERE last_name NOT LIKE '%a%' AND first_name LIKE 'A%' ORDER BY 1;

但是,如果sub-select包含GROUP BY、HAVING或者包含的内容复杂(SELECT比SELECT X FROM (SELECT ...) WHERE [simple_condition]),则当前不支持。

9、在 HAVING 子句中使用 FIRST/LAST聚合函数

不支持在HAVING子句中使用 FIRST 和 LAST ,当目标列是keyword类型时,这同样适用于 MIN 和 MAX,因为它们会在内部转换为FIRST。

10、在GROUP BY中使用 TIME 数据类型或者 HISTOGRAM

当前不支持将TIME数据类型用作分组键。例如:

SELECT count(*) FROM test GROUP BY CAST(date_created AS TIME);

另一方面,如果它用返回另一种数据类型的标量函数包装,则仍然可以使用,例如:

SELECT count(*) FROM test GROUP BY MINUTE((CAST(date_created AS TIME));

HISTOGRAM分组功能当前也不支持TIME 数据类型,例如:

SELECT HISTOGRAM(CAST(birth_date AS TIME), INTERVAL '10' MINUTES) as h, COUNT(*) FROM t GROUP BY h;

11、不支持数组类型的字段

因为在SQL中一个field只对应一个值,这种情况下我们可以使用上面介绍的 SQL To DSL的API 转化为DSL语句,用DSL查询就好了。

12、返回字段的限制

如果一个字段不在source中存储,是无法查询到的。keyword, date, scaled_float, geo_point, geo_shape这些类型的字段不受这种限制,因为他们不是从_source中返回,而是从docvalue_fields中返回。

0

评论区