win环境下把MySql中的数据导入到Elasticsearch（二）

最新推荐文章于 2024-08-05 14:21:54 发布

_小骑士

最新推荐文章于 2024-08-05 14:21:54 发布

阅读量5.6k

点赞数 3

本文链接：https://blog.csdn.net/qq_24265945/article/details/81169245

版权

本文介绍了如何在Windows环境中将MySQL数据库的数据导入到Elasticsearch，包括下载mysql-connector-java，配置jdbc连接，编写jdbc.sql脚本，以及解决logstash运行时的常见错误。文章提到了日志文件的报错提示，并提醒读者注意配置文件中的hosts字段，以及在导入前先创建合适的映射（mapping）以包含中文分词工具IK Analysis。

摘要由CSDN通过智能技术生成

环境问题参考我上文：

https://blog.csdn.net/qq_24265945/article/details/81168158

环境问题已经好了，接下来，我们讲实战。

下载：mysql-connector-java-5.1.46.zip

该压缩包帮助mysql与其他平台连接。看到很多资源都要积分，不能选0分，所以选1分了

https://download.csdn.net/download/qq_24265945/10558892

在bin目录下创建jdbc.config

根据需求改连接，账号，密码，名字，端口等等。

input {
    stdin {
    }
    jdbc {
      # mysql jdbc connection string to our backup databse
      jdbc_connection_string => "jdbc:mysql://127.0.0.1:3306/zhangjiang?characterEncoding=UTF-8&useSSL=false"
      # the user we wish to excute our statement as
      jdbc_user => "root"
      jdbc_password => "111111"
      # the path to our downloaded jdbc driver
      jdbc_driver_library => "D:/logstash-6.3.1/mysql-connector-java-5.1.46/mysql-connector-java-5.1.46/mysql-connector-java-5.1.46.jar"
      # the name of the driver class for mysql
      jdbc_driver_class => "com.mysql.jdbc.Driver"
      jdbc_paging_enabled => "true"
      jdbc_page_size => "50000"
	  jdbc_default_timezone => "UTC"
      statement_filepath => "D:/logstash-6.3.1/logstash-6.3.1/bin/jdbc.sql"
      schedule => "* * * * *"
      type => "patent"
    }
}

filter {
    json {
        source => "message"
        remove_field => ["message"]
    }
}

output {
    elasticsearch {
        hosts => "localhost:9200"
		index => "patent"
		document_id => "%{id}"
       
    }
    stdout {
        codec => json_lines
    }
}

创建jdbc.sql（这里根据自己需求改啦）

select 
    Patent_ID as id, 
    Patent_Num as pnum,
    Patent_Name as pname,
    Patent_Link as link,
    Patent_Applicant as applicant,
    Patent_Summary as summary,
    Patent_Date as pdate,
    Patent_ClassNum as classnum,
    Patent_Update as pupdate,
    Patent_Status as pstatus
from 
    patentinfor

在bin中输入 logstash -f jdbc.conf

疯狂导入啦~~~

这里也记录下几个报错的问题给大家参考：

D:\logstash-6.3.1\logstash-6.3.1\bin>logstash -f jdbc.conf

Sending Logstash's logs to D:/logstash-6.3.1/logstash-6.3.1/logs which is now configured via log4j2.properties

[2018-07-23T09:40:23,744][WARN ][logstash.config.source.multilocal] Ignoring the 'pipelines.yml' file because modules or command line options are specified

2018-07-23 09:40:23,822 LogStash::Runner ERROR Unable to move file D:\logstash-6.3.1\logstash-6.3.1\logs\logstash-plain.log to D:\logstash-6.3.1\logstash-6.3.1\logs\logstash-plain-2018-07-20-1.log: java.nio.file.FileSystemException D:\logstash-6.3.1\logstash-6.3.1\logs\logstash-plain.log -> D:\logstash-6.3.1\logstash-6.3.1\logs\logstash-plain-2018-07-20-1.log: 另一个程序正在使用此文件，进程无法访问。

