Logstash 是一個資料流引擎:
它是用於資料物流的開源流式 ETL(Extract-Transform-Load)引擎
在幾分鐘內建立資料流管道
具有水平可擴充套件及韌性且具有自適應緩衝
不可知的資料來源
具有200多個整合和處理器的外掛生態系統
使用 Elastic Stack 監視和管理部署
Logstash 包含3個主要部分: 輸入(inputs),過濾器(filters)和輸出(outputs)。 你必須定義這些過程的配置才能使用 Logstash,儘管不是每一個都必須的。在有些情況下,我們可以甚至沒有過濾器。在過濾器的部分,它可以對資料來源的資料進行分析,豐富,處理等等。
docker pull logstash:7.12.1
----------------------------------
vim logstash.conf
input {
stdin { }
jdbc {
#注意mysql連線地址一定要用ip,不能使用localhost等
jdbc_connection_string => "jdbc:mysql://172.18.12.1:3306/lmrs_2008_shops"
jdbc_user => "root"
jdbc_password => "root"
#資料庫重連嘗試
connection_retry_attempts => "3"
#資料庫連線可用校驗超時時間,預設為3600s
jdbc_validation_timeout => "3600"
#這個jar包的地址是容器內的地址
jdbc_driver_library => "/etc/logstash/pipeline/mysql-connector-java-8.0.24.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
#開啟分頁查詢(預設是false)
jdbc_paging_enabled => "true"
#單次分頁查詢條數(預設100000,欄位較多的話,可以適當調整這個數值)
jdbc_page_size => "50000"
#執行的sql語句
statement => "select a.id,a.`name`,a.long_name,a.brand_id,a.three_category_id as category_id,a.shop_id,a.price,a.sold_count,a.review_count,a.`status`,a.create_time,a.last_time,b.`name` as category,b.path from lmrs_products as a LEFT JOIN lmrs_product_categorys as b on a.three_category_id = b.id where a.id > :sql_last_value"
#需要記錄查詢結果某欄位的值時,此欄位為true,否則預設tracking_colum為timestamp的值
use_column_value => true
#是否將欄位名轉為小寫,預設為true(如果具備序列化或者反序列化,建議設定為false)
lowercase_column_names => false
#需要記錄的欄位,同於增量同步,需要是資料庫欄位
tracking_column => id
#記錄欄位的資料型別
tracking_column_type => numeric
#上次資料存放位置
record_last_run => true
#上一個sql_last_value的存放路徑,必須在檔案中指定欄位的初始值
last_run_metadata_path => "/etc/logstash/pipeline/products.txt"
#是否清除last_run_metadata_path的記錄,需要增量同步這個欄位的值必須為false
clean_run => false
#同步的頻率(分 時 天 月 年)預設為每分鐘同步一次
schedule => "* * * * *"
type => "_doc"
}
jdbc {
#注意mysql連線地址一定要用ip,不能使用localhost等
jdbc_connection_string => "jdbc:mysql://172.18.12.1:3306/lmrs_2008_shops"
jdbc_user => "root"
jdbc_password => "root"
#資料庫重連嘗試
connection_retry_attempts => "3"
#資料庫連線可用校驗超時時間,預設為3600s
jdbc_validation_timeout => "3600"
#這個jar包的地址是容器內的地址
jdbc_driver_library => "/etc/logstash/pipeline/mysql-connector-java-8.0.24.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
#開啟分頁查詢(預設是false)
jdbc_paging_enabled => "true"
#單次分頁查詢條數(預設100000,欄位較多的話,可以適當調整這個數值)
jdbc_page_size => "50000"
#執行的sql語句
statement => "select c.*,d.`name`as category,d.path as category_path from (select b.id,a.`name`,b.`name` as `value`,a.sort as attribute_sort,b.sort as attribute_value_sort,a.category_id from lmrs_attributes as a LEFT JOIN lmrs_attribute_values as b on a.id = b.attribute_id) as c LEFT JOIN lmrs_product_categorys as d on c.category_id = d.id where c.id
> :sql_last_value"
#需要記錄查詢結果某欄位的值時,此欄位為true,否則預設tracking_colum為timestamp的值
use_column_value => true
#是否將欄位名轉為小寫,預設為true(如果具備序列化或者反序列化,建議設定為false)
lowercase_column_names => false
#需要記錄的欄位,同於增量同步,需要是資料庫欄位
tracking_column => id
#記錄欄位的資料型別
tracking_column_type => numeric
#上次資料存放位置
record_last_run => true
#上一個sql_last_value的存放路徑,必須在檔案中指定欄位的初始值
last_run_metadata_path => "/etc/logstash/pipeline/attributes.txt"
#是否清除last_run_metadata_path的記錄,需要增量同步這個欄位的值必須為false
clean_run => false
#同步的頻率(分 時 天 月 年)預設為每分鐘同步一次
schedule => "* * * * *"
type => "attributes"
}
}
filter {
jdbc_streaming {
jdbc_driver_library => "/etc/logstash/pipeline/mysql-connector-java-8.0.24.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_connection_string => "jdbc:mysql://172.18.12.1:3306/lmrs_2008_shops"
jdbc_user => "root"
jdbc_password => "root"
parameters => { "sensor_identifier" => "id"}
statement => "select `name`,price from lmrs_product_skus where product_id= :sensor_identifier"
target => "skus"
}
jdbc_streaming {
jdbc_driver_library => "/etc/logstash/pipeline/mysql-connector-java-8.0.24.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_connection_string => "jdbc:mysql://172.18.12.1:3306/lmrs_2008_shops"
jdbc_user => "root"
jdbc_password => "root"
parameters => { "sensor_identifiers" => "id"}
statement => "select c.`name`,f.`name` as `value` from (select b.`name`,b.id FROM lmrs_product_attribute_values as a LEFT JOIN lmrs_attributes as b on a.attribute_id= b.id where a.product_id = :sensor_identifiers) as c LEFT JOIN(select d.attribute_id,d.`name` from lmrs_attribute_values as d LEFT JOIN lmrs_product_attribute_values as e on d.id = e.attribute_value_id where product_id = :sensor_identifiers) as f on c.id = f.attribute_id GROUP BY f.`name`"
target => "attributes"
}
}
output {
if [type] == "_doc" {
elasticsearch {
#注意mysql連線地址一定要用ip,不能使用localhost等
hosts => "172.18.12.6:9200"
index => "products"
document_type => "_doc"
document_id => "%{id}"
}
}
if [type] == "attributes" {
elasticsearch {
#注意mysql連線地址一定要用ip,不能使用localhost等
hosts => "172.18.12.6:9200"
index => "attributes"
document_type => "_doc"
document_id => "%{id}"
}
}
stdout {
codec => json_lines
}
}
PUT /products/
{
"mappings": {
"properties": {
"name":{
"type": "text",
"analyzer": "ik_smart"
},
"long_name":{
"type": "text",
"analyzer": "ik_smart"
},
"brand_id":{
"type": "integer"
},
"category_id":{
"type":"integer"
},
"category":{
"type": "keyword"
},
"category_path":{
"type": "keyword"
},
"shop_id":{
"type":"integer"
},
"price":{
"type":"scaled_float",
"scaling_factor":100
},
"sold_count":{
"type":"integer"
},
"review_count":{
"type":"integer"
},
"status":{
"type":"integer"
},
"create_time" : {
"type" : "date"
},
"last_time" : {
"type" : "date"
},
"skus":{
"type": "nested",
"properties": {
"name":{
"type":"text",
"analyzer": "ik_smart"
},
"price":{
"type":"scaled_float",
"scaling_factor":100
}
}
},
"attributes":{
"type": "nested",
"properties": {
"name": { "type": "keyword" },
"value": { "type": "keyword"}
}
}
}
}
}
PUT /attributes
{
"mappings": {
"properties": {
"name": {
"type": "keyword"
},
"value": {
"type": "keyword"
},
"category_id": {
"type": "integer"
},
"attribute_sort": {
"type": "integer"
},
"attribute_value_sort": {
"type": "integer"
},
"category": {
"type": "keyword"
},
"category_path": {
"type": "text"
}
}
}
}
{
"took" : 4,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 2,
"relation" : "eq"
},
"max_score" : 0.18232156,
"hits" : [
{
"_index" : "products",
"_type" : "_doc",
"_id" : "2",
"_score" : 0.18232156,
"_source" : {
"status" : 1,
"category_id" : 440,
"long_name" : "HUAWEI Mate Book 14 32GB 1TB 觸屏 集顯",
"path" : "-425-438-",
"id" : 2,
"type" : "_doc",
"shop_id" : 2,
"review_count" : 222,
"attributes" : [
{
"value" : "翡冷翠",
"name" : "顏色"
},
{
"value" : "冰霜銀",
"name" : "顏色"
},
{
"value" : "星際藍",
"name" : "顏色"
},
{
"value" : "R5/32GB/1TB 觸屏",
"name" : "配置"
},
{
"value" : "R7/32GB/1TB 觸屏",
"name" : "配置"
},
{
"value" : "整合顯示卡",
"name" : "顯示卡"
},
{
"value" : "官方標配",
"name" : "型別"
}
],
"brand_id" : 2,
"skus" : [
{
"name" : "翡冷翠 R5/32GB/1TB 觸屏 觸屏 整合顯示卡 官方標配",
"price" : 7999
},
{
"name" : "翡冷翠 R7/32GB/1TB 觸屏 觸屏 整合顯示卡 官方標配",
"price" : 9999
},
{
"name" : "冰霜銀 R5/32GB/1TB 觸屏 觸屏 整合顯示卡 官方標配",
"price" : 7999
},
{
"name" : "冰霜銀 R7/32GB/1TB 觸屏 觸屏 整合顯示卡 官方標配",
"price" : 9999
},
{
"name" : "星際藍 R5/32GB/1TB 觸屏 觸屏 整合顯示卡 官方標配",
"price" : 7999
},
{
"name" : "星際藍 R7/32GB/1TB 觸屏 觸屏 整合顯示卡 官方標配",
"price" : 9999
}
],
"@timestamp" : "2021-06-03T08:55:01.124Z",
"category" : "膝上型電腦",
"name" : "HUAWEI Mate Book 14",
"@version" : "1",
"create_time" : "2021-05-28T20:02:02.000Z",
"sold_count" : 222,
"last_time" : "2021-05-28T21:10:04.000Z",
"price" : 7999.0
}
},
{
"_index" : "products",
"_type" : "_doc",
"_id" : "1",
"_score" : 0.18232156,
"_source" : {
"status" : 1,
"category_id" : 440,
"long_name" : "HUAWEI Mate Book 13 16GB 512GB 觸屏 集顯",
"path" : "-425-438-",
"id" : 1,
"type" : "_doc",
"shop_id" : 1,
"review_count" : 1111,
"attributes" : [
{
"value" : "皓月銀",
"name" : "顏色"
},
{
"value" : "深空灰",
"name" : "顏色"
},
{
"value" : "櫻粉金",
"name" : "顏色"
},
{
"value" : "I5/16GB/512GB 觸屏",
"name" : "配置"
},
{
"value" : "I7/16GB/512GB 觸屏",
"name" : "配置"
},
{
"value" : "整合顯示卡",
"name" : "顯示卡"
},
{
"value" : "官方標配",
"name" : "型別"
}
],
"brand_id" : 1,
"skus" : [
{
"name" : "皓月銀 I5/16GB/512GB 觸屏 整合顯示卡 官方標配",
"price" : 6299
},
{
"name" : "皓月銀 I7/16GB/512GB 觸屏 整合顯示卡 官方標配",
"price" : 6599
},
{
"name" : "深空灰 I5/16GB/512GB 觸屏 整合顯示卡 官方標配",
"price" : 6299
},
{
"name" : "深空灰 I7/16GB/512GB 觸屏 整合顯示卡 官方標配",
"price" : 6599
},
{
"name" : "櫻粉金 I5/16GB/512GB 觸屏 整合顯示卡 官方標配",
"price" : 6299
},
{
"name" : "櫻粉金 I7/16GB/512GB 觸屏 整合顯示卡 官方標配",
"price" : 6599
}
],
"@timestamp" : "2021-06-03T08:55:01.122Z",
"category" : "膝上型電腦",
"name" : "HUAWEI Mate Book 13",
"@version" : "1",
"create_time" : "2021-05-25T15:12:09.000Z",
"sold_count" : 111,
"last_time" : "2021-05-25T15:12:14.000Z",
"price" : 6299.0
}
}
]
}
}
本作品採用《CC 協議》,轉載必須註明作者和本文連結