前置工作
- 当前服务器为CentOS6.5+ 64bit
elasticsearch与springboot已经正常运行,可以参考上一篇 SpringBoot集成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
wget http://xbib.org/repository/org/xbib/elasticsearch/importer/elasticsearch-jdbc/2.3.4.0/elasticsearch-jdbc-2.3.4.0-dist.zip
unzip elasticsearch-jdbc-2.3.4.0-dist.zip
安装dos2unix
yum 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参考地址
如有疑问请加公众号(K171),如果觉得对您有帮助请 github start