Elasticsearch官方已支持SQL查詢，用起來賊方便！

平時使用Elasticsearch的時候，偶爾會在Kibana中使用Query DSL來查詢數(shù)據(jù)。每次要用到Query DSL時都基本忘光了，需要重新在回顧一遍，最近發(fā)現(xiàn)Elasticsearch已經(jīng)支持SQL查詢了（6.3版本以后），整理了下其用法，希望對大家有所幫助！

簡介

Elasticsearch SQL是一個X-Pack組件，它允許針對Elasticsearch實時執(zhí)行類似SQL的查詢。無論使用REST接口，命令行還是JDBC，任何客戶端都可以使用SQL對Elasticsearch中的數(shù)據(jù)進行原生搜索和聚合數(shù)據(jù)。可以將Elasticsearch SQL看作是一種翻譯器，它可以將SQL翻譯成Query DSL。

Elasticsearch SQL具有如下特性：

• 原生支持：Elasticsearch SQL是專門為Elasticsearch打造的。
• 沒有額外的零件：無需其他硬件，處理器，運行環(huán)境或依賴庫即可查詢Elasticsearch，Elasticsearch SQL直接在Elasticsearch內(nèi)部運行。
• 輕巧高效：Elasticsearch SQL并未抽象化其搜索功能，相反的它擁抱并接受了SQL來實現(xiàn)全文搜索，以簡潔的方式實時運行全文搜索。

學前準備

學習之前我們需要先對Elasticsearch有所了解，并安裝好Elasticsearch和Kibana，這里安裝的是7.6.2版本。

安裝完成后在Kibana中導入測試數(shù)據(jù)，數(shù)據(jù)地址：https://github.com/macrozheng/mall-learning/blob/master/document/json/accounts.json

直接在Kibana的Dev Tools中運行如下命令即可：

第一個SQL查詢

我們使用SQL來查詢下前10條記錄，可以通過format參數(shù)控制返回結(jié)果的格式，txt表示文本格式，看起來更直觀點，默認為json格式。

在Kibana的Console中輸入如下命令：

POST?/_sql?format=txt
{
??"query":?"SELECT?account_number,address,age,balance?FROM?account?LIMIT?10"
}

查詢結(jié)果顯示如下。

將SQL轉(zhuǎn)化為DSL

當我們需要使用Query DSL時，也可以先使用SQL來查詢，然后通過Translate API轉(zhuǎn)換即可。

例如我們翻譯以下查詢語句：

POST?/_sql/translate
{
??"query":?"SELECT?account_number,address,age,balance?FROM?account?WHERE?age>32?LIMIT?10"
}

最終獲取到Query DSL結(jié)果如下。

SQL和DSL混合使用

我們還可以將SQL和Query DSL混合使用，比如使用Query DSL來設(shè)置過濾條件。

例如查詢age在30-35之間的記錄，可以使用如下查詢語句：

POST?/_sql?format=txt
{
??"query":?"SELECT?account_number,address,age,balance?FROM?account",
??????"filter":?{
????????"range":?{
????????????"age":?{
????????????????"gte"?:?30,
????????????????"lte"?:?35
????????????}
????????}
????},
????"fetch_size":?10
}

查詢結(jié)果展示如下：

SQL和ES對應關(guān)系

常用SQL操作

語法

在ES中使用SQL查詢的語法與在數(shù)據(jù)庫中使用基本一致，具體格式如下：

SELECT?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?]?[,?...]?)?)?]

WHERE

可以使用WHERE語句設(shè)置查詢條件，比如查詢state字段為VA的記錄，查詢語句如下。

POST?/_sql?format=txt
{
??"query":?"SELECT?account_number,address,age,balance,state?FROM?account?WHERE?state='VA'?LIMIT?10?"
}

查詢結(jié)果展示如下：

GROUP BY

我們可以使用GROUP BY語句對數(shù)據(jù)進行分組，統(tǒng)計出分組記錄數(shù)量，最大age和平均balance等信息，查詢語句如下。

POST?/_sql?format=txt
{
??"query":?"SELECT?state,COUNT(*),MAX(age),AVG(balance)?FROM?account?GROUP?BY?state?LIMIT?10"
}

HAVING

我們可以使用HAVING語句對分組數(shù)據(jù)進行二次篩選，比如篩選分組記錄數(shù)量大于15的信息，查詢語句如下。

POST?/_sql?format=txt
{
??"query":?"SELECT?state,COUNT(*),MAX(age),AVG(balance)?FROM?account?GROUP?BY?state?HAVING?COUNT(*)>15?LIMIT?10"
}

ORDER BY

我們可以使用ORDER BY語句對數(shù)據(jù)進行排序，比如按照balance字段從高到低排序，查詢語句如下。

POST?/_sql?format=txt
{
??"query":?"SELECT?account_number,address,age,balance,state?FROM?account?ORDER?BY?balance?DESC?LIMIT?10?"
}

DESCRIBE

我們可以使用DESCRIBE語句查看表（ES中為索引）中有哪些字段，比如查看account表的字段，查詢語句如下。

POST?/_sql?format=txt
{
??"query":?"DESCRIBE?account"
}

SHOW TABLES

我們可以使用SHOW TABLES查看所有的表（ES中為索引）。

POST?/_sql?format=txt
{
??"query":?"SHOW?TABLES"
}

支持的函數(shù)

使用SQL查詢ES中的數(shù)據(jù)，不僅可以使用一些SQL中的函數(shù)，還可以使用一些ES中特有的函數(shù)。

查詢支持的函數(shù)

我們可以使用SHOW FUNCTIONS語句查看所有支持的函數(shù)，比如搜索所有帶有DATE字段的函數(shù)可以使用如下語句。

POST?/_sql?format=txt
{
??"query":?"SHOW?FUNCTIONS?LIKE?'%DATE%'"
}

全文搜索函數(shù)

全文搜索函數(shù)是ES中特有的，當使用MATCH或QUERY函數(shù)時，會啟用全文搜索功能，SCORE函數(shù)可以用來統(tǒng)計搜索評分。

MATCH()

使用MATCH函數(shù)查詢address中包含Street的記錄。

POST?/_sql?format=txt
{
??"query":?"SELECT?account_number,address,age,balance,SCORE()?FROM?account?WHERE?MATCH(address,'Street')?LIMIT?10"
}

QUERY()

使用QUERY函數(shù)查詢address中包含Street的記錄。

POST?/_sql?format=txt
{
??"query":?"SELECT?account_number,address,age,balance,SCORE()?FROM?account?WHERE?QUERY('address:Street')?LIMIT?10"
}

SQL CLI

如果你不想使用Kibana來使用ES SQL的話，也可以使用ES自帶的SQL CLI來查詢，該命令位于ES的bin目錄下。

使用如下命令啟動SQL CLI：

elasticsearch-sql-cli?http://localhost:9200

然后直接輸入SQL命令即可查詢了，注意要加分號。

SELECT?account_number,address,age,balance?FROM?account?LIMIT?10;

局限性

使用SQL查詢ES有一定的局限性，沒有原生的Query DSL那么強大，對于嵌套屬性和某些函數(shù)的支持并不怎么好，但是平時用來查詢下數(shù)據(jù)基本夠用了。

參考資料

官方文檔：https://www.elastic.co/guide/en/elasticsearch/reference/7.6/xpack-sql.html