Examples with DbDialect com.alibaba.otter.node.etl.common.db.dialect.DbDialect used on opensource projects

Example 1 with DbDialect

use of com.alibaba.otter.node.etl.common.db.dialect.DbDialect in project otter by alibaba.

the class RowDataTransformer method transform.

public EventData transform(EventData data, OtterTransformerContext context) {
    EventData result = new EventData();
    // 处理Table转化
    DataMedia dataMedia = context.getDataMediaPair().getTarget();
    result.setPairId(context.getDataMediaPair().getId());
    result.setTableId(dataMedia.getId());
    // 需要特殊处理下multi场景
    buildName(data, result, context.getDataMediaPair());
    result.setEventType(data.getEventType());
    result.setExecuteTime(data.getExecuteTime());
    result.setSyncConsistency(data.getSyncConsistency());
    result.setRemedy(data.isRemedy());
    result.setSyncMode(data.getSyncMode());
    result.setSize(data.getSize());
    result.setHint(data.getHint());
    result.setWithoutSchema(data.isWithoutSchema());
    if (data.getEventType().isDdl()) {
        // ddl不需要处理字段
        if (StringUtils.equalsIgnoreCase(result.getSchemaName(), data.getSchemaName()) && StringUtils.equalsIgnoreCase(result.getTableName(), data.getTableName())) {
            // 是否需要对ddl sql进行转化，暂时不支持异构，必须保证源表和目标表的名字相同
            result.setDdlSchemaName(data.getDdlSchemaName());
            result.setSql(data.getSql());
            return result;
        } else {
            throw new TransformException("no support ddl for [" + data.getSchemaName() + "." + data.getTableName() + "] to [" + result.getSchemaName() + "." + result.getTableName() + "] , sql :" + data.getSql());
        }
    }
    Multimap<String, String> translateColumnNames = HashMultimap.create();
    if (context.getDataMediaPair().getColumnPairMode().isInclude()) {
        // 只针对正向匹配进行名字映射，exclude不做处理
        List<ColumnPair> columnPairs = context.getDataMediaPair().getColumnPairs();
        for (ColumnPair columnPair : columnPairs) {
            translateColumnNames.put(columnPair.getSourceColumn().getName(), columnPair.getTargetColumn().getName());
        }
    }
    // 准备一下table meta
    DataMediaPair dataMediaPair = context.getDataMediaPair();
    boolean useTableTransform = context.getPipeline().getParameters().getUseTableTransform();
    boolean enableCompatibleMissColumn = context.getPipeline().getParameters().getEnableCompatibleMissColumn();
    TableInfoHolder tableHolder = null;
    if (useTableTransform || enableCompatibleMissColumn) {
        // 控制一下是否需要反查table
        // meta信息，如果同构数据库，完全没必要反查
        // 获取目标库的表信息
        DbDialect dbDialect = dbDialectFactory.getDbDialect(dataMediaPair.getPipelineId(), (DbMediaSource) dataMedia.getSource());
        Table table = dbDialect.findTable(result.getSchemaName(), result.getTableName());
        tableHolder = new TableInfoHolder(table, useTableTransform, enableCompatibleMissColumn);
    }
    // 处理column转化
    List<EventColumn> otherColumns = translateColumns(result, data.getColumns(), context.getDataMediaPair(), translateColumnNames, tableHolder);
    translatePkColumn(result, data.getKeys(), data.getOldKeys(), otherColumns, context.getDataMediaPair(), translateColumnNames, tableHolder);
    result.setColumns(otherColumns);
    return result;
}

Example 2 with DbDialect

use of com.alibaba.otter.node.etl.common.db.dialect.DbDialect in project otter by alibaba.

the class DbDialectTableTest method testOracleTable.

@Test
public void testOracleTable() {
    DbDataMedia oracleMedia = getOracleMedia();
    DbDialect dbDialect = dbDialectFactory.getDbDialect(1L, oracleMedia.getSource());
    Table table = dbDialect.findTable(oracleMedia.getNamespace(), oracleMedia.getName());
    want.object(table).notNull();
    System.out.println("tableName = " + table.getName());
    Column[] columns = table.getColumns();
    for (Column column : columns) {
        System.out.println("columnName = " + column.getName() + ",columnType = " + column.getTypeCode() + ",isPrimary = " + column.isPrimaryKey() + ",nullable = " + column.isRequired());
    }
}

