免費(fèi)AI編程助手
文章來(lái)源于公眾號(hào)：碼農(nóng)知識(shí)點(diǎn) ，作者M(jìn)onica2333
ES7.x 版本的 x-pack 自帶 ElasticSearch SQL，我們可以直接通過(guò) SQL REST API、SQL CLI 等方式使用 SQL 查詢(xún)。
SQL REST API
在Kibana Console中輸入：
POST /_sql?format=txt
{
"query": "SELECT * FROM library ORDER BY page_count DESC LIMIT 5"
}將上述 SQL 替換為你自己的 SQL 語(yǔ)句，即可。返回格式如下：
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 時(shí) bin 目錄的一個(gè)腳本文件，也可單獨(dú)下載。我們?cè)?ES 目錄運(yùn)行
./bin/elasticsearch-sql-cli https://some.server:9200輸入 SQL 即可查詢(xún)
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
}即可得到轉(zhuǎn)化后的 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"
}
}
]
}因?yàn)椴樵?xún)相關(guān)的語(yǔ)句已經(jīng)生成，我們只需要在這個(gè)基礎(chǔ)上適當(dāng)修改或不修改就可以愉快使用 DSL 了。
下面我們?cè)敿?xì)介紹下 ES SQL 支持的SQL語(yǔ)句 和 如何避免錯(cuò)誤使用。
首先需要了解下 ES SQL 支持的 SQL 語(yǔ)句中，SQL 術(shù)語(yǔ)和ES術(shù)語(yǔ)的對(duì)應(yīng)關(guān)系：
ES SQL 的語(yǔ)法支持大多遵循 ANSI SQL 標(biāo)準(zhǔn)，支持的 SQL 語(yǔ)句有 DML 查詢(xún)和部分 DDL 查詢(xún)。
DDL 查詢(xún)?nèi)纾?code>DESCRIBE table,SHOW COLUMNS IN table略顯雞肋，我們主要看下對(duì)SELECT,Function的DML查詢(xún)支持。
SELECT
語(yǔ)法結(jié)構(gòu)如下：
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個(gè)表中獲取行數(shù)據(jù)。SQL 的執(zhí)行順序?yàn)椋?/p>
- 獲取所有
FROM中的關(guān)鍵詞，確定表名。 - 如果有
WHERE條件，過(guò)濾掉所有不符合的行。 - 如果有
GROUP BY條件，則分組聚合；如果有HAVING條件，則過(guò)濾聚合的結(jié)果。 - 上一步得到的結(jié)果經(jīng)過(guò)
select_expr運(yùn)算，確定具體返回的數(shù)據(jù)。 - 如果有
ORDER BY條件，會(huì)對(duì)返回的數(shù)據(jù)排序。 - 如果有
LIMITorTOP條件，會(huì)返回上一步結(jié)果的子集。
與常用的SQL有兩點(diǎn)不同，ES SQL 支持
TOP [ count ]和PIVOT ( aggregation_expr FOR column IN ( value [ [ AS ] alias ] [, ...] ) )子句。
TOP [ count ]：如SELECT TOP 2 first_name FROM emp表示最多返回兩條數(shù)據(jù)，不可與LIMIT條件共用。
PIVOT子句會(huì)對(duì)其聚合條件得到的結(jié)果進(jìn)行行轉(zhuǎn)列，進(jìn)一步運(yùn)算。這個(gè)我是沒(méi)用過(guò)，不做介紹。
FUNCTION
基于上面的 SQL 我們其實(shí)已經(jīng)能有過(guò)濾，聚合，排序，分頁(yè)功能的 SQL 了。但是我們需要進(jìn)一步了解 ES SQL 中 FUNCTION 的支持，才能寫(xiě)出豐富的具有全文搜索，聚合，分組功能的 SQL。
使用SHOW FUNCTIONS 可列舉出支持的函數(shù)名稱(chēng)和所屬類(lèi)型。
SHOW FUNCTIONS;
name | type
-----------------+---------------
AVG |AGGREGATE
COUNT |AGGREGATE
FIRST |AGGREGATE
FIRST_VALUE |AGGREGATE
LAST |AGGREGATE
LAST_VALUE |AGGREGATE
MAX |AGGREGATE
MIN |AGGREGATE
SUM |AGGREGATE
........我們主要看下聚合，分組，全文搜索相關(guān)的常用函數(shù)。
全文匹配函數(shù)
MATCH：相當(dāng)于 DSL 中的match and multi_match查詢(xún)。
MATCH(
field_exp, --字段名稱(chēng)
constant_exp, --字段的匹配值
[, options]) --可選項(xiàng)使用舉例：
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：相當(dāng)于 DSL 中的 query_string 查詢(xún)。
QUERY(
constant_exp --匹配值表達(dá)式
[, options]) --可選項(xiàng)使用舉例：
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()：返回輸入數(shù)據(jù)和返回?cái)?shù)據(jù)的相關(guān)度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聚合函數(shù)
AVG(numeric_field) ：計(jì)算數(shù)字類(lèi)型的字段的平均值。
SELECT AVG(salary) AS avg FROM emp;
COUNT(expression)：返回輸入數(shù)據(jù)的總數(shù)，包括COUNT()時(shí)field_name對(duì)應(yīng)的值為null的數(shù)據(jù)。
COUNT(ALL field_name)：返回輸入數(shù)據(jù)的總數(shù)，不包括field_name對(duì)應(yīng)的值為null的數(shù)據(jù)。
COUNT(DISTINCT field_name)：返回輸入數(shù)據(jù)中field_name對(duì)應(yīng)的值不為null的總數(shù)。
SUM(field_name)：返回輸入數(shù)據(jù)中數(shù)字字段field_name對(duì)應(yīng)的值的總和。
MIN(field_name)：返回輸入數(shù)據(jù)中數(shù)字字段field_name對(duì)應(yīng)的值的最小值。
MAX(field_name)：返回輸入數(shù)據(jù)中數(shù)字字段field_name對(duì)應(yīng)的值的最大值。
分組函數(shù)
這里的分組函數(shù)是對(duì)應(yīng) DSL 中的bucket分組。
HISTOGRAM：語(yǔ)法如下：
HISTOGRAM(
numeric_exp, --數(shù)字表達(dá)式，通常是一個(gè)field_name
numeric_interval --數(shù)字的區(qū)間值
)
HISTOGRAM(
date_exp, --date/time表達(dá)式，通常是一個(gè)field_name
date_time_interval --date/time的區(qū)間值
)如下返回每年1月1號(hào)凌晨出生的數(shù)據(jù):
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局限性
因?yàn)?code>ES SQL和ES DSL在功能上并非完全匹配，官方文檔提到的 SQL 局限性有：
大的查詢(xún)可能拋ParsingException
在解析階段，極大的查詢(xún)會(huì)占用過(guò)多的內(nèi)存，在這種情況下，Elasticsearch SQL引擎將中止解析并拋出錯(cuò)誤。
nested類(lèi)型字段的表示方法
SQL 中不支持nested類(lèi)型的字段，只能使用
[nested_field_name].[sub_field_name]這種形式來(lái)引用內(nèi)嵌子字段。 使用舉例：
SELECT dep.dep_name.keyword FROM test_emp GROUP BY languages;nested類(lèi)型字段不能用在where 和 order by 的Scalar函數(shù)上
如以下 SQL 都是錯(cuò)誤的
SELECT * FROM test_emp WHERE LENGTH(dep.dep_name.keyword) > 5;
SELECT * FROM test_emp ORDER BY YEAR(dep.start_date);不支持多個(gè)nested字段的同時(shí)查詢(xún)
如嵌套字段nested_A和nested_B無(wú)法同時(shí)使用。
nested內(nèi)層字段分頁(yè)限制
當(dāng)分頁(yè)查詢(xún)有nested字段時(shí)，分頁(yè)結(jié)果可能不正確。這是因?yàn)椋篍S 中的分頁(yè)查詢(xún)發(fā)生在Root nested document上，而不是它的內(nèi)層字段上。
keyword類(lèi)型的字段不支持normalizer
不支持?jǐn)?shù)組類(lèi)型的字段
這是因?yàn)樵?SQL 中一個(gè)field只對(duì)應(yīng)一個(gè)值，這種情況下我們可以使用上面介紹的 SQL To DSL 的 API 轉(zhuǎn)化為 DSL 語(yǔ)句，用 DSL 查詢(xún)就好了。
聚合排序的限制
- 排序字段必須是聚合桶中的字段，ES SQL CLI突破了這種限制，但上限不能超過(guò)512行，否則在sorting階段會(huì)拋異常。推薦搭配
Limit子句使用，如：
SELECT * FROM test GROUP BY age ORDER BY COUNT(*) LIMIT 100;- 聚合排序的排序條件不支持Scalar函數(shù)或者簡(jiǎn)單的操作符運(yùn)算。聚合后的復(fù)雜字段（比如包含聚合函數(shù)）也是不能用在排序條件上的。
以下是錯(cuò)誤例子：
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;子查詢(xún)的限制
子查詢(xún)中包含GROUP BY or HAVING 或者比SELECT X FROM (SELECT ...) WHERE [simple_condition]這種結(jié)構(gòu)復(fù)雜，都是可能執(zhí)行不成功的。
TIME 數(shù)據(jù)類(lèi)型的字段不支持GROUP BY條件和HISTOGRAM函數(shù)
如以下查詢(xún)是錯(cuò)誤的：
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 類(lèi)型的字段包裝為Scalar函數(shù)返回是支持 GROUP BY 的，如：
SELECT count(*) FROM test GROUP BY MINUTE((CAST(date_created AS TIME));
返回字段的限制
如果一個(gè)字段不在 source 中存儲(chǔ)，是無(wú)法查詢(xún)到的。keyword, date, scaled_float, geo_point, geo_shape這些類(lèi)型的字段不受這種限制，因?yàn)樗麄儾皇菑?code>_source中返回，而是從docvalue_fields中返回。
以上就是W3Cschool編程獅關(guān)于查詢(xún)ElasticSearch：用SQL代替DSL的相關(guān)介紹了，希望對(duì)大家有所幫助。
