0. Overview
1. 測(cè)試數(shù)據(jù)集
2. 簡(jiǎn)單語(yǔ)句
3. 稍復(fù)雜語(yǔ)句
   - mysql
   - es-sql
      - only group by
      - group by with order by
4. Others

Overview

es v6.3.0之后，推出了es-SQL的支持士聪。今天來試試這個(gè)功能。

測(cè)試數(shù)據(jù)集

geonames

簡(jiǎn)單語(yǔ)句

在簡(jiǎn)單語(yǔ)句的情況下猛蔽，這個(gè)功能ok剥悟，具體表現(xiàn)如下，

simple es-sql

# execute
curl -X POST "$HOST/_xpack/sql?format=txt" -H 'Content-Type: application/json' -d'
{
    "query": "SELECT * FROM bm ORDER BY longitude DESC limit 3",
    "fetch_size": 3
}'

# translate to es DSL
curl -X POST "$HOST/_xpack/sql/translate?pretty" -H 'Content-Type: application/json' -d'
{
    "query": "SELECT * FROM bm ORDER BY longitude DESC limit 3",
    "fetch_size": 3
}'

# execute2（雙引號(hào)里面的字符串）
curl -X POST "$HOST/_xpack/sql?format=txt" -H 'Content-Type: application/json' -d"
{
    \"query\": \"SELECT country_code, population AS sum_pop FROM bm WHERE population > 1 AND country_code = 'CN' ORDER BY population DESC\",
    \"fetch_size\": 11
}"

translate from es DSL

稍復(fù)雜語(yǔ)句

mysql

我們先看在mysql數(shù)據(jù)庫(kù)下面曼库，這些復(fù)雜語(yǔ)句的語(yǔ)法準(zhǔn)確性懦胞。

mysql-process

es-sql

only group by

only group by

translate from es DSL與execute的返回結(jié)果一致

group by with order by

當(dāng)在group by之后添加order by，es-sql就不能正常解析了凉泄。而在es-DSL里面是可以實(shí)現(xiàn)這個(gè)agg-sort功能的。

es-sql fail with group-order

根據(jù)上一節(jié)的without order by解析出來的DSL蚯根，再配合agg-sort這個(gè)功能后众，來實(shí)現(xiàn)group-order。

without order

with order

# without order
curl -X POST "$HOST/bm/_search?pretty" -H 'Content-Type: application/json' -d'
{
  "size" : 0,
  "query" : {
    "range" : {
      "population" : {
        "from" : 0,
        "to" : null,
        "include_lower" : true,
        "include_upper" : false,
        "boost" : 1.0
      }
    }
  },
  "_source" : false,
  "stored_fields" : "_none_",
  "aggregations" : {
    "groupby" : {
      "composite" : {
        "size" : 11,
        "sources" : [
          {
            "1674" : {
              "terms" : {
                "field" : "country_code",
                "order" : "asc"
              }
            }
          }
        ]
      },
      "aggregations" : {
        "1683" : {
          "sum" : {
            "field" : "population"
          }
        }
      }
    }
  }
}
'

# with order
curl -X POST "$HOST/bm/_search?pretty" -H 'Content-Type: application/json' -d'
{
  "size" : 0,
  "query" : {
    "range" : {
      "population" : {
        "from" : 0,
        "to" : null,
        "include_lower" : true,
        "include_upper" : false,
        "boost" : 1.0
      }
    }
  },
  "_source" : false,
  "stored_fields" : "_none_",
  "aggregations" : {
    "groupby" : {
      "composite" : {
        "size" : 11,
        "sources" : [
          {
            "1674" : {
              "terms" : {
                "field" : "country_code",
                "order" : "asc"
              }
            }
          }
        ]
      },
      "aggregations" : {
        "1683" : {
          "sum" : {
            "field" : "population"
          }
        }
        ,"population_bucket_sort": {
            "bucket_sort": {
                "sort": [
                  {"1683": {"order": "desc"}}
                ]
            }
        }
      }
    }
  }
}
'

Others

es-sql source code

不知道這個(gè)fix/enhancement是否可以在es-string通過antlr義成AST的es-DSL颅拦。有時(shí)間再回頭看這個(gè)issue蒂誉。

costin回復(fù)說Bucket Sort Aggregation只是局部排序，非全局排序距帅。但是至于如何實(shí)現(xiàn)全局排序右锨，我仍然沒有弄明白。

costin reply