使用BalancedClickhouseDataSource

参考以下clickhouse-jdbc中的代码中的注释：jdbc:clickhouse://localhost:8123,localhost:8123/database?compress=1&decompress=2

clickhouse-jdbc/BalancedClickhouseDataSource.java at master · yandex/clickhouse-jdbc · GitHub

/**
     * create Datasource for clickhouse JDBC connections
     *
     * @param url address for connection to the database
     *            must have the next format {@code jdbc:clickhouse://<first-host>:<port>,<second-host>:<port>/<database>?param1=value1&param2=value2 }
     *            for example, {@code jdbc:clickhouse://localhost:8123,localhost:8123/database?compress=1&decompress=2 }
     * @throws IllegalArgumentException if param have not correct format, or error happens when checking host availability
     */
    public BalancedClickhouseDataSource(final String url) {
        this(splitUrl(url), getFromUrl(url));
    }

    /**
     * create Datasource for clickhouse JDBC connections
     *
     * @param url        address for connection to the database
     * @param properties database properties
     * @see #BalancedClickhouseDataSource(String)
     */
    public BalancedClickhouseDataSource(final String url, Properties properties) {
        this(splitUrl(url), new ClickHouseProperties(properties));
    }

    /**
     * create Datasource for clickhouse JDBC connections
     *
     * @param url        address for connection to the database
     * @param properties database properties
     * @see #BalancedClickhouseDataSource(String)
     */
    public BalancedClickhouseDataSource(final String url, ClickHouseProperties properties) {
        this(splitUrl(url), properties.merge(getFromUrlWithoutDefault(url)));
    }

使用SLB

使用LB均衡到各个副本，保证应用方查询单host，本次不使用BalancedClickhouseDataSource，从github issue上看BalancedClickhouseDataSource在之前版本出现副本故障时没能故障转移，不知道是否有修复。

配置LB：使用标准JDBC连接时需要映射http协议到clickhouse的8123端口(http监听端口)

验证LB配置是否生效

echo 'SELECT * from dm.delphi_membership_properties FORMAT Pretty' | curl 'internal-clickhouse-prod-621097858.
cn-north-1.elb.amazonaws.com.cn:80/?' --data-binary @-

贴一下在Springboot中使用标准JDBC数据源HikariDataSource÷连接clickhouse的配置：

package com.kezaihui.delphi.core.config;

import com.baomidou.mybatisplus.entity.GlobalConfiguration;
import com.baomidou.mybatisplus.enums.DBType;
import com.baomidou.mybatisplus.enums.IdType;
import com.baomidou.mybatisplus.spring.MybatisSqlSessionFactoryBean;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.boot.autoconfigure.MybatisProperties;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;

import javax.sql.DataSource;

/**
 * clickhouse 数据源配置
 *
 * @author Suncle
 * @date 2019-07-05
 */
@Slf4j
@Configuration
@MapperScan(basePackages = {
    "com.kezaihui.delphi.core.membership.**.mapper"
}, sqlSessionFactoryRef = "ckSqlSessionFactory")
public class CkDataSourceConfig {

    @Autowired
    private MybatisProperties mybatisProperties;

    /**
     * 读取数据源
     *
     * @return javax.sql.DataSource 数据源
     */
    @Bean(name = "ckDataSource")
    @ConfigurationProperties(prefix = "spring.clickhouse.datasource")
    public DataSource dataSource() {
        return new HikariDataSource();
    }

    /**
     * sql 会话工厂配置
     *
     * @param ckDataSource javax.sql.DataSource 数据源
     * @return SqlSessionFactory
     */
    @Bean(name = "ckSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("ckDataSource") DataSource ckDataSource) {
        MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
        bean.setDataSource(ckDataSource);

        try {
            GlobalConfiguration configuration = new GlobalConfiguration();
            configuration.setDbType(DBType.OTHER.name());
            configuration.setIdType(IdType.AUTO.getKey());
            configuration.setDbColumnUnderline(true);
            bean.setGlobalConfig(configuration);
            ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
            bean.setMapperLocations(mybatisProperties.resolveMapperLocations());
            bean.setConfigLocation(resolver.getResource(mybatisProperties.getConfigLocation()));
            return bean.getObject();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}

配置好之后可以使用mybatis查询数据

原生TCP协议连接clickhouse cluster

同样是采用SLB映射到不同的节点上，但是映射的时候需要注意不同之处：

映射TCP协议到clickhouse的9000端口

连接方式可以参考clickhouse-driver的连接，也可以直接使用python语言clickhouse-driver库

clickhouse python client的选择

官方没有维护各语言的driver，全部由第三方维护，主要有以下两个，对比参见后面。

结论：选择clickhouse-driver，数仓项目使用orm的意义不大。因为应用层不是python项目，是java项目

clickhouse-driver

GitHub - mymarilyn/clickhouse-driver: ClickHouse Python Driver with native interface support 活跃度高，star数最高。语法主要是执行原生sql

infi.clickhouse_orm

GitHub - Infinidat/infi.clickhouse_orm: A Python library for working with the ClickHouse database (https://clickhouse.yandex/) 活跃度高，star数第二高。是一个为clickhouse封装的orm框架，写起来有django的感觉

参考：