平時(shí)使用Elasticsearch的時(shí)候，偶爾會(huì)在Kibana中使用Query DSL來查詢數(shù)據(jù)蹋嵌。每次要用到Query DSL時(shí)都基本忘光了育瓜，需要重新在回顧一遍，最近發(fā)現(xiàn)Elasticsearch已經(jīng)支持SQL查詢了（6.3版本以后）栽烂，整理了下其用法躏仇，希望對(duì)大家有所幫助！
簡(jiǎn)介
Elasticsearch SQL是一個(gè)X-Pack組件愕鼓，它允許針對(duì)Elasticsearch實(shí)時(shí)執(zhí)行類似SQL的查詢钙态。無論使用REST接口，命令行還是JDBC菇晃，任何客戶端都可以使用SQL對(duì)Elasticsearch中的數(shù)據(jù)進(jìn)行原生搜索和聚合數(shù)據(jù)册倒。可以將Elasticsearch SQL看作是一種翻譯器磺送，它可以將SQL翻譯成Query DSL驻子。
Elasticsearch SQL具有如下特性：
- 原生支持：Elasticsearch SQL是專門為Elasticsearch打造的灿意。
- 沒有額外的零件：無需其他硬件，處理器崇呵，運(yùn)行環(huán)境或依賴庫即可查詢Elasticsearch缤剧，Elasticsearch SQL直接在Elasticsearch內(nèi)部運(yùn)行。
- 輕巧高效：Elasticsearch SQL并未抽象化其搜索功能域慷，相反的它擁抱并接受了SQL來實(shí)現(xiàn)全文搜索荒辕，以簡(jiǎn)潔的方式實(shí)時(shí)運(yùn)行全文搜索。
學(xué)前準(zhǔn)備
學(xué)習(xí)之前我們需要先對(duì)Elasticsearch有所了解犹褒，并安裝好Elasticsearch和Kibana抵窒，這里安裝的是7.6.2版本
安裝完成后在Kibana中導(dǎo)入測(cè)試數(shù)據(jù)，數(shù)據(jù)地址：https://github.com/macrozheng/mall-learning/blob/master/document/json/accounts.json
直接在Kibana的Dev Tools中運(yùn)行如下命令即可：
第一個(gè)SQL查詢
我們使用SQL來查詢下前10條記錄叠骑，可以通過format參數(shù)控制返回結(jié)果的格式李皇，txt表示文本格式，看起來更直觀點(diǎn)宙枷，默認(rèn)為json格式掉房。
在Kibana的Console中輸入如下命令：
POST /_sql?format=txt
{
"query": "SELECT account_number,address,age,balance FROM account LIMIT 10"
}
將SQL轉(zhuǎn)化為DSL
當(dāng)我們需要使用Query DSL時(shí)慰丛，也可以先使用SQL來查詢卓囚，然后通過Translate API轉(zhuǎn)換即可。
例如我們翻譯以下查詢語句：
POST /_sql/translate
{
"query": "SELECT account_number,address,age,balance FROM account WHERE age>32 LIMIT 10"
}
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
}
SQL和ES對(duì)應(yīng)關(guān)系
![]()
image.png
常用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 "
}
`GROUP BY
我們可以使用GROUP BY語句對(duì)數(shù)據(jù)進(jìn)行分組，統(tǒng)計(jì)出分組記錄數(shù)量恤左，最大age和平均balance等信息贴唇，查詢語句如下。
POST /_sql?format=txt
{
"query": "SELECT state,COUNT(*),MAX(age),AVG(balance) FROM account GROUP BY state LIMIT 10"
}
HAVING
我們可以使用HAVING語句對(duì)分組數(shù)據(jù)進(jìn)行二次篩選飞袋，比如篩選分組記錄數(shù)量大于15的信息戳气，查詢語句如下。
POST /_sql?format=txt
{
"query": "SELECT state,COUNT(*),MAX(age),AVG(balance) FROM account GROUP BY state HAVING COUNT(*)>15 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中特有的，當(dāng)使用MATCH或QUERY函數(shù)時(shí)寇僧，會(huì)啟用全文搜索功能摊腋，SCORE函數(shù)可以用來統(tǒng)計(jì)搜索評(pí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目錄下。
使用如下命令啟動(dòng)SQL CLI：
elasticsearch-sql-cli http://localhost:9200
然后直接輸入SQL命令即可查詢了馒铃，注意要加分號(hào)蟹腾。
SELECT account_number,address,age,balance FROM account LIMIT 10;
局限性
使用SQL查詢ES有一定的局限性，沒有原生的Query DSL那么強(qiáng)大区宇，對(duì)于嵌套屬性和某些函數(shù)的支持并不怎么好娃殖，但是平時(shí)用來查詢下數(shù)據(jù)基本夠用了。
參考資料
官方文檔：https://www.elastic.co/guide/en/elasticsearch/reference/7.6/xpack-sql.html
