How to load tables from mysql to elasticsearch

Elastic Stack Logstash
1

Hi,
I want load multiple tables from mysql to different indexes in elastic search(i.e; each table in mysql to different index in elastic search), Is it possible to load using single logstash config file.
Anyone can help on this.

2

If you want to do it in one pipeline, you could set up multiple JDBC inputs and add a metadata field (with add_field) to each one that says which index the data should go to and use that field in your Elasticsearch output with index =>"%{[@metadata][index_name]}".
If the index name depends not just on the table, but on the data of the individual entries (e.g. the log time) you could save the table name in the field and then use conditions like if([@metadata][source_table] == "table 1" { … } to direct the events to the correct output.

3

@Jenni
Hi Jenni

I have my logstash config file as below, i have to load table1 and table2 into different indexes, how can i do that.
Can u please help me on this.

input {
jdbc {
jdbc_driver_library => "/Users/logstash/mysql-connector-java-5.1.39-bin.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_connection_string => "jdbc:mysql://localhost:3306/database_name"
jdbc_user => "root"
jdbc_password => "password"
statement => "select * from table1"
type => "table1"
}
jdbc {
jdbc_driver_library => "/Users/logstash/mysql-connector-java-5.1.39-bin.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_connection_string => "jdbc:mysql://localhost:3306/database_name"
jdbc_user => "root"
jdbc_password => "password"

statement => "select * from table2"
type => "table2"

}

}
output {
elasticsearch {
index => "testdb"
document_type => "%{type}"
hosts => "localhost:9200"
}
}

4

The syntax to add the meta information to your events in the input would be add_field => {"[@metadata][index_name]" => "yourindexname"}

5

@Jenni
If i add this in input field (add_field => {"[@metadata][index_name]" => "yourindexname"})

did i have to mention the index name in output filed also?

6

Yes, just like I said before :slight_smile:

7

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