JDBC Importer - Custom mapping for single field

Elastic Stack Elasticsearch
1

Hi,

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
etc.

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,
Marv

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

https://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#operator_case

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

Thank you Jörg. This worked for me.