1。ElasticSearch SQL介紹
ElasticSearch v6。3之後自帶SQL查詢,該SQL模組屬於X_pack的一部分,主要特點如下:
允許在kibana以及elasticsearch中使用 SQL 查詢其中的資料
支援 REST 、 JDBC 以及命令列來來下資料,任何客戶端都可以使用 SQL 在 Elasticsearch 中本地搜尋和聚合資料
要求 ES版本必須為6。3。0及以
命令 說明
DESC table 用來描述索引的欄位屬性
SHOW COLUMNS 功能同上,只是別名
SHOW FUNCTIONS 列出支援的函式列表,支援萬用字元過濾
SHOW TABLES 返回索引列表
SELECT … FROM table_name WHERE … GROUP BY … HAVING … ORDER BY … LIMIT … 用來執行查詢的命令
2。使用例子
2。1 查詢資料 (其他的類似)
_xpack/sql?format=json
{
“query”: “SELECT * FROM ods_iris”
}
1
2
3
4
5
6
返回結果
{
“columns”: [
{
“name”: “name”,
“type”: “text”
}
,
{
“name”: “petallength”,
“type”: “float”
}
,
{
“name”: “petalwidth”,
“type”: “float”
}
,
{
“name”: “sepallength”,
“type”: “float”
}
,
{
“name”: “sepalwidth”,
“type”: “float”
}
],
“rows”: [
[
“Iris-setosa”
,
1。399999976158142
,
0。20000000298023224
,
5。099999904632568
,
3。5
]
,
[
“Iris-setosa”
,
1。399999976158142
,
0。20000000298023224
,
4。900000095367432
,
3
]
,
[
“Iris-setosa”
,
1。2999999523162842
,
0。20000000298023224
,
4。699999809265137
,
3。200000047683716
]
,
[
“Iris-setosa”
,
1。5
,
0。20000000298023224
,
4。599999904632568
,
3。0999999046325684
]
,
[
“Iris-setosa”
,
1。399999976158142
,
0。20000000298023224
,
5
,
3。5999999046325684
]
,
[
“Iris-setosa”
,
1。7000000476837158
,
0。4000000059604645
,
5。400000095367432
,
3。9000000953674316
]
,
[
“Iris-setosa”
,
1。399999976158142
,
0。30000001192092896
,
4。599999904632568
,
3。4000000953674316
]
,
[
“Iris-setosa”
,
1。5
,
0。20000000298023224
,
5
,
3。4000000953674316
]
,
[
“Iris-setosa”
,
1。399999976158142
,
0。20000000298023224
,
4。400000095367432
,
2。9000000953674316
]
,
[
“Iris-setosa”
,
1。5
,
0。10000000149011612
,
4。900000095367432
,
3。0999999046325684
]
,
[
“Iris-setosa”
,
1。5
,
0。20000000298023224
,
5。400000095367432
,
3。700000047683716
]
,
[
“Iris-setosa”
,
1。600000023841858
,
0。20000000298023224
,
4。800000190734863
,
3。4000000953674316
]
,
[
“Iris-setosa”
,
1。399999976158142
,
0。10000000149011612
,
4。800000190734863
,
3
]
,
[
“Iris-setosa”
,
1。100000023841858
,
0。10000000149011612
,
4。300000190734863
,
3
]
,
[
“Iris-setosa”
,
1。2000000476837158
,
0。20000000298023224
,
5。800000190734863
,
4
]
,
[
“Iris-setosa”
,
1。5
,
0。4000000059604645
,
5。699999809265137
,
4。400000095367432
]
,
[
“Iris-setosa”
,
1。2999999523162842
,
0。4000000059604645
,
5。400000095367432
,
3。9000000953674316
]
,
[
“Iris-setosa”
,
1。399999976158142
,
0。30000001192092896
,
5。099999904632568
,
3。5
]
,
[
“Iris-setosa”
,
1。7000000476837158
,
0。30000001192092896
,
5。699999809265137
,
3。799999952316284
]
,
[
“Iris-setosa”
,
1。5
,
0。30000001192092896
,
5。099999904632568
,
3。799999952316284
]
,
[
“Iris-setosa”
,
1。7000000476837158
,
0。20000000298023224
,
5。400000095367432
,
3。4000000953674316
]
]
}