Elasticsearch SQL特性研究
Elasticsearch SQL特性研究
简介
从Elasticsearch 发布以来，一直有自己的查询语言（DSL），从6.3版本开始，Elasticsearch 开始支持SQL查询语言。即6.3、6.4、6.5、6.6、6.7、7.0均支持SQL查询。但是6.7之前SQL功能是实验性质的，6.6进入beta特性列表，6.7后成为官方正式支持的特性。其中一些SQL相关的API变化较大，各版本间不兼容。SQL查询是Elasticsearch XPack商用插件包中的功能，商用是需要收费的，目前jdbc Driver 只在铂金级的产品上才能用。
概念对应
| SQL | Elasticsearch | 对比说明 |
|---|---|---|
| column | field | ES尽可能的把字段和SQL中的列做对应，并不是完全对应的上，比如ES中字段可能是一个list |
| row | document | document 结构更灵活和松散，row更严格 |
| table | index | 查询语句执行的对象，理论上table 和index 更加具有可比性，而不是type |
| schema | 无对应关系 | SQL中schema是一个table、index 等对象的命名空间，能起到一定的访问控制的作用，ES中没有合适的对应，或者对应上具有安全特性的ES的role概念 |
| database或catalog | cluster 实例 | SQL中database和catalog大多数情况指的是一个概念，代表schema的集合。ES中cluster是index的集合 |
| cluster | cluster、集群联盟 | 典型的SQL场景中，cluster指的是包含多个database或catalog的单个实例，ES的cluster是真正的运行在多个机器上的分布式节点集群 |
SQL查询的方式
- 使用PST _xpack/sql?format=txt 或者_sql?format=txt(7.0后)API
- format 支持 txt ，以表格方式返回数据
- format 支持josn ，以json格式返回数据
- format支持其他格式，如csv、yaml、cbor、smile
- 使用es命令行工具
/bin/elasticsearch-sql-cli - 使用jdbc Driver 或者odbc Driver (铂金级产品才能用)
SQL查询的本质
_sql API 本质上是一个翻译者的角色，把SQL翻译成了DSL语句给ES去执行，可以通过 /_xpack/sql/translate 查看翻译后的DSL
POST /_xpack/sql/translate
{
"query": "SELECT * FROM library ORDER BY page_count DESC",
"fetch_size": 10
}
返回
{
"size" : 10,
"docvalue_fields" : [
{
"field": "page_count",
"format": "use_field_mapping"
},
{
"field": "release_date",
"format": "epoch_millis"
}
],
"_source": {
"includes": [
"author",
"name"
],
"excludes": []
},
"sort" : [
{
"page_count" : {
"order" : "desc",
"missing" : "_first",
"unmapped_type" : "short"
}
}
]
}
支持的SQL命令
- SELECT
只支持SELECT 查询语句，且不是所有的SELECT 语法结构都支持，不支持INSERT、UPDATE、DELETE、CREATE等语句
curl -X POST -H 'Content-Type: application/json' -i http://localhost:9200/test2/_doc/2 --data '{
"name":"wangzhen2",
"info":{"sex":"man","age":28}
}'
查询全部
curl -X POST -H 'Content-Type: application/json' -i 'http://localhost:9200/_xpack/sql?format=txt' --data '{
"query":"select * from test2"
}'
返回数据
info.age | info.sex | name
---------------+---------------+---------------
28 |man |wangzhen2
28 |man |wangzhen
- DESCRIBE/DESC/SHOW COLUMNS
描述一个索引的定义，类似GET /{index}/{type}/_mappings ，查看该索引有多少字段，字段的类型。
curl -X POST -H 'Content-Type: application/json' -i 'http://localhost:9200/_xpack/sql?format=json' --data
'{
"query": "desc library"
}'
返回
{
"columns": [{
"name": "column",
"type": "keyword"
}, {
"name": "type",
"type": "keyword"
}, {
"name": "mapping",
"type": "keyword"
}],
"rows": [
["author", "VARCHAR", "text"],
["author.keyword", "VARCHAR", "keyword"],
["name", "VARCHAR", "text"],
["name.keyword", "VARCHAR", "keyword"],
["page_count", "BIGINT", "long"],
["release_date", "TIMESTAMP", "datetime"]
]
}
- SHOW FUNCTIONS
展示支持的SQL函数
curl -X POST -H 'Content-Type: application/json' -i 'http://localhost:9200/_xpack/sql?format=json' --data '{
"query": "show functions"
}'
返回
{
"columns": [{
"name": "name",
"type": "keyword"
}, {
"name": "type",
"type": "keyword"
}],
"rows": [
["AVG", "AGGREGATE"],
["COUNT", "AGGREGATE"],
["FIRST", "AGGREGATE"],
...
]
}
- SHOW TABLES
展示集群中的index 信息
curl -X POST -H 'Content-Type: application/json' -i 'http://localhost:9200/_xpack/sql?format=json' --data
'{
"query": "show tables"
}'
返回
{
"columns": [{
"name": "name",
"type": "keyword"
}, {
"name": "type",
"type": "keyword"
}],
"rows": [
["library", "BASE TABLE"],
["test", "BASE TABLE"],
["twitter", "BASE TABLE"]
]
}
SQL限制
-
nested 类型字段限制
- DESC/SYSC_COLUMNS 不显示字段内的定义
- 在查询中不能直接使用该字段，只能使用该字段的子字段
- 同一个查询中不能引用多个嵌套(nested)字段
- 包含内嵌字段的分页是不准确的，因为分页是作用在根文档上的，不是内嵌文档
-
数组字段限制
- 如果一个字段是数组类型，且包含多个值，用REST接口查询时会抛出异常（field.multi.value.leniency 参数默认是false）
- 如果一个字段是数组类型，且包含多个值，用JDBC Driver查询时会返回第一个值，但是值的顺序不能保证（field.multi.value.leniency 参数默认是true）
-
聚合限制
- 聚合排序（group by a order by b）只能对前512个结果排序，超过这个大小的结果会抛出异常，推荐用limit 限制结果结合大小
- 聚合函数如 sum、max、mix 只能直接使用在字段上，不能嵌套使用，如
SELECT MAX(abs(age)) FROM test
-
子查询限制
- 只能支持简单的子查询（比如，这个子查询实际上可以扁平化改写成一个不包含子查询的SELECT 语句）
- 不支持复杂的子查询
- 不支持子查询里带有 group by 或者having
-
不支持在Having 子语句中使用First 或者 Last
