文章目录
1. 配置configmap.yaml文件中配置多数据源
data:
application.yml: |-
hh:
data:
clickhouse:
dataSources:
- dataSourceName: clickHouse
clickHouseHost: {{ .Values.clickHouse.host }}
clickHousePort: {{ .Values.clickHouse.port }}
useHttps: {{ .Values.clickHouse.useHttps }}
userName: {{ .Values.clickHouse.userName }}
password: {{ .Values.clickHouse.password }}
tenantId: {{ .Values.clickHouse.tenantId }}
passwordEncrypt: {{ .Values.clickHouse.passwordEncrypt }}
maxActive: {{ .Values.clickHouse.maxActive }}
- dataSourceName: clickHouse_weixin
clickHouseHost: {{ .Values.clickHouse_weixin.host }}
clickHousePort: {{ .Values.clickHouse_weixin.port }}
useHttps: {{ .Values.clickHouse_weixin.useHttps }}
userName: {{ .Values.clickHouse_weixin.userName }}
password: {{ .Values.clickHouse_weixin.password }}
tenantId: {{ .Values.clickHouse_weixin.tenantId }}
passwordEncrypt: {{ .Values.clickHouse_weixin.passwordEncrypt }}
maxActive: {{ .Values.clickHouse_weixin.maxActive }}
- dataSourceName: clickHouse_screen
clickHouseHost: {{ .Values.clickHouse_screen.host }}
clickHousePort: {{ .Values.clickHouse_screen.port }}
useHttps: {{ .Values.clickHouse_screen.useHttps }}
userName: {{ .Values.clickHouse_screen.userName }}
password: {{ .Values.clickHouse_screen.password }}
tenantId: {{ .Values.clickHouse_screen.tenantId }}
passwordEncrypt: {{ .Values.clickHouse_screen.passwordEncrypt }}
maxActive: {{ .Values.clickHouse_screen.maxActive }}
- dataSourceName: clickHouse_device
clickHouseHost: {{ .Values.clickHouse_device.host }}
clickHousePort: {{ .Values.clickHouse_device.port }}
useHttps: {{ .Values.clickHouse_device.useHttps }}
userName: {{ .Values.clickHouse_device.userName }}
password: {{ .Values.clickHouse_device.password }}
tenantId: {{ .Values.clickHouse_device.tenantId }}
passwordEncrypt: {{ .Values.clickHouse_device.passwordEncrypt }}
maxActive: {{ .Values.clickHouse_device.maxActive }}
2. 在 values.yaml 文件中配置数据取值
clickHouse:
host: ck.datalake.novalocal
port: 4433
useHttps: false
userName: user_xujin_readonly
password: sdail2023
tenantId: null
passwordEncrypt: true
maxActive: 200
clickHouse_weixin:
host: ck.datalake.novalocal
port: 4433
useHttps: false
userName: user_weixin_readonly
password: sdawl2023
tenantId: null
passwordEncrypt: true
maxActive: 200
clickHouse_screen:
host: ck.datalake.novalocal
port: 4433
useHttps: false
userName: user_screen_readonly
password: sdacl2023
tenantId: null
passwordEncrypt: true
maxActive: 200
clickHouse_device:
host: ck.datalake.novalocal
port: 4433
useHttps: false
userName: user_device_readonly
password: sdavl2023
tenantId: null
passwordEncrypt: true
maxActive: 200
3. 读取配置文件信息到 ClickHouseConnectionCacheEntity:
@Data
@Document("click_house_connection_cache")
@NoArgsConstructor
@AllArgsConstructor
public class ClickHouseConnectionCacheEntity {
private String dataSourceName;
private String clickHouseHost;
private Integer clickHousePort;
private Boolean useHttps;
@Nullable
private String userName;
@Nullable
private String password;
}
@Component
@ConfigurationProperties(prefix = "hh.data.clickhouse")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class ClickHouseDataSources {
/**
* 数据源列表
*/
@Nullable
private List<ClickHouseConnectionCacheEntity> dataSources;
}
4. 处理配置文件信息接口:SplClickHouseResourceService
public interface SplClickHouseResourceService {
/**
* 从配置中构建SplClickHouseResource
*
* @return SplClickHouseResource
*/
@NotNull
SplClickHouseResource buildSplClickHouseResource();
/**
* 从配置中构建SplCkServiceResource
*
* @return SplCkServiceResource
*/
@NotNull
SplCkServiceResource buildSplCkServiceResource();
/**
* 从配置中提取默认ClickHouseConnectionCacheEntity
*
* @return ClickHouseConnectionCacheEntity
*/
@NotNull
ClickHouseConnectionCacheEntity getClickHouseConnectionCacheEntity();
/**
* getClickHouseConnectionCacheEntities
*
* @return Map<String, ClickHouseConnectionCacheEntity>
*/
@NotNull
Map<String, ClickHouseConnectionCacheEntity> getClickHouseConnectionCacheEntities();
}
@CustomLog
@Service
public class SplClickHouseResourceServiceImpl implements SplClickHouseResourceService, InitializingBean {
private static final String CLICK_HOUSE_DEFAULT_DATASOURCE_NAME = "clickHouse";
@Nullable
@Getter
@Setter(onMethod_ = @Autowired(required = false))
private DynamicRoutingDataSource dynamicRoutingDataSource;
@Nullable
@Getter
@Setter(onMethod_ = @Autowired(required = false))
private ClickHouseDataSources clickHouseDataSources;
@Value("${ngsoc.CkService.host:127.0.0.1}")
private String ckServiceHost;
@Value("${ngsoc.CkService.port:8124}")
private Integer ckServicePort;
@Value("${ngsoc.CkService.timeout:3600}")
private Integer ckServiceTimeout;
@Value("${ngsoc.ClickHouse.passwordEncrypt:false}")
private boolean passwordEncrypt;
@Value("${ngsoc.ClickHouse.useJdbc:false}")
private boolean useJdbc;
private ManagedChannel ckServiceGrpcManagedChannel;
private static volatile Map<String, ClickHouseConnectionCacheEntity> CLICK_HOUSE_CONNECTION_CACHE_ENTITIES;
@Nullable
private static String extractPasswordRaw(@Nullable String password) {
if (password == null) {
return null;
}
try {
return KeyDecryptor.decrypt(password);
} catch (Exception e) {
log.warn("CK密码解密失败,请检查", e);
return password;
}
}
@NotNull
static String calculateClickHouseClientName() {
String hostName = null;
try {
hostName = System.getenv("HOSTNAME");
if (StringUtils.isBlank(hostName)) {
hostName = System.getProperty("HOSTNAME");
}
} catch (Exception e) {
log.error("获取HOSTNAME失败", e);
}
String result;
if (StringUtils.isBlank(hostName)) {
result = "Platform Unknown Pod";
} else {
result = "Platform " + hostName;
}
log.info("calculateClickHouseClientName : {}", result);
return result;
}
@NotNull
@Override
public SplClickHouseResource buildSplClickHouseResource() {
final DataSource dataSource;
if (useJdbc) {
DynamicRoutingDataSource dynamicRoutingDataSource = this.getDynamicRoutingDataSource();
if (dynamicRoutingDataSource == null) {
dataSource = null;
} else {
dataSource = dynamicRoutingDataSource.getCurrentDataSources().get(CLICK_HOUSE_DEFAULT_DATASOURCE_NAME);
}
} else {
dataSource = null;
}
ClickHouseConnectionCacheEntity clickHouseConnectionCacheEntity = getClickHouseConnectionCacheEntity();
return SplClickHouseResource.buildHttpSplClickHouseResource(
clickHouseConnectionCacheEntity.getClickHouseHost(),
clickHouseConnectionCacheEntity.getClickHousePort(),
clickHouseConnectionCacheEntity.getUseHttps(),
clickHouseConnectionCacheEntity.getUserName(),
clickHouseConnectionCacheEntity.getPassword(),
SplClickHouseResourceServiceImpl::calculateClickHouseClientName,
dataSource
);
}
@NotNull
@Override
public SplCkServiceResource buildSplCkServiceResource() {
ClickHouseConnectionCacheEntity clickHouseConnectionCacheEntity = getClickHouseConnectionCacheEntity();
return SplCkServiceResource.buildHttpSplCkServiceResource(
ckServiceGrpcManagedChannel,
clickHouseConnectionCacheEntity.getUserName(),
clickHouseConnectionCacheEntity.getPassword(),
ckServiceTimeout
);
}
@NotNull
@Override
public ClickHouseConnectionCacheEntity getClickHouseConnectionCacheEntity() {
return getClickHouseConnectionCacheEntities().get(
CLICK_HOUSE_DEFAULT_DATASOURCE_NAME
);
}
@NotNull
@Override
public Map<String, ClickHouseConnectionCacheEntity> getClickHouseConnectionCacheEntities() {
Map<String, ClickHouseConnectionCacheEntity> clickHouseConnectionCacheEntities = CLICK_HOUSE_CONNECTION_CACHE_ENTITIES;
if (clickHouseConnectionCacheEntities == null) {
synchronized (SplClickHouseResourceService.class) {
if (CLICK_HOUSE_CONNECTION_CACHE_ENTITIES == null) {
clickHouseConnectionCacheEntities = new HashMap<>(0);
ClickHouseDataSources clickHouseDataSources = this.getClickHouseDataSources();
if (clickHouseDataSources != null) {
List<ClickHouseConnectionCacheEntity> dataSources = clickHouseDataSources.getDataSources();
if (dataSources != null) {
for (ClickHouseConnectionCacheEntity clickHouseConnectionCacheEntity : dataSources) {
clickHouseConnectionCacheEntities.put(
clickHouseConnectionCacheEntity.getDataSourceName(),
clickHouseConnectionCacheEntity
);
}
}
}
CLICK_HOUSE_CONNECTION_CACHE_ENTITIES = Collections.unmodifiableMap(
clickHouseConnectionCacheEntities
);
} else {
clickHouseConnectionCacheEntities = CLICK_HOUSE_CONNECTION_CACHE_ENTITIES;
}
return clickHouseConnectionCacheEntities;
}
} else {
return clickHouseConnectionCacheEntities;
}
}
@Override
public void afterPropertiesSet() {
ckServiceGrpcManagedChannel = ManagedChannelBuilder
.forAddress(this.ckServiceHost, this.ckServicePort)
.usePlaintext()
.build();
}
}
5. 连接多数据源
@CustomLog
@Component
public class DynamicClickHouseMybatisDataSourceComponent implements InitializingBean {
private static final String STRING_CLIENT_NAME = "client_name";
@Setter(onMethod_ = @Autowired)
private SplClickHouseResourceService splClickHouseResourceService;
@Setter(onMethod_ = @Autowired)
private DynamicRoutingDataSource dynamicRoutingDataSource;
@Setter(onMethod_ = @Autowired)
private DruidDataSourceCreator druidDataSourceCreator;
@Value("${ngsoc.ClickHouse.maxActive:200}")
private Integer maxActive;
/**
* 设置每5分钟更新一次
*/
@Scheduled(cron = "0 */5 * * * ?")
public void updateUserNameAndPasswordFromMongodb() {
Map<String, ClickHouseConnectionCacheEntity> clickHouseConnectionCacheEntities = splClickHouseResourceService.getClickHouseConnectionCacheEntities();
for (Map.Entry<String, ClickHouseConnectionCacheEntity> entry : clickHouseConnectionCacheEntities.entrySet()) {
String dataSourceName = entry.getKey();
ClickHouseConnectionCacheEntity clickHouseConnectionCacheEntity = entry.getValue();
DruidDataSource druidDataSource = null;
try {
DataSource dataSource = dynamicRoutingDataSource.getCurrentDataSources().get(dataSourceName);
if (dataSource instanceof DruidDataSource && ((DruidDataSource) dataSource).isClosed()) {
try {
dynamicRoutingDataSource.getCurrentDataSources().remove(dataSourceName);
} catch (Exception e2) {
log.error("移除数据源失败", e2);
}
dataSource = null;
}
if (dataSource == null) {
log.info("正在添加mybatis plus CK数据源");
DataSourceProperty dataSourceProperty = new DataSourceProperty();
dataSourceProperty.setLazy(true);
dataSourceProperty.setUrl(
"jdbc:clickhouse://"
+ clickHouseConnectionCacheEntity.getClickHouseHost()
+ ":"
+ clickHouseConnectionCacheEntity.getClickHousePort()
+ "/business"
);
dataSourceProperty.setDriverClassName("com.clickhouse.jdbc.ClickHouseDriver");
dataSource = druidDataSourceCreator.createDataSource(dataSourceProperty);
druidDataSource = (DruidDataSource) dataSource;
Properties properties = new Properties();
properties.put(STRING_CLIENT_NAME, splClickHouseResourceService.buildSplClickHouseResource().getClientNameSupplier().get());
properties.put(ClickHouseClientOption.CLIENT_NAME.getKey(), splClickHouseResourceService.buildSplClickHouseResource().getClientNameSupplier().get());
properties.put(ClickHouseClientOption.CONNECTION_TIMEOUT.getKey(), 60 * 1000);
properties.put(ClickHouseClientOption.SESSION_TIMEOUT.getKey(), 60 * 1000);
properties.put(ClickHouseClientOption.SOCKET_TIMEOUT.getKey(), 60 * 1000);
druidDataSource.setKeepAlive(false);
druidDataSource.setInitialSize(1);
druidDataSource.setMinIdle(0);
druidDataSource.setMaxActive(maxActive);
druidDataSource.setMaxWait(30000);
druidDataSource.setTimeBetweenEvictionRunsMillis(5000);
druidDataSource.setMinEvictableIdleTimeMillis(30000);
druidDataSource.setTestWhileIdle(true);
druidDataSource.setTestOnBorrow(false);
druidDataSource.setTestOnReturn(false);
druidDataSource.setConnectProperties(properties);
druidDataSource.setUserCallback(
new NameCallback("ClickHouseUserName") {
/**
* Get the retrieved name.
*
* @return the retrieved name (which may be null)
*
* @see #setName
*/
@Override
public String getName() {
return clickHouseConnectionCacheEntity.getUserName();
}
}
);
druidDataSource.setPasswordCallback(
new PasswordCallback("ClickHouseUserPassword", false) {
/**
* Get the retrieved password.
*
* <p>
* This method returns a copy of the retrieved password.
*
* @return the retrieved password, which may be null.
*
* @see #setPassword
*/
@Override
public char[] getPassword() {
return Objects.requireNonNull(clickHouseConnectionCacheEntity.getPassword()).toCharArray();
}
}
);
druidDataSource.setValidationQuery("SELECT 1");
druidDataSource.init();
checkDruidDataSource(druidDataSource);
dynamicRoutingDataSource.addDataSource(dataSourceName, druidDataSource);
} else {
druidDataSource = (DruidDataSource) dataSource;
Properties properties = druidDataSource.getConnectProperties();
String newClientName = splClickHouseResourceService.buildSplClickHouseResource().getClientNameSupplier().get();
if (!Objects.equals(newClientName, properties.get(STRING_CLIENT_NAME))) {
properties.put(STRING_CLIENT_NAME, newClientName);
}
checkDruidDataSource(druidDataSource);
properties.put(ClickHouseClientOption.CLIENT_NAME.getKey(), splClickHouseResourceService.buildSplClickHouseResource().getClientNameSupplier().get());
}
} catch (Exception e) {
log.error("更新连接信息失败", e);
if (druidDataSource != null) {
try {
druidDataSource.close();
} catch (Exception e2) {
log.error("关闭连接失败", e2);
}
}
try {
dynamicRoutingDataSource.getCurrentDataSources().remove(dataSourceName);
} catch (Exception e2) {
log.error("移除数据源失败", e2);
}
}
}
}
private static void checkDruidDataSource(@NotNull DruidDataSource druidDataSource) throws SQLException {
DruidPooledConnection druidPooledConnection = null;
try {
druidPooledConnection = druidDataSource.getConnection();
druidPooledConnection.checkState();
PreparedStatement preparedStatement = druidPooledConnection.prepareStatement(
"SELECT 1"
);
ResultSet resultSet = preparedStatement.executeQuery();
if (!resultSet.next() || resultSet.getInt(1) != 1) {
throw new RuntimeException("hunt_mybatis_ck检验失败");
}
try {
resultSet.close();
} catch (Exception ignored) {
}
try {
preparedStatement.close();
} catch (Exception ignored) {
}
log.info("hunt_mybatis_ck检验成功");
} finally {
Objects.requireNonNull(druidPooledConnection).recycle();
}
}
@Override
public void afterPropertiesSet() {
this.updateUserNameAndPasswordFromMongodb();
}
}
6. 使用不同数据源账号查询
public interface IncidentCommonConstants {
interface DatasourceConstant {
String CLICK_HOUSE = "clickHouse";
String CLICK_HOUSE_WEIXIN = "clickHouse_weixin";
String CLICK_HOUSE_SCREEN = "clickHouse_screen";
String CLICK_HOUSE_DEVICE = "clickHouse_device";
}
}
@Repository
public interface EndpointBehaviorLogMapper {
/**
* 获取当前时间范围内的所有日志数量
*
* @param tenantId 租户id
* @param startTime 查询起始时间
* @param endTime 查询结束时间
* @return 查询到的数量
*/
@DS(IncidentCommonConstants.DatasourceConstant.CLICK_HOUSE_WEIXIN)
Long queryLogCountByTime(@Param("tenant") String tenantId, @Param("startTime") long startTime, @Param("endTime") long endTime);
}
@Repository
public interface EndpointSecurityLogMapper {
/**
* 获取日志设备统计
*
* @param tenantId 租户id
* @param startTime 查询起始时间
* @param endTime 查询
* @return 统计结果
*/
@DS(IncidentCommonConstants.DatasourceConstant.CLICK_HOUSE_SCREEN)
List<LogCountDto> queryLogCount(@Param("tenant") String tenantId, @Param("startTime") long startTime, @Param("endTime") long endTime);
/**
* 获取当前时间范围内的所有日志数量
*
* @param tenantId 租户id
* @param startTime 查询起始时间
* @param endTime 查询结束时间
* @return 查询到的数量
*/
@DS(IncidentCommonConstants.DatasourceConstant.CLICK_HOUSE_DEVICE)
Long queryLogCountByTime(@Param("tenant") String tenantId, @Param("startTime") long startTime, @Param("endTime") long endTime);
}
@Override
public FusionAlertVo countFusionAlerts(FusionAlertQo fusionAlertQo) {
FusionAlertVo fusionAlertVo = new FusionAlertVo();
// ....
List<SaasThreadContextDataHolder> threadContextDataHolders = SaasThreadContextUtil.save();
// 查询日志信息
Date beginDate = timeRange.getBeginDate();
long startTimeStamp = beginDate == null ? 0L : beginDate.getTime() / CommonConstants.DateConstants.MS_PER_SECOND;
long endTimeStamp = timeRange.getEndDate().getTime() / CommonConstants.DateConstants.MS_PER_SECOND;
String tenantId = Objects.requireNonNull(TenantInfoContext.getTenantInfo()).getProjectId().toString();
CompletableFuture<Long> networkSecurityLogCountFuture = CompletableFuture.supplyAsync(() -> {
Long networkSecurityLogCount = 0L;
try {
SaasThreadContextUtil.load(threadContextDataHolders);
StopWatch stopWatch = new StopWatch();
stopWatch.start("AlertDaoImpl network security log query count");
networkSecurityLogCount = networkSecurityLogMapper.queryLogCountByTime(tenantId, startTimeStamp, endTimeStamp);
stopWatch.stop();
log.info("{} time = {} ms", stopWatch.getLastTaskName(), stopWatch.getLastTaskTimeMillis());
return networkSecurityLogCount;
} catch (Exception e) {
log.error("networkSecurityLogCountFuture, get logCount failed {}", e);
return networkSecurityLogCount;
} finally {
SaasThreadContextUtil.remove();
}
},
THREAD_POOL_EXECUTOR
);
CompletableFuture<Long> endpointSecurityLogCountFuture = CompletableFuture.supplyAsync(() -> {
Long endpointSecurityLogCount = 0L;
try {
SaasThreadContextUtil.load(threadContextDataHolders);
StopWatch stopWatch = new StopWatch();
stopWatch.start("AlertDaoImpl endpoint security log query count");
endpointSecurityLogCount = endpointSecurityLogMapper.queryLogCountByTime(tenantId, startTimeStamp, endTimeStamp);
stopWatch.stop();
log.info("{} time = {} ms", stopWatch.getLastTaskName(), stopWatch.getLastTaskTimeMillis());
return endpointSecurityLogCount;
} catch (Exception e) {
log.error("endpointSecurityLogCountFuture, get logCount failed {}", e);
return endpointSecurityLogCount;
} finally {
SaasThreadContextUtil.remove();
}
},
THREAD_POOL_EXECUTOR
);
CompletableFuture<Long> endpointBehaviorLogCountFuture = CompletableFuture.supplyAsync(() -> {
Long endpointBehaviorLogCount = 0L;
try {
SaasThreadContextUtil.load(threadContextDataHolders);
StopWatch stopWatch = new StopWatch();
stopWatch.start("AlertDaoImpl endpoint behavior log query count");
endpointBehaviorLogCount = endpointBehaviorLogMapper.queryLogCountByTime(tenantId, startTimeStamp, endTimeStamp);
stopWatch.stop();
log.info("{} time = {} ms", stopWatch.getLastTaskName(), stopWatch.getLastTaskTimeMillis());
return endpointBehaviorLogCount;
} catch (Exception e) {
log.error("endpointBehaviorLogCountFuture, get logCount failed {}", e);
return endpointBehaviorLogCount;
} finally {
SaasThreadContextUtil.remove();
}
},
THREAD_POOL_EXECUTOR
);
try {
long networkSecurityLogCount = networkSecurityLogCountFuture.get();
long endpointSecurityLogCount = endpointSecurityLogCountFuture.get();
long endpointBehaviorLogCount = endpointBehaviorLogCountFuture.get();
fusionAlertVo.setOriginLogCount(networkSecurityLogCount + endpointSecurityLogCount + endpointBehaviorLogCount);
} catch (InterruptedException | ExecutionException e) {
log.warn("获取融合告警数据异常:{}", fusionAlertVo, e);
}
return fusionAlertVo;
}
如果想看具体是哪个数据源账号调用的mapper接口,可以在源码 DynamicDataSourceAnnotationInterceptor 类上打上断点:
public class DynamicDataSourceAnnotationInterceptor implements MethodInterceptor {
private static final String DYNAMIC_PREFIX = "#";
private final DataSourceClassResolver dataSourceClassResolver;
private final DsProcessor dsProcessor;
public DynamicDataSourceAnnotationInterceptor(Boolean allowedPublicOnly, DsProcessor dsProcessor) {
this.dataSourceClassResolver = new DataSourceClassResolver(allowedPublicOnly);
this.dsProcessor = dsProcessor;
}
public Object invoke(MethodInvocation invocation) throws Throwable {
String dsKey = this.determineDatasourceKey(invocation);
DynamicDataSourceContextHolder.push(dsKey);
Object var3;
try {
var3 = invocation.proceed();
} finally {
DynamicDataSourceContextHolder.poll();
}
return var3;
}
private String determineDatasourceKey(MethodInvocation invocation) {
String key = this.dataSourceClassResolver.findDSKey(invocation.getMethod(), invocation.getThis());
return !key.isEmpty() && key.startsWith("#") ? this.dsProcessor.determineDatasource(invocation, key) : key;
}
}