Blog Content

ElasticSearch opendistro插件的SQL特性支持及使用

ElasticSearch   2020-04-18 11:36:47

---

SQL特性支持

语句

语句	例子
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

注意：Like any complex query, large UNION and MINUS statements can strain or even crash your cluster.

条件查询

条件	例子
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

聚合函数

聚合函数	例子
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

字段包含与排除

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

函数

You must enable fielddata in the document mapping for most string functions to work properly.

函数	例子
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

连接

连接	例子
Inner join	SELECT p.firstname, p.lastname, p.gender, dogs.name FROM people p JOIN dogs d  ON d.holdersName = p.firstname WHERE p.age > 12 AND d.age > 1
Left outer join	SELECT p.firstname, p.lastname, p.gender, dogs.name FROM people p LEFT JOIN dogs d ON d.holdersName = p.firstname
Cross join	SELECT p.firstname, p.lastname, p.gender, dogs.name FROM people p CROSS JOIN dogs d

连接约束

1. 仅能连接两个索引。

2. 必须对索引使用别名。

3. ON连接中，仅能使用AND条件。

4. 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)

5. 不能对结果集使用GROUP BY or ORDER BY 。

6. 不支持LIMIT with OFFSET。

显示

显示	例子
Show tables like	SHOW TABLES LIKE logs-*

参考连接：https://opendistro.github.io/for-elasticsearch-docs/docs/sql/operations/  

---

上一篇：Elasticsearch plugin opendistro-sql插件安装
下一篇：ElasticSearch opendistro插件的CURL方式的SQL查询