doris中间件搭配java idea 使用
目录结构:
properties文件内容
mysql.datasource.doris.url=jdbc:mysql://doris-ip:端口号/数据库名字?allowMultiQueries=true&useSSL=false&rewriteBatchedStatements=true&multiStatementAllow=true
mysql.datasource.doris.username=root
mysql.datasource.doris.password=
mysql.datasource.doris.driver-class-name=com.mysql.jdbc.Driver
mysql.datasource.doris.minIdle=1
mysql.datasource.doris.maxActive=3
mysql.datasource.doris.initialSize=1
mysql.datasource.doris.maxWait=60000
mysql.datasource.doris.timeBetweenEvictionRunsMillis=60000
mysql.datasource.doris.minEvictableIdleTimeMillis=300000
mysql.datasource.doris.validationQuery=select 'x'
mysql.datasource.doris.testWhileIdle=true
mysql.datasource.doris.testOnBorrow=false
mysql.datasource.doris.testOnReturn=false
mysql.datasource.doris.poolPreparedStatements=true
mysql.datasource.doris.maxPoolPreparedStatementPerConnectionSize=50
mysql.datasource.doris.removeAbandoned=true
mysql.datasource.doris.filters=stat
mybatis.mapper-locations=classpath*:/mapper/*.xml
mysql.datasource.configLocation=classpath:mybatis-config.xml
mybatis-config.xml 内容
<?xml version="1.0" encoding="UTF-8" ?>
<!-- mybatis的配置文件 -->
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<setting name="lazyLoadingEnabled" value="false"/>
<setting name="defaultStatementTimeout" value="25000"/>
<!-- <setting name="logPrefix" value="jd.wl.mybatis."/>-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
<!-- <setting name="logImpl" value="STDOUT_LOGGING"/>-->
</settings>
</configuration>
@Target({ElementType.FIELD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DorisLoad {
/**
* @return 文件中排序,不能重复
*/
int sort() default 0;
/**
* @return 对应列名
*/
String col() default "";
/**
* @return 对应表名
*/
String table() default "";
/**
* @return 对应表名
*/
String defaultValue() default "";
}
import lombok.Data;
import org.springframework.beans.factory.annotation.Configurable;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;
import java.util.Map;
@Data
@Configuration
@EnableAutoConfiguration
@ConfigurationProperties(prefix = "mysql.datasource")
public class DataSourceProperties {
private Map<String, String> doris;
}
import cn.hutool.db.ds.druid.DruidDSFactory;
import com.alibaba.druid.pool.DruidDataSource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import javax.annotation.Resource;
import java.sql.SQLData;
import java.sql.SQLException;
import java.time.Duration;
import java.util.Map;
@Slf4j
@Configuration
@EnableAutoConfiguration
public class DataSourceConfiguration {
@Resource
private DataSourceProperties dataSourceProperties;
public static DruidDataSource getDataSource(Map<String, String> params) throws SQLException{
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(params.get("url"));
datasource.setUsername(params.get("username"));
datasource.setPassword(params.get("password"));
if (params.containsKey("initialSize")) {
datasource.setInitialSize(Integer.parseInt(params.get("initialSize")));
}
if (params.containsKey("minIdle")) {
datasource.setMinIdle(Integer.parseInt(params.get("minIdle")));
}
if (params.containsKey("maxActive")) {
datasource.setMaxActive(Integer.parseInt(params.get("maxActive")));
}
if (params.containsKey("maxWait")) {
datasource.setMaxWait(Long.parseLong(params.get("maxWait")));
}
if (params.containsKey("timeBetweenEvictionRunsMillis")) {
datasource.setTimeBetweenEvictionRunsMillis(Long.parseLong(params.get("timeBetweenEvictionRunsMillis")));
}
if (params.containsKey("minEvictableIdleTimeMillis")) {
datasource.setMinEvictableIdleTimeMillis(Long.parseLong(params.get("minEvictableIdleTimeMillis")));
}
if (params.containsKey("validationQuery")) {
datasource.setValidationQuery(params.get("validationQuery"));
}
if (params.containsKey("testWhileIdle")) {
datasource.setTestWhileIdle(Boolean.parseBoolean(params.get("testWhileIdle")));
}
if (params.containsKey("testOnBorrow")) {
datasource.setTestOnBorrow(Boolean.parseBoolean(params.get("testOnBorrow")));
}
if (params.containsKey("testOnReturn")) {
datasource.setTestOnBorrow(Boolean.parseBoolean(params.get("testOnReturn")));
}
if (params.containsKey("poolPreparedStatements")) {
datasource.setPoolPreparedStatements(Boolean.parseBoolean(params.get("poolPreparedStatements")));
}
if (params.containsKey("maxPoolPreparedStatementPerConnectionSize")) {
datasource.setMaxPoolPreparedStatementPerConnectionSize(
Integer.parseInt(params.get("maxPoolPreparedStatementPerConnectionSize")));
}
if (params.containsKey("filters")) {
datasource.setFilters(params.get("filters"));
}
if (params.containsKey("connectionProperties")) {
datasource.setConnectionProperties(params.get("connectionProperties"));
}
return datasource;
}
@Bean( name = "dorisDataSource", initMethod = "init")
public DruidDataSource dorisDataSource(){
log.info("-------------------dorisDataSource init-----------------------");
try {
return getDataSource(dataSourceProperties.getDoris());
} catch (SQLException e) {
log.error("初始化 doris 数据库源失败", e);
}
return null;
}
}
下面这个是核心文件
import com.alibaba.druid.pool.DruidDataSource;
import io.swagger.annotations.Authorization;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
import org.example.doman.DataSourceType;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.DefaultResourceLoader;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.transaction.PlatformTransactionManager;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
@Slf4j
@Configuration
@AutoConfigureAfter(DataSourceConfiguration.class)
public class MybatisConfiguration {
@Value("${mybatis.mapper-locations}")
private String mapperLocations;
@Value("${mysql.datasource.configLocation}")
private String configLocation;
@javax.annotation.Resource
@Qualifier("predictDataSource")
private DruidDataSource predictDataSource;
@javax.annotation.Resource
@Qualifier("dorisDataSource")
private DruidDataSource dorisDataSource;
@Bean(name = "sqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(){
log.info("-------------------- sqlSessionFactory init ---------------------");
try {
SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
//设置mapper.xml文件所在位置
Resource[] resources = new PathMatchingResourcePatternResolver().getResources(mapperLocations);
sessionFactoryBean.setMapperLocations(resources);
//设置mybatis-config.xml配置文件位置
sessionFactoryBean.setConfigLocation(new DefaultResourceLoader().getResource(configLocation));
return sessionFactoryBean.getObject();
} catch (IOException e) {
log.error("mybatis resolver mapper*xml is error", e);
return null;
} catch (Exception e) {
log.error("mybatis sqlSessionFactoryBean create error", e);
return null;
}
}
//这个是用于多源数据的方法可以把这部分去掉
@Bean(name = "roundRobinDataSourceProxy")
public AbstractRoutingDataSource roundRobinDataSourceProxy() {
Map<Object, Object> targetDataSources = new HashMap<Object, Object>();
//把所有数据库都放在targetDataSources中,注意key值要和determineCurrentLookupKey()中代码写的一至,
//否则切换数据源时找不到正确的数据源
targetDataSources.put(DataSourceType.PREDICT.getType(), predictDataSource);
targetDataSources.put(DataSourceType.DORIS.getType(), dorisDataSource);
//路由类,寻找对应的数据源
AbstractRoutingDataSource proxy = new RoundRobinRoutingDataSource(2);
//默认库
proxy.setDefaultTargetDataSource(predictDataSource);
proxy.setTargetDataSources(targetDataSources);
return proxy;
}
@Bean
public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
/**
* 事务管理
* @param roundRobinDataSourceProxy
* @return
*/
@Bean
public PlatformTransactionManager transactionManager(AbstractRoutingDataSource roundRobinDataSourceProxy) {
return new DataSourceTransactionManager(roundRobinDataSourceProxy);
}
}
也是核心配置文化
package org.example.config;
import com.alibaba.cola.exception.BizException;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.codec.binary.Base64;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.http.HttpHeaders;
import org.apache.http.client.methods.CloseableHttpResponse;
import org.apache.http.client.methods.HttpPut;
import org.apache.http.entity.ContentType;
import org.apache.http.entity.FileEntity;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.DefaultRedirectStrategy;
import org.apache.http.impl.client.HttpClientBuilder;
import org.apache.http.impl.client.HttpClients;
import org.apache.http.util.EntityUtils;
import org.example.util.DateUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.PropertySource;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ReflectionUtils;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;
/**
* DorisStreamLoader
*
*/
@Component
@Slf4j
@PropertySource("classpath:application.properties")
@ConfigurationProperties(prefix = "doris.stream")
public class DorisStreamLoader {
/**
* doris状态
*/
private static final String DORIS_STATUS = "Status";
/**
* doris错误信息
*/
private static final String DORIS_MESSAGE = "Message";
/**
* doris总数量
*/
private static final String DORIS_TOTAL_ROWS = "NumberTotalRows";
/**
* doris成功数量
*/
private static final String DORIS_LOADED_ROWS = "NumberLoadedRows";
/**
*
*/
@Value("${doris.stream.domain}")
private String domain;
/**
* 数据库
*/
@Value("${doris.stream.db}")
private String db;
/**
* 用户
*/
@Value("${doris.stream.user}")
private String user;
/**
* 密码
*/
@Value("${doris.stream.password}")
private String password;
/**
* 最多重试5次,100次会jsf超时
*/
private final static int MAX_RETRY = 5;
private final static int RETRY_SLEEPMS = 5000;
/**
* 采用 stream load方式,将 doris数据对象 装入到 doris表中
*
* @param clazz 数据对象
* @param list 列表对象
*/
public <T> int load(Class<T> clazz, Collection<T> list) {
if (CollectionUtils.isEmpty(list)) {
return 0;
}
DorisLoad dorisLoad = clazz.getAnnotation(DorisLoad.class);
if (dorisLoad == null || StringUtils.isEmpty(dorisLoad.table())) {
throw new BizException("未设置对应doris表");
}
Field[] fields = clazz.getDeclaredFields();
List<Field> fieldList = Stream.of(fields)
.filter(field -> field.getAnnotation(DorisLoad.class) != null)
.sorted(
Comparator.comparing(
field -> field.getAnnotation(DorisLoad.class).sort()
)).collect(Collectors.toList());
StringBuilder colSb = new StringBuilder();
for (Field field : fieldList) {
colSb.append(field.getAnnotation(DorisLoad.class).col()).append(",");
field.setAccessible(true);
}
String cols = colSb.substring(0, colSb.length() - 1);
File file = null;
// 重试循环
boolean succeed = false;
int retry = 0;
int resultCount = 0;
while (!succeed && retry < MAX_RETRY) {
try {
file = buildContent(list, fieldList);
String rt = sendData(dorisLoad.table(), file, UUID.randomUUID().toString(), cols);
JSONObject jsonObject = JSON.parseObject(rt);
if (jsonObject != null) {
log.info("sendData dorisLoad.table():{},结果:{}", dorisLoad.table(), rt);
DorisLoadResultCo resultCo = getResult(jsonObject.getString(DORIS_STATUS),
jsonObject.getString(DORIS_MESSAGE),
jsonObject.getInteger(DORIS_TOTAL_ROWS),
jsonObject.getInteger(DORIS_LOADED_ROWS));
log.info("resultCo: {}", resultCo);
// 收到结果不验证,直接成功
succeed = true;
resultCount = resultCo.getLoadedQty();
}
} catch (Exception e) {
log.warn(String.format("数据上传异常, 重试次数 %s", retry), e);
try {
Thread.sleep(RETRY_SLEEPMS);
} catch (Exception e2) {
log.warn("", e2);
}
} finally {
retry++;
if (file != null) {
file.delete();
}
}
}
if (!succeed) {
throw new BizException(String.format("数据上传异常, 重试次数 %s", retry));
}
return resultCount;
}
public <T> File loadBuildContent(Class<T> clazz, Collection<T> list,File tempFile) {
if (CollectionUtils.isEmpty(list)) {
return null;
}
DorisLoad dorisLoad = clazz.getAnnotation(DorisLoad.class);
if (dorisLoad == null || StringUtils.isEmpty(dorisLoad.table())) {
throw new BizException("未设置对应doris表");
}
Field[] fields = clazz.getDeclaredFields();
List<Field> fieldList = Stream.of(fields)
.filter(field -> field.getAnnotation(DorisLoad.class) != null)
.sorted(
Comparator.comparing(
field -> field.getAnnotation(DorisLoad.class).sort()
)).collect(Collectors.toList());
StringBuilder colSb = new StringBuilder();
for (Field field : fieldList) {
colSb.append(field.getAnnotation(DorisLoad.class).col()).append(",");
field.setAccessible(true);
}
File file = null;
try {
file = buildContent(list, fieldList,tempFile);
} catch (Exception e) {
log.error("doris组装数据出现错误",e);
}
return file;
}
public <T> int batchload(Class<T> clazz, File file) {
if(Objects.isNull(file)){
return 0;
}
DorisLoad dorisLoad = clazz.getAnnotation(DorisLoad.class);
if (dorisLoad == null || StringUtils.isEmpty(dorisLoad.table())) {
throw new BizException("未设置对应doris表");
}
Field[] fields = clazz.getDeclaredFields();
List<Field> fieldList = Stream.of(fields)
.filter(field -> field.getAnnotation(DorisLoad.class) != null)
.sorted(
Comparator.comparing(
field -> field.getAnnotation(DorisLoad.class).sort()
)).collect(Collectors.toList());
StringBuilder colSb = new StringBuilder();
for (Field field : fieldList) {
colSb.append(field.getAnnotation(DorisLoad.class).col()).append(",");
field.setAccessible(true);
}
String cols = colSb.substring(0, colSb.length() - 1);
// 重试循环
boolean succeed = false;
int retry = 0;
int resultCount = 0;
while (!succeed && retry < MAX_RETRY) {
try {
String rt = sendData(dorisLoad.table(), file, UUID.randomUUID().toString(), cols);
JSONObject jsonObject = JSON.parseObject(rt);
if (jsonObject != null) {
log.info("sendData dorisLoad.table():{},结果:{}", dorisLoad.table(), rt);
DorisLoadResultCo resultCo = getResult(jsonObject.getString(DORIS_STATUS),
jsonObject.getString(DORIS_MESSAGE),
jsonObject.getInteger(DORIS_TOTAL_ROWS),
jsonObject.getInteger(DORIS_LOADED_ROWS));
log.info("resultCo: {}", resultCo);
// 收到结果不验证,直接成功
succeed = true;
resultCount = resultCo.getLoadedQty();
}
} catch (Exception e) {
log.warn(String.format("数据上传异常, 重试次数 %s", retry), e);
try {
Thread.sleep(RETRY_SLEEPMS);
} catch (Exception e2) {
log.warn("", e2);
}
} finally {
retry++;
if (file != null) {
file.delete();
}
}
}
if (!succeed) {
throw new BizException(String.format("数据上传异常, 重试次数 %s", retry));
}
return resultCount;
}
/**
* 获取文件内容
*
* @param dataList 传入数组
* @return 字符串
*/
private <T> File buildContent(Collection<T> dataList, List<Field> fieldList) throws Exception {
StringBuilder content = new StringBuilder();
dataList.forEach(
data -> {
StringBuilder sb = new StringBuilder();
fieldList.forEach(
field -> {
try {
sb.append(getFormatData(field, data)).append("\t");
} catch (Exception e) {
log.error("buildContent异常", e);
throw new RuntimeException("生成文档出错");
}
}
);
sb.replace(sb.length() - 1, sb.length(), "\n");
content.append(sb);
}
);
File file = File.createTempFile("doris_", "txt");
try (FileOutputStream fos = new FileOutputStream(file)) {
fos.write(content.toString().getBytes());
log.info("buildContent 生成文件 {} ", file.getAbsolutePath());
} catch (Exception e) {
log.error("buildContent生成文件异常 {}", file.getAbsolutePath());
file.delete();
}
return file;
}
private <T> File buildContent(Collection<T> dataList, List<Field> fieldList,File tempFile) throws Exception {
StringBuilder content = new StringBuilder();
dataList.forEach(
data -> {
StringBuilder sb = new StringBuilder();
fieldList.forEach(
field -> {
try {
sb.append(getFormatData(field, data)).append("\t");
} catch (Exception e) {
log.error("buildContent异常", e);
throw new RuntimeException("生成文档出错");
}
}
);
sb.replace(sb.length() - 1, sb.length(), "\n");
content.append(sb);
}
);
File file;
if(tempFile == null){
file= File.createTempFile("doris_", "txt");
try (FileOutputStream fos = new FileOutputStream(file)) {
fos.write(content.toString().getBytes());
log.info("buildContent 生成文件 {} ", file.getAbsolutePath());
} catch (Exception e) {
log.error("buildContent生成文件异常 {}", file.getAbsolutePath());
file.delete();
}
}else{
file=tempFile;
byte[] bytes = content.toString().getBytes();
FileUtils.writeByteArrayToFile(file,bytes,true);
log.info("buildContent 追加 {} ", file.getAbsolutePath());
}
return file;
}
/**
* 返回格式化后的值
*
* @param field 变量
* @param data 数据
* @param <T> 类型
* @return 结果
*/
private <T> Object getFormatData(Field field, T data) {
Object obj = ReflectionUtils.getField(field, data);
if (obj == null) {
return field.getAnnotation(DorisLoad.class).defaultValue();
}
if (obj instanceof String) {
return ((String) obj)
.replaceAll("\t", StringUtils.EMPTY)
.replaceAll("\n", StringUtils.EMPTY);
}
if (obj instanceof Date) {
return DateUtils.format((Date) obj, DateUtils.FORMAT_TIME);
}
return obj;
}
/**
* load公用方法
*
* @param table 表
* @param file 内容
* @param label 流水
* @throws Exception 异常
*/
private String sendData(String table, File file, String label, String column) throws Exception {
log.info("sendData start table:{},column:{}", table, column);
try {
return doSendData(table, file, label, column);
} catch (IOException e) {
try {
return doSendData(table, file, label, column);
} catch (Exception ex) {
log.error("doSendData Exception table:{},label:{},{}", table, label, ex);
throw ex;
}
} catch (Exception ex) {
log.error("doSendData Exception table:{},label:{},{}", table, label, ex);
throw ex;
}
}
/**
* load公用方法
*
* @param table 表
* @param file 内容
* @param label 流水
* @throws Exception 异常
*/
private String doSendData(String table, File file, String label, String column) throws Exception {
final HttpClientBuilder httpClientBuilder = HttpClients
.custom()
.setRedirectStrategy(new DefaultRedirectStrategy() {
@Override
protected boolean isRedirectable(String method) {
return true;
}
});
try (CloseableHttpClient client = httpClientBuilder.build()) {
try (CloseableHttpResponse response = client.execute(buildPut(table, file, label, column))) {
String loadResult = "";
if (response.getEntity() != null) {
loadResult = EntityUtils.toString(response.getEntity());
}
final int statusCode = response.getStatusLine().getStatusCode();
if (statusCode != 200) {
throw new IOException(
String.format("Stream load failed, statusCode=%s load result=%s", statusCode, loadResult));
}
log.info("sendData end table:{}", table);
return loadResult;
}
}
}
/**
* 获取http客户端
*
* @param table 表名
* @param file 内容
* @param label 流水
* @return 客户端
*/
private HttpPut buildPut(String table, File file, String label, String cols) {
HttpPut put = new HttpPut(getUrl(table));
FileEntity entity = new FileEntity(file, ContentType.APPLICATION_OCTET_STREAM);
put.setHeader(HttpHeaders.EXPECT, "100-continue");
put.setHeader(HttpHeaders.AUTHORIZATION, basicAuthHeader());
put.setHeader("label", label);
// 导入容错率设置为100%,即不会失败
put.setHeader("max_filter_ratio", "1");
if (StringUtils.isNotEmpty(cols)) {
put.setHeader("columns", cols);
}
put.setEntity(entity);
return put;
}
/**
* 加密账号
*
* @return 密文
*/
private String basicAuthHeader() {
final String tobeEncode = user + ":" + password;
byte[] encoded = Base64.encodeBase64(tobeEncode.getBytes(StandardCharsets.UTF_8));
return "Basic " + new String(encoded);
}
/**
* load的url
*
* @param table 表
* @return url
*/
private String getUrl(String table) {
return String.format("%s/api/%s/%s/_stream_load", domain, db, table);
}
/**
* 获取结果
*
* @param status 状态
* @param message 错误信息
* @param totalQty 总数量
* @param loadedQty 成功数量
* @return 结果
*/
private DorisLoadResultCo getResult(String status, String message, Integer totalQty, Integer loadedQty) {
return new DorisLoadResultCo(status, message, totalQty, loadedQty);
}
}