springboot整合clickhouse

1.pom 配置

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

如果需要查询bitmap，或者使用bitmap，还需要引入

        <dependency>
            <groupId>org.roaringbitmap</groupId>
            <artifactId>RoaringBitmap</artifactId>
            <version>0.9.31</version>
        </dependency>

2.配置

clickhouse:
  url: jdbc:clickhouse://ip:port/xxxx
  username: xxxx
  password: xxxxxxxxx

集群配置

clickhouse:
  url: jdbc:clickhouse://ip1:port,ip2:port,ip3:port/xxxx
  username: xxxx
  password: xxxxxxxxx

注：如果存在查询本地表，且本地表只建在某个单节点，使用集群模式查询会报错，表不存在。

3.创建连接

   //单节点连接 
   public ClickHouseConnection getConn() {
        ClickHouseProperties properties = new ClickHouseProperties();
        properties.setUser(username);
        properties.setPassword(password);
        //properties.setSocketTimeout();
        ClickHouseDataSource clickHouseDataSource = new ClickHouseDataSource(url,properties);
        try {
            return clickHouseDataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }



    //集群模式
    public ClickHouseConnection getConns() throws SQLException {
        ClickHouseProperties properties = new ClickHouseProperties();
        properties.setUser(username);
        properties.setPassword(password);
        properties.setSocketTimeout(Integer.MAX_VALUE);
        properties.setMaxQuerySize(Long.MAX_VALUE);
        properties.setConnectionTimeout(10 * 60 * 1000);
        BalancedClickhouseDataSource clickhouseDataSource = new BalancedClickhouseDataSource(url, properties);
        return clickhouseDataSource.getConnection();
    }

4.查询ck

        Connection connection = clickhouseConfig.getConn();
        Statement statement = connection.createStatement();
        ResultSet results = statement.executeQuery(sql);
        ResultSetMetaData rsmd = results.getMetaData();
        if(resultSet != null){
           ResultSetMetaData rsmd = resultSet.getMetaData();
           while(resultSet.next()){
                String cl1= resultSet.getString(rsmd.getColumnName(1));
                String cl2= resultSet.getString(rsmd.getColumnName(2));
                String cl3= resultSet.getString(rsmd.getColumnName(3));
           }
        }

bitmap查询

            ClickHouseConnection connection = clickhouseDataSource.getConnection();
            ClickHouseStatement statement = connection.createStatement();
            ClickHouseRowBinaryInputStream rowBinaryInputStream =  statement.executeQueryClickhouseRowBinaryStream(sql);
            ClickHouseBitmap bitmap = rowBinaryInputStream.readBitmap(ClickHouseDataType.Int32);
            RoaringBitmap roaringBitmap = (RoaringBitmap) bitmap.unwrap();