查詢Elasticsearch SQL
更新時間 2024-10-09 21:02:33
最近更新時間: 2024-10-09 21:02:33
分享文章
在6.5.4及之后版本中我們提供Open Distro for Elasticsearch SQL插件允許您使用SQL而不是Elasticsearch查詢域特定語言（DSL）編寫查詢。
如果您已經熟悉SQL并且不想學習DSL查詢，那么此功能是一個很好的選擇。
基本操作
要使用該功能，需要將請求發送到_opendistro/_sqlURI。您可以使用請求參數或請求正文（推薦）。
GET //<host>:<port>/_opendistro/_sql?sql=select * from my-index limit 50
POST //<host>:<port>/_opendistro/_sql
{
"query": "SELECT * FROM my-index LIMIT 50"
}
您還可以使用curl命令：
curl -XPOST //localhost:9200/_opendistro/_sql -u username:password -k -d '{"query": "SELECT * FROM kibana_sample_data_flights LIMIT 10"}' -H 'Content-Type: application/json'
默認情況下，查詢返回JSON，但您也可以選擇CSV格式返回數據，需要對format參數進行設置：
POST _opendistro/_sql?format=csv
{
"query": "SELECT * FROM my-index LIMIT 50"
}
CSV格式返回數據時，每行對應一個文檔，每列對應一個字段。
支持操作
我們支持的SQL操作包括聲明、條件、聚合函數、Include和Exclude、常用函數、連接join和展示等操作。
- 聲明statements
聲明statements
| Statement | Example |
|---|---|
| Select | SELECT * FROM my-index |
| Delete | DELETE FROM my-index WHERE _id=1 |
| Where | SELECT * FROM my-index WHERE ['field']='value' |
| Order by | SELECT * FROM my-index ORDER BY _id asc |
| Group by | SELECT * FROM my-index GROUP BY range(age, 20,30,39) |
| Limit | SELECT * FROM my-index LIMIT 50 (default is 200) |
| Union | SELECT * FROM my-index1 UNION SELECT * FROM my-index2 |
| Minus | SELECT * FROM my-index1 MINUS SELECT * FROM my-index2 |
說明與任何復雜查詢一樣，大型UNION和MINUS語句可能會使集群資源緊張甚至崩潰。
- 條件Conditions
條件Conditions
| Condition | Example |
|---|---|
| Like | SELECT * FROM my-index WHERE name LIKE 'j%' |
| And | SELECT * FROM my-index WHERE name LIKE 'j%' AND age > 21 |
| Or | SELECT * FROM my-index WHERE name LIKE 'j%' OR age > 21 |
| Count distinct | SELECT count(distinct age) FROM my-index |
| In | SELECT * FROM my-index WHERE name IN ('alejandro', 'carolina') |
| Not | SELECT * FROM my-index WHERE name NOT IN ('jane') |
| Between | SELECT * FROM my-index WHERE age BETWEEN 20 AND 30 |
| Aliases | SELECT avg(age) AS Average_Age FROM my-index |
| Date | SELECT * FROM my-index WHERE birthday='1990-11-15' |
| Null | SELECT * FROM my-index WHERE name IS NULL |
- 聚合函數Aggregation
聚合函數Aggregation
| Aggregation | Example |
|---|---|
| avg() | SELECT avg(age) FROM my-index |
| count() | SELECT count(age) FROM my-index |
| max() | SELECT max(age) AS Highest_Age FROM my-index |
| min() | SELECT min(age) AS Lowest_Age FROM my-index |
| sum() | SELECT sum(age) AS Age_Sum FROM my-index |
- Include和Exclude字段
Include和Exclude
| Pattern | Example |
|---|---|
| include() | SELECT include('a*'), exclude('age') FROM my-index |
| exclude() | SELECT exclude('*name') FROM my-index |
- 函數Functions
函數Functions
| Function | Example |
|---|---|
| floor | SELECT floor(number) AS Rounded_Down FROM my-index |
| trim | SELECT trim(name) FROM my-index |
| log | SELECT log(number) FROM my-index |
| log10 | SELECT log10(number) FROM my-index |
| substring | SELECT substring(name, 2,5) FROM my-index |
| round | SELECT round(number) FROM my-index |
| sqrt | SELECT sqrt(number) FROM my-index |
| concat_ws | SELECT concat_ws(' ', age, height) AS combined FROM my-index |
| / | SELECT number / 100 FROM my-index |
| % | SELECT number % 100 FROM my-index |
| date_format | SELECT date_format(date, 'Y') FROM my-index |
說明必須在文檔映射中啟用fielddata才能使大多數字符串函數正常工作。
- 連接操作Joins
連接操作Joins
| Join | Example |
|---|---|
| Inner join | SELECT p.firstname, p.lastname, p.gender, dogs.name FROM people p JOIN dogs d ON d.holdersName = p.firstname WHERE p.age > 12 AND d.age > 1 |
| Left outer join | SELECT p.firstname, p.lastname, p.gender, dogs.name FROM people p LEFT JOIN dogs d ON d.holdersName = p.firstname |
| Cross join | SELECT p.firstname, p.lastname, p.gender, dogs.name FROM people p CROSS JOIN dogs d |
相關約束和限制，參考“連接操作Joins”。
- 展示Show
展示show操作與索引模式匹配的索引和映射。您可以使用*或%使用通配符。
展示show
| Show | Example |
|---|---|
| Show tables like | SHOW TABLES LIKE logs-* |
連接操作Joins
Open Distro for Elasticsearch SQL支持inner joins, left outer joins,和cross joins。Join操作有許多約束：
- 您只能加入兩個參數。
- 您必須為索引使用別名（例如people p）。
- 在ON子句中，您只能使用AND條件。
- 在WHERE語句中，不要將包含多個索引的樹組合在一起。例如，以下語句有效：
WHERE (a.type1 > 3 OR a.type1 < 0) AND (b.type2 > 4 OR b.type2 < -1)
以下聲明無效：
WHERE (a.type1 > 3 OR b.type2 < 0) AND (a.type1 > 4 OR b.type2 < -1)
- 您不能使用GROUP BY或ORDER BY來獲得結果。
- LIMIT和OFFSET不支持一起使用（例如LIMIT 25 OFFSET 25）。
JDBC驅動
Java數據庫連接（JDBC）驅動程序允許您將Open Distro for Elasticsearch與您的商業智能（BI）應用程序集成。
