天天看點
傳回

用SQL代替DSL查詢ElasticSearch怎樣？

如果你和我一樣「熟悉SQL，但不咋會寫DSL」 or 「想要用SQL簡化查詢」，本文會介紹一下官方對ES SQL的支援，希望對你有所幫助～

ES7.x版本的x-pack自帶ElasticSearch SQL，我們可以直接通過SQL REST API、SQL CLI等方式使用SQL查詢。

SQL REST API

在Kibana Console中輸入：

POST /_sql?format=txt
{
  "query": "SELECT * FROM library ORDER BY page_count DESC LIMIT 5"
}

複制

将上述SQL替換為你自己的SQL語句，即可。傳回格式如下：

author      |        name        |  page_count   | release_date
-----------------+--------------------+---------------+------------------------
Peter F. Hamilton|Pandora's Star      |768            |2004-03-02T00:00:00.000Z
Vernor Vinge     |A Fire Upon the Deep|613            |1992-06-01T00:00:00.000Z
Frank Herbert    |Dune                |604            |1965-06-01T00:00:00.000Z

複制

SQL CLI

elasticsearch-sql-cli是安裝ES時bin目錄的一個腳本檔案，也可單獨下載下傳。我們在ES目錄運作

./bin/elasticsearch-sql-cli https://some.server:9200

複制

輸入sql即可查詢

sql> SELECT * FROM library WHERE page_count > 500 ORDER BY page_count DESC;
     author      |        name        |  page_count   | release_date
-----------------+--------------------+---------------+---------------
Peter F. Hamilton|Pandora's Star      |768            |1078185600000
Vernor Vinge     |A Fire Upon the Deep|613            |707356800000
Frank Herbert    |Dune                |604            |-144720000000

複制

SQL To DSL

在Kibana輸入：

POST /_sql/translate
{
  "query": "SELECT * FROM library ORDER BY page_count DESC",
  "fetch_size": 10
}

複制

即可得到轉化後的DSL query:

{
  "size": 10,
  "docvalue_fields": [
    {
      "field": "release_date",
      "format": "epoch_millis"
    }
  ],
  "_source": {
    "includes": [
      "author",
      "name",
      "page_count"
    ],
    "excludes": []
  },
  "sort": [
    {
      "page_count": {
        "order": "desc",
        "missing": "_first",
        "unmapped_type": "short"
      }
    }
  ]
}

複制

因為查詢相關的語句已經生成，我們隻需要在這個基礎上适當修改或不修改就可以愉快使用DSL了。

下面我們詳細介紹下ES SQL 支援的SQL語句 和 如何避免錯誤使用。

首先需要了解下ES SQL支援的SQL語句中，SQL術語和ES術語的對應關系：

用SQL代替DSL查詢ElasticSearch怎樣？

ES SQL的文法支援大多遵循ANSI SQL标準，支援的SQL語句有DML查詢和部分DDL查詢。

DDL查詢如：

DESCRIBE table

,

SHOW COLUMNS IN table

略顯雞肋，我們主要看下對

SELECT,Function

的DML查詢支援。

SELECT

文法結構如下：

SELECT [TOP [ count ] ] select_expr [, ...]
[ FROM table_name ]
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition]
[ ORDER BY expression [ ASC | DESC ] [, ...] ]
[ LIMIT [ count ] ]
[ PIVOT ( aggregation_expr FOR column IN ( value [ [ AS ] alias ] [, ...] ) ) ]

複制

表示從0-N個表中擷取行資料。SQL的執行順序為：

  1. 擷取所有

    FROM

     中的關鍵詞，确定表名。
  2. 如果有

    WHERE

     條件，過濾掉所有不符合的行。
  3. 如果有

    GROUP BY

     條件，則分組聚合；如果有

    HAVING

     條件，則過濾聚合的結果。
  4. 上一步得到的結果經過

    select_expr

     運算，确定具體傳回的資料。
  5. 如果有

    ORDER BY

     條件，會對傳回的資料排序。
  6. 如果有

    LIMIT

     or

    TOP

     條件，會傳回上一步結果的子集。
與常用的SQL有兩點不同，ES SQL 支援

TOP [ count ]

 和

