【最佳實踐】使用 Elasticsearch SQL 實現數據查詢

Elasticsearch 是一個全文搜索引擎，具有您期望的所有優點，例如相關性評分，詞幹，同義詞等。而且，由於它是具有水平可擴展的分佈式文檔存儲，因此它可以處理數十億行數據，而不會費勁。針對 Elasticsearch 專業人員來說，大多數人喜歡使用 DSL 來進行搜索，但是對於一些不是那麼專業的人員來說，他們更為熟悉的是 SQL 語句。如何讓他們對 Elasticsearch 的數據進行查詢是一個問題。藉助 Elasticsearch SQL，你可以使用熟悉的查詢語法訪問全文搜索，超快的速度和輕鬆的可伸縮性。X-Pack 包含一項 SQL 功能，可對 Elasticsearch 索引執行 SQL 查詢並以表格格式返回結果。

數據準備

1、開通阿里雲 Elasticsearch 1核2G 免費測試環境
2、進入 Kibana

點擊上面的 “Load a data set and a Kibana dashboard”：

點擊上面的 Add data，這樣我們就可以完成實驗數據的導入了。在 Elasticsearch 中，我們會找到一個叫kibana_sample_data_flights 的索引。

SQL 實操

查詢有哪些索引

根據 Elasticsearch 的文檔，我們可以使用如下的命令來查看有哪些索引：

POST /_sql?format=txt 
{
  "query": "SHOW tables"
}

上面的命令顯示結果：

檢索 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 / Tomakomai
Nashville      |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小時，並且按照時間最長來排序。”

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公里。將距離和速度四捨五入到最接近的整數。如果速度相等，請先顯示最長的時間。”

首先我們在上面的 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 可能是此過程的第一步。