[hard]57. Insert Interval

本文介绍了一个插入区间问题的解决方案,该问题要求在一个已排序且不重叠的区间集合中插入一个新的区间,并在必要时进行合并。通过具体示例展示了如何处理区间重叠的情况,并提供了一段C++代码实现。

57. Insert Interval

Given a set of non-overlapping intervals, insert a new interval into the intervals (merge if necessary).

You may assume that the intervals were initially sorted according to their start times.

Example 1:
Given intervals [1,3],[6,9], insert and merge [2,5] in as [1,5],[6,9].

Example 2:
Given [1,2],[3,5],[6,7],[8,10],[12,16], insert and merge [4,9] in as [1,2],[3,10],[12,16].

This is because the new interval [4,9] overlaps with [3,5],[6,7],[8,10].


Way to easy to be called hard.


class Solution {
public:
    vector<Interval> insert(vector<Interval>& intervals, Interval newInterval) {
        vector<Interval> ret;
        
        bool inserted = false;

        for (auto i: intervals) {
            if (i.end < newInterval.start)
                ret.push_back(i);
            else if (i.start > newInterval.end) {
                if (!inserted)
                    ret.push_back(newInterval);
                ret.push_back(i);
                inserted = true;
            }
            else {
                if (i.end >= newInterval.start)
                    newInterval.start = min(i.start, newInterval.start);
                if (i.start <= newInterval.end)
                    newInterval.end = max(newInterval.end, i.end);
            }
        }

        if (!inserted)
            ret.push_back(newInterval);

        return ret;
    }
};


