Elasticsearch MySQL incremental sync three-table federated script

Source: Internet
Author: User
Tags kibana logstash

Download the appropriate dist package from the top plugin website and unzip it. Into the bin directory, you can see a bunch of sh scripts. Create a test.sh in the bin directory:

Bin=/home/csonezp/dev/elasticsearch-jdbc-2.3.1.0/bin
Lib=/home/csonezp/dev/elasticsearch-jdbc-2.3.1.0/lib
Echo ' {
"Type": "JDBC",
"Statefile": "Statefile.json",
"JDBC": {
"url": "Jdbc:mysql://myaddr",
"User": "MyUser",
"Password": "MyPwd",
"Type": "MyType",
"Index": "Myindex",
"Schedule": "0 * * * *?",
"Metrics": {"Enabled": True
},
"SQL": [{"Statement": "SELECT * from Gd_actor_info where update_time >?", "parameter": ["$metrics. Lastexecutionst Art "]}
]
}
} ' | Java \
-CP "${lib}/*" \
-dlog4j.configurationfile=${bin}/log4j2.xml \
Org.xbib.tools.Runner \
Org.xbib.tools.JDBCImporter

Schedule is now set to execute once every minute to facilitate observing behavior. Statefile This sentence must be added. $metrics. Lastexecutionstart is the key to this script, which refers to the last time the script was executed, and can be used to compare the time and the fields in the database to determine whether to update.

Elasticsearch MySQL incremental sync three-table federated script

The above briefly said about the ES synchronization data script, but the actual situation is certainly not as simple as the script in the previous article. For example, I now have three tables, two entity tables, and an association table. The approximate implementation is as follows:

The bin directory establishes a Statefile.json file:

{
"Type": "JDBC",
"Statefile": "Statefile.json",
"JDBC": {
"url": "jdbc:mysql://",
"User": "",
"Password": "",
"Type": "Actor",
"Index": "Test",
"Schedule": "0 * * * *?",
"Metrics": {
"Lastexecutionstart": "0",
"Lastexecutionend": "0",
"Counter": "1"
},
"SQL": [{"Statement": "Select a.actor_id as _id, A.*,group_concat (b.tag_name) as Tag_name from ((Gd_actor_info as a Left join Gd_actor_tag as AB in a.actor_id = ab.actor_id) left join Gd_tag_actor as B on ab.tag_id = b.tag_id) where a.up Date_time;? or Ab.update_time >? GROUP by a.actor_id "," parameter ": [" $metrics. Lastexecutionstart "," $metrics. Lastexecutionstart "]}
]
}
}

The main lastexecutionstart is set to 0, in order for the first execution to be able to perform a full-scale backup.

In fact, the SH script information is on the top, and then write a good

ElasticSearch Latest Version 2.20 released downloads http://www.linuxidc.com/Linux/2016-02/128166.htm

Install deployment Elasticsearch full record on Linux http://www.codesec.net/Linux/2015-09/123241.htm

Elasticsearch Installation using tutorial http://www.codesec.net/Linux/2015-02/113615.htm

ElasticSearch configuration file Translation parsing http://www.codesec.net/Linux/2015-02/114244.htm

Elasticsearch Cluster Building Example http://www.codesec.net/Linux/2015-02/114243.htm

Distributed search Elasticsearch stand-alone and server environment construction http://www.codesec.net/Linux/2012-05/60787.htm

The working mechanism of Elasticsearch http://www.codesec.net/Linux/2014-11/109922.htm

Elasticsearch installation, operation and basic configuration http://www.codesec.net/Linux/2016-07/2881064151.htm

Use Elasticsearch + Logstash + Kibana to build a log centralized analytics platform Practice http://www.codesec.net/Linux/2015-12/126587.htm

Ubuntu 14.04 Build Elk Log Analysis System (Elasticsearch+logstash+kibana) http://www.codesec.net/Linux/2016-06/132618.htm

Elasticsearch1.7 upgrade to 2.3 practice summary http://www.codesec.net/Linux/2016-11/137282.htm

Elasticsearch MySQL incremental synchronization Three-table union script

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.