将MySQL值转换为建议字段上的嵌套elasticsearch属性时出现Logstash错误 [英] Logstash error when converting MySQL value to nested elasticsearch property on suggestion field
问题描述
在这里寻求帮助的呼声很高,当我尝试使用Logstash将MySQL值转换为嵌套的Elasticsearch字段时,出现以下错误.
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)"
使用以下配置文件:
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"
}
}
但是,如果我将address_count重命名为我的映射中尚未存在的字段,那么它可以正常工作,并且可以将值正确地添加为嵌套属性,我已经尝试了在我的其他字段中使用索引,而不仅仅是Recommendations.payloads.address_count,我遇到了同样的问题,仅当未在映射中定义字段时,它才有效.
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.
这使我感到有些头疼,如果有人可以帮助我解决这个问题,我将不胜感激,因为我花了最后48个小时在桌子上敲打我的头!
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!
我最初以为我可以通过MySQL查询执行以下操作:
I initially assumed i could do the following with a MySQL query:
SELECT id, suggestion, '[suggestions][payload][count]' FROM `suggestions` WHERE id <= 100
然后我也尝试过
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.
最后是映射:
{
"mappings": {
"address": {
"properties": {
"suggestions": {
"type": "completion",
"payloads" : true
}
}
}
}
}
感谢Val -对于与我本人处于相同情况的未来用户,需要使用logstash将MySQL数据转换为嵌套的Elasticsearch对象,这是使用Logstash 5和Elasticsearch的有效解决方案2.*
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"
}
}
推荐答案
我认为您需要以不同的方式进行.首先,我将SQL查询中的suggestions
字段重命名为其他名称,然后根据从SQL查询中获得的值构建suggestions
对象.
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"
然后,您可以使用ruby
过滤器(并删除mutate
一个)来构建您的suggestions
字段,如下所示:
Then you could use a ruby
filter (and remove your mutate
one) in order to build your suggestions
field, like this:
Logstash 2.x代码:
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代码:
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:所有这些都假设您正在使用ES 2.x,因为payload
字段在ES 5.x中已消失
PS: All this assumes you're using ES 2.x since the payload
field has disappeared in ES 5.x
这篇关于将MySQL值转换为建议字段上的嵌套elasticsearch属性时出现Logstash错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!