Examples with DbMapping com.alibaba.otter.canal.client.adapter.phoenix.config.MappingConfig.DbMapping used on opensource projects

Example 1 with DbMapping

use of com.alibaba.otter.canal.client.adapter.phoenix.config.MappingConfig.DbMapping in project canal by alibaba.

the class PhoenixEtlService method syncSchema.

@SuppressWarnings("unchecked")
private static boolean syncSchema(Connection srcDS, Connection targetDS, MappingConfig config) {
    DbMapping dbMapping = config.getDbMapping();
    if (dbMapping.getMapAll() && dbMapping.isAlter()) {
        // 检查字段是否缺失
        Map<String, Integer> targetColumnType = new LinkedHashMap<>();
        String targetTable = SyncUtil.getDbTableName(dbMapping);
        try {
            Util.sqlRS(targetDS, "SELECT * FROM " + targetTable + " LIMIT 1", rs -> {
                try {
                    ResultSetMetaData rsd = rs.getMetaData();
                    int columnCount = rsd.getColumnCount();
                    for (int i = 1; i <= columnCount; i++) {
                        targetColumnType.put(rsd.getColumnName(i).toLowerCase(), rsd.getColumnType(i));
                    }
                } catch (Exception e) {
                    logger.error(dbMapping.getTable() + " etl failed! ==>" + e.getMessage(), e);
                }
            });
        } catch (RuntimeException e) {
            if (!e.getCause().getClass().getName().endsWith("TableNotFoundException")) {
                throw e;
            }
        }
        StringBuilder missing = new StringBuilder();
        StringBuilder constraint = new StringBuilder();
        Util.sqlRS(srcDS, "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '" + dbMapping.getDatabase() + "'  AND TABLE_NAME = '" + dbMapping.getTable() + "'", rs -> {
            try {
                List<String> excludeColumns = config.getDbMapping().getExcludeColumns();
                while (rs.next()) {
                    String name = rs.getString("COLUMN_NAME");
                    String lower = name.toLowerCase();
                    String colType = rs.getString("COLUMN_TYPE");
                    if (targetColumnType.get(lower) == null && !excludeColumns.contains(lower)) {
                        boolean isPri = rs.getString("COLUMN_KEY").equals("PRI");
                        String[] args = splitNotEmpty(colType.replaceAll("^\\w+(?:\\(([^)]*)\\))?[\\s\\S]*$", "$1"));
                        missing.append(dbMapping.escape(name)).append(" ").append(TypeUtil.getPhoenixType(rs.getString("DATA_TYPE").toUpperCase(), args, colType.contains("unsigned"), dbMapping.isLimit()));
                        if (isPri) {
                            if (args.length > 0 && dbMapping.isLimit() || rs.getString("IS_NULLABLE").equals("NO")) {
                                missing.append(" NOT NULL");
                            }
                            constraint.append(dbMapping.escape(name)).append(',');
                        }
                        missing.append(',');
                    }
                }
            } catch (Exception e) {
                logger.error(dbMapping.getDatabase() + "." + dbMapping.getTable() + " schema failed! ==>" + e.getMessage(), e);
                throw new RuntimeException(e);
            }
        });
        if (missing.length() > 0) {
            String sql;
            if (targetColumnType.isEmpty()) {
                if (constraint.length() > 0) {
                    constraint.deleteCharAt(constraint.length() - 1);
                    missing.append("CONSTRAINT pk PRIMARY KEY(").append(constraint.toString()).append(")");
                } else {
                    missing.deleteCharAt(missing.length() - 1);
                }
                sql = "CREATE TABLE " + targetTable + " (" + missing.toString() + ")";
            } else {
                missing.deleteCharAt(missing.length() - 1);
                sql = "ALTER TABLE " + targetTable + " ADD " + missing.toString();
            }
            logger.info("schema missing: {} {}", targetColumnType, sql);
            try (PreparedStatement pstmt = targetDS.prepareStatement(sql)) {
                pstmt.executeUpdate();
            } catch (SQLException e) {
                logger.error("sync schema error: " + e.getMessage(), e);
            }
        } else {
            logger.debug("schema ok: {}", targetColumnType);
        }
        return true;
    }
    return false;
}

Example 2 with DbMapping

use of com.alibaba.otter.canal.client.adapter.phoenix.config.MappingConfig.DbMapping in project canal by alibaba.

the class PhoenixSyncService method alter.

