《Elastic Stack 实战手册》——三、产品能力——3.5 进阶篇——3.5.17.Elasticsearch SQL (3) https://developer.aliyun.com/article/1227075
4. SQL 与 DSL
4.1 SQL 转换 为DSL
Elasticsearch SQL 提供了 translate 接口,可以将 SQL 语法转换为对应的 DSL 语法。
通过查看 DSL 语法,可以更好的学习 Elasticsearch DSL;也可以通过转化结果,反过来判断SQL 查询的正确性或者优化 SQL 查询。
POST _sql/translate { "query":""" SELECT clientip, url, response FROM test_logs WHERE MATCH(url, 'metricbeat') ORDER BY timestamp DESC LIMIT 10 """ } { "size" : 10, "query" : { "match" : { "url" : { "query" : "metricbeat", "operator" : "OR", "prefix_length" : 0, "max_expansions" : 50, "fuzzy_transpositions" : true, "lenient" : false, "zero_terms_query" : "NONE", "auto_generate_synonyms_phrase_query" : true, "boost" : 1.0 } } }, "_source" : false, "fields" : [ { "field" : "clientip" }, { "field" : "url" }, { "field" : "response" } ], "sort" : [ { "timestamp" : { "order" : "desc", "missing" : "_first", "unmapped_type" : "date" } } ] }
4.2 SQL 混合 DSL 语句
如果遇到过滤条件逻辑关系非常复杂,我们可以在 SQL 中 添加 Elasticsearch DSL 过滤条件。
POST /_sql?format=txt { "query": """ SELECT clientip, host, response FROM test_logs WHERE MATCH(url, 'metricbeat') ORDER BY timestamp DESC LIMIT 3 """, "filter": { "range": { "response": { "gte": 200, "lt": 300 } } } } clientip | host | response ---------------+--------------------+--------------- 74.184.0.64 |artifacts.elastic.co|200 232.20.97.5 |artifacts.elastic.co|200 148.192.209.125|www.elastic.co |200
注意此时 filter 中的过滤条件,与 query 中的 WHERE 是逻辑 AND 关系。
POST /_sql/translate { "query": """ SELECT clientip, host, response FROM test_logs WHERE MATCH(url, 'metricbeat') ORDER BY timestamp DESC LIMIT 10 """, "filter": { "range": { "response": { "gte": 200, "lt": 300 } } } } { "size" : 10, "query" : { "bool" : { "must" : [ { "match" : { "url" : { "query" : "metricbeat", "operator" : "OR", "prefix_length" : 0, "max_expansions" : 50, "fuzzy_transpositions" : true, "lenient" : false, "zero_terms_query" : "NONE", "auto_generate_synonyms_phrase_query" : true, "boost" : 1.0 } } } ], "filter" : [ { "range" : { "response" : { "from" : 200, "to" : 300, "include_lower" : true, "include_upper" : false, "boost" : 1.0 } } } ], "adjust_pure_negative" : true, "boost" : 1.0 } }, "_source" : false, "fields" : [ { "field" : "clientip" }, { "field" : "host" }, { "field" : "response" } ], "sort" : [ { "timestamp" : { "order" : "desc", "missing" : "_first", "unmapped_type" : "date" } } ] }
4.3 SQL 与 DSL 的区别
最直观的感觉,Elasticsearch SQL 查询语法更加简洁,适用于一般场景,而 ElasticsearchElasticsearch DSL 相比较 Elasticsearch SQL ,有下面的优点:
1)Elasticsearch DSL 查询功能更加强大:
1、通过上面的 translate 接口,我们知道,Elasticsearch SQL 会被解析为 Elasticsearch
DSL 然后再去执行。所以理论上,Elasticsearch DSL 可以实现所有的 Elasticsearch SQL 查询语句;
2、Elasticsearch DSL 对复杂字段类型的支持更加完善,例如:array,object,nested,join 类型的各种查询;
3、Elasticsearch DSL 中通过添加 script ,实现各种灵活的计算功能。
2)Elasticsearch DSL 查询接口,可以携带更多的参数,实现更多的查询优化,例如:
DSL 更加强大,可以实现更加复杂的查询条件。
# 参数 track_total_hits 可以返回查询的 hit 文档数量 # 参数 timeout 可以自定义查询的超时时间 GET test_logs/_search { "timeout": "60s", "track_total_hits": true, "query": { "match_all": {} } }
3)Elasticsearch DSL 可以满足更多的查询需求,例如:高亮查询结果、自定义相关性评分等等;
4)Elasticsearch DSL 查询结果携带更多的信息,例如:查询耗时、分片信息、每个文档的
_id 等等。
Elasticsearch SQL 相比较 Elasticsearch DSL ,有下面的优点:
1)查询语句更加简洁,返回结果更加结构化,更容易处理;
2)更容易上手,满足数据分析人员的大部分日常查询;
3)通过进一步抽象,可以更好的兼容其他引擎。例如:很多公司会实现统一的 SQL 查询语言,通过判断查询的数据,连接到不同的数据库引擎( MySQL/ Elasticsearch/ Apache Druid/
Apache Druid 等),显然,自己实现的语言更容易解析为 Elasticsearch SQL。
《Elastic Stack 实战手册》——三、产品能力——3.5 进阶篇——3.5.17.Elasticsearch SQL (5) https://developer.aliyun.com/article/1227073