|
|
|
|
公众号矩阵

查询ElasticSearch:用SQL代替DSL

233酱工作中使用了一点ELK,偶尔使用Kibana拼接ES DSL简直要命。如果你和我一样「熟悉SQL,但不咋会写DSL」 or 「想要用SQL简化查询」,本文会介绍一下官方对ES SQL的支持,希望对你有所帮助~

作者: Monica2333 来源:码农知识点 |2020-09-28 11:09

 

233酱工作中使用了一点ELK,偶尔使用Kibana拼接ES DSL简直要命。如果你和我一样「熟悉SQL,但不咋会写DSL」 or 「想要用SQL简化查询」,本文会介绍一下官方对ES SQL的支持,希望对你有所帮助~

ES7.x版本的x-pack自带ElasticSearch SQL,我们可以直接通过SQL REST API、SQL CLI等方式使用SQL查询。

SQL REST API

在Kibana Console中输入:

  1. POST /_sql?format=txt 
  2.   "query""SELECT * FROM library ORDER BY page_count DESC LIMIT 5" 

将上述SQL替换为你自己的SQL语句,即可。返回格式如下:

  1.     author      |        name        |  page_count   | release_date 
  2. -----------------+--------------------+---------------+------------------------ 
  3. Peter F. Hamilton|Pandora's Star      |768            |2004-03-02T00:00:00.000Z 
  4. Vernor Vinge     |A Fire Upon the Deep|613            |1992-06-01T00:00:00.000Z 
  5. Frank Herbert    |Dune                |604            |1965-06-01T00:00:00.000Z 

SQL CLI

elasticsearch-sql-cli是安装ES时bin目录的一个脚本文件,也可单独下载。我们在ES目录运行

  1. ./bin/elasticsearch-sql-cli https://some.server:9200 

输入sql即可查询

  1. sql> SELECT * FROM library WHERE page_count > 500 ORDER BY page_count DESC
  2.      author      |        name        |  page_count   | release_date 
  3. -----------------+--------------------+---------------+--------------- 
  4. Peter F. Hamilton|Pandora's Star      |768            |1078185600000 
  5. Vernor Vinge     |A Fire Upon the Deep|613            |707356800000 
  6. Frank Herbert    |Dune                |604            |-144720000000 

SQL To DSL

在Kibana输入:

  1. POST /_sql/translate 
  2.   "query""SELECT * FROM library ORDER BY page_count DESC"
  3.   "fetch_size": 10 

即可得到转化后的DSL query:

  1.   "size": 10, 
  2.   "docvalue_fields": [ 
  3.     { 
  4.       "field""release_date"
  5.       "format""epoch_millis" 
  6.     } 
  7.   ], 
  8.   "_source": { 
  9.     "includes": [ 
  10.       "author"
  11.       "name"
  12.       "page_count" 
  13.     ], 
  14.     "excludes": [] 
  15.   }, 
  16.   "sort": [ 
  17.     { 
  18.       "page_count": { 
  19.         "order""desc"
  20.         "missing""_first"
  21.         "unmapped_type""short" 
  22.       } 
  23.     } 
  24.   ] 

因为查询相关的语句已经生成,我们只需要在这个基础上适当修改或不修改就可以愉快使用DSL了。

下面我们详细介绍下ES SQL 支持的SQL语句 和 如何避免错误使用。

首先需要了解下ES SQL支持的SQL语句中,SQL术语和ES术语的对应关系:

ES SQL的语法支持大多遵循ANSI SQL标准,支持的SQL语句有DML查询和部分DDL查询。

DDL查询如:DESCRIBE table,SHOW COLUMNS IN table略显鸡肋,我们主要看下对SELECT,Function的DML查询支持。

SELECT

语法结构如下:

  1. SELECT [TOP [ count ] ] select_expr [, ...] 
  2. FROM table_name ] 
  3. WHERE condition ] 
  4. GROUP BY grouping_element [, ...] ] 
  5. HAVING condition] 
  6. ORDER BY expression [ ASC | DESC ] [, ...] ] 
  7. [ LIMIT [ count ] ] 
  8. [ PIVOT ( aggregation_expr FOR column IN ( value [ [ AS ] alias ] [, ...] ) ) ] 

表示从0-N个表中获取行数据。SQL的执行顺序为:

  1. 获取所有 FROM中的关键词,确定表名。
  2. 如果有WHERE条件,过滤掉所有不符合的行。
  3. 如果有GROUP BY条件,则分组聚合;如果有HAVING条件,则过滤聚合的结果。
  4. 上一步得到的结果经过select_expr运算,确定具体返回的数据。
  5. 如果有 ORDER BY条件,会对返回的数据排序。
  6. 如果有 LIMIT or TOP条件,会返回上一步结果的子集。