private void alter(BatchExecutor batchExecutor, MappingConfig config, Dml dml, List<SQLStatement> stmtList, String configFile) throws SQLException {
    if (config.isDebug()) {
        logger.info("DML: {} {}", configFile, JSON.toJSONString(dml, SerializerFeature.WriteMapNullValue));
    }
    DbMapping dbMapping = config.getDbMapping();
    if (!dbMapping.isAlter()) {
        logger.info("not alterable table: {} {}", dml.getTable(), configFile);
        return;
    }
    Map<String, String> columnsMap = dbMapping.getTargetColumns();
    Map<String, String> columnsMap1 = new HashMap<>();
    for (Map.Entry<String, String> entry : columnsMap.entrySet()) {
        columnsMap1.put(entry.getValue(), entry.getKey());
    }
    String targetTable = SyncUtil.getDbTableName(dbMapping);
    Map<String, String> defValues = new HashMap<>();
    for (SQLStatement statement : stmtList) {
        if (statement instanceof SQLAlterTableStatement) {
            SQLAlterTableStatement alterTable = (SQLAlterTableStatement) statement;
            for (SQLAlterTableItem item : alterTable.getItems()) {
                if (item instanceof SQLAlterTableDropColumnItem) {
                    SQLAlterTableDropColumnItem dropColumnItem = (SQLAlterTableDropColumnItem) item;
                    if (!dbMapping.isDrop()) {
                        logger.info("drop table column disabled: {} {}", targetTable, dropColumnItem.getColumns());
                        continue;
                    }
                    for (SQLName sqlName : dropColumnItem.getColumns()) {
                        String name = Util.cleanColumn(sqlName.getSimpleName());
                        String sql = "ALTER TABLE " + targetTable + " DROP COLUMN IF EXISTS " + dbMapping.escape(columnsMap1.getOrDefault(name, name));
                        try {
                            logger.info("drop table column: {} {}", sql, batchExecutor.executeUpdate(sql));
                            dbMapping.removeTargetColumn(name);
                        } catch (Exception e) {
                            logger.warn("drop table column error: " + sql, e);
                        }
                    }
                } else if (item instanceof SQLAlterTableAddColumn) {
                    SQLAlterTableAddColumn addColumn = (SQLAlterTableAddColumn) item;
                    if (!dbMapping.getMapAll()) {
                        logger.info("add table column disabled: {} {}", targetTable, addColumn.getColumns());
                        continue;
                    }
                    for (SQLColumnDefinition definition : addColumn.getColumns()) {
                        String name = Util.cleanColumn(definition.getNameAsString());
                        if (dbMapping.getExcludeColumns().contains(name)) {
                            continue;
                        }
                        String sql = "ALTER TABLE " + targetTable + " ADD IF NOT EXISTS " + dbMapping.escape(name) + " " + TypeUtil.getPhoenixType(definition, dbMapping.isLimit());
                        try {
                            logger.info("add table column: {} {}", sql, batchExecutor.executeUpdate(sql));
                            dbMapping.addTargetColumn(name, name);
                            if (definition.getDefaultExpr() != null) {
                                String defVal = definition.getDefaultExpr().toString();
                                if (!defVal.equalsIgnoreCase("NULL") && !defVal.equalsIgnoreCase("NOT NULL") && name.length() > 0) {
                                    defValues.put(name, defVal);
                                }
                            }
                        } catch (Exception e) {
                            logger.error("add table column error: " + sql, e);
                            throw e;
                        }
                    }
                }
            }
        }
    }
    if (!defValues.isEmpty()) {
        StringBuilder defSql = new StringBuilder();
        defSql.append("UPSERT INTO ").append(targetTable).append("(");
        Set<Map.Entry<String, String>> pkSet = dbMapping.getTargetPk().entrySet();
        Set<Map.Entry<String, String>> defSet = defValues.entrySet();
        for (Map.Entry<String, String> entry : pkSet) {
            defSql.append(dbMapping.escape(entry.getKey())).append(",");
        }
        for (Map.Entry<String, String> entry : defSet) {
            defSql.append(dbMapping.escape(entry.getKey())).append(",");
        }
        defSql.deleteCharAt(defSql.length() - 1).append(") SELECT ");
        for (Map.Entry<String, String> entry : pkSet) {
            defSql.append(dbMapping.escape(entry.getKey())).append(",");
        }
        for (Map.Entry<String, String> entry : defSet) {
            defSql.append(entry.getValue()).append(",");
        }
        defSql.deleteCharAt(defSql.length() - 1).append(" FROM ").append(targetTable);
        try {
            logger.info("set column default value: {} {}", defSql, batchExecutor.executeUpdate(defSql.toString()));
            batchExecutor.commit();
        } catch (SQLException e) {
            logger.error("set column default value error: {}", defSql, e);
            batchExecutor.rollback();
            throw e;
        }
    }
}

