Mysql 数据同步 ClickHouse 亿级数据10分钟内搞定

1、安装ClickHouse

docker run -d --name my-clickhouse \
-p 8123:8123 \
-p 9000:9000 \
-p 9009:9009 \
-v clickhouse_data:/var/lib/clickhouse \
-v clickhouse_log:/var/log/clickhouse-server \
-v clickhouse_config:/etc/clickhouse-server \
--ulimit nofile=262144:262144 \
-e CLICKHOUSE_USER=ckuser \
-e CLICKHOUSE_PASSWORD="ClickHouse123!" \
-e CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT=1 \
--restart unless-stopped \
clickhouse/clickhouse-server:latest

安装完成之后：

1、浏览器访问http://localhost:8123/play

2、点击右上角Connection setting

3、输入用户名ckuser及密码ClickHouse123!
【就能执行sql了】

2、建表

CREATE TABLE company_info
(
    id                      Int64 COMMENT '企业ID',
    it_code                 String COMMENT '标识',
    company_name            String COMMENT '公司名称',
    e_name                  String COMMENT '英文名称',
    prev_name               String COMMENT '曾用名',
    group_name              String COMMENT '集团名称',
    credit_code             String COMMENT '统一社会信用代码',
    org_number              String COMMENT '组织机构代码',
    reg_number              String COMMENT '工商注册号',
    taxpayer_id             String COMMENT '纳税人识别号',
    legal_person_name       String COMMENT '法定代表人',
    reg_capital             Decimal(20,2) COMMENT '注册资本',
    reg_capital_unit        String COMMENT '注册资本单位',
    reg_capital_str         String COMMENT '注册资本',
    registrar_code          String COMMENT '登记机关代码',
    estiblish_time          DateTime COMMENT '成立日期',
    issue_date              DateTime COMMENT '核准日期',
    business_start          String COMMENT '营业期限',
    organization            String COMMENT '组织形式',
    company_org_type        String COMMENT '公司类型',
    industry_mc_code        String COMMENT '行业门类代码',
    industry_mc             String COMMENT '行业门类',
    industry_sc_code        String COMMENT '行业大类代码',
    industry_sc             String COMMENT '行业大类',
    industry_tc_code        String COMMENT '行业中类代码',
    industry_tc             String COMMENT '行业中类',
    industry_fc_code        String COMMENT '行业小类代码',
    industry_fc             String COMMENT '行业小类',
    ind_category            String COMMENT '所属类别',
    ind_sub_category        String COMMENT '所属子类',
    reg_location            String COMMENT '注册地址',
    province                String COMMENT '省份',
    province_code           String COMMENT '省份代码',
    city                    String COMMENT '城市',
    city_code               String COMMENT '城市代码',
    region                  String COMMENT '区县',
    region_code             String COMMENT '区县编码',
    reg_authority           String COMMENT '登记机关',
    business_scope          String COMMENT '经营范围',
    company_scale           Int8 COMMENT '企业类型',
    status                  String COMMENT '经营状态',
    status_code             String COMMENT '经营状态代码',
    website                 String COMMENT '公司官网',
    bank_top_name           String,
    bank_type               String COMMENT '金融机构类型',
    bank_type2              String COMMENT '银行细分类型',
    postal_code             String COMMENT '邮政编码',
    contact_phone_number    String COMMENT '联系电话',
    other_phone_number      String COMMENT '其它联系电话',
    contact_address         String COMMENT '联系地址',
    e_mail                  String COMMENT '电子邮箱',
    other_mail              String COMMENT '其它邮箱',
    participate_ssn         String COMMENT '参加社保人数',
    currency_code           String COMMENT '货币代码',
    create_time             DateTime COMMENT '创建时间',
    update_time             DateTime COMMENT '更新时间',
    del_flag                Int8 COMMENT '是否删除',
    remark                  String COMMENT '说明',
    lat                     Float64 COMMENT '维度',
    lng                     Float64 COMMENT '经度',
    location                String COMMENT '位置信息',
    type_flag               UInt64 COMMENT '分类编码（位掩码：最多64位）',

    _version                UInt64 MATERIALIZED toUInt64(now())
)
ENGINE = ReplacingMergeTree(_version)
PARTITION BY tuple()
ORDER BY (id)
PRIMARY KEY (id)
COMMENT '企业信息表';

3、同步数据

依赖

        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>com.clickhouse</groupId>
            <artifactId>clickhouse-jdbc</artifactId>
            <version>0.9.8</version>
            <classifier>all</classifier>
        </dependency>
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
        </dependency>

