Elasticsearch SQL

最新推荐文章于 2024-08-03 21:48:37 发布

路言汐

最新推荐文章于 2024-08-03 21:48:37 发布

阅读量4k

点赞数 3

分类专栏： ElasticSearch 文章标签： elasticsearch

本文链接：https://blog.csdn.net/vdora/article/details/111573899

版权

ElasticSearch 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

Elasticsearch SQL

Elasticsearch SQL是一个X-Pack组件，它允许针对Elasticsearch实时执行类似SQL的查询。无论使用REST接口，命令行还是JDBC，任何客户端都可以使用SQL对Elasticsearch中的数据进行原生搜索和聚合数据。可以将Elasticsearch SQL看作是一种翻译器，它可以将SQL翻译成Query DSL。

官方文档(ElasticSearch[7.x])

语法

在ES中使用SQL查询的语法与在数据库中使用基本一致，具体格式如下：

SELECT [TOP [ count ] ] select_expr [, ...]
[ FROM table_name ]
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition]
[ ORDER BY expression [ ASC | DESC ] [, ...] ]
[ LIMIT [ count ] ]
[ PIVOT ( aggregation_expr FOR column IN ( value [ [ AS ] alias ] [, ...] ) ) ]

WHERE

可以使用WHERE语句设置查询条件，比如查询age字段为19的记录，查询语句如下:

POST /_sql?format=txt
{
  "query": "SELECT address,createTime,name FROM city WHERE age=19 LIMIT 10 "
}

查询结果：

GROUP BY

我们可以使用GROUP BY语句对数据进行分组，统计出分组记录数量，最大age和平均salary等信息，查询语句如下:

POST /_sql?format=txt
{
  "query": "SELECT createTime,COUNT(*),MAX(age),AVG(salary) FROM city GROUP BY createTime LIMIT 10"
}

查询结果：

字段为text类型时（解决方案）：

POST /_sql?format=txt
{
  "query": "SELECT id FROM test GROUP BY id "
}

查询结果：

HAVING

我们可以使用HAVING语句对分组数据进行二次筛选，比如筛选分组记录数量大于15的信息，查询语句如下:

POST /_sql?format=txt
{
  "query": "SELECT age,COUNT(*),AVG(salary) FROM city GROUP BY age HAVING count(*)>15 LIMIT 10"
}

查询结果：

ORDER BY

我们可以使用ORDER BY语句对数据进行排序（text类型的字段须注意的地方），比如按照salary字段从高到低排序，查询语句如下:

POST /_sql?format=txt
{
  "query": "SELECT createTime,age,salary FROM city ORDER BY salary DESC LIMIT 10 "
}

查询结果：

DESCRIBE

我们可以使用DESCRIBE语句查看表（ES中为索引）中有哪些字段，比如查看alicia表的字段，查询语句如下：

POST /_sql?format=txt
{
  "query": "DESCRIBE alicia"
}

查询结果：

SHOW TABLES

我们可以使用SHOW TABLES查看所有的表（ES中为索引），查询语句如下：

POST /_sql?format=txt
{
  "query": "SHOW TABLES"
}

查询结果：

常用SQL操作

常用的方法和操作

比较操作

操作	操作符
等于	=
不等于	<> 或者 !=
比较	<，<=，>，>=
介于两个值之间的数据范围	BETWEEN
为空/不为空	IS NULL/IS NOT NULL
在 WHERE 子句中规定多个值	IN(, , …)

示例

SELECT name FROM alicia WHERE salary = 100 LIMIT 5;
SELECT name FROM alicia WHERE salary <> 200 ORDER BY createTime LIMIT 5;
SELECT name FROM alicia WHERE salary < 1000 ORDER BY createTime LIMIT 5;
SELECT name FROM alicia WHERE salary BETWEEN 100 AND 200 ORDER BY createTime;
SELECT name FROM alicia WHERE salary IS NOT NULL AND  IS NULL;
SELECT name FROM alicia WHERE salary IN (10000, 10001, 10002, 999) ORDER BY emp_no LIMIT 5;
SELECT name FROM alicia WHERE salary IN (10000, 10001, 10002, 999) ORDER BY emp_no LIMIT 5;

