将ElasticSearch的数据批量导入至MySQL
导入Jar包
<dependency>
<groupId>org.elasticsearch</groupId>
<artifactId>elasticsearch</artifactId>
<version>7.14.0</version>
</dependency>
<dependency>
<groupId>org.elasticsearch.client</groupId>
<artifactId>elasticsearch-rest-high-level-client</artifactId>
<version>7.14.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.28</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.22</version>
</dependency>
Elastic的操作类
public class ElasticCustomClient {
RestHighLevelClient restHighLevelClient = null;
public void createClient() {
restHighLevelClient = new RestHighLevelClient(
RestClient.builder(new HttpHost("127.0.0.1", 9200),
new HttpHost("127.0.0.1", 9300),
new HttpHost("127.0.0.1", 9400))
);
}
public List<String> scrollData(String index) {
try {
createClient();
String scrollId = "";
List<String> result = new ArrayList<>();
SearchRequest searchRequest = new SearchRequest();
searchRequest.indices(index);
searchRequest.scroll("1m");
SearchResponse response = restHighLevelClient.search(searchRequest, RequestOptions.DEFAULT);
SearchHits hits = response.getHits();
for (int i = 0; i < hits.getHits().length; i++) {
result.add(hits.getHits()[i].getSourceAsString());
}
//记录滚动ID
scrollId = response.getScrollId();
while (true) {
if (scrollId == null || scrollId.equals("")) {
break;
}
SearchScrollRequest searchScrollRequest = new SearchScrollRequest(scrollId).scroll(TimeValue.timeValueMinutes(1));
response = restHighLevelClient.scroll(searchScrollRequest, RequestOptions.DEFAULT);
if (response != null && response.getHits().getHits().length > 0) {
for (SearchHit hit : response.getHits().getHits()) {
result.add(hit.getSourceAsString());
}
scrollId = response.getScrollId();
} else {
break;
}
}
//清除Scroll
ClearScrollRequest clearScrollRequest = new ClearScrollRequest();
clearScrollRequest.addScrollId(scrollId);
restHighLevelClient.clearScroll(clearScrollRequest, RequestOptions.DEFAULT);
return result;
} catch (Exception e) {
e.printStackTrace();
} finally {
closeClient();
}
return null;
}
public void closeClient() {
if (restHighLevelClient != null) {
try {
restHighLevelClient.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
数据转换
public class TransElasticToMySQL {
JdbcServiceMySQL jdbcServiceMySQL = new JdbcServiceMySQL();
public static void main(String[] args) {
TransElasticToMySQL transElasticToMySQL = new TransElasticToMySQL();
transElasticToMySQL.transData("people");
}
public void transData(String index) {
ElasticCustomClient elasticClient = new ElasticCustomClient();
Set<String> indexList = elasticClient.getAllIndices();
Connection connection = jdbcServiceMySQL.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=true&serverTimezone=GMT%2B8",
"root",
"root");
//获取所有数据
List<String> result = elasticClient.scrollData(s);
if (CollectionUtil.isNotEmpty(result)) {
for (String line : result) {
insertData(connection, line);
}
}
}
public void insertData(Connection connection, String lines) {
JSONObject jsonObject = JSONUtil.parseObj(lines);
String username = jsonObject.getStr("username");
String age = jsonObject.getStr("age");
String address = jsonObject.getStr("address");
String sql = String.format("insert into people(username,age,address) " +
" values('%s','%s','%s')",
username,
age,
address);
jdbcServiceMySQL.runDDL(connection, sql);
}
}
MySQL操作类
import lombok.extern.slf4j.Slf4j;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Slf4j
public class JdbcServiceMySQL {
/**
* 获取Connection
* @param url
* @param username
* @param password
* @return
*/
public Connection getConnection(String url, String username, String password) {
Connection connection = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
}
return connection;
}
/**
* 获取所有的数据库
*
* @param connection
* @return
* @throws Exception
*/
public List<String> getAllDataBase(Connection connection) {
List<String> result = new ArrayList<>();
ResultSet tables = null;
try {
DatabaseMetaData metaData = connection.getMetaData();
//检索数据库名称列表
tables = metaData.getCatalogs();
while (tables.next()) {
result.add(tables.getString("TABLE_CAT"));
}
} catch (Exception e) {
log.error("getAllDataBase cause error msg:{}", e.getMessage());
return result;
} finally {
close(tables, null);
}
return result;
}
/**
* 根据SQL获取一个统计值,不关闭Connection
*
* @param connection
* @param sql
* @return
* @throws Exception
*/
public long getOneLongResult(Connection connection, String sql) {
log.info("JdbcService.getOneLongResult execute sql : {}", sql);
long count = 0l;
ResultSet resultSet = null;
PreparedStatement preparedStatement = null;
try {
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
count = resultSet.getLong(1);
}
} catch (Exception e) {
log.error("getOneLongResult cause error msg:{}", e.getMessage());
return count;
} finally {
close(resultSet, preparedStatement);
}
return count;
}
public void runDDL(Connection connection, String sql) {
log.info("wait wait wait the sql is very important sql : {}",sql);
log.info("JdbcService.runDDL execute sql : {}", sql);
PreparedStatement preparedStatement = null;
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.execute();
} catch (Exception e) {
log.error("runDDL cause error msg:{}", e.getMessage());
} finally {
close(null, preparedStatement);
}
}
public List<Map<String, Object>> selectData(Connection connection, String sql) {
List<Map<String, Object>> list = new ArrayList<>();
log.info("this sql is :{}",sql);
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
final ResultSetMetaData metaData = resultSet.getMetaData();
final int columnCount = metaData.getColumnCount();
//遍历加入
while (resultSet.next()) {
Map<String, Object> map = new HashMap<>();
for (int i = 1; i <= columnCount; i++) {
map.put(metaData.getColumnLabel(i), resultSet.getObject(i));
}
list.add(map);
}
} catch (Exception e) {
log.error("selectData cause error msg:{}", e.getMessage());
return list;
} finally {
close(resultSet, preparedStatement);
}
return list;
}
public Map<String, Object> selectOneData(Connection connection, String sql) {
List<Map<String, Object>> list = new ArrayList<>();
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
final ResultSetMetaData metaData = resultSet.getMetaData();
final int columnCount = metaData.getColumnCount();
//遍历加入
while (resultSet.next()) {
Map<String, Object> map = new HashMap<>();
for (int i = 1; i <= columnCount; i++) {
map.put(metaData.getColumnLabel(i), resultSet.getObject(i));
}
list.add(map);
}
} catch (Exception e) {
log.error("selectData cause error msg:{}", e.getMessage());
return null;
} finally {
close(resultSet, preparedStatement);
}
if(list.size()>0){
Map<String, Object> map = list.get(0);
return map;
}
return null;
}
public void close(ResultSet resultSet, PreparedStatement preparedStatement) {
if (resultSet != null) {
try {
resultSet.close();
} catch (Exception e) {
log.error("close error : {}", e.getMessage());
}
}
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (Exception e) {
log.error("close error : {}", e.getMessage());
}
}
}
public void closeConnection(Connection connection){
if (connection != null) {
try {
connection.close();
} catch (Exception e) {
log.error("close error : {}", e.getMessage());
}
}
}
public List<String> getAllTable(Connection connection, String schemaName) {
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
List<String> result = new ArrayList<>();
try {
String sql = "SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA = '" + schemaName + "'";
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
result.add(resultSet.getString("TABLE_NAME"));
}
} catch (Exception e) {
log.error("JdbcServiceMySQL.getAllTable cause error msg:{}", e.getMessage());
return result;
} finally {
close(resultSet, preparedStatement);
}
return result;
}
/**
* 获取表的字段和注释
* @param connection
* @param table
* @return
*/
public Map<String,String> getAllColumnAndComment(Connection connection,String table) {
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
Map<String,String> result = new HashMap<>();
try {
String sql = "select COLUMN_NAME,DATA_TYPE,COLUMN_COMMENT,COLUMN_TYPE from information_schema.COLUMNS where table_name ='"+table+"'";
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
result.put(resultSet.getString("COLUMN_NAME"),resultSet.getString("COLUMN_COMMENT"));
}
} catch (Exception e) {
return result;
} finally {
close(resultSet, preparedStatement);
}
return result;
}
}
上述即可简单的将ElasticSearch索引里的数据传输到MySQL,其实代码也非常简单,就是ElasticSearch API通过scroll方式拿出所有的数据,然后解析数据插入到MySQL中,非常简单的代码。