SQL插件

更新时间：2024-07-12

7.4.2、7.10.2版本中我们提供Open Distro for Elasticsearch SQL插件，允许用户使用SQL而不是Elasticsearch查询域特定语言（DSL）编写查询语句。

基本操作:

要使用该功能，需要将请求发送到_opendistro/_sqlURI。用户可以使用请求参数或请求正文（推荐）。

GET https://<host>:<port>/_opendistro/_sql?sql=select * from my-index limit 50

POST https://<host>:<port>/_opendistro/_sql
{
  "query": "SELECT * FROM my-index LIMIT 50"
}

还可以使用curl命令：

curl -XPOST  -u username:password -k -d '{"query": "SELECT * FROM my-index LIMIT 10"}' -H 'Content-Type: application/json'

默认情况下，查询返回JSON，但您也可以选择CSV格式返回数据，需要对format参数进行设置：

POST _opendistro/_sql?format=csv
{
  "query": "SELECT * FROM my-index LIMIT 50"
}

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

data
2
1
3

format也支持json参数（格式与默认方式不同），如下所示：

POST _opendistro/_sql?format=json
{
    "query":"select * from my-index limit 10"
}
响应：
{
   "total": 3,
   "size": 3,
   "columns": [
      {
         "name": "data",
         "type": "long"
      }
   ],
   "rows": [
      [
         2
      ],
      [
         1
      ],
      [
         3
      ]
   ],
   "status": 200
}

支持操作

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

声明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

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

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字段

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

函数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-in

说明：

1.字符串函数使用：

必须在文档映射中启用fielddata，字符串函数（例如：substring、concat_ws、trim）才能正常工作。

2.date_format使用说明：

date_format函数只能针对date类型的字段使用，下面是一个完整的示例：

1.创建包含date类型字段的索引

PUT my_index
{
  "mappings": {
    "properties": {
      "date": {
        "type": "date" 
      }
    }
  }
}

2.写入数据
PUT my_index/_doc/2
{ "date": "2022-10-26T10:46:30Z" }

3.函数使用
POST /_opendistro/_sql?format=json
{
  "query": "select DATE_FORMAT(date, 'Y-M-d') from my_index"
}

连接操作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	joinSELECT 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

展示Show

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

Show	Example
Show tables like	SHOW TABLES LIKE logs-*
Show tables	SHOW TABLES

连接操作Joins

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

注意：

Sql插件在使用时，需要对线上使用的语句，需进行充分完整的测试。

ingest attachment插件

数据迁移

百度智能云

Elasticsearch BES

Elasticsearch BES

SQL插件

基本操作:

支持操作