逻辑操作

操作	操作符
并且	AND
或者	OR
否定	NOT

示例

SELECT name FROM alicia WHERE salary >= 100 AND salary < 200 ORDER BY createTime LIMIT 5;
SELECT name FROM alicia WHERE salary < 200 OR salary = 100 ORDER BY createTime LIMIT 5;
SELECT name FROM alicia WHERE NOT salary = 200 LIMIT 5;

数学运算

操作	操作符
加	+
减	-
负	-
乘	*
除	/
取模/取余	%

示例

SELECT 1 + 1 AS x;
SELECT 1 - 1 AS x;
SELECT - 1 AS x;
SELECT 2 * 3 AS x;
SELECT 6 / 3 AS x;
SELECT 5 % 2 AS x;

类型转换操作

操作	操作符
类型转换	::

示例

SELECT '123'::long AS long;

LIKE 和 RLIKE 运算符

即使在过滤时，LIKE/RLIKE是一种有效的方案，但是全文搜索谓词MATCH和QUERY更快更强大，更适合作为首选方案（使用方法）。

如果与LIKE / RLIKE一起使用的字段没有精确的（keyword类型的）非归一化子字段，Elasticsearch SQL将无法运行查询。如果该字段是

精确字段或者有精确的子字段，它将按原样使用它，或者即使未在语句中明确指定，它也会自动使用精确的子字段。

示例

SELECT name FROM alicia WHERE name LIKE 'ali%'; 
SELECT name FROM city WHERE name RLIKE 'ali.*';

聚合函数

一、一般用途

用途	函数名
返回平均值	AVG
返回匹配指定条件的行数	COUNT
返回非重复结果的数目	COUNT(DISTINCT)
返回指定的字段中第一个记录的值	FIRST/FIRST_VALUE
返回指定的字段中最后一个记录的值	LAST/LAST_VALUE
返回指定列的最大值	MAX
返回指定列的最小值	MIN
返回数值列的总数	SUM

注意：

1、FIRST/LAST不能用于类型为text的列，除非这个字段也另存为keyword。

2、MAX作用于text或者keyword字段时，被翻译为LAST/LAST_VALUE，因此，它不能在HAVING字句中使用。

3、MIN作用于text或者keyword字段时，被翻译为FIRST/FIRST_VALUE，因此，它不能在HAVING字句中使用。

示例：

SELECT AVG(salary) AS avg FROM city;
SELECT COUNT(*) AS count FROM city;
SELECT COUNT(ALL name) AS count_all, COUNT(DISTINCT remark) count_distinct FROM city;
SELECT COUNT(DISTINCT name) unique_name, COUNT(salary) AS salary FROM city;
SELECT FIRST(name) FROM city;
SELECT createTime, FIRST(name) FROM city GROUP BY createTime ORDER BY createTime;
SELECT LAST(name) FROM city;
SELECT createTime, LAST(name) FROM city GROUP BY createTime ORDER BY createTime;
SELECT MAX(salary) AS max FROM city;
SELECT MIN(salary) AS min FROM city;
SELECT SUM(salary) AS sum FROM city;

二、统计

用途	函数名
峰度（描述总体中所有取值分布形态陡缓程度）	KURTOSIS
中位数绝对偏差	MAD
百分位数	PERCENTILE
百分等级	PERCENTILE_RANK
偏态系数（统计数据分布偏斜方向和程度）	SKEWNESS
总体标准方差	STDDEV_POP
平方和	SUM_OF_SQUARES
总体方差	VAR_POP

注意：

1、KURTOSIS只能直接在字段上使用

2、SKEWNESS只能直接在字段上使用

示例

SELECT MIN(salary) AS min, MAX(salary) AS max, KURTOSIS(salary) AS k FROM city;
SELECT MIN(salary) AS min, MAX(salary) AS max, AVG(salary) AS avg, MAD(salary) AS mad FROM city;
SELECT name, PERCENTILE(salary, 95) AS "95th" FROM city GROUP BY name;
SELECT name, PERCENTILE_RANK(salary, 65000) AS rank FROM city GROUP BY name;
SELECT MIN(salary) AS min, MAX(salary) AS max, SKEWNESS(salary) AS s FROM city;
SELECT MIN(salary) AS min, MAX(salary) AS max, STDDEV_POP(salary) AS stddev FROM city;
SELECT MIN(salary) AS min, MAX(salary) AS max, SUM_OF_SQUARES(salary) AS sumsq FROM city;
SELECT MIN(salary) AS min, MAX(salary) AS max, VAR_POP(salary) AS varpop FROM city;

