Elasticsearch 是一個全文搜索引擎，具有您期望的所有優點，例如相關性評分，詞干，同義詞等。而且，由于它是具有水平可擴展的分布式文檔存儲，因此它可以處理數十億行數據，而不會費勁。針對Elasticsearch專業人員來說，大多數人喜歡使用DSL來進行搜索，但是對于一些不是那么專業的人員來說，他們更為熟悉的是 SQL 語句。如何讓他們對 Elasticsearch 的數據進行查詢是一個問題。借助 Elasticsearch SQL，您可以使用熟悉的查詢語法訪問全文搜索，超快的速度和輕松的可伸縮性。X-Pack 包含一項 SQL 功能，可對 Elasticsearch 索引執行 SQL 查詢并以表格格式返回結果。
?
在今天的文章里，我們將簡單介紹一下如何使用 Elasticsearch SQL來對我們的數據進行查詢。在之前的一篇文章“Kibana：Canvas入門”里也有 Elasticsearch SQL 的具體用例介紹。
?
安裝
?
對于還沒安裝好自己的 Elasticsearch 的開發者來說，你可以參閱我之前的文章“Elastic：菜鳥上手指南”來進行安裝自己的 Elasticsearch 及 Kibana。在這里我就不累述了。
?
準備數據
?
我們首先打開 Kibana:
?
?
點擊上面的“Load a data set and a Kibana dashboard”：
?
?
點擊上面的 Add data，這樣我們就可以完成實驗數據的導入了。在 Elasticsearch 中，我們會找到一個叫 kibana_sample_data_flights 的索引。
?
SQL 實操
?
?
檢索 Elasticsearch schema 信息：DSL vs SQL
?
首先，我們確定表/索引的 schema 以及可供我們使用的字段。我們將通過 REST 界面執行此操作：
?
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
POST /_sql{ "query": """ DESCRIBE kibana_sample_data_flights """}上面命令的結果：{ "columns" : [ { "name" : "column", "type" : "keyword" }, { "name" : "type", "type" : "keyword" }, { "name" : "mapping", "type" : "keyword" } ], "rows" : [ [ "AvgTicketPrice", "REAL", "float" ], [ "Cancelled", "BOOLEAN", "boolean" ], [ "Carrier", "VARCHAR", "keyword" ], [ "Dest", "VARCHAR", "keyword" ], [ "DestAirportID", "VARCHAR", "keyword" ], [ "DestCityName", "VARCHAR", "keyword" ], [ "DestCountry", "VARCHAR", "keyword" ], [ "DestLocation", "GEOMETRY", "geo_point" ], [ "DestRegion", "VARCHAR", "keyword" ], [ "DestWeather", "VARCHAR", "keyword" ], [ "DistanceKilometers", "REAL", "float" ], [ "DistanceMiles", "REAL", "float" ], [ "FlightDelay", "BOOLEAN", "boolean" ], [ "FlightDelayMin", "INTEGER", "integer" ], [ "FlightDelayType", "VARCHAR", "keyword" ], [ "FlightNum", "VARCHAR", "keyword" ], [ "FlightTimeHour", "VARCHAR", "keyword" ], [ "FlightTimeMin", "REAL", "float" ], [ "Origin", "VARCHAR", "keyword" ], [ "OriginAirportID", "VARCHAR", "keyword" ], [ "OriginCityName", "VARCHAR", "keyword" ], [ "OriginCountry", "VARCHAR", "keyword" ], [ "OriginLocation", "GEOMETRY", "geo_point" ], [ "OriginRegion", "VARCHAR", "keyword" ], [ "OriginWeather", "VARCHAR", "keyword" ], [ "dayOfWeek", "INTEGER", "integer" ], [ "timestamp", "TIMESTAMP", "datetime" ] ]}
?
也可以通過 url 參數 format = txt 以表格形式格式化以上響應。例如：
?
POST /_sql?format=txt { "query": "DESCRIBE kibana_sample_data_flights"}
?
上面命令查詢的結果是：
?
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
column | type | mapping ------------------+---------------+---------------AvgTicketPrice |REAL |float Cancelled |BOOLEAN |boolean Carrier |VARCHAR |keyword Dest |VARCHAR |keyword DestAirportID |VARCHAR |keyword DestCityName |VARCHAR |keyword DestCountry |VARCHAR |keyword DestLocation |GEOMETRY |geo_point DestRegion |VARCHAR |keyword DestWeather |VARCHAR |keyword DistanceKilometers|REAL |float DistanceMiles |REAL |float FlightDelay |BOOLEAN |boolean FlightDelayMin |INTEGER |integer FlightDelayType |VARCHAR |keyword FlightNum |VARCHAR |keyword FlightTimeHour |VARCHAR |keyword FlightTimeMin |REAL |float Origin |VARCHAR |keyword OriginAirportID |VARCHAR |keyword OriginCityName |VARCHAR |keyword OriginCountry |VARCHAR |keyword OriginLocation |GEOMETRY |geo_point OriginRegion |VARCHAR |keyword OriginWeather |VARCHAR |keyword dayOfWeek |INTEGER |integer timestamp |TIMESTAMP |datetime
?
是不是感覺回到 SQL 時代啊：）
?
向前邁進，只要提供來自 REST api 的示例響應，我們就會使用上面顯示的表格響應結構。要通過控制臺實現相同的查詢，需要使用以下命令登錄：
?
./bin/elasticsearch-sql-cli http://localhost:9200
?
我們可在屏幕上看到如下的畫面：
?
?
太神奇了。我們直接看到 SQL 的命令提示符了。在上面的命令行中，我們打入如下的命令：
?
DESCRIBE kibana_sample_data_flights;
?
這個結果和我們在Kibana中得到的結果是一樣的。
?
上面的schema也會隨對在 SELECT 子句中顯示的字段的任何查詢一起返回，從而為任何潛在的驅動程序提供格式化或對結果進行操作所需的必要類型信息。例如，考慮帶有 LIMIT 子句的簡單 SELECT，以使響應簡短。默認情況下，我們返回1000行。
?
我們發現索引的名字 kibana_sample_data_flights 比較長，為了方便，我們來創建一個alias：
?
PUT /kibana_sample_data_flights/_alias/flights
?
這樣在以后的操作中，當我們使用flights的時候，其實也就是對索引kibana_sample_data_flights 進行操作。
?
我們執行如下的命令：
?
POST /_sql?format=txt { "query": "SELECT FlightNum FROM flights LIMIT 1"}
?
顯示結果：
?
FlightNum ---------------9HY9SWR
?
相同的REST請求/響應由JDBC驅動程序和控制臺使用：
?
sql> SELECT OriginCountry, OriginCityName FROM flights LIMIT 1; OriginCountry | OriginCityName ---------------+-----------------DE |Frankfurt am Main
?
請注意，如果在任何時候請求的字段都不存在（區分大小寫），則表格式和強類型存儲區的語義意味著將返回錯誤-這與 Elasticsearch 行為不同，在該行為中，根本不會返回該字段。例如，將上面的內容修改為使用字段“OrigincityName”而不是“OriginCityName”會產生有用的錯誤消息：
?
sql> SELECT OriginCountry, OrigincityName FROM flights LIMIT 1;Bad request [Found 1 problem(s)line 1:23: Unknown column [OrigincityName], did you mean any of [OriginCityName, DestCityName]?]
?
同樣，如果我們嘗試在不兼容的字段上使用函數或表達式，則會出現相應的錯誤。通常，分析器在驗證 AST 時會較早失敗。為了實現這一點，Elasticsearch 必須了解每個字段的索引映射和功能。因此，任何具有安全性訪問 SQL 接口的客戶端都需要適當的權限。
?
如果我們繼續提供每一個請求和相應的回復，我們將最終獲得一篇冗長的博客文章！為了簡潔起見，以下是一些帶有感興趣的注釋的日益復雜的查詢。
?
?
使用 WHERE 及 ORDER BY 來 SELECT
?
“找到飛行時間超過5小時的美國最長10班航班。”
?
POST /_sql?format=txt{ "query": """ SELECT OriginCityName, DestCityName FROM flights WHERE FlightTimeHour > 5 AND OriginCountry='US' ORDER BY FlightTimeHour DESC LIMIT 10 """}
?
顯示結果是：
?
OriginCityName | DestCityName ---------------+-------------------Chicago |Oslo Cleveland |Seoul Denver |Chitose / TomakomaiNashville |Verona Minneapolis |Tokyo Portland |Treviso Spokane |Vienna Kansas City |Zurich Kansas City |Shanghai Los Angeles |Zurich
?
限制行數的運算符因 SQL 實現而異。對于 Elasticsearch SQL，我們在實現LIMIT運算符時與 Postgresql/Mysql 保持一致。
?
?
Math
?
只是一些隨機數字...
?
sql> SELECT ((1 + 3) * 1.5 / (7 - 6)) * 2 AS random; random ---------------12.0
?
這代表服務器端對功能執行某些后處理的示例。沒有等效的Elasticsearch DSL查詢。
?
?
?
Functions & Expressions
?
“在2月份之后查找所有航班，該航班的飛行時間大于5小時，并且按照時間最長來排序?！?/strong>
?
POST /_sql?format=txt{ "query": """ SELECT MONTH_OF_YEAR(timestamp), OriginCityName, DestCityName FROM flights WHERE FlightTimeHour > 1 AND MONTH_OF_YEAR(timestamp) > 2 ORDER BY FlightTimeHour DESC LIMIT 10 """}
?
顯示結果是：
?
MONTH_OF_YEAR(timestamp)|OriginCityName | DestCityName ------------------------+---------------+---------------4 |Chicago |Oslo 4 |Osaka |Spokane 4 |Quito |Tucson 4 |Shanghai |Stockholm 5 |Tokyo |Venice 5 |Tokyo |Venice 5 |Tokyo |Venice 5 |Buenos Aires |Treviso 5 |Amsterdam |Birmingham 5 |Edmonton |Milan
?
這些功能通常需要在 Elasticsearch 中運用 Painless 變形才能達到等效的效果，而 SQL 的功能聲明避免任何腳本編寫。還要注意我們如何在WHERE和SELECT子句中使用該函數。WHERE 子句組件被下推到 Elasticsearch，因為它影響結果計數。SELECT 函數由演示中的服務器端插件處理。
?
請注意，可用功能列表可通過“SHOW FUNCTIONS”檢索
?
sql> SHOW FUNCTIONS; name | type -----------------+---------------AVG |AGGREGATE COUNT |AGGREGATE FIRST |AGGREGATE FIRST_VALUE |AGGREGATE LAST |AGGREGATE LAST_VALUE |AGGREGATE MAX |AGGREGATE ...
?
將其與我們之前的數學能力相結合，我們可以開始制定查詢，對于大多數DSL用戶來說，查詢將非常復雜。
?
“找出最快的2個航班（速度）的距離和平均速度，這些航班在星期一，星期二或星期三上午9點至11點之間離開，并且距離超過500公里。將距離和速度四舍五入到最接近的整數。如果速度相等，請先顯示最長的時間?！?/strong>
?
首先我們在上面的 DESCRIBE kibana_sample_data_flights 命令的輸出中，我們可以看到FlightTimeHour 是一個 keyword。這個顯然是不對的，因為它是一個數值。也許在最初的設計時這么想的。我們需要把這個字段改為 float 類型的數據。
?
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
PUT flight1{ "mappings": { "properties": { "AvgTicketPrice": { "type": "float" }, "Cancelled": { "type": "boolean" }, "Carrier": { "type": "keyword" }, "Dest": { "type": "keyword" }, "DestAirportID": { "type": "keyword" }, "DestCityName": { "type": "keyword" }, "DestCountry": { "type": "keyword" }, "DestLocation": { "type": "geo_point" }, "DestRegion": { "type": "keyword" }, "DestWeather": { "type": "keyword" }, "DistanceKilometers": { "type": "float" }, "DistanceMiles": { "type": "float" }, "FlightDelay": { "type": "boolean" }, "FlightDelayMin": { "type": "integer" }, "FlightDelayType": { "type": "keyword" }, "FlightNum": { "type": "keyword" }, "FlightTimeHour": { "type": "float" }, "FlightTimeMin": { "type": "float" }, "Origin": { "type": "keyword" }, "OriginAirportID": { "type": "keyword" }, "OriginCityName": { "type": "keyword" }, "OriginCountry": { "type": "keyword" }, "OriginLocation": { "type": "geo_point" }, "OriginRegion": { "type": "keyword" }, "OriginWeather": { "type": "keyword" }, "dayOfWeek": { "type": "integer" }, "timestamp": { "type": "date" } } }}
?
我們需要 reindex 這個索引。
?
POST _reindex{ "source": { "index": "flights" }, "dest": { "index": "flight1" }}
?
那么現在 flight1 的數據中，FlightTimeHour 字段將會是一個 float 的類型。我們再次重新設置 alias 為 flights:
?
POST _aliases{ "actions": [ { "add": { "index": "flight1", "alias": "flights" } }, { "remove": { "index": "kibana_sample_data_flights", "alias": "flights" } } ]}
?
那么現在 flights 將是指向 flight1 的一個 alias。
?
我們使用如下的 SQL 語句來查詢：
?
sql> SELECT timestamp, FlightNum, OriginCityName, DestCityName, ROUND(DistanceMiles) AS distance, ROUND(DistanceMiles/FlightTimeHour) AS speed, DAY_OF_WEEK(timestamp) AS day_of_week FROM flights WHERE DAY_OF_WEEK(timestamp) >= 0 AND DAY_OF_WEEK(timestamp) <= 2 AND HOUR_OF_DAY(timestamp) >=9 AND HOUR_OF_DAY(timestamp) <= 10 ORDER BY speed DESC, distance DESC LIMIT 2; timestamp | FlightNum |OriginCityName | DestCityName | distance | speed | day_of_week ------------------------+---------------+---------------+---------------+---------------+---------------+---------------2020-05-17T10:53:52.000Z|LAJSKLT |Guangzhou |Lima |11398.0 |783.0 |1 2020-04-27T09:30:39.000Z|VLUDO2H |Buenos Aires |Moscow |8377.0 |783.0 |2
? ? ? ? ? ??
一個相當復雜且奇怪的問題，但希望您能明白這一點。還要注意我們如何創建字段別名并在ORDER BY 子句中引用它們。
?
還要注意，不需要在 SELECT 子句中指定 WHERE 和 ORDER BY 中使用的所有字段。這可能與您過去使用的 SQL 實現不同。例如，以下內容完全正確：
?
POST /_sql{ "query":"SELECT timestamp, FlightNum FROM flights WHERE AvgTicketPrice > 500 ORDER BY AvgTicketPrice"}
?
它顯示：
?
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
{ "columns" : [ { "name" : "timestamp", "type" : "datetime" }, { "name" : "FlightNum", "type" : "text" } ], "rows" : [ [ "2020-04-26T09:04:20.000Z", "QG5DXD3" ], [ "2020-05-02T23:18:27.000Z", "NXA71BT" ], [ "2020-04-17T01:55:18.000Z", "VU8K9DM" ], [ "2020-04-24T08:46:45.000Z", "UM8IKF8" ],...]
?
?
將SQL查詢轉換為DSL
?
我們都曾嘗試過要在 Elasticsearch DSL 中表達的 SQL 查詢，或者想知道它是否是最佳的。新 SQL 接口的引人注目的功能之一是它能夠協助 Elasticsearch 的新采用者解決此類問題。使用 REST 接口，我們只需將/translate附加到“sql”端點，即可獲取驅動程序將發出的Elasticsearch 查詢。
?
讓我們考慮一下以前的一些查詢：
?
POST /_sql/translate{ "query": "SELECT OriginCityName, DestCityName FROM flights WHERE FlightTimeHour > 5 AND OriginCountry='US' ORDER BY FlightTimeHour DESC LIMIT 10"}
?
對于任何有經驗的 Elasticsearch 用戶，等效的 DSL 都應該是顯而易見的：
?
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
{ "size" : 10, "query" : { "bool" : { "must" : [ { "range" : { "FlightTimeHour" : { "from" : 5, "to" : null, "include_lower" : false, "include_upper" : false, "boost" : 1.0 } } }, { "term" : { "OriginCountry.keyword" : { "value" : "US", "boost" : 1.0 } } } ], "adjust_pure_negative" : true, "boost" : 1.0 } }, "_source" : { "includes" : [ "OriginCityName", "DestCityName" ], "excludes" : [ ] }, "sort" : [ { "FlightTimeHour" : { "order" : "desc", "missing" : "_first", "unmapped_type" : "float" } } ]}
?
WHERE 子句將按您期望的那樣轉換為 range 和 term 查詢。請注意，子字段的OriginCountry.keyword變體如何用于與父代 OriginCountry（文本類型）的精確匹配。不需要用戶知道基礎映射的行為差異-正確的字段類型將會被自動選擇。有趣的是，該接口嘗試通過在 _source 上使用?docvalue_fields 來優化檢索性能，例如適用于啟用了 doc 值的確切類型（數字，日期，關鍵字）。我們可以依靠 Elasticsearch SQL 為指定的查詢生成最佳的 DSL。
?
現在考慮我們上次使用的最復雜的查詢：
?
POST /_sql/translate{ "query": """ SELECT timestamp, FlightNum, OriginCityName, DestCityName, ROUND(DistanceMiles) AS distance, ROUND(DistanceMiles/FlightTimeHour) AS speed, DAY_OF_WEEK(timestamp) AS day_of_week FROM flights WHERE DAY_OF_WEEK(timestamp) >= 0 AND DAY_OF_WEEK(timestamp) <= 2 AND HOUR_OF_DAY(timestamp) >=9 AND HOUR_OF_DAY(timestamp) <= 10 ORDER BY speed DESC, distance DESC LIMIT 2 """}
?
上面的響應為：
?
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
{ "size" : 2, "query" : { "bool" : { "must" : [ { "script" : { "script" : { "source" : "InternalSqlScriptUtils.nullSafeFilter(InternalSqlScriptUtils.and(InternalSqlScriptUtils.gte(InternalSqlScriptUtils.dateTimeChrono(InternalSqlScriptUtils.docValue(doc,params.v0), params.v1, params.v2), params.v3), InternalSqlScriptUtils.lte(InternalSqlScriptUtils.dateTimeChrono(InternalSqlScriptUtils.docValue(doc,params.v4), params.v5, params.v6), params.v7)))", "lang" : "painless", "params" : { "v0" : "timestamp", "v1" : "Z", "v2" : "HOUR_OF_DAY", "v3" : 9, "v4" : "timestamp", "v5" : "Z", "v6" : "HOUR_OF_DAY", "v7" : 10 } }, "boost" : 1.0 } }, { "script" : { "script" : { "source" : "InternalSqlScriptUtils.nullSafeFilter(InternalSqlScriptUtils.and(InternalSqlScriptUtils.gte(InternalSqlScriptUtils.dayOfWeek(InternalSqlScriptUtils.docValue(doc,params.v0), params.v1), params.v2), InternalSqlScriptUtils.lte(InternalSqlScriptUtils.dayOfWeek(InternalSqlScriptUtils.docValue(doc,params.v3), params.v4), params.v5)))", "lang" : "painless", "params" : { "v0" : "timestamp", "v1" : "Z", "v2" : 0, "v3" : "timestamp", "v4" : "Z", "v5" : 2 } }, "boost" : 1.0 } } ], "adjust_pure_negative" : true, "boost" : 1.0 } }, "_source" : { "includes" : [ "FlightNum", "OriginCityName", "DestCityName", "DistanceMiles", "FlightTimeHour" ], "excludes" : [ ] }, "docvalue_fields" : [ { "field" : "timestamp", "format" : "epoch_millis" } ], "sort" : [ { "_script" : { "script" : { "source" : "InternalSqlScriptUtils.nullSafeSortNumeric(InternalSqlScriptUtils.round(InternalSqlScriptUtils.div(InternalSqlScriptUtils.docValue(doc,params.v0),InternalSqlScriptUtils.docValue(doc,params.v1)),params.v2))", "lang" : "painless", "params" : { "v0" : "DistanceMiles", "v1" : "FlightTimeHour", "v2" : null } }, "type" : "number", "order" : "desc" } }, { "_script" : { "script" : { "source" : "InternalSqlScriptUtils.nullSafeSortNumeric(InternalSqlScriptUtils.round(InternalSqlScriptUtils.docValue(doc,params.v0),params.v1))", "lang" : "painless", "params" : { "v0" : "DistanceMiles", "v1" : null } }, "type" : "number", "order" : "desc" } } ]}
?
是不是覺得非常復雜啊？
?
我們的 WHERE 和 ORDER BY 子句已轉換為 painless 腳本，并在 Elasticsearch 提供的排序和腳本查詢中使用。這些腳本甚至被參數化以避免編譯并利用腳本緩存。
?
附帶說明一下，盡管以上內容代表了 SQL 語句的最佳翻譯，但并不代表解決更廣泛問題的最佳解決方案。實際上，我們希望在索引時間對文檔中的星期幾，一天中的小時和速度進行編碼，因此可以只使用簡單的范圍查詢。這可能比使用painless 腳本解決此特定問題的性能更高。實際上，由于這些原因，其中的某些字段實際上甚至已經存在于文檔中。這是用戶應注意的常見主題：盡管我們可以依靠 Elasticsearch SQL 實現為我們提供最佳翻譯，但它只能利用查詢中指定的字段，因此不一定能為更大的問題查詢提供最佳解決方案。為了實現最佳方法，需要考慮基礎平臺的優勢，而 _translate API 可能是此過程的第一步。
?
參考：https://elasticstack.blog.csdn.net/article/details/105199768
總結
以上是生活随笔 為你收集整理的Elasticsearch SQL介绍及实例 的全部內容，希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔 網站內容還不錯，歡迎將生活随笔 推薦給好友。
