ELK系列(十二)、使用SQL查询ElasticSearch7.7.0

╰+哭是因爲堅強的太久メ 2023-02-13 04:00 32阅读 0赞

ES的DSL可能对很多人来说有学习成本，但SQL相信每个开发无论是应用开发，大数据开发还是数据分析人员应该都是最熟悉的，本篇就介绍一下如何使用es7的sql功能对索引数据进行查询。

[ELK系列(一)、安装ElasticSearch+Logstash+Kibana+Filebeat-v7.7.0][ELK_ElasticSearch_Logstash_Kibana_Filebeat-v7.7.0]

[ELK系列(二)、在Kibana中使用RESTful操作ES库][ELK_Kibana_RESTful_ES]

[ELK系列(三)、安装Logstash插件及打包离线安装包][ELK_Logstash]

[ELK系列(四)、Logstash读取nginx日志写入ES中][ELK_Logstash_nginx_ES]

[ELK系列(五)、Logstash修改@timestamp时间为日志的产生时间][ELK_Logstash_timestamp]

[ELK系列(六)、修改Nginx日志为Json格式并使用Logstash导入至ES][ELK_Nginx_Json_Logstash_ES]

[ELK系列(七)、Filebeat+Logstash采集多个日志文件并写入不同的ES索引中][ELK_Filebeat_Logstash_ES]

[ELK系列(八)、使用Filebeat+Redis+Logstash收集日志数据][ELK_Filebeat_Redis_Logstash]

[ELK系列(九)、配置ES和Kibana的用户密码][ELK_ES_Kibana]

[ELK系列(十)、ES中文分词器IK插件安装和配置远程词库热加载][ELK_ES_IK]

[ELK系列(十一)、ElasticSearch7.7.0插件es-head安装及使用][ELK_ElasticSearch7.7.0_es-head]

\-------------------------------在ES7中使用SQL查询数据------------------------------

# 查询数据 #

下面的命令会将SQL在后台转为DSL命令查询索引：

#二维表返回
    POST /_sql?format=txt
    {
        "query": "SELECT host,ip,machine.os,bytes FROM kibana_sample_data_logs  where machine.os='win 8' and host like 'cdn.%.org' order by 4 desc limit 50"
    }
    
    #formart返回类型支持很多，可参考官方文档：
    有csv,txt,tsv,json,yml等等
    https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-rest-format.html

![20200527145301968.png][]

# 翻译成DSL #

下面的命令可以将SQL转为DSL：

POST /_sql/translate
    {
        "query": "SELECT host,ip,machine.os,bytes FROM kibana_sample_data_logs  where machine.os='win 8' and host like 'cdn.%.org' order by 4 desc limit 50"
    }

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70][]

# 使用DSL查询 #

上面转换成的DSL可以直接使用对索引进行查询，效果与第一个里面使用SQL查询是一样的结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70 1][]

# 命令行模式 #

使用下面的命令进入es-sql的命令行模式：

$ ./bin/elasticsearch-sql-cli
    $ ./bin/elasticsearch-sql-cli https://es_host:9200
    $ ./bin/elasticsearch-sql-cli https://es_user:es_pwd@es_host:9200

效果与在kibana中的一样：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70 2][]

# JDBC模式 #

下载jar包：[https://www.elastic.co/cn/downloads/jdbc-client][https_www.elastic.co_cn_downloads_jdbc-client]

或者使用maven:

<repositories>
      <repository>
        <id>elastic.co</id>
        <url>https://artifacts.elastic.co/maven</url>
      </repository>
    </repositories>
    
    <dependency>
      <groupId>org.elasticsearch.plugin</groupId>
      <artifactId>x-pack-sql-jdbc</artifactId>
      <version>7.7.0</version>
    </dependency>

在DBeaver中配置ES7的连接，并测试，会报错，原因是因为白金版才支持JDBC的功能：

Query execution failed
    
    原因：
     SQL 错误: current license is non-compliant for [jdbc]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70 3][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70 4][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70 5][]

希望本文对你有帮助，请点个赞鼓励一下作者吧~ 谢谢！

[ELK_ElasticSearch_Logstash_Kibana_Filebeat-v7.7.0]: https://blog.csdn.net/wsdc0521/article/details/106236499
[ELK_Kibana_RESTful_ES]: https://blog.csdn.net/wsdc0521/article/details/106241596
[ELK_Logstash]: https://blog.csdn.net/wsdc0521/article/details/106292639
[ELK_Logstash_nginx_ES]: https://blog.csdn.net/wsdc0521/article/details/106306584
[ELK_Logstash_timestamp]: https://blog.csdn.net/wsdc0521/article/details/106307452
[ELK_Nginx_Json_Logstash_ES]: https://blog.csdn.net/wsdc0521/article/details/106307938
[ELK_Filebeat_Logstash_ES]: https://blog.csdn.net/wsdc0521/article/details/106308441
[ELK_Filebeat_Redis_Logstash]: https://blog.csdn.net/wsdc0521/article/details/106344073
[ELK_ES_Kibana]: https://blog.csdn.net/wsdc0521/article/details/106344974
[ELK_ES_IK]: https://blog.csdn.net/wsdc0521/article/details/106368378
[ELK_ElasticSearch7.7.0_es-head]: https://blog.csdn.net/wsdc0521/article/details/106377407
[20200527145301968.png]: https://img-blog.csdnimg.cn/20200527145301968.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200527145346257.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200527145507558.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200527150441303.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx,size_16,color_FFFFFF,t_70
[https_www.elastic.co_cn_downloads_jdbc-client]: https://www.elastic.co/cn/downloads/jdbc-client
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200527152857176.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200527152920106.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200527152802610.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx,size_16,color_FFFFFF,t_70