Logstash：使用 Logstash 导入 CSV 文件示例
转载自：https://elasticstack.blog.csdn.net/article/details/114374804
在今天的文章中，我将展示如何使用 file input 结合 multiline 来展示如何导入一个 CSV 文件。针对 multiline，我在之前的文章 “运用 Elastic Stack 分析 Spring boot 微服务日志 (一）” 有讲到过。另外我也有两篇关于使用 Logstash 导入 CSV 的例子
Logstash：应用实践 - 装载 CSV 文档到 Elasticsearch
Logstash：导入 zipcode CSV 文件和 Geo Search 体验
针对 CSV 的导入，我们也可以使用 Filebeat 来解析 CSV 文件。如果你有兴趣的话，请参考：
Beats：运用 Elastic Stack 分析 COVID-19 数据并进行可视化分析
准备数据
在今天的练习中，我们有如下的测试数据：
multiline.csv
INV-12402400071,05/31/2018,2595,Hy-Vee Wine and Spirits / Denison,"1620 4th Ave, South",Denison,51442,"1620 4th Ave, South Denison 51442(42.012395, -95.348601)",24,CRAWFORD,1011100,Blended Whiskies,260,DIAGEO AMERICAS,25608,Seagrams 7 Crown Bl Whiskey,6,1750,11.96,17.94,1,107.64,1.75,0.46
S29195400002,11/21/2015,2205,Ding's Honk And Holler,900 E WASHINGTON,CLARINDA,51632,"900 E WASHINGTON
CLARINDA 51632
(40.739238, -95.02756)",73,Page,,,255,Wilson Daniels Ltd.,297,Templeton Rye w/Flask,6,750,18.09,27.14,12,325.68,9.00,2.38
S29198800001,11/20/2015,2191,Keokuk Spirits,1013 MAIN,KEOKUK,52632,"1013 MAIN
KEOKUK 52632
(40.39978, -91.387531)",56,Lee,,,255,Wilson Daniels Ltd.,297,Templeton Rye w/Flask,6,750,18.09,27.14,6,162.84,4.50,1.19
S29198800001,11/20/2015,2191,Keokuk Spirits,1013 MAIN,KEOKUK,52632,"1013 MAIN
KEOKUK 52632
(40.39978, -91.387531)",56,Lee,,,255,Wilson Daniels Ltd.,297,Templeton Rye w/Flask,6,750,18.09,27.14,6,162.84,4.50,1.19
这个数据来源于 https://data.iowa.gov/Sales-Distribution/Iowa-Liquor-Sales/m3tr-qhgy/data。其中的有些数据具有多行输入，也就是多出了一些换行符 "\n"，从而导致有些记录分布在多行，尽管这种情况比较少见。在上面，我们可以看到如下的三个文档：
INV-12402400071
S29195400002
S29198800001
其中 S29195400002 及 S29198800001 连个文档的内容跨三行。和第一个文档显然是不同的。那么我们该如何处理这种情况呢？首先，我们看到文档都是以 INV- 已经 S 开头的行。一般来说 Logstash 的架构图如下：
首先它含有一个 Input, 然后经过0个或多个 filter 的处理，最终输出到 Output。
针对我们的情况，我们可以使用如下的架构来对它进行处理：
我们可以使用 file input 配合 multiline，然后把数据传入到 csv, mutate, 及 Grok 这样的过滤器来进行处理。
首先，我们创建一个叫做 logstash_csv.conf 文件
logstash_csv.conf
input {
# Read the csv file. also use the multiline codec, everything that does not start with S or INV- is part of the prior line due to addresses having line breaks
file {
start_position => "beginning"
path => "/Users/liuxg/data/logstash_multiline/multline.csv"
sincedb_path => "/dev/null"
codec => multiline {
pattern => "^(S|INV-)[0-9][0-9]"
negate => "true"
what => "previous"
}
}
}
output {
stdout {
codec => rubydebug
}
}
在上面，我们使用 file 把指定位置的 multilne.csv 读入进来。我们使用了如下的 codec：
codec => multiline {
pattern => "^(S|INV-)[0-9][0-9]"
negate => "true"
what => "previous"
}
它首先匹配以 S 或 INV- 为开头的行，紧接着 S 或 INV- 后面接0-9之中的两个数字。negate 为 true 表示没有匹配的行需要添加到 previous （前面）已经匹配的行里从而组成一个文档。如果你对这个还不是很理解的话，请参阅之前在 “Beats：使用 Filebeat 传送多行日志” 中的描述。
我们使用 Logstash 运行上面的配置文件：
sudo ./bin/logstash -f logstash_csv.conf
那么输出的结果为：
我们看到文档虽然一个文档被分为三行，但是它们还是被正确地识别为一个文档。在文档中，我们看见有 \n 字符出现。在接下来的处理中，我们需要把这个字符去掉。
我们接下来使用 csv 过滤器来进行处理：
logstash_csv.conf
input {
# Read the csv file. also use the multiline codec, everything that does not start with S or INV- is part of the prior line due to addresses having line breaks
file {
start_position => "beginning"
path => "/Users/liuxg/data/logstash_multiline/multline.csv"
sincedb_path => "/dev/null"
codec => multiline {
pattern => "^(S|INV-)[0-9][0-9]"
negate => "true"
what => "previous"
}
}
}
filter {
# Parse the csv values define fields as integers and \floats
csv {
columns => ["InvoiceItemNumber","Date","StoreNumber","StoreName","Address","City","ZipCode","StoreLocation","CountyNumber","County","Category","CategoryName","VendorNumber","VendorName","ItemNumber","ItemDescription","Pack","BottleVolumeml","StateBottleCost","StateBottleRetail","BottlesSold","SaleDollars","VolumeSoldLiters","VolumeSoldGallons"]
convert => { "StoreNumber" => "integer" "ItemNumber" => "integer" "Category" => "integer" "CountyNumber" => "integer" "VendorNumber" => "integer" "Pack" => "integer" "SaleDollars" => "float" "StateBottleCost" => "float" "StateBottleRetail" => "float" "BottleVolumeml" => "float" "BottlesSold" => "float" "VolumeSoldLiters" => "float" "VolumeSoldGallons" => "float"}
remove_field => ["message"]
}
}
output {
stdout {
codec => rubydebug
}
}
在上面，我们把 CSV 文档中的项进行解析，并形成各个字段。同时我们也使用 convert 把字段里的数值字段转换为数值类型以便于分析。删除 message 字段。
重新运行 Logstash, 并查看结果：
在上面，我们看到 Country 以及 City，它们都是大写字母，我们想把它们转换为小写字母。同时在 StoreLocation 中，我们发现有 \n 字符。我们在 filter 部分添加 mutate 来对它们进行处理：
logstash_csv.conf
input {
# Read the csv file. also use the multiline codec, everything that does not start with S or INV- is part of the prior line due to addresses having line breaks
file {
start_position => "beginning"
path => "/Users/liuxg/data/logstash_multiline/multline.csv"
sincedb_path => "/dev/null"
codec => multiline {
pattern => "^(S|INV-)[0-9][0-9]"
negate => "true"
what => "previous"
}
}
}
filter {
# Parse the csv values define fields as integers and \floats
csv {
columns => ["InvoiceItemNumber","Date","StoreNumber","StoreName","Address","City","ZipCode","StoreLocation","CountyNumber","County","Category","CategoryName","VendorNumber","VendorName","ItemNumber","ItemDescription","Pack","BottleVolumeml","StateBottleCost","StateBottleRetail","BottlesSold","SaleDollars","VolumeSoldLiters","VolumeSoldGallons"]
convert => { "StoreNumber" => "integer" "ItemNumber" => "integer" "Category" => "integer" "CountyNumber" => "integer" "VendorNumber" => "integer" "Pack" => "integer" "SaleDollars" => "float" "StateBottleCost" => "float" "StateBottleRetail" => "float" "BottleVolumeml" => "float" "BottlesSold" => "float" "VolumeSoldLiters" => "float" "VolumeSoldGallons" => "float"}
remove_field => ["message"]
}
# Take the linebreaks out of the location and convert to spaces and lowercase the city and county as they change in the source file
mutate {
gsub => [ "StoreLocation", "\n", " " ]
lowercase => [ "County", "City" ]
}
}
output {
stdout {
codec => rubydebug
}
}
重新运行 Logstash 并查看输出结果：
我们看到 Country 及 City 的字母都变为小写了，同时在 StoreLocation 中再也没有 \n 字符了。
接下来，我们想提取 StoreLocation 里面的位置信息。我们可以看到里面含有一个坐标（经纬度）。我们可以使用 grok 过滤器来进行匹配：
logstash_csv.conf
input {
# Read the csv file. also use the multiline codec, everything that does not start with S or INV- is part of the prior line due to addresses having line breaks
file {
start_position => "beginning"
path => "/Users/liuxg/data/logstash_multiline/multline.csv"
sincedb_path => "/dev/null"
codec => multiline {
pattern => "^(S|INV-)[0-9][0-9]"
negate => "true"
what => "previous"
}
}
}
filter {
# Parse the csv values define fields as integers and \floats
csv {
columns => ["InvoiceItemNumber","Date","StoreNumber","StoreName","Address","City","ZipCode","StoreLocation","CountyNumber","County","Category","CategoryName","VendorNumber","VendorName","ItemNumber","ItemDescription","Pack","BottleVolumeml","StateBottleCost","StateBottleRetail","BottlesSold","SaleDollars","VolumeSoldLiters","VolumeSoldGallons"]
convert => { "StoreNumber" => "integer" "ItemNumber" => "integer" "Category" => "integer" "CountyNumber" => "integer" "VendorNumber" => "integer" "Pack" => "integer" "SaleDollars" => "float" "StateBottleCost" => "float" "StateBottleRetail" => "float" "BottleVolumeml" => "float" "BottlesSold" => "float" "VolumeSoldLiters" => "float" "VolumeSoldGallons" => "float"}
remove_field => ["message"]
}
# Take the linebreaks out of the location and convert to spaces and lowercase the city and county as they change in the source file
mutate {
gsub => [ "StoreLocation", "\n", " " ]
lowercase => [ "County", "City" ]
}
# Get the lat/lon if there is a (numbers,numbers) data in the location
grok {
match => { "StoreLocation" => "\((?<location>[-,.0-9 ]*)\)" }
}
}
output {
stdout {
codec => rubydebug
}
}
我们匹配 StoreLocation 里的含有括号 （）里的内容并赋予给 location。字符含 -,.0-9。重新运行 Logstash：
从上面我们可以看出来 location 从 StoreLocation 中被提取出来了。
接下来，我们来把文档的时间修改为来自文档中的时间。我们可以看到目前的 @timestamp 不是我们文档的 Date 字段的时间。
logstash_csv.conf
input {
# Read the csv file. also use the multiline codec, everything that does not start with S or INV- is part of the prior line due to addresses having line breaks
file {
start_position => "beginning"
path => "/Users/liuxg/data/logstash_multiline/multline.csv"
sincedb_path => "/dev/null"
codec => multiline {
pattern => "^(S|INV-)[0-9][0-9]"
negate => "true"
what => "previous"
}
}
}
filter {
# Parse the csv values define fields as integers and \floats
csv {
columns => ["InvoiceItemNumber","Date","StoreNumber","StoreName","Address","City","ZipCode","StoreLocation","CountyNumber","County","Category","CategoryName","VendorNumber","VendorName","ItemNumber","ItemDescription","Pack","BottleVolumeml","StateBottleCost","StateBottleRetail","BottlesSold","SaleDollars","VolumeSoldLiters","VolumeSoldGallons"]
convert => { "StoreNumber" => "integer" "ItemNumber" => "integer" "Category" => "integer" "CountyNumber" => "integer" "VendorNumber" => "integer" "Pack" => "integer" "SaleDollars" => "float" "StateBottleCost" => "float" "StateBottleRetail" => "float" "BottleVolumeml" => "float" "BottlesSold" => "float" "VolumeSoldLiters" => "float" "VolumeSoldGallons" => "float"}
remove_field => ["message"]
}
# Take the linebreaks out of the location and convert to spaces and lowercase the city and county as they change in the source file
mutate {
gsub => [ "StoreLocation", "\n", " " ]
lowercase => [ "County", "City" ]
}
# Get the lat/lon if there is a (numbers,numbers) data in the location
grok {
match => { "StoreLocation" => "\((?<location>[-,.0-9 ]*)\)" }
}
# Match the date to just daily and the correct timezone
date {
"match" => [ "Date", "MM/dd/YYYY" ]
"timezone" => "America/Chicago"
}
}
output {
stdout {
codec => rubydebug
}
}
再次运行 Logstash：
显然现在的 @timestamp 变为来自文档中的时间了。
我们接下来可以添加输出到 Elasticsearch:
logstash_csv.conf
input {
# Read the csv file. also use the multiline codec, everything that does not start with S or INV- is part of the prior line due to addresses having line breaks
file {
start_position => "beginning"
path => "/Users/liuxg/data/logstash_multiline/multline.csv"
sincedb_path => "/dev/null"
codec => multiline {
pattern => "^(S|INV-)[0-9][0-9]"
negate => "true"
what => "previous"
}
}
}
filter {
# Parse the csv values define fields as integers and \floats
csv {
columns => ["InvoiceItemNumber","Date","StoreNumber","StoreName","Address","City","ZipCode","StoreLocation","CountyNumber","County","Category","CategoryName","VendorNumber","VendorName","ItemNumber","ItemDescription","Pack","BottleVolumeml","StateBottleCost","StateBottleRetail","BottlesSold","SaleDollars","VolumeSoldLiters","VolumeSoldGallons"]
convert => { "StoreNumber" => "integer" "ItemNumber" => "integer" "Category" => "integer" "CountyNumber" => "integer" "VendorNumber" => "integer" "Pack" => "integer" "SaleDollars" => "float" "StateBottleCost" => "float" "StateBottleRetail" => "float" "BottleVolumeml" => "float" "BottlesSold" => "float" "VolumeSoldLiters" => "float" "VolumeSoldGallons" => "float"}
remove_field => ["message"]
}
# Take the linebreaks out of the location and convert to spaces and lowercase the city and county as they change in the source file
mutate {
gsub => [ "StoreLocation", "\n", " " ]
lowercase => [ "County", "City" ]
}
# Get the lat/lon if there is a (numbers,numbers) data in the location
grok {
match => { "StoreLocation" => "\((?<location>[-,.0-9 ]*)\)" }
}
# Match the date to just daily and the correct timezone
date {
"match" => [ "Date", "MM/dd/YYYY" ]
"timezone" => "America/Chicago"
}
}
output {
elasticsearch {
hosts => ["https://your.cluster.here:9243"]
index => ["iowa-liquor"]
user => "elastic"
password => "redacted"
manage_template => false
}
#output dots while we process
stdout { codec => "dots" }
#if we saw a date parse failure, dump it to screen to review
if "_dateparsefailure" in [tags] {
stdout { codec => "rubydebug" }
}
}
