色婷婷视频在线播放,最新亚洲国产黄色视频在线观看 ,综合在线一区,久久大香蕉在线视频,超碰97av在线,色无码毛片,左手影音成人在线,91无码影院

引言

通過SQL進行檢索ElasticSearch的文檔，在一些復雜場景更為靈活。由于DSL需要熟悉其語法，自建的日志平臺可能將DSL屏蔽和封裝，暴露SQL的查詢更易上手。本文順著官方指南實操一把，文章內容有。

Kibana執(zhí)行SQL查詢
Post請求執(zhí)行SQL分頁查詢
SQL中使用DSL過濾
使用復雜查詢條件
其他查詢方式（運行時字段與異步SQL）

一、Kibana執(zhí)行SQL查詢

請求示例：

POST?/_sql?format=txt
{
??"query":?"""
??????SELECT?"pid","span_id","trace_id","user_id"?FROM?"prd_detail-xxx_*"?LIMIT?10
???"""
}

返回結果：

??????pid??????|????span_id?????|????trace_id????|????user_id????
---------------+----------------+----------------+---------------
833037?????????|a481fcd11b5e7ef3|0ffc42e668901b86|null???????????
2631155????????|44273ff566fc9634|2a770bf4a65425e6|null???????????
1397839????????|691f7a77caf21a4c|ebc60684c13a2af3|null???????????
3984591????????|638c9eda5973bcd3|e36218668bcac321|null???

