SpringBoot2增加ClickHouse集群SLB支持

发表于

首先：感谢杜顺利同学和我介绍ClickHouse整体调用情况，JDBC支持情况。然后我才有机会根据他提供的信息探究和总结SpringBoot2集成ClickHouse以及对SLB的支持情况。
ClickHouse有两种模式，一种是单点模式、一种是集群模式。
对应的java连接方式也就有两种，一种是连接集群中的每一个节点、另一种是使用SLB提供一个对外的统一地址，这里介绍下SpringBoot2集成SLB的访问形式。

ClickHouse的jdbc驱动

1
2
3
4
5
<dependency>
	<groupId>ru.yandex.clickhouse</groupId>
	<artifactId>clickhouse-jdbc</artifactId>
	<version>0.2.3</version>
</dependency>

负载流程图

BalancedClickhouseDataSource类是提供负载均衡的支持类,执行流程如下图所示：
图片

这个流程逻辑从源码中很容可以看出，负载均衡策略使用的是随机。虽然对我们留了检测SLB可用节点的入口，但是并没有默认为我们启动检测。测试是需要我们手动启动的，配置在下面模板配置里介绍。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
private String getAnyUrl() throws SQLException {
        List<String> localEnabledUrls = enabledUrls;
        if (localEnabledUrls.isEmpty()) {
            throw new SQLException("Unable to get connection: there are no enabled urls");
        }
        Random random = this.randomThreadLocal.get();
        if (random == null) {
            this.randomThreadLocal.set(new Random());
            random = this.randomThreadLocal.get();
        }

        int index = random.nextInt(localEnabledUrls.size());
        return localEnabledUrls.get(index);
    }

数据源配置

1
2
3
4
5
6
7
#clickhouse-slb
spring.datasource.clickhouse.driverClassName=ru.yandex.clickhouse.ClickHouseDriver
spring.datasource.clickhouse.url=jdbc:clickhouse://127.0.0.1:8123,127.0.0.2:8123,127.0.0.3:8123/system
#秒，可以根据ClickHouseProperties配置更多属性
spring.datasource.clickhouse.delay=12
spring.datasource.clickhouse.user=
spring.datasource.clickhouse.password=

扩展ClickHouseProperties属性YueWenClickHouseProperties

YueWenClickHouseProperties.java 所有需要客制化的参数，但是源ClickHouseProperties中又没有提供的属性都可以在这里处理。这里我至扩展了两个参数：一个是访问连接、一个是检测间隔时间。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
package com.yuewen.bigdata.dataapi.config;

import ru.yandex.clickhouse.settings.ClickHouseProperties;

/**
 * Created by xiehui1956(@)gmail.com on 2020/4/28
 */
public class YueWenClickHouseProperties extends ClickHouseProperties {

    /**
     * 请求url，支持多个
     * jdbc:clickhouse://ip:port,ip:port
     * {@link ru.yandex.clickhouse.BalancedClickhouseDataSource.URL_TEMPLATE}
     * {@code jdbc:clickhouse://<first-host>:<port>,<second-host>:<port>/<database>?param1=value1&param2=value2 }
     */
    private String url;

    /**
     * 连接可用性检测间隔时间
     * 单位(秒)
     * 默认 10秒钟检测一次
     */
    private int delay = 10;

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public int getDelay() {
        return delay;
    }

    public void setDelay(int delay) {
        this.delay = delay;
    }
}

模板配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
@Bean
    @Resource
    @ConfigurationProperties(prefix = "spring.datasource.clickhouse")
    public YueWenClickHouseProperties yueWenClickHouseProperties() {
        YueWenClickHouseProperties yueWenClickHouseProperties = new YueWenClickHouseProperties();
        return yueWenClickHouseProperties;
    }

    /**
     * clickHouse操作模板
     *
     * @return
     */
    @Resource
    @Bean(name = "clickHouseDataSource")
    public DataSource clickHouseDataSource(YueWenClickHouseProperties yueWenClickHouseProperties) {
        BalancedClickhouseDataSource balancedClickhouseDataSource
                = new BalancedClickhouseDataSource(yueWenClickHouseProperties.getUrl(), yueWenClickHouseProperties);
        balancedClickhouseDataSource.scheduleActualization(yueWenClickHouseProperties.getDelay(), TimeUnit.SECONDS);
        return balancedClickhouseDataSource;
    }

    @Bean(name = "clickHouseJdbcTemplate")
    public JdbcTemplate clickHouseJdbcTemplate(@Qualifier(value = "clickHouseDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

使用模板

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
@Resource
    @Qualifier(value = "clickHouseJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @GetMapping("/tt")
    public CommonResult test(CommonResult commonResult) {
        List<Map<String, Object>> show_tables = jdbcTemplate.queryForList("show tables");
        return commonResult.setData(show_tables.stream().limit(10).collect(Collectors.toList()));
    }
	
@GetMapping("/cc")
    public CommonResult createTable(CommonResult commonResult) {
        final String CREATE_DATABASE = "CREATE DATABASE student ENGINE = Ordinary",
                CREATE_TABLE = "CREATE TABLE user_02 (id UInt16, username String, birthday date) ENGINE = MergeTree(birthday, (id), 8192)";
//        jdbcTemplate.execute(CREATE_DATABASE);
        jdbcTemplate.execute(CREATE_TABLE);
        List<String> list = jdbcTemplate.queryForList("show tables", String.class);
        return commonResult.setData(list);
    }

测试结果

图片

图片

遇到的问题-异常消息

没有使用docker安装ClickHouse、或者启动的时候做了端口映射的应该不会出现这个异常。
如果执行操作的时候没有获取可用的连接会报错：

1
2
3
if (localEnabledUrls.isEmpty()) {
            throw new SQLException("Unable to get connection: there are no enabled urls");
        }

程序抛出来的异常信息如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: Unable to get connection: there are no enabled urls
	at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:82) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:371) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:452) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:462) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:490) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at com.yuewen.bigdata.dataapi.controller.DbTableFieldController.test(DbTableFieldController.java:37) ~[classes/:na]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_161]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_161]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_161]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_161]
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190) ~[spring-web-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138) ~[spring-web-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105) ~[spring-webmvc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:879) ~[spring-webmvc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:793) ~[spring-webmvc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040) ~[spring-webmvc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943) ~[spring-webmvc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) [spring-webmvc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898) [spring-webmvc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:634) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) [spring-webmvc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:741) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) [tomcat-embed-websocket-9.0.33.jar:9.0.33]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) [spring-web-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) [spring-web-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) [spring-web-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) [spring-web-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) [spring-web-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) [spring-web-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:373) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1594) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_161]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_161]
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-9.0.33.jar:9.0.33]
	at java.lang.Thread.run(Thread.java:748) [na:1.8.0_161]
