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();