人工智能-项目实践-数据预处理-基于SpringBoot、MyBatisPlus、ElementUI、G2Plot、Echart

package com.gccloud.bigscreen.core.module.dataset.utils; import com.gccloud.bigscreen.core.module.dataset.common.DbCommon; import com.gccloud.bigscreen.core.module.dataset.dto.DatasetParamDto; import com.gccloud.bigscreen.core.module.dataset.entity.DatasourceConfig; import com.gccloud.bigscreen.core.module.dataset.constant.ReportDbType; import com.gccloud.bigscreen.core.exception.GlobalException; import lombok.extern.slf4j.Slf4j; import net.sf.jsqlparser.parser.CCJSqlParserManager; import net.sf.jsqlparser.statement.Statement; import net.sf.jsqlparser.statement.delete.Delete; import net.sf.jsqlparser.statement.insert.Insert; import net.sf.jsqlparser.statement.replace.Replace; import net.sf.jsqlparser.statement.select.Select; import net.sf.jsqlparser.statement.update.Update; import net.sf.jsqlparser.util.TablesNamesFinder; import org.apache.commons.lang3.exception.ExceptionUtils; import org.json.JSONObject; import org.springframework.util.StringUtils; import java.io.StringReader; import java.sql.*; import java.text.SimpleDateFormat; import java.util.Date; import java.util.*; /** * @Description: 数据库连接工具类 * @Author liyong * @Date 2021/11/25 */ @Slf4j public class TelePGDBUtils extends DbCommon { private static final String CREATE_TABLE_SQL = "CREATE TABLE "; private static final String DROP_TABLE_SQL = "DROP TABLE IF EXISTS "; private static final CCJSqlParserManager pm = new CCJSqlParserManager(); /** * 执行 sql脚本 获取数据 及结构 * * @param sql 脚本 * @param params 入参 * @param datasourceConfig 数据源 */ public static Map<String, List<Map<String, Object>>> getClickHouseValue(String sql, List<DatasetParamDto> params, DatasourceConfig datasourceConfig) { List<DatasetParamDto> copyParamsList = new ArrayList<>(params); sql = updateParamsConfig(sql, copyParamsList); Map<String, List<Map<String, Object>>> map = new HashMap<>(); List<Map<String, Object>> dataPreview = new ArrayList<>(); List<Map<String, Object>> structurePreview = new ArrayList<>(); Connection conn = getConnection(datasourceConfig); PreparedStatement pStemt; try { pStemt = conn.prepareStatement(sql); if (copyParamsList.size() > 0) { int size = copyParamsList.size(); for (int i = 0; i < size; i++) { if (StringUtils.isEmpty(copyParamsList.get(i).getValue())) { throw new GlobalException("参数值不能为空"); } //判断参数类型是否是时间类型，进行转换 if (("Date").equalsIgnoreCase(copyParamsList.get(i).getType())) { if (ReportDbType.TELEPG.getUpInfo().equalsIgnoreCase(datasourceConfig.getSourceType())) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Date date = sdf.parse(copyParamsList.get(i).getValue()); Timestamp timestamp = new Timestamp(date.getTime()); pStemt.setTimestamp(i + 1, timestamp); } else { pStemt.setObject(i + 1, copyParamsList.get(i).getValue()); } } else if (("Integer").equalsIgnoreCase(copyParamsList.get(i).getType())) { if (ReportDbType.TELEPG.getUpInfo().equalsIgnoreCase(datasourceConfig.getSourceType())) { pStemt.setInt(i + 1, Integer.parseInt(copyParamsList.get(i).getValue())); } else { pStemt.setObject(i + 1, Integer.parseInt(copyParamsList.get(i).getValue())); } } else { pStemt.setObject(i + 1, copyParamsList.get(i).getValue()); } } } ResultSet clickRs = pStemt.executeQuery(); ResultSetMetaData metaData = clickRs.getMetaData(); int columnCount = metaData.getColumnCount(); while (clickRs.next()) { //LinkedHashMap 保证数据字段顺序同数据库一致 LinkedHashMap<String, Object> map1 = new LinkedHashMap<>(); for (int i = 1; i <= columnCount; i++) { map1.put(metaData.getColumnName(i), clickRs.getObject(i)); } dataPreview.add(map1); } for (int i = 0; i < columnCount; i++) { Map<String, Object> map2 = new HashMap<>(); map2.put("columnName", metaData.getColumnName(i + 1)); map2.put("columnType", metaData.getColumnTypeName(i + 1)); structurePreview.add(map2); } map.put("dataPreview", dataPreview); map.put("structurePreview", structurePreview); } catch (SQLException e) { if (e.getMessage().contains("doesn't exist")) { log.error("数据查询失败：查询表不存在：{}", e.getMessage()); throw new GlobalException("数据查询失败：查询表不存在"); } else { log.error("数据查询失败：{}", e.getMessage()); throw new GlobalException("数据查询失败"); } } catch (ArrayIndexOutOfBoundsException e) { log.error(e.getMessage()); throw new GlobalException("请检查参数配置与SQL加工脚本是否对应"); } catch (Exception e) { log.error("数据查询失败:{}", ExceptionUtils.getStackTrace(e)); throw new GlobalException("数据查询失败" + e.getMessage()); } finally {//关闭连接 finalUtils(conn); } return map; } public static Map<String, List<Map<String, Object>>> getHiveFieldComment(String sql, List<DatasetParamDto> params, DatasourceConfig datasourceConfig) { Map<String, List<Map<String, Object>>> map = new HashMap<>(); List<Map<String, Object>> dataPreview = new ArrayList<>(); List<Map<String, Object>> structurePreview = new ArrayList<>(); Connection conn = getConnection(datasourceConfig); PreparedStatement pStemt; try { pStemt = conn.prepareStatement(sql); insertParamsConfig(params, pStemt); ResultSet clickRs = pStemt.executeQuery(); ResultSetMetaData metaData = clickRs.getMetaData(); int columnCount = metaData.getColumnCount(); while (clickRs.next()) { //LinkedHashMap 保证数据字段顺序同数据库一致 LinkedHashMap<String, Object> map1 = new LinkedHashMap<>(); for (int i = 1; i <= columnCount; i++) { if (i == 2) { continue; } map1.put(metaData.getColumnName(i), clickRs.getObject(i)); } dataPreview.add(map1); } for (int i = 0; i < columnCount; i++) { if (i == 1) { continue; } Map<String, Object> map2 = new HashMap<>(); map2.put("columnName", metaData.getColumnName(i + 1)); map2.put("columnType", metaData.getColumnTypeName(i + 1)); structurePreview.add(map2); } map.put("dataPreview", dataPreview); map.put("structurePreview", structurePreview); } catch (SQLException e) { if (e.getMessage().contains("doesn't exist")) { log.error("数据查询失败：查询表不存在：{}", e.getMessage()); throw new GlobalException("数据查询失败：查询表不存在"); } else { log.error("数据查询