備注：在使用kibana console會使用三引號(""")，format格式支持csv、json、txt、yaml等眾多格式，查詢支持*號。

二、Post請求執(zhí)行SQL分頁查詢

1.添加測試數(shù)據(jù)

先造點測試數(shù)據(jù)，方便測試，請求URL：

PUT?/library/_bulk?refresh

輸入?yún)?shù)：

{"index":{"_id":?"Leviathan?Wakes"}}
{"name":?"Leviathan?Wakes",?"author":?"James?S.A.?Corey",?"release_date":?"2011-06-02",?"page_count":?561}
{"index":{"_id":?"Hyperion"}}
{"name":?"Hyperion",?"author":?"Dan?Simmons",?"release_date":?"1989-05-26",?"page_count":?482}
{"index":{"_id":?"Dune"}}
{"name":?"Dune",?"author":?"Frank?Herbert",?"release_date":?"1965-06-01",?"page_count":?604}

備注：上面命令通過kibana將結果注入。

2.查詢數(shù)據(jù)

請求URL：

http://127.0.0.1:9200/_sql?format=json

輸入?yún)?shù)：

{
??"query":?"SELECT?*?FROM?library?ORDER?BY?page_count?DESC"
}

返回結果：

{
????"columns":?[
????????{
????????????"name":?"author",
????????????"type":?"text"
????????},
????????{
????????????"name":?"name",
????????????"type":?"text"
????????},
????????{
????????????"name":?"page_count",
????????????"type":?"long"
????????},
????????{
????????????"name":?"release_date",
????????????"type":?"datetime"
????????}
????],
????"rows":?[
????????[
????????????"Frank?Herbert",
????????????"Dune",
????????????604,
????????????"1965-06-01T00:00:00.000Z"
????????],
????????[
????????????"James?S.A.?Corey",
????????????"Leviathan?Wakes",
????????????561,
????????????"2011-06-02T00:00:00.000Z"
????????],
????????[
????????????"Dan?Simmons",
????????????"Hyperion",
????????????482,
????????????"1989-05-26T00:00:00.000Z"
????????]
????]
}

備注：Postman中通過SQL查詢導入的共計3條數(shù)據(jù)。

2.分頁首次查詢

輸入?yún)?shù)：

{
??"query":?"SELECT?*?FROM?library?ORDER?BY?page_count?DESC",
??"fetch_size":?2
}

返回結果：

{
????"columns":?[
????????{
????????????"name":?"author",
????????????"type":?"text"
????????},
????????{
????????????"name":?"name",
????????????"type":?"text"
????????},
????????{
????????????"name":?"page_count",
????????????"type":?"long"
????????},
????????{
????????????"name":?"release_date",
????????????"type":?"datetime"
????????}
????],
????"rows":?[
????????[
????????????"Frank?Herbert",
????????????"Dune",
????????????604,
????????????"1965-06-01T00:00:00.000Z"
????????],
????????[
????????????"James?S.A.?Corey",
????????????"Leviathan?Wakes",
????????????561,
????????????"2011-06-02T00:00:00.000Z"
????????]
????],
????"cursor":?"i6+xAwFaAXN4RkdsdVkyeDFaR1ZmWTI5dWRHVjRkRjkxZFdsa0RYRjFaWEo1UVc1a1JtVjBZMmdCRm1kbFJGRTBZa2wzVkdwdGN6ZG1RMVo1WlZWSFgyY0FBQUFBQUFBQ0R4WXRNMnhRTm5CTk9GTkljVEV0ZFd4d1IxRjVZbGRS/////w8EAWYGYXV0aG9yAQZhdXRob3IBBHRleHQAAAABZgRuYW1lAQRuYW1lAQR0ZXh0AAAAAWYKcGFnZV9jb3VudAEKcGFnZV9jb3VudAEEbG9uZwAAAAFmDHJlbGVhc2VfZGF0ZQEMcmVsZWFzZV9kYXRlAQhkYXRldGltZQEAAAEP"
}

備注：Postman中執(zhí)行，總共3條數(shù)據(jù)，查詢一頁2條，返回的最后一行cursor，下一頁用它來查。

3.分頁第二次查詢

輸入?yún)?shù)：

{
??"cursor":?"i6+xAwFaAXN4RkdsdVkyeDFaR1ZmWTI5dWRHVjRkRjkxZFdsa0RYRjFaWEo1UVc1a1JtVjBZMmdCRm5WSmRrWktRM0pIVVhOeGVFNTRVRGsyVFhGNFdIY0FBQUFBQUFBQ2xCWTJTRU5VTUhSTVVGRTNkVXR2U2xCdVdWQnNUekZC/////w8EAWYGYXV0aG9yAQZhdXRob3IBBHRleHQAAAABZgRuYW1lAQRuYW1lAQR0ZXh0AAAAAWYKcGFnZV9jb3VudAEKcGFnZV9jb3VudAEEbG9uZwAAAAFmDHJlbGVhc2VfZGF0ZQEMcmVsZWFzZV9kYXRlAQhkYXRldGltZQEAAAEP"
}

返回結果：

{
????"rows":?[
????????[
????????????"Dan?Simmons",
????????????"Hyperion",
????????????482,
????????????"1989-05-26T00:00:00.000Z"
????????]
????],
????"cursor":?"i6+xAwFaAXN4RkdsdVkyeDFaR1ZmWTI5dWRHVjRkRjkxZFdsa0RYRjFaWEo1UVc1a1JtVjBZMmdCRm5WSmRrWktRM0pIVVhOeGVFNTRVRGsyVFhGNFdIY0FBQUFBQUFBQ2xCWTJTRU5VTUhSTVVGRTNkVXR2U2xCdVdWQnNUekZC/////w8EAWYGYXV0aG9yAQZhdXRob3IBBHRleHQAAAABZgRuYW1lAQRuYW1lAQR0ZXh0AAAAAWYKcGFnZV9jb3VudAEKcGFnZV9jb3VudAEEbG9uZwAAAAFmDHJlbGVhc2VfZGF0ZQEMcmVsZWFzZV9kYXRlAQhkYXRldGltZQEAAAEP"
}

備注：當繼續(xù)分頁查詢時，需要使用上次查詢返回的cursor來查，第二次查詢依舊一頁2條數(shù)據(jù)，總共3條，返回了1條數(shù)據(jù)。

4.分頁第三次查詢

輸入?yún)?shù)：

{
??"cursor":?"i6+xAwFaAXN4RkdsdVkyeDFaR1ZmWTI5dWRHVjRkRjkxZFdsa0RYRjFaWEo1UVc1a1JtVjBZMmdCRm1kbFJGRTBZa2wzVkdwdGN6ZG1RMVo1WlZWSFgyY0FBQUFBQUFBQ2NSWXRNMnhRTm5CTk9GTkljVEV0ZFd4d1IxRjVZbGRS/////w8EAWYGYXV0aG9yAQZhdXRob3IBBHRleHQAAAABZgRuYW1lAQRuYW1lAQR0ZXh0AAAAAWYKcGFnZV9jb3VudAEKcGFnZV9jb3VudAEEbG9uZwAAAAFmDHJlbGVhc2VfZGF0ZQEMcmVsZWFzZV9kYXRlAQhkYXRldGltZQEAAAEP"?
}

返回結果：

{
????"rows":?[]
}

備注：當再次輸入cursor查詢時，返回記錄為空，分頁結束。

三、SQL中使用DSL過濾

請求URL：

http://127.0.0.1:9200/_sql?format=json

輸入?yún)?shù)：

{
??"query":?"SELECT?*?FROM?library?ORDER?BY?page_count?DESC",
??"filter":?{
????"range":?{
??????"page_count":?{
????????"gte"?:?400,
????????"lte"?:?500
??????}
????}
??},
??"fetch_size":?2
}

返回結果：

{
????"columns":?[
????????{
????????????"name":?"author",
????????????"type":?"text"
????????},
????????{
????????????"name":?"name",
????????????"type":?"text"
????????},
????????{
????????????"name":?"page_count",
????????????"type":?"long"
????????},
????????{
????????????"name":?"release_date",
????????????"type":?"datetime"
????????}
????],
????"rows":?[
????????[
????????????"Dan?Simmons",
????????????"Hyperion",
????????????482,
????????????"1989-05-26T00:00:00.000Z"
????????]
????]
}

備注：可以通過ElasticSearch DSL來過濾結果。

四、柱狀顯示查詢結果

請求參數(shù)：

http://127.0.0.1:9200/_sql?format=json

輸入?yún)?shù)：

{
??"query":?"SELECT?*?FROM?library?ORDER?BY?page_count?DESC",
??"fetch_size":?2,
??"columnar":?true
}

返回結果：

{
????"columns":?[
????????{
????????????"name":?"author",
????????????"type":?"text"
????????},
????????{
????????????"name":?"name",
????????????"type":?"text"
????????},
????????{
????????????"name":?"page_count",
????????????"type":?"long"
????????},
????????{
????????????"name":?"release_date",
????????????"type":?"datetime"
????????}
????],
????"values":?[
????????[
????????????"Frank?Herbert",
????????????"James?S.A.?Corey"
????????],
????????[
????????????"Dune",
????????????"Leviathan?Wakes"
????????],
????????[
????????????604,
????????????561
????????],
????????[
????????????"1965-06-01T00:00:00.000Z",
????????????"2011-06-02T00:00:00.000Z"
????????]
????],
????"cursor":?"i6+xAwFaAXN4RkdsdVkyeDFaR1ZmWTI5dWRHVjRkRjkxZFdsa0RYRjFaWEo1UVc1a1JtVjBZMmdCRm1kbFJGRTBZa2wzVkdwdGN6ZG1RMVo1WlZWSFgyY0FBQUFBQUFBZkdoWXRNMnhRTm5CTk9GTkljVEV0ZFd4d1IxRjVZbGRS/////w8EAWYGYXV0aG9yAQZhdXRob3IBBHRleHQAAAABZgRuYW1lAQRuYW1lAQR0ZXh0AAAAAWYKcGFnZV9jb3VudAEKcGFnZV9jb3VudAEEbG9uZwAAAAFmDHJlbGVhc2VfZGF0ZQEMcmVsZWFzZV9kYXRlAQhkYXRldGltZQEAAAEP"
}

備注：通過參數(shù)columnar來設置顯示樣式，默認為false。

五、使用復雜查詢條件

請求url：

http://127.0.0.1:9200/_sql?format=json

輸入?yún)?shù)：

{
?"query":?"SELECT?YEAR(release_date)?AS?year?FROM?library?WHERE?page_count?>?300?AND?author?=?'Frank?Herbert'?GROUP?BY?year?HAVING?COUNT(*)?>?0"
}

返回結果：

{
????"columns":?[
????????{
????????????"name":?"year",
????????????"type":?"integer"
????????}
????],
????"rows":?[
????????[
????????????1965
????????]
????],
????"cursor":?"i6+xAwFaAWMBB2xpYnJhcnn+AgEBCWNvbXBvc2l0ZQdncm91cGJ5AAEPYnVja2V0X3NlbGVjdG9yD2hhdmluZy4zZTc4ZDhjNgEGX2NvdW50/wECYTAGX2NvdW50AAEIcGFpbmxlc3NTSW50ZXJuYWxRbFNjcmlwdFV0aWxzLm51bGxTYWZlRmlsdGVyKEludGVybmFsUWxTY3JpcHRVdGlscy5ndChwYXJhbXMuYTAscGFyYW1zLnYwKSkKAAoBAnYwAQAAAAAB/wEBCDYzOTU0MjMzAQxyZWxlYXNlX2RhdGUAAAEAAAECMXkCAQFaAAAAAAAAAADoBwEKAQg2Mzk1NDIzMwL////bRGPIAAACAQAAAAABAP////8PAAAAAAEEYm9vbD+AAAAAAgVyYW5nZT+AAAAACnBhZ2VfY291bnQBAAABLP8AAAAAAAR0ZXJtP4AAAAAOYXV0aG9yLmtleXdvcmQVDUZyYW5rIEhlcmJlcnQAAAAAAQAAAAAAAAAAAVoDAAICAAAAAAAAAAD/////DwIBcAEuAWEBawg2Mzk1NDIzMwABAmR0CQABawg2Mzk1NDIzMwEAAQEA"
}

備注：可通過SQL92查詢、分組等復雜條件來執(zhí)行。

六、其他查詢方式

可利用運行時字段(runtime fields)對查詢結果聚合，過濾和排序，需要es 7.11版本以上，本文使用7.10 不再演示
通常使用同步SQL查詢，elasticsearch也支持異步SQL查詢

ES08# ElasticSearch中的SQL查詢