Example 3 with DbDialect

use of com.alibaba.otter.node.etl.common.db.dialect.DbDialect in project otter by alibaba.

the class DbDialectTest method test_mysql.

@Test(expectedExceptions = RuntimeException.class)
public void test_mysql() {
    DbDataMedia media = getMysqlMedia();
    final DbDialect dbDialect = dbDialectFactory.getDbDialect(2L, media.getSource());
    want.object(dbDialect).clazIs(MysqlDialect.class);
    final SqlTemplate sqlTemplate = dbDialect.getSqlTemplate();
    final JdbcTemplate jdbcTemplate = dbDialect.getJdbcTemplate();
    final TransactionTemplate transactionTemplate = dbDialect.getTransactionTemplate();
    final int[] pkColumnTypes = { Types.INTEGER, Types.VARCHAR };
    final int[] columnTypes = { Types.CHAR, Types.DECIMAL, Types.BLOB, Types.CLOB, Types.DATE, Types.TIMESTAMP, Types.TIMESTAMP };
    transactionTemplate.execute(new TransactionCallback() {

        public Object doInTransaction(TransactionStatus status) {
            int affect = 0;
            String sql = null;
            // 执行insert
            sql = sqlTemplate.getInsertSql(MYSQL_SCHEMA_NAME, TABLE_NAME, pkColumns, columns);
            System.out.println(sql);
            affect = (Integer) jdbcTemplate.execute(sql, new PreparedStatementCallback() {

                public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                    doPreparedStatement(ps, dbDialect, toTypes(columnTypes, pkColumnTypes), toValues(columnValues, pkColumnValues));
                    return ps.executeUpdate();
                }
            });
            want.number(affect).isEqualTo(1);
            // 执行update
            sql = sqlTemplate.getUpdateSql(MYSQL_SCHEMA_NAME, TABLE_NAME, pkColumns, columns);
            System.out.println(sql);
            affect = (Integer) jdbcTemplate.execute(sql, new PreparedStatementCallback() {

                public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                    doPreparedStatement(ps, dbDialect, toTypes(columnTypes, pkColumnTypes), toValues(columnValues, pkColumnValues));
                    return ps.executeUpdate();
                }
            });
            want.number(affect).isEqualTo(1);
            // 执行deleate
            sql = sqlTemplate.getDeleteSql(MYSQL_SCHEMA_NAME, TABLE_NAME, pkColumns);
            System.out.println(sql);
            affect = (Integer) jdbcTemplate.execute(sql, new PreparedStatementCallback() {

                public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                    doPreparedStatement(ps, dbDialect, toTypes(pkColumnTypes), toValues(pkColumnValues));
                    return ps.executeUpdate();
                }
            });
            want.number(affect).isEqualTo(1);
            // 执行merge
            sql = sqlTemplate.getMergeSql(MYSQL_SCHEMA_NAME, TABLE_NAME, pkColumns, columns, null, true);
            System.out.println(sql);
            affect = (Integer) jdbcTemplate.execute(sql, new PreparedStatementCallback() {

                public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                    doPreparedStatement(ps, dbDialect, toTypes(columnTypes, pkColumnTypes), toValues(columnValues, pkColumnValues));
                    return ps.executeUpdate();
                }
            });
            want.number(affect).isEqualTo(1);
            throw new RuntimeException("rollback");
        }
    });
}

Example 4 with DbDialect

use of com.alibaba.otter.node.etl.common.db.dialect.DbDialect in project otter by alibaba.

the class DbLoadAction method doDdl.

/**
     * 执行ddl的调用，处理逻辑比较简单: 串行调用
     * 
     * @param context
     * @param eventDatas
     */
