Import data from mysql to elasticsearch using logstash

Question

I would like to import data from my MySQL database to elasticsearch with logstash. I am already able to import a custom query result, but I am missing the point where I can define the mapping/settings of the index being created by logstash. Also, I have no idea how to import data with one to many relations.

This is my logstash.conf so far:

input {
jdbc {
    jdbc_connection_string => "jdbc:mysql://localhost:3306/shop"
    jdbc_user => "root"
    jdbc_password => ""
    jdbc_driver_library => "C:\Users\curUser\Desktop\mysql-connector-java-5.1.42\mysql-connector-java-5.1.42-bin.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"

    statement => "SELECT * FROM variants var"
    }
}

output {
elasticsearch {
    hosts => "localhost:9200"
    index => "search"
    document_type => "variants"
    document_id => "%{id}"
    }
}

Note: I want to use logstash just to import data to elasticsearch. This is for an online shop, so I have to use some analyzers on the fields

score 0 · Answer 1 · answered Aug 03 '17 at 11:54

0

I've looked through the documentation of the ES plugin for LogStash - not sure if this is possible.

However, you can set the index mapping before running LogStash. You can do so by putting the index mapping with cURL, or maybe there's a way to perform this HTTP request with LogStash

See https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping.html and https://www.elastic.co/guide/en/logstash/current/plugins-outputs-elasticsearch.html

answered Aug 03 '17 at 11:54

aclowkay

3,577
5
35
66

I have already read the documentation and came to the same result. If so, I know how to put the index mapping with curl, but what about the nested fields? Do you have any idea? Simple "inner join" does not work – RoyRobsen Aug 03 '17 at 12:08
Then you could use something like ` filter { mutate { rename => { "columnToNest" => "[level1][level2]" } } } ` – aclowkay Aug 03 '17 at 12:33

score 0 · Answer 2 · edited Sep 04 '18 at 20:49

For mapping/settings, you can use the Elasticsearch rest API
Once mapping/settings are created, use the same index name in logstash.conf file
If your table (SELECT * FROM variants var) has many columns and you don't want to import all of them, remove unwanted columns with a mutator:
```
filter { mutate {  remove_field => ["@version", "@timestamp", "column"]}}
```

If you want to rename a column use:

filter { mutate {  rename => { "id" => "ID" }}}

Join query can be added like you added the select query.

Import data from mysql to elasticsearch using logstash

2 Answers2