针对项目中的问题跟踪记录进行导入分析，同时测试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:\Projects\202001-HNRF\Doc\05_项目开发文档\问题跟踪记录-excel\"
sPath = "D:\Projects\202001-HNRF\Doc\05_项目开发文档\问题跟踪记录-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文件。
批量导入文件，配置文件中的input项也可以这样写
input {
stdin { }
file{
path => [
"D:/Projects/202001-HNRF/Doc/05_项目开发文档/问题跟踪记录-csv/系统问题跟踪记录-2020遗留汇总.xlsx.csv",
"D:/Projects/202001-HNRF/Doc/05_项目开发文档/问题跟踪记录-csv/系统问题跟踪记录-2021年4月前.xlsx.csv",
"D:/Projects/202001-HNRF/Doc/05_项目开发文档/问题跟踪记录-csv/系统问题跟踪记录-20200827.xlsx.csv",
"D:/Projects/202001-HNRF/Doc/05_项目开发文档/问题跟踪记录-csv/系统问题跟踪记录-20200902.xlsx.csv",
"D:/Projects/202001-HNRF/Doc/05_项目开发文档/问题跟踪记录-csv/系统问题跟踪记录-20200911.xlsx.csv",
"D:/Projects/202001-HNRF/Doc/05_项目开发文档/问题跟踪记录-csv/系统问题跟踪记录-20201012.xlsx.csv",
"D:/Projects/202001-HNRF/Doc/05_项目开发文档/问题跟踪记录-csv/系统问题跟踪记录-20201015.xlsx.csv"
]
start_position => "beginning"
}
}
由于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过滤器可以匹配多种类型的时间格式到特定字段。
创建索引模式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" : 29
},
{
"key" : "现场评审",
"doc_count" : 28
},
{
"key" : "2021-3-24",
"doc_count" : 20
},
{
"key" : "2021-3-25",
"doc_count" : 16
}
]
}
}
}
可以使用相同的结果数据构建可视化图表，或者按照这个标准创建的可视化图表的请求和响应也都是一致的。
同样的，使用Postman可以通过同样的请求获取同样的数据。
聚合查询的好处在于可以降低原始数据结构对最终查询结果的影响，即使不同数据源的数据结构不一致，也可以通过聚合对关联数据进行查询。
同时可以通过Elasticsearch对外提供查询接口，也可通过Kibana对外提供可视化图表。
PNG