es应用笔记2-sql查询

本文涉及的产品
Elasticsearch Serverless通用抵扣包,测试体验金 200元
简介: es应用笔记2-sql查询

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查询主流,有许多限制需要注意,这里仅将常见的列出来。

  1. 查询返回结果不能过大,会抛出异常ParsingExpection
  2. where和 order by时,scalar函数不能在嵌套字段上使用
  3. 两个不同的结构的嵌套字段不能同时使用
  4. 嵌套字段不能分页
  5. keyword 属性需要常态化
  6. arrary类型不能搜索,可以配置field.multi.value.leniency争取宽大处理
  7. 聚合的排序不支持,将其放在客户端实现,且不允许超过512行
  8. 聚合函数中必须是直接属性,而不能是scalar函数加工的属性
  9. 嵌套子查询的实力只有小学生级别,超出这个范围就不支持了:SELECT X FROM (SELECT ...) WHERE [simple_condition]
  10. 不能在having 中使用FIRST/LAST
  11. TIME类型的属性不可以在GROUP BY / HISTOGRAM中使用
  12. PIVOT中只能接收一个聚合函数
相关实践学习
以电商场景为例搭建AI语义搜索应用
本实验旨在通过阿里云Elasticsearch结合阿里云搜索开发工作台AI模型服务,构建一个高效、精准的语义搜索系统,模拟电商场景,深入理解AI搜索技术原理并掌握其实现过程。
ElasticSearch 最新快速入门教程
本课程由千锋教育提供。全文搜索的需求非常大。而开源的解决办法Elasricsearch(Elastic)就是一个非常好的工具。目前是全文搜索引擎的首选。本系列教程由浅入深讲解了在CentOS7系统下如何搭建ElasticSearch,如何使用Kibana实现各种方式的搜索并详细分析了搜索的原理,最后讲解了在Java应用中如何集成ElasticSearch并实现搜索。  
目录
相关文章
|
27天前
|
SQL 监控 关系型数据库
一键开启百倍加速!RDS DuckDB 黑科技让SQL查询速度最高提升200倍
RDS MySQL DuckDB分析实例结合事务处理与实时分析能力,显著提升SQL查询性能,最高可达200倍,兼容MySQL语法,无需额外学习成本。
|
27天前
|
SQL 存储 关系型数据库
MySQL体系结构详解:一条SQL查询的旅程
本文深入解析MySQL内部架构,从SQL查询的执行流程到性能优化技巧,涵盖连接建立、查询处理、执行阶段及存储引擎工作机制,帮助开发者理解MySQL运行原理并提升数据库性能。
|
26天前
|
SQL 监控 关系型数据库
SQL优化技巧:让MySQL查询快人一步
本文深入解析了MySQL查询优化的核心技巧,涵盖索引设计、查询重写、分页优化、批量操作、数据类型优化及性能监控等方面,帮助开发者显著提升数据库性能,解决慢查询问题,适用于高并发与大数据场景。
|
4月前
|
SQL 数据挖掘 数据库
第三篇:高级 SQL 查询与多表操作
本文深入讲解高级SQL查询技巧,涵盖多表JOIN操作、聚合函数、分组查询、子查询及视图索引等内容。适合已掌握基础SQL的学习者,通过实例解析INNER/LEFT/RIGHT/FULL JOIN用法,以及COUNT/SUM/AVG等聚合函数的应用。同时探讨复杂WHERE条件、子查询嵌套,并介绍视图简化查询与索引优化性能的方法。最后提供实践建议与学习资源,助你提升SQL技能以应对实际数据处理需求。
312 1
|
1月前
|
SQL XML Java
通过MyBatis的XML配置实现灵活的动态SQL查询
总结而言,通过MyBatis的XML配置实现灵活的动态SQL查询,可以让开发者以声明式的方式构建SQL语句,既保证了SQL操作的灵活性,又简化了代码的复杂度。这种方式可以显著提高数据库操作的效率和代码的可维护性。
150 18
|
1月前
|
SQL 人工智能 数据库
【三桥君】如何正确使用SQL查询语句:避免常见错误?
三桥君解析了SQL查询中的常见错误和正确用法。AI产品专家三桥君通过三个典型案例:1)属性重复比较错误,应使用IN而非AND;2)WHERE子句中非法使用聚合函数的错误,应改用HAVING;3)正确的分组查询示例。三桥君还介绍了学生、课程和选课三个关系模式,并分析了SQL查询中的属性比较、聚合函数使用和分组查询等关键概念。最后通过实战练习帮助读者巩固知识,强调掌握这些技巧对提升数据库查询效率的重要性。
90 0
|
2月前
|
SQL
SQL中如何删除指定查询出来的数据
SQL中如何删除指定查询出来的数据
|
4月前
|
SQL 关系型数据库 MySQL
凌晨2点报警群炸了:一条sql 执行200秒!搞定之后,我总结了一个慢SQL查询、定位分析解决的完整套路
凌晨2点报警群炸了:一条sql 执行200秒!搞定之后,我总结了一个慢SQL查询、定位分析解决的完整套路
凌晨2点报警群炸了:一条sql 执行200秒!搞定之后,我总结了一个慢SQL查询、定位分析解决的完整套路
|
3月前
|
SQL 存储 弹性计算
OSS Select 加速查询:10GB CSV 文件秒级过滤的 SQL 语法优化技巧
OSS Select 可直接在对象存储上执行 SQL 过滤,跳过文件下载,仅返回所需数据,性能比传统 ECS 方案提升 10~100 倍。通过减少返回列、使用等值查询、避免复杂函数、分区剪枝及压缩优化等技巧,可大幅降低扫描与传输量,显著提升查询效率并降低成本。
|
6月前
|
SQL 关系型数据库 MySQL
如何优化SQL查询以提高数据库性能?
这篇文章以生动的比喻介绍了优化SQL查询的重要性及方法。它首先将未优化的SQL查询比作在自助餐厅贪多嚼不烂的行为,强调了只获取必要数据的必要性。接着,文章详细讲解了四种优化策略:**精简选择**(避免使用`SELECT *`)、**专业筛选**(利用`WHERE`缩小范围)、**高效联接**(索引和限制数据量)以及**使用索引**(加速搜索)。此外,还探讨了如何避免N+1查询问题、使用分页限制结果、理解执行计划以及定期维护数据库健康。通过这些技巧,可以显著提升数据库性能,让查询更高效流畅。