当前位置：首页 > article >正文

后台数据报表导出数据量过大问题

article 2025/3/3 22:41:28

现状分析

之前在mysql业务库,导出报表会出现各种表相互关联，导致夯死的情况

改进使用ClickHouse做宽表

后面使用binlog监听，洗数据洗成一张宽表，存放在ck中，但是发现超过一定数量级100w也会很卡慢，但是比mysql强的是可以出来。

如何导出300w 、500w 1000w数据量级

查看了CK有个特性，执行流式数据处理，可以使用这个特性来处理

方案 1：分批查询

如果你当前的做法是一次性查询 100W 条数据，那就容易导致 CK 查询压力大、内存占用高。可以分批查询，用流式处理（如 LIMIT … OFFSET 或游标）优化导出。

SELECT * FROM my_table WHERE 条件 ORDER BY id LIMIT 10000 OFFSET 0;
SELECT * FROM my_table WHERE 条件 ORDER BY id LIMIT 10000 OFFSET 10000;
...
# 使用 游标方式 进行分页，例如：
SELECT * FROM my_table WHERE 条件 AND id > last_id ORDER BY id LIMIT 10000;

这种方案比较繁琐，效果也不好

方案 2：ClickHouse 外部表流式查询

ClickHouse 提供流式查询 + CSV 导出，这样数据不会全部加载到内存，而是边查边写，提高效率。

clickhouse-client --user username --password password  --query="SELECT page_name,page_path FROM table WHERE type='visit'" --format CSV > export11.csv

📌 优势

不会占用太多内存（不像 SELECT * 一次性取 100W 数据）。
查询结果可以直接写入文件，减少网络传输压力。

测试结果 百万级数据秒级生成

方案 3：异步任务 + 预生成文件

(适合超大量数据)
如果查询仍然导致性能下降，可以考虑后台异步处理，生成 CSV 文件后再让用户下载：

思路
用户提交导出请求后，后端异步任务开始查询并生成文件（存储到 OSS 或本地）。
任务完成后，给用户返回下载链接，前端再下载文件。

实现
📌 Step 1: 后端异步查询并存 CSV

clickhouse-client --user username --password password --query="SELECT * FROM my_table WHERE 条件" --format CSV > /tmp/export_$(date +%s).csv

📌 Step 2: 文件存储到对象存储（如 MinIO / OSS）

aws s3 cp /tmp/export.csv s3://my-bucket/

📌 Step 3: 前端定期轮询下载链接后端提供一个 API，比如：

{
  "status": "done",
  "download_url": "https://oss.example.com/export_123.csv"
}

前端轮询这个 API，一旦任务完成，就给用户提供下载按钮。

CK原生命令集成到Java

和ck部署在同一个服务器上（不推荐）

import org.springframework.web.bind.annotation.*;
import java.io.*;

@RestController
@RequestMapping("/clickhouse")
public class ClickHouseController {

    @GetMapping("/export")
    public String exportData() {
        String query = "SELECT * FROM my_table WHERE 条件";
        String exportFile = "/tmp/export.csv";  // 保存到临时目录

        String command = String.format("clickhouse-client --query=\"%s\" --format CSV > %s", query, exportFile);
        ProcessBuilder processBuilder = new ProcessBuilder("/bin/bash", "-c", command);

        try {
            Process process = processBuilder.start();
            int exitCode = process.waitFor();

            if (exitCode == 0) {
                return "✅ ClickHouse 数据导出成功：" + exportFile;
            } else {
                return "❌ ClickHouse 导出失败";
            }
        } catch (IOException | InterruptedException e) {
            return "⚠️ 发生异常：" + e.getMessage();
        }
    }
}

远程 SSH 连接到 ClickHouse 服务器

import com.jcraft.jsch.*;

import java.io.*;

public class RemoteClickHouseExecutor {
    private static final String SSH_HOST = "clickhouse_server_ip"; // 远程 ClickHouse 服务器 IP
    private static final String SSH_USER = "root";  // SSH 用户
    private static final String SSH_PASSWORD = "your_password"; // SSH 密码
    private static final String CLICKHOUSE_QUERY = "SELECT * FROM my_table WHERE 条件";
    private static final String EXPORT_FILE = "/tmp/export.csv";  // 远程服务器上的文件路径

    public static void main(String[] args) {
        try {
            JSch jsch = new JSch();
            Session session = jsch.getSession(SSH_USER, SSH_HOST, 22);
            session.setPassword(SSH_PASSWORD);
            session.setConfig("StrictHostKeyChecking", "no");
            session.connect();

            // 远程执行 ClickHouse 查询
            String command = "clickhouse-client --query=\"" + CLICKHOUSE_QUERY + "\" --format CSV > " + EXPORT_FILE;
            ChannelExec channel = (ChannelExec) session.openChannel("exec");
            channel.setCommand(command);
            channel.setInputStream(null);
            channel.setErrStream(System.err);

            InputStream inputStream = channel.getInputStream();
            channel.connect();

            // 读取命令执行结果
            BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream));
            String line;
            while ((line = reader.readLine()) != null) {
                System.out.println(line);
            }

            channel.disconnect();
            session.disconnect();

            System.out.println("✅ ClickHouse 远程数据导出完成：" + EXPORT_FILE);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

ClickHouse JDBC 连接远程 ClickHouse

添加 ClickHouse JDBC 依赖

<dependency>
    <groupId>ru.yandex.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.3.2</version>
</dependency>

JDBC 查询 ClickHouse 并导出 CSV

import ru.yandex.clickhouse.ClickHouseDataSource;

import java.io.*;
import java.sql.*;

public class ClickHouseJdbcExport {
    private static final String CLICKHOUSE_URL = "jdbc:clickhouse://clickhouse_server_ip:8123/default";
    private static final String CLICKHOUSE_USER = "default";
    private static final String CLICKHOUSE_PASSWORD = "";

    public static void main(String[] args) {
        String query = "SELECT * FROM my_table WHERE 条件";
        String exportFile = "export.csv";

        try (Connection connection = new ClickHouseDataSource(CLICKHOUSE_URL).getConnection(CLICKHOUSE_USER, CLICKHOUSE_PASSWORD);
             Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(query);
             BufferedWriter writer = new BufferedWriter(new FileWriter(exportFile))) {

            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            // 写入 CSV 头部
            for (int i = 1; i <= columnCount; i++) {
                writer.write(metaData.getColumnName(i));
                if (i < columnCount) writer.write(",");
            }
            writer.newLine();

            // 写入数据
            while (rs.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    writer.write(rs.getString(i));
                    if (i < columnCount) writer.write(",");
                }
                writer.newLine();
            }

            System.out.println("✅ ClickHouse 数据已导出：" + exportFile);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}