Elasticsearch7.3使用SQL查询

知识点

读完本文将学会以下技能

• 使用sql进行文档查询
• 将sql翻译成QueryDsl

前置条件

• 已安装好Elasticsearch&kibana
  本文基于Elasticsearch7.3&kibana7.3进行演示,虚拟机IP192.168.1.14,kibana地址http://192.168.1.14:5601
  如果没有相关环境可以参照Centos7安装Elasticsearch&Kibana

数据准备

演示基于kibana内置的样例数据进行,可以通过以下步骤进行样例数据添加
访问kibana地址http://192.168.1.14:5601,然后依次点击主页图标->加载数据集和 Kibana 仪表板,如下图红线和箭头所示

点击添加数据将样例Web日志添加到kibana,如下图所示

数据添加完成后,依次点击查看数据->仪表板,如下图所示

如果出现下图所示的仪表板,且仪表板中有数据,则说明数据导入成功.

SQL查询

样例数据查看

我们上一步导入的数据存在名字为kibana_sample_data_logs的索引中

# 查看索引中数据条数
GET _cat/indices/*logs?v&h=index,docs.*
# 返回信息,表明索引中与14074个文档
index                   docs.count docs.deleted
kibana_sample_data_logs      14074            0

# 索引mapping查看
GET kibana_sample_data_logs/_mapping
# 返回信息
{
  "kibana_sample_data_logs" : {
    "mappings" : {
      "properties" : {
        "@timestamp" : {
          "type" : "alias",
          "path" : "timestamp"
        }
        ...省略
        "memory" : {
          "type" : "double"
        },
        "utc_time" : {
          "type" : "date"
        }
      }
    }
  }
}

根据mapping返回信息,挑选出如下字段进行查询测试

字段名	类型	描述
timestamp	date	时间戳
clientip	ip	客户端IP
machine.os	text	操作系统版本
request	text	请求url
response	text	返回状态码

简单查询

# 查询日志中全部字段
GET _sql?format=txt
{
  "query":"select * from kibana_sample_data_logs"
}
# 返回信息, 报错内容表明sql不支持Array类型的字段
{
  "error": {
    "root_cause": [
      {
        "type": "sql_illegal_argument_exception",
        "reason": "Arrays (returned by [tags]) are not supported"
      }
    ],
    "type": "sql_illegal_argument_exception",
    "reason": "Arrays (returned by [tags]) are not supported"
  },
  "status": 500
}

# 查询日志中部分字段
GET _sql?format=txt
{
  "query":"select timestamp,clientip,machine.os,request,response from kibana_sample_data_logs"
}
# 返回内容中前10条,(默认最多返回1000条)
       timestamp        |   clientip    |  machine.os   |                          request                          |   response    
------------------------+---------------+---------------+-----------------------------------------------------------+---------------
2019-09-29T00:39:02.912Z|223.87.60.27   |win 8          |/elasticsearch/elasticsearch-6.3.2.deb                     |200            
2019-09-29T03:26:21.326Z|130.246.123.197|win 8          |/beats/metricbeat                                          |200            
2019-09-29T03:30:25.131Z|120.49.143.213 |ios            |/styles/main.css                                           |503            
2019-09-29T03:34:43.399Z|99.74.118.237  |ios            |/beats/metricbeat/metricbeat-6.3.2-amd64.deb               |200            
2019-09-29T03:37:04.863Z|177.111.217.54 |win 7          |/enterprise                                                |200            
2019-09-29T03:49:40.669Z|106.225.58.146 |win 7          |/apm                                                       |503            
2019-09-29T03:57:39.612Z|6.138.148.165  |win 8          |/beats/metricbeat/metricbeat-6.3.2-amd64.deb               |200            
2019-09-29T04:18:12.345Z|218.148.135.12 |win 8          |/beats/filebeat/filebeat-6.3.2-linux-x86_64.tar.gz         |200      

统计Unique Visitors

统计今天的Unique Visitors,并与仪表板中的数值进行核对
点击仪表板->网络流量

点击时间选择->今日,如下图所示

今天数据如下图所示,Unique Visitors=208

使用sql进行统计验证

# 统计当日Unique Visitors,也就是计算今天请求中一共多少个不一样客户端IP
GET _sql?format=txt
{
  "query":"""select count(distinct clientip) from  kibana_sample_data_logs 
    where timestamp>= '2019-10-11T00:00:00+08:00' 
    and timestamp < '2019-10-12T00:00:00+08:00'"""
}

# 返回数据,与仪表盘中数据一致
count(distinct clientip)
------------------------
208.0                   

Sql翻译成queryDsl

 # 简单查询翻译
 GET _sql/translate
{
  "query":"select timestamp,clientip,machine.os,request,response from kibana_sample_data_logs"
}
# 返回内容
{
  "size" : 1000,
  "_source" : {
    "includes" : [
      "machine.os",
      "request",
      "response"
    ],
    "excludes" : [ ]
  },
  "docvalue_fields" : [
    {
      "field" : "timestamp",
      "format" : "epoch_millis"
    },
    {
      "field" : "clientip"
    }
  ],
  "sort" : [
    {
      "_doc" : {
        "order" : "asc"
      }
    }
  ]
}

# 执行翻译出来的queryDsl
GET kibana_sample_data_logs/_search
{
  "size" : 1000,
  "_source" : {
    "includes" : [
      "machine.os",
      "request",
      "response"
    ],
    "excludes" : [ ]
  },
  "docvalue_fields" : [
    {
      "field" : "timestamp",
      "format" : "epoch_millis"
    },
    {
      "field" : "clientip"
    }
  ],
  "sort" : [
    {
      "_doc" : {
        "order" : "asc"
      }
    }
  ]
}
# 返回结果

{
  "took" : 31,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 10000,
      "relation" : "gte"
    },
    "max_score" : null,
    "hits" : [
      {
        "_index" : "kibana_sample_data_logs",
        "_type" : "_doc",
        "_id" : "dSgCu20BA5mecxzOBkZa",
        "_score" : null,
        "_source" : {
          "request" : "/elasticsearch/elasticsearch-6.3.2.deb",
          "machine" : {
            "os" : "win 8"
          },
          "response" : 200
        },
        "fields" : {
          "timestamp" : [
            "1569717542912"
          ],
          "clientip" : [
            "223.87.60.27"
          ]
        },
        "sort" : [
          0
        ]
      },
      ... 省略
  }