同步代码

package com.faster.admin.bigData;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.*;

@SpringBootTest
public class Mysql2ClickHouseTest {

    private static HikariDataSource mysqlDs;
    private static HikariDataSource chDs;

    private static final int BATCH_SIZE = 8000;

    // 表独立断点文件
    private static final String BREAKPOINT_COMPANY = "breakpoint_company.txt";

    // ========== 配置 ==========
    private static final String MYSQL_URL = "jdbc:mysql://192.168.10.124:3306/arfdata?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull";
    private static final String MYSQL_USER = "root";
    private static final String MYSQL_PWD = "root";

    private static final String CK_URL = "jdbc:clickhouse:http://192.168.21.197:8123/default?use_time_zone=Asia/Shanghai";
    private static final String CK_USER = "ckuser";
    private static final String CK_PWD = "ClickHouse123!";

    public static void initDataSource() {
        HikariConfig mysqlCfg = new HikariConfig();
        mysqlCfg.setJdbcUrl(MYSQL_URL);
        mysqlCfg.setUsername(MYSQL_USER);
        mysqlCfg.setPassword(MYSQL_PWD);
        mysqlCfg.setMaximumPoolSize(4);
        mysqlDs = new HikariDataSource(mysqlCfg);

        HikariConfig chCfg = new HikariConfig();
        chCfg.setJdbcUrl(CK_URL);
        chCfg.setUsername(CK_USER);
        chCfg.setPassword(CK_PWD);
        chCfg.setDriverClassName("com.clickhouse.jdbc.ClickHouseDriver");
        chCfg.setMaximumPoolSize(2);
        chDs = new HikariDataSource(chCfg);
    }

    public static long readBreakpoint(String filePath) {
        File f = new File(filePath);
        if (!f.exists()) return 0L;
        try (FileInputStream fis = new FileInputStream(f)) {
            byte[] buf = new byte[64];
            int len = fis.read(buf);
            if (len <= 0) return 0L;
            return Long.parseLong(new String(buf, 0, len).trim());
        } catch (Exception e) {
            System.err.println("读取断点失败 file:" + filePath + "，从0开始");
            return 0L;
        }
    }

    public static void saveBreakpoint(String filePath, long maxId) {
        try (FileOutputStream fos = new FileOutputStream(filePath)) {
            fos.write(String.valueOf(maxId).getBytes());
        } catch (IOException e) {
            System.err.println("保存断点失败 file=" + filePath + " maxId=" + maxId + " err=" + e.getMessage());
        }
    }



