Search in sources :

Example 1 with MappingConfig

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

the class PhoenixAdapter method etl.

/**
 * ETL方法
 *
 * @param task   任务名, 对应配置名
 * @param params etl筛选条件
 * @return ETL结果
 */
@Override
public EtlResult etl(String task, List<String> params) {
    EtlResult etlResult = new EtlResult();
    MappingConfig config = phoenixMapping.get(task);
    if (config != null) {
        DataSource srcDataSource = DatasourceConfig.DATA_SOURCES.get(config.getDataSourceKey());
        if (srcDataSource != null) {
            return PhoenixEtlService.importData(srcDataSource, getPhoenixConnection(), config, params);
        } else {
            etlResult.setSucceeded(false);
            etlResult.setErrorMessage("DataSource not found");
            return etlResult;
        }
    } else {
        StringBuilder resultMsg = new StringBuilder();
        boolean resSucc = true;
        // ds不为空说明传入的是destination
        for (MappingConfig configTmp : phoenixMapping.values()) {
            // 取所有的destination为task的配置
            if (configTmp.getDestination().equals(task)) {
                DataSource srcDataSource = DatasourceConfig.DATA_SOURCES.get(configTmp.getDataSourceKey());
                if (srcDataSource == null) {
                    continue;
                }
                EtlResult etlRes = PhoenixEtlService.importData(srcDataSource, getPhoenixConnection(), configTmp, params);
                if (!etlRes.getSucceeded()) {
                    resSucc = false;
                    resultMsg.append(etlRes.getErrorMessage()).append("\n");
                } else {
                    resultMsg.append(etlRes.getResultMessage()).append("\n");
                }
            }
        }
        if (resultMsg.length() > 0) {
            etlResult.setSucceeded(resSucc);
            if (resSucc) {
                etlResult.setResultMessage(resultMsg.toString());
            } else {
                etlResult.setErrorMessage(resultMsg.toString());
            }
            return etlResult;
        }
    }
    etlResult.setSucceeded(false);
    etlResult.setErrorMessage("Task not found");
    return etlResult;
}
Also used : MappingConfig(com.alibaba.otter.canal.client.adapter.phoenix.config.MappingConfig) DataSource(javax.sql.DataSource)

Example 2 with MappingConfig

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

the class PhoenixAdapter method count.

/**
 * 获取总数方法
 *
 * @param task 任务名, 对应配置名
 * @return 总数
 */
@Override
public Map<String, Object> count(String task) {
    Map<String, Object> res = new LinkedHashMap<>();
    MappingConfig config = phoenixMapping.get(task);
    if (config == null) {
        logger.info("[{}]phoenix config mapping: {}", this, phoenixMapping.keySet());
        res.put("succeeded", false);
        res.put("errorMessage", "Task[" + task + "] not found");
        res.put("tasks", phoenixMapping.keySet());
        return res;
    }
    MappingConfig.DbMapping dbMapping = config.getDbMapping();
    String sql = "SELECT COUNT(1) AS cnt FROM " + SyncUtil.getDbTableName(dbMapping);
    Connection conn = null;
    try {
        // conn = dataSource.getConnection();
        conn = getPhoenixConnection();
        Util.sqlRS(conn, sql, rs -> {
            try {
                if (rs.next()) {
                    Long rowCount = rs.getLong("cnt");
                    res.put("count", rowCount);
                }
            } catch (SQLException e) {
                logger.error(e.getMessage(), e);
            }
        });
    } catch (Exception e) {
        logger.error(e.getMessage(), e);
    } finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                logger.error(e.getMessage(), e);
            }
        }
    }
    res.put("targetTable", SyncUtil.getDbTableName(dbMapping));
    return res;
}
Also used : MappingConfig(com.alibaba.otter.canal.client.adapter.phoenix.config.MappingConfig) SQLException(java.sql.SQLException) Connection(java.sql.Connection) SQLException(java.sql.SQLException) LinkedHashMap(java.util.LinkedHashMap)

Example 3 with MappingConfig