Caused by: java.sql.SQLException: Unable to get connection: there are no enabled urls
	at ru.yandex.clickhouse.BalancedClickhouseDataSource.getAnyUrl(BalancedClickhouseDataSource.java:177) ~[clickhouse-jdbc-0.2.3.jar:na]
	at ru.yandex.clickhouse.BalancedClickhouseDataSource.getConnection(BalancedClickhouseDataSource.java:194) ~[clickhouse-jdbc-0.2.3.jar:na]
	at ru.yandex.clickhouse.BalancedClickhouseDataSource.getConnection(BalancedClickhouseDataSource.java:27) ~[clickhouse-jdbc-0.2.3.jar:na]
	at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:158) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:116) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:79) ~[spring-jdbc-5.2.5.RELEASE.jar:5.2.5.RELEASE]
	... 55 common frames omitted

2020-04-28 12:59:24.858  WARN 62666 --- [nio-8080-exec-1] .m.m.a.ExceptionHandlerExceptionResolver : Resolved [org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: Unable to get connection: there are no enabled urls]

我的程序这个错误异常抛出位置在这里：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
private void initTimeZone(ClickHouseProperties properties) {
        if (properties.isUseServerTimeZone() && !Strings.isNullOrEmpty(properties.getUseTimeZone())) {
            throw new IllegalArgumentException(String.format("only one of %s or %s must be enabled", ClickHouseConnectionSettings.USE_SERVER_TIME_ZONE.getKey(), ClickHouseConnectionSettings.USE_TIME_ZONE.getKey()));
        }
        if (!properties.isUseServerTimeZone() && Strings.isNullOrEmpty(properties.getUseTimeZone())) {
            throw new IllegalArgumentException(String.format("one of %s or %s must be enabled", ClickHouseConnectionSettings.USE_SERVER_TIME_ZONE.getKey(), ClickHouseConnectionSettings.USE_TIME_ZONE.getKey()));
        }
        if (properties.isUseServerTimeZone()) {
            ResultSet rs = null;
            try {
                timezone = TimeZone.getTimeZone("UTC"); // just for next query
                rs = createStatement().executeQuery("select timezone()"); //这里抛出的异常
                rs.next();
                String timeZoneName = rs.getString(1);
                timezone = TimeZone.getTimeZone(timeZoneName);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                StreamUtils.close(rs);
            }
        } else if (!Strings.isNullOrEmpty(properties.getUseTimeZone())) {
            timezone = TimeZone.getTimeZone(properties.getUseTimeZone());
        }
    }

遇到的问题-处理异常

错误的原因是因为无法建立连接，排查了一下发现docker没有做端口映射。建立端口映射：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
BrucedeMacBook-Pro:druid brucexie$ docker images
REPOSITORY                      TAG                                              IMAGE ID            CREATED             SIZE
yandex/clickhouse-client        latest                                           82ed6fa8b103        4 days ago          488MB
yandex/clickhouse-server        latest                                           bb41d103e9b1        10 days ago         496MB
docker/desktop-kubernetes       kubernetes-v1.16.5-cni-v0.7.5-critools-v1.15.0   a86647f0b376        3 months ago        279MB
docker/kube-compose-installer   v0.4.25-alpha1                                   2a71ac5a1359        6 months ago        42.3MB
BrucedeMacBook-Pro:druid brucexie$ 
BrucedeMacBook-Pro:druid brucexie$ 
BrucedeMacBook-Pro:druid brucexie$ 
BrucedeMacBook-Pro:druid brucexie$ docker run -p 127.0.0.1:8123:8123 bb41d103e9b1
Processing configuration file '/etc/clickhouse-server/config.xml'.
Merging configuration file '/etc/clickhouse-server/config.d/docker_related_config.xml'.
Include not found: clickhouse_remote_servers
Include not found: clickhouse_compression
Logging trace to /var/log/clickhouse-server/clickhouse-server.log
Logging errors to /var/log/clickhouse-server/clickhouse-server.err.log
Processing configuration file '/etc/clickhouse-server/users.xml'.
Include not found: networks
Saved preprocessed configuration to '/var/lib/clickhouse//preprocessed_configs/users.xml'.
Processing configuration file '/etc/clickhouse-server/config.xml'.
Merging configuration file '/etc/clickhouse-server/config.d/docker_related_config.xml'.
Include not found: clickhouse_remote_servers
Include not found: clickhouse_compression
Saved preprocessed configuration to '/var/lib/clickhouse//preprocessed_configs/config.xml'.

此时连接可用性检测流程顺利通过：
图片