package com.kyexpress.common.data.connect.provider.service;
import java.io.IOException;
import java.util.Collection;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import java.util.TreeSet;
import java.util.concurrent.CompletionService;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorCompletionService;
import java.util.concurrent.Future;
import java.util.stream.Collectors;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.exception.ExceptionUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.elasticsearch.client.RestHighLevelClient;
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.scheduling.annotation.Async;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ArrayNode;
import com.fasterxml.jackson.databind.node.NullNode;
import com.fasterxml.jackson.databind.node.ObjectNode;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.kyexpress.common.data.connect.api.model.CompareRelation;
import com.kyexpress.common.data.connect.api.model.CompareResult;
import com.kyexpress.common.data.connect.api.model.CompareSource;
import com.kyexpress.common.data.connect.api.model.CompareTask;
import com.kyexpress.common.data.connect.api.model.DataSource;
import com.kyexpress.common.data.connect.api.model.JobInfo;
import com.kyexpress.common.data.connect.api.model.JobInfoItem;
import com.kyexpress.common.data.connect.api.model.ResourceTable;
import com.kyexpress.common.data.connect.provider.constant.ResponseCode;
import com.kyexpress.common.data.connect.provider.enums.CompareResultType;
import com.kyexpress.common.data.connect.provider.utils.CompareResultHelper;
import com.kyexpress.common.data.connect.provider.utils.ElasticUtils;
import com.kyexpress.common.data.connect.provider.utils.ObjectNodeListHandler;
import com.kyexpress.common.data.connect.provider.utils.Tools;
import com.kyexpress.common.data.connect.provider.utils.compare.DbCompareTask;
import com.kyexpress.common.data.connect.provider.utils.compare.EsCompareTask;
import com.kyexpress.common.data.worker.util.JobInfoHelper;
import com.kyexpress.common.data.worker.util.JobInfoItemHelper;
import com.kyexpress.common.data.worker.util.ResourceTableHelper;
import com.kyexpress.framework.exception.ApplicationException;
import com.kyexpress.framework.utils.Context;
import com.kyexpress.framework.utils.ContextUtils;
import com.kyexpress.framework.utils.EncryptUtils;
import com.zaxxer.hikari.HikariDataSource;
@Service(“compareDbAndEsService”)
public class CompareDbAndEsService {
private static final Logger LOGGER = LoggerFactory.getLogger(CompareDbAndEsService.class);
@Autowired
@Qualifier("compareTaskExecutor")
private ThreadPoolTaskExecutor compareTaskExecutor;
@Autowired
private JobInfoService jobInfoService;
@Autowired
private WorkerNodeJobService workerNodeJobService;
@Autowired
private ObjectMapper objectMapper;
@Autowired
private DataSourceService dataSourceService;
@Autowired
private CompareSourceService compareSourceService;
@Autowired
private CompareRelationService compareRelationService;
@Autowired
private ObjectNodeListHandler objectNodeListHandler;
@Autowired
private CompareResultService compareResultService;
@Autowired
private CompareTaskService compareTaskService;
@Autowired
private ElasticsearchHightService elasticsearchService;
@Async("compareTaskExecutor")
@Transactional(propagation = Propagation.SUPPORTS)
public void dbCompareEs(final CompareRelation compareRelation, final Context context) {
Long beginTime = System.currentTimeMillis();
ContextUtils.set(context);
final CompareTask compareTask;
try {
compareTask = compareTaskService.get(compareRelation.getCompareTaskId());
} catch (Exception e) {
// 正常情况下,都需要查到对比任务相关信息
LOGGER.error(e.getMessage(), e);
return;
}
// 基础数据构建
Long batchId = compareRelation.getCompareBatchId();
final JobInfo jobInfo = jobInfoService.get(compareTask.getJobId());
workerNodeJobService.fillJobInfo(jobInfo);
final Map<Long, DataSource> dbDataSourceMap = getDBDataSourceMap(jobInfo);
final Map<Long, HikariDataSource> connectionMap = getConnectionMap(dbDataSourceMap);
Map<String, List<DataSource>> dataMap = dataSourceMap(dbDataSourceMap);
List<Thread> threads = Lists.newArrayList();
try {
final JobInfoHelper jobInfoHelper = JobInfoHelper.newInstance(objectMapper, jobInfo);
int dbMaxSize = 9;
// 10库100表
CountDownLatch latch = new CountDownLatch(1000);
// 循环库
for (int i = 0; i <= dbMaxSize; i++) {
int finalI = i;
Thread thread = new Thread(() -> {
compareSourceIds(compareTaskExecutor, latch, compareRelation, compareTask, batchId, finalI, jobInfo,
dataMap, jobInfoHelper, connectionMap);
});
thread.start();
threads.add(thread);
}
latch.await();
compareRelation.setCompareBatchStatus(CompareRelation.COMPARE_BATCH_STATUS_FINISH);
compareRelation.setExceptionValue("");
updateBatchStatus(compareRelation);
} catch (Exception e) {
compareRelation.setCompareBatchStatus(CompareRelation.COMPARE_BATCH_STATUS_EXCEPTION);
compareRelation.setExceptionValue(StringUtils.substring(ExceptionUtils.getStackTrace(e), 0, 65535));
try {
updateBatchStatus(compareRelation);
} catch (Exception e1) {
LOGGER.error(e1.getMessage(), e1);
}
LOGGER.error(e.getMessage(), e);
} finally {
close(connectionMap.values());
for (Thread thread : threads) {
thread.interrupted();
}
}
LOGGER.info("任务名称:{},执行结束,共耗时{}毫秒->", compareTask.getCompareTaskName(),
(System.currentTimeMillis() - beginTime));
}
// 关闭数据源连接
private void close(Collection<HikariDataSource> values) {
for (HikariDataSource dataSource : values) {
IOUtils.closeQuietly(dataSource);
}
}
// 关闭es连接
private void closeEsClient(RestHighLevelClient elasticSearchClient) {
try {
if (null != elasticSearchClient) {
elasticSearchClient.close();
}
} catch (Exception e) {
LOGGER.error(e.getMessage(), e);
}
}
// 按照库进行处理
private void compareSourceIds(ThreadPoolTaskExecutor compareTaskExecutor, CountDownLatch latch,
CompareRelation compareRelation, CompareTask compareTask, Long batchId, int dbNumber, JobInfo jobInfo,
Map<String, List<DataSource>> dataMap, JobInfoHelper jobInfoHelper,
Map<Long, HikariDataSource> connectionMap) {
// 根据ids 循环00-99表查询数据
for (int i = 0; i <= 99; i++) {
try {
if (!CompareRelation.COMPARE_BATCH_STATUS_RUN
.equals(compareRelationService.getCompareBatchStatus(compareRelation))) {
continue;
}
// 根据表和库获取数据
String sourceTable = String.format("%02d", i);
String sourceDb = String.valueOf(dbNumber);
String sourceId = String.valueOf(batchId).concat("-").concat(sourceDb).concat(sourceTable);
String tableName = "compare_source".concat(sourceDb).concat(sourceTable);
int pageSize = 256;
int num = 1;
while (true) {
List<CompareSource> list = compareSourceService.selectCompareSourceList(sourceId,
(num - 1) * pageSize, pageSize, tableName);
if (list.isEmpty()) {
break;
}
List<String> ids = list.stream().map(CompareSource::getCompareValue).collect(Collectors.toList());
compareData(compareTaskExecutor, compareTask, batchId, compareRelation, ids, jobInfo, dbNumber, i,
dataMap, jobInfoHelper, connectionMap);
// 大于256继续循环下去
if (list.size() < pageSize) {
break;
}
num++;
}
} catch (Exception e) {
LOGGER.error(e.getMessage(), e);
throw new ApplicationException(ResponseCode.COMPARE_TASK_EXCEPTION);
} finally {
latch.countDown();
LOGGER.info("CountDown大小:{}->", latch.getCount());
}
}
}
private List<CompareResult> getCompareResultList(List<String> ids, Long taskId, Long barchId) {
List<CompareResult> compareResultList = Lists.newArrayList();
CompareResult compareResult = null;
for (String compareId : ids) {
compareResult = new CompareResult();
compareResult.setCompareTaskId(taskId);
compareResult.setCompareBatchId(barchId);
compareResult.setCompareId(compareId);
compareResultList.add(compareResult);
}
return compareResultList;
}
/**
* 根据库下表和表下标获取相对应的数据id
*
* @param taskId
* @param batchId
* @param compareRelation
* @param elasticSearchClient
* @param ids
* @param jobInfo
* @param dbNumber
* @param tableNumber
* @param dataMap
* @param jobInfoHelper
* @param connectionMap
*/
private void compareData(ThreadPoolTaskExecutor compareTaskExecutor, CompareTask compareTask, Long batchId,
CompareRelation compareRelation, List<String> ids, JobInfo jobInfo, int dbNumber, int tableNumber,
Map<String, List<DataSource>> dataMap, JobInfoHelper jobInfoHelper,
Map<Long, HikariDataSource> connectionMap) {
final long batchBeginTime = System.currentTimeMillis();
final List<Future<Object>> futureList = new LinkedList<>();
// 根据ids 查询es数据
final List<CompareResult> compareResultList = getCompareResultList(ids, compareTask.getId(), batchId);
final Map<String, CompareResultHelper> compareResultMap = getCompareResultMap(compareResultList);
// 根据库获取 到对应的数据源
List<DataSource> dataSources = dataMap.get(String.valueOf(dbNumber));
final RestHighLevelClient elasticSearchClient = getESClient(jobInfo);
final CompletionService<Object> completionService = new ExecutorCompletionService<>(compareTaskExecutor);
try {
// 数据源并行处理
// 查询db数据
futureList.add(completionService.submit(new EsCompareTask(elasticSearchClient, jobInfo, compareResultMap)));
for (DataSource vo : dataSources) {
futureList.add(completionService.submit(new DbCompareTask(connectionMap, compareTaskExecutor, vo,
objectNodeListHandler, objectMapper, jobInfoHelper, compareResultMap, tableNumber)));
}
for (Future<Object> future : futureList) {
future.get();
}
// 生成 DB Data
for (CompareResultHelper compareResultHelper : compareResultMap.values()) {
final ObjectNode dbData = objectMapper.createObjectNode();
final ObjectNode showDBData = objectMapper.createObjectNode();
int dbCount = 0;
for (Long tableId : compareResultHelper.getRowMap().keySet()) {
final ResourceTableHelper resourceTableHelper = jobInfoHelper.getResourceTableHelper(tableId);
for (ObjectNode row : compareResultHelper.getRowMap().get(tableId)) {
final String schema = Tools.asText(row, "schema", "unknown");
final String table = Tools.asText(row, "table", "unknown");
final JsonNode data = row.get("data");
resourceTableHelper.setValue(dbData, data, null, "INSERT");
dbCount++;
ObjectNode schemaNode = (ObjectNode) showDBData.get(schema);
if (schemaNode == null) {
schemaNode = showDBData.objectNode();
showDBData.set(schema, schemaNode);
}
ArrayNode tableNode = (ArrayNode) schemaNode.get(table);
if (tableNode == null) {
tableNode = schemaNode.arrayNode();
schemaNode.set(table, tableNode);
}
tableNode.add(data);
}
}
compareResultHelper.setShowDBData(showDBData);
compareResultHelper.setDBData(dbData);
compareResultHelper.getCompareResult().setCompareDbNum(Long.valueOf((long) dbCount));
}
final List<CompareResult> insertList = new LinkedList<>();
for (CompareResultHelper compareResultHelper : compareResultMap.values()) {
compare(jobInfoHelper, compareResultHelper);
final CompareResult compareResult = compareResultHelper.getCompareResult();
if (!CompareResult.COMPARE_STATUS_EQUAL.equals(compareResult.getCompareStatus())) {
compareResult.setId(null);
insertList.add(compareResult);
}
}
if (!insertList.isEmpty()) {
if (CompareResultType.ES.getCode().equals(compareTask.getCompareResultType())) {
// 存储es
elasticsearchService.insertBatchCompareResult(insertList);
} else {
// 存储mysql
compareResultService.insertBatch(insertList);
}
}
final long batchEndTime = System.currentTimeMillis();
if (LOGGER.isInfoEnabled()) {
LOGGER.info("[compareDbAndEsService][compare]ES-DB数据耗时{}毫秒", (batchEndTime - batchBeginTime));
}
} catch (Exception e) {
throw new ApplicationException(ResponseCode.COMPARE_TASK_EXCEPTION);
} finally {
closeEsClient(elasticSearchClient);
}
}
/**
* 根据比对任务获取抽取任务所关联的数据源
*
* @param dbDataSourceMap
* @return
*/
private Map<Long, HikariDataSource> getConnectionMap(final Map<Long, DataSource> dbDataSourceMap) {
final Map<Long, HikariDataSource> connectionMap = new ConcurrentHashMap<>(dbDataSourceMap.size() * 2);
for (DataSource dbDataSource : dbDataSourceMap.values()) {
HikariDataSource hikariDataSource = new HikariDataSource();
hikariDataSource.setDriverClassName("com.mysql.jdbc.Driver");
hikariDataSource.setMaximumPoolSize(64);
hikariDataSource.setMinimumIdle(0);
hikariDataSource.setUsername(dbDataSource.getDataSourceUser());
hikariDataSource.setPassword(EncryptUtils.aesDecrypt(dbDataSource.getDataSourcePasswd()));
hikariDataSource.setJdbcUrl(dbDataSource.getDataSourceAddress());
connectionMap.put(dbDataSource.getId(), hikariDataSource);
}
return connectionMap;
}
/**
* 更新状态
*
* @param compareRelation
*/
private void updateBatchStatus(final CompareRelation compareRelation) {
final Long compareTaskId = compareRelation.getCompareTaskId();
final Long compareBatchId = compareRelation.getCompareBatchId();
final CompareRelation dbCompareRelation = getByCompareTaskIdAndCompareBatchId(compareTaskId, compareBatchId);
final CompareRelation updateCompareRelation = new CompareRelation();
updateCompareRelation.setId(dbCompareRelation.getId());
updateCompareRelation.setCompareBatchStatus(compareRelation.getCompareBatchStatus());
updateCompareRelation.setExceptionValue(compareRelation.getExceptionValue());
compareRelationService.updateSelective(updateCompareRelation);
final CompareTask updateCompareTask = new CompareTask();
updateCompareTask.setId(compareTaskId);
updateCompareTask.setCompareBatchStatus(compareRelation.getCompareBatchStatus());
compareTaskService.updateSelective(updateCompareTask);
}
/**
*
* @param compareTaskId
* @param compareBatchId
* @return
*/
private CompareRelation getByCompareTaskIdAndCompareBatchId(Long compareTaskId, Long compareBatchId) {
final CompareRelation query = new CompareRelation();
query.setCompareTaskId(compareTaskId);
query.setCompareBatchId(compareBatchId);
final List<CompareRelation> list = compareRelationService.selectAll(query);
return Tools.removeFirst(list);
}
/**
*
* @param compareResultList
* @return
*/
private Map<String, CompareResultHelper> getCompareResultMap(List<CompareResult> compareResultList) {
final Map<String, CompareResultHelper> map = new ConcurrentHashMap<>(compareResultList.size() * 2);
for (CompareResult compareResult : compareResultList) {
final CompareResultHelper helper = new CompareResultHelper(compareResult);
helper.setObjectMapper(objectMapper);
map.put(compareResult.getCompareId(), helper);
}
return map;
}
/**
*
* @param jobInfo
* @return
*/
private Map<Long, DataSource> getDBDataSourceMap(final JobInfo jobInfo) {
final Map<Long, DataSource> dbDataSourceMap = new TreeMap<>();
final List<ResourceTable> resourceTableList = jobInfo.getResourceTableList();
for (ResourceTable resourceTable : resourceTableList) {
final Set<Long> dataSourceIDSet = getRelationDataSourceIDSet(resourceTable);
dataSourceIDSet.removeAll(dbDataSourceMap.keySet());
for (Long dataSourceID : dataSourceIDSet) {
DataSource dbDataSource = dataSourceService.get(dataSourceID);
dbDataSourceMap.put(dbDataSource.getId(), dbDataSource);
}
}
return dbDataSourceMap;
}
/**
* 创建Job ES连接
*
* @param jobInfo
* @return
*/
private RestHighLevelClient getESClient(final JobInfo jobInfo) {
DataSource elasticSearchDataSource = jobInfo.getDataSourceList().stream()
.filter(dataSources -> jobInfo.getDataSourceId().longValue() == dataSources.getId()).findFirst().get();
final RestHighLevelClient elasticSearchClient = ElasticUtils.createElasticSearchClient(elasticSearchDataSource);
return elasticSearchClient;
}
private Set<Long> getRelationDataSourceIDSet(final ResourceTable resourceTable) {
if (resourceTable == null) {
return new TreeSet<>();
}
final String[] split = StringUtils.split(resourceTable.getRelationDataSource(), ',');
if (split == null) {
return new TreeSet<>();
}
final Set<Long> idSet = new TreeSet<>();
for (String dataSourceIDString : split) {
dataSourceIDString = StringUtils.trimToEmpty(dataSourceIDString);
final long dataSourceID = NumberUtils.toLong(dataSourceIDString, 0);
if (dataSourceID > 0) {
idSet.add(Long.valueOf(dataSourceID));
}
}
return idSet;
}
// 按照dbNumber进行区分
private Map<String, List<DataSource>> dataSourceMap(Map<Long, DataSource> dbDataSourceMap) {
Map<String, List<DataSource>> dataSourceMap = Maps.newConcurrentMap();
List<DataSource> list = null;
for (int i = 0; i < 10; i++) {
list = Lists.newArrayList();
for (DataSource dataSource : dbDataSourceMap.values()) {
String url = dataSource.getDataSourceAddress();
String dataBaseName = url.substring(url.lastIndexOf("/") + 1, url.lastIndexOf("?"));
// 不是分库的也要添加进去
if (!checkIsPartionDb(dataBaseName)) {
list.add(dataSource);
}
// 数据源属于当前库
if (checkIsNumber(i, dataBaseName)) {
list.add(dataSource);
}
}
if (!list.isEmpty()) {
dataSourceMap.put(String.valueOf(i), list);
}
}
return dataSourceMap;
}
// 校验是否分库
private boolean checkIsPartionDb(String str) {
if (StringUtils.isNotBlank(str)) {
String index = str.substring(str.lastIndexOf("_") + 1, str.length());
if (StringUtils.isNumeric(index)) {
return true;
}
}
return false;
}
// 校验是不是当前dbNumber
private boolean checkIsNumber(int dbNumber, String str) {
if (StringUtils.isNotBlank(str)) {
String index = str.substring(str.lastIndexOf("_") + 1, str.length());
if (StringUtils.isNumeric(index) && dbNumber == Integer.valueOf(index)) {
return true;
}
}
return false;
}
private void compare(final JobInfoHelper jobInfoHelper, final CompareResultHelper compareResultHelper)
throws IOException {
final CompareResult compareResult = compareResultHelper.getCompareResult();
final ObjectNode dbData = compareResultHelper.getDBData();
final ObjectNode esData = compareResultHelper.getESData();
if (Tools.defaultLong(compareResult.getCompareDbNum()) < 1
&& Tools.defaultLong(compareResult.getCompareEsNum()) < 1) {
compareResult.setDbValue(objectMapper.writeValueAsString(compareResultHelper.getShowDBData()));
compareResult.setEsValue(objectMapper.writeValueAsString(esData));
compareResult.setCompareStatus(CompareResult.COMPARE_STATUS_EXCEPTION);
compareResult.setDifferenceFieldNum(Long.valueOf(0L));
compareResult.setDifferenceValue("{}");
return;
}
final ObjectNode differenceValue = objectMapper.createObjectNode();
long differenceFieldNum = 0;
for (JobInfoItemHelper jobInfoItemHelper : jobInfoHelper.getJobInfoItemHelperList()) {
if (!jobInfoItemHelper.isSubscribe()) {
continue;
}
final String path = jobInfoItemHelper.getPath();
final List<String> pathList = jobInfoItemHelper.getPathList();
final JsonNode dbValue = getValue(dbData, pathList);
final JsonNode esValue = getValue(esData, pathList);
if (dbValue instanceof NullNode && esValue instanceof NullNode) {
continue;
}
if (dbValue instanceof NullNode) {
final ObjectNode objectNode = createFieldDifferent(jobInfoItemHelper, dbValue, esValue);
differenceValue.set(path, objectNode);
differenceFieldNum++;
continue;
}
if (esValue instanceof NullNode) {
final ObjectNode objectNode = createFieldDifferent(jobInfoItemHelper, dbValue, esValue);
differenceValue.set(path, objectNode);
differenceFieldNum++;
continue;
}
if (isNumber(jobInfoItemHelper.getJobInfoItem())) {
final double dbDouble = dbValue.asDouble(0D);
final double esDouble = esValue.asDouble(0D);
if (Double.doubleToLongBits(dbDouble) != Double.doubleToLongBits(esDouble)) {
final ObjectNode objectNode = createFieldDifferent(jobInfoItemHelper, dbValue, esValue);
differenceValue.set(path, objectNode);
differenceFieldNum++;
}
continue;
}
if (!dbValue.asText().equals(esValue.asText())) {
final ObjectNode objectNode = createFieldDifferent(jobInfoItemHelper, dbValue, esValue);
differenceValue.set(path, objectNode);
differenceFieldNum++;
}
}
compareResult.setDifferenceFieldNum(Long.valueOf(differenceFieldNum));
if (differenceFieldNum > 0) {
compareResult.setDifferenceValue(objectMapper.writeValueAsString(differenceValue));
compareResult.setCompareStatus(CompareResult.COMPARE_STATUS_EXCEPTION);
compareResult.setDbValue(objectMapper.writeValueAsString(compareResultHelper.getShowDBData()));
compareResult.setEsValue(objectMapper.writeValueAsString(esData));
} else {
compareResult.setDifferenceValue("{}");
compareResult.setCompareStatus(CompareResult.COMPARE_STATUS_EQUAL);
compareResult.setDbValue("{}");
compareResult.setEsValue("{}");
}
}
private JsonNode getValue(ObjectNode data, List<String> pathList) {
JsonNode value = data;
for (String path : pathList) {
value = value.get(path);
if (value == null) {
return NullNode.getInstance();
}
}
return value;
}
private ObjectNode createFieldDifferent(JobInfoItemHelper jobInfoItemHelper, JsonNode dbValue, JsonNode esValue) {
final ObjectNode objectNode = objectMapper.createObjectNode();
objectNode.set("db", dbValue);
objectNode.set("es", esValue);
final String orginTableName = jobInfoItemHelper.getJobInfoItem().getOrginTableName();
final String orginFieldName = jobInfoItemHelper.getJobInfoItem().getOrginFieldName();
objectNode.put("dbSrc", orginTableName + "." + orginFieldName);
return objectNode;
}
private boolean isNumber(JobInfoItem jobInfoItem) {
if ("double".equals(jobInfoItem.getDestFieldType())) {
return true;
}
if ("decimal".equals(jobInfoItem.getOrginFieldType())) {
return true;
}
return false;
}
}