已经有端口使用了。查看下是不是开着别的cmd在调用logstash,关闭就是了

D:\logstash-6.3.1\logstash-6.3.1\bin>logstash -f jdbc.conf

Sending Logstash's logs to D:/logstash-6.3.1/logstash-6.3.1/logs which is now configured via log4j2.properties

[2018-07-23T09:44:41,964][WARN ][logstash.config.source.multilocal] Ignoring the 'pipelines.yml' file because modules or command line options are specified

[2018-07-23T09:44:42,995][INFO ][logstash.runner ] Starting Logstash {"logstash.version"=>"6.3.1"}

[2018-07-23T09:44:46,715][ERROR][logstash.outputs.elasticsearch] Unknown setting 'host' for elasticsearch

[2018-07-23T09:44:46,715][ERROR][logstash.outputs.elasticsearch] Unknown setting 'port' for elasticsearch

[2018-07-23T09:44:46,715][ERROR][logstash.outputs.elasticsearch] Unknown setting 'protocol' for elasticsearch

[2018-07-23T09:44:46,715][ERROR][logstash.outputs.elasticsearch] Unknown setting 'cluster' for elasticsearch

[2018-07-23T09:44:46,746][ERROR][logstash.agent ] Failed to execute action {:action=>LogStash::PipelineAction::Create/pipeline_id:main, :exception=>"LogStash::ConfigurationError", :message=>"Something is wrong with your configuration.", :backtrace=>["D:/logstash-6.3.1/logstash-6.3.1/logstash-core/lib/logstash/config/mixin.rb:89:in `config_init'", "D:/logstash-6.3.1/logstash-6.3.1/logstash-core/lib/logstash/outputs/base.rb:62:in `initialize'", "org/logstash/config/ir/compiler/OutputStrategyExt.java:202:in `initialize'", "org/logstash/config/ir/compiler/OutputDelegatorExt.java:68:in `initialize'", "D:/logstash-6.3.1/logstash-6.3.1/logstash-core/lib/logstash/plugins/plugin_factory.rb:93:in `plugin'", "D:/logstash-6.3.1/logstash-6.3.1/logstash-core/lib/logstash/pipeline.rb:110:in `plugin'", "(eval):39:in `<eval>'", "org/jruby/RubyKernel.java:994:in `eval'", "D:/logstash-6.3.1/logstash-6.3.1/logstash-core/lib/logstash/pipeline.rb:82:in `initialize'", "D:/logstash-6.3.1/logstash-6.3.1/logstash-core/lib/logstash/pipeline.rb:167:in `initialize'", "D:/logstash-6.3.1/logstash-6.3.1/logstash-core/lib/logstash/pipeline_action/create.rb:40:in `execute'", "D:/logstash-6.3.1/logstash-6.3.1/logstash-core/lib/logstash/agent.rb:305:in `block in converge_state'"]}

[2018-07-23T09:44:47,215][INFO ][logstash.agent ] Successfully started Logstash API endpoint {:port=>9600}

这里是参考了别的网页，本来在下文中还写了port，protocol等内容，删除就是了，同时注意是hosts而不是host；记得查看自己的sql文件，如果sql文件出错，同样会导致导入失败，结果是：可以手动在cmd写内容进es，但是mysql的内容并没有录入es

output {
    elasticsearch {
        hosts => "localhost:9200"
       index => "patent"
       document_id => "%{id}"

    }
    stdout {
        codec => json_lines
    }

最后附上成功运行后可视化软件kibana图:

一周后的后续：当我加入中文分词工具以后，突然发现一个问题，映射是elasticsearch默认自己创建的，一旦创建没有办法修改，所以建议大家插入数据前先创建mapping，把分词工具加进去，具体分词工具我这里采用elasticsearch中的IK Analysis,后续会考虑其他分词工具，比较效果。这里附上我分词工具IK Analysis的安装和使用后续：

https://blog.csdn.net/qq_24265945/article/details/81355504