private void doDdl(DbLoadContext context, List<EventData> eventDatas) {
    for (final EventData data : eventDatas) {
        DataMedia dataMedia = ConfigHelper.findDataMedia(context.getPipeline(), data.getTableId());
        final DbDialect dbDialect = dbDialectFactory.getDbDialect(context.getIdentity().getPipelineId(), (DbMediaSource) dataMedia.getSource());
        Boolean skipDdlException = context.getPipeline().getParameters().getSkipDdlException();
        try {
            Boolean result = dbDialect.getJdbcTemplate().execute(new StatementCallback<Boolean>() {

                public Boolean doInStatement(Statement stmt) throws SQLException, DataAccessException {
                    Boolean result = false;
                    if (dbDialect instanceof MysqlDialect && StringUtils.isNotEmpty(data.getDdlSchemaName())) {
                        // 如果mysql，执行ddl时，切换到在源库执行的schema上
                        // result &= stmt.execute("use " + data.getDdlSchemaName());
                        // 解决当数据库名称为关键字如"Order"的时候,会报错,无法同步
                        result &= stmt.execute("use `" + data.getDdlSchemaName() + "`");
                    }
                    result &= stmt.execute(data.getSql());
                    return result;
                }
            });
            if (result) {
                // 记录为成功处理的sql
                context.getProcessedDatas().add(data);
            } else {
                context.getFailedDatas().add(data);
            }
        } catch (Throwable e) {
            if (skipDdlException) {
                // do skip
                logger.warn("skip exception for ddl : {} , caused by {}", data, ExceptionUtils.getFullStackTrace(e));
            } else {
                throw new LoadException(e);
            }
        }
    }
}

Example 5 with DbDialect

use of com.alibaba.otter.node.etl.common.db.dialect.DbDialect in project otter by alibaba.

the class SqlBuilderLoadInterceptor method before.

public boolean before(DbLoadContext context, EventData currentData) {
    // 初步构建sql
    DbDialect dbDialect = dbDialectFactory.getDbDialect(context.getIdentity().getPipelineId(), (DbMediaSource) context.getDataMediaSource());
    SqlTemplate sqlTemplate = dbDialect.getSqlTemplate();
    EventType type = currentData.getEventType();
    String sql = null;
    String schemaName = (currentData.isWithoutSchema() ? null : currentData.getSchemaName());
    // 注意insert/update语句对应的字段数序都是将主键排在后面
    if (type.isInsert()) {
        if (CollectionUtils.isEmpty(currentData.getColumns()) && sqlTemplate instanceof OracleSqlTemplate) {
            // 如果表为全主键，直接进行insert
            // sql
            sql = sqlTemplate.getInsertSql(schemaName, currentData.getTableName(), buildColumnNames(currentData.getKeys()), buildColumnNames(currentData.getColumns()));
        } else {
            sql = sqlTemplate.getMergeSql(schemaName, currentData.getTableName(), buildColumnNames(currentData.getKeys()), buildColumnNames(currentData.getColumns()), new String[] {}, !dbDialect.isDRDS());
        }
    } else if (type.isUpdate()) {
        // String[] keyColumns = buildColumnNames(currentData.getKeys());
        // String[] otherColumns =
        // buildColumnNames(currentData.getUpdatedColumns());
        // boolean existOldKeys = false;
        // for (String key : keyColumns) {
        // // 找一下otherColumns是否有主键，存在就代表有主键变更
        // if (ArrayUtils.contains(otherColumns, key)) {
        // existOldKeys = true;
        // break;
        // }
        // }
        boolean existOldKeys = !CollectionUtils.isEmpty(currentData.getOldKeys());
        boolean rowMode = context.getPipeline().getParameters().getSyncMode().isRow();
        String[] keyColumns = null;
        String[] otherColumns = null;
        if (existOldKeys) {
            // 需要考虑主键变更的场景
            // 构造sql如下：update table xxx set pk = newPK where pk = oldPk
            keyColumns = buildColumnNames(currentData.getOldKeys());
            otherColumns = buildColumnNames(currentData.getUpdatedColumns(), currentData.getKeys());
        } else {
            keyColumns = buildColumnNames(currentData.getKeys());
            otherColumns = buildColumnNames(currentData.getUpdatedColumns());
        }
        if (rowMode && !existOldKeys) {
            // 如果是行记录,并且不存在主键变更，考虑merge sql
            sql = sqlTemplate.getMergeSql(schemaName, currentData.getTableName(), keyColumns, otherColumns, new String[] {}, !dbDialect.isDRDS());
        } else {
            // 否则进行update sql
            sql = sqlTemplate.getUpdateSql(schemaName, currentData.getTableName(), keyColumns, otherColumns);
        }
    } else if (type.isDelete()) {
        sql = sqlTemplate.getDeleteSql(schemaName, currentData.getTableName(), buildColumnNames(currentData.getKeys()));
    }
    // 处理下hint sql
    if (currentData.getHint() != null) {
        currentData.setSql(currentData.getHint() + sql);
    } else {
        currentData.setSql(sql);
    }
    return false;
}