更新时间:2023-06-20 GMT+08:00

查询Elasticsearch SQL

在6.5.4及之后版本中提供Open Distro for Elasticsearch SQL插件允许您使用SQL而不是Elasticsearch查询域特定语言(DSL)编写查询。

如果您已经熟悉SQL并且不想学习DSL查询,那么此功能是一个很好的选择。

基本操作

  • Kibana(推荐)
    • 登录Kibana,在DevTools中将请求发送到_opendistro/_sqlURI,可以使用请求参数或请求正文。
      GET _opendistro/_sql?sql=select * from my-index limit 50
      POST _opendistro/_sql
      {
        "query": "SELECT * FROM my-index LIMIT 50"
      }
    • 默认情况下,查询返回JSON。您也可以选择CSV格式返回数据,选择CSV格式需要对format参数进行如下设置:
      POST _opendistro/_sql?format=csv
      {
        "query": "SELECT * FROM my-index LIMIT 50"
      }

      CSV格式返回数据时,每行对应一个文档,每列对应一个字段。

  • curl命令

    您也可以在ECS中使用curl命令,来执行该SQL操作。

    curl -XPOST https://localhost:9200/_opendistro/_sql -u username:password -k -d '{"query": "SELECT * FROM kibana_sample_data_flights LIMIT 10"}' -H 'Content-Type: application/json'

支持操作

支持的SQL操作包括声明、条件、聚合函数、Include和Exclude、常用函数、连接join和展示等操作。

  • 声明statements
    表1 声明statements

    Statement

    Example

    Select

    SELECT * FROM my-index

    Delete

    DELETE FROM my-index WHERE _id=1

    Where

    SELECT * FROM my-index WHERE ['field']='value'

    Order by

    SELECT * FROM my-index ORDER BY _id asc

    Group by

    SELECT * FROM my-index GROUP BY range(age, 20,30,39)

    Limit

    SELECT * FROM my-index LIMIT 50 (default is 200)

    Union

    SELECT * FROM my-index1 UNION SELECT * FROM my-index2

    Minus

    SELECT * FROM my-index1 MINUS SELECT * FROM my-index2

    与任何复杂查询一样,大型UNION和MINUS语句可能会使集群资源紧张甚至崩溃。

  • 条件Conditions
    表2 条件Conditions

    Condition

    Example

    Like

    SELECT * FROM my-index WHERE name LIKE 'j%'

    And

    SELECT * FROM my-index WHERE name LIKE 'j%' AND age > 21

    Or

    SELECT * FROM my-index WHERE name LIKE 'j%' OR age > 21

    Count distinct

    SELECT count(distinct age) FROM my-index

    In

    SELECT * FROM my-index WHERE name IN ('alejandro', 'carolina')

    Not

    SELECT * FROM my-index WHERE name NOT IN ('jane')

    Between

    SELECT * FROM my-index WHERE age BETWEEN 20 AND 30

    Aliases

    SELECT avg(age) AS Average_Age FROM my-index

    Date

    SELECT * FROM my-index WHERE birthday='1990-11-15'

    Null

    SELECT * FROM my-index WHERE name IS NULL

  • 聚合函数Aggregation
    表3 聚合函数Aggregation

    Aggregation

    Example

    avg()

    SELECT avg(age) FROM my-index

    count()

    SELECT count(age) FROM my-index

    max()

    SELECT max(age) AS Highest_Age FROM my-index

    min()

    SELECT min(age) AS Lowest_Age FROM my-index

    sum()

    SELECT sum(age) AS Age_Sum FROM my-index

  • Include和Exclude字段
    表4 Include和Exclude

    Pattern

    Example

    include()

    SELECT include('a*'), exclude('age') FROM my-index

    exclude()

    SELECT exclude('*name') FROM my-index

  • 函数Functions
    表5 函数Functions

    Function

    Example

    floor

    SELECT floor(number) AS Rounded_Down FROM my-index

    trim

    SELECT trim(name) FROM my-index

    log

    SELECT log(number) FROM my-index

    log10

    SELECT log10(number) FROM my-index

    substring

    SELECT substring(name, 2,5) FROM my-index

    round

    SELECT round(number) FROM my-index

    sqrt

    SELECT sqrt(number) FROM my-index

    concat_ws

    SELECT concat_ws(' ', age, height) AS combined FROM my-index

    /

    SELECT number / 100 FROM my-index

    %

    SELECT number % 100 FROM my-index

    date_format

    SELECT date_format(date, 'Y') FROM my-index

    必须在文档映射中启用fielddata才能使大多数字符串函数正常工作。

  • 连接操作Joins
    表6 连接操作Joins

    Join

    Example

    Inner join

    SELECT s.firstname, s.lastname, s.gender, sc.name FROM student s JOIN school sc ON sc.name = s.school_name WHERE s.age > 20

    Left outer join

    SELECT s.firstname, s.lastname, s.gender, sc.name FROM student s LEFT JOIN school sc ON sc.name = s.school_name

    Cross join

    SELECT s.firstname, s.lastname, s.gender, sc.name FROM student s CROSS JOIN school sc

    相关约束和限制,参考连接操作Joins

  • 展示Show

    展示show操作与索引模式匹配的索引和映射。您可以使用*或%使用通配符。

    表7 展示show

    Show

    Example

    Show tables like

    SHOW TABLES LIKE logs-*

连接操作Joins

Open Distro for Elasticsearch SQL支持inner joins, left outer joins,和cross joins。Join操作有许多约束:

  • 您只能加入两个参数。
  • 您必须为索引使用别名(例如people p)。
  • 在ON子句中,您只能使用AND条件。
  • 在WHERE语句中,不要将包含多个索引的树组合在一起。例如,以下语句有效:
    WHERE (a.type1 > 3 OR a.type1 < 0) AND (b.type2 > 4 OR b.type2 < -1)
    以下声明无效:
    WHERE (a.type1 > 3 OR b.type2 < 0) AND (a.type1 > 4 OR b.type2 < -1)
  • 您不能使用GROUP BY或ORDER BY来获得结果。
  • LIMIT和OFFSET不支持一起使用(例如LIMIT 25 OFFSET 25)。

JDBC驱动

Java数据库连接(JDBC)驱动程序允许您将Open Distro for Elasticsearch与您的商业智能(BI)应用程序集成。

有关下载和使用JAR文件的信息,请参阅GitHub仓库