运用SQL简化DSL查询SQL是ES在6.5.x之后推出的一种查询脚本，相比原有DSL,大大简化了查询语法，也降低了普通开发者的门槛。在此之前，采用NLPchina提供的ES-SQL插件实现SQL查询。ES的DSL表达能力非常强，SQL仅仅是简化了部分常用的，很多高级复杂的无法表达出来，我们必须知晓。SQL 查询SQL概念映射ES与数据库SQL概念映射SQLElasticsearch数据列 clumn字段 field数据行 row数据文档 document数据表 table索引 index表结构 schema映射 mapping数据库 databaseES集群 clusterSQL-API 接口SQL 执行初体验SQL_sql 执行sql查询APIformat 返回结果的格式化，支持多种，默认jsonquery 执行SQL脚本输入案例说明，基于航班样例数据，查询航班明细数据，限制数据返回数据1条# 原始查询1
GET kibana_sample_data_flights_3shard/_search
{
“size”: 1,
“track_total_hits”: true
}

SQL查询1

POST _sql?format=json
{
“query”: “””
SELECT FROM “kibana_sample_data_flights_3shard” limit 1
“””
}format 响应内容格式支持多种响应格式，默认Json相比原有dsl-json大大减少资源json、csv、tsv、txt、yaml、cbor、simile案例练习，修改format参数，对比下前后的数据，比对不同的应用场景POST _sql?format=csv
{
“query”: “””
SELECT FROM “kibana_sample_data_flights_3shard” limit 1
“””
}SQL 分页SQL执行查询下，分页采用的是快照方式，基于游标fetch_size 分页大小，默认1000案例说明，从行业信息中查询，限制单页大小2POST _sql?format=json
{
“query”: “””
SELECT * FROM “kibana_sample_data_flights_3shard”
“””,
“fetch_size”:2
}

*bc

POST _sql
{
“cursor”:”游标字符串”
}结果行转列在部分场景下，我们需要提取数据按照列示的方式，可以减少转换ES默认情况下，数据全部是按照行式返回的目前限定format格式必须是json/yaml才可以columnar 取值范围true/false案例说明，查询航班信息中目的地国家，结果按照列式返回，限制2条，格式为jsonPOST _sql?format=json
{
“query”: “””
SELECT DestCountry FROM “kibana_sample_data_flights_3shard”
“””,
“fetch_size”:2,
“columnar”:true
}查询过滤条件参数化ES提供的SQL查询近可能的靠近SQL的表达能力，但与标准的SQL还是有点差距params 提供参数化案例练习，查询航班信息，限制目的地国家为“GB”的，第1种参数直接写在SQL中；第2种参数通过占位符提供，必须按照数组的顺序POST _sql?format=json
{
“query”: “””
SELECT * FROM “kibana_sample_data_flights_3shard” where DestCountry.keyword=’GB’
“””,
“fetch_size”:1
}

带参数

POST _sql?format=json
{
“query”: “””
SELECT FROM “kibana_sample_data_flights_3shard” where DestCountry.keyword=?
“””,
“params”:[
“GB”
],
“fetch_size”:1
}SQL 与 DSL 混合限制查询SQL执行查询时，也可以基于DSL编写限制过滤条件filter 限制条件查询关键字表达式案例练习，查询目的地国家为“GB”的航班信息；第1种标准的dsl查询，2.第二种sql与filter混合。GET kibana_sample_data_flights_3shard/_search
{
“size”: 1,
“track_total_hits”: true,
“query”: {
“bool”: {
“filter”:[
{
“term”: {
“DestCountry.keyword”:{
“value”:”GB”
}
}
}
]
}
}
}
POST _sql?format=json
{
“query”: “””
SELECT FROM “kibana_sample_data_flights_3shard”
“””,
“filter”: {
“term”: {
“DestCountry.keyword”: {
“value”: “GB”
}
}
},
“fetch_size”: 1
}SQL 转换SQL 转 DSL_sql/translate 讲SQL表达式转换为DSL查询语法注意转换后的数据字段结构POST _sql/translate
{
“query”:”select * from kibana_sample_data_flights_3shard”
}常用SQLSQL 语法SQL查询语法几乎与常用的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][,…]))]SQL 查询精确条件查询精确查询，泛指无需分词的字段，非text类型案例说明，查询出发地国家为“CN”，目的地国家为”US“的航班信息，注意这里的分页信息# DSL 查询
GET kibana_sample_data_flights_3shard/_search
{
“size”: 5,
“track_total_hits”: true,
“query”: {
“bool”: {
“must”: [
{
“term”: {
“OriginCountry.keyword”: {
“value”: “CN”
}
}
},
{
“term”: {
“DestCountry.keyword”: {
“value”: “US”
}
}
}
]
}
}
}

SQL 查询

POST _sql?format=json
{
“query”: “””
SELECT FROM kibana_sample_data_flights_3shard where OriginCountry.keyword=’CN’ and DestCountry.keyword=’US’
“””,
“fetch_size”:5
}分词查询针对text类型查询，ES用到类似的函数功能，后面会讲述案例说明，查询目的地包括关键字包括”Tulsa”的所有航班信息# SQL 查询
POST _sql?format=json
{
“query”: “””
SELECT FROM kibana_sample_data_flights_3shard where OriginCountry.keyword=’CN’ and DestCountry.keyword=’US’
“””,
“fetch_size”:5
}