与常用的SQL有两点不同,ES SQL 支持TOP [ count ]和PIVOT ( aggregation_expr FOR column IN ( value [ [ AS ] alias ] [, ...] ) )子句。

TOP [ count ] :如SELECT TOP 2 first_name FROM emp表示最多返回两条数据,不可与LIMIT条件共用。

PIVOT子句会对其聚合条件得到的结果进行行转列,进一步运算。这个我是没用过,不做介绍。

FUNCTION

基于上面的SQL我们其实已经能有过滤,聚合,排序,分页功能的SQL了。但是我们需要进一步了解ES SQL中FUNCTION的支持,才能写出丰富的具有全文搜索,聚合,分组功能的SQL。

使用SHOW FUNCTIONS 可列举出支持的函数名称和所属类型。

  1. SHOW FUNCTIONS; 
  2.  
  3.       name       |     type 
  4. -----------------+--------------- 
  5. AVG              |AGGREGATE 
  6. COUNT            |AGGREGATE 
  7. FIRST            |AGGREGATE 
  8. FIRST_VALUE      |AGGREGATE 
  9. LAST             |AGGREGATE 
  10. LAST_VALUE       |AGGREGATE 
  11. MAX              |AGGREGATE 
  12. MIN              |AGGREGATE 
  13. SUM              |AGGREGATE 
  14. ........ 

我们主要看下聚合,分组,全文搜索相关的常用函数。

全文匹配函数

MATCH:相当于DSL中的match and multi_match查询。

  1. MATCH( 
  2.     field_exp,       --字段名称 
  3.     constant_exp,       --字段的匹配值 
  4.     [, options])       --可选项 

使用举例:

  1. SELECT author, name FROM library WHERE MATCH(author, 'frank'); 
  2.  
  3.     author     |       name 
  4. ---------------+------------------- 
  5. Frank Herbert  |Dune 
  6. Frank Herbert  |Dune Messiah 
  7. SELECT author, name, SCORE() FROM library WHERE MATCH('author^2,name^5''frank dune'); 
  8.  
  9.     author     |       name        |    SCORE() 
  10. ---------------+-------------------+--------------- 
  11. Frank Herbert  |Dune               |11.443176 
  12. Frank Herbert  |Dune Messiah       |9.446629 

QUERY:相当于DSL中的 query_string 查询。

  1. QUERY( 
  2.     constant_exp      --匹配值表达式 
  3.     [, options])       --可选项 

使用举例:

  1. SELECT author, name, page_count, SCORE() FROM library WHERE QUERY('_exists_:"author" AND page_count:>200 AND (name:/star.*/ OR name:duna~)'); 
  2.  
  3.       author      |       name        |  page_count   |    SCORE() 
  4. ------------------+-------------------+---------------+--------------- 
  5. Frank Herbert     |Dune               |604            |3.7164764 
  6. Frank Herbert     |Dune Messiah       |331            |3.4169943 

SCORE():返回输入数据和返回数据的相关度relevance.