分组功能

用途	函数
直方图	HISTOGRAM

示例

SELECT HISTOGRAM(salary, 5000) AS h FROM city GROUP BY h
SELECT HISTOGRAM(createTime, INTERVAL 1 YEAR) AS h, COUNT(*) AS c FROM city GROUP BY h
SELECT HISTOGRAM(salary % 100, 10) AS h, COUNT(*) AS c FROM city GROUP BY h

注意：

1、HISTOGRAM允许自定义表达式，但是在GROUP BY子句中，不能将HISTOGRAM应用于其他函数：

SELECT MONTH(HISTOGRAM(birth_date), 2)) AS h, COUNT(*) as c FROM city GROUP BY h ORDER BY h DESC;

相反地，可以在HISTOGRAM中应用其他函数：

SELECT HISTOGRAM(MONTH(createTime), 2) AS h, COUNT(*) as c FROM city GROUP BY h ORDER BY h DESC

2、当histogram在SQL中被应用于DATE类型而不是DATETIME时，指定的间隔将被截断为一天的倍数。例如，对于HISTOGRAM(CAST(birthDate AS DATE), INTERVAL '2 3:04' DAY TO MINUTE)实际使用的间隔为INTERVAL '2' DAY。如果指定的间隔小于1天，例如： HISTOGRAM(CAST(birthDate AS DATE), INTERVAL '20' HOUR)则间隔为INTERVAL '1' DAY。

3、所有指定用于date/time HISTOGRAM的间隔，会使用在它的date_histogram聚合的定义中固定的时间间隔，使用日历间隔的时候，INTERVAL '1' YEAR，INTERVAL '1' MONTH和INTERVAL '1' DAY 有显著的异常。选择日历间隔是为了使YEAR，MONTH和DAY分组的结果更加直观。例如，在YEAR的情况下，日历间隔将一年的时段视为从特定年份的1月1日开始的时段，而固定间隔的一年的时段将一年视为毫秒数（例如，31536000000ms对应365天，每天24小时，每小时60分钟等）。以固定的时间间隔，例如，2019年2月5日属于从2018年12月20日开始的bucket，Elasticsearch SQL将这个实际为2019年的日期返回为2018年的。

4、SQL中的直方图不能应用于TIME类型。例如：HISTOGRAM(CAST(birthDate AS TIME), INTERVAL '10' MINUTES)目前不支持。

支持的函数

使用SQL查询ES中的数据，不仅可以使用一些SQL中的函数，还可以使用一些ES中特有的函数。

查询支持的函数

我们可以使用SHOW FUNCTIONS语句查看所有支持的函数，比如搜索所有带有DATE字段的函数可以使用如下语句:

POST /_sql?format=txt
{
  "query": "SHOW FUNCTIONS LIKE '%DATE%'"
}

查询结果：

示例：

POST /_sql?format=txt
{
  "query": "select CURRENT_DATE "
}

查询结果：

全文搜索函数

全文搜索函数是ES中特有的，当使用MATCH或QUERY函数时，会启用全文搜索功能，SCORE函数可以用来统计搜索评分。

MATCH()

使用MATCH函数查询birthDate为2020-05-01的记录。

POST /_sql?format=txt
{
  "query": "SELECT address,createTime FROM city WHERE MATCH(birthDate,'2020-05-01') LIMIT 10"
}

查询结果：

在这里插入图片描述

QUERY()

使用QUERY函数查询birthDate中包含2020的记录。

POST /_sql?format=txt
{
  "query": "SELECT address,createTime FROM city WHERE QUERY('birthDate:2020') LIMIT 10"
}

查询结果：

在这里插入图片描述

其他相关用法请参考官方文档

局限性

使用SQL查询ES有一定的局限性，没有原生的Query DSL那么强大，对于嵌套属性和某些函数的支持并不怎么好，但是平时用来查询下数据基本够用了。

