為什么查詢ElasticSearch用SQL代替DSL

這篇文章主要講解了“為什么查詢ElasticSearch用SQL代替DSL”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“為什么查詢ElasticSearch用SQL代替DSL”吧！

SQL REST API

在Kibana Console中輸入：

POST /_sql?format=txt {   "query": "SELECT * FROM library ORDER BY page_count DESC LIMIT 5" }

將上述SQL替換為你自己的SQL語句，即可。返回格式如下：

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

SQL CLI

elasticsearch-sql-cli是安裝ES時bin目錄的一個腳本文件，也可單獨下載。我們在ES目錄運行

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

輸入sql即可查詢

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

SQL To DSL

在Kibana輸入：

POST /_sql/translate {   "query": "SELECT * FROM library ORDER BY page_count DESC",   "fetch_size": 10 }

即可得到轉化后的DSL query:

{   "size": 10,   "docvalue_fields": [     {       "field": "release_date",       "format": "epoch_millis"     }   ],   "_source": {     "includes": [       "author",       "name",       "page_count"     ],     "excludes": []   },   "sort": [     {       "page_count": {         "order": "desc",         "missing": "_first",         "unmapped_type": "short"       }     }   ] }

因為查詢相關的語句已經生成，我們只需要在這個基礎上適當修改或不修改就可以愉快使用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

語法結構如下：

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

表示從0-N個表中獲取行數據。SQL的執行順序為：

1. 鴻蒙官方戰略合作共建——HarmonyOS技術社區

2. 獲取所有 FROM中的關鍵詞，確定表名。

3. 如果有WHERE條件，過濾掉所有不符合的行。

4. 如果有GROUP BY條件，則分組聚合;如果有HAVING條件，則過濾聚合的結果。

5. 上一步得到的結果經過select_expr運算，確定具體返回的數據。

6. 如果有 ORDER BY條件，會對返回的數據排序。

7. 如果有 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 可列舉出支持的函數名稱和所屬類型。

SHOW FUNCTIONS;        name       |     type -----------------+--------------- AVG              |AGGREGATE COUNT            |AGGREGATE FIRST            |AGGREGATE FIRST_VALUE      |AGGREGATE LAST             |AGGREGATE LAST_VALUE       |AGGREGATE MAX              |AGGREGATE MIN              |AGGREGATE SUM              |AGGREGATE ........

我們主要看下聚合，分組，全文搜索相關的常用函數。

全文匹配函數

MATCH：相當于DSL中的match and multi_match查詢。

MATCH(     field_exp,       --字段名稱     constant_exp,       --字段的匹配值     [, options])       --可選項

使用舉例：

SELECT author, name FROM library WHERE MATCH(author, 'frank');      author     |       name ---------------+------------------- Frank Herbert  |Dune Frank Herbert  |Dune Messiah SELECT author, name, SCORE() FROM library WHERE MATCH('author^2,name^5', 'frank dune');      author     |       name        |    SCORE() ---------------+-------------------+--------------- Frank Herbert  |Dune               |11.443176 Frank Herbert  |Dune Messiah       |9.446629

QUERY：相當于DSL中的 query_string 查詢。

QUERY(     constant_exp      --匹配值表達式     [, options])       --可選項

使用舉例：

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

SCORE()：返回輸入數據和返回數據的相關度relevance.

使用舉例：

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

聚合函數

AVG(numeric_field) ：計算數字類型的字段的平均值。

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：語法如下：

HISTOGRAM(            numeric_exp,    --數字表達式，通常是一個field_name            numeric_interval    --數字的區間值 )  HISTOGRAM(            date_exp,      --date/time表達式，通常是一個field_name            date_time_interval      --date/time的區間值 )

如下返回每年1月1號凌晨出生的數據:

ELECT HISTOGRAM(birth_date, INTERVAL 1 YEAR) AS h, COUNT(*) AS c FROM emp GROUP BY h;              h            |       c ------------------------+--------------- null                    |10 1952-01-01T00:00:00.000Z|8 1953-01-01T00:00:00.000Z|11 1954-01-01T00:00:00.000Z|8 1955-01-01T00:00:00.000Z|4 1956-01-01T00:00:00.000Z|5 1957-01-01T00:00:00.000Z|4 1958-01-01T00:00:00.000Z|7 1959-01-01T00:00:00.000Z|9 1960-01-01T00:00:00.000Z|8 1961-01-01T00:00:00.000Z|8 1962-01-01T00:00:00.000Z|6 1963-01-01T00:00:00.000Z|7 1964-01-01T00:00:00.000Z|4 1965-01-01T00:00:00.000Z|1

ES SQL局限性

因為ES SQL和ES DSL在功能上并非完全匹配，官方文檔提到的SQL局限性有：

大的查詢可能拋ParsingException

在解析階段，極大的查詢會占用過多的內存，在這種情況下，Elasticsearch SQL引擎將中止解析并拋出錯誤。

nested類型字段的表示方法

SQL中不支持nested類型的字段，只能使用

[nested_field_name].[sub_field_name]

這種形式來引用內嵌子字段。

使用舉例：

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

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

如以下SQL都是錯誤的

SELECT * FROM test_emp WHERE LENGTH(dep.dep_name.keyword) > 5;  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子句使用，如：

SELECT * FROM test GROUP BY age ORDER BY COUNT(*) LIMIT 100;

聚合排序的排序條件不支持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;

子查詢的限制

子查詢中包含GROUP BY or HAVING 或者比SELECT X FROM (SELECT ...) WHERE  [simple_condition]這種結構復雜，都是可能執行不成功的。

TIME 數據類型的字段不支持GROUP BY條件和HISTOGRAM函數

如以下查詢是錯誤的：

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

但是將TIME類型的字段包裝為Scalar函數返回是支持GROUP BY的，如：

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

返回字段的限制

如果一個字段不在source中存儲，是無法查詢到的。keyword, date, scaled_float, geo_point,  geo_shape這些類型的字段不受這種限制，因為他們不是從_source中返回，而是從docvalue_fields中返回。

感謝各位的閱讀，以上就是“為什么查詢ElasticSearch用SQL代替DSL”的內容了，經過本文的學習后，相信大家對為什么查詢ElasticSearch用SQL代替DSL這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！