查询ElasticSearch：用SQL代替DSL_Hollis Chuang的博客-CSDN博客
查询ElasticSearch：用SQL代替DSL
Hollis Chuang
于 2020-09-18 09:30:00 发布
1053
收藏
文章标签：
sql
数据库
java
mysql
oracle
ES7.x版本的x-pack自带ElasticSearch SQL，我们可以直接通过SQL REST API、SQL CLI等方式使用SQL查询。
SQL REST API
在Kibana Console中输入：
POST /_sql?format=txt
"query": "SELECT * FROM library ORDER BY page_count DESC LIMIT 5"
将上述SQL替换为你自己的SQL语句，即可。返回格式如下：
author | name | page_count | release_date
-----------------+--------------------+---------------+------------------------
Peter F. Hamilton|Pandora's Star |768 |2004-03-02T00:00:00.000Z
Vernor Vinge |A Fire Upon the Deep|613 |1992-06-01T00:00:00.000Z
Frank Herbert |Dune |604 |1965-06-01T00:00:00.000Z
SQL CLI
elasticsearch-sql-cli是安装ES时bin目录的一个脚本文件，也可单独下载。我们在ES目录运行
./bin/elasticsearch-sql-cli https://some.server:9200
输入sql即可查询
sql> SELECT * FROM library WHERE page_count > 500 ORDER BY page_count DESC;
author | name | page_count | release_date
-----------------+--------------------+---------------+---------------
Peter F. Hamilton|Pandora's Star |768 |1078185600000
Vernor Vinge |A Fire Upon the Deep|613 |707356800000
Frank Herbert |Dune |604 |-144720000000
SQL To DSL
在Kibana输入：
POST /_sql/translate
"query": "SELECT * FROM library ORDER BY page_count DESC",
"fetch_size": 10
即可得到转化后的DSL query:
"size": 10,
"docvalue_fields": [
{
"field": "release_date",
"format": "epoch_millis"
}
],
"_source": {
"includes": [
"author",
"name",
"page_count"
],
"excludes": []
},
"sort": [
{
"page_count": {
"order": "desc",
"missing": "_first",
"unmapped_type": "short"
}
}
]
因为查询相关的语句已经生成，我们只需要在这个基础上适当修改或不修改就可以愉快使用DSL了。
下面我们详细介绍下ES SQL 支持的SQL语句 和 如何避免错误使用。
首先需要了解下ES SQL支持的SQL语句中，SQL术语和ES术语的对应关系：
ES SQL的语法支持大多遵循ANSI SQL标准，支持的SQL语句有DML查询和部分DDL查询。DDL查询如：DESCRIBE table,SHOW COLUMNS IN table略显鸡肋，我们主要看下对SELECT,Function的DML查询支持。
SELECT
语法结构如下：
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 ] [, ...] ) ) ]
表示从0-N个表中获取行数据。SQL的执行顺序为：
获取所有 FROM中的关键词，确定表名。如果有WHERE条件，过滤掉所有不符合的行。如果有GROUP BY条件，则分组聚合；如果有HAVING条件，则过滤聚合的结果。上一步得到的结果经过select_expr运算，确定具体返回的数据。如果有 ORDER BY条件，会对返回的数据排序。如果有 LIMIT or TOP条件，会返回上一步结果的子集。
与常用的SQL有两点不同，ES SQL 支持TOP [ count ]和PIVOT ( aggregation_expr FOR column IN ( value [ [ AS ] alias ] [, ...] ) )子句。TOP [ count ] ：如SELECT TOP 2 first_name FROM emp表示最多返回两条数据，不可与LIMIT条件共用。PIVOT子句会对其聚合条件得到的结果进行行转列，进一步运算。这个我是没用过，不做介绍。
FUNCTION
基于上面的SQL我们其实已经能有过滤，聚合，排序，分页功能的SQL了。但是我们需要进一步了解ES SQL中FUNCTION的支持，才能写出丰富的具有全文搜索，聚合，分组功能的SQL。使用SHOW FUNCTIONS 可列举出支持的函数名称和所属类型。
SHOW FUNCTIONS;
name | type
-----------------+---------------
AVG |AGGREGATE
COUNT |AGGREGATE
FIRST |AGGREGATE
FIRST_VALUE |AGGREGATE
LAST |AGGREGATE
LAST_VALUE |AGGREGATE
MAX |AGGREGATE
MIN |AGGREGATE
SUM |AGGREGATE
........
我们主要看下聚合，分组，全文搜索相关的常用函数。
全文匹配函数
MATCH：相当于DSL中的match and multi_match查询。
MATCH(
field_exp, --字段名称
constant_exp, --字段的匹配值
[, options]) --可选项
使用举例：
SELECT author, name FROM library WHERE MATCH(author, 'frank');
author | name
---------------+-------------------
Frank Herbert |Dune
Frank Herbert |Dune Messiah
SELECT author, name, SCORE() FROM library WHERE MATCH('author^2,name^5', 'frank dune');
author | name | SCORE()
---------------+-------------------+---------------
Frank Herbert |Dune |11.443176
Frank Herbert |Dune Messiah |9.446629
QUERY：相当于DSL中的 query_string 查询。
QUERY(
constant_exp --匹配值表达式
[, options]) --可选项
使用举例：
SELECT author, name, page_count, SCORE() FROM library WHERE QUERY('_exists_:"author" AND page_count:>200 AND (name:/star.*/ OR name:duna~)');
author | name | page_count | SCORE()
------------------+-------------------+---------------+---------------
Frank Herbert |Dune |604 |3.7164764
Frank Herbert |Dune Messiah |331 |3.4169943
SCORE()：返回输入数据和返回数据的相关度relevance.使用举例：
SELECT SCORE(), * FROM library WHERE MATCH(name, 'dune') ORDER BY SCORE() DESC;
SCORE() | author | name | page_count | release_date
---------------+---------------+-------------------+---------------+--------------------
2.2886353 |Frank Herbert |Dune |604 |1965-06-01T00:00:00Z
1.8893257 |Frank Herbert |Dune Messiah |331 |1969-10-15T00:00:00Z
聚合函数
AVG(numeric_field) ：计算数字类型的字段的平均值。
SELECT AVG(salary) AS avg FROM emp;
COUNT(expression)：返回输入数据的总数，包括COUNT()时field_name对应的值为null的数据。COUNT(ALL field_name)：返回输入数据的总数，不包括field_name对应的值为null的数据。COUNT(DISTINCT field_name)：返回输入数据中field_name对应的值不为null的总数。SUM(field_name)：返回输入数据中数字字段field_name对应的值的总和。MIN(field_name)：返回输入数据中数字字段field_name对应的值的最小值。MAX(field_name)：返回输入数据中数字字段field_name对应的值的最大值。
分组函数
这里的分组函数是对应DSL中的bucket分组。
HISTOGRAM：语法如下：
HISTOGRAM(
numeric_exp, --数字表达式，通常是一个field_name
numeric_interval --数字的区间值
HISTOGRAM(
date_exp, --date/time表达式，通常是一个field_name
date_time_interval --date/time的区间值
如下返回每年1月1号凌晨出生的数据:
ELECT HISTOGRAM(birth_date, INTERVAL 1 YEAR) AS h, COUNT(*) AS c FROM emp GROUP BY h;
h | c
------------------------+---------------
null |10
1952-01-01T00:00:00.000Z|8
1953-01-01T00:00:00.000Z|11
1954-01-01T00:00:00.000Z|8
1955-01-01T00:00:00.000Z|4
1956-01-01T00:00:00.000Z|5
1957-01-01T00:00:00.000Z|4
1958-01-01T00:00:00.000Z|7
1959-01-01T00:00:00.000Z|9
1960-01-01T00:00:00.000Z|8
1961-01-01T00:00:00.000Z|8
1962-01-01T00:00:00.000Z|6
1963-01-01T00:00:00.000Z|7
1964-01-01T00:00:00.000Z|4
1965-01-01T00:00:00.000Z|1
ES SQL局限性
因为ES SQL和ES DSL在功能上并非完全匹配，官方文档提到的SQL局限性有：
大的查询可能抛ParsingException
在解析阶段，极大的查询会占用过多的内存，在这种情况下，Elasticsearch SQL引擎将中止解析并抛出错误。
nested类型字段的表示方法
SQL中不支持nested类型的字段，只能使用
[nested_field_name].[sub_field_name]
这种形式来引用内嵌子字段。使用举例：
SELECT dep.dep_name.keyword FROM test_emp GROUP BY languages;
nested类型字段不能用在where 和 order by 的Scalar函数上
如以下SQL都是错误的
SELECT * FROM test_emp WHERE LENGTH(dep.dep_name.keyword) > 5;
SELECT * FROM test_emp ORDER BY YEAR(dep.start_date);
不支持多个nested字段的同时查询
如嵌套字段nested_A和nested_B无法同时使用。
nested内层字段分页限制
当分页查询有nested字段时，分页结果可能不正确。这是因为：ES中的分页查询发生在Root nested document上，而不是它的内层字段上。
keyword类型的字段不支持normalizer
不支持数组类型的字段
这是因为在SQL中一个field只对应一个值，这种情况下我们可以使用上面介绍的 SQL To DSL的API 转化为DSL语句，用DSL查询就好了。
聚合排序的限制
排序字段必须是聚合桶中的字段，ES SQL CLI突破了这种限制，但上限不能超过512行，否则在sorting阶段会抛异常。推荐搭配Limit子句使用，如：
SELECT * FROM test GROUP BY age ORDER BY COUNT(*) LIMIT 100;
聚合排序的排序条件不支持Scalar函数或者简单的操作符运算。聚合后的复杂字段（比如包含聚合函数）也是不能用在排序条件上的。
以下是错误例子：
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;
子查询的限制
子查询中包含GROUP BY or HAVING 或者比SELECT X FROM (SELECT ...) WHERE [simple_condition]这种结构复杂，都是可能执行不成功的。
TIME 数据类型的字段不支持GROUP BY条件和HISTOGRAM函数
如以下查询是错误的：
SELECT count(*) FROM test GROUP BY CAST(date_created AS TIME);
SELECT HISTOGRAM(CAST(birth_date AS TIME), INTERVAL '10' MINUTES) as h, COUNT(*) FROM t GROUP BY h
但是将TIME类型的字段包装为Scalar函数返回是支持GROUP BY的，如：
SELECT count(*) FROM test GROUP BY MINUTE((CAST(date_created AS TIME));
返回字段的限制如果一个字段不在source中存储，是无法查询到的。keyword, date, scaled_float, geo_point, geo_shape这些类型的字段不受这种限制，因为他们不是从_source中返回，而是从docvalue_fields中返回。
有道无术，术可成；有术无道，止于术
欢迎大家关注Java之道公众号
好文章，我在看❤️
Hollis Chuang
关注
关注
点赞
收藏
评论
查询ElasticSearch：用SQL代替DSL
ES7.x版本的x-pack自带ElasticSearch SQL，我们可以直接通过SQL REST API、SQL CLI等方式使用SQL查询。SQL REST API在Kibana ...
复制链接
扫一扫
SQL to ElasticSearch DSL
colorknight的专栏
04-20
5690
众所周知ElasticSearch目前是一个应用最为广泛的分布式搜索与分析引擎，它的功能强大，能够已很高的性能访问大规模数据。它拥有强大的查询分析语法，能够完成模糊查询、精准查询及聚集计算等诸多功能的表达。但对于那些用惯了SQL语言的数据分析人员来说，掌握ElasticSearch的DSL语言来做以前熟悉的事情，还是有比较陡的学习曲线的。MOQL是一款基于Java的面向内存对象过...
SQL 转换器
10-19
sqlkitd-v2.3.zip 官方网站上有一个工具是Migration Tools，目前是放在MySQL Workbench（GUI Tools）中间的，这个工具可以实现从Oracle到MySQL之间的数据库结构和数据的转换，但是对数据库端的程序的支持还不是很好。如果你只是想转换数据库结构和数据的话，还是很不错的。
另外，还有一个工具叫dbmoto，据说是可以直接转换数据库端的程序代码，你可以搜索一下这个产品，也有试用版本可以测试效果。
参与评论
您还未登录，请先
登录
后发表或查看评论
kibana查看ES
最新发布
qq_55961709的博客
10-13
398
kibana查看ES
教你快速从SQL过度到Elasticsearch的DSL查询
manong20210713的博客
08-18
198
前言
Elasticsearch太强大了，强大到跟python一样，一种查询能好几种语法。
其实我们用到的可能只是其中的一部分，比如：全文搜索。
我们一般是会将mysql的部分字段导入到es，再查询出相应的ID，再根据这些ID去数据库找出来。
问题来了：数据导入到es后，很多人都要面对这个es的json查询语法，也叫DSL，如下
于是一堆新词来了，比如：filter、match、multi_match、query、term、range，容易让没学过的人抵触。
如果正常开发业务的程序员，只关心原先
DSL的诞生 | 复杂sql转成Elasticsearch DSL深入详解
热门推荐
铭毅天下Elasticsearch
11-16
2万+
源自死磕ElasticsearchQQ群（626036393）中的一个问题：
问题如下：where (position=ES or work=ES or content=ES) and academic=本科 and （city=北京 or city=深圳）怎么构建ES的查询条件？我的问题拆解与实现如下：1、sql语句转成DSL有哪些方法？方案一：借助工具 NLP团体开发的Elasticsearc
streamingpro mysql_StreamingPro 支持类SQL DSL
weixin_42151305的博客
02-05
22
前言受spark sql在喜马拉雅的使用之xql 这篇文章影响，我发现类似下面这种语法是极好的：//加载mysql表load jdbc.`mysql1.tb_v_user` as mysql_tb_user;//处理后映射成spark临时表select * from mysql_tb_user limit 100 as result_csv;//保存到文件里save result_csv as c...
ElasticSearch SQL转DSL
hudeyong926的专栏
09-02
1627
前文
Elasticsearch在较高版本中内置SQL查询的功能，猜想本质上应该是将SQL语句转化为原生的DSL语句，再使用原生进行查询，可以让不熟悉ES的用户能通过SQL语句快速查询结果，降低使用门槛减少学习成本。另外，ES也提供Java客户端以JDBC的方式连接查询，但该方式是收费的。所以，如果用户不想购买服务建议使用官方提供的免费的restful的方式去查询，例如Java REST Client。当不熟悉ES原生的DSL语句的时候，可以先使用SQL编写查询语句，然后再使用ES自带的请求将SQL语句翻
Elasticsearch：Elasticsearch SQL介绍及实例 （一）
Elastic 中国社区官方博客
04-22
4360
Elasticsearch是一个全文搜索引擎，具有您期望的所有优点，例如相关性评分，词干，同义词等。 而且，由于它是具有水平可扩展的分布式文档存储，因此它可以处理数十亿行数据，而不会费劲。针对Elasticsearch专业人员来说，大多数人喜欢使用DSL来进行搜索，但是对于一些不是那么专业的人员来说，他们更为熟悉的是SQL语句。如何让他们对Elasticsearch的数据进行查询是一个问题。借助E............
ES中SQL查询详解
弹指天下
11-03
1万+
本文主要介绍了Elasticsearch SQL的使用。如果你对DSL查询语句不熟悉，那么采用SQL查询索引数据将是一个非常简单，0门槛入门的好方法。
1、注意ES在6.3版本之后才原生支持SQL查询。
2、可以通过translate API将sql语句转为DSL语句。
3、ES的SQL查询提供对自查询的简单支持。
4、通过SHOW FUNCTIONS可以查看ES的SQL查询支持的函数。
5、ES的SQL查询可以通过游标cursor实现分页查询。
es应用笔记2-sql查询
过了这个村没这个老王的博客
03-24
4945
es应用笔记2-sql查询
es作为一个搜索索引，在分析场景中，作为明细查询的场景会比kylin、impala、hive等更加合适。
es在6.3版本开始支持sql查询，且其sql基础语法与大数据端的语法较兼容，函数库略有不同。
对于多数据源的接入，通过jdbc接入es改造成本较低，但是xpack-sql-jdbc这个客户端的包是收费的，但是其服务端仍提供了rest api 供查询。
界面查询
kibana中添加简单数据
选择想要的一个栗子
开发者工具查询
进入开发者工具界面
查看有什么表
es-sql中的一些查詢 sql 函数
qq_31286957的博客
06-15
973
ES-SQL 中可以写sql 进行数据查询 ，但是个别的查询sql 还是有一定的区别，以下记录一些实际用到的一些函数
1、模糊查询
非分词字段 类型为keyword 的字段 使用 like， 和mysql 数据库中的like 使用一样
例如 mobile like ’%keyword%‘
分词字段 类型为text 的字段 使用 matchPhrase，对应es 中的 match_phrase
例如 name =matchPhrase(‘keyword’)
2、多值查询
吐血整理：一文看懂ES的R，查询与聚合
weixin_37337210的博客
09-24
1956
对es查询的索引的company，其有如下字段，下面是一个示例数据
"id": "1", //id
"name": "张三",//姓名
"sex": "男",//性别
"age": 49,//年龄
"birthday": "1970-01-01",//生日
"position": "董事长",//职位
"joinTime": "1990-01-01",//入职时间,日期格式
"modified": "1562167817000",//修改时间，毫秒
"created": "1562167817000"
elasticsearch sql转dsl并获取搜索结果
0箫逝的博客
09-21
4519
由于目前的项目上其他同事有es搜索应用的需求，但是由于学习成本较高，所以我构思了一个简单的工具类，针对spring boot注入的es进行elasticsearch6.3版本以后推出的sql功能， 把sql转为dsl并返回搜索结果（因为es sql的jdbc是收费的，所以用这种方法可以做到sql搜索）。
直接上代码：（泛型为实体类，用来把es响应结果转对象用的）
import org.apa...
Elasticsearch案例-SQL转DSL语句
y1006597541的博客
09-19
4623
查看更多Elasticsearch、Logstash、Kibana的问题处理和案例
前文
Elasticsearch在较高版本中内置SQL查询的功能，猜想本质上应该是将SQL语句转化为原生的DSL语句，再使用原生进行查询，可以让不熟悉ES的用户能通过SQL语句快速查询结果，降低使用门槛减少学习成本。另外，ES也提供Java客户端以JDBC的方式连接查询，但该方式是收费的。所以，如果用户不想购买...
【持续记录】使用elasticSearch支持的sql语句进行查询
gugugutime.com
04-09
1045
ElasticSearch使用SQL语句查询
本文环境：ElasticSearch7.6.2；es所在系统CentOS7.6；开发语言：Java（jdk8）；查询的是单个索引，不存在跨索引查询。
为避免歧义，规定：本文中elasticsearch支持的SQL语句简写为“esSQL”，MySQL、Oracle支持的SQL为“标准SQL”。
网址链接
官网类
esSQL支持的函数
其它
bboss，支持将kibana请求封装为一个mapper文件。
示例
SpringBoot项目
依赖
Elasticsearch DSL 常用语法介绍
阿瞒--治国之能臣 乱世之奸雄
04-27
2万+
DSL 介绍这个才是实际最常用的方式，可以构建复杂的查询条件。不用一开始就想着怎样用 Java Client 端去调用 Elasticsearch 接口。DSL 会了，Client 的也只是用法问题而已。DSL 语句的校验以及 score 计算原理对于复杂的查询，最好都先校验下，看有没有报错。GET /product_index/product/_validate/query?explain
...
ElasticSearch【五】浅析Query DSL & 映射sql语法
不吃_花椒的博客
01-25
1094
ElasticSearch官方给出的DSL解释如下：
elasticsearch provides a full Query DSL based on JSON to define queries. In general, there are basic queries such as term or prefix. There are also compound queries like the...
spring data elasticsearch 打印sql（DSL）语句
ratel的博客
03-01
7608
spring data elasticsearch 打印sql（DSL）语句
【ELK】SQL 翻译成DSL
勤杂工的博客
06-02
367
# 这是默认的
GET _search
"query": {
"match_all": {}
# 这是效果图
GET nginx-*/_search
"size" : 10,
"_source" : {
"includes" : [
"hostname"
],
"excludes" : [ ]
},
"sort" : [
"_doc" : {
"order" : "asc"
...
“相关推荐”对你有帮助么？
非常没帮助
没帮助
一般
有帮助
非常有帮助
提交
©️2022 CSDN
皮肤主题：博客之星2020
设计师：CSDN官方博客
返回首页
Hollis Chuang
CSDN认证博客专家
CSDN认证企业博客
码龄8年
暂无认证
241
原创
4097
周排名
179
总排名
354万+
访问
等级
3万+
积分
1万+
粉丝
1万+
获赞
2522
评论
2万+
收藏
私信
关注
热门文章
新来个技术总监，禁止我们使用Lombok！
157250
你H第一次做的视频，在B站播放量过万了~
118897
告别动态规划，连刷40道动规算法题，我总结了动规的套路
113075
最近程序员频繁被抓，如何避免面向监狱编程！？
98137
Java 13 来袭，最新最全新特性解读
65992
分类专栏
GitHub
Java
87篇
框架
4篇
随笔
4篇
数据库
9篇
中间件
webx
4篇
工具
4篇
diamond
TDDL
Linux
3篇
数据结构
1篇
服务器
2篇
架构
3篇
最新评论
MySQL不会丢失数据的秘密，就藏在它的 7种日志里
fenda、:
图怎么时先更新buffer，后写redo log的，redo log讲解是先写log后更新buffer
苹果曝出严重安全漏洞，黑客可全面接管设备！！！
mavengo:
关机状态能被攻击么？
chatGPT代码写的有点好啊，程序员要失业了？
蓝色的梦qw:
抖音来的
告别动态规划，连刷40道动规算法题，我总结了动规的套路
m0_69107469:
感觉和递归的概念有点类似呀
以 B 站为例，聊聊站内消息系统的设计
编程小龙:
非常有帮助
您愿意向朋友推荐“博客详情页”吗？
强烈不推荐
不推荐
一般般
推荐
强烈推荐
提交
最新文章
被迫毕业，面试 30 家公司，终于上岸了！
用了BigDecimal就不会资损？了解下BigDecimal这五个坑
实战总结！18种接口优化方案的总结
2022
12月
17篇
11月
26篇
10月
26篇
09月
16篇
08月
18篇
07月
70篇
06月
56篇
05月
76篇
04月
64篇
03月
60篇
02月
37篇
01月
61篇
2021年824篇
2020年834篇
2019年344篇
2018年27篇
2015年2篇
2014年18篇
目录
目录
分类专栏
GitHub
Java
87篇
框架
4篇
随笔
4篇
数据库
9篇
中间件
webx
4篇
工具
4篇
diamond
TDDL
Linux
3篇
数据结构
1篇
服务器
2篇
架构
3篇
目录
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额
抵扣说明：
1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。 2.余额无法直接购买下载，可以购买VIP、C币套餐、付费专栏及课程。
余额充值