一、使用text类型的字段比较、排序或者聚合

text类型的字段不是用来排序或者聚合的，因为它们的实际值（actual value）取决于分词器。因此，ElasticSearch提供了keyword字段用于存储字段的精确值（exact value）。

在大多数情况下，默认设置由多字段（multi field）支持的ElasticSearch的两种字符串类型，即以多种方式索引同一字符串。例如，既将其作为搜索的text索引，又作为对排序和聚合的keyword索引。

由于SQL需要精确的值，因此当遇到text字段时，Elasticsearch SQL将搜索可用于比较、排序和聚合的多字段（multi field）。为此，它将搜索可以找到的未归一化（not normalized ）的第一个keyword，并将其用作字段原始的精确值（exact value）。

示例：

（1）比较

POST /_sql?format=txt
{
  "query": " SELECT * FROM city where birthDate > '2000-01-01'  "
}

查询结果：

（2）排序

POST /_sql?format=txt
{
  "query": " SELECT * FROM city  order by name  "   
}

查询结果：

在这里插入图片描述

（3）聚合

POST /_sql?format=txt
{
  "query": " SELECT count(name) FROM city "  
}

查询结果：
在这里插入图片描述

解决方案：

1、创建额外的关键字s映射

PUT test/_mapping
{
 "properties":{
   "id":{
     "type": "text",
     "fields": {
        "keyword": { 
        "type": "keyword"
      }
    }
  }
}

若创建成功，则返回：
在这里插入图片描述

此时，索引中的字段如下：

在这里插入图片描述

创建成功后即可用上述的SQL语句进行查询操作。

二、使用substring的情况下做聚合

查询生日在2020年2月，并且日期大于2号的人员的平均工资：

POST /_sql?format=txt
{
  "query": "select avg(salary) from city where substring(birthDate,1,7) = '2020-02' and substring(birthDate,9,2) > '02' "    
}

查询结果：

在这里插入图片描述

解决方案：

1、将使用substring的字段设置为keyword类型

2、若字段为text类型，可创建额外的关键字映射（解决方案）

3、使用MATCH/QUERY查询（使用方法）

三、WHERE和ORDER BY子句中不允许使用嵌套字段的标量函数

除比较和逻辑运算符外，Elasticsearch SQL不支持在WHERE 和ORDER BY子句中的嵌套字段之上使用标量函数。

例如：

SELECT * FROM test_emp WHERE LENGTH(dep.dep_name.keyword) > 5;

和

SELECT * FROM test_emp ORDER BY YEAR(dep.start_date);

不支持，但是：

SELECT * FROM test_emp WHERE dep.start_date >= CAST('2020-01-01' AS DATE) OR dep.dep_end_date IS NULL;

支持。

四、多嵌套字段

Elasticsearch SQL不支持多嵌套文档，因此查询不能引用一个索引中的多个嵌套字段。这适用于多级嵌套字段，但也适用于在同一级别上定义的多个嵌套字段。例如，对于此索引：

       column         |     type      |    mapping
----------------------+---------------+-------------
nested_A              |STRUCT         |NESTED
nested_A.nested_X     |STRUCT         |NESTED
nested_A.nested_X.text|VARCHAR        |KEYWORD
nested_A.text         |VARCHAR        |KEYWORD
nested_B              |STRUCT         |NESTED
nested_B.text         |VARCHAR        |KEYWORD

nested_A并且nested_B不能同时使用，也不能与nested_A/nested_B和nested_A.nested_X组合使用。在这种情况下，Elasticsearch SQL将显示一条错误消息。

五、分页嵌套的内部命中

当选择一个嵌套字段，分页不会按预期起作用，Elasticsearch SQL至少会返回页面大小的记录。这是因为嵌套查询在Elasticsearch中的工作方式：将返回根嵌套字段以及匹配的内部嵌套字段，分页发生在根嵌套文档上，而不是其内部hits上。

六、归一化keyword字段

Elasticsearch中的keyword字段可以通过定义normalizer来归一化。Elasticsearch SQL不支持此类字段。

七、字段的数组类型

由于Elasticsearch处理值数组的“不可见”方式，因此不支持数组字段：mapping不指示字段是否为数组（具有多个值），因此，在不读取所有数据的情况下，Elasticsearch SQL无法知道字段是单个值还是多个值。当为一个字段返回多个值时，默认情况下，Elasticsearch SQL将引发异常。但是，可以通过field_multi_value_leniencyREST（默认情况下禁用）或field.multi.value.leniency驱动程序（默认情况下启用）中的参数更改此行为。

八、按聚合排序

执行聚合（GROUP BY）时，Elasticsearch SQL依靠Elasticsearch的composite聚合来支持分页结果。但是，这种类型的聚合确实有局限性：排序只能应用于用于聚合buckets的键。Elasticsearch SQL通过进行client-side排序来克服此限制，但是作为一种安全的方法，它最多只允许65535行。

建议将LIMIT查询用于按聚合排序的查询，从本质上指示所需的前N个结果：

SELECT * FROM test GROUP BY age ORDER BY COUNT （*）LIMIT 100 ;

可能和没有LIMIT的情况下得到相同的查询结果，但是如果超过了最大大小（10000），将返回异常，因为Elasticsearch SQL无法追踪（和排序）所有返回的结果。

此外，在ORDER BY中使用的聚合必须仅是纯聚合函数。不能使用标量函数或运算符，因此不能使用组合两个或多个聚合函数的复杂列进行排序。以下是一些不允许的查询示例：

SELECT age, ROUND(AVG(salary)) AS avg FROM test GROUP BY age ORDER BY avg;

SELECT age, MAX(salary) - MIN(salary) AS diff FROM test GROUP BY age ORDER BY diff;

九、使用sub-select

在某种程度上，支持使用sub-selects（SELECT X FROM (SELECT Y)）：Elasticsearch SQL可以将任何的sub-selects“扁平化”为单个的子选择。例如：SELECT

SELECT * FROM (SELECT first_name, last_name FROM emp WHERE last_name NOT LIKE '%a%') WHERE first_name LIKE 'A%' ORDER BY 1;

  first_name   |   last_name
---------------+---------------
 Alejandro     |McAlpine
 Anneke        |Preusig
 Anoosh        |Peyn
 Arumugam      |Ossenbruggen

上面的查询是可能的，因为它等价于：

SELECT first_name, last_name FROM emp WHERE last_name NOT LIKE '%a%' AND first_name LIKE 'A%' ORDER BY 1;

但是，如果sub-select包含GROUP BY、HAVING或者包含的内容SELECT比SELECT X FROM (SELECT ...) WHERE [simple_condition]复杂，则当前不支持。

十、在 HAVING 子句中使用 FIRST/LAST聚合函数

不支持在HAVING子句中使用FIRST 和LAST ，当目标列是keyword类型时，这同样适用于MIN和MAX，因为它们在内部转换为FIRST

十一、在GROUP BY中使用TIME 数据类型或者HISTOGRAM

当前不支持将TIME数据类型用作分组键。例如：

SELECT count(*) FROM test GROUP BY CAST(date_created AS TIME);

另一方面，如果它用返回另一种数据类型的标量函数包装，则仍然可以使用，例如：

SELECT count(*) FROM test GROUP BY MINUTE((CAST(date_created AS TIME));

HISTOGRAM分组功能当前也不支持TIME 数据类型，例如：

SELECT HISTOGRAM(CAST(birth_date AS TIME), INTERVAL '10' MINUTES) as h, COUNT(*) FROM t GROUP BY h

路言汐

关注

3
点赞
踩
11

收藏

觉得还不错? 一键收藏
0
评论
Elasticsearch SQL

Elasticsearch SQLElasticsearch SQL是一个X-Pack组件，它允许针对Elasticsearch实时执行类似SQL的查询。无论使用REST接口，命令行还是JDBC，任何客户端都可以使用SQL对Elasticsearch中的数据进行原生搜索和聚合数据。可以将Elasticsearch SQL看作是一种翻译器，它可以将SQL翻译成Query DSL。官方文档(ElasticSearch[7.x])语法在ES中使用SQL查询的语法与在数据库中使用基本一致，具体格式如下：
复制链接

扫一扫

专栏目录