• JDBC连接clickhouse cluster


    JDBC连接clickhouse cluster

    两种方式,一种是使用clickhouse-jdbc连接集群中的每一个节点,另外一种是使用SLB提供一个对外的统一地址

    使用BalancedClickhouseDataSource

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

    /**
         * 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的感觉

    参考:

  • 相关阅读:
    .net正在终止线程异常
    js判断客户浏览器类型,版本
    C#中ToString格式大全
    WPF TextBox 搜索框 自定义
    C#:获取设备电量相关信息
    C#中的委托与事件并存的理由
    WPF中的Pack URI
    SQLServer中的数据库备份和还原
    使用Aspose.Cells读取Excel
    SQLServer存储过程事务用法
  • 原文地址:https://www.cnblogs.com/liuys635/p/14307151.html
Copyright © 2020-2023  润新知