use of com.alibaba.otter.canal.client.adapter.phoenix.config.MappingConfig 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;
}
Also used : ResultSetMetaData(java.sql.ResultSetMetaData) BatchExecutor(com.alibaba.otter.canal.client.adapter.phoenix.support.BatchExecutor) StringUtils(org.apache.commons.lang.StringUtils) java.util(java.util) Connection(java.sql.Connection) SerializerFeature(com.alibaba.fastjson.serializer.SerializerFeature) Util(com.alibaba.otter.canal.client.adapter.support.Util) Dml(com.alibaba.otter.canal.client.adapter.support.Dml) LoggerFactory(org.slf4j.LoggerFactory) SQLName(com.alibaba.druid.sql.ast.SQLName) DbMapping(com.alibaba.otter.canal.client.adapter.phoenix.config.MappingConfig.DbMapping) ParserException(com.alibaba.druid.sql.parser.ParserException) Function(java.util.function.Function) SQLException(java.sql.SQLException) SQLUtils(com.alibaba.druid.sql.SQLUtils) TypeUtil(com.alibaba.otter.canal.client.adapter.phoenix.support.TypeUtil) Logger(org.slf4j.Logger) java.util.concurrent(java.util.concurrent) ConfigurationManager(com.alibaba.otter.canal.client.adapter.phoenix.config.ConfigurationManager) MappingConfig(com.alibaba.otter.canal.client.adapter.phoenix.config.MappingConfig) SyncUtil(com.alibaba.otter.canal.client.adapter.phoenix.support.SyncUtil) JSON(com.alibaba.fastjson.JSON) SingleDml(com.alibaba.otter.canal.client.adapter.phoenix.support.SingleDml) JdbcConstants(com.alibaba.druid.util.JdbcConstants) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement) ResultSetMetaData(java.sql.ResultSetMetaData) com.alibaba.druid.sql.ast.statement(com.alibaba.druid.sql.ast.statement) DbMapping(com.alibaba.otter.canal.client.adapter.phoenix.config.MappingConfig.DbMapping) SQLException(java.sql.SQLException)

Example 4 with MappingConfig

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

the class PhoenixSyncService method sync.

/**
 * 批量同步 :将批量DML进行解析并放入List<SingleDml> --> dmlsPartition[hash].add(syncItem);
 * @param mappingConfig 配置集合
 * @param dmls   批量 DML
 */
