Clickhouse-Java使用JDBC连接大批量导出(表2本地文件)

依赖配置pom.xml

<dependency>
    <groupId>cc.blynk.clickhouse</groupId>
    <artifactId>clickhouse4j</artifactId>
    <version>1.4.4</version>
</dependency>

CK基本信息

String driver = "cc.blynk.clickhouse.ClickHouseDriver";
String ip = "xxx.xxx.xxx.xxx";
String port = "8123";
String db = "db";
String user = "user";
String pwd = "pwd";
// 数据输出文件
String fileName = "/data/table1_cols123_20211125.txt.gz";

创建ClickHouse连接

Class.forName(driver);
StringBuffer urlSb = new StringBuffer()
        .append("jdbc:clickhouse://")
        .append(ip).append(":").append(port).append("/").append(db)
        .append("?characterEncoding=utf8&useSSL=false");
Connection connection = DriverManager.getConnection(urlSb.toString(), user, pwd);
CopyManager copyManager = CopyManagerFactory.create(connection);

数据导出模式

String query = new StringBuilder()
        .append("select col1,col2,col3 from db.table1 where col4='data4' group by col1,col2,col3")
        .append(" FORMAT TabSeparated")
        .toString();

(1)输出gz文件,每行数据根据table键分割
(2)可以根据表条件过滤、可以根据某些字段分组、并只输出指定列
(3)语句SQL全量groupby可能内存不够用;语句需要在clickhouse执行一次

输出gz文件流

OutputStream outputStream = new GZIPOutputStream(new FileOutputStream(new File(fileName)));
copyManager.copyFromDb(query, outputStream);
outputStream.flush();

关闭相关连接

if(outputStream != null) outputStream.close();
if(copyManager != null) copyManager.close();
if(connection != null) connection.close();
上一篇:SpringCloud注册中心Eureka


下一篇:Java实现xml文件解析