Moving multiple MySQL tables to ElasticSearch in the same index

Elastic Stack Logstash
1

I am migrating my database to ElasticSearch. Its current size is less than 3G, and since many fields are common across tables, I have decided to use a single index to store them.

Here's my Logstash configuration:

input {
  jdbc { 
    jdbc_connection_string => "jdbc:mysql://localhost:3306/cinestop?
useUnicode=yes&characterEncoding=UTF-8"
    jdbc_user => "root"
    jdbc_password => "abc"
    # The path to our downloaded jdbc driver
    jdbc_driver_library => 
"/home/limafoxtrottango/Downloads/elasticsearch-jdbc-2.3.4.0/lib/mysql-connector-java-5.1.38.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
statement => "SELECT * FROM media_adult"
       }
}
output {
  stdout { codec => json_lines }
  elasticsearch {
  "hosts" => "localhost:9200"
  "index" => "cinestop"
  "document_type" => "media_adult"
       }
}

I have imported a previous table (that had some common fields) with this table. Now, I get the following error:

"Rejecting mapping update to [cinestop] as the final mapping would have more than 1 type: [media_adult, media_id]"}}}}

I have read the documentation, and it mentions that ES won't let you have multiple fields with different types in the same index, due to how the mapping is done in Apache Lucene. But even if I do

SELECT adult FROM media_adult

in the config file (the resulting table won't have any common fields with the previously imported table), the error does not go away.

Can someone please tell me what is it that I am doing wrong? Should I create a separate index for each type?

Thanks!

2

But even if I do ... in the config file (the resulting table won't have any common fields with the previously imported table), the error does not go away.

It's irrelevant whether the tables have common fields. What matters is that you're not allowed to have more than one type in the same index.

Splitting the data in two indexes is probably a good idea, but it's certainly possible to use a single index too. Just don't use distincit types for documents from each tables. Keep in mind that the concepts from a relational database (table, rows, columns) doesn't map one-to-one to how data is stored in Elasticsearch.

3

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