ELK学习实践(八)--批量导入CSV文件及聚合查询

以项目中的问题跟踪记录为例进行导入分析，同时测试excel文档批量转换、CSV文件批量导入、过滤器数据转换及聚合查询。

---

1.Excel文件批量转换为CSV文件

少量的Excel文件可以手动另存为CSV文件，但是几十上百份的文件就有点费工夫了，还是需要一种批量转换的方式。

打开Excel，选择“文件>选项”，打开“Excel选项”窗口，选择“自定义功能区”，勾选“开发工具”。

在Excel主界面选择“开发工具>Visual Basic”。

双击“Microsoft Excel对象”中的Sheet1，打开命令行窗口，并输入如下代码。

Sub SaveToCSVs()

    Dim fDir As String

    Dim wB As Workbook

    Dim wS As Worksheet

    Dim fPath As String

    Dim sPath As String

    fPath = "D:\原始文件路径\excel\"

    sPath = "D:\目标文件路径\csv\"

    fDir = Dir(fPath)

    Do While (fDir <> "")

        If Right(fDir, 4) = ".xls" Or Right(fDir, 5) = ".xlsx" Then

            On Error Resume Next

            Set wB = Workbooks.Open(fPath & fDir)

            'MsgBox (wB.Name)

            For Each wS In wB.Sheets

                wS.SaveAs sPath & wB.Name & ".csv", xlCSV

            Next wS

            wB.Close False

            Set wB = Nothing

        End If

        fDir = Dir

        On Error GoTo 0

    Loop

End Sub

其中fPath为excel文件所在文件夹，sPath为输出CSV文件的文件夹。保存后运行开始批量转换，过程中窗口会循环打开和关闭。
注意：如果excel中有多个sheet，每个sheet都会另存为单独的csv文件。

2.Logstash批量导入CSV

批量导入文件，配置文件中的input项也可以这样写

 input {
 stdin { }
 file{
		path => [
			"D:\目标文件路径\csv\系统问题跟踪记录1.xlsx.csv",
			"D:\目标文件路径\csv\系统问题跟踪记录2.xlsx.csv",
			"D:\目标文件路径\csv\系统问题跟踪记录3.xlsx.csv",
			"D:\目标文件路径\csv\系统问题跟踪记录4.xlsx.csv",
			"D:\目标文件路径\csv\系统问题跟踪记录5.xlsx.csv",
			"D:\目标文件路径\csv\系统问题跟踪记录6.xlsx.csv",
			"D:\目标文件路径\csv\系统问题跟踪记录7.xlsx.csv"
		]
		start_position  => "beginning"
	 }
 }

3.数据格式转换

由于CSV中默认都是字符串，需要将有特殊要求的字段进行数据转换，比如时间、整型类型数据。这其中会用到Date过滤器和Mutate过滤器。

CSV项中定义了使用“,”分割（如果单元格正文中存在这个符号，可能会导致分割结果错误，字段映射错位），columns为列名。

 filter {
	csv {
		separator => ","
		columns => ["序号","反馈人员","反馈时间","紧急程度","严重程度","所属模块","问题详情","问题判断","解决方案","问题处理状态","计划完成时间","实际完成时间","处理人","确认人","备注"]
	}
	date {
		match => ["反馈时间","MM/dd/yyyy","yyyy-MM-dd"]
		match => ["计划完成时间","MM/dd/yyyy","yyyy-MM-dd"]
		match => ["实际完成时间","MM/dd/yyyy","yyyy-MM-dd"]
	}
	mutate {
		convert => {"序号" => "intger"}
		remove_field => ["message"]
	}
 }

Mutate过滤器提供了转换字段类型（从字符串到整数等）、添加/重命名/替换/复制字段、大/小写转换、将数组连接在一起（对于Array => String操作很有用）、合并哈希、将字段拆分为数组、去除空白等操作。
Mutate过滤器无法转换时间格式的字符串，所以对于时间格式需要单独使用Date过滤器。

Date过滤器可以匹配多种类型的时间格式到特定字段。

4.数据聚合查询

创建索引模式problems用于数据查询。以查询不同来源的问题数量为例。

查询特定来源的问题，如意见类型为“用户意见”的数据条数。

GET problems/_count
{
  "query": {
    "match": {
      "反馈人员": "用户意见"
    }
  }
}

返回结果为：

{
  "count" : 137,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  }
}

如果需要查询每一种来源的数据条数，就要用到聚合查询。

GET problems/_search
{
  "aggs": {
    "0": {
      "terms": {
        "field": "反馈人员.keyword",
        "order": {
          "_count": "desc"
        },
        "size": 10
      }
    }
  },
  "size": 0,
  "fields": [
    {
      "field": "@timestamp",
      "format": "date_time"
    }
  ],
  "script_fields": {},
  "stored_fields": [
    "*"
  ],
  "runtime_mappings": {},
  "_source": {
    "excludes": []
  }
}

返回结果为：

{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 907,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "0" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 56,
      "buckets" : [
        {
          "key" : "现场反馈",
          "doc_count" : 396
        },
        {
          "key" : "用户意见",
          "doc_count" : 103
        },
        {
          "key" : "反馈人员",
          "doc_count" : 39
        },
        {
          "key" : "用户反馈",
          "doc_count" : 34
        },
        {
          "key" : "2021-3-23",
          "doc_count" : 33
        },
        {
          "key" : "中",
          "doc_count" : 32
        },
        {
          "key" : "现场评审",
          "doc_count" : 28
        }
      ]
    }
  }
}

可以使用相同的结果数据构建可视化图表，或者按照这个标准创建的可视化图表的请求和响应也都是一致的。

同样的，使用Postman可以通过同样的请求获取同样的数据。

聚合查询的好处在于可以降低原始数据结构对最终查询结果的影响，即使不同数据源的数据结构不一致，也可以通过聚合对关联数据进行查询。

同时可以通过Elasticsearch对外提供查询接口，也可通过Kibana对外提供可视化图表。

---

用途很多，值得继续深挖