使用jdbc操作ClickHouse

• 建立maven項目引入依賴

引入ClickHouse驅動包

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

• 修改ClickHouse配置檔案

vim /etc/clickhouse-server/config.xml

配置jdbc通路端口

<http_port>8123</http_port>
           

配置ip位址

<listen_host>::1</listen_host>
<listen_host>172.16.1.23</listen_host>
           

注：需要配置listen_host,不然會出現 Connection refused的情況，修改完成之後需要重新開機

• 編寫測試類

package com.zhangwq;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author zhangwq
 * @date 2018/6/30 20:58
 */
public class ClickHouseJDBC {
    public static void main(String[] args) {
        String sqlDB = "show databases";//查詢資料庫
        String sqlTab = "show tables";//檢視表
        String sqlCount = "select count(*) count from ontime";//查詢ontime資料量
        exeSql(sqlDB);
        exeSql(sqlTab);
        exeSql(sqlCount);
    }

    public static void exeSql(String sql){
        String address = "jdbc:clickhouse://172.16.1.23:8123/default";
        Connection connection = null;
        Statement statement = null;
        ResultSet results = null;
        try {
            Class.forName("ru.yandex.clickhouse.ClickHouseDriver");
            connection = DriverManager.getConnection(address);
            statement = connection.createStatement();
            long begin = System.currentTimeMillis();
            results = statement.executeQuery(sql);
            long end = System.currentTimeMillis();
            System.out.println("執行（"+sql+"）耗時："+(end-begin)+"ms");
            ResultSetMetaData rsmd = results.getMetaData();
            List<Map> list = new ArrayList();
            while(results.next()){
                Map map = new HashMap();
                for(int i = 1;i<=rsmd.getColumnCount();i++){
                    map.put(rsmd.getColumnName(i),results.getString(rsmd.getColumnName(i)));
                }
                list.add(map);
            }
            for(Map map : list){
                System.err.println(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {//關閉連接配接
            try {
                if(results!=null){
                    results.close();
                }
                if(statement!=null){
                    statement.close();
                }
                if(connection!=null){
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
           

啟動項目測試，發現如下報錯。

SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".

SLF4J: Defaulting to no-operation (NOP) logger implementation

pom引入slf4j相關檔案引入依賴，并resource下添加log4j2.properties解決該問題

<dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-slf4j-impl</artifactId>
            <version>2.11.0</version>
        </dependency>
           

• 檢視輸出結果

{name=default}
{name=system}
執行（show databases）耗時：2ms
{name=ontime}
執行（show tables）耗時：2ms
{name=zwqtest}
{name=zwqtestall}
執行（select count(*) count from ontime）耗時：98ms
{count=177920306}