文档首页/ 云搜索服务 CSS/ 用户指南/ 使用OpenSearch搜索数据/ 使用OpenSearch集群搜索数据/ 使用SQL语言在OpenSearch中搜索数据
更新时间：2024-10-12 GMT+08:00
查看PDF
分享

使用SQL语言在OpenSearch中搜索数据

在OpenSearch版本中提供Open Distro for Elasticsearch SQL插件允许您使用SQL而不是Elasticsearch查询域特定语言（DSL）编写查询。

熟悉SQL语言的用户，可以使用SQL语言在OpenSearch中搜索数据。

SQL使用示例

  • 在Kibana中使用SQL语言搜索数据（推荐）

    在Kibana的DevTools中将请求发送到“_plugins/_sql”，可以使用请求参数或请求正文。

    例如，执行如下命令，从“my-index”索引中搜索出50条数据。 
    1
2
3
4
    		 
    POST _plugins/_sql
{
  "query": "SELECT * FROM my-index LIMIT 50"
}
    默认情况下，查询结果返回的是JSON格式的数据。当需要返回CSV格式的数据时，则需要在命令中对format参数进行如下设置： 
    1
2
3
4
    		 
    POST _plugins/_sql?format=csv
{
  "query": "SELECT * FROM my-index LIMIT 50"
}

    查询结果返回CSV格式的数据时，每行对应一个文档，每列对应一个字段。

  • 在ECS中使用Curl命令执行SQL搜索数据

    例如，执行如下命令，从“kibana_sample_data_flights”索引中搜索出10条数据。

    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'

    localhost表示集群的访问地址，username和password分别表示安全模式集群的用户名和密码。

支持的SQL操作

支持的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仓库。