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

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