数据插入失败,正在进行第2次重试: StatementCallback; uncategorized SQLException for SQL [INSERT INTO my_battery_info_M240910700000001 USING my_battery_info TAGS ('M240910700000001') (ts, id, battery_voltage, battery_current, battery_soc, battery_hard_version, battery_soft_version, battery_work_mode, battery_protect_code, battery_error_code, battery_temperature_max, battery_temperature_min, battery_voltage_max, battery_voltage_min, mos_status, mos_temp, battery_cycle_times, steady_status, cell_voltage, model, manufacture, imei, imsi, iccid, tracker_hardware_version, tracker_software_version, csq, network_type, location_mode, longitude, longitude_direction, latitude, latitude_direction, gps_speed, gps_signal, satellite_num, accuracy, flag, client_id, topic, product_key, handled, todo_now, needack, businessmode, upload_time, create_time, update_time, payload, project_id, sn, soh, battery_temperature_avg, charge_mode, low_battery_warn, charge_current_max, discharge_current_max, charge_current_avg, discharge_current_avg, gps_cog, total_charge_energy, charge_cycles_history, charge_capacity_history, discharge_capacity_history, ntc1, ntc2, ntc3, ntc4, tracker_communication_status, battery_kind, dsoc, correction_rate, tsoc, bsoc, fcc, actual_soh, charging_sop, discharging_sop, task_max_delay_1ms, task_max_delay_10ms, task_max_delay_100ms, task_max_delay_500ms, task_max_time_1ms, task_max_time_10ms, task_max_time_100ms, task_max_time_500ms, empty_count, cell_info, gps_max_signal, msg_delay_time, data_type, battery_vehicle_status, storage_status, ota_status, lock_status, battery_vehicle_alarm_status) VALUES ('2024-09-23 08:58:06.000', 320, 70000, 0, 0, NULL, 'V1.00', '48', NULL, '000000000008000000000004', 24, -31, 3199, 3177, 8, 25, 0, NULL, NULL, NULL, NULL, '865271061996824', NULL, '898607F01124F9007644', NULL, '2024-09-20 14:35', NULL, NULL, NULL, '0', 'E', '0', 'N', NULL, '9', NULL, NULL, 1, NULL, NULL, NULL, 0, NULL, NULL, NULL, '2024-09-23 08:58:06.000', '2024-09-23 13:54:31.000', '2024-09-23 13:54:31.000', '{"ts":1727053086,"code":200,"data":[1,0,1,0,0,0,0,25,225,26,2,188,12,127,12,105,48,8,0,0,0,0,0,0,0,8,0,0,0,0,0,4,105,120],"tracker":[0,0,9,"865271061996824","898607F01124F9007644","2024-09-20 14:35"]}', 38, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 0, 0, 0, 0)]; SQL state []; error code [9730]; TDengine ERROR (0x2602): sql: INSERT INTO my_battery_info_M240910700000001 USING my_battery_info TAGS ('M240910700000001') (ts, id, battery_voltage, battery_current, battery_soc, battery_hard_version, battery_soft_version, battery_work_mode, battery_protect_code, battery_error_code, battery_temperature_max, battery_temperature_min, battery_voltage_max, battery_voltage_min, mos_status, mos_temp, battery_cycle_times, steady_status, cell_voltage, model, manufacture, imei, imsi, iccid, tracker_hardware_version, tracker_software_version, csq, network_type, location_mode, longitude, longitude_direction, latitude, latitude_direction, gps_speed, gps_signal, satellite_num, accuracy, flag, client_id, topic, product_key, handled, todo_now, needack, businessmode, upload_time, create_time, update_time, payload, project_id, sn, soh, battery_temperature_avg, charge_mode, low_battery_warn, charge_current_max, discharge_current_max, charge_current_avg, discharge_current_avg, gps_cog, total_charge_energy, charge_cycles_history, charge_capacity_history, discharge_capacity_history, ntc1, ntc2, ntc3, ntc4, tracker_communication_status, battery_kind, dsoc, correction_rate, tsoc, bsoc, fcc, actual_soh, charging_sop, discharging_sop, task_max_delay_1ms, task_max_delay_10ms, task_max_delay_100ms, task_max_delay_500ms, task_max_time_1ms, task_max_time_10ms, task_max_time_100ms, task_max_time_500ms, empty_count, cell_info, gps_max_signal, msg_delay_time, data_type, battery_vehicle_status, storage_status, ota_status, lock_status, battery_vehicle_alarm_status) VALUES ('2024-09-23 08:58:06.000', 320, 70000, 0, 0, NULL, 'V1.00', '48', NULL, '000000000008000000000004', 24, -31, 3199, 3177, 8, 25, 0, NULL, NULL, NULL, NULL, '865271061996824', NULL, '898607F01124F9007644', NULL, '2024-09-20 14:35', NULL, NULL, NULL, '0', 'E', '0', 'N', NULL, '9', NULL, NULL, 1, NULL, NULL, NULL, 0, NULL, NULL, NULL, '2024-09-23 08:58:06.000', '2024-09-23 13:54:31.000', '2024-09-23 13:54:31.000', '{"ts":1727053086,"code":200,"data":[1,0,1,0,0,0,0,25,225,26,2,188,12,127,12,105,48,8,0,0,0,0,0,0,0,8,0,0,0,0,0,4,105,120],"tracker":[0,0,9,"865271061996824","898607F01124F9007644","2024-09-20 14:35"]}', 38, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 0, 0, 0, 0), desc: Invalid column name: id,超级表的创建sql是:CREATE STABLE IF NOT EXISTS my_battery_info ( ts TIMESTAMP, battery_voltage INT, battery_current INT, battery_soc INT, battery_hard_version BINARY(100), battery_soft_version BINARY(100), battery_work_mode BINARY(100), battery_protect_code BINARY(100), battery_error_code BINARY(100), battery_temperature_max INT, battery_temperature_min INT, battery_voltage_max INT, battery_voltage_min INT, mos_status INT, mos_temp INT, battery_cycle_times INT, steady_status INT, cell_voltage BINARY(500), model BINARY(100), manufacture BINARY(100), imei BINARY(100), imsi BINARY(100), iccid BINARY(100), tracker_hardware_version BINARY(100), tracker_software_version BINARY(100), csq BINARY(100), network_type BINARY(100), location_mode BINARY(100), longitude BINARY(100), longitude_direction BINARY(100), latitude BINARY(100), latitude_direction BINARY(100), gps_speed BINARY(100), gps_signal BINARY(100), satellite_num BINARY(100), accuracy BINARY(100), flag INT, client_id BINARY(100), `topic` BINARY(100), product_key BINARY(100), handled INT, todo_now INT, needack TINYINT, businessmode BINARY(100), create_time TIMESTAMP, update_time TIMESTAMP, payload BINARY(5000), project_id INT, sn BINARY(100), soh INT, battery_temperature_avg INT, charge_mode INT, low_battery_warn INT, charge_current_max INT, discharge_current_max INT, charge_current_avg INT, discharge_current_avg INT, gps_cog BINARY(100), total_charge_energy DOUBLE, charge_cycles_history INT, charge_capacity_history DOUBLE, discharge_capacity_history DOUBLE, ntc1 INT, ntc2 INT, ntc3 INT, ntc4 INT, tracker_communication_status BINARY(100), battery_kind BINARY(100), dsoc INT, correction_rate INT, tsoc INT, bsoc INT, fcc INT, actual_soh INT, charging_sop INT, discharging_sop INT, task_max_delay_1ms INT, task_max_delay_10ms INT, task_max_delay_100ms INT, task_max_delay_500ms INT, task_max_time_1ms INT, task_max_time_10ms INT, task_max_time_100ms INT, task_max_time_500ms INT, empty_count INT, cell_info BINARY(5000), gps_max_signal INT, msg_delay_time INT, data_type INT, battery_vehicle_status INT, storage_status INT, ota_status INT, lock_status INT, battery_vehicle_alarm_status INT ) TAGS ( battery_id BINARY(100) );。然后Java代码是:package com.bms.sync.data.service; import com.bms.sync.data.util.TimeUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.beans.factory.annotation.Value; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.scheduling.annotation.Async; import org.springframework.stereotype.Service; import javax.sql.DataSource; import java.sql.Timestamp; import java.time.LocalDateTime; import java.time.format.DateTimeFormatter; import java.util.List; import java.util.Map; import java.util.Arrays; @Service public class DataMigrationService { private final JdbcTemplate mysqlJdbcTemplate; private final JdbcTemplate tdengineJdbcTemplate; @Value("${table.tag.columns}") private String tagColumnsConfig; @Value("${table.timestamp.field}") private String timestampField; // TDengine时间戳字段名 @Value("${table.tdengine.timestamp.field}") private String tdengineTimestampField; @Value("${performance.batch-size.historical}") private int historicalBatchSize; @Value("${performance.batch-size.incremental}") private int incrementalBatchSize; // 重试配置 @Value("${retry.max-attempts}") private int maxRetries; @Value("${retry.interval}") private long retryInterval; @Autowired private PerformanceMonitorService performanceMonitorService; @Autowired private TableAutoCreateService tableAutoCreateService; @Autowired private RedisQueueService redisQueueService; @Autowired public DataMigrationService(JdbcTemplate mysqlJdbcTemplate, JdbcTemplate tdengineJdbcTemplate, PerformanceMonitorService performanceMonitorService, TableAutoCreateService tableAutoCreateService, RedisQueueService redisQueueService) { this.mysqlJdbcTemplate = mysqlJdbcTemplate; this.tdengineJdbcTemplate = tdengineJdbcTemplate; this.performanceMonitorService = performanceMonitorService; this.tableAutoCreateService = tableAutoCreateService; this.redisQueueService = redisQueueService; } /** * 迁移数据(支持批量处理) * @param mysqlTableName MySQL表名 * @param superTableName TDengine超级表名 */ public void migrateData(String mysqlTableName, String superTableName) { long startTime = System.currentTimeMillis(); try { System.out.println("开始数据迁移: " + mysqlTableName + " -> " + superTableName); // 确保表结构已创建 System.out.println("步骤1: 确保表结构已创建"); ensureTableStructureExists(mysqlTableName, superTableName); // 1. 获取总记录数 System.out.println("步骤2: 获取总记录数"); int totalRecords = getTotalRecordCount(mysqlTableName); if (totalRecords == 0) { System.out.println("没有数据需要迁移"); return; } System.out.println("总记录数: " + totalRecords); // 2. 分批迁移数据(增加批处理大小以提高效率) int batchSize = historicalBatchSize; // 每批处理5000条记录 int totalBatches = (int) Math.ceil((double) totalRecords / batchSize); System.out.println("总批次数: " + totalBatches + ",批处理大小: " + batchSize); // 使用Redis队列处理历史数据 System.out.println("步骤3: 使用Redis队列处理历史数据"); processHistoricalDataWithQueue(mysqlTableName, superTableName, totalRecords, batchSize); System.out.println("数据迁移完成"); } catch (Exception e) { System.err.println("数据迁移失败: " + e.getMessage()); e.printStackTrace(); } finally { long endTime = System.currentTimeMillis(); performanceMonitorService.recordTime("data.migration", endTime - startTime); performanceMonitorService.incrementCounter("data.migration.totalRecords", getTotalRecordCount(mysqlTableName)); System.out.println("数据迁移总耗时: " + (endTime - startTime) + "ms"); } } /** * 确保表结构已创建 * @param mysqlTableName MySQL表名 * @param superTableName TDengine超级表名 */ public void ensureTableStructureExists(String mysqlTableName, String superTableName) { try { System.out.println("确保表结构存在: " + mysqlTableName + " -> " + superTableName); // 从配置中获取标签列 String[] tagColumnsArray = tagColumnsConfig.split(","); List<String> tagColumns = Arrays.asList(tagColumnsArray); // 自动创建表结构 tableAutoCreateService.autoCreateTableStructure(mysqlTableName, superTableName, tagColumns); System.out.println("表结构创建完成"); } catch (Exception e) { System.err.println("确保表结构存在时失败: " + e.getMessage()); e.printStackTrace(); } } /** * 使用Redis队列处理历史数据 * @param mysqlTableName MySQL表名 * @param superTableName TDengine超级表名 * @param totalRecords 总记录数 * @param batchSize 批处理大小 */ private void processHistoricalDataWithQueue(String mysqlTableName, String superTableName, int totalRecords, int batchSize) { try { String queueName = mysqlTableName + "_to_" + superTableName; // 清空之前的队列数据 redisQueueService.clearQueue(queueName); // 使用基于时间戳的分页方式替代LIMIT/OFFSET,避免数据遗漏 String lastProcessedTime = null; String currentTime = java.time.LocalDateTime.now().format(java.time.format.DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")); long startTime = System.currentTimeMillis(); int totalProcessed = 0; boolean hasMoreData = true; while (hasMoreData) { // 构建查询SQL,使用时间戳分页 StringBuilder querySql = new StringBuilder(); querySql.append("SELECT * FROM ").append(mysqlTableName); // 添加时间范围条件,确保时间字段不为空 querySql.append(" WHERE ").append(timestampField).append(" IS NOT NULL"); // 确保时间字段不为空 if (lastProcessedTime != null && !lastProcessedTime.isEmpty()) { querySql.append(" AND ").append(timestampField).append(" > '").append(lastProcessedTime).append("'"); } querySql.append(" ORDER BY ").append(timestampField).append(" ASC"); List<Map<String, Object>> records = mysqlJdbcTemplate.queryForList(querySql.toString()); if (!records.isEmpty()) { // 将数据放入Redis队列 redisQueueService.pushHistoricalDataToQueue(queueName, records); // 更新最大时间戳 Object lastTimestamp = records.get(records.size() - 1).get(timestampField); if (lastTimestamp != null) { lastProcessedTime = lastTimestamp.toString(); } totalProcessed += records.size(); System.out.println("已将 " + records.size() + " 条记录放入Redis队列,总计已处理: " + totalProcessed); } else { hasMoreData = false; } } long endTime = System.currentTimeMillis(); System.out.println("历史数据已全部放入Redis队列,队列大小: " + redisQueueService.getQueueSize(queueName) + ",耗时: " + (endTime - startTime) + "ms"); // 从Redis队列中取出数据并同步到TDengine int successCount = syncDataFromQueue(queueName, superTableName); System.out.println("历史数据同步完成,总计成功同步记录数: " + successCount); } catch (Exception e) { System.err.println("使用Redis队列处理历史数据失败: " + e.getMessage()); e.printStackTrace(); } } /** * 从Redis队列中取出数据并同步到TDengine * @param queueName 队列名称 * @param superTableName TDengine超级表名 * @return 成功同步的记录数 */ private int syncDataFromQueue(String queueName, String superTableName) { int totalSynced = 0; int batchSize = incrementalBatchSize; // 从队列中每次取出100条记录进行同步 long startTime = System.currentTimeMillis(); System.out.println("开始从Redis队列同步数据到TDengine,批处理大小: " + batchSize); int batchCount = 0; while (redisQueueService.getQueueSize(queueName) > 0) { // 先查看但不移除队列中的数据 List<Object> records = redisQueueService.peekBatchFromQueue(queueName, batchSize); if (records != null && !records.isEmpty()) { // 转换为正确的类型 List<Map<String, Object>> convertedRecords = (List<Map<String, Object>>) (List<?>) records; // 批量插入到TDengine并统计成功插入的记录数 int successCount = batchInsertIntoTDengine(superTableName, convertedRecords); totalSynced += successCount; batchCount++; // 只有在数据成功同步后才从队列中移除 if (successCount > 0) { // 从队列中移除已成功处理的数据 redisQueueService.removeBatchFromQueue(queueName, successCount); } // 每处理10批打印一次进度信息 if (batchCount % 10 == 0) { System.out.println("已同步 " + batchCount + " 批次数据,当前批次成功记录数: " + successCount + ",总计已同步: " + totalSynced); } } } long endTime = System.currentTimeMillis(); // 只有在有数据成功同步时才打印完成信息 if (totalSynced > 0) { System.out.println("从Redis队列同步数据完成,总计同步: " + totalSynced + " 条记录,总批次数: " + batchCount + ",耗时: " + (endTime - startTime) + "ms"); } else { System.out.println("Redis队列中没有数据需要同步"); } return totalSynced; } /** * 获取记录数 * @param mysqlTableName MySQL表名 * @return 记录数 */ public int getRecordCount(String mysqlTableName) { // 直接从数据库获取,不使用缓存 String countSql = "SELECT COUNT(*) FROM " + mysqlTableName; Integer count = mysqlJdbcTemplate.queryForObject(countSql, Integer.class); return count != null ? count : 0; } /** * 获取总记录数 * @param tableName 表名 * @return 总记录数 */ public int getTotalRecordCount(String tableName) { // 直接从数据库获取,不使用缓存 String countSql = "SELECT COUNT(*) FROM " + tableName; Integer count = mysqlJdbcTemplate.queryForObject(countSql, Integer.class); return count != null ? count : 0; } /** * 批量插入数据到TDengine * @param superTableName TDengine超级表名 * @param records 记录列表 * @return 成功插入的记录数 */ private int batchInsertIntoTDengine(String superTableName, List<Map<String, Object>> records) { if (records.isEmpty()) { return 0; } int successCount = 0; try { // 一次性检查并创建所有需要的子表 ensureAllChildTablesExist(superTableName, records); // 然后批量插入数据 for (Map<String, Object> record : records) { try { insertIntoTDengine(superTableName, record); successCount++; } catch (Exception e) { System.err.println("单条记录插入失败: " + e.getMessage()); } } } catch (Exception e) { System.err.println("批量数据插入失败: " + e.getMessage()); // 不打印完整堆栈,减少日志输出 } return successCount; } /** * 一次性检查并创建所有需要的子表 * @param superTableName 超级表名 * @param records 记录列表 */ private void ensureAllChildTablesExist(String superTableName, List<Map<String, Object>> records) { try { // 首先确保超级表存在 // 不使用缓存检查超级表是否存在 // 直接确保超级表存在 ensureSuperTableExists(superTableName); // 不使用缓存存储超级表存在信息 // 收集所有需要的子表名 java.util.Set<String> childTableNames = new java.util.HashSet<>(); java.util.Map<String, String> childTableToTagMap = new java.util.HashMap<>(); String tagColumn = tagColumnsConfig; for (Map<String, Object> record : records) { Object tagValueObj = record.get(tagColumn); String tagValue = (tagValueObj != null) ? tagValueObj.toString() : "null"; String childTableName = superTableName + "_" + tagValue; // 仅处理缓存中不存在的表 String cacheKey = "tdengine:table:" + childTableName; // 不使用缓存,直接检查表是否存在 if (!childTableNames.contains(childTableName)) { childTableNames.add(childTableName); childTableToTagMap.put(childTableName, tagValue); } } // 一次性查询所有子表是否存在 if (!childTableNames.isEmpty()) { // 使用更高效的方式检查表是否存在 for (String childTableName : childTableNames) { if (!isTableExists(childTableName)) { String tagValue = childTableToTagMap.get(childTableName); createChildTable(superTableName, childTableName, tagValue); } else { // 缓存已存在的表信息 // 不使用缓存存储表存在信息 } } } } catch (Exception e) { System.err.println("批量检查子表存在时失败: " + e.getMessage()); // 不打印完整堆栈,减少日志输出 } } /** * 检查TDengine表是否存在 * @param tableName 表名 * @return 是否存在 */ private boolean isTableExists(String tableName) { try { String checkSql = "SELECT table_name FROM information_schema.ins_tables WHERE table_name = ?"; List<Map<String, Object>> result = tdengineJdbcTemplate.queryForList(checkSql, tableName); return !result.isEmpty(); } catch (Exception e) { System.err.println("检查表是否存在时出错: " + e.getMessage()); return false; } } /** * 创建子表 * @param superTableName 超级表名 * @param childTableName 子表名 * @param tagValue 标签值 */ private void createChildTable(String superTableName, String childTableName, String tagValue) { try { String createChildTableSql = "CREATE TABLE IF NOT EXISTS " + childTableName + " USING " + superTableName + " TAGS ('" + tagValue + "')"; tdengineJdbcTemplate.execute(createChildTableSql); // 缓存表存在信息 // 不使用缓存存储表存在信息 System.out.println("创建子表: " + childTableName); } catch (Exception e) { System.err.println("创建子表失败: " + e.getMessage()); } } /** * 插入数据到TDengine * @param superTableName TDengine超级表名 * @param record 记录 */ public void insertIntoTDengine(String superTableName, Map<String, Object> record) { long startTime = System.currentTimeMillis(); int retryCount = 0; while (retryCount < maxRetries) { try { // 获取标签列名(从配置中获取) String tagColumn = tagColumnsConfig; // 获取标签值 Object tagValueObj = record.get(tagColumn); String tagValue = (tagValueObj != null) ? tagValueObj.toString() : "null"; // 生成子表名(使用标签值) String childTableName = superTableName + "_" + tagValue; // 优化:仅在缓存中不存在时检查子表 String cacheKey = "tdengine:table:" + childTableName; // 不使用缓存检查表是否存在 // 直接检查表是否存在 if (!isTableExists(childTableName)) { // 确保子表存在 ensureChildTableExists(superTableName, childTableName, tagValue); // 缓存表存在信息,避免频繁检查 // 不使用缓存存储表存在信息 } // 构建SQL StringBuilder sql = new StringBuilder(); sql.append("INSERT INTO ").append(childTableName).append(" USING ").append(superTableName) .append(" TAGS ('").append(tagValue).append("') (").append(tdengineTimestampField).append(", "); // 添加列名 boolean first = true; for (String columnName : record.keySet()) { // 跳过标签列和时间戳字段 if (tagColumn.equals("id")||tagColumn.equals(columnName) || tdengineTimestampField.equals(columnName)) { continue; } if (!first) { sql.append(", "); } // 处理关键字冲突 sql.append(escapeKeyword(columnName)); first = false; } // 获取时间戳字段的值 Object timestampValue = record.get(timestampField); if (timestampValue == null) { // 如果时间戳为空,跳过这条记录 System.err.println("警告: 记录的时间戳字段为空,跳过该记录"); return; } String formattedTimestamp; if (timestampValue instanceof Timestamp) { // 格式化时间戳 Timestamp ts = (Timestamp) timestampValue; formattedTimestamp = "'" + TimeUtils.formatWithMillis(TimeUtils.from(ts)) + "'"; } else if (timestampValue instanceof LocalDateTime) { // 格式化LocalDateTime LocalDateTime ldt = (LocalDateTime) timestampValue; formattedTimestamp = "'" + TimeUtils.formatWithMillis(ldt) + "'"; } else { // 其他情况使用字符串值 String timestampStr = timestampValue.toString(); // 检查是否需要调整毫秒格式 if (timestampStr.matches(".*\\.\\d{3}$")) { // 如果已经包含毫秒,直接使用 formattedTimestamp = "'" + timestampStr + "'"; } else if (timestampStr.matches(".*\\.\\d{1,2}$")) { // 如果毫秒部分不足3位,补零 String[] parts = timestampStr.split("\\."); if (parts.length == 2) { String millis = parts[1]; while (millis.length() < 3) { millis += "0"; } formattedTimestamp = "'" + parts[0] + "." + millis + "'"; } else { formattedTimestamp = "'" + timestampStr + "'"; } } else if (timestampStr.matches(".*:\\d{2}$")) { // 如果没有毫秒部分,添加.000 formattedTimestamp = "'" + timestampStr + ".000'"; } else { formattedTimestamp = "'" + timestampStr + "'"; } } sql.append(") VALUES (").append(formattedTimestamp).append(", "); // 添加值 first = true; for (Map.Entry<String, Object> entry : record.entrySet()) { String columnName = entry.getKey(); Object value = entry.getValue(); // 跳过标签列和时间戳字段 if (tagColumn.equals(columnName) || tdengineTimestampField.equals(columnName)) { continue; } if (!first) { sql.append(", "); } // 处理NULL值 if (value == null) { sql.append("NULL"); } else if (value instanceof String || value instanceof Timestamp || value instanceof LocalDateTime) { // 对字符串和时间戳类型进行特殊处理 if (value instanceof Timestamp) { // 格式化时间戳 Timestamp ts = (Timestamp) value; sql.append("'").append(TimeUtils.formatWithMillis(TimeUtils.from(ts))).append("'"); } else if (value instanceof LocalDateTime) { // 格式化LocalDateTime LocalDateTime ldt = (LocalDateTime) value; sql.append("'").append(TimeUtils.formatWithMillis(ldt)).append("'"); } else { // 字符串值需要添加引号,并处理NULL字符串 String strValue = value.toString(); if ("NULL".equalsIgnoreCase(strValue)) { sql.append("NULL"); } else { sql.append("'").append(strValue.replace("'", "''")).append("'"); } } } else { // 其他类型直接添加 sql.append(value.toString()); } first = false; } sql.append(")"); // System.out.println("SQL语句: " + sql.toString()); tdengineJdbcTemplate.execute(sql.toString()); // 更新性能监控 performanceMonitorService.incrementCounter("data.migration.insert.success", 1); break; // 成功执行,跳出重试循环 } catch (Exception e) { retryCount++; System.err.println("数据插入失败,正在进行第" + retryCount + "次重试: " + e.getMessage()); if (retryCount >= maxRetries) { System.err.println("数据插入最终失败: " + e.getMessage()); performanceMonitorService.incrementCounter("data.migration.insert.failure", 1); throw new RuntimeException("数据插入失败", e); } else { try { Thread.sleep(retryInterval * retryCount); // 使用配置的重试间隔和指数退避 } catch (InterruptedException ie) { Thread.currentThread().interrupt(); throw new RuntimeException("重试被中断", ie); } } } finally { long endTime = System.currentTimeMillis(); performanceMonitorService.recordTime("data.migration.insert", endTime - startTime); } } } /** * 确保子表存在 * @param superTableName 超级表名 * @param childTableName 子表名 * @param tagValue 标签值 */ private void ensureChildTableExists(String superTableName, String childTableName, String tagValue) { // 使用类级锁来确保在多线程环境下不会重复创建表 synchronized (DataMigrationService.class) { try { // 首先确保超级表存在(使用缓存优化) String superTableCacheKey = "tdengine:stable:" + superTableName; // 不使用缓存检查超级表是否存在 // 直接确保超级表存在 ensureSuperTableExists(superTableName); // 不使用缓存存储超级表存在信息 // 检查子表是否存在 if (!isTableExists(childTableName)) { createChildTable(superTableName, childTableName, tagValue); } } catch (Exception e) { System.err.println("确保子表存在时失败: " + e.getMessage()); // 不打印完整堆栈,减少日志输出 } } } /** * 确保超级表存在 * @param superTableName 超级表名 */ private void ensureSuperTableExists(String superTableName) { try { // 检查超级表是否存在 if (!isTableExists(superTableName)) { // 超级表应该已经由TableAutoCreateService创建,这里只是确保缓存存在 // 不再创建默认的超级表结构 System.out.println("警告: 超级表 " + superTableName + " 不存在,需要先分析MySQL表结构并创建"); } } catch (Exception e) { System.err.println("确保超级表存在时失败: " + e.getMessage()); // 不打印完整堆栈,减少日志输出 } } /** * 处理SQL关键字冲突,为字段名添加反引号 * @param columnName 字段名 * @return 处理后的字段名 */ private String escapeKeyword(String columnName) { // TDengine中的关键字列表(部分) String[] keywords = {"query", "order", "group", "limit", "offset", "select", "from", "where", "insert", "update", "delete"}; for (String keyword : keywords) { if (keyword.equalsIgnoreCase(columnName)) { return "`" + columnName + "`"; } } return columnName; } }给我优化后的完整代码
11-15
package com.bms.sync.data.service; import com.bms.sync.data.util.TimeUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.beans.factory.annotation.Value; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.scheduling.annotation.Async; import org.springframework.stereotype.Service; import javax.sql.DataSource; import java.sql.Timestamp; import java.time.LocalDateTime; import java.time.format.DateTimeFormatter; import java.util.List; import java.util.Map; import java.util.Arrays; @Service public class DataMigrationService { private final JdbcTemplate mysqlJdbcTemplate; private final JdbcTemplate tdengineJdbcTemplate; @Value("${table.tag.columns}") private String tagColumnsConfig; @Value("${table.timestamp.field}") private String timestampField; // TDengine时间戳字段名 @Value("${table.tdengine.timestamp.field}") private String tdengineTimestampField; @Value("${performance.batch-size.historical}") private int historicalBatchSize; @Value("${performance.batch-size.incremental}") private int incrementalBatchSize; // 重试配置 @Value("${retry.max-attempts}") private int maxRetries; @Value("${retry.interval}") private long retryInterval; @Autowired private PerformanceMonitorService performanceMonitorService; @Autowired private TableAutoCreateService tableAutoCreateService; @Autowired private RedisQueueService redisQueueService; @Autowired public DataMigrationService(JdbcTemplate mysqlJdbcTemplate, JdbcTemplate tdengineJdbcTemplate, PerformanceMonitorService performanceMonitorService, TableAutoCreateService tableAutoCreateService, RedisQueueService redisQueueService) { this.mysqlJdbcTemplate = mysqlJdbcTemplate; this.tdengineJdbcTemplate = tdengineJdbcTemplate; this.performanceMonitorService = performanceMonitorService; this.tableAutoCreateService = tableAutoCreateService; this.redisQueueService = redisQueueService; } /** * 迁移数据(支持批量处理) * @param mysqlTableName MySQL表名 * @param superTableName TDengine超级表名 */ public void migrateData(String mysqlTableName, String superTableName) { long startTime = System.currentTimeMillis(); try { System.out.println("开始数据迁移: " + mysqlTableName + " -> " + superTableName); // 确保表结构已创建 System.out.println("步骤1: 确保表结构已创建"); ensureTableStructureExists(mysqlTableName, superTableName); // 1. 获取总记录数 System.out.println("步骤2: 获取总记录数"); int totalRecords = getTotalRecordCount(mysqlTableName); if (totalRecords == 0) { System.out.println("没有数据需要迁移"); return; } System.out.println("总记录数: " + totalRecords); // 2. 分批迁移数据(增加批处理大小以提高效率) int batchSize = historicalBatchSize; // 每批处理5000条记录 int totalBatches = (int) Math.ceil((double) totalRecords / batchSize); System.out.println("总批次数: " + totalBatches + ",批处理大小: " + batchSize); // 使用Redis队列处理历史数据 System.out.println("步骤3: 使用Redis队列处理历史数据"); processHistoricalDataWithQueue(mysqlTableName, superTableName, totalRecords, batchSize); System.out.println("数据迁移完成"); } catch (Exception e) { System.err.println("数据迁移失败: " + e.getMessage()); e.printStackTrace(); } finally { long endTime = System.currentTimeMillis(); performanceMonitorService.recordTime("data.migration", endTime - startTime); performanceMonitorService.incrementCounter("data.migration.totalRecords", getTotalRecordCount(mysqlTableName)); System.out.println("数据迁移总耗时: " + (endTime - startTime) + "ms"); } } /** * 确保表结构已创建 * @param mysqlTableName MySQL表名 * @param superTableName TDengine超级表名 */ public void ensureTableStructureExists(String mysqlTableName, String superTableName) { try { System.out.println("确保表结构存在: " + mysqlTableName + " -> " + superTableName); // 从配置中获取标签列 String[] tagColumnsArray = tagColumnsConfig.split(","); List<String> tagColumns = Arrays.asList(tagColumnsArray); // 自动创建表结构 tableAutoCreateService.autoCreateTableStructure(mysqlTableName, superTableName, tagColumns); System.out.println("表结构创建完成"); } catch (Exception e) { System.err.println("确保表结构存在时失败: " + e.getMessage()); e.printStackTrace(); } } /** * 使用Redis队列处理历史数据 * @param mysqlTableName MySQL表名 * @param superTableName TDengine超级表名 * @param totalRecords 总记录数 * @param batchSize 批处理大小 */ private void processHistoricalDataWithQueue(String mysqlTableName, String superTableName, int totalRecords, int batchSize) { try { String queueName = mysqlTableName + "_to_" + superTableName; // 清空之前的队列数据 redisQueueService.clearQueue(queueName); // 使用基于时间戳的分页方式替代LIMIT/OFFSET,避免数据遗漏 String lastProcessedTime = null; String currentTime = java.time.LocalDateTime.now().format(java.time.format.DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")); long startTime = System.currentTimeMillis(); int totalProcessed = 0; boolean hasMoreData = true; while (hasMoreData) { // 构建查询SQL,使用时间戳分页 StringBuilder querySql = new StringBuilder(); querySql.append("SELECT * FROM ").append(mysqlTableName); // 添加时间范围条件,确保时间字段不为空 querySql.append(" WHERE ").append(timestampField).append(" IS NOT NULL"); // 确保时间字段不为空 if (lastProcessedTime != null && !lastProcessedTime.isEmpty()) { querySql.append(" AND ").append(timestampField).append(" > '").append(lastProcessedTime).append("'"); } querySql.append(" ORDER BY ").append(timestampField).append(" ASC"); querySql.append(" LIMIT ").append(batchSize); List<Map<String, Object>> records = mysqlJdbcTemplate.queryForList(querySql.toString()); if (!records.isEmpty()) { // 将数据放入Redis队列 redisQueueService.pushHistoricalDataToQueue(queueName, records); // 更新最大时间戳 Object lastTimestamp = records.get(records.size() - 1).get(timestampField); if (lastTimestamp != null) { lastProcessedTime = lastTimestamp.toString(); } totalProcessed += records.size(); System.out.println("已将 " + records.size() + " 条记录放入Redis队列,总计已处理: " + totalProcessed); } else { hasMoreData = false; } } long endTime = System.currentTimeMillis(); System.out.println("历史数据已全部放入Redis队列,队列大小: " + redisQueueService.getQueueSize(queueName) + ",耗时: " + (endTime - startTime) + "ms"); // 从Redis队列中取出数据并同步到TDengine int successCount = syncDataFromQueue(queueName, superTableName); System.out.println("历史数据同步完成,总计成功同步记录数: " + successCount); } catch (Exception e) { System.err.println("使用Redis队列处理历史数据失败: " + e.getMessage()); e.printStackTrace(); } } /** * 从Redis队列中取出数据并同步到TDengine * @param queueName 队列名称 * @param superTableName TDengine超级表名 * @return 成功同步的记录数 */ private int syncDataFromQueue(String queueName, String superTableName) { int totalSynced = 0; int batchSize = incrementalBatchSize; // 从队列中每次取出100条记录进行同步 long startTime = System.currentTimeMillis(); System.out.println("开始从Redis队列同步数据到TDengine,批处理大小: " + batchSize); int batchCount = 0; while (redisQueueService.getQueueSize(queueName) > 0) { // 先查看但不移除队列中的数据 List<Object> records = redisQueueService.peekBatchFromQueue(queueName, batchSize); if (records != null && !records.isEmpty()) { // 转换为正确的类型 List<Map<String, Object>> convertedRecords = (List<Map<String, Object>>) (List<?>) records; // 批量插入到TDengine并统计成功插入的记录数 int successCount = batchInsertIntoTDengine(superTableName, convertedRecords); totalSynced += successCount; batchCount++; // 只有在数据成功同步后才从队列中移除 if (successCount > 0) { // 从队列中移除已成功处理的数据 redisQueueService.removeBatchFromQueue(queueName, successCount); } // 每处理10批打印一次进度信息 if (batchCount % 10 == 0) { System.out.println("已同步 " + batchCount + " 批次数据,当前批次成功记录数: " + successCount + ",总计已同步: " + totalSynced); } } } long endTime = System.currentTimeMillis(); // 只有在有数据成功同步时才打印完成信息 if (totalSynced > 0) { System.out.println("从Redis队列同步数据完成,总计同步: " + totalSynced + " 条记录,总批次数: " + batchCount + ",耗时: " + (endTime - startTime) + "ms"); } else { System.out.println("Redis队列中没有数据需要同步"); } return totalSynced; } /** * 获取记录数 * @param mysqlTableName MySQL表名 * @return 记录数 */ public int getRecordCount(String mysqlTableName) { // 直接从数据库获取,不使用缓存 String countSql = "SELECT COUNT(*) FROM " + mysqlTableName; Integer count = mysqlJdbcTemplate.queryForObject(countSql, Integer.class); return count != null ? count : 0; } /** * 获取总记录数 * @param tableName 表名 * @return 总记录数 */ public int getTotalRecordCount(String tableName) { // 直接从数据库获取,不使用缓存 String countSql = "SELECT COUNT(*) FROM " + tableName; Integer count = mysqlJdbcTemplate.queryForObject(countSql, Integer.class); return count != null ? count : 0; } /** * 批量插入数据到TDengine * @param superTableName TDengine超级表名 * @param records 记录列表 * @return 成功插入的记录数 */ private int batchInsertIntoTDengine(String superTableName, List<Map<String, Object>> records) { if (records.isEmpty()) { return 0; } int successCount = 0; try { // 一次性检查并创建所有需要的子表 ensureAllChildTablesExist(superTableName, records); // 然后批量插入数据 for (Map<String, Object> record : records) { try { insertIntoTDengine(superTableName, record); successCount++; } catch (Exception e) { System.err.println("单条记录插入失败: " + e.getMessage()); } } } catch (Exception e) { System.err.println("批量数据插入失败: " + e.getMessage()); // 不打印完整堆栈,减少日志输出 } return successCount; } /** * 一次性检查并创建所有需要的子表 * @param superTableName 超级表名 * @param records 记录列表 */ private void ensureAllChildTablesExist(String superTableName, List<Map<String, Object>> records) { try { // 首先确保超级表存在 // 不使用缓存检查超级表是否存在 // 直接确保超级表存在 ensureSuperTableExists(superTableName); // 不使用缓存存储超级表存在信息 // 收集所有需要的子表名 java.util.Set<String> childTableNames = new java.util.HashSet<>(); java.util.Map<String, String> childTableToTagMap = new java.util.HashMap<>(); String tagColumn = tagColumnsConfig; for (Map<String, Object> record : records) { Object tagValueObj = record.get(tagColumn); String tagValue = (tagValueObj != null) ? tagValueObj.toString() : "null"; String childTableName = superTableName + "_" + tagValue; // 仅处理缓存中不存在的表 String cacheKey = "tdengine:table:" + childTableName; // 不使用缓存,直接检查表是否存在 if (!childTableNames.contains(childTableName)) { childTableNames.add(childTableName); childTableToTagMap.put(childTableName, tagValue); } } // 一次性查询所有子表是否存在 if (!childTableNames.isEmpty()) { // 使用更高效的方式检查表是否存在 for (String childTableName : childTableNames) { if (!isTableExists(childTableName)) { String tagValue = childTableToTagMap.get(childTableName); createChildTable(superTableName, childTableName, tagValue); } else { // 缓存已存在的表信息 // 不使用缓存存储表存在信息 } } } } catch (Exception e) { System.err.println("批量检查子表存在时失败: " + e.getMessage()); // 不打印完整堆栈,减少日志输出 } } /** * 检查TDengine表是否存在 * @param tableName 表名 * @return 是否存在 */ private boolean isTableExists(String tableName) { try { String checkSql = "SELECT table_name FROM information_schema.ins_tables WHERE table_name = ?"; List<Map<String, Object>> result = tdengineJdbcTemplate.queryForList(checkSql, tableName); return !result.isEmpty(); } catch (Exception e) { System.err.println("检查表是否存在时出错: " + e.getMessage()); return false; } } /** * 创建子表 * @param superTableName 超级表名 * @param childTableName 子表名 * @param tagValue 标签值 */ private void createChildTable(String superTableName, String childTableName, String tagValue) { try { String createChildTableSql = "CREATE TABLE IF NOT EXISTS " + childTableName + " USING " + superTableName + " TAGS ('" + tagValue + "')"; tdengineJdbcTemplate.execute(createChildTableSql); // 缓存表存在信息 // 不使用缓存存储表存在信息 System.out.println("创建子表: " + childTableName); } catch (Exception e) { System.err.println("创建子表失败: " + e.getMessage()); } } /** * 插入数据到TDengine * @param superTableName TDengine超级表名 * @param record 记录 */ public void insertIntoTDengine(String superTableName, Map<String, Object> record) { long startTime = System.currentTimeMillis(); int retryCount = 0; while (retryCount < maxRetries) { try { // 获取标签列名(从配置中获取) String tagColumn = tagColumnsConfig; // 获取标签值 Object tagValueObj = record.get(tagColumn); String tagValue = (tagValueObj != null) ? tagValueObj.toString() : "null"; // 生成子表名(使用标签值) String childTableName = superTableName + "_" + tagValue; // 优化:仅在缓存中不存在时检查子表 String cacheKey = "tdengine:table:" + childTableName; // 不使用缓存检查表是否存在 // 直接检查表是否存在 if (!isTableExists(childTableName)) { // 确保子表存在 ensureChildTableExists(superTableName, childTableName, tagValue); // 缓存表存在信息,避免频繁检查 // 不使用缓存存储表存在信息 } // 构建SQL StringBuilder sql = new StringBuilder(); sql.append("INSERT INTO ").append(childTableName).append(" USING ").append(superTableName) .append(" TAGS ('").append(tagValue).append("') (").append(tdengineTimestampField).append(", "); // 添加列名 boolean first = true; for (String columnName : record.keySet()) { // 跳过标签列和时间戳字段 if (tagColumn.equals(columnName) || tdengineTimestampField.equals(columnName)) { continue; } if (!first) { sql.append(", "); } // 处理关键字冲突 sql.append(escapeKeyword(columnName)); first = false; } // 获取时间戳字段的值 Object timestampValue = record.get(timestampField); if (timestampValue == null) { // 如果时间戳为空,跳过这条记录 System.err.println("警告: 记录的时间戳字段为空,跳过该记录"); return; } String formattedTimestamp; if (timestampValue instanceof Timestamp) { // 格式化时间戳 Timestamp ts = (Timestamp) timestampValue; formattedTimestamp = "'" + TimeUtils.formatWithMillis(TimeUtils.from(ts)) + "'"; } else if (timestampValue instanceof LocalDateTime) { // 格式化LocalDateTime LocalDateTime ldt = (LocalDateTime) timestampValue; formattedTimestamp = "'" + TimeUtils.formatWithMillis(ldt) + "'"; } else { // 其他情况使用字符串值 formattedTimestamp = "'" + timestampValue.toString() + "'"; } sql.append(") VALUES (").append(formattedTimestamp).append(", "); // 添加值 first = true; for (Map.Entry<String, Object> entry : record.entrySet()) { String columnName = entry.getKey(); Object value = entry.getValue(); // 跳过标签列和时间戳字段 if (tagColumn.equals(columnName) || tdengineTimestampField.equals(columnName)) { continue; } if (!first) { sql.append(", "); } // 处理NULL值 if (value == null) { sql.append("NULL"); } else if (value instanceof String || value instanceof Timestamp || value instanceof LocalDateTime) { // 对字符串和时间戳类型进行特殊处理 if (value instanceof Timestamp) { // 格式化时间戳 Timestamp ts = (Timestamp) value; sql.append("'").append(TimeUtils.formatWithMillis(TimeUtils.from(ts))).append("'"); } else if (value instanceof LocalDateTime) { // 格式化LocalDateTime LocalDateTime ldt = (LocalDateTime) value; sql.append("'").append(TimeUtils.formatWithMillis(ldt)).append("'"); } else { // 字符串值需要添加引号,并处理NULL字符串 String strValue = value.toString(); if ("NULL".equalsIgnoreCase(strValue)) { sql.append("NULL"); } else { sql.append("'").append(strValue.replace("'", "''")).append("'"); } } } else { // 其他类型直接添加 sql.append(value.toString()); } first = false; } sql.append(")"); // System.out.println("SQL语句: " + sql.toString()); tdengineJdbcTemplate.execute(sql.toString()); // 更新性能监控 performanceMonitorService.incrementCounter("data.migration.insert.success", 1); break; // 成功执行,跳出重试循环 } catch (Exception e) { retryCount++; System.err.println("数据插入失败,正在进行第" + retryCount + "次重试: " + e.getMessage()); if (retryCount >= maxRetries) { System.err.println("数据插入最终失败: " + e.getMessage()); performanceMonitorService.incrementCounter("data.migration.insert.failure", 1); throw new RuntimeException("数据插入失败", e); } else { try { Thread.sleep(retryInterval * retryCount); // 使用配置的重试间隔和指数退避 } catch (InterruptedException ie) { Thread.currentThread().interrupt(); throw new RuntimeException("重试被中断", ie); } } } finally { long endTime = System.currentTimeMillis(); performanceMonitorService.recordTime("data.migration.insert", endTime - startTime); } } } /** * 确保子表存在 * @param superTableName 超级表名 * @param childTableName 子表名 * @param tagValue 标签值 */ private void ensureChildTableExists(String superTableName, String childTableName, String tagValue) { // 使用类级锁来确保在多线程环境下不会重复创建表 synchronized (DataMigrationService.class) { try { // 首先确保超级表存在(使用缓存优化) String superTableCacheKey = "tdengine:stable:" + superTableName; // 不使用缓存检查超级表是否存在 // 直接确保超级表存在 ensureSuperTableExists(superTableName); // 不使用缓存存储超级表存在信息 // 检查子表是否存在 if (!isTableExists(childTableName)) { createChildTable(superTableName, childTableName, tagValue); } } catch (Exception e) { System.err.println("确保子表存在时失败: " + e.getMessage()); // 不打印完整堆栈,减少日志输出 } } } /** * 确保超级表存在 * @param superTableName 超级表名 */ private void ensureSuperTableExists(String superTableName) { try { // 检查超级表是否存在 if (!isTableExists(superTableName)) { // 超级表应该已经由TableAutoCreateService创建,这里只是确保缓存存在 // 不再创建默认的超级表结构 System.out.println("警告: 超级表 " + superTableName + " 不存在,需要先分析MySQL表结构并创建"); } } catch (Exception e) { System.err.println("确保超级表存在时失败: " + e.getMessage()); // 不打印完整堆栈,减少日志输出 } } /** * 处理SQL关键字冲突,为字段名添加反引号 * @param columnName 字段名 * @return 处理后的字段名 */ private String escapeKeyword(String columnName) { // TDengine中的关键字列表(部分) String[] keywords = {"query", "order", "group", "limit", "offset", "select", "from", "where", "insert", "update", "delete"}; for (String keyword : keywords) { if (keyword.equalsIgnoreCase(columnName)) { return "`" + columnName + "`"; } } return columnName; } }报错:数据插入失败,正在进行第2次重试: StatementCallback; uncategorized SQLException for SQL [INSERT INTO my_battery_info_M240910700000001 USING my_battery_info TAGS (‘M240910700000001’) (ts, id, battery_voltage, battery_current, battery_soc, battery_hard_version, battery_soft_version, battery_work_mode, battery_protect_code, battery_error_code, battery_temperature_max, battery_temperature_min, battery_voltage_max, battery_voltage_min, mos_status, mos_temp, battery_cycle_times, steady_status, cell_voltage, model, manufacture, imei, imsi, iccid, tracker_hardware_version, tracker_software_version, csq, network_type, location_mode, longitude, longitude_direction, latitude, latitude_direction, gps_speed, gps_signal, satellite_num, accuracy, flag, client_id, topic, product_key, handled, todo_now, needack, businessmode, upload_time, create_time, update_time, payload, project_id, sn, soh, battery_temperature_avg, charge_mode, low_battery_warn, charge_current_max, discharge_current_max, charge_current_avg, discharge_current_avg, gps_cog, total_charge_energy, charge_cycles_history, charge_capacity_history, discharge_capacity_history, ntc1, ntc2, ntc3, ntc4, tracker_communication_status, battery_kind, dsoc, correction_rate, tsoc, bsoc, fcc, actual_soh, charging_sop, discharging_sop, task_max_delay_1ms, task_max_delay_10ms, task_max_delay_100ms, task_max_delay_500ms, task_max_time_1ms, task_max_time_10ms, task_max_time_100ms, task_max_time_500ms, empty_count, cell_info, gps_max_signal, msg_delay_time, data_type, battery_vehicle_status, storage_status, ota_status, lock_status, battery_vehicle_alarm_status) VALUES (‘2024-09-25 11:42:07.000’, 969, 71200, 0, 14, NULL, ‘V1.00’, ‘48’, NULL, ‘000000000000000000000000’, 26, 26, 3244, 3231, 8, 28, 2, NULL, NULL, NULL, NULL, ‘865271061996824’, NULL, ‘898600231618F2649946’, NULL, ‘STR_SW_M_B1_B100_101_test’, NULL, NULL, NULL, ‘0’, ‘E’, ‘0’, ‘N’, NULL, ‘23’, NULL, NULL, 1, NULL, NULL, NULL, 0, NULL, NULL, NULL, ‘2024-09-25 11:42:07.000’, ‘2024-09-25 11:42:07.000’, ‘2024-09-25 11:42:07.000’, ‘{“ts”:1727235727,“code:200,“data”:[1,0,1,0,14,0,2,26,26,28,2,200,12,172,12,159,48,8,0,0,0,0,0,0,0,0,0,0,0,0,0,0,241,104],“tracker”:[0,0,23,“865271061996824”,“898600231618F2649946”,“STR_SW_M_B1_B100_101_test”]}’, 38, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ‘2’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 0, 0, 0, 0)]; SQL state []; error code [1547]; TDengine ERROR (0x60b): sql: INSERT INTO my_battery_info_M240910700000001 USING my_battery_info TAGS (‘M240910700000001’) (ts, id, battery_voltage, battery_current, battery_soc, battery_hard_version, battery_soft_version, battery_work_mode, battery_protect_code, battery_error_code, battery_temperature_max, battery_temperature_min, battery_voltage_max, battery_voltage_min, mos_status, mos_temp, battery_cycle_times, steady_status, cell_voltage, model, manufacture, imei, imsi, iccid, tracker_hardware_version, tracker_software_version, csq, network_type, location_mode, longitude, longitude_direction, latitude, latitude_direction, gps_speed, gps_signal, satellite_num, accuracy, flag, client_id, topic, product_key, handled, todo_now, needack, businessmode, upload_time, create_time, update_time, payload, project_id, sn, soh, battery_temperature_avg, charge_mode, low_battery_warn, charge_current_max, discharge_current_max, charge_current_avg, discharge_current_avg, gps_cog, total_charge_energy, charge_cycles_history, charge_capacity_history, discharge_capacity_history, ntc1, ntc2, ntc3, ntc4, tracker_communication_status, battery_kind, dsoc, correction_rate, tsoc, bsoc, fcc, actual_soh, charging_sop, discharging_sop, task_max_delay_1ms, task_max_delay_10ms, task_max_delay_100ms, task_max_delay_500ms, task_max_time_1ms, task_max_time_10ms, task_max_time_100ms, task_max_time_500ms, empty_count, cell_info, gps_max_signal, msg_delay_time, data_type, battery_vehicle_status, storage_status, ota_status, lock_status, battery_vehicle_alarm_status) VALUES (‘2024-09-25 11:42:07.000’, 969, 71200, 0, 14, NULL, ‘V1.00’, ‘48’, NULL, ‘000000000000000000000000’, 26, 26, 3244, 3231, 8, 28, 2, NULL, NULL, NULL, NULL, ‘865271061996824’, NULL, ‘898600231618F2649946’, NULL, ‘STR_SW_M_B1_B100_101_test’, NULL, NULL, NULL, ‘0’, ‘E’, ‘0’, ‘N’, NULL, ‘23’, NULL, NULL, 1, NULL, NULL, NULL, 0, NULL, NULL, NULL, ‘2024-09-25 11:42:07.000’, ‘2024-09-25 11:42:07.000’, ‘2024-09-25 11:42:07.000’, ‘{“ts”:1727235727,“code:200,“data”:[1,0,1,0,14,0,2,26,26,28,2,200,12,172,12,159,48,8,0,0,0,0,0,0,0,0,0,0,0,0,0,0,241,104],“tracker”:[0,0,23,“865271061996824”,“898600231618F2649946”,“STR_SW_M_B1_B100_101_test”]}’, 38, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ‘2’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 0, 0, 0, 0), desc: Timestamp data out of range。但是package com.bms.sync.data.service; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.beans.factory.annotation.Value; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.scheduling.annotation.Async; import org.springframework.stereotype.Service; import javax.sql.DataSource; import java.sql.Timestamp; import java.time.LocalDateTime; import java.time.format.DateTimeFormatter; import java.util.List; import java.util.Map; import java.util.Arrays; @Service public class DataMigrationService { private final JdbcTemplate mysqlJdbcTemplate; private final JdbcTemplate tdengineJdbcTemplate; @Value("${table.tag.columns}") private String tagColumnsConfig; @Value("${performance.batch-size.historical}") private int historicalBatchSize; @Value("${performance.batch-size.incremental}") private int incrementalBatchSize; @Autowired private PerformanceMonitorService performanceMonitorService; @Autowired private MultiLevelCacheService multiLevelCacheService; @Autowired private TableAutoCreateService tableAutoCreateService; @Autowired private RedisQueueService redisQueueService; @Autowired public DataMigrationService(@Qualifier("mysqlDataSource") DataSource mysqlDataSource, @Qualifier("tdengineDataSource") DataSource tdengineDataSource, PerformanceMonitorService performanceMonitorService, MultiLevelCacheService multiLevelCacheService, TableAutoCreateService tableAutoCreateService, RedisQueueService redisQueueService) { this.mysqlJdbcTemplate = new JdbcTemplate(mysqlDataSource); this.tdengineJdbcTemplate = new JdbcTemplate(tdengineDataSource); this.performanceMonitorService = performanceMonitorService; this.multiLevelCacheService = multiLevelCacheService; this.tableAutoCreateService = tableAutoCreateService; this.redisQueueService = redisQueueService; } /** * 迁移数据(支持批量处理) * @param mysqlTableName MySQL表名 * @param superTableName TDengine超级表名 */ public void migrateData(String mysqlTableName, String superTableName) { long startTime = System.currentTimeMillis(); try { // 确保表结构已创建 ensureTableStructureExists(mysqlTableName, superTableName); // 1. 获取总记录数 int totalRecords = getTotalRecordCount(mysqlTableName); if (totalRecords == 0) { System.out.println("没有数据需要迁移"); return; } // 2. 分批迁移数据(增加批处理大小以提高效率) int batchSize = historicalBatchSize; // 每批处理5000条记录 int totalBatches = (int) Math.ceil((double) totalRecords / batchSize); System.out.println("开始迁移数据,总记录数: " + totalRecords + ",总批次数: " + totalBatches); // 使用Redis队列处理历史数据 processHistoricalDataWithQueue(mysqlTableName, superTableName, totalRecords, batchSize); System.out.println("数据迁移完成"); } catch (Exception e) { System.err.println("数据迁移失败: " + e.getMessage()); e.printStackTrace(); } finally { long endTime = System.currentTimeMillis(); performanceMonitorService.recordTime("data.migration", endTime - startTime); performanceMonitorService.incrementCounter("data.migration.totalRecords", getTotalRecordCount(mysqlTableName)); } } /** * 确保表结构已创建 * @param mysqlTableName MySQL表名 * @param superTableName TDengine超级表名 */ private void ensureTableStructureExists(String mysqlTableName, String superTableName) { try { // 从配置中获取标签列 String[] tagColumnsArray = tagColumnsConfig.split(","); List<String> tagColumns = Arrays.asList(tagColumnsArray); // 自动创建表结构 tableAutoCreateService.autoCreateTableStructure(mysqlTableName, superTableName, tagColumns); } catch (Exception e) { System.err.println("确保表结构存在时失败: " + e.getMessage()); e.printStackTrace(); } } /** * 使用Redis队列处理历史数据 * @param mysqlTableName MySQL表名 * @param superTableName TDengine超级表名 * @param totalRecords 总记录数 * @param batchSize 批处理大小 */ private void processHistoricalDataWithQueue(String mysqlTableName, String superTableName, int totalRecords, int batchSize) { try { String queueName = mysqlTableName + "_to_" + superTableName; // 清空之前的队列数据 redisQueueService.clearQueue(queueName); // 将历史数据分批放入Redis队列 int totalBatches = (int) Math.ceil((double) totalRecords / batchSize); System.out.println("开始将历史数据放入Redis队列,总记录数: " + totalRecords + ", 总批次数: " + totalBatches + ", 批处理大小: " + batchSize); long startTime = System.currentTimeMillis(); for (int i = 0; i < totalBatches; i++) { int offset = i * batchSize; String selectSql = "SELECT * FROM " + mysqlTableName + " LIMIT " + batchSize + " OFFSET " + offset; List<Map<String, Object>> records = mysqlJdbcTemplate.queryForList(selectSql); // 将数据放入Redis队列 redisQueueService.pushHistoricalDataToQueue(queueName, records); // 每处理10批打印一次进度信息 if ((i + 1) % 10 == 0 || i == totalBatches - 1) { System.out.println("已将批次 " + (i + 1) + "/" + totalBatches + " 放入Redis队列,当前批次记录数: " + records.size()); } } long endTime = System.currentTimeMillis(); System.out.println("历史数据已全部放入Redis队列,队列大小: " + redisQueueService.getQueueSize(queueName) + ",耗时: " + (endTime - startTime) + "ms"); // 从Redis队列中取出数据并同步到TDengine syncDataFromQueue(queueName, superTableName); } catch (Exception e) { System.err.println("使用Redis队列处理历史数据失败: " + e.getMessage()); e.printStackTrace(); } } /** * 从Redis队列中取出数据并同步到TDengine * @param queueName 队列名称 * @param superTableName TDengine超级表名 */ private void syncDataFromQueue(String queueName, String superTableName) { try { long totalSynced = 0; int batchSize = incrementalBatchSize; // 从队列中每次取出100条记录进行同步 long startTime = System.currentTimeMillis(); System.out.println("开始从Redis队列同步数据到TDengine,批处理大小: " + batchSize); int batchCount = 0; while (redisQueueService.getQueueSize(queueName) > 0) { List<Object> records = redisQueueService.popBatchFromQueue(queueName, batchSize); if (records != null && !records.isEmpty()) { // 转换为正确的类型 List<Map<String, Object>> convertedRecords = (List<Map<String, Object>>) (List<?>) records; // 批量插入到TDengine batchInsertIntoTDengine(superTableName, convertedRecords); totalSynced += records.size(); batchCount++; // 每处理10批打印一次进度信息 if (batchCount % 10 == 0) { System.out.println("已同步 " + batchCount + " 批次数据,当前批次记录数: " + records.size() + ",总计已同步: " + totalSynced); } } } long endTime = System.currentTimeMillis(); // 只有在有数据成功同步时才打印完成信息 if (totalSynced > 0) { System.out.println("从Redis队列同步数据完成,总计同步: " + totalSynced + " 条记录,总批次数: " + batchCount + ",耗时: " + (endTime - startTime) + "ms"); } else { System.out.println("Redis队列中没有数据需要同步"); } } catch (Exception e) { System.err.println("从Redis队列同步数据失败: " + e.getMessage()); e.printStackTrace(); } } /** * 获取记录数 * @param mysqlTableName MySQL表名 * @return 记录数 */ public int getRecordCount(String mysqlTableName) { // 先从缓存中获取 String cacheKey = "table:count:" + mysqlTableName; Object cachedCount = multiLevelCacheService.get(cacheKey); if (cachedCount != null) { return (Integer) cachedCount; } String countSql = "SELECT COUNT(*) FROM " + mysqlTableName; Integer count = mysqlJdbcTemplate.queryForObject(countSql, Integer.class); // 将结果放入缓存 if (count != null) { multiLevelCacheService.put(cacheKey, count); } return count != null ? count : 0; } /** * 获取总记录数 * @param tableName 表名 * @return 总记录数 */ public int getTotalRecordCount(String tableName) { // 先从缓存中获取 String cacheKey = "table:count:" + tableName; Object cachedCount = multiLevelCacheService.get(cacheKey); if (cachedCount != null) { return (Integer) cachedCount; } String countSql = "SELECT COUNT(*) FROM " + tableName; Integer count = mysqlJdbcTemplate.queryForObject(countSql, Integer.class); // 将结果放入缓存 if (count != null) { multiLevelCacheService.put(cacheKey, count); } return count != null ? count : 0; } /** * 批量插入数据到TDengine * @param superTableName TDengine超级表名 * @param records 记录列表 */ private void batchInsertIntoTDengine(String superTableName, List<Map<String, Object>> records) { if (records.isEmpty()) { return; } try { // 一次性检查并创建所有需要的子表 ensureAllChildTablesExist(superTableName, records); // 然后批量插入数据 for (Map<String, Object> record : records) { insertIntoTDengine(superTableName, record); } } catch (Exception e) { System.err.println("批量数据插入失败: " + e.getMessage()); // 不打印完整堆栈,减少日志输出 } } /** * 一次性检查并创建所有需要的子表 * @param superTableName 超级表名 * @param records 记录列表 */ private void ensureAllChildTablesExist(String superTableName, List<Map<String, Object>> records) { try { // 首先确保超级表存在 String superTableCacheKey = "tdengine:stable:" + superTableName; if (!multiLevelCacheService.exists(superTableCacheKey)) { ensureSuperTableExists(superTableName); multiLevelCacheService.put(superTableCacheKey, true, 3600); // 缓存1小时 } // 收集所有需要的子表名 java.util.Set<String> childTableNames = new java.util.HashSet<>(); java.util.Map<String, String> childTableToTagMap = new java.util.HashMap<>(); String tagColumn = tagColumnsConfig; for (Map<String, Object> record : records) { Object tagValueObj = record.get(tagColumn); String tagValue = (tagValueObj != null) ? tagValueObj.toString() : "null"; String childTableName = superTableName + "_" + tagValue; // 仅处理缓存中不存在的表 String cacheKey = "tdengine:table:" + childTableName; if (!multiLevelCacheService.exists(cacheKey) && !childTableNames.contains(childTableName)) { childTableNames.add(childTableName); childTableToTagMap.put(childTableName, tagValue); } } // 一次性查询所有子表是否存在 if (!childTableNames.isEmpty()) { // 使用更高效的方式检查表是否存在 for (String childTableName : childTableNames) { if (!isTableExists(childTableName)) { String tagValue = childTableToTagMap.get(childTableName); createChildTable(superTableName, childTableName, tagValue); } else { // 缓存已存在的表信息 multiLevelCacheService.put("tdengine:table:" + childTableName, true, 3600); } } } } catch (Exception e) { System.err.println("批量检查子表存在时失败: " + e.getMessage()); // 不打印完整堆栈,减少日志输出 } } /** * 检查TDengine表是否存在 * @param tableName 表名 * @return 是否存在 */ private boolean isTableExists(String tableName) { try { String checkSql = "SELECT table_name FROM information_schema.ins_tables WHERE table_name = '" + tableName + "'"; List<Map<String, Object>> result = tdengineJdbcTemplate.queryForList(checkSql); return !result.isEmpty(); } catch (Exception e) { System.err.println("检查表是否存在时出错: " + e.getMessage()); return false; } } /** * 创建子表 * @param superTableName 超级表名 * @param childTableName 子表名 * @param tagValue 标签值 */ private void createChildTable(String superTableName, String childTableName, String tagValue) { try { String createChildTableSql = "CREATE TABLE IF NOT EXISTS " + childTableName + " USING " + superTableName + " TAGS ('" + tagValue + "')"; tdengineJdbcTemplate.execute(createChildTableSql); // 缓存表存在信息 multiLevelCacheService.put("tdengine:table:" + childTableName, true, 3600); System.out.println("创建子表: " + childTableName); } catch (Exception e) { System.err.println("创建子表失败: " + e.getMessage()); } } /** * 插入数据到TDengine * @param superTableName TDengine超级表名 * @param record 记录 */ private void insertIntoTDengine(String superTableName, Map<String, Object> record) { long startTime = System.currentTimeMillis(); try { // 获取标签列名(从配置中获取) String tagColumn = tagColumnsConfig; // 获取标签值 Object tagValueObj = record.get(tagColumn); String tagValue = (tagValueObj != null) ? tagValueObj.toString() : "null"; // 生成子表名(使用标签值) String childTableName = superTableName + "_" + tagValue; // 优化:仅在缓存中不存在时检查子表 String cacheKey = "tdengine:table:" + childTableName; if (!multiLevelCacheService.exists(cacheKey)) { // 确保子表存在 ensureChildTableExists(superTableName, childTableName, tagValue); // 缓存表存在信息,避免频繁检查 multiLevelCacheService.put(cacheKey, true, 3600); // 缓存1小时 } // 构建SQL StringBuilder sql = new StringBuilder(); sql.append("INSERT INTO ").append(childTableName).append(" USING ").append(superTableName) .append(" TAGS ('").append(tagValue).append("') (ts, "); // 添加列名 boolean first = true; for (String columnName : record.keySet()) { // 跳过标签列和时间戳字段 if (tagColumn.equals(columnName) || "ts".equals(columnName)) { continue; } if (!first) { sql.append(", "); } // 处理关键字冲突 sql.append(escapeKeyword(columnName)); first = false; } sql.append(") VALUES (NOW(), "); // 添加值 first = true; for (Map.Entry<String, Object> entry : record.entrySet()) { String columnName = entry.getKey(); Object value = entry.getValue(); // 跳过标签列和时间戳字段 if (tagColumn.equals(columnName) || "ts".equals(columnName)) { continue; } if (!first) { sql.append(", "); } // 处理NULL值 if (value == null) { sql.append("NULL"); } else if (value instanceof String || value instanceof Timestamp || value instanceof LocalDateTime) { // 对字符串和时间戳类型进行特殊处理 if (value instanceof Timestamp) { // 格式化时间戳 Timestamp ts = (Timestamp) value; DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS"); LocalDateTime ldt = ts.toLocalDateTime(); sql.append("'").append(ldt.format(formatter)).append("'"); } else if (value instanceof LocalDateTime) { // 格式化LocalDateTime LocalDateTime ldt = (LocalDateTime) value; DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS"); sql.append("'").append(ldt.format(formatter)).append("'"); } else { // 字符串值需要添加引号,并处理NULL字符串 String strValue = value.toString(); if ("NULL".equalsIgnoreCase(strValue)) { sql.append("NULL"); } else { sql.append("'").append(strValue.replace("'", "''")).append("'"); } } } else { // 其他类型直接添加 sql.append(value.toString()); } first = false; } sql.append(")"); // System.out.println("SQL语句: " + sql.toString()); tdengineJdbcTemplate.execute(sql.toString()); // 更新性能监控 performanceMonitorService.incrementCounter("data.migration.insert.success", 1); } catch (Exception e) { System.err.println("数据插入失败: " + e.getMessage()); e.printStackTrace(); performanceMonitorService.incrementCounter("data.migration.insert.failure", 1); } finally { long endTime = System.currentTimeMillis(); performanceMonitorService.recordTime("data.migration.insert", endTime - startTime); } } /** * 确保子表存在 * @param superTableName 超级表名 * @param childTableName 子表名 * @param tagValue 标签值 */ private void ensureChildTableExists(String superTableName, String childTableName, String tagValue) { try { // 首先确保超级表存在(使用缓存优化) String superTableCacheKey = "tdengine:stable:" + superTableName; if (!multiLevelCacheService.exists(superTableCacheKey)) { ensureSuperTableExists(superTableName); multiLevelCacheService.put(superTableCacheKey, true, 3600); // 缓存1小时 } // 检查子表是否存在 if (!isTableExists(childTableName)) { createChildTable(superTableName, childTableName, tagValue); } } catch (Exception e) { System.err.println("确保子表存在时失败: " + e.getMessage()); // 不打印完整堆栈,减少日志输出 } } /** * 确保超级表存在 * @param superTableName 超级表名 */ private void ensureSuperTableExists(String superTableName) { try { // 检查超级表是否存在 if (!isTableExists(superTableName)) { // 超级表应该已经由TableAutoCreateService创建,这里只是确保缓存存在 // 不再创建默认的超级表结构 System.out.println("警告: 超级表 " + superTableName + " 不存在,需要先分析MySQL表结构并创建"); } } catch (Exception e) { System.err.println("确保超级表存在时失败: " + e.getMessage()); // 不打印完整堆栈,减少日志输出 } } /** * 处理SQL关键字冲突,为字段名添加反引号 * @param columnName 字段名 * @return 处理后的字段名 */ private String escapeKeyword(String columnName) { // TDengine中的关键字列表(部分) String[] keywords = {"query", "order", "group", "limit", "offset", "select", "from", "where", "insert", "update", "delete"}; for (String keyword : keywords) { if (keyword.equalsIgnoreCase(columnName)) { return "`" + columnName + "`"; } } return columnName; } }是正常插入tdengine,修复好第一个文件
11-15
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值