    /**
     *  company_info
     * CK _version MATERIALIZED，插入不传
     */
    public static void syncArfCompanyInfo(long startId) throws SQLException {
        String mysqlSelect = "SELECT id,it_code,company_name,e_name,prev_name,group_name,credit_code,org_number,reg_number,taxpayer_id," +
                "legal_person_name,reg_capital,reg_capital_unit,reg_capital_str,registrar_code,estiblish_time,issue_date,business_start," +
                "organization,company_org_type,industry_mc_code,industry_mc,industry_sc_code,industry_sc,industry_tc_code,industry_tc," +
                "industry_fc_code,industry_fc,ind_category,ind_sub_category,reg_location,province,province_code,city,city_code,region," +
                "region_code,reg_authority,business_scope,company_scale,status,status_code,website,bank_top_name,bank_type,bank_type2," +
                "postal_code,contact_phone_number,other_phone_number,contact_address,e_mail,other_mail,participate_ssn,currency_code," +
                "create_time,update_time,del_flag,remark,lat,lng,location,type_flag   " +
                "FROM company_info WHERE id > ? ORDER BY id";

        String chInsert = "INSERT INTO company_info(" +
                "id,it_code,company_name,e_name,prev_name,group_name,credit_code,org_number,reg_number,taxpayer_id," +
                "legal_person_name,reg_capital,reg_capital_unit,reg_capital_str,registrar_code,estiblish_time,issue_date,business_start," +
                "organization,company_org_type,industry_mc_code,industry_mc,industry_sc_code,industry_sc,industry_tc_code,industry_tc," +
                "industry_fc_code,industry_fc,ind_category,ind_sub_category,reg_location,province,province_code,city,city_code,region," +
                "region_code,reg_authority,business_scope,company_scale,status,status_code,website,bank_top_name,bank_type,bank_type2," +
                "postal_code,contact_phone_number,other_phone_number,contact_address,e_mail,other_mail,participate_ssn,currency_code," +
                "create_time,update_time,del_flag,remark,lat,lng,location,type_flag) " +
                "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

        try (Connection mysqlConn = mysqlDs.getConnection();
             PreparedStatement mysqlPs = mysqlConn.prepareStatement(mysqlSelect, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
             Connection chConn = chDs.getConnection();
             PreparedStatement chPs = chConn.prepareStatement(chInsert)) {

            mysqlPs.setFetchSize(Integer.MIN_VALUE);
            mysqlPs.setLong(1, startId);
            ResultSet rs = mysqlPs.executeQuery();

            long maxProcessId = startId;
            int count = 0;
            while (rs.next()) {
                // 设置所有参数
                chPs.setLong(1, rs.getLong("id"));
                chPs.setString(2, getStringOrDefault(rs.getString("it_code")));
                chPs.setString(3, getStringOrDefault(rs.getString("company_name")));
                chPs.setString(4, getStringOrDefault(rs.getString("e_name")));
                chPs.setString(5, getStringOrDefault(rs.getString("prev_name")));
                chPs.setString(6, getStringOrDefault(rs.getString("group_name")));
                chPs.setString(7, getStringOrDefault(rs.getString("credit_code")));
                chPs.setString(8, getStringOrDefault(rs.getString("org_number")));
                chPs.setString(9, getStringOrDefault(rs.getString("reg_number")));
                chPs.setString(10, getStringOrDefault(rs.getString("taxpayer_id")));

                chPs.setString(11, getStringOrDefault(rs.getString("legal_person_name")));

                // BigDecimal 处理
                BigDecimal regCapital = rs.getBigDecimal("reg_capital");
                chPs.setBigDecimal(12, regCapital == null ? BigDecimal.ZERO : regCapital);

                chPs.setString(13, getStringOrDefault(rs.getString("reg_capital_unit")));
                chPs.setString(14, getStringOrDefault(rs.getString("reg_capital_str")));
                chPs.setString(15, getStringOrDefault(rs.getString("registrar_code")));

                // 时间字段处理：如果为 null 设置为 2000-01-01 00:00:00
                chPs.setTimestamp(16, getTimestampOrDefault(rs.getTimestamp("estiblish_time")));
                chPs.setTimestamp(17, getTimestampOrDefault(rs.getTimestamp("issue_date")));

                chPs.setString(18, getStringOrDefault(rs.getString("business_start")));

                chPs.setString(19, getStringOrDefault(rs.getString("organization")));
                chPs.setString(20, getStringOrDefault(rs.getString("company_org_type")));
                chPs.setString(21, getStringOrDefault(rs.getString("industry_mc_code")));
                chPs.setString(22, getStringOrDefault(rs.getString("industry_mc")));
                chPs.setString(23, getStringOrDefault(rs.getString("industry_sc_code")));
                chPs.setString(24, getStringOrDefault(rs.getString("industry_sc")));
                chPs.setString(25, getStringOrDefault(rs.getString("industry_tc_code")));
                chPs.setString(26, getStringOrDefault(rs.getString("industry_tc")));

                chPs.setString(27, getStringOrDefault(rs.getString("industry_fc_code")));
                chPs.setString(28, getStringOrDefault(rs.getString("industry_fc")));
                chPs.setString(29, getStringOrDefault(rs.getString("ind_category")));
                chPs.setString(30, getStringOrDefault(rs.getString("ind_sub_category")));
                chPs.setString(31, getStringOrDefault(rs.getString("reg_location")));
                chPs.setString(32, getStringOrDefault(rs.getString("province")));
                chPs.setString(33, getStringOrDefault(rs.getString("province_code")));
                chPs.setString(34, getStringOrDefault(rs.getString("city")));
                chPs.setString(35, getStringOrDefault(rs.getString("city_code")));
                chPs.setString(36, getStringOrDefault(rs.getString("region")));

                chPs.setString(37, getStringOrDefault(rs.getString("region_code")));
                chPs.setString(38, getStringOrDefault(rs.getString("reg_authority")));
                chPs.setString(39, getStringOrDefault(rs.getString("business_scope")));
                chPs.setByte(40, getByteOrDefault(rs.getByte("company_scale")));
                chPs.setString(41, getStringOrDefault(rs.getString("status")));
                chPs.setString(42, getStringOrDefault(rs.getString("status_code")));
                chPs.setString(43, getStringOrDefault(rs.getString("website")));
                chPs.setString(44, getStringOrDefault(rs.getString("bank_top_name")));
                chPs.setString(45, getStringOrDefault(rs.getString("bank_type")));
                chPs.setString(46, getStringOrDefault(rs.getString("bank_type2")));

                chPs.setString(47, getStringOrDefault(rs.getString("postal_code")));
                chPs.setString(48, getStringOrDefault(rs.getString("contact_phone_number")));
                chPs.setString(49, getStringOrDefault(rs.getString("other_phone_number")));
                chPs.setString(50, getStringOrDefault(rs.getString("contact_address")));
                chPs.setString(51, getStringOrDefault(rs.getString("e_mail")));
                chPs.setString(52, getStringOrDefault(rs.getString("other_mail")));
                chPs.setString(53, getStringOrDefault(rs.getString("participate_ssn")));
                chPs.setString(54, getStringOrDefault(rs.getString("currency_code")));

                // 时间字段处理
                chPs.setTimestamp(55, getTimestampOrDefault(rs.getTimestamp("create_time")));
                chPs.setTimestamp(56, getTimestampOrDefault(rs.getTimestamp("update_time")));

                chPs.setByte(57, getByteOrDefault(rs.getByte("del_flag")));
                chPs.setString(58, getStringOrDefault(rs.getString("remark")));

                // 数值字段：如果为 null 设置为 0
                chPs.setDouble(59, getDoubleOrDefault(rs.getDouble("lat"), rs.wasNull()));
                chPs.setDouble(60, getDoubleOrDefault(rs.getDouble("lng"), rs.wasNull()));

                chPs.setString(61, getStringOrDefault(rs.getString("location")));

                // type_flag：如果为 null 设置为 0
                long typeFlag = rs.getLong("type_flag");
                if (rs.wasNull()) {
                    chPs.setLong(62, 0L);
                } else {
                    chPs.setLong(62, 0L);
                }


                chPs.addBatch();
                maxProcessId = rs.getLong("id");
                count++;
                if (count >= BATCH_SIZE) {
                    chPs.executeBatch();
                    chPs.clearBatch();
                    saveBreakpoint(BREAKPOINT_COMPANY, maxProcessId);
                    System.out.printf("company_info 已同步 id=%d%n", maxProcessId);
                    count = 0;
                }
            }
            if (count > 0) {
                chPs.executeBatch();
                chPs.clearBatch();
                saveBreakpoint(BREAKPOINT_COMPANY, maxProcessId);
                System.out.printf("company_info 最后一批完成，maxId=%d%n", maxProcessId);
            }
        }
        System.out.println("==== company_info 全量同步完成 ====");
    }

    // 辅助方法：将 null 转为空字符串
    private static String getStringOrDefault(String value) {
        return value == null ? "" : value;
    }

    // 辅助方法：将 null 转为 0（double）
    private static double getDoubleOrDefault(double value, boolean wasNull) {
        return wasNull ? 0.0 : value;
    }

    // 辅助方法：将 null 转为 0（byte）
    private static byte getByteOrDefault(byte value) {
        // 注意：rs.getByte() 对于 NULL 返回 0，所以这里直接返回
        return value;
    }

    // 辅助方法：将 null 时间转为 2000-01-01 00:00:00
    private static Timestamp getTimestampOrDefault(Timestamp timestamp) {
        if (timestamp == null) {
            // 2000-01-01 00:00:00
            return Timestamp.valueOf("2000-01-01 00:00:00");
        }
        return timestamp;
    }



    @Test
    public void testSyncArfCompanyInfo() throws SQLException {
        initDataSource();
        long startId = readBreakpoint(BREAKPOINT_COMPANY);
        System.out.println("company_info 从断点id=" + startId + "开始同步");
        syncArfCompanyInfo(1);
        mysqlDs.close();
        chDs.close();
    }

}

posted @ 2026-08-21 10:35 一文搞懂阅读(3) 评论(0) 收藏举报

刷新页面返回顶部

看例学习

学习一个东西，先会用，再理解其原理，再会造。要学会用，最快的的步骤就是依葫芦画瓢，看例子。

Mysql 数据同步 ClickHouse 亿级数据10分钟内搞定

1、安装ClickHouse

2、建表

3、同步数据

公告

看例学习

学习一个东西，先会用，再理解其原理，再会造。 要学会用，最快的的步骤就是依葫芦画瓢，看例子。

Mysql 数据同步 ClickHouse 亿级数据10分钟内搞定

1、安装ClickHouse

2、建表

3、同步数据

公告

学习一个东西，先会用，再理解其原理，再会造。要学会用，最快的的步骤就是依葫芦画瓢，看例子。