Example 3 with DbMapping

use of com.alibaba.otter.canal.client.adapter.phoenix.config.MappingConfig.DbMapping in project canal by alibaba.

the class PhoenixSyncService method getTargetColumnType.

/**
 * 获取目标字段类型
 *
 * @param conn   sql connection
 * @param config 映射配置
 * @return 字段sqlType
 */
private Map<String, Integer> getTargetColumnType(Connection conn, MappingConfig config) {
    DbMapping dbMapping = config.getDbMapping();
    String cacheKey = config.getDestination() + "." + dbMapping.getDatabase() + "." + dbMapping.getTable();
    Map<String, Integer> columnType = columnsTypeCache.get(cacheKey);
    if (columnType == null) {
        synchronized (PhoenixSyncService.class) {
            columnType = columnsTypeCache.get(cacheKey);
            if (columnType == null) {
                columnType = new LinkedHashMap<>();
                final Map<String, Integer> columnTypeTmp = columnType;
                String sql = "SELECT * FROM " + SyncUtil.getDbTableName(dbMapping) + " WHERE 1=2";
                try {
                    Util.sqlRS(conn, sql, rs -> {
                        try {
                            ResultSetMetaData rsd = rs.getMetaData();
                            int columnCount = rsd.getColumnCount();
                            for (int i = 1; i <= columnCount; i++) {
                                columnTypeTmp.put(rsd.getColumnName(i).toLowerCase(), rsd.getColumnType(i));
                            }
                            columnsTypeCache.put(cacheKey, columnTypeTmp);
                        } catch (SQLException e) {
                            logger.error(e.getMessage(), e);
                        }
                    });
                } catch (RuntimeException e) {
                    // 新增catch 里面做了操作
                    if (!e.getCause().getClass().getName().endsWith("TableNotFoundException")) {
                        throw e;
                    }
                    if (!PhoenixEtlService.syncSchema(conn, config)) {
                        throw e;
                    }
                    Util.sqlRS(conn, sql, rs -> {
                        try {
                            ResultSetMetaData rsd = rs.getMetaData();
                            int columnCount = rsd.getColumnCount();
                            for (int i = 1; i <= columnCount; i++) {
                                columnTypeTmp.put(rsd.getColumnName(i).toLowerCase(), rsd.getColumnType(i));
                            }
                            columnsTypeCache.put(cacheKey, columnTypeTmp);
                        } catch (SQLException e1) {
                            logger.error(e1.getMessage(), e1);
                        }
                    });
                }
            }
        }
    }
    return columnType;
}

Example 4 with DbMapping

use of com.alibaba.otter.canal.client.adapter.phoenix.config.MappingConfig.DbMapping in project canal by alibaba.

the class PhoenixSyncService method delete.

/**
 * 删除操作 没有改动
 *
 * @param config MappingConfig
 * @param dml    Single DML
 */
private void delete(BatchExecutor batchExecutor, MappingConfig config, SingleDml dml) throws SQLException {
    Map<String, Object> data = dml.getData();
    if (data == null || data.isEmpty()) {
        return;
    }
    DbMapping dbMapping = config.getDbMapping();
    Map<String, Integer> ctype = getTargetColumnType(batchExecutor.getConn(), config);
    StringBuilder sql = new StringBuilder();
    sql.append("DELETE FROM ").append(SyncUtil.getDbTableName(dbMapping)).append(" WHERE ");
    List<Map<String, ?>> values = new ArrayList<>();
    // 拼接主键
    appendCondition(dbMapping, sql, ctype, values, data);
    try {
        batchExecutor.execute(sql.toString(), values);
        if (logger.isTraceEnabled()) {
            logger.trace("Delete from target table, sql: {}", sql);
        }
    } catch (SQLException e) {
        logger.warn("Delete from target error, sql: {} {}", sql, values);
        throw e;
    }
}

Example 5 with DbMapping

