How to integrate ElasticSearch with MySQL?

by

As of ES 5.x , they have given this feature out of the box with logstash plugin.

This will periodically import data from database and push to ES server.

One has to create a simple import file given below (which is also described here) and use logstash to run the script. Logstash supports running this script on a schedule.

# file: contacts-index-logstash.conf
input {
    jdbc {
        jdbc_connection_string => "jdbc:mysql://localhost:3306/mydb"
        jdbc_user => "user"
        jdbc_password => "pswd"
        schedule => "* * * * *"
        jdbc_validate_connection => true
        jdbc_driver_library => "/path/to/latest/mysql-connector-java-jar"
        jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
        statement => "SELECT * from contacts where updatedAt > :sql_last_value"
    }
}
output {
    elasticsearch {
        protocol => http
        index => "contacts"
        document_type => "contact"
        document_id => "%{id}"
        host => "ES_NODE_HOST"
    }
}
# "* * * * *" -> run every minute
# sql_last_value is a built in parameter whose value is set to Thursday, 1 January 1970,
# or 0 if use_column_value is true and tracking_column is set

You can download the mysql jar from maven here.

In case indexes do not exist in ES when this script is executed, they will be created automatically. Just like a normal post call to elasticsearch

More Related Contents:

  1. How to setup ElasticSearch index structure with multiple entity bindings
  2. How to fuzzy match email or telephone by Elasticsearch?
  3. Laravel – search with multiple keywords against multiple columns with the search result to be ordered in relevance
  4. mysql_fetch_array() expects parameter 1 to be resource, boolean given in [closed]
  5. How to group by two rows in a column
  6. How to perform grouped ranking in MySQL
  7. How to abort INSERT operation in MySql trigger?
  8. Mysql or/and precedence?
  9. MySQL “Group By” and “Order By”
  10. CHECK constraint in MySQL is not working
  11. ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111)
  12. Migrate from Oracle to MySQL
  13. Which is more efficient: Multiple MySQL tables or one large table?
  14. Are there disadvantages to using a generic varchar(255) for all text-based fields?
  15. How to skip columns in CSV file when importing into MySQL table using LOAD DATA INFILE?
  16. Error Code 1292 – Truncated incorrect DOUBLE value – Mysql
  17. MySQL InnoDB foreign key between different databases
  18. Advanced MySql Query: Update table with info from another table
  19. MySQL CREATE FUNCTION Syntax
  20. MySQL sum elements of a column
  21. Difference between SET autocommit=1 and START TRANSACTION in mysql (Have I missed something?)
  22. MySQL equivalent of Oracle’s SEQUENCE.NEXTVAL
  23. Why MySQL is giving error “Not allowed to return a result set from a function”?
  24. How to store decimal in MySQL?
  25. MySQL cumulative sum grouped by date
  26. Where I can find a list of “mysqldump” exit codes?
  27. ALTER TABLE ADD COLUMN takes a long time
  28. Creating MYSQL Procedure in Laravel 4 Migrations
  29. MySQL: Computed Column
  30. Mysql server does not support 4-byte encoded utf8 characters

Leave a Comment