spring boot es mysql_SpringBoot -- ES从mysql中获取数据/Elasticsearch-jdbc

前置工作当前服务器为CentOS6.5+ 64bit

elasticsearch与springboot已经正常运行，可以参考上一篇 SpringBoot集成ES

获取Elasticsearch-jdbc以及support ES版本

获取地址为：http://xbib.org/repository/org/xbib/elasticsearch/importer/elasticsearch-jdbc/2.3.4.0/elasticsearch-jdbc-2.3.4.0-dist.zip

如果是在release里获取的版本，则没有 lib目录，在后面的脚本运行中会报错

服务器安装 dos2unix

编写shell脚本后上传服务器，如果不进行转换则会出现各种错误

安装Elasticsearch-jdbc

安装dos2unixyum install dos2unix

创建shell脚本

全量获取数据

mysql_accountinfo.sh#!/bin/sh

DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"

bin=${DIR}/../bin

lib=${DIR}/../lib

echo '{

"type":"jdbc",

"jdbc":{

"url":"jdbc:mysql://localhost:3306/kakme",

"user":"root",

"password":"123456",

"sql":"select id as _id , account_name as accountName , nick_name nickName from account_info",

"elasticsearch" : {

"cluster" : "nini",

"host" : "localhost",

"port" : 9300

},

"index":"cwenao",

"type":"accountinfo",

"type_mapping" :{

"account_info": {

"properties": {

"id":{

"type":"string",

"index":"not_analyzed"

},

"accountName":{

"type":"string"

},

"nickName":{

"type":"string"

}

}

}

}

}

}' | java \

-cp "${lib}/*" \

-Dlog4j.configurationFile=${bin}/log4j2.xml \

org.xbib.tools.Runner \

org.xbib.tools.JDBCImporter

上传服务器并用dos2unix进行格式转换运行前需要更改权限

chmod 700 mysql_accountinfo.sh

dos2unix mysql_accountinfo.sh

chmod 700 mysql_accountinfo.sh

sh mysql_accountinfo.sh

查看导入结果curl -XGET 'http://localhost:9200/cwenao/accountinfo/_search?pretty'

查询结果

{

"took" : 3,

"timed_out" : false,

"_shards" : {

"total" : 5,

"successful" : 5,

"failed" : 0

},

"hits" : {

"total" : 2,

"max_score" : 1.0,

"hits" : [ {

"_index" : "cwenao",

"_type" : "accountinfo",

"_id" : "a63126d074f04db587cd76a48c817509",

"_score" : 1.0,

"_source" : {

"accountName" : "cwenao",

"nickName" : null

}

}, {

"_index" : "cwenao",

"_type" : "accountinfo",

"_id" : "a63126d074f04db587cd76a48c817510",

"_score" : 1.0,

"_source" : {

"accountName" : "nini",

"nickName" : "啦啦啦啦啦啦啦阿里"

}

} ]

}

}

增量数据增加增量条件存储文件： statefile

执行时间： schedule

增量查询sql

mysql_accountinfo_time.sh

#!/bin/sh

DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"

bin=${DIR}/../bin

lib=${DIR}/../lib

echo '{

"type":"jdbc",

"jdbc":{

"url":"jdbc:mysql://localhost:3306/kakme",

"user":"root",

"password":"123456",

"statefile":"statefile_account.json",

"schedule" : "0 0-59 0-23 ? * *",

"sql": [{

"statement":"select id as _id , account_name as accountName , nick_name nickName from account_info where update_time > ?",

"parameter" : ["$metrics.lastexecutionstart"]

}],

"elasticsearch" : {

"cluster" : "nini",

"host" : "localhost",

"port" : 9300

},

"index":"cwenao",

"type":"accountinfo",

"type_mapping" :{

"account_info": {

"properties": {

"id":{

"type":"string",

"index":"not_analyzed"

},

"accountName":{

"type":"string"

},

"nickName":{

"type":"string"

}

}

}

}

}

}' | java \

-cp "${lib}/*" \

-Dlog4j.configurationFile=${bin}/log4j2.xml \

org.xbib.tools.Runner \

org.xbib.tools.JDBCImporter

上传服务器并用dos2unix进行格式转换运行前需要更改权限

chmod 700 mysql_accountinfo.sh

dos2unix mysql_accountinfo_time.sh

chmod 700 mysql_accountinfo_time.sh

sh mysql_accountinfo_time.sh

数据库插入数据INSERT INTO `kakme`.`account_info` (

`id`,

`account_name`,

`nick_name`,

`mail`,

`m_tel`,

`land_tel`,

`is_vip`,

`password`,

`salt`,

`head_image`,

`back_image`,

`account_type`,

`source`,

`audit_status`,

`create_time`,

`update_time`,

`status`

)

VALUES

(

'a63126d074f04db587cd76a48c817512',

'jintiantianq',

'你说天气如何',

NULL,

NULL,

NULL,

NULL,

'12b66f880fddbbabf279e64076e288fc',

'112233',

NULL,

NULL,

NULL,

NULL,

NULL,

'2017-02-09 11:32:14',

'2017-02-09 11:32:14',

'1'

);

查看导入结果curl -XGET 'http://localhost:9200/cwenao/accountinfo/_search?pretty'

{

"took" : 3,

"timed_out" : false,

"_shards" : {

"total" : 5,

"successful" : 5,

"failed" : 0

},

"hits" : {

"total" : 2,

"max_score" : 1.0,

"hits" : [ {

"_index" : "cwenao",

"_type" : "accountinfo",

"_id" : "a63126d074f04db587cd76a48c817509",

"_score" : 1.0,

"_source" : {

"accountName" : "cwenao",

"nickName" : null

}

}, {

"_index" : "cwenao",

"_type" : "accountinfo",

"_id" : "a63126d074f04db587cd76a48c817510",

"_score" : 1.0,

"_source" : {

"accountName" : "nini",

"nickName" : "啦啦啦啦啦啦啦阿里"

}

} ]

}

}

代码github start