Logstash SQL Server数据导入

问题描述

input {
  jdbc {
    jdbc_driver_library => "sqljdbc4.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_connection_string => "jdbc:sqlserver://192.168.2.126\\SQLEXPRESS2014:1433;databaseName=test
	jdbc_password => "sa@sa2015"
    schedule => "0 0-59 0-23 * * *"
    statement => "SELECT ID , Name, City, State,ShopName FROM dbo.Shops"
	jdbc_paging_enabled => "true"
    jdbc_page_size => "50000"
  }
}
filter {
}
output {
  stdout { codec => rubydebug }
    elasticsearch {
        protocol => "http"
		index => "shops"
		document_id => "%{id}"
    }
}

我想使用Logstash(使用JDBC SQL Server作为输入)在ElasticSearch中导入数据，但是我得到的错误类路径不正确.

I want to import data in ElasticSearch using Logstash using JDBC SQL Server as input but I am getting error class path is not correct.

任何人都知道如何使用Logstash连接sqljdbc FILE和CONFIG FILE的正确位置

Anybody know how to connect using Logstash for correct location for sqljdbc FILE WITH CONFIG FILE

推荐答案

我认为"sqljdbc4.jar"文件的路径不正确.这是我用来从sql db查询数据到elasticsearch(logstash.conf)的配置:

I think that path to the "sqljdbc4.jar" file is not correct. Here is the config I am using to query data from a sql db into elasticsearch (logstash.conf):

input {
  jdbc {
    jdbc_driver_library => "D:\temp\sqljdbc\sqljdbc_4.2\enu\sqljdbc42.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_connection_string => "jdbc:sqlserver://DBSVR_NAME;user=****;password=****;"
    jdbc_user => "****"
    jdbc_password => "****"
    statement => "SELECT *
FROM [DB].[SCHEMA].[TABLE]"
  }
}
filter {
}
output {
  elasticsearch {
    hosts => "localhost"
    index => "INDEX_NAME"
    document_type => "DOCUMENT_TYPE"
    document_id => "%{id}"
    protocol => "http"
  }
  stdout { codec => rubydebug }
}

我从此处下载了用于SQL Server的Microsoft JDBC驱动程序:" https://msdn.microsoft.com/en-us/sqlserver/aa937724 .aspx "

I downloaded the Microsoft JDBC Driver for SQL Server from here:"https://msdn.microsoft.com/en-us/sqlserver/aa937724.aspx"

将文件提取到"jdbc_driver_library"中指定的路径

Extracted the files to the path specified in "jdbc_driver_library"

然后我运行插件命令:"plugin install logstash-input-jdbc"以安装logstash输入jdbc插件.

Then I ran the plugin command: "plugin install logstash-input-jdbc" to install the logstash input jdbc plugin.

最后运行logstash:"logstash -f logstash.conf".

And finally running logstash: "logstash -f logstash.conf".

顺便说一句:我还在.Net服务应用程序中使用Elasticsearch.Net刷新数据" http://nest.azurewebsites.net/"

As an aside: I am also using Elasticsearch.Net in a .Net service app to refresh the data"http://nest.azurewebsites.net/"

这个视频:将Elasticsearch添加到现有的.NET/SQL Server应用程序中"" https://www.youtube.com/watch?v=sv-MflnT9qI 讨论使用Service Broker队列从sql中获取数据.我们目前正在将此作为一种选择.

And this vid: "Adding Elasticsearch To An Existing .NET / SQL Server Application" "https://www.youtube.com/watch?v=sv-MflnT9qI" discuses using a Service Broker queue to get the data out of sql. We are currently exploring this as an option.

编辑-如此处文档所述，将主机更新为主机 https://www.elastic.co/guide/zh-CN/logstash/current/plugins-outputs-elasticsearch.html#plugins-outputs-elasticsearch-hosts

Edit - Updated host to hosts as in documentation here https://www.elastic.co/guide/en/logstash/current/plugins-outputs-elasticsearch.html#plugins-outputs-elasticsearch-hosts

这篇关于Logstash SQL Server数据导入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！