106 基于消息队列来做 mysql 大数据表数据的遍历处理

前言

最近有这样的一个需求, 我们存在一张 很大的 mysql 数据表, 数据量大概是在 六百万左右 

然后 需要获取所有的记录, 将数据传输到 es 中 

然后 当时 我就写了一个脚本来读取 这张大表, 然后 分页获取数据, 然后 按页进行数据处理 转换到 es 

但是存在的问题是, 前面 还效率还可以, 但是 约到后面, 大概是到 三百多页, 的时候 从 mysql 读取数据 已经快不行了 

十分耗时, 这里就是 记录这个问题的 另外的处理方式 

我这里的处理是基于 消息中间件, 从 mysql 通过 datax/spoon 传输数据到 kafka 很快 

然后  java 程序从 kafka 中消费队列的数据 也很快, 最终 六百万的数据 读取 + 处理 合计差不多是 一个多小时完成, 其中处理 有一部分地方 业务上面比较耗时 

 

 

待处理的数据表

待处理的数据表如下, 里面合计 600w 的数据 

CREATE TABLE `student_all` (
  `id` int NOT NULL AUTO_INCREMENT,
  `field0` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `field1` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `field2` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `field3` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `field4` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `field5` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `field6` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `field7` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `field8` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `field9` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `field10` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `field11` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `field12` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `field13` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `field14` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `field15` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `field16` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `field17` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `field18` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `field19` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `field20` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `field21` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `field22` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `field23` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `field24` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `field25` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `field26` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `field27` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `field28` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `field29` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `CREATED_AT` bigint NOT NULL,
  `UPDATED_AT` bigint NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4379001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

 

 

基于 mysql 的数据分页处理

基于 mysql 的处理程序如下, 就是一个简单的 mysql 分页 

然后将需要提取的数据封装, 然后 批量提交给 es 

总的情况来说是 前面的一部分页是可以 很快的响应数据, 但是 越到后面, mysql 服务器越慢 

/**
 * Test05PostQy2Es
 *
 * @author Jerry.X.He
 * @version 1.0
 * @date 2022/11/21 16:00
 */
public class Test05PostEsFromMysql {

    private static String mysqlUrl = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&autoReconnectForPools=true";
    private static String mysqlUsername = "postgres";
    private static String mysqlPassword = "postgres";
    private static JdbcTemplate mysqlJdbcTemplate = JdbcTemplateUtils.getJdbcTemplate(mysqlUrl, mysqlUsername, mysqlPassword);

    private static RestHighLevelClient esClient = getEsClient();
    private static IndicesClient indicesClient = esClient.indices();

    // Test05PostQy2Es
    public static void main(String[] args) throws Exception {

        String esIndexName = "student_all_20221211";
        bulkEsData(esIndexName);

    }

    private static void bulkEsData(String esIndexName) throws Exception {
        String queryDbTableName = "student_all";
        List<String> fieldList = Arrays.asList("id", "field0", "field1", "field2", "field3", "field4", "field5", "field6", "field7", "field8", "field9", "field10", "field11", "field12", "field13", "field14", "field15", "field16", "field17", "field18", "field19", "field20", "field21", "field22", "field23", "field24", "field25", "field26", "field27", "field28", "field29", "CREATED_AT", "UPDATED_AT");

        String idKey = "id";
        String whereCond = "";
//        String orderBy = "order by id asc";
        String orderBy = "";
        AtomicInteger counter = new AtomicInteger(0);
        int pageSize = 1000;
        int startPage = 0;
        pageDo(queryDbTableName, whereCond, orderBy, pageSize, startPage, (pageNo, list) -> {
            BulkRequest bulkRequest = new BulkRequest();
            for (Map<String, Object> entity : list) {
                IndexRequest indexRequest = new IndexRequest(esIndexName);
                Map<String, Object> sourceMap = new LinkedHashMap<>();
                List<String> allFieldsListed = new ArrayList<>();
                for (String fieldName : fieldList) {
                    String fieldValue = String.valueOf(entity.get(fieldName));
                    sourceMap.put(fieldName, fieldValue);
                    allFieldsListed.add(Objects.toString(fieldValue, ""));
                }
                String id = String.valueOf(entity.get(idKey));
                indexRequest.id(id);
                sourceMap.put("_allFields", StringUtils.join(allFieldsListed, "$$"));

                indexRequest.source(sourceMap);
                bulkRequest.add(indexRequest);
            }

            try {
                BulkResponse bulkResponse = esClient.bulk(bulkRequest, RequestOptions.DEFAULT);
                counter.addAndGet(list.size());
            } catch (Exception e) {
                e.printStackTrace();
            }
            System.out.println(" page : " + pageNo + ", flushed " + counter.get() + " records ");
        });
    }

    private static void pageDo(String tableName, String whereCond, String orderBy, int pageSize, int startPage,
                               BiConsumer<Integer, List<Map<String, Object>>> func) {
        if (StringUtils.isNotBlank(whereCond) && (!whereCond.trim().toLowerCase().startsWith("where"))) {
            whereCond = " where " + whereCond;
        }
        if (StringUtils.isNotBlank(orderBy) && (!orderBy.trim().toLowerCase().startsWith("order"))) {
            orderBy = " order by " + orderBy;
        }

        String queryCountSql = String.format(" select count(*) from %s %s %s", tableName, whereCond, orderBy);
        Integer totalCount = mysqlJdbcTemplate.queryForObject(queryCountSql, Integer.class);
        Integer totalPage = (totalCount == null || totalCount == 0) ? 0 : (totalCount - 1) / pageSize + 1;
        for (int i = startPage; i < totalPage; i++) {
            int offset = i * pageSize;
            String queryPageSql = String.format(" select * from %s %s %s limit %s,%s ", tableName, whereCond, orderBy, offset, pageSize);
            List<Map<String, Object>> list = mysqlJdbcTemplate.queryForList(queryPageSql);
            func.accept(i, list);
        }
    }

}

 

 

基于中间件 kafka 的处理

首先通过 spoon/datax 将数据从 mysql 转换到 kafka 

然后 再由脚本从 kafka 消费数据, 处理 传输到 es 中 

入了一次 消息队列之后, 然后程序 再来消费, 就会快很多了, 消息队列本身功能比较单纯 比较适合于做做顺序遍历 就会有优势一些 

 

这里以 spoon 将数据从 mysql 转换到 kafka 

我这里 本地环境 内存等什么的都不足, 因此是 一分钟 入库三万条, 但是 实际生产环境 会很快 

在生产环境 五百多w 的数据, 基于 datax 传输 mysql 到 kafka, 差不多是 五六分钟 就可以了 

e3cb2b641cfe4d208e11040f1b5fbc2a.png

 

 

基于 kafka 将数据传输到 es 

如下程序 仅仅是将 kafka 中的数据 原样照搬过去了, 但是 实际的场景 中会做一些 额外的业务处理, 这里仅仅是为了 演示 

/**
 * Test05PostQy2Es
 *
 * @author Jerry.X.He
 * @version 1.0
 * @date 2022/11/21 16:00
 */
public class Test05PostEsFromKafka {

    private static RestHighLevelClient esClient = getEsClient();
    private static IndicesClient indicesClient = esClient.indices();
    private static String esIndexName = "student_all_20221211";
    private static String groupId = "group-01";

    // Test05PostQy2Es
    public static void main(String[] args) throws Exception {

        bulkKafka2EsData(esIndexName, groupId);

    }

    private static void bulkKafka2EsData(String esIndexName, String groupId) throws Exception {
        List<Pair<String, String>> hjk2StdFieldMap = hjk2StdFieldMap();
        Properties properties = kafkaProperties(groupId);

        String idKey = "ID";
        KafkaConsumer<String, String> kafkaConsumer = new KafkaConsumer<>(properties);
        kafkaConsumer.subscribe(Arrays.asList("STUDENT_ALL_20221211"));
        AtomicInteger counter = new AtomicInteger(0);
        long start = System.currentTimeMillis();
        while (true) {
            ConsumerRecords<String, String> records = kafkaConsumer.poll(100);
            if (records.isEmpty()) {
                Thread.sleep(10 * 1000);
                long spent = System.currentTimeMillis() - start;
                System.out.println(" spent : " + (spent / 1000) + " s ");
                continue;
            }

            BulkRequest bulkRequest = new BulkRequest();
            boolean isEmpty = true;
            for (ConsumerRecord<String, String> record : records) {
                IndexRequest indexRequest = new IndexRequest(esIndexName);
                String value = record.value();
                JSONObject entity = JSON.parseObject(value);

                // 获取 id
                String id = StringUtils.defaultIfBlank(entity.getString(idKey), "");
                if (isFilterByQy(id)) {
                    continue;
                }

                Map<String, Object> sourceMap = new LinkedHashMap<>();
                List<String> allFieldsListed = new ArrayList<>();
                for (Pair<String, String> entry : hjk2StdFieldMap) {
                    String hjkKey = entry.getKey(), stdKey = entry.getValue();
                    String fieldValue = StringUtils.defaultIfBlank(entity.getString(hjkKey), "");
                    sourceMap.put(stdKey, fieldValue);
                    allFieldsListed.add(Objects.toString(fieldValue, ""));
                }
                indexRequest.id(id);
                sourceMap.put("_allFields", StringUtils.join(allFieldsListed, "$$"));

                isEmpty = false;
                indexRequest.source(sourceMap);
                bulkRequest.add(indexRequest);
            }
            if (isEmpty) {
                continue;
            }

            try {
                BulkResponse bulkResponse = esClient.bulk(bulkRequest, RequestOptions.DEFAULT);
                counter.addAndGet(bulkRequest.requests().size());
            } catch (Exception e) {
                e.printStackTrace();
            }
            System.out.println(" flushed " + counter.get() + " records ");
        }

    }

    private static List<Pair<String, String>> hjk2StdFieldMap() {
        List<Pair<String, String>> hjk2StdFieldMap = new ArrayList<>();
        hjk2StdFieldMap.add(new ImmutablePair<>("id", "id"));
        hjk2StdFieldMap.add(new ImmutablePair<>("CREATED_AT", "CREATED_AT"));
        hjk2StdFieldMap.add(new ImmutablePair<>("UPDATED_AT", "UPDATED_AT"));
        for (int i = 0; i < Test05CreateMysqlBigTable.maxFieldIdx; i++) {
            String fieldName = String.format("field%s", i);
            hjk2StdFieldMap.add(new ImmutablePair<>(fieldName, fieldName));
        }
        return hjk2StdFieldMap;
    }

    private static Properties kafkaProperties(String groupId) {
        Properties properties = new Properties();
        properties.put("bootstrap.servers", "192.168.0.190:9092");
        properties.put("group.id", groupId);
        properties.put("enable.auto.commit", "true");
        properties.put("auto.commit.interval.ms", "1000");
        properties.put("auto.offset.reset", "earliest");
        properties.put("session.timeout.ms", "30000");
        properties.put("key.serializer", "org.apache.kafka.common.serialization.StringSerializer");
        properties.put("value.serializer", "org.apache.kafka.common.serialization.StringSerializer");
        properties.put("key.deserializer", "org.apache.kafka.common.serialization.StringDeserializer");
        properties.put("value.deserializer", "org.apache.kafka.common.serialization.StringDeserializer");
        return properties;
    }

    private static boolean isFilterByQy(String qy) {
        if (StringUtils.isBlank(qy)) {
            return true;
        }

        return false;
    }

}

 

 

spoon 安装 kakfa 插件

来自 Kettle安装Kafka Consumer和Kafka Producer插件

    1.从github上下载kettle的kafka插件，地址如下
    Kafka Consumer地址：
    https://github.com/RuckusWirelessIL/pentaho-kafka-consumer/releases/tag/v1.7
    Kafka Producer地址：
    https://github.com/RuckusWirelessIL/pentaho-kafka-producer/releases/tag/v1.9
    2.进入 kettle 安装目录：在plugin目录下创建steps目录
    3.把下载的插件解压后放到 steps 目录下
    5.重启 spoon.bat 即可

 

 

完

 

 

参考

Kettle安装Kafka Consumer和Kafka Producer插件

 

 

 

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.mfbz.cn/a/469288.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

【Micropython ESP32】pwm脉宽调制技术

【Micropython ESP32】pwm脉宽调制技术

文章目录 前言一、PWM脉宽调制技术介绍二、machine.PWM 类2.1 machine.PWM 类的构造对象2.2 PWM 对象初始化2.3 关闭PWM设备2.4 设置pwm的周期2.5 设置占空比 三、pwm示例代码总结 前言 在嵌入式系统和物联网应用中&#xff0c;控制电机、LED灯和其他设备的亮度或速度是常见的…
阅读更多...
汽车电子零部件（8）：T_Box

汽车电子零部件（8）：T_Box

前言： 网联汽车（Connected Vehicles ，CV）是一个广泛的概念，四个主要的CV线程已发展起来：互联、自主、共享和电动。这些应用于包括CV在内的垂直领域：汽车、通信、互联网和共享手机服务。中国汽车工程师学会（SAEC）提倡将车载ADAS（高级驾驶员辅助系统）与通信技术相结合…
阅读更多...
基于单片机的模糊PID炉温控制系统设计

基于单片机的模糊PID炉温控制系统设计

摘 要 电热炉是在工业热处理的生产中广泛使用的一种设备&#xff0c;电热炉的温度控制系统存在时变性&#xff0c;非线性&#xff0c;滞后性等特征&#xff0c;难以用常规PID的控制器对系统达到很好的控制效果。当控温精度的要求高时&#xff0c;使用传统的控制理论方法难以达…
阅读更多...
医药工厂5G智能制造数字孪生可视化平台，推进医药企业数字化转型

医药工厂5G智能制造数字孪生可视化平台，推进医药企业数字化转型

医药工厂5G智能制造数字孪生可视化平台&#xff0c;推进医药企业数字化转型。随着科技的不断发展&#xff0c;数字化转型已成为医药企业不可或缺的一部分。5G智能制造医药工厂数字孪生可视化平台作为数字化转型的重要工具&#xff0c;正在逐步改变医药企业的生产方式和管理模式…
阅读更多...
信息论相关知识简单整理

信息论相关知识简单整理

信息论相关知识 互信息 互信息(Mutual Information)是信息论里一种有用的信息度量&#xff0c;它可以看成是一个随机变量中包含的关于另一个随机变量的信息量&#xff0c;或者说是一个随机变量由于已知另一个随机变量而减少的不肯定性。 设两个随机变量&#xff08;X,Y)的联合…
阅读更多...
嵌入式驱动学习第四周——platform总线

嵌入式驱动学习第四周——platform总线

前言 platform是Linux内核抽象出来的软件代码&#xff0c;用于设备与驱动的连接&#xff0c;设备与驱动通过总线进行匹配&#xff1b;匹配成功后会执行驱动中的probe函数&#xff0c;在probe函数中可以获取到设备的信息&#xff1b; 嵌入式驱动学习专栏将详细记录博主学习驱动的…
阅读更多...
3月份后半月比赛总结

3月份后半月比赛总结

VCTF web hackjs 就直接给了源码审计 const express require(express) const fs require(fs) var bodyParser require(body-parser); const app express() app.use(bodyParser.urlencoded({extended: true })); app.use(bodyParser.json());app.post(/plz, (req, res) …
阅读更多...
VR历史建筑漫游介绍|虚拟现实体验店|VR设备购买

VR历史建筑漫游介绍|虚拟现实体验店|VR设备购买

VR历史建筑漫游是一种利用虚拟现实技术&#xff0c;让用户可以身临其境地参观和探索历史建筑的体验。通过VR头显和相关设备&#xff0c;用户可以在虚拟环境中自由移动和互动&#xff0c;感受历史建筑的真实氛围和文化内涵。 在VR历史建筑漫游中&#xff0c;您可以选择不同的历史…
阅读更多...
Android 之 GMS 认证知多少？

Android 之 GMS 认证知多少？

GMS认证 1.什么是GMS GMS全称Google Mobile Service&#xff0c;谷歌移动服务。 为什么要通过GMS认证 Android 系统是开源的&#xff0c;但是 Google 针对GMS所提供的服务却是收费的&#xff0c;比如Google Map&#xff0c;Google Play&#xff0c;Youtube&#xff0c;Gmai…
阅读更多...
堆（数据结构）

堆（数据结构）

堆的概念及结构 如果有一个关键码的集合K { &#xff0c; &#xff0c; &#xff0c;…&#xff0c; }&#xff0c;把它的所有元素按完全二叉树的顺序存储方式存储在一个一维数组中&#xff0c;并满足&#xff1a; < 且 < ( > 且 > ) i 0&#xff0c;1&#xff…
阅读更多...
Python爬虫案例-爬取主题图片（可以选择自己喜欢的主题）

Python爬虫案例-爬取主题图片（可以选择自己喜欢的主题）

2024年了&#xff0c;你需要网络资源不能还自己再慢慢找吧&#xff1f; 跟着博主一块学习如何利用爬虫获取资源&#xff0c;从茫茫大海中寻找那个她到再妹子群中找妹子&#xff0c;闭着眼睛都可以找到合适的那种。文章有完整示例代码&#xff0c;拿过来就可以用&#xff0c;欢迎…
阅读更多...
【C语言】数据在内存中的存储(包含大小端字节序问题)~

【C语言】数据在内存中的存储(包含大小端字节序问题)~

一、前言 我们在刚开始学习C语言的时候&#xff0c;就接触到了很多数据的不同类型。我们也知道&#xff0c;数据是存储在一块内存空间的&#xff0c;且我们只知道数据的类型决定着&#xff0c;该数据在内存中所占内存空间的大小&#xff0c;且超过一个字节的数据在内存中存储的…
阅读更多...
【项目实践Day06】异步请求与同步请求+Ajax+微信小程序上实现发送异步请求

【项目实践Day06】异步请求与同步请求+Ajax+微信小程序上实现发送异步请求

什么是同步和异步 同步 在主线程上排队执行的任务&#xff0c;只有前一个任务执行完毕&#xff0c;才能继续执行下一个任务。也就是一旦调用开始&#xff0c;就必须等待其返回结果&#xff0c;程序的执行顺序和任务排列顺序一致。客户端必须等待服务器端的响应。在等待的期间客…
阅读更多...
Android源码阅读 SharedPreferences - 1

Android源码阅读 SharedPreferences - 1

目录 前言 正文 SharedPreferences.java PreferenceManager.java ContextImpl.java 前言 由于笔者目前水平限制&#xff0c;表达能力有限&#xff0c;尽请见谅。 SharedPreferences提供了一种轻量级的数据存储方式&#xff0c;允许保存和获取简单的键值对。它适用于保存少…
阅读更多...
转座子插入序列分析1-GENE-IS分析管道

转座子插入序列分析1-GENE-IS分析管道

如果你使用 GENE-IS: Saira Afzal et al。 &#xff0c;2016请引用这篇研究文章。GENE-IS: time-efficient and accurate analysis of viral integration events in large-scale gene therapy data. Molecular Therapy - Nucleic Acids 2016, vol. 6:133-139. DOI:https://doi.…
阅读更多...
做好外贸网站SEO优化,拓展海外市场

做好外贸网站SEO优化,拓展海外市场

随着全球贸易的发展和互联网的普及&#xff0c;越来越多的外贸企业将目光投向了网络&#xff0c;希望通过建立网站来拓展海外市场。然而&#xff0c;在竞争激烈的外贸市场中&#xff0c;要让自己的网站脱颖而出&#xff0c;吸引更多的目标客户&#xff0c;就需要进行有效的SEO优…
阅读更多...
StarRocks 记录

StarRocks 记录

《实时数仓StarRocks集群部署》
阅读更多...
提升Spring Boot应用性能的秘密武器：揭秘@Async注解的实用技巧

提升Spring Boot应用性能的秘密武器：揭秘@Async注解的实用技巧

引言 在日常业务开发中&#xff0c;异步编程已成为应对并发挑战和提升应用程序性能的关键策略。传统的同步编程方式&#xff0c;由于会阻碍主线程执行后续任务直至程序代码执行结束&#xff0c;不可避免地降低了程序整体效率与响应速度。因此&#xff0c;为克服这一瓶颈&#…
阅读更多...
win11环境安装VmwareLinux

win11环境安装VmwareLinux

VMware 安装Vmware 操作系统&#xff1a; win11 VM版本&#xff1a; 重启系统 输入许可证秘钥 安装centos finalshell连接linux服务 配置虚拟机运行状态 查询linux服务器的ip地址 下载finalshell 访问FinalShell官网 (hostbuf.com)
阅读更多...
Spring6入门到高级-动力节点老杜

Spring6入门到高级-动力节点老杜

文章目录 OCP开闭原则依赖倒置原则控制反转依赖注入DISet方法注入构造注入 Sping特点代理模式代理模式中的角色动态代理JDK动态代理newProxyInstance() 的三个参数 JDK实现代理的步骤第一步&#xff1a;创建目标对象第二步&#xff1a;创建代理对象第三步&#xff1a;调用代理对…
阅读更多...
最新文章

Copyright @ 2022~2023

友情链接： 我的编程经验分享 一条长河网 尧图网站开发 尧图建网站