将ElasticSearch的数据批量导入至MySQL

将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中，非常简单的代码。