以项目中的问题跟踪记录为例进行导入分析,同时测试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对外提供可视化图表。
用途很多,值得继续深挖