Create Mappings for DB output columns in Logstash configurations file


(Gangam Prashanth) #1

Hello everyone,
I'm new to logstash and elastic search.Please help me out on the below
We are working on loading data from SQL Server database onto elasticsearch.(Version 6.2.4)

The below is the simple configuration

input {
jdbc {
jdbc_driver_library => "sqljdbc4-2.0.jar"
jdbc_connection_string => "jdbc:sqlserver://localhost:1433;databaseName=sampleDB;MultiSubnetFailover=True;"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_user => ""
jdbc_password => "
****"
statement => "SELECT name , salary from Test"
}
}
output {
elasticsearch {
hosts => ["localhost:9200"]
index => "es_mappings"
document_type => "DOCUMENT_TYPE"
document_id => "es_configurations"
}
stdout { codec => rubydebug }
}

The above works as expected.

When we try to add mappings to it like below configuration :slight_smile:

input {
jdbc {
jdbc_driver_library => "sqljdbc4-2.0.jar"
jdbc_connection_string => "jdbc:sqlserver://localhost:1433;databaseName=sampleDB;MultiSubnetFailover=True;"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_user => ""
jdbc_password => "
****"
statement => "SELECT name , salary from Test"
}
}
mappings : {
"properties": {
"name": {
"type": "string",
"index": "not_analyzed",
"include_in_all": false
}
}

		}

output {
elasticsearch {
hosts => ["localhost:9200"]
index => "es_%{slice}_mappings"
document_type => "DOCUMENT_TYPE"
document_id => "es_configurations"
}
stdout { codec => rubydebug }
}

It throws an error.

Can someone please specify how we need to map a DB column output in elastic search for the below properties
"type": "string",
"index": "not_analyzed",
"include_in_all": false

The below is the error log:

C:\Elasticsearch\logstash-6.2.4\bin>logstash.bat -f logstash-sqlserver.conf
Sending Logstash's logs to C:/Elasticsearch/logstash-6.2.4/logs which is now configured via log4j2.properties
[2018-06-14T18:16:47,642][INFO ][logstash.modules.scaffold] Initializing module {:module_name=>"fb_apache", :directory=>"C:/Elasticsearch/logstash-6.2.4/modules/fb_apache/configuration"}
[2018-06-14T18:16:47,662][INFO ][logstash.modules.scaffold] Initializing module {:module_name=>"netflow", :directory=>"C:/Elasticsearch/logstash-6.2.4/modules/netflow/configuration"}
[2018-06-14T18:16:47,856][WARN ][logstash.config.source.multilocal] Ignoring the 'pipelines.yml' file because modules or command line options are specified
[2018-06-14T18:16:48,462][INFO ][logstash.runner ] Starting Logstash {"logstash.version"=>"6.2.4"}
[2018-06-14T18:16:48,981][INFO ][logstash.agent ] Successfully started Logstash API endpoint {:port=>9600}
[2018-06-14T18:16:49,694][ERROR][logstash.agent ] Failed to execute action {:action=>LogStash::PipelineAction::Create/pipeline_id:main, :exception=>"LogStash::ConfigurationError", :message=>"Expected one of #, input, filter, output at line 11, column 1 (byte 353) after ", :backtrace=>["C:/Elasticsearch/logstash-6.2.4/logstash-core/lib/logstash/compiler.rb:42:in compile_imperative'", "C:/Elasticsearch/logstash-6.2.4/logstash-core/lib/logstash/compiler.rb:50:incompile_graph'", "C:/Elasticsearch/logstash-6.2.4/logstash-core/lib/logstash/compiler.rb:12:in block in compile_sources'", "org/jruby/RubyArray.java:2486:inmap'", "C:/Elasticsearch/logstash-6.2.4/logstash-core/lib/logstash/compiler.rb:11:in compile_sources'", "C:/Elasticsearch/logstash-6.2.4/logstash-core/lib/logstash/pipeline.rb:51:ininitialize'", "C:/Elasticsearch/logstash-6.2.4/logstash-core/lib/logstash/pipeline.rb:169:in initialize'", "C:/Elasticsearch/logstash-6.2.4/logstash-core/lib/logstash/pipeline_action/create.rb:40:inexecute'", "C:/Elasticsearch/logstash-6.2.4/logstash-core/lib/logstash/agent.rb:315:in block in converge_state'", "C:/Elasticsearch/logstash-6.2.4/logstash-core/lib/logstash/agent.rb:141:inwith_pipelines'", "C:/Elasticsearch/logstash-6.2.4/logstash-core/lib/logstash/agent.rb:312:in block in converge_state'", "org/jruby/RubyArray.java:1734:ineach'", "C:/Elasticsearch/logstash-6.2.4/logstash-core/lib/logstash/agent.rb:299:in converge_state'", "C:/Elasticsearch/logstash-6.2.4/logstash-core/lib/logstash/agent.rb:166:inblock in converge_state_and_update'", "C:/Elasticsearch/logstash-6.2.4/logstash-core/lib/logstash/agent.rb:141:in with_pipelines'", "C:/Elasticsearch/logstash-6.2.4/logstash-core/lib/logstash/agent.rb:164:inconverge_state_and_update'", "C:/Elasticsearch/logstash-6.2.4/logstash-core/lib/logstash/agent.rb:90:in execute'", "C:/Elasticsearch/logstash-6.2.4/logstash-core/lib/logstash/runner.rb:348:inblock in execute'", "C:/Elasticsearch/logstash-6.2.4/vendor/bundle/jruby/2.3.0/gems/stud-0.0.23/lib/stud/task.rb:24:in `block in initialize'"]}

Thanks,
Prashanth


(Magnus B├Ąck) #2

Mappings can't be stored in the Logstash configuration file. Read about index templates in the ES documentation and check out the template-related configuration options of the elasticsearch output.


(system) #3

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.