DSL 分词查询

GET kibana_sample_data_flights_3shard/_search
{
“size”: 5,
“track_total_hits”: true,
“query”: {
“bool”: {
“must”: [
{
“match”: {
“Dest”: “Tulsa”
}
}
]
}
}
}

SQL 分词查询

POST _sql?format=json
{
“query”: “””
SELECT FROM kibana_sample_data_flights_3shard where match(Dest,’Tulsa’)
“””,
“fetch_size”:5混合查询精确查询，分词查询联合一起案例说明，查询目的地机场名字包括”Tulsa“，并且飞行时间分钟控制在200~300的所有航班信息GET kibana_sample_data_flights_3shard/_search
{
“size”: 5,
“track_total_hits”: true,
“query”: {
“bool”: {
“must”: [
{
“match”: {
“Dest”: “Tulsa”
}
},
{
“range”:{
“FlightTimeMin”: {
“gte”: 200,
“lte”: 300
}
}
}
]
}
}
}
POST _sql?format=json
{
“query”: “””
SELECT FROM kibana_sample_data_flights_3shard where match(Dest,’Tulsa’) and FlightTimeMin>=200 and FlightTimeMin<=300
“””,
“fetch_size”:5
}SQL 聚合ESSQL提供了很多常用的指标聚合函数，等同于原有DSL的聚合API案例说明，统计所有航班的指标信息；第1种写法DSL;第2种写法SQL,几乎比较完美的SQL标准# DSL 指标混合
GET kibana_sample_data_flights_3shard/_search
{
“size”: 0,
“track_total_hits”: true,
“aggs”: {
“stats_FlightTimeMin”: {
“stats”: {
“field”: “FlightTimeMin”
}
}
}
}

SQL 指标聚合

POST _sql?format=json
{
“query”: “””
SELECT count(0) as count,min(FlightTimeMin) as min,max(FlightTimeMin) as max,avg(FlightTimeMin) as avg,sum(FlightTimeMin) as sum FROM “kibana_sample_data_flights_3shard”
“””,
“fetch_size”: 5
}分桶聚合ESSQL基于Group关键字实现数据分组，等同于DSL中buckets分桶案例说明，按照出发地国家分组，统计每个国家航班的飞行时间指标信息，并按照目的地国家名称排序；第1种，采用DSL 第2种采用SQL# DSL 分桶
GET kibana_sample_data_flights_3shard/_search
{
“size”: 0,
“track_total_hits”: true,
“aggs”: {
“terms_OriginCountry”: {
“terms”: {
“field”: “OriginCountry.keyword”,
“size”: 100,
“order”: {
“_key”: “asc”
}
},
“aggs”: {
“stats_FlightTimeMin”: {
“stats”: {
“field”: “FlightTimeMin”
}
}
}
}
}
}

SQL 分组

POST _sql?format=json
{
“query”: “””
SELECT OriginCountry.keyword as OriginCountry,count(0) as count,min(FlightTimeMin) as min,max(FlightTimeMin) as max,avg(FlightTimeMin) as avg,sum(FlightTimeMin) as sum FROM “kibana_sample_data_flights_3shard” group by OriginCountry.keyword order by OriginCountry.keyword asc
“””,
“fetch_size”:5
}管道聚合ESSQL,无法表达出管道聚合DSL可同时有并行的聚合，也可以有嵌套的聚合，ESSQL无法表达高级SQLSQL 命令show tables展示所有素引信息案例说明，注意2种不同的写法，代表不同的运行环境；前一种基于客户端shell;后一种基于rest写法POST _sql?format=json
{
“query”: “””
show tables
“””,
“fetch_size”:100
}show functions显示内置所有函数案例说明，注意2种不同的写法，代表不同的运行环境；前一种基于客户端shell;后一种基于rest写法；POST _sql?format=json
{
“query”: “””
show functions
“””,
“fetch_size”:100
}show columns显示某个索引的所有字段列信息案例说明，注意2种不同的写法，代表不同的运行环境；前一种基于客户端shell;后一种基于rest写法；POST _sql?format=json
{
“query”: “””
show columns in kibana_sample_data_flights_3shard
“””,
“fetch_size”:100
}DESCRIBE显示某个所有的描述信息POST _sql?format=json
{
“query”: “””
DESCRIBE kibana_sample_data_flights_3shard
“””
}SQL 函数全文文本函数ESSQL全文检索与标准SQL有差异，采用定制函数的方式实现MATCH,查询函数关键字案例说明，查询出发地国家包括“GB”的航班信息POST _sql?format=json
{
“query”: “””
SELECT FROM kibana_sample_data_flights_3shard WHERE match(OriginCountry,’GB’)
“””,
“fetch_size”:2
}聚合函数常用聚合函数AVG、SUM、MIN、MAXCOUNTFIRST、LAST、FIRST_VALUE、LAST_VALUE案例说明，提取航班信息中，第一个出发地国家，最后一个出发地国家；POST _sql?format=json
{
“query”: “””
SELECT first(OriginCountry),last(OriginCountry) FROM kibana_sample_data_flights_3shard
“””,
“fetch_size”:100
}统计类聚合函数PERCENTILE、PERCENTILE_RANK其它聚合统计函数，查阅官方网站案例说明，基于百分位占比，统计航班信息中飞行时间长百分位占比；第1个，统计第95百分位的飞行时间平均时间；第2个，统计飞行时间为700的百分位占比POST _sql?format=json
{
“query”: “””
SELECT PERCENTILE(FlightTimeMin,95),PERCENTILE_RANK(FlightTimeMin,700) FROM kibana_sample_data_flights_3shard
“””,
“fetch_size”:100
}分桶类聚合函数HISTOGRAM,直方图统计函数，必须带上group关键字=案例说明，按照飞行时间统计所有航班信息，按照100间隔；此处对比结果，并没有DSL表达能力好POST _sql?format=json
{
“query”: “””
SELECT HISTOGRAM(FlightTimeMin,100) as f,count(0) as count FROM kibana_sample_data_flights_3shard GROUP BY f
“””,
“fetch_size”:100
}数值函数数学函数ESSQL支持很多数学函数ABS案例说明，绝对值查询，去掉负数；对比DSL没有此功能，需要通过脚本的机制转换POST _sql?format=json
{
“query”: “””
SELECT abs(-100)
“””,
“fetch_size”:100
}日期函数ESSQL与标准SQL一致，用法也一致系统内置时间，CURRENT_DATE、CURRENT_TIME、CURRENT_TIMESTAMP系统时间计算函数，DATEADD案例说明，输出各种系统时间；当前时间加1年POST _sql?format=json
{
“query”: “””
select CURRENT_DATE(),CURRENT_DATE,CURRENT_TIME(),CURRENT_TIME,CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP,DATEADD(‘year’,1,CURRENT_DATE)
“””,
“fetch_size”:100
}条件函数ESSQL与标准SQL几乎一致，用法也一致CASE WHEN,逻辑条件更多其他条件函数，参考官方POST _sql?format=json
{
“query”: “””
SELECT CASE 5
WHEN 1 THEN ‘aaaaa’
WHEN 2 THEN ‘bbbbb’
WHEN 5 THEN ‘ccccc’
END AS “case”
“””
}字符函数ESSQL与标准SQL几乎一致，用法也一致CONCAT,连接字符串函数更多其他条件函数，参考官方POST _sql?format=json
{
“query”: “””
SELECT CONCAT(‘hello’,’ world’)
“””
}SQL 操作符条件比对ESSQL与标准SQL几乎一致，用法也一致比对操作符：=,→，!=,>,<,>=,<=BETWEEN AND , IN逻辑条件ESSQL与标准SQL几乎一致,用法也一致逻辑条件:AND,OR,NOT数学操作符ESSQL与标准SQL几乎一致,用法也一致数值之间加减乘除Like操作符ESSQL与标准SQL几乎一致,用法也一致模糊匹配SQL限制性ESSQL并非完整的支持标准SQL,有很多限制性语法ES本身数据类型特征非常丰富,SQL无法完整能适应表达DSL数组类型ESSQL不支持数组查询,也不支持对象型查询案例说明,下面案例执行会出错,索引中有数组字段GET kibana_sample_data_ecommerce/_search?size=1
POST _sql?format=json
{
“query”: “””
SELECT FROM kibana_sample_data_ecommerce
“””,
“fetch_size”:2
}聚合二次反转ES支持的分组查询仅限与查询型的，在一次查询中完成，不支持类似数据的嵌套多次Group反转。案例说明，优先基于出发地国家与城市分组统计，然后按照出发地国家再次聚合。POST _sql?format=json
{
“query”: “””
SELECT OriginCountry.keyword as OriginCountry,OriginCityName.keyword as OriginCityName,count(0) as num FROM kibana_sample_data_flights_3shard GROUP BY OriginCountry.keyword,OriginCityName.keyword
“””
}执行出错,无法执行嵌套的 GroupPOST _sql?format=json
{
“query”: “””
SELECT OriginCountry,sum(num)
FROM (SELECT OriginCountry.keyword as OriginCountry,OriginCityName.keyword as OriginCityName,count(0) as num FROM kibana_sample_data_flights_3shard GROUP BY OriginCountry.keyword,OriginCityName.keyword) as a
GROUP BY OriginCountry
“””
}更多限制请参考官方,或者工作中用到时查看即可,ES也在不断的发展更新,后续可能会支持客户端工具elasticsearch-sql-cliES官方提供的客户端命令工具,简单好用指向ES服务器地址,注意端口为http./『ES_HOME』/bin/elasticsearch-sql-cli http://127.0.0.1:9201sql-jdbcmaven下载代码Maven仓储位置, Maven程序版本

elastic.co
https://artifacts.elastic.co/maven

org.elasticsearch.plugin
x-pack-sql-jdbcs
7.8.1
dbeaver下载 beaver安装 es-jdbc驱动,开启 X-pack试用功能连接使用