使用举例:

  1. SELECT SCORE(), * FROM library WHERE MATCH(name'dune'ORDER BY SCORE() DESC
  2.  
  3.     SCORE()    |    author     |       name        |  page_count   |    release_date 
  4. ---------------+---------------+-------------------+---------------+-------------------- 
  5. 2.2886353      |Frank Herbert  |Dune               |604            |1965-06-01T00:00:00Z 
  6. 1.8893257      |Frank Herbert  |Dune Messiah       |331            |1969-10-15T00:00:00Z 

聚合函数

AVG(numeric_field) :计算数字类型的字段的平均值。

  1. SELECT AVG(salary) AS avg FROM emp; 

COUNT(expression):返回输入数据的总数,包括COUNT()时field_name对应的值为null的数据。

COUNT(ALL field_name):返回输入数据的总数,不包括field_name对应的值为null的数据。

COUNT(DISTINCT field_name):返回输入数据中field_name对应的值不为null的总数。

SUM(field_name):返回输入数据中数字字段field_name对应的值的总和。

MIN(field_name):返回输入数据中数字字段field_name对应的值的最小值。

MAX(field_name):返回输入数据中数字字段field_name对应的值的最大值。

分组函数

这里的分组函数是对应DSL中的bucket分组。

HISTOGRAM:语法如下:

  1. HISTOGRAM( 
  2.            numeric_exp,    --数字表达式,通常是一个field_name 
  3.            numeric_interval    --数字的区间值 
  4.  
  5. HISTOGRAM( 
  6.            date_exp,      --date/time表达式,通常是一个field_name 
  7.            date_time_interval      --date/time的区间值 

如下返回每年1月1号凌晨出生的数据:

  1. ELECT HISTOGRAM(birth_date, INTERVAL 1 YEARAS h, COUNT(*) AS c FROM emp GROUP BY h; 
  2.  
  3.  
  4.            h            |       c 
  5. ------------------------+--------------- 
  6. null                    |10 
  7. 1952-01-01T00:00:00.000Z|8 
  8. 1953-01-01T00:00:00.000Z|11 
  9. 1954-01-01T00:00:00.000Z|8 
  10. 1955-01-01T00:00:00.000Z|4 
  11. 1956-01-01T00:00:00.000Z|5 
  12. 1957-01-01T00:00:00.000Z|4 
  13. 1958-01-01T00:00:00.000Z|7 
  14. 1959-01-01T00:00:00.000Z|9 
  15. 1960-01-01T00:00:00.000Z|8 
  16. 1961-01-01T00:00:00.000Z|8 
  17. 1962-01-01T00:00:00.000Z|6 
  18. 1963-01-01T00:00:00.000Z|7 
  19. 1964-01-01T00:00:00.000Z|4 
  20. 1965-01-01T00:00:00.000Z|1 

ES SQL局限性

因为ES SQL和ES DSL在功能上并非完全匹配,官方文档提到的SQL局限性有:

大的查询可能抛ParsingException

在解析阶段,极大的查询会占用过多的内存,在这种情况下,Elasticsearch SQL引擎将中止解析并抛出错误。

nested类型字段的表示方法

SQL中不支持nested类型的字段,只能使用

  1. [nested_field_name].[sub_field_name] 

这种形式来引用内嵌子字段。

使用举例:

  1. SELECT dep.dep_name.keyword FROM test_emp GROUP BY languages; 

nested类型字段不能用在where 和 order by 的Scalar函数上

如以下SQL都是错误的

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

不支持多个nested字段的同时查询

如嵌套字段nested_A和nested_B无法同时使用。

nested内层字段分页限制

当分页查询有nested字段时,分页结果可能不正确。这是因为:ES中的分页查询发生在Root nested document上,而不是它的内层字段上。

keyword类型的字段不支持normalizer

不支持数组类型的字段

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

聚合排序的限制

  • 排序字段必须是聚合桶中的字段,ES SQL CLI突破了这种限制,但上限不能超过512行,否则在sorting阶段会抛异常。推荐搭配Limit子句使用,如:
  1. SELECT * FROM test GROUP BY age ORDER BY COUNT(*) LIMIT 100; 

聚合排序的排序条件不支持Scalar函数或者简单的操作符运算。聚合后的复杂字段(比如包含聚合函数)也是不能用在排序条件上的。

以下是错误例子:

  1. SELECT age, ROUND(AVG(salary)) AS avg FROM test GROUP BY age ORDER BY avg
  2.  
  3. SELECT age, MAX(salary) - MIN(salary) AS diff FROM test GROUP BY age ORDER BY diff; 

子查询的限制

子查询中包含GROUP BY or HAVING 或者比SELECT X FROM (SELECT ...) WHERE [simple_condition]这种结构复杂,都是可能执行不成功的。

TIME 数据类型的字段不支持GROUP BY条件和HISTOGRAM函数

如以下查询是错误的:

  1. SELECT count(*) FROM test GROUP BY CAST(date_created AS TIME); 
  2.  
  3. SELECT HISTOGRAM(CAST(birth_date AS TIME), INTERVAL '10' MINUTES) as h, COUNT(*) FROM t GROUP BY h 

但是将TIME类型的字段包装为Scalar函数返回是支持GROUP BY的,如:

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

返回字段的限制

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

本文转载自微信公众号「 码农知识点」,可以通过以下二维码关注。转载本文请联系 码农知识点公众号。

【编辑推荐】

  1. 如何用C语言操作sqlite3,一文搞懂
  2. 这个MySQL优化原理剖析,比照X光还清楚
  3. MySQL GTID的混合问题修复和思考
  4. Linux下MySQL 8.0安装配置
  5. mybatis如何防止SQL注入?
【责任编辑:武晓燕 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

云原生架构实践

云原生架构实践

新技术引领移动互联网进入急速赛道
共3章 | KaliArch

16人订阅学习

数据中心和VPDN网络建设案例

数据中心和VPDN网络建设案例

漫画+案例
共20章 | 捷哥CCIE

172人订阅学习

搭建数据中心实验Lab

搭建数据中心实验Lab

实验平台Datacenter
共5章 | ITGO(老曾)

111人订阅学习

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微