public void sync(Map<String, Map<String, MappingConfig>> mappingConfig, List<Dml> dmls, Properties envProperties) {
    sync(dmls, dml -> {
        String destination = StringUtils.trimToEmpty(dml.getDestination());
        String groupId = StringUtils.trimToEmpty(dml.getGroupId());
        String database = dml.getDatabase();
        String table = dml.getTable().toLowerCase();
        Map<String, MappingConfig> configMap;
        if (envProperties != null && !"tcp".equalsIgnoreCase(envProperties.getProperty("canal.conf.mode"))) {
            // tcp 模式
            configMap = mappingConfig.get(destination + "-" + groupId + "_" + database + "-" + table);
        } else {
            // kafka 模式 或者 RocketMQ模式
            configMap = mappingConfig.get(destination + "_" + database + "-" + table);
        }
        if (configMap == null) {
            if (logger.isTraceEnabled()) {
                logger.trace("no config map: destination={},groupId={}, database={}, table={}, keys={}", destination, groupId, database, table, mappingConfig.keySet());
            }
            return false;
        }
        if (configMap.values().isEmpty()) {
            logger.info("config map empty: destination={},groupId={}, database={}, table={}, keys={}", destination, groupId, database, table, mappingConfig.keySet());
            return false;
        }
        if (dml.getIsDdl() != null && dml.getIsDdl() && StringUtils.isNotEmpty(dml.getSql())) {
            // DDL
            columnsTypeCache.remove(dml.getDestination() + "." + dml.getDatabase() + "." + dml.getTable());
            List<SQLStatement> stmtList;
            try {
                stmtList = SQLUtils.parseStatements(dml.getSql(), JdbcConstants.MYSQL, false);
            } catch (ParserException e) {
                // 可能存在一些SQL是不支持的,比如存储过程
                logger.info("parse sql error: " + dml.getSql(), e);
                return false;
            }
            for (Map.Entry<String, MappingConfig> entry : configMap.entrySet()) {
                try {
                    alter(batchExecutors[0], entry.getValue(), dml, stmtList, entry.getKey());
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            return false;
        } else {
            // DML
            for (Map.Entry<String, MappingConfig> entry : configMap.entrySet()) {
                MappingConfig config = entry.getValue();
                if (config.isDebug()) {
                    logger.info("DML: {} {}", entry.getKey(), JSON.toJSONString(dml, SerializerFeature.WriteMapNullValue));
                }
                if (config.getConcurrent()) {
                    // 并行同步
                    // 将一批DML转成SingleDml
                    List<SingleDml> singleDmls = SingleDml.dml2SingleDmls(dml);
                    singleDmls.forEach(singleDml -> {
                        // 取主键hash
                        int hash = pkHash(config.getDbMapping(), singleDml.getData());
                        SyncItem syncItem = new SyncItem(config, singleDml);
                        // 相同的主键数据的顺序是可以保证的
                        dmlsPartition[hash].add(syncItem);
                    });
                } else {
                    // 不并行同步
                    int hash = 0;
                    List<SingleDml> singleDmls = SingleDml.dml2SingleDmls(dml);
                    singleDmls.forEach(singleDml -> {
                        SyncItem syncItem = new SyncItem(config, singleDml);
                        // 这里线程默认是3个,如果不并行,则会出现2个线程空跑
                        dmlsPartition[hash].add(syncItem);
                    });
                }
            }
            return true;
        }
    });
}
Also used : ParserException(com.alibaba.druid.sql.parser.ParserException) SQLException(java.sql.SQLException) SingleDml(com.alibaba.otter.canal.client.adapter.phoenix.support.SingleDml) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement) MappingConfig(com.alibaba.otter.canal.client.adapter.phoenix.config.MappingConfig)

Example 5 with MappingConfig

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

the class PhoenixEtlService method importData.

/**
 * 导入数据
 */
public static EtlResult importData(DataSource srcDS, Connection targetDSConnection, MappingConfig config, List<String> params) {
    EtlResult etlResult = new EtlResult();
    AtomicLong successCount = new AtomicLong();
    List<String> errMsg = new ArrayList<>();
    String hbaseTable = "";
    try {
        if (config == null) {
            logger.error("Config is null!");
            etlResult.setSucceeded(false);
            etlResult.setErrorMessage("Config is null!");
            return etlResult;
        }
        boolean debug = params != null && params.get(0).equals("_debug");
        if (debug) {
            params = params.subList(1, params.size());
        }
        syncSchema(srcDS, targetDSConnection, config);
        DbMapping dbMapping = config.getDbMapping();
        long start = System.currentTimeMillis();
        // 拼接sql
        StringBuilder sql = new StringBuilder("SELECT * FROM " + dbMapping.getDatabase() + "." + dbMapping.getTable());
        // 拼接条件
        appendCondition(params, dbMapping, srcDS, sql);
        // 获取总数
        String countSql = "SELECT COUNT(1) FROM ( " + sql + ") _CNT ";
        long cnt = (Long) Util.sqlRS(srcDS, countSql, rs -> {
            Long count = null;
            try {
                if (rs.next()) {
                    count = ((Number) rs.getObject(1)).longValue();
                }
            } catch (Exception e) {
                logger.error(e.getMessage(), e);
            }
            return count == null ? 0 : count;
        });
        // 当大于1万条记录时开启多线程
        if (cnt >= 10000) {
            int threadCount = 3;
            long perThreadCnt = cnt / threadCount;
            ExecutorService executor = Util.newFixedThreadPool(threadCount, 5000L);
            for (int i = 0; i < threadCount; i++) {
                long offset = i * perThreadCnt;
                Long size = null;
                if (i != threadCount - 1) {
                    size = perThreadCnt;
                }
                String sqlFinal;
                if (size != null) {
                    sqlFinal = sql + " LIMIT " + offset + "," + size;
                } else {
                    sqlFinal = sql + " LIMIT " + offset + "," + cnt;
                }
                executor.execute(() -> executeSqlImport(srcDS, targetDSConnection, sqlFinal, dbMapping, successCount, errMsg, debug));
            }
            executor.shutdown();
            // noinspection StatementWithEmptyBody
            while (!executor.awaitTermination(3, TimeUnit.SECONDS)) ;
        } else {
            executeSqlImport(srcDS, targetDSConnection, sql.toString(), dbMapping, successCount, errMsg, debug);
        }
        logger.info(dbMapping.getTable() + " etl completed in: " + (System.currentTimeMillis() - start) / 1000 + "s!");
        etlResult.setResultMessage("导入目标表 " + SyncUtil.getDbTableName(dbMapping) + " 数据:" + successCount.get() + " 条");
    } catch (Exception e) {
        logger.error(e.getMessage(), e);
        errMsg.add(hbaseTable + " etl failed! ==>" + e.getMessage());
    }
    if (errMsg.isEmpty()) {
        etlResult.setSucceeded(true);
    } else {
        etlResult.setErrorMessage(Joiner.on("\n").join(errMsg));
    }
    return etlResult;
}
Also used : DatasourceConfig(com.alibaba.otter.canal.client.adapter.support.DatasourceConfig) java.sql(java.sql) java.util(java.util) TypeUtil(com.alibaba.otter.canal.client.adapter.phoenix.support.TypeUtil) Logger(org.slf4j.Logger) Util(com.alibaba.otter.canal.client.adapter.support.Util) LoggerFactory(org.slf4j.LoggerFactory) DbMapping(com.alibaba.otter.canal.client.adapter.phoenix.config.MappingConfig.DbMapping) PhoenixSupportUtil(com.alibaba.otter.canal.client.adapter.phoenix.support.PhoenixSupportUtil) AtomicBoolean(java.util.concurrent.atomic.AtomicBoolean) EtlResult(com.alibaba.otter.canal.client.adapter.support.EtlResult) MappingConfig(com.alibaba.otter.canal.client.adapter.phoenix.config.MappingConfig) SyncUtil(com.alibaba.otter.canal.client.adapter.phoenix.support.SyncUtil) TimeUnit(java.util.concurrent.TimeUnit) AtomicLong(java.util.concurrent.atomic.AtomicLong) DataSource(javax.sql.DataSource) PhoenixAdapter(com.alibaba.otter.canal.client.adapter.phoenix.PhoenixAdapter) ExecutorService(java.util.concurrent.ExecutorService) Joiner(com.google.common.base.Joiner) AtomicLong(java.util.concurrent.atomic.AtomicLong) DbMapping(com.alibaba.otter.canal.client.adapter.phoenix.config.MappingConfig.DbMapping) EtlResult(com.alibaba.otter.canal.client.adapter.support.EtlResult) AtomicLong(java.util.concurrent.atomic.AtomicLong) ExecutorService(java.util.concurrent.ExecutorService)

Aggregations

MappingConfig (com.alibaba.otter.canal.client.adapter.phoenix.config.MappingConfig)6 SQLException (java.sql.SQLException)3 SQLStatement (com.alibaba.druid.sql.ast.SQLStatement)2 ParserException (com.alibaba.druid.sql.parser.ParserException)2 DbMapping (com.alibaba.otter.canal.client.adapter.phoenix.config.MappingConfig.DbMapping)2 SingleDml (com.alibaba.otter.canal.client.adapter.phoenix.support.SingleDml)2 SyncUtil (com.alibaba.otter.canal.client.adapter.phoenix.support.SyncUtil)2 TypeUtil (com.alibaba.otter.canal.client.adapter.phoenix.support.TypeUtil)2 Util (com.alibaba.otter.canal.client.adapter.support.Util)2 Connection (java.sql.Connection)2 java.util (java.util)2 LinkedHashMap (java.util.LinkedHashMap)2 DataSource (javax.sql.DataSource)2 Logger (org.slf4j.Logger)2 LoggerFactory (org.slf4j.LoggerFactory)2 SQLUtils (com.alibaba.druid.sql.SQLUtils)1 SQLName (com.alibaba.druid.sql.ast.SQLName)1 com.alibaba.druid.sql.ast.statement (com.alibaba.druid.sql.ast.statement)1 JdbcConstants (com.alibaba.druid.util.JdbcConstants)1 JSON (com.alibaba.fastjson.JSON)1