一: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 => true
codec => plain { charset => "UTF-8"}
jdbc_paging_enabled => true
tracking_column_type => numeric
tracking_column => id
jdbc_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:是用来链接数据库,版本需要和服务器的一致
jdbc_driver_class:数据库驱动
jdbc_connection_string:相应的数据源
jdbc_user:数据库账号
jdbc_password:数据库密码
statement_filepath:导入这个文件里面sql查出来的数据
type:根据库type名来判断输出给哪个索引
jdbc_paging_enabled:是否开启分页
tracking_column_type:递增字段的类型,numeric 表示数值类型, timestamp 表示时间戳类型
jdbc_page_size:分页的大小
hosts:链接es的路径
index:索引的名称
document_id:判断唯一标识的字段
2.对应的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.yyzbh
from cj_da_zd a, yx_meter b, yx_cjd c, cj_da_cjd d, yx_term_pz e,yx_term_az f
where 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
这四个sql文件就分别是各个索引需要导入的数据
3.pipelines.yml
路径:/home/bigdata/install/logstash-7.6.0/config
打开config文件夹 打开pipelines.yml文件
pipeline.id是索引名字 这里的索引名必须和jdbc_orcale.conf里面的index一致 不然会报错
4.使用/home/bigdata/install/logstash-7.6.0/bin/logstash -f /home/bigdata/install/logstash-7.6.0/driver/jdbc_oracle.conf & 这个命令就可以启动logstash
二:increase_index.sh脚本
路径:/home/bigdata/install/logstash-7.6.0/bin
#!/bin/sh
#获取当前时间
d=`date +%Y%m%d`
#获取当前时间前一天
yd=`date -d -1day +%Y%m%d`
#计量信息
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" }
}
}}'
#用户信息
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"}}]}'
#设置最大搜索值
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}}'
#往驱动插入修改索引名
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.conf
sed -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.conf
sed -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.conf
sed -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.yml
sed -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.yml
sed -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.yml
sed -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
#启动脚本
/home/bigdata/install/logstash-7.6.0/bin/logstash -f /home/bigdata/install/logstash-7.6.0/driver/jdbc_oracle.conf &
wait
#用户信息
curl -H "Content-Type:application/json" -XDELETE http://172.16.2.4:6200/yhxx$yd
#计量信息
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
创建计量信息索引以及让字段不分词
给索引起别名以及设置最大搜索值
sed -i为固定格式,就是直接对文本文件进行操作的
命令 | 说明 |
---|---|
sed -i ‘i’ | 在当前行之前插入一行 |
sed -i ‘d’ | 删除匹配的行 |
在启动logstash之前index的索引名字和pipeline.id这里的索引名字相对应,如果不相同logstash就跑不起来
接下来就是启动脚本 下面这个wait是等启动脚本这个操作执行完事之后,再进行删除名字为昨天的索引