JDBC Importer - Custom mapping for single field



I´m trying to index a Mantis MySql-database with Elasticsearch using the Elasticsearch JDBC Importer(which is a great tool by the way). The dynamic Mapping just works fine for me but I´m wondering if it is possible to map numerical values to a string value.

There is a status field for every Mantis issue, which is saved by long values from 10 to 90. Every number has a specific status, e.g:
10 => new
80 => resolved

Im trying to visualize the status data via Kibana and therefore it would be nice to have the status as string value. Is it possible to with a custom mapping, analyzer or something like that?

I appreciate your help,

(Jörg Prante) #2

JDBC importer maps SQL types to JSON types and the JSON types are mapped to Elasticsearch.

The trick is to write a smart (and verbose) SQL statement that takes your number column and turns them into a string. For an example, see the MySQL CASE SQL expression


SELECT CASE my_status_code
    WHEN 10 then 'new'
    WHEN 80 then 'resolved'
  END as my_status_text


Thank you Jörg. This worked for me.

(system) #4