Float values are imported incorrectly from mySQL DB

I import data from a mySQL DB via Logstash into an Elasticsearch index.
The database contains float values in a field.
However, these values change during import.
3378.2 from the mySQL DB becomes 3378.199951171875 in the Elasticsearch Index.
Does anyone have an idea why that is and how I can fix the error?
Is there a possibility to round the values by mutations during import?

Thanks for the help.

Could you share your logstash config file?

input {
  jdbc {
    jdbc_paging_enabled => true
    jdbc_page_size => 5000
    jdbc_driver_library => "/opt/logstash/mysql-connector-java-5.1.44-bin.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://db:3306/lightscout_data_2020"
    jdbc_user => "root"
    jdbc_password => "**"
    statement => "SELECT * FROM `familien_artikeldaten_2020_de`"
    type=>"artikeldaten_2020_de"
  }
}

filter {
  mutate {
    convert => {
      "pkm_artikeldaten" => "string"
    }
  }
}

output {
  if [type] == "artikeldaten_2020_de" {
    elasticsearch {
      index => "artikeldaten_2020_de"
      hosts => "elasticsearch:9200"
          document_id => "%{pkm_artikeldaten}"
    }
  }
}

In a single precision float there are 23 bits to the mantissa. Everything else is a rounding error. 2^23 is 8MM, so you should not expect accuracy beyond 7 digits.

Okay, what can I do to import the data without the many decimal places?

You could try using a ruby filter to call the round method of Float.

I have now added a Ruby filter and also installed the plugin via bin/logstash-plugin install logstash-filter-ruby.
Unfortunately I get the following error now:

root@376f1ace7f99:/opt/logstash# bin/logstash 
Sending Logstash logs to /opt/logstash/logs which is now configured via log4j2.properties
[2019-11-29T17:05:02,335][INFO ][logstash.runner          ] Starting Logstash {"logstash.version"=>"7.0.1"}
[2019-11-29T17:05:10,228][ERROR][logstash.plugins.registry] Tried to load a plugin's code, but failed. {:exception=>#<LoadError: no such file to load -- logstash/filters/"ruby">, :path=>"logstash/filters/\"ruby\"", :type=>"filter", :name=>"\"ruby\""}
[2019-11-29T17:05:10,259][ERROR][logstash.agent           ] Failed to execute action {:action=>LogStash::PipelineAction::Create/pipeline_id:main, :exception=>"LogStash::PluginLoadingError", :message=>"Couldn't find any filter plugin named '\"ruby\"'. Are you sure this is correct? Trying to load the \"ruby\" filter plugin resulted in this error: no such file to load -- logstash/filters/\"ruby\"", :backtrace=>["/opt/logstash/logstash-core/lib/logstash/plugins/registry.rb:211:in `lookup_pipeline_plugin'", "/opt/logstash/logstash-core/lib/logstash/plugin.rb:137:in `lookup'", "org/logstash/plugins/PluginFactoryExt.java:200:in `plugin'", "org/logstash/execution/JavaBasePipelineExt.java:50:in `initialize'", "/opt/logstash/logstash-core/lib/logstash/java_pipeline.rb:23:in `initialize'", "/opt/logstash/logstash-core/lib/logstash/pipeline_action/create.rb:36:in `execute'", "/opt/logstash/logstash-core/lib/logstash/agent.rb:325:in `block in converge_state'"]}
[2019-11-29T17:05:10,833][INFO ][logstash.agent           ] Successfully started Logstash API endpoint {:port=>9600}
[2019-11-29T17:05:15,631][INFO ][logstash.runner          ] Logstash shut down.

Do I have to do anything else to use the plugin?

The ruby filter is installed by default.

It looks to me as though you have

filter { "ruby" { ... } }

when you should have

filter { ruby { ... } }

Yes, that's the solution. Thanks!

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