logstash同步mysql数据到elasticsearch，动态模板

最新推荐文章于 2023-04-21 02:18:21 发布

在路上669

最新推荐文章于 2023-04-21 02:18:21 发布

阅读量426

点赞数

分类专栏： elk 文章标签： elasticsearch mysql 数据库

本文链接：https://blog.csdn.net/obj_668793/article/details/107435628

版权

elk 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

后台运行logstash

Last login: Thu Jul 23 17:50:26 2020
[root@localhost ~]# nohup /home/essoft/logstash-6.8.0/bin/logstash -f /home/essoft/logstash-6.8.0/bin/mysql/allfiledtalentid.conf >/dev/null &

只是单纯的模板记录，各项属性待研究

input {
   stdin { }
   jdbc {
       type => "talent_all_type"

       #数据库地址

       # mysql 数据库链接,itv_web_log为数据库名
       jdbc_connection_string => "jdbc:mysql://192.168.0.100:3306/ztwbxwtest?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true"

       # 用户名和密码
       jdbc_user => "root"
       jdbc_password => "123456"

       # 驱动
       jdbc_driver_library => "/home/elksofts/logstash-6.8.0/bin/mysqlv1/mysql-connector-java-5.1.37.jar"

       # 驱动类名
       jdbc_driver_class => "com.mysql.jdbc.Driver"


       #sql路径
       # 执行的sql 文件路径+名称
       statement_filepath => "/home/elksofts/logstash-6.8.0/bin/mysqlv1/allfiledtalentid.sql"


       #是否开启记录追踪
       record_last_run => "true"
       #是否需要追踪字段，如果为true，则需要指定tracking_column，默认是timestamp
       use_column_value => "true"
       #指定追踪的字段
       tracking_column => "f_talent_id"
       #追踪字段的类型，目前只有数字和时间类型，默认是数字类型
       #tracking_column_type => "timestamp"

       #设置时区
       #jdbc_default_timezone =>"Asia/Shanghai"

       #是否每次清除 last_run_metadata_path 的内容
       clean_run => "false"

       #这里可以手动设置:sql_last_value的值，默认时间是1970-01-01，默认数字是0
       #们只需要在 SQL 语句中 WHERE MY_ID > :last_sql_value 即可. 其中 :sql_last_value 取得就是该文件中的值
       last_run_metadata_path => "/home/elksofts/logstash-6.8.0/bin/mysqlv1/logstashrun/allfiledtalentid.txt"

       #多久同步一次
       schedule => "* * * * *"
       #是否分页
       jdbc_paging_enabled => "true"
       jdbc_page_size => "50000"
   }

   jdbc {
       type => "cv_talent_resume_jobs_type"

       #数据库地址

       # mysql 数据库链接,itv_web_log为数据库名
       jdbc_connection_string => "jdbc:mysql://192.168.0.100:3306/ztwbxwtest?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true"

       # 用户名和密码
       jdbc_user => "root"
       jdbc_password => "123456"

       # 驱动
       jdbc_driver_library => "/home/elksofts/logstash-6.8.0/bin/mysqlv1/mysql-connector-java-5.1.37.jar"

       # 驱动类名
       jdbc_driver_class => "com.mysql.jdbc.Driver"


       #sql路径
       # 执行的sql 文件路径+名称
       statement_filepath => "/home/elksofts/logstash-6.8.0/bin/mysqlv1/cv_talent_resume_jobs.sql"


       #是否开启记录追踪
       record_last_run => "true"
       #是否需要追踪字段，如果为true，则需要指定tracking_column，默认是timestamp
       use_column_value => "true"
       #指定追踪的字段
       tracking_column => "f_ctj_id"
       #追踪字段的类型，目前只有数字和时间类型，默认是数字类型
       #tracking_column_type => "timestamp"

       #设置时区
       #jdbc_default_timezone =>"Asia/Shanghai"

       #是否每次清除 last_run_metadata_path 的内容
       clean_run => "false"

       #这里可以手动设置:sql_last_value的值，默认时间是1970-01-01，默认数字是0
       #们只需要在 SQL 语句中 WHERE MY_ID > :last_sql_value 即可. 其中 :sql_last_value 取得就是该文件中的值
       last_run_metadata_path => "/home/elksofts/logstash-6.8.0/bin/mysqlv1/logstashrun/cv_talent_resume_jobs.txt"

       #多久同步一次
       schedule => "* * * * *"
       #是否分页
       jdbc_paging_enabled => "true"
       jdbc_page_size => "50000"
   }
}
filter {
   # json {
   #    source => "message"
   #    remove_field => ["message"]
   # }

   mutate {
       #指定要删除的字段
       #remove_field => "tempid"
       #remove_field => "f_ctr_talentId"
       #remove_field => "f_ctj_resumeId"
       #remove_field => "f_ctre_resumeId"
       #remove_field => "f_ctrp_resumeId"
       #remove_field => "f_ctrs_resumeId"

       #merge => {"author" => ["name","price"]}
   }


}
output {

   #stdout {
   #   codec => rubydebug
   #}

   if[type] == "talent_all_type"{
       elasticsearch {
           hosts => ["http://192.168.0.101:9200/"]
           index => "talent_all"
           upsert => "update"
           document_id => "%{f_talent_id}"

           manage_template => true
           template_overwrite => true
           template_name => "talent_allfiled"
           template => "/home/elksofts/logstash-6.8.0/bin/mysqlv1/personalset/cv_talent_allfiled_template.json"
       }
   }

   if[type] == "cv_talent_resume_jobs_type"{
       elasticsearch {
           hosts => ["http://192.168.0.101:9200/"]
           index => "resume_jobs"
           upsert => "update"
           document_id => "%{f_ctj_id}"

           manage_template => true
           template_overwrite => true
           template_name => "cv_talent_resume_jobs_template"
           template => "/home/elksofts/logstash-6.8.0/bin/mysqlv1/personalset/cv_talent_resume_jobs_template.json"
       }
   }
}

======

cv_talent_allfiled_template.json

{
   "settings":{
        "number_of_shards": 3,
        "number_of_replicas": 0
    },
   "template": "talent_all",
   "mappings": {
       "doc": {
          "dynamic_templates": [
           {
              "integers": {
               "match_mapping_type": "long",
               "mapping": {
                  "type": "integer"
               }
              }
           },
           {
              "strings": {
               "match_mapping_type": "string",
               "mapping": {
                  "type": "text",
                  "analyzer": "ik_max_word",
                  "fields": {
                   "keyword": {
                      "type": "keyword",
                      "ignore_above": 256
                   }
                  }
               }
              }
           }
          ]
       }
   }
}

=====

cv_talent_resume_jobs_template.json

{
   "settings":{
        "number_of_shards": 3,
        "number_of_replicas": 0
    },
   "template": "resume_jobs",
   "mappings": {
       "doc": {
          "dynamic_templates": [
           {
              "integers": {
               "match_mapping_type": "long",
               "mapping": {
                  "type": "integer"
               }
              }
           },
           {
              "strings": {
               "match_mapping_type": "string",
               "mapping": {
                  "type": "text",
                  "analyzer": "ik_max_word",
                  "fields": {
                   "keyword": {
                      "type": "keyword",
                      "ignore_above": 256
                   }
                  }
               }
              }
           }
          ]
       }
   }
}