use of com.alibaba.otter.canal.client.adapter.phoenix.config.MappingConfig.DbMapping in project canal by alibaba.

the class PhoenixSyncService method insert.

/**
 * 插入操作
 *
 * @param config 配置项
 * @param dml    DML数据
 */
private void insert(BatchExecutor batchExecutor, MappingConfig config, SingleDml dml) throws SQLException {
    Map<String, Object> data = dml.getData();
    if (data == null || data.isEmpty()) {
        return;
    }
    DbMapping dbMapping = config.getDbMapping();
    Map<String, String> columnsMap = SyncUtil.getColumnsMap(dbMapping, data);
    StringBuilder insertSql = new StringBuilder();
    insertSql.append("UPSERT INTO ").append(SyncUtil.getDbTableName(dbMapping)).append(" (");
    Map<String, Integer> ctype = getTargetColumnType(batchExecutor.getConn(), config);
    int mapLen = columnsMap.size();
    List<Map<String, ?>> values = new ArrayList<>();
    for (Map.Entry<String, String> entry : columnsMap.entrySet()) {
        String targetColumnName = entry.getKey();
        String srcColumnName = entry.getValue();
        if (srcColumnName == null) {
            srcColumnName = Util.cleanColumn(targetColumnName);
        }
        Integer type = ctype.get(Util.cleanColumn(targetColumnName).toLowerCase());
        if (type == null) {
            if (dbMapping.isSkipMissing()) {
                logger.warn("Target missing field: {}", targetColumnName);
                mapLen -= 1;
                continue;
            } else if (dbMapping.getMapAll() && dbMapping.isAlter() && PhoenixEtlService.syncSchema(batchExecutor.getConn(), config)) {
                columnsTypeCache.remove(config.getDestination() + "." + dbMapping.getDatabase() + "." + dbMapping.getTable());
                ctype = getTargetColumnType(batchExecutor.getConn(), config);
                type = ctype.get(Util.cleanColumn(targetColumnName).toLowerCase());
            }
            if (type == null) {
                throw new RuntimeException("Target column: " + targetColumnName + " not matched");
            }
        }
        insertSql.append(dbMapping.escape(targetColumnName)).append(",");
        Object value = data.get(srcColumnName);
        BatchExecutor.setValue(values, type, value);
    }
    int len = insertSql.length();
    insertSql.delete(len - 1, len).append(") VALUES (");
    for (int i = 0; i < mapLen; i++) {
        insertSql.append("?,");
    }
    len = insertSql.length();
    insertSql.delete(len - 1, len).append(")");
    Map<String, Object> old = dml.getOld();
    try {
        if (old != null && !old.isEmpty()) {
            boolean keyChanged = false;
            List<Map<String, ?>> delValues = new ArrayList<>();
            StringBuilder deleteSql = new StringBuilder();
            deleteSql.append("DELETE FROM ").append(SyncUtil.getDbTableName(dbMapping)).append(" WHERE ");
            for (Map.Entry<String, String> entry : dbMapping.getTargetPk().entrySet()) {
                String targetColumnName = entry.getKey();
                String srcColumnName = entry.getValue();
                if (srcColumnName == null) {
                    srcColumnName = Util.cleanColumn(targetColumnName);
                }
                Integer type = ctype.get(Util.cleanColumn(targetColumnName).toLowerCase());
                if (type != null) {
                    deleteSql.append(dbMapping.escape(targetColumnName)).append("=? AND ");
                    // 如果有修改主键的情况
                    if (old.containsKey(srcColumnName)) {
                        keyChanged = true;
                        BatchExecutor.setValue(delValues, type, old.get(srcColumnName));
                    } else {
                        BatchExecutor.setValue(delValues, type, data.get(srcColumnName));
                    }
                }
            }
            if (keyChanged) {
                if (config.isDebug()) {
                    logger.info("insert into table: {} {}", deleteSql, delValues);
                }
                batchExecutor.execute(deleteSql.toString(), delValues);
            }
        }
        if (config.isDebug()) {
            logger.info("insert into table: {} {}", insertSql, values);
        }
        batchExecutor.execute(insertSql.toString(), values);
    } catch (SQLException | RuntimeException e) {
        logger.warn("Insert into target table, sql: {} {}", insertSql, values, e);
        throw e;
    }
    if (logger.isTraceEnabled()) {
        logger.trace("Insert into target table, sql: {}", insertSql);
    }
}