3

A Huge cry for help here, When i try to convert a MySQL value to a nested elasticsearch field using logstash i get the following error.

{"exception"=>"expecting List or Map, found class org.logstash.bivalues.StringBiValue", "backtrace"=>["org.logstash.Accessors.newCollectionException(Accessors.java:195)"

Using the following config file:

input {
    jdbc {
        jdbc_driver_library => "/logstash/mysql-connector-java-5.1.42-bin.jar"
        jdbc_driver_class => "com.mysql.jdbc.Driver"
        jdbc_connection_string => "jdbc:mysql://localhost:3306/data"
        jdbc_user => "username"
        jdbc_password => "password"
        statement => "SELECT id, suggestions, address_count FROM `suggestions` WHERE id <= 100"
        jdbc_paging_enabled => "true"
        jdbc_page_size => "50000"
    }
}
filter {
  mutate {
  rename => { 'address_count' => '[suggestions][payload][count]' }
  }
}
output {
    elasticsearch {
    hosts => [
        "localhost:9200"
    ]
        index => "dev_suggestions"
        document_type => "address"
    }
}

However if i rename address_count to a field that is not already in my mapping, Then it works just fine and it correctly adds the value as a nested property, I have tried on other fields in my index and not just suggestions.payloads.address_count and i get the same issue, It only works if the field has not been defined in the mapping.

This has caused me some serious headaches and if anyone could help me out to overcome this issue i would greatly appreciate it as Ive spent the last 48 hours banging my head on the table!

I initially assumed i could do the following with a MySQL query:

SELECT id, suggestion, '[suggestions][payload][count]' FROM `suggestions` WHERE id <= 100

Then i also tried

SELECT id, suggestion, 'suggestions.payload.count' FROM `suggestions` WHERE id <= 100

Both failed to insert the value with the later option giving an error that a field can not contain dots.

And finally the mapping:

{
  "mappings": {
    "address": {
      "properties": {
        "suggestions": {
          "type": "completion",
          "payloads" : true
        }
      }
    }
  }
}

Thanks to Val - and for future users in the same situation as myself that need to convert MySQL data into nested Elasticsearch objects using logstash, Here is a working solution using Logstash 5 and Elasticsearch 2.*

input {
    jdbc {
        jdbc_driver_library => "/logstash/mysql-connector-java-5.1.42-bin.jar"
        jdbc_driver_class => "com.mysql.jdbc.Driver"
        jdbc_connection_string => "jdbc:mysql://localhost:3306/data"
        jdbc_user => "username"
        jdbc_password => "password"
        statement => "SELECT addrid, suggestion, address_count FROM `suggestions` WHERE id <= 20"
        jdbc_paging_enabled => "true"
        jdbc_page_size => "50000"
    }
}

filter {
  ruby {
       code => "
           event.set('[suggestions][input]', event.get('suggestion'))
           event.set('[suggestions][payload][address_count]', event.get('address_count'))
           event.set('[v][payload][id]', event.get('addrid'))
       "
       remove_field => [ 'suggestion', 'address_count', 'addrid' ]
  }
}

output {
    elasticsearch {
        hosts => [
            "localhost:9200"
        ]
        index => "dev_suggestions"
        document_type => "address"
    }
}
Birdy
  • 775
  • 1
  • 6
  • 21

1 Answers1

4

I think you need to proceed differently. First, I would rename the suggestions field in your SQL query to something else and then build the suggestions object from the values you get from your SQL query.

    statement => "SELECT id, suggestion, address_count FROM `suggestions` WHERE id <= 100"

Then you could use a ruby filter (and remove your mutate one) in order to build your suggestions field, like this:

Logstash 2.x code:

ruby {
     code => "
         event['suggestions']['input'] = event['suggestion']
         event['suggestions']['payload']['count'] = event['address_count']
     "
     remove_field => [ 'suggestion', 'address_count' ]
}

Logstash 5.x code:

ruby {
     code => "
         event.set('[suggestions][input]', event.get('suggestion'))
         event.set('[suggestions][payload][count]', event.get('address_count'))
     "
     remove_field => [ 'suggestion', 'address_count' ]
}

PS: All this assumes you're using ES 2.x since the payload field has disappeared in ES 5.x

Val
  • 207,596
  • 13
  • 358
  • 360
  • Thaks for taking the time out to help Val, i was litterally at the end of the road with no idea what to do next.. hoping you would save the day! I get the following error but will be able to look into it more as soon as i get back home. [2017-08-09T08:25:39,002][ERROR][logstash.filters.ruby ] Ruby exception occurred: Direct event field references (i.e. event['field']) have been disabled in favor of using event get and set methods (e.g. event.get('field')). Please consult the Logstash 5.0 breaking changes documentation for more details. – Birdy Aug 09 '17 at 08:28
  • P.S -- im a using ES 2.3.1, I will be back home in 45 minutes. – Birdy Aug 09 '17 at 08:30
  • Are you using Logstash 5 with ES 2.3.1? – Val Aug 09 '17 at 08:31
  • I think it is logstash 5 yes, I just thought the same thing! and thought i bet thats why? Its because i have been testing between ES 5.5 and 2.3.1 – Birdy Aug 09 '17 at 08:33
  • I've updated my answer with Logstash 5 compliant code. – Val Aug 09 '17 at 08:34
  • Val, you are an absolute freaking life saver.. Know that you have done a very good deed for a man in need haha! Without your help i would not have been able to do this and would have wasted another 2 days trying different configurations. I have no ruby experience what so ever so it was all a bit overwhelming for me but i guess we all need to ask for help at some point especially when we are just starting out. You have helped me move past a huge stopping block within my project and for that i am truly appreciative for your time and support, Wish i could TIP you 50$ when its more than deserved. – Birdy Aug 09 '17 at 11:05