一：Logstash配置文件

1.jdbc_orcale.conf

路徑：/home/bigdata/install/logstash-7.6.0/bin

input { jdbc {jdbc_driver_library => "/home/bigdata/install/logstash-7.6.0/driver/ojdbc8-19.3.0.0.jar"jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"jdbc_connection_string => "jdbc:oracle:thin:@172.16.2.2:1521:yxyxdb"jdbc_user => "yx_data"jdbc_password => "sfyd"statement_filepath => "/home/bigdata/install/logstash-7.6.0/driver/test_oracle_yhxx.sql"type => "yhxxsy"use_column_value => truecodec => plain { charset => "UTF-8"}jdbc_paging_enabled => truetracking_column_type => numerictracking_column => idjdbc_page_size => 500000}} filter {date {match => [ "timestamp" , "yyyy/MM/dd HH:mm:ss Z" ]} } output {if[type] == "yhxxsy" {elasticsearch {hosts => ["172.16.2.4:6200","172.16.2.5:6200","172.16.2.6:6200"] index => "yhxx20210118"document_type => "_doc"document_id => "%{id}"}}}

jdbc_driver_library：是用來(lái)鏈接數(shù)據(jù)庫(kù)，版本需要和服務(wù)器的一致

jdbc_driver_class：數(shù)據(jù)庫(kù)驅(qū)動(dòng)

jdbc_connection_string：相應(yīng)的數(shù)據(jù)源

jdbc_user：數(shù)據(jù)庫(kù)賬號(hào)

jdbc_password：數(shù)據(jù)庫(kù)密碼

statement_filepath：導(dǎo)入這個(gè)文件里面sql查出來(lái)的數(shù)據(jù)

type：根據(jù)庫(kù)type名來(lái)判斷輸出給哪個(gè)索引

jdbc_paging_enabled：是否開(kāi)啟分頁(yè)

tracking_column_type：遞增字段的類(lèi)型，numeric 表示數(shù)值類(lèi)型, timestamp 表示時(shí)間戳類(lèi)型

jdbc_page_size：分頁(yè)的大小

hosts：鏈接es的路徑

index：索引的名稱(chēng)

document_id：判斷唯一標(biāo)識(shí)的字段

2.對(duì)應(yīng)的sql文件

路徑：/home/bigdata/install/logstash-7.6.0/driver

SELECT TL.*,ROWNUM rn FROM ( select b.cjdbh || e.termbh as ID,b.yhdabh ,d.xlmc ckxlmc,d.bdzmc ckbdzmc,a.termmc cktermmc,a.termbh cktermbh,a.txdz cktxdz,a.termlx cktermlx，b.gsbh,b.fgsbh,b.yyzbhfrom cj_da_zd a, yx_meter b, yx_cjd c, cj_da_cjd d, yx_term_pz e,yx_term_az fwhere b.cjdbh = c.cjdbh(+)and b.cjdbh = d.cjdbh(+)and b.cjdbh = e.cjdbh(+)and e.termbh = a.termbh(+)and e.termbh = f.termbh(+) ) TL

這四個(gè)sql文件就分別是各個(gè)索引需要導(dǎo)入的數(shù)據(jù)

3.pipelines.yml

路徑：/home/bigdata/install/logstash-7.6.0/config

打開(kāi)config文件夾 打開(kāi)pipelines.yml文件

pipeline.id是索引名字 這里的索引名必須和jdbc_orcale.conf里面的index一致 不然會(huì)報(bào)錯(cuò)

4.使用/home/bigdata/install/logstash-7.6.0/bin/logstash -f /home/bigdata/install/logstash-7.6.0/driver/jdbc_oracle.conf & 這個(gè)命令就可以啟動(dòng)logstash

二：increase_index.sh腳本

路徑：/home/bigdata/install/logstash-7.6.0/bin

#!/bin/sh #獲取當(dāng)前時(shí)間 d=`date +%Y%m%d` #獲取當(dāng)前時(shí)間前一天 yd=`date -d -1day +%Y%m%d` #計(jì)量信息 curl -H "Content-Type:application/json" -XPUT http://172.16.2.6:6200/jlxx$d/?pretty -d '{"settings":{"number_of_shards":5,"number_of_replicas":2},"mappings": {"properties": {"id": { "type": "keyword" }, "yhdabh": { "type": "keyword" },"yhmc": { "type": "keyword" },"addr": { "type": "keyword" },"cblxbh": { "type": "keyword" },"ydrl": { "type": "keyword" }, "jlbbh": { "type": "keyword" },"djmc": { "type": "keyword" },"azwz": { "type": "keyword" },"sccj": { "type": "keyword" },"dbxb": { "type": "keyword" }, "ptbl": { "type": "keyword" },"ctbl": { "type": "keyword" },"zhbl": { "type": "keyword" },"bjlx": { "type": "keyword" },"syfs": { "type": "keyword" }, "hgqxs": { "type": "keyword" },"zcbh": { "type": "keyword" },"zqddj": { "type": "keyword" },"hgqdy": { "type": "keyword" }}}}' #用戶(hù)信息 curl -H "Content-Type:application/json" -XPUT http://172.16.2.6:6200/yhxx$d/?pretty -d '{"settings":{"number_of_shards":5,"number_of_replicas":2},"mappings": {"properties": {"id": { "type": "keyword" }, "yhdabh": { "type": "keyword" },"yhmc": { "type": "keyword" },"jlbbh": { "type": "keyword" },"tel": { "type": "keyword" },"dydj": { "type": "keyword" }, "yhzt": { "type": "keyword" },"jlfs": { "type": "keyword" },"ydflbh": { "type": "keyword" },"zjlb": { "type": "keyword" },"gmjjhybh": { "type": "keyword" }, "zjhm": { "type": "keyword" },"djmc": { "type": "keyword" },"tqmc": { "type": "keyword" },"xlmc": { "type": "keyword" },"bdzmc": { "type": "keyword" },"yhlx": { "type": "keyword" }}}}' #工作票 curl -H "Content-Type:application/json" -XPUT http://172.16.2.6:6200/gzpxx$d/?pretty -d '{"settings":{"number_of_shards":5,"number_of_replicas":2},"mappings": {"properties": {"id": { "type": "keyword" }, "ywmc": { "type": "keyword" },"gdbh": { "type": "keyword" },"rwmc": { "type": "keyword" },"khbh": { "type": "keyword" },"khmc": { "type": "keyword" },"zcbz": { "type": "keyword" },"dnzcbh": { "type": "keyword" },"zcbh": { "type": "keyword" },"cjdbh": { "type": "keyword" },"termZcbh": { "type": "keyword" },"sqsj": { "type": "keyword" },"wcsj": { "type": "keyword" },"clzt": { "type": "keyword" }}}}' #采控信息 curl -H "Content-Type:application/json" -XPUT http://172.16.2.6:6200/ckxx$d/?pretty -d '{"settings":{"number_of_shards":5,"number_of_replicas":2},"mappings": {"properties": {"id": { "type": "keyword" }, "yhdabh": { "type": "keyword" },"cjdbh": { "type": "keyword" },"jlbbh": { "type": "keyword" },"cjdmc": { "type": "keyword" },"yxcjdbh": { "type": "keyword" },"termbh": { "type": "keyword" },"termmc": { "type": "keyword" },"yxtermbh": { "type": "keyword" },"txdz": { "type": "keyword" },"termlx": { "type": "keyword" },"tqmc": { "type": "keyword" },"xlmc": { "type": "keyword" },"bdzmc": { "type": "keyword" },"ckcjdbh": { "type": "keyword" },"ckjlbbh,": { "type": "keyword" },"sjly": { "type": "keyword" },"cktqmc": { "type": "keyword" },"ckxlmc": { "type": "keyword" },"ckbdzmc": { "type": "keyword" },"cktermmc": { "type": "keyword" },"cktermbh": { "type": "keyword" },"cktxdz": { "type": "keyword" },"cktermlx": { "type": "keyword" }}}}' #起別名操作 curl -H "Content-Type:application/json" -XPOST http://172.16.2.5:6200/_aliases -d'{"actions":[{"add":{"index":"yhxx'$d'","alias":"yhxx"}}]}' curl -H "Content-Type:application/json" -XPOST http://172.16.2.5:6200/_aliases -d'{"actions":[{"add":{"index":"jlxx'$d'","alias":"jlxx"}}]}' curl -H "Content-Type:application/json" -XPOST http://172.16.2.5:6200/_aliases -d'{"actions":[{"add":{"index":"gzpxx'$d'","alias":"gzpxx"}}]}' curl -H "Content-Type:application/json" -XPOST http://172.16.2.5:6200/_aliases -d'{"actions":[{"add":{"index":"ckxx'$d'","alias":"ckxx"}}]}' #設(shè)置最大搜索值 curl -X PUT "http://172.16.2.4:6200/yhxx$d/_settings?pretty" -H 'Content-Type: application/json' -d'{"index":{"max_result_window":100000000}}' curl -X PUT "http://172.16.2.4:6200/jlxx$d/_settings?pretty" -H 'Content-Type: application/json' -d'{"index":{"max_result_window":100000000}}' curl -X PUT "http://172.16.2.4:6200/gzpxx$d/_settings?pretty" -H 'Content-Type: application/json' -d'{"index":{"max_result_window":100000000}}' curl -X PUT "http://172.16.2.4:6200/ckxx$d/_settings?pretty" -H 'Content-Type: application/json' -d'{"index":{"max_result_window":100000000}}' #往驅(qū)動(dòng)插入修改索引名 sed -i '72d' /home/bigdata/install/logstash-7.6.0/driver/jdbc_oracle.conf sed -i '72i''index => "yhxx'$d'"' /home/bigdata/install/logstash-7.6.0/driver/jdbc_oracle.confsed -i '80d' /home/bigdata/install/logstash-7.6.0/driver/jdbc_oracle.conf sed -i '80i''index => "jlxx'$d'"' /home/bigdata/install/logstash-7.6.0/driver/jdbc_oracle.confsed -i '88d' /home/bigdata/install/logstash-7.6.0/driver/jdbc_oracle.conf sed -i '88i''index => "ckxx'$d'"' /home/bigdata/install/logstash-7.6.0/driver/jdbc_oracle.confsed -i '96d' /home/bigdata/install/logstash-7.6.0/driver/jdbc_oracle.conf sed -i '96i''index => "gzpxx'$d'"' /home/bigdata/install/logstash-7.6.0/driver/jdbc_oracle.conf#修改配置文件 sed -i '78d' /home/bigdata/install/logstash-7.6.0/config/pipelines.yml sed -i '78i''- pipeline.id: yhxx'$d'' /home/bigdata/install/logstash-7.6.0/config/pipelines.ymlsed -i '80d' /home/bigdata/install/logstash-7.6.0/config/pipelines.yml sed -i '80i''- pipeline.id: jlxx'$d'' /home/bigdata/install/logstash-7.6.0/config/pipelines.ymlsed -i '82d' /home/bigdata/install/logstash-7.6.0/config/pipelines.yml sed -i '82i''- pipeline.id: ckxx'$d'' /home/bigdata/install/logstash-7.6.0/config/pipelines.ymlsed -i '84d' /home/bigdata/install/logstash-7.6.0/config/pipelines.yml sed -i '84i''- pipeline.id: gzpxx'$d'' /home/bigdata/install/logstash-7.6.0/config/pipelines.yml#啟動(dòng)腳本 /home/bigdata/install/logstash-7.6.0/bin/logstash -f /home/bigdata/install/logstash-7.6.0/driver/jdbc_oracle.conf & wait #用戶(hù)信息 curl -H "Content-Type:application/json" -XDELETE http://172.16.2.4:6200/yhxx$yd #計(jì)量信息 curl -H "Content-Type:application/json" -XDELETE http://172.16.2.5:6200/jlxx$yd #工作票信息 curl -H "Content-Type:application/json" -XDELETE http://172.16.2.6:6200/gzpxx$yd #采控信息 curl -H "Content-Type:application/json" -XDELETE http://172.16.2.6:6200/ckxx$yd

創(chuàng)建計(jì)量信息索引以及讓字段不分詞

給索引起別名以及設(shè)置最大搜索值

sed -i為固定格式，就是直接對(duì)文本文件進(jìn)行操作的

命令說(shuō)明

sed -i ‘i’	在當(dāng)前行之前插入一行
sed -i ‘d’	刪除匹配的行

在啟動(dòng)logstash之前index的索引名字和pipeline.id這里的索引名字相對(duì)應(yīng)，如果不相同logstash就跑不起來(lái)

接下來(lái)就是啟動(dòng)腳本 下面這個(gè)wait是等啟動(dòng)腳本這個(gè)操作執(zhí)行完事之后，再進(jìn)行刪除名字為昨天的索引

總結(jié)

以上是生活随笔為你收集整理的elasticsearch7.6 + Oracle12 数据同步 logstatsh 无缝衔接一天同步一次的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。