查询ElasticSearch：用SQL代替DSL
ES7.x版本的x-pack自帶ElasticSearch SQL，我們可以直接通過SQL REST API、SQL CLI等方式使用SQL查詢。
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.000ZSQL 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????????????|-144720000000SQL 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的執行順序為：
獲取所有 FROM中的關鍵詞，確定表名。
如果有WHERE條件，過濾掉所有不符合的行。
如果有GROUP BY條件，則分組聚合；如果有HAVING條件，則過濾聚合的結果。
上一步得到的結果經過select_expr運算，確定具體返回的數據。
如果有 ORDER BY條件，會對返回的數據排序。
如果有 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 可列舉出支持的函數名稱和所屬類型。
我們主要看下聚合，分組，全文搜索相關的常用函數。
全文匹配函數
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.446629QUERY：相當于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.4169943SCORE()：返回輸入數據和返回數據的相關度relevance.
使用舉例：
聚合函數
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_namenumeric_interval????--數字的區間值 )HISTOGRAM(date_exp,??????--date/time表達式，通常是一個field_namedate_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|1ES SQL局限性
因為ES SQL和ES DSL在功能上并非完全匹配，官方文檔提到的SQL局限性有：
大的查詢可能拋ParsingException
在解析階段，極大的查詢會占用過多的內存，在這種情況下，Elasticsearch SQL引擎將中止解析并拋出錯誤。
nested類型字段的表示方法
SQL中不支持nested類型的字段，只能使用
[nested_field_name].[sub_field_name]這種形式來引用內嵌子字段。
使用舉例：
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子句使用，如：
聚合排序的排序條件不支持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中返回。
有道無術，術可成；有術無道，止于術
歡迎大家關注Java之道公眾號
好文章，我在看??
總結
以上是生活随笔為你收集整理的查询ElasticSearch：用SQL代替DSL的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： MySQL之查询性能优化（四）
- 下一篇： C++学习-环境配置