es应用笔记2-sql查询
es作为一个搜索索引，在分析场景中，作为明细查询的场景会比kylin、impala、hive等更加合适。
es在6.3版本开始支持sql查询，且其sql基础语法与大数据端的语法较兼容，函数库略有不同。
对于多数据源的接入，通过jdbc接入es改造成本较低，但是xpack-sql-jdbc这个客户端的包是收费的，但是其服务端仍提供了rest api 供查询。
界面查询
kibana中添加简单数据
选择想要的一个栗子
开发者工具查询
- 进入开发者工具界面
- 查看有什么表
使用 SHOW TABLES查询
- 查看表有什么列
使用 DESCRIBE [TABLENAME]
- SQL查询记录
查询一下延误的航班
REST API
REST API 才是其他程序可以通过SQL查询ES的关键。
kibana rest api
通过浏览器F12可以获取到查询kibana的api接口，不过我们并不关心它的API：
curl 'http://localhost:5601/api/console/proxy?path=%2F_sql%3Fformat%3Dtxt&method=POST' \ -H 'Connection: keep-alive' \ -H 'sec-ch-ua: "Chromium";v="98", " Not A;Brand";v="99"' \ -H 'Accept: text/plain, */*; q=0.01' \ -H 'Content-Type: application/json' \ -H 'sec-ch-ua-mobile: ?0' \ -H 'User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/98.0.4758.136 Safari/537.36' \ -H 'kbn-version: 7.6.2' \ -H 'sec-ch-ua-platform: "Windows"' \ -H 'Origin: http://localhost:5601' \ -H 'Sec-Fetch-Site: same-origin' \ -H 'Sec-Fetch-Mode: cors' \ -H 'Sec-Fetch-Dest: empty' \ -H 'Referer: http://localhost:5601/app/kibana' \ -H 'Accept-Language: zh-CN,zh;q=0.9,zh-Hans;q=0.8,en;q=0.7' \ --data-raw $'{\r\n "query": "select t.Dest from kibana_sample_data_flights t limit 20"\r\n}\n' \ --compressed
es rest api
其实kibana的开发者工具已经告诉我们ES的查询API为POST /_sql?format=txt，那么稍作改造直接发给ES：
curl 'http://localhost:9200/_sql?format=txt' \ -H 'Connection: keep-alive' \ -H 'Accept: text/plain, */*; q=0.01' \ -H 'Content-Type: application/json' \ -d $'{\r\n "query": "select t.Dest from kibana_sample_data_flights t limit 20"\r\n}\n' \ --compressed
其结果如下：
sh-4.2# curl 'http://localhost:9200/_sql?format=txt' \ > -H 'Connection: keep-alive' \ > -H 'Accept: text/plain, */*; q=0.01' \ > -H 'Content-Type: application/json' \ > -d $'{\r\n "query": "select t.Dest from kibana_sample_data_flights t limit 1"\r\n}\n' \ > --compressed Dest -------------------------------------------- Sydney Kingsford Smith International Airport
对于应用程序，我们选择接收JSON，那么format=json即可，结果如下：
sh-4.2# curl 'http://localhost:9200/_sql?format=json' \ > -H 'Connection: keep-alive' \ > -H 'Accept: text/plain, */*; q=0.01' \ > -H 'Content-Type: application/json' \ > -d $'{\r\n "query": "select t.Dest from kibana_sample_data_flights t limit 1"\r\n}\n' \ > --compressed {"columns":[{"name":"Dest","type":"keyword"}],"rows":[["Sydney Kingsford Smith International Airport"]]}sh-4.2#
主要参数介绍
format
格式化返回结果，摘抄自官网：
| format | Accept HTTP header |
Description |
| Human Readable | ||
csv |
text/csv |
Comma-separated values |
json |
application/json |
JSON (JavaScript Object Notation) human-readable format |
tsv |
text/tab-separated-values |
Tab-separated values |
txt |
text/plain |
CLI-like representation |
yaml |
application/yaml |
YAML (YAML Ain’t Markup Language) human-readable format |
| Binary Formats | ||
cbor |
application/cbor |
Concise Binary Object Representation |
smile |
application/smile |
Smile binary data format similar to CBOR |
分页
如果在查询时，使用了DSL的fetch_size如：
POST /_sql?format=json { "query": "SELECT * FROM library ORDER BY page_count DESC", "fetch_size": 5 }
其返回中就会有游标：
{ "columns": [ ], "rows": [ ], "cursor": "sDXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAAAEWWWdrRlVfSS1TbDYtcW9lc1FJNmlYdw==:BAFmBmF1dGhvcgFmBG5hbWUBZgpwYWdlX2NvdW50AWYMcmVsZWFzZV9kYXRl+v///w8=" }
可以通过发送游标进行下一页查询，同时，游标还必须手动进行关闭。
POST /_sql/close { "cursor": "sDXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAAAEWYUpOYklQMHhRUEtld3RsNnFtYU1hQQ==:BAFmBGRhdGUBZgVsaWtlcwFzB21lc3NhZ2UBZgR1c2Vy9f///w8=" }
columnar
是否返回列信息
默认为true，查询返回列信息。
POST /_sql?format=json { "query": "SELECT * FROM library ORDER BY page_count DESC", "fetch_size": 5, "columnar": true }
结果：
{ "columns": [ {"name": "author", "type": "text"}, {"name": "name", "type": "text"}, {"name": "page_count", "type": "short"}, {"name": "release_date", "type": "datetime"} ], "values": [ ], "cursor": "sDXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAAAEWWWdrRlVfSS1TbDYtcW9lc1FJNmlYdw==:BAFmBmF1dGhvcgFmBG5hbWUBZgpwYWdlX2NvdW50AWYMcmVsZWFzZV9kYXRl+v///w8=" }
官方推荐在分页查询第一次查询时返回列信息，后续查询不再返回列信息的方式。
其他rest参数
官网链接：https://www.elastic.co/guide/en/elasticsearch/reference/7.6/sql-rest-fields.html
fetch_size、filter、request_timeout、page_timeout也是会用到的参数。
SQL转DSL
可以通过/_sql/translate进行转换
POST /_sql/translate { "query": "SELECT * FROM library ORDER BY page_count DESC", "fetch_size": 10 }
SQL语法、命令
https://www.elastic.co/guide/en/elasticsearch/reference/7.6/sql-spec.html
函数
https://www.elastic.co/guide/en/elasticsearch/reference/7.6/sql-functions.html
限制
https://www.elastic.co/guide/en/elasticsearch/reference/7.6/sql-limitations.html
SQL查询并非ES查询主流，有许多限制需要注意，这里仅将常见的列出来。
- 查询返回结果不能过大，会抛出异常ParsingExpection
- where和 order by时，scalar函数不能在嵌套字段上使用
- 两个不同的结构的嵌套字段不能同时使用
- 嵌套字段不能分页
- keyword 属性需要常态化
- arrary类型不能搜索，可以配置field.multi.value.leniency争取宽大处理
- 聚合的排序不支持，将其放在客户端实现，且不允许超过512行
- 聚合函数中必须是直接属性，而不能是scalar函数加工的属性
- 嵌套子查询的实力只有小学生级别，超出这个范围就不支持了：SELECT X FROM (SELECT ...) WHERE [simple_condition]
- 不能在having 中使用FIRST/LAST
- TIME类型的属性不可以在GROUP BY / HISTOGRAM中使用
- PIVOT中只能接收一个聚合函数
![NL2SQL进阶系列(3)：Data-Copilot、Chat2DB、Vanna Text2SQL优化框架开源应用实践详解[Text2SQL]](https://ucc.alicdn.com/fnj5anauszhew_20240416_bc5637dd773e47daa8f6c80ac51f69fc.jpeg?x-oss-process=image/resize,h_160,m_lfit)
