简介
我自己搭建的ck是两节点的,两分片一备份。写入数据是生成随机数取余机器数来随机分配机器写入本地表的,然后建的分布式表供查询,外加一个web服务集成springboot、提供对外服务。
ck查询的负载均衡没有使用chproxy和nginx,因为我们项目比较简单,就没弄额外的组件了,直接在代码中控制了,下面是集成步骤。
1.pom
<dependency>
<groupId>ru.yandex.clickhouse</groupId>
<artifactId>clickhouse-jdbc</artifactId>
<version>0.2.4</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.60</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.6.1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<exclusions>
<exclusion>
<artifactId>logback-classic</artifactId>
<groupId>ch.qos.logback</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.10</version>
</dependency>
2.配置文件
因为是两台机器,这里是用了两个datasource,然后随机分配其中一个去查询
server:
port: 8086
servlet:
context-path: /ck-module
tomcat:
uri-encoding: utf-8
spring:
http:
encoding:
charset: UTF-8
enabled: true
force: true
datasource:
clickhouse220:
type: com.alibaba.druid.pool.DruidDataSource
jdbcUrl: jdbc:clickhouse://xxx:8123/xxx
username: default
password:
driver-class-name: ru.yandex.clickhouse.ClickHouseDriver
druid:
initial-size: 5
min-idle: 5
max-active: 20
max-wait: 60000
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 300000
test-while-idle: true
validation-query: select 1
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
clickhouse200:
type: com.alibaba.druid.pool.DruidDataSource
jdbcUrl: jdbc:clickhouse://xxx:8123/xxx
username: default
password:
driver-class-name: ru.yandex.clickhouse.ClickHouseDriver
druid:
initial-size: 5
min-idle: 5
max-active: 20
max-wait: 60000
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 300000
test-while-idle: true
validation-query: select 1
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
ck:
200ip: xxx
220ip: xxx
3.Datasource配置
package com.bigblue.clickhouse.config;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
/**
* @Author: TheBigBlue
* @Description:
* @Date: 2020/12/24
*/
@Configuration
public class CK200Config {
@Bean(name = "ck200DataSource")
@ConfigurationProperties(prefix = "spring.datasource.clickhouse200")
public DataSource dataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "ck200Template")
public JdbcTemplate ck200Template(@Qualifier("ck200DataSource") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
}
package com.bigblue.clickhouse.config;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
/**
* @Author: TheBigBlue
* @Description:
* @Date: 2020/12/24
*/
@Configuration
public class CK220Config {
@Bean(name = "ck220DataSource")
@ConfigurationProperties(prefix = "spring.datasource.clickhouse220")
public DataSource dataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "ck220Template")
public JdbcTemplate ck220Template(@Qualifier("ck220DataSource") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
}
4.负载均衡代码
package com.bigblue.clickhouse.service;
import com.alibaba.fastjson.JSONArray;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
/**
* @Author: TheBigBlue
* @Description:
* @Date: 2020/7/14
*/
@Service
public class WContentService {
private static final Logger LOGGER = LoggerFactory.getLogger(WContentService.class);
@Autowired
@Qualifier("ck220Template")
private JdbcTemplate ck220Template;
@Autowired
@Qualifier("ck200Template")
private JdbcTemplate ck200Template;
@Value("${ck.200ip}")
private String ck200IP;
@Value("${ck.220ip}")
private String ck220IP;
/**
* @Author: TheBigBlue
* @Description: 负载均衡,差不同机器
* @Date: 2020/12/25
**/
private JdbcTemplate getSpecTemplate(String uuid) {
String[] ips = new String[]{ck200IP, ck220IP};
JdbcTemplate[] templates = new JdbcTemplate[]{ck200Template, ck220Template};
int index = (int) ((Math.random() * 9 + 1)) % 2;
LOGGER.info("UUID: 【{}】,当前查询,Index: 【{}】, 分配机器:【{}】", uuid, index, ips[index]);
return templates[index];
}
}
5.使用BalancedClickhouseDataSource做负载均衡
未完待续
6.使用ChProxy做负载均衡
-
下载:chproxy
-
chproxy是clickhouse官方推荐的代理工具, 只需要配置config.yml即可
hack_me_please: true
log_debug: true
# chproxy的访问接口,你会通过这里配置的ip:port来访问你的chproxy
server:
http:
listen_addr: ":3380"
# 允许访问的网络评断
# allowed_networks: ["10.0.0.0/8", "146.0.0.0/100"]
read_timeout: 3m
# 连接到clickhouse的集群配置
clusters:
# 这个clickhouse的连接的集群是支持写操作
- name: "writer-cluster"
# clickhouse的集群的地址,如果是全分布式集群,那么多个节点之间使用逗号隔开即可
nodes: ["node01:8123","node02:8123","node03:8123"]
# clickhouse的用户配置
users:
- name: "default"
password: ""
# 这个clickhouse的连接集群只读操作
- name: "readonly-cluster"
# clickhouse的集群的地址,如果是全分布式集群,那么多个节点之间使用逗号隔开即可
nodes: ["node01:8123","node02:8123","node03:8123"]
# clickhouse的用户配置
users:
- name: "clickhouse_r"
password: "clickhouse_r"
# chproxy调用clickhouse的账户
users:
# 用户[写入权限],使用此用户连接clickhouse可以进行写入操作
- name: "default"
password: ""
to_cluster: "writer-cluster"
to_user: "default"
allow_cors: true
- name: "clickhouse_r"
password: "clickhouse_r"
to_cluster: "readonly-cluster"
to_user: "clickhouse_r"
allow_cors: true
- 启动:
nohup /data/services/chproxy-1.16.3/chproxy \
-config=/data/services/chproxy-1.16.3/config.yml \
>> /data/services/chproxy-1.16.3/error.log 2>&1 &