将ElasticSearch的数据批量导入至MySQL

最新推荐文章于 2023-12-07 23:06:17 发布

原创

最新推荐文章于 2023-12-07 23:06:17 发布 · 1.4k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #elasticsearch

该博客介绍了如何将ElasticSearch中的数据批量导入到MySQL数据库。首先，通过引入相关依赖，创建ElasticSearch客户端并使用scroll API获取所有数据。然后，数据经过转换后，利用MySQL JDBC连接插入到指定表中。整个过程涉及ElasticSearch的搜索操作和MySQL的SQL执行，实现了数据迁移。

将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()) {

最低0.47元/天解锁文章