es查询时间long转date_Elasticsearch7.x SQL语法查询支持

最新推荐文章于 2023-12-07 10:05:46 发布

weixin_39594457

最新推荐文章于 2023-12-07 10:05:46 发布

阅读量636

点赞数

文章标签： es查询时间long转date

本文链接：https://blog.csdn.net/weixin_39594457/article/details/112516636

版权

本文介绍了如何在Elasticsearch 7.x中使用SQL进行查询，包括条件查询、排序、限制返回字段、时间比较、聚合查询、COUNT和分组查询等，并探讨了SQL转DSL的过程，同时提供了相关查询的示例。

摘要由CSDN通过智能技术生成

??记得点击上方蓝字“程序员小强”关注哦

?发送关键字“elasticSearch”到公众号获取相关篇

1.前言

原生的DSL语法毕竟有熟悉成本, 而且还容易写错？

其实 Elasticsearch 也是支持SQL查询，基本的SQL都支持，同时还具有SQL转DSL语句的功能。

官方Api文档

https://www.elastic.co/guide/en/elasticsearch/reference/7.4/sql-concepts.html

1.1.初始测试数据

PUT /book_info/_bulk{ "index" : {  "_id" : "101" } }{ "name" : "hello","author":"tom","release_date":"2020-01-26","page_count": 500 }{ "index" : {  "_id" : "102" } }{ "name" : "world","author":"Marry","release_date":"2018-01-26","page_count": 352 }{ "index" : {  "_id" : "103" } }{ "name" : "test","author":"test","release_date":"2019-03-26","page_count": 436 }{ "index" : {  "_id" : "104" } }{ "name" : "demo","author":"demo","release_date":"2020-06-26","page_count": 337 }{ "index" : {  "_id" : "105" } }{ "name" : "zhangsan","author":"zhangsan","release_date":"2015-05-21","page_count": 562 }{ "index" : {  "_id" : "106" } }{ "name" : "zhangsan02","author":"zhangsan","release_date":"2015-05-21","page_count": 562 }

2.查询实例

语法格式：/_sql?format=txt

url上的参数

format 返回格式，不设置则默认返回JSON (支持，csv,txt,json,yaml.等详见文末附录格式说明)

请求体body参数

名称	默认值	描述
query	Mandatory	SQL查询执行示例： "query": "SELECT * FROM book_info limit 1"
fetch_size	1000	一个响应中要返回的最大行(或条目)数
filter	none	可选的Elasticsearch查询DSL，以进行其他过滤。
request_timeout	90s	请求失败之前的超时。
page_timeout	45s	分页请求失败之前的超时。
time_zone	Z (or UTC)	ISO 8601中的时区，用于在服务器上执行查询。更多信息请点击这里。
columnar	false	以列方式而不是基于行的方式返回结果。有效期为`json`，`yaml`，`cbor`和`smile`。
field_multi_value_leniency	false	当遇到一个字段的多个值(默认值)或宽大处理时抛出异常，并从列表中返回第一个值 (不保证将要返回的值-通常以自然的升序方式第一个)。
index_include_frozen	false	是否在查询执行中包括冻结索引(默认值)。

2.1条件查询

单条件

POST /_sql?format=txt{  "query": "SELECT * FROM book_info where name = 'hello'"}

多条件

POST /_sql?format=txt{  "query": "SELECT * FROM book_info where name = 'hello' and author='tom'"}

2.2排序+限制行数

POST /_sql?format=txt{  "query": "SELECT * FROM book_info ORDER BY release_date DESC LIMIT 2"}

2.3排序+限制返回字段

POST /_sql?format=txt{  "query": "SELECT name,release_date,page_count FROM book_info ORDER BY release_date DESC  LIMIT 3"}

2.4时间比较查询

POST /_sql?format=txt{  "query": "SELECT * FROM book_info WHERE release_date < '2020-01-01'  LIMIT 3"}

POST /_sql?format=txt{  "query": "SELECT * FROM book_info WHERE release_date <= '2020-01-26'  LIMIT 3"}

2.5聚合查询

POST /_sql?format=txt{  "query": "SELECT sum(page_count),name FROM book_info group by name"}

POST /_sql?format=txt{  "query": "SELECT avg(page_count) FROM book_info"}

POST /_sql?format=txt{  "query": "SELECT min(page_count) FROM book_info"}

POST /_sql?format=txt{  "query": "SELECT max(page_count) FROM book_info"}

2.6COUNT查询

POST /_sql?format=txt{  "query": "SELECT count(*),count(distinct author) FROM book_info "}

2.7分组查询

POST /_sql?format=txt{  "query": "SELECT author,count(*) as count FROM book_info group by author"}

3.SQL转DSL

3.1普通SQL

POST /_sql/translate {   "query": "SELECT name,author FROM book_info where name ='hello'",   "fetch_size": 1}

3.2右模糊

POST /_sql/translate {   "query": "SELECT name,author FROM book_info where name like 'hello%'",   "fetch_size": 10 }

3.3时间区间

POST /_sql/translate {  "query": "SELECT name,author,release_date FROM book_info WHERE release_date >'2019-01-01' and release_date < '2020-01-01'  LIMIT 3"}

附录

1.响应数据格式介绍

官方7.4版本说明文档

https://www.elastic.co/guide/en/elasticsearch/reference/7.4/sql-rest-format.html#sql-rest-format

设置方式:

通过URL参数设置format=xx
通过Accept HTTP头信息

URL参数优先于AcceptHTTP标头。如果两者均未指定，则以与请求相同的格式返回响应。

规则

格式	Accept HTTP头	描述
csv	text/csv	逗号分隔值
json	application/json	JSON(JavaScript对象表示法)人类可读格式
tsv	text/tab-separated-values	制表符分隔的值
txt	text/plain	类似于CLI的表示
yaml	application/yaml	YAML(YAML不是标记语言)人类可读的格式
cbor	application/cbor	简洁的二进制对象表示
smile	application/smile	Smile类似于CBOR的二进制数据格式