Elasticsearch SQL
2024-08-24 14:00:09
es sql是一个X-pack组件 ,允许对es执行类似sql的查询,可以将Elasticsearch SQL理解为一个编译器,既能理解es,又能理解sql。可以通过利用es,实施大规模实时读取和处理数据。
sql和es的映射关系
SQL | Elasticsearch |
columns | field |
raw | document |
table | index |
catalog or database | cluster实例 |
cluster | cluster |
先插入一些数据:
PUT /my_index/doc/_bulk
{"index":{"_id":""}}
{"name":"lily","birthday":"2000-01-01","gender":"female"}
{"index":{"_id":""}}
{"name":"kangkang","birthday":"1998-04-01","gender":"male"}
{"index":{"_id":""}}
{"name":"jane","birthday":"1995-02-07","gender":"female"}
SQL REST API
POST /_xpack/sql?format=txt
{
"query":"select * from my_index where birthday<'1999-01-01' limit 2"
}
# format类型有:json,yaml,smile,cbor,txt,csv,tsv
返回结果:
birthday | gender | name
------------------------+---------------+---------------
1998-04-01T00:00:00.000Z|male |kangkang
1995-02-07T00:00:00.000Z|female |jane
POST /_xpack/sql?
{
"query":"select * from my_index order by birthday desc",
"fetch_size":1 # fetch_size 每页返回多少个结果
}
--------->
{
"columns": [
{
"name": "birthday",
"type": "date"
},
{
"name": "gender",
"type": "text"
},
{
"name": "name",
"type": "text"
}
],
"rows": [
[
"2000-01-01T00:00:00.000Z",
"female",
"lily"
]
],
"cursor": "k4bwAgFz5AFEbkYxWlhKNVZHaGxia1psZEdOb0JRQUFBQUFBQUY3WEZrbGtNa3R5V2s1VVZFTnRORmd3Y21Gd2VHeERMVkVBQUFBQUFBQmUyeFpKWkRKTGNscE9WRlJEYlRSWU1ISmhjSGhzUXkxUkFBQUFBQUFBWHRnV1NXUXlTM0phVGxSVVEyMDBXREJ5WVhCNGJFTXRVUUFBQUFBQUFGN1pGa2xrTWt0eVdrNVVWRU50TkZnd2NtRndlR3hETFZFQUFBQUFBQUJlMmhaSlpESkxjbHBPVkZSRGJUUllNSEpoY0hoc1F5MVL/////DwMBZghiaXJ0aGRheQEAAWYGZ2VuZGVyAAABZgRuYW1lAAA="
}
# 该column对象只是第一页的一部分,当cursor结果中没有返回时,说明到达最后一页。
# 可以通过发回cursor字段继续下一页。在文本格式的情况下,光标作为Cursorhttp标头返回。
POST /_xpack/sql?format=json
{
"cursor": "k4bwAgFz5AFEbkYxWlhKNVZHaGxia1psZEdOb0JRQUFBQUFBQUY3WEZrbGtNa3R5V2s1VVZFTnRORmd3Y21Gd2VHeERMVkVBQUFBQUFBQmUyeFpKWkRKTGNscE9WRlJEYlRSWU1ISmhjSGhzUXkxUkFBQUFBQUFBWHRnV1NXUXlTM0phVGxSVVEyMDBXREJ5WVhCNGJFTXRVUUFBQUFBQUFGN1pGa2xrTWt0eVdrNVVWRU50TkZnd2NtRndlR3hETFZFQUFBQUFBQUJlMmhaSlpESkxjbHBPVkZSRGJUUllNSEpoY0hoc1F5MVL/////DwMBZghiaXJ0aGRheQEAAWYGZ2VuZGVyAAABZgRuYW1lAAA="
}
#结果--------->
{
"rows": [
[
"1998-04-01T00:00:00.000Z",
"male",
"kangkang"
]
],
"cursor": "k4bwAgFz5AFEbkYxWlhKNVZHaGxia1psZEdOb0JRQUFBQUFBQUY3WEZrbGtNa3R5V2s1VVZFTnRORmd3Y21Gd2VHeERMVkVBQUFBQUFBQmUyeFpKWkRKTGNscE9WRlJEYlRSWU1ISmhjSGhzUXkxUkFBQUFBQUFBWHRnV1NXUXlTM0phVGxSVVEyMDBXREJ5WVhCNGJFTXRVUUFBQUFBQUFGN1pGa2xrTWt0eVdrNVVWRU50TkZnd2NtRndlR3hETFZFQUFBQUFBQUJlMmhaSlpESkxjbHBPVkZSRGJUUllNSEpoY0hoc1F5MVL/////DwMBZghiaXJ0aGRheQEAAWYGZ2VuZGVyAAABZgRuYW1lAAA="
}
## -------------再次发回cursor: POST /_xpack/sql?format=json
{
"cursor": "k4bwAgFz5AFEbkYxWlhKNVZHaGxia1psZEdOb0JRQUFBQUFBQUY3WEZrbGtNa3R5V2s1VVZFTnRORmd3Y21Gd2VHeERMVkVBQUFBQUFBQmUyeFpKWkRKTGNscE9WRlJEYlRSWU1ISmhjSGhzUXkxUkFBQUFBQUFBWHRnV1NXUXlTM0phVGxSVVEyMDBXREJ5WVhCNGJFTXRVUUFBQUFBQUFGN1pGa2xrTWt0eVdrNVVWRU50TkZnd2NtRndlR3hETFZFQUFBQUFBQUJlMmhaSlpESkxjbHBPVkZSRGJUUllNSEpoY0hoc1F5MVL/////DwMBZghiaXJ0aGRheQEAAWYGZ2VuZGVyAAABZgRuYW1lAAA="
}
#结果----------------》
{
"rows": []
}
#接收到最后一页时,清空es状态,没有cursor #要提前清理状态,可以使用 clear cursor
POST _xpack/sql/close
{
"cursor": "k4bwAgFz5AFEbkYxWlhKNVZHaGxia1psZEdOb0JRQUFBQUFBQUY3NkZrbGtNa3R5V2s1VVZFTnRORmd3Y21Gd2VHeERMVkVBQUFBQUFBQmVfaFpKWkRKTGNscE9WRlJEYlRSWU1ISmhjSGhzUXkxUkFBQUFBQUFBWHZzV1NXUXlTM0phVGxSVVEyMDBXREJ5WVhCNGJFTXRVUUFBQUFBQUFGNzhGa2xrTWt0eVdrNVVWRU50TkZnd2NtRndlR3hETFZFQUFBQUFBQUJlX1JaSlpESkxjbHBPVkZSRGJUUllNSEpoY0hoc1F5MVL/////DwMBZghiaXJ0aGRheQEAAWYGZ2VuZGVyAAABZgRuYW1lAAA="
}
#结果——----------------->
{ "succeeded": true }
通过filter参数可以指定es的Query DSL来过滤
POST _xpack/sql?format=txt
{
"query":"select * from my_index order by birthday desc",
"filter":{
"term": {
"name": "kangkang"
}
},
"fetch_size":1
}
# 除了query和cursor字段外 请求还可以包括fetch_size和time_zone
# fetch_size 每页返回多少个结果
# time_zone 日期函数和日期解析的时区,默认为utc
SQL Translate API
sql translate api接受json文档中的sql并将其转换为es查询。
POST _xpack/sql/translate
{
"query":"select * from my_index order by birthday",
"fetch_size":3
}
#结果----------------->
{
"size": 3,
"_source": {
"includes": [
"gender",
"name"
],
"excludes": []
},
"docvalue_fields": [
"birthday"
],
"sort": [
{
"birthday": {
"order": "asc"
}
}
]
}
SQL CLI
可以用命令行形式,在x-pack的bin目录执行查询语句:
# ./elasticsearch-sql-cli
sql> select * from my_index where birthday<'1999-01-01';
birthday | gender | name
------------------------+---------------+---------------
1998-04-01T00:00:00.000Z|male |kangkang
1995-02-07T00:00:00.000Z|female |jane
SQL JDBC
将jdbc调用转化为es sql
SQL 语句
- describe table
# DESC table
# DESCRIBE table POST _xpack/sql?format=txt
{
"query":"describe my_index"
}
---------->
column | type
---------------+---------------
birthday |TIMESTAMP
gender |VARCHAR
gender.keyword |VARCHAR
name |VARCHAR
name.keyword |VARCHAR
- select
# SELECT select_expr [, ...]
[ FROM table_name ]
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition]
[ ORDER BY expression [ ASC | DESC ] [, ...] ]
[ LIMIT [ count ] ] - show columns
#SHOW COLUMNS [ FROM | IN ] ? table POST _xpack/sql?format=txt
{
"query":"show columns in my_index"
}column | type
---------------+---------------
birthday |TIMESTAMP
gender |VARCHAR
gender.keyword |VARCHAR
name |VARCHAR
name.keyword |VARCHAR - show functions
#SHOW FUNCTIONS [ LIKE? pattern? ]? POST _xpack/sql?format=txt
{
"query":"show functions like 'sum%'"
} name | type
---------------+---------------
SUM |AGGREGATE
SUM_OF_SQUARES |AGGREGATE - show tables
# SHOW TABLES [ LIKE? pattern? ]? POST _xpack/sql?format=txt
{
"query":"show tables like 'my_index'"
}
#------------------------>
name | type
---------------+---------------
my_index |BASE TABLE
functions and operators
- 比较运算符: = , < , <= , > , >=, 不等于 <> != <=> , between,is null/is not null
- 逻辑运算符: AND ,OR ,NOT
- 数字运算符: + - * / %
POST _xpack/sql?format=txt
{
"query":"select 1+1 as x"
}
---------->
x
---------------
2 - 数学函数: abs(绝对值), crbt(立方根),round(四舍五入)....
POST _xpack/sql?format=txt
{
"query":"select abs(age) from test_index "
}
--------->
ABS(age)
---------------
27 - 时间和日期函数: year, month, week, doy, dow, hour ,minute_of_day, minute,second,extract
POST _xpack/sql?format=txt
{
"query":"select year(cast('2018-07-12' as timestamp )) as year"
} #从日期中提取年份
------->
year
---------------
2018 - 聚合: avg , count , count(distinct) , max , min , sum
POST _xpack/sql/?format=txt
{
"query":"select avg(age) as avg from test_index"
} POST _xpack/sql?format=txt
{
"query":"select count(distinct age) as count from test_index"
} #不同值的个数
最新文章
- HttpWebResponse远程服务器返回错误: (500) 内部服务器错误。
- Asp.net MVC 中Ajax的使用 [分享]
- PRML读书会第十章 Approximate Inference(近似推断,变分推断,KL散度,平均场, Mean Field )
- PHP5 session 详解【经典】 -- 转帖
- uva 11178 - Morley&#39;s Theorem
- mysql-5.5.46源码编译安装
- 二、python 函数
- QF——UI之几种常用的隐藏键盘的方法
- 使用CocoaPods出现 The `master` repo requires CocoaPods 0.32.1 - 问题解决
- ZeroMQ中PUB-SUB模式测试
- Python手动实现k-means
- java-16习题
- Codeforces 700 C. Break Up(Tarjan求桥)
- 用户管理_组管理_权限管理.ziw
- Codeforces Round #494 (Div 3) (A~E)
- python nose测试
- 【PyQt】插入排序算法
- SAP标准培训课程C4C10学习笔记(四)第四单元
- java面试基础问题
- 【js】--常用DOM库工具