Import Lat/Lon from SQL Server using JDBC

Elastic Stack Logstash
1

I'm trying to import a few fields into an existing index, 3 doubles and a geo_point. Spent the better part of the day with this one without luck.

Here is my index mapping setup:

 "mappings": {
"_doc": {
"properties": {
"paidin": {
"type": "double"
},
"risk": {
"type": "double"
},
"paidout": {
"type": "double"
},
"location": {
"type": "geo_point"
}
}
}

Here is my config:

input {
   jdbc {
      jdbc_driver_library => "C:\Program Files\Microsoft JDBC Driver 6.4 for SQL Server\sqljdbc_6.4\enu\mssql-jdbc-6.4.0.jre7.jar"
      jdbc_driver_class => "com.microsoft.sqlserver"
      jdbc_connection_string => "jdbc:sqlserver://server_name=db_name;user=****;password=*****"
      jdbc_user => "*****"
      clean_run => true
      statement => "SELECT top 20 [paidin] as paidin,[risk] as risk,[paidout] as paidout, lat as latitude, lon as longitude FROM [dbo].[Locations] WHERE lat > 0"
   }
}
filter {
   mutate {
    add_field => {"[location][lon]" => "%{longitude}"}
    add_field => {"[location][lat]" => "%{latitude}"}
    }
   
   mutate {
          remove_field => [ "latitude", "longitude", "@version","@timestamp" ]
      }
}
output {
   elasticsearch {
      hosts => "localhost"
      index => "geo_points"
      action => "index"
	  document_type => "type1"
   }
   stdout {
      codec => "rubydebug"
   }
}

here is the std-out rubydebug

{
           "paidin" => "5811.00",
    "paidout"=> "2681.00",
       "risk"=> "0.634000",
         "location" => {
        "lon" => "-78.118546",
        "lat" => "38.460266"
    }

here is the error when trying to get it into the index...

Could not index
 event to Elasticsearch. {:status=>400, :action=>["index", {:_id=>nil, :_index=>
"geo_points", :_type=>"type1", :_routing=>nil}, #<LogStash::Event:0x3834913e>],
:response=>{"index"=>{"_index"=>"geo_points", "_type"=>"type1", "_id"=>"awK5gGQB
EWz9z5vG4rXt", "status"=>400, "error"=>{"type"=>"illegal_argument_exception", "r
eason"=>"Rejecting mapping update to [geo_points] as the final mapping would hav
e more than 1 type: [_doc, type1]"}}}}

Any help would be appreciated

2

Answer - _doc as the mapping document type clashed with the doc type of apparently the location array...

Changed mapping from _doc to doc

3

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