PIVOT ( aggregation_expr FOR column IN ( value [ [ AS ] alias ] [, ...] ) )

 子句。

TOP [ count ]

 ：如

SELECT TOP 2 first_name FROM emp

 表示最多傳回兩條資料，不可與

LIMIT

 條件共用。

PIVOT

 子句會對其聚合條件得到的結果進行行轉列，進一步運算。這個我是沒用過，不做介紹。

FUNCTION

基于上面的SQL我們其實已經能有過濾，聚合，排序，分頁功能的SQL了。但是我們需要進一步了解ES SQL中FUNCTION的支援，才能寫出豐富的具有全文搜尋，聚合，分組功能的SQL。

使用

SHOW FUNCTIONS

可列舉出支援的函數名稱和所屬類型。

SHOW FUNCTIONS;

      name       |     type
-----------------+---------------
AVG              |AGGREGATE
COUNT            |AGGREGATE
FIRST            |AGGREGATE
FIRST_VALUE      |AGGREGATE
LAST             |AGGREGATE
LAST_VALUE       |AGGREGATE
MAX              |AGGREGATE
MIN              |AGGREGATE
SUM              |AGGREGATE
........

複制

我們主要看下聚合，分組，全文搜尋相關的常用函數。

全文比對函數

MATCH

：相當于DSL中的match and multi_match查詢。

MATCH(
    field_exp,       --字段名稱
    constant_exp,       --字段的比對值
    [, options])       --可選項

複制

使用舉例：

SELECT author, name FROM library WHERE MATCH(author, 'frank');

    author     |       name
---------------+-------------------
Frank Herbert  |Dune
Frank Herbert  |Dune Messiah

複制

SELECT author, name, SCORE() FROM library WHERE MATCH('author^2,name^5', 'frank dune');

    author     |       name        |    SCORE()
---------------+-------------------+---------------
Frank Herbert  |Dune               |11.443176
Frank Herbert  |Dune Messiah       |9.446629

複制

QUERY

：相當于DSL中的 query_string 查詢。

QUERY(
    constant_exp      --比對值表達式
    [, options])       --可選項

複制

使用舉例：

SELECT author, name, page_count, SCORE() FROM library WHERE QUERY('_exists_:"author" AND page_count:>200 AND (name:/star.*/ OR name:duna~)');

      author      |       name        |  page_count   |    SCORE()
------------------+-------------------+---------------+---------------
Frank Herbert     |Dune               |604            |3.7164764
Frank Herbert     |Dune Messiah       |331            |3.4169943

複制

SCORE()

：傳回輸入資料和傳回資料的相關度relevance.

使用舉例：

SELECT SCORE(), * FROM library WHERE MATCH(name, 'dune') ORDER BY SCORE() DESC;

    SCORE()    |    author     |       name        |  page_count   |    release_date
---------------+---------------+-------------------+---------------+--------------------
2.2886353      |Frank Herbert  |Dune               |604            |1965-06-01T00:00:00Z
1.8893257      |Frank Herbert  |Dune Messiah       |331            |1969-10-15T00:00:00Z

複制

聚合函數

AVG(numeric_field)

：計算數字類型的字段的平均值。

SELECT AVG(salary) AS avg FROM emp;

複制

COUNT(expression)

：傳回輸入資料的總數，包括COUNT()時field_name對應的值為null的資料。

COUNT(ALL field_name)

：傳回輸入資料的總數，不包括field_name對應的值為null的資料。

COUNT(DISTINCT field_name)

：傳回輸入資料中field_name對應的值不為null的總數。

SUM(field_name)

：傳回輸入資料中數字字段field_name對應的值的總和。

MIN(field_name)

：傳回輸入資料中數字字段field_name對應的值的最小值。

MAX(field_name)

：傳回輸入資料中數字字段field_name對應的值的最大值。

分組函數

這裡的分組函數是對應DSL中的bucket分組。

HISTOGRAM

：文法如下：

HISTOGRAM(
           numeric_exp,    --數字表達式，通常是一個field_name
           numeric_interval    --數字的區間值
)

HISTOGRAM(
           date_exp,      --date/time表達式，通常是一個field_name
           date_time_interval      --date/time的區間值
)

複制

如下傳回每年1月1号淩晨出生的資料:

ELECT HISTOGRAM(birth_date, INTERVAL 1 YEAR) AS h, COUNT(*) AS c FROM emp GROUP BY h;


           h            |       c
------------------------+---------------
null                    |10
1952-01-01T00:00:00.000Z|8
1953-01-01T00:00:00.000Z|11
1954-01-01T00:00:00.000Z|8
1955-01-01T00:00:00.000Z|4
1956-01-01T00:00:00.000Z|5
1957-01-01T00:00:00.000Z|4
1958-01-01T00:00:00.000Z|7
1959-01-01T00:00:00.000Z|9
1960-01-01T00:00:00.000Z|8
1961-01-01T00:00:00.000Z|8
1962-01-01T00:00:00.000Z|6
1963-01-01T00:00:00.000Z|7
1964-01-01T00:00:00.000Z|4
1965-01-01T00:00:00.000Z|1

複制

ES SQL局限性

因為ES SQL和ES DSL在功能上并非完全比對，官方文檔提到的SQL局限性有：

大的查詢可能抛ParsingException

在解析階段，極大的查詢會占用過多的記憶體，在這種情況下，Elasticsearch SQL引擎将中止解析并抛出錯誤。

nested類型字段的表示方法

SQL中不支援nested類型的字段，隻能使用

[nested_field_name].[sub_field_name]

複制

這種形式來引用内嵌子字段。

使用舉例：

SELECT dep.dep_name.keyword FROM test_emp GROUP BY languages;

複制

nested類型字段不能用在where 和 order by 的Scalar函數上

如以下SQL都是錯誤的

SELECT * FROM test_emp WHERE LENGTH(dep.dep_name.keyword) > 5;

SELECT * FROM test_emp ORDER BY YEAR(dep.start_date);

複制

不支援多個nested字段的同時查詢

如嵌套字段nested_A和nested_B無法同時使用。

nested内層字段分頁限制

當分頁查詢有nested字段時，分頁結果可能不正确。這是因為：ES中的分頁查詢發生在Root nested document上，而不是它的内層字段上。

keyword類型的字段不支援normalizer

不支援數組類型的字段

這是因為在SQL中一個field隻對應一個值，這種情況下我們可以使用上面介紹的 SQL To DSL的API 轉化為DSL語句，用DSL查詢就好了。

聚合排序的限制

  • 排序字段必須是聚合桶中的字段，ES SQL CLI突破了這種限制，但上限不能超過512行，否則在sorting階段會抛異常。推薦搭配

    Limit

     子句使用，如： 
SELECT * FROM test GROUP BY age ORDER BY COUNT(*) LIMIT 100;

複制

  • 聚合排序的排序條件不支援Scalar函數或者簡單的操作符運算。聚合後的複雜字段（比如包含聚合函數）也是不能用在排序條件上的。

以下是錯誤例子：

SELECT age, ROUND(AVG(salary)) AS avg FROM test GROUP BY age ORDER BY avg;

SELECT age, MAX(salary) - MIN(salary) AS diff FROM test GROUP BY age ORDER BY diff;

複制

子查詢的限制

子查詢中包含

GROUP BY or HAVING

或者比

SELECT X FROM (SELECT ...) WHERE [simple_condition]

這種結構複雜，都是可能執行不成功的。

TIME 資料類型的字段不支援GROUP BY條件和HISTOGRAM函數

如以下查詢是錯誤的：

SELECT count(*) FROM test GROUP BY CAST(date_created AS TIME);

SELECT HISTOGRAM(CAST(birth_date AS TIME), INTERVAL '10' MINUTES) as h, COUNT(*) FROM t GROUP BY h

複制

但是将TIME類型的字段包裝為Scalar函數傳回是支援GROUP BY的，如：

SELECT count(*) FROM test GROUP BY MINUTE((CAST(date_created AS TIME));

複制

傳回字段的限制

如果一個字段不在source中存儲，是無法查詢到的。

keyword, date, scaled_float, geo_point, geo_shape

這些類型的字段不受這種限制，因為他們不是從

_source

中傳回，而是從

docvalue_fields

中傳回。

上一篇: js調用ocx控件的問題
下一篇: COM元件開發（五）——使用MFC開發COM元件