概要
说明：修改DataX源码支持Oracle的[UPSET]
技术细节
- 下载DataX源码
- 因为这里只是改了Oracle的部分,所以注释掉了其余组件(datax-all -> pom.xml),各位可以视需求来定
- 修改如下文件
1、Oraclewriter.java
package com.alibaba.datax.plugin.writer.oraclewriter;
import com.alibaba.datax.common.exception.DataXException;
import com.alibaba.datax.common.plugin.RecordReceiver;
import com.alibaba.datax.common.spi.Writer;
import com.alibaba.datax.common.util.Configuration;
import com.alibaba.datax.plugin.rdbms.util.DBUtilErrorCode;
import com.alibaba.datax.plugin.rdbms.util.DataBaseType;
import com.alibaba.datax.plugin.rdbms.writer.CommonRdbmsWriter;
import com.alibaba.datax.plugin.rdbms.writer.Key;
import java.util.List;
public class OracleWriter extends Writer {
private static final DataBaseType DATABASE_TYPE = DataBaseType.Oracle;
public static class Job extends Writer.Job {
private Configuration originalConfig = null;
private CommonRdbmsWriter.Job commonRdbmsWriterJob;
public void preCheck() {
this.init();
this.commonRdbmsWriterJob.writerPreCheck(this.originalConfig, DATABASE_TYPE);
}
@Override
public void init() {
this.originalConfig = super.getPluginJobConf();
// warn：not like mysql, oracle only support insert mode, don't use
/*
String writeMode = this.originalConfig.getString(Key.WRITE_MODE);
if (null != writeMode) {
throw DataXException
.asDataXException(
DBUtilErrorCode.CONF_ERROR,
String.format(
"写入模式(writeMode)配置错误. 因为Oracle不支持配置项 writeMode: %s, Oracle只能使用insert sql 插入数据. 请检查您的配置并作出修改",
writeMode));
}
*/
this.commonRdbmsWriterJob = new CommonRdbmsWriter.Job(
DATABASE_TYPE);
this.commonRdbmsWriterJob.init(this.originalConfig);
}
@Override
public void prepare() {
//oracle实跑先不做权限检查
//this.commonRdbmsWriterJob.privilegeValid(this.originalConfig, DATABASE_TYPE);
this.commonRdbmsWriterJob.prepare(this.originalConfig);
}
@Override
public List<Configuration> split(int mandatoryNumber) {
return this.commonRdbmsWriterJob.split(this.originalConfig,
mandatoryNumber);
}
@Override
public void post() {
this.commonRdbmsWriterJob.post(this.originalConfig);
}
@Override
public void destroy() {
this.commonRdbmsWriterJob.destroy(this.originalConfig);
}
}
public static class Task extends Writer.Task {
private Configuration writerSliceConfig;
private CommonRdbmsWriter.Task commonRdbmsWriterTask;
@Override
public void init() {
this.writerSliceConfig = super.getPluginJobConf();
this.commonRdbmsWriterTask = new CommonRdbmsWriter.Task(DATABASE_TYPE);
this.commonRdbmsWriterTask.init(this.writerSliceConfig);
}
@Override
public void prepare() {
this.commonRdbmsWriterTask.prepare(this.writerSliceConfig);
}
public void startWrite(RecordReceiver recordReceiver) {
this.commonRdbmsWriterTask.startWrite(recordReceiver,
this.writerSliceConfig, super.getTaskPluginCollector());
}
@Override
public void post() {
this.commonRdbmsWriterTask.post(this.writerSliceConfig);
}
@Override
public void destroy() {
this.commonRdbmsWriterTask.destroy(this.writerSliceConfig);
}
}
}
2、WriterUtil.java
package com.alibaba.datax.plugin.rdbms.writer.util;
import com.alibaba.datax.common.exception.DataXException;
import com.alibaba.datax.common.util.Configuration;
import com.alibaba.datax.plugin.rdbms.util.DBUtil;
import com.alibaba.datax.plugin.rdbms.util.DBUtilErrorCode;
import com.alibaba.datax.plugin.rdbms.util.DataBaseType;
import com.alibaba.datax.plugin.rdbms.util.RdbmsException;
import com.alibaba.datax.plugin.rdbms.writer.Constant;
import com.alibaba.datax.plugin.rdbms.writer.Key;
import com.alibaba.druid.sql.parser.ParserException;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.Connection;
import java.sql.Statement;
import java.util.*;
public final class WriterUtil {
private static final Logger LOG = LoggerFactory.getLogger(WriterUtil.class);
//TODO 切分报错
public static List<Configuration> doSplit(Configuration simplifiedConf,
int adviceNumber) {
List<Configuration> splitResultConfigs = new ArrayList<Configuration>();
int tableNumber = simplifiedConf.getInt(Constant.TABLE_NUMBER_MARK);
//处理单表的情况
if (tableNumber == 1) {
//由于在之前的 master prepare 中已经把 table,jdbcUrl 提取出来，所以这里处理十分简单
for (int j = 0; j < adviceNumber; j++) {
splitResultConfigs.add(simplifiedConf.clone());
}
return splitResultConfigs;
}
if (tableNumber != adviceNumber) {
throw DataXException.asDataXException(DBUtilErrorCode.CONF_ERROR,
String.format("您的配置文件中的列配置信息有误. 您要写入的目的端的表个数是:%s , 但是根据系统建议需要切分的份数是：%s. 请检查您的配置并作出修改.",
tableNumber, adviceNumber));
}
String jdbcUrl;
List<String> preSqls = simplifiedConf.getList(Key.PRE_SQL, String.class);
List<String> postSqls = simplifiedConf.getList(Key.POST_SQL, String.class);
List<Object> conns = simplifiedConf.getList(Constant.CONN_MARK,
Object.class);
for (Object conn : conns) {
Configuration sliceConfig = simplifiedConf.clone();
Configuration connConf = Configuration.from(conn.toString());
jdbcUrl = connConf.getString(Key.JDBC_URL);
sliceConfig.set(Key.JDBC_URL, jdbcUrl);
sliceConfig.remove(Constant.CONN_MARK);
List<String> tables = connConf.getList(Key.TABLE, String.class);
for (String table : tables) {
Configuration tempSlice = sliceConfig.clone();
tempSlice.set(Key.TABLE, table);
tempSlice.set(Key.PRE_SQL, renderPreOrPostSqls(preSqls, table));
tempSlice.set(Key.POST_SQL, renderPreOrPostSqls(postSqls, table));
splitResultConfigs.add(tempSlice);
}
}
return splitResultConfigs;
}
public static List<String> renderPreOrPostSqls(List<String> preOrPostSqls, String tableName) {
if (null == preOrPostSqls) {
return Collections.emptyList();
}
List<String> renderedSqls = new ArrayList<String>();
for (String sql : preOrPostSqls) {
//preSql为空时，不加入执行队列
if (StringUtils.isNotBlank(sql)) {
renderedSqls.add(sql.replace(Constant.TABLE_NAME_PLACEHOLDER, tableName));
}
}
return renderedSqls;
}
public static void executeSqls(Connection conn, List<String> sqls, String basicMessage,DataBaseType dataBaseType) {
Statement stmt = null;
String currentSql = null;
try {
stmt = conn.createStatement();
for (String sql : sqls) {
currentSql = sql;
DBUtil.executeSqlWithoutResultSet(stmt, sql);
}
} catch (Exception e) {
throw RdbmsException.asQueryException(dataBaseType,e,currentSql,null,null);
} finally {
DBUtil.closeDBResources(null, stmt, null);
}
}
public static String getWriteTemplate(List<String> columnHolders, List<String> valueHolders, String writeMode, DataBaseType dataBaseType, boolean forceUseUpdate) {
boolean update = writeMode.trim().toLowerCase().startsWith("update");
boolean isWriteModeLegal = writeMode.trim().toLowerCase().startsWith("insert")
|| writeMode.trim().toLowerCase().startsWith("replace")
|| update;
if (!isWriteModeLegal) {
throw DataXException.asDataXException(DBUtilErrorCode.ILLEGAL_VALUE,
String.format("您所配置的 writeMode:%s 错误. 因为DataX 目前仅支持replace,update 或 insert 方式. 请检查您的配置并作出修改.", writeMode));
}
// && writeMode.trim().toLowerCase().startsWith("replace")
String writeDataSqlTemplate;
if (forceUseUpdate || update) {
//update只在mysql下使用
if (dataBaseType == DataBaseType.MySql || dataBaseType == DataBaseType.Tddl) {
writeDataSqlTemplate = new StringBuilder()
.append("INSERT INTO %s (").append(StringUtils.join(columnHolders, ","))
.append(") VALUES(").append(StringUtils.join(valueHolders, ","))
.append(")")
.append(onDuplicateKeyUpdateString(columnHolders))
.toString();
}
//update在Oracle下使用
else if (dataBaseType == DataBaseType.Oracle) {
writeDataSqlTemplate = onMergeIntoDoString(writeMode, columnHolders, valueHolders) + "INSERT (" +
StringUtils.join(columnHolders, ",") +
") VALUES(" + StringUtils.join(valueHolders, ",") +")";
}else {
throw DataXException.asDataXException(DBUtilErrorCode.ILLEGAL_VALUE,
String.format("当前数据库不支持 writeMode:%s 模式.", writeMode));
}
} else {
//这里是保护,如果其他错误的使用了update,需要更换为replace
if (update) {
writeMode = "replace";
}
writeDataSqlTemplate = new StringBuilder().append(writeMode)
.append(" INTO %s (").append(StringUtils.join(columnHolders, ","))
.append(") VALUES(").append(StringUtils.join(valueHolders, ","))
.append(")").toString();
}
return writeDataSqlTemplate;
}
public static String onDuplicateKeyUpdateString(List<String> columnHolders){
if (columnHolders == null || columnHolders.size() < 1) {
return "";
}
StringBuilder sb = new StringBuilder();
sb.append(" ON DUPLICATE KEY UPDATE ");
boolean first = true;
for(String column:columnHolders){
if(!first){
sb.append(",");
}else{
first = false;
}
sb.append(column);
sb.append("=VALUES(");
sb.append(column);
sb.append(")");
}
return sb.toString();
}
public static void preCheckPrePareSQL(Configuration originalConfig, DataBaseType type) {
List<Object> conns = originalConfig.getList(Constant.CONN_MARK, Object.class);
Configuration connConf = Configuration.from(conns.get(0).toString());
String table = connConf.getList(Key.TABLE, String.class).get(0);
List<String> preSqls = originalConfig.getList(Key.PRE_SQL,
String.class);
List<String> renderedPreSqls = WriterUtil.renderPreOrPostSqls(
preSqls, table);
if (null != renderedPreSqls && !renderedPreSqls.isEmpty()) {
LOG.info("Begin to preCheck preSqls:[{}].",
StringUtils.join(renderedPreSqls, ";"));
for(String sql : renderedPreSqls) {
try{
DBUtil.sqlValid(sql, type);
}catch(ParserException e) {
throw RdbmsException.asPreSQLParserException(type,e,sql);
}
}
}
}
public static void preCheckPostSQL(Configuration originalConfig, DataBaseType type) {
List<Object> conns = originalConfig.getList(Constant.CONN_MARK, Object.class);
Configuration connConf = Configuration.from(conns.get(0).toString());
String table = connConf.getList(Key.TABLE, String.class).get(0);
List<String> postSqls = originalConfig.getList(Key.POST_SQL,
String.class);
List<String> renderedPostSqls = WriterUtil.renderPreOrPostSqls(
postSqls, table);
if (null != renderedPostSqls && !renderedPostSqls.isEmpty()) {
LOG.info("Begin to preCheck postSqls:[{}].",
StringUtils.join(renderedPostSqls, ";"));
for(String sql : renderedPostSqls) {
try{
DBUtil.sqlValid(sql, type);
}catch(ParserException e){
throw RdbmsException.asPostSQLParserException(type,e,sql);
}
}
}
}
public static String onMergeIntoDoString(String merge, List<String> columnHolders, List<String> valueHolders) {
String[] sArray = getStrings(merge);
StringBuilder sb = new StringBuilder();
sb.append("MERGE INTO %s A USING ( SELECT ");
boolean first = true;
boolean first1 = true;
StringBuilder str = new StringBuilder();
StringBuilder update = new StringBuilder();
for (String columnHolder : columnHolders) {
if (Arrays.asList(sArray).contains(columnHolder)) {
if (!first) {
sb.append(",");
str.append(" AND ");
} else {
first = false;
}
str.append("TMP.").append(columnHolder);
sb.append("?");
str.append(" = ");
sb.append(" AS ");
str.append("A.").append(columnHolder);
sb.append(columnHolder);
}
}
for (String columnHolder : columnHolders) {
if (!Arrays.asList(sArray).contains(columnHolder)) {
if (!first1) {
update.append(",");
} else {
first1 = false;
}
update.append(columnHolder);
update.append(" = ");
update.append("?");
}
}
sb.append(" FROM DUAL ) TMP ON (");
sb.append(str);
sb.append(" ) WHEN MATCHED THEN UPDATE SET ");
sb.append(update);
sb.append(" WHEN NOT MATCHED THEN ");
return sb.toString();
}
public static String[] getStrings(String merge) {
merge = merge.replace("update", "");
merge = merge.replace("(", "");
merge = merge.replace(")", "");
merge = merge.replace(" ", "");
return merge.split(",");
}
}
3、CommonRdbmsWriter.java
package com.alibaba.datax.plugin.rdbms.writer;
import com.alibaba.datax.common.element.Column;
import com.alibaba.datax.common.element.Record;
import com.alibaba.datax.common.exception.DataXException;
import com.alibaba.datax.common.plugin.RecordReceiver;
import com.alibaba.datax.common.plugin.TaskPluginCollector;
import com.alibaba.datax.common.util.Configuration;
import com.alibaba.datax.plugin.rdbms.util.DBUtil;
import com.alibaba.datax.plugin.rdbms.util.DBUtilErrorCode;
import com.alibaba.datax.plugin.rdbms.util.DataBaseType;
import com.alibaba.datax.plugin.rdbms.util.RdbmsException;
import com.alibaba.datax.plugin.rdbms.writer.util.OriginalConfPretreatmentUtil;
import com.alibaba.datax.plugin.rdbms.writer.util.WriterUtil;
import java.util.Arrays;
import java.util.LinkedList;
import java.util.concurrent.atomic.AtomicLong;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.tuple.Triple;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
public class CommonRdbmsWriter {
public static class Job {
private DataBaseType dataBaseType;
private static final Logger LOG = LoggerFactory
.getLogger(Job.class);
public Job(DataBaseType dataBaseType) {
this.dataBaseType = dataBaseType;
OriginalConfPretreatmentUtil.DATABASE_TYPE = this.dataBaseType;
}
public void init(Configuration originalConfig) {
OriginalConfPretreatmentUtil.doPretreatment(originalConfig, this.dataBaseType);
LOG.debug("After job init(), originalConfig now is:[\n{}\n]",
originalConfig.toJSON());
}
/*目前只支持MySQL Writer跟Oracle Writer;检查PreSQL跟PostSQL语法以及insert，delete权限*/
public void writerPreCheck(Configuration originalConfig, DataBaseType dataBaseType) {
/*检查PreSql跟PostSql语句*/
prePostSqlValid(originalConfig, dataBaseType);
/*检查insert 跟delete权限*/
privilegeValid(originalConfig, dataBaseType);
}
public void prePostSqlValid(Configuration originalConfig, DataBaseType dataBaseType) {
/*检查PreSql跟PostSql语句*/
WriterUtil.preCheckPrePareSQL(originalConfig, dataBaseType);
WriterUtil.preCheckPostSQL(originalConfig, dataBaseType);
}
public void privilegeValid(Configuration originalConfig, DataBaseType dataBaseType) {
/*检查insert 跟delete权限*/
String username = originalConfig.getString(Key.USERNAME);
String password = originalConfig.getString(Key.PASSWORD);
List<Object> connections = originalConfig.getList(Constant.CONN_MARK,
Object.class);
for (int i = 0, len = connections.size(); i < len; i++) {
Configuration connConf = Configuration.from(connections.get(i).toString());
String jdbcUrl = connConf.getString(Key.JDBC_URL);
List<String> expandedTables = connConf.getList(Key.TABLE, String.class);
boolean hasInsertPri = DBUtil.checkInsertPrivilege(dataBaseType, jdbcUrl, username, password, expandedTables);
if (!hasInsertPri) {
throw RdbmsException.asInsertPriException(dataBaseType, originalConfig.getString(Key.USERNAME), jdbcUrl);
}
if (DBUtil.needCheckDeletePrivilege(originalConfig)) {
boolean hasDeletePri = DBUtil.checkDeletePrivilege(dataBaseType, jdbcUrl, username, password, expandedTables);
if (!hasDeletePri) {
throw RdbmsException.asDeletePriException(dataBaseType, originalConfig.getString(Key.USERNAME), jdbcUrl);
}
}
}
}
// 一般来说，是需要推迟到 task 中进行pre 的执行（单表情况例外）
public void prepare(Configuration originalConfig) {
int tableNumber = originalConfig.getInt(Constant.TABLE_NUMBER_MARK);
if (tableNumber == 1) {
String username = originalConfig.getString(Key.USERNAME);
String password = originalConfig.getString(Key.PASSWORD);
List<Object> conns = originalConfig.getList(Constant.CONN_MARK,
Object.class);
Configuration connConf = Configuration.from(conns.get(0)
.toString());
// 这里的 jdbcUrl 已经 append 了合适后缀参数
String jdbcUrl = connConf.getString(Key.JDBC_URL);
originalConfig.set(Key.JDBC_URL, jdbcUrl);
String table = connConf.getList(Key.TABLE, String.class).get(0);
originalConfig.set(Key.TABLE, table);
List<String> preSqls = originalConfig.getList(Key.PRE_SQL,
String.class);
List<String> renderedPreSqls = WriterUtil.renderPreOrPostSqls(
preSqls, table);
originalConfig.remove(Constant.CONN_MARK);
if (null != renderedPreSqls && !renderedPreSqls.isEmpty()) {
// 说明有 preSql 配置，则此处删除掉
originalConfig.remove(Key.PRE_SQL);
Connection conn = DBUtil.getConnection(dataBaseType,
jdbcUrl, username, password);
LOG.info("Begin to execute preSqls:[{}]. context info:{}.",
StringUtils.join(renderedPreSqls, ";"), jdbcUrl);
WriterUtil.executeSqls(conn, renderedPreSqls, jdbcUrl, dataBaseType);
DBUtil.closeDBResources(null, null, conn);
}
}
LOG.debug("After job prepare(), originalConfig now is:[\n{}\n]",
originalConfig.toJSON());
}
public List<Configuration> split(Configuration originalConfig,
int mandatoryNumber) {
return WriterUtil.doSplit(originalConfig, mandatoryNumber);
}
// 一般来说，是需要推迟到 task 中进行post 的执行（单表情况例外）
public void post(Configuration originalConfig) {
int tableNumber = originalConfig.getInt(Constant.TABLE_NUMBER_MARK);
if (tableNumber == 1) {
String username = originalConfig.getString(Key.USERNAME);
String password = originalConfig.getString(Key.PASSWORD);
// 已经由 prepare 进行了appendJDBCSuffix处理
String jdbcUrl = originalConfig.getString(Key.JDBC_URL);
String table = originalConfig.getString(Key.TABLE);
List<String> postSqls = originalConfig.getList(Key.POST_SQL,
String.class);
List<String> renderedPostSqls = WriterUtil.renderPreOrPostSqls(
postSqls, table);
if (null != renderedPostSqls && !renderedPostSqls.isEmpty()) {
// 说明有 postSql 配置，则此处删除掉
originalConfig.remove(Key.POST_SQL);
Connection conn = DBUtil.getConnection(this.dataBaseType,
jdbcUrl, username, password);
LOG.info(
"Begin to execute postSqls:[{}]. context info:{}.",
StringUtils.join(renderedPostSqls, ";"), jdbcUrl);
WriterUtil.executeSqls(conn, renderedPostSqls, jdbcUrl, dataBaseType);
DBUtil.closeDBResources(null, null, conn);
}
}
}
public void destroy(Configuration originalConfig) {
}
}
public static class Task {
protected static final Logger LOG = LoggerFactory
.getLogger(Task.class);
protected DataBaseType dataBaseType;
private static final String VALUE_HOLDER = "?";
protected String username;
protected String password;
protected String jdbcUrl;
protected String table;
protected List<String> columns;
protected List<String> preSqls;
protected List<String> postSqls;
protected int batchSize;
protected int batchByteSize;
protected int columnNumber = 0;
protected TaskPluginCollector taskPluginCollector;
// 作为日志显示信息时，需要附带的通用信息。比如信息所对应的数据库连接等信息，针对哪个表做的操作
protected static String BASIC_MESSAGE;
protected static String INSERT_OR_REPLACE_TEMPLATE;
protected String writeRecordSql;
protected String writeMode;
protected boolean emptyAsNull;
protected Triple<List<String>, List<Integer>, List<String>> resultSetMetaData;
private int dumpRecordLimit = Constant.DEFAULT_DUMP_RECORD_LIMIT;
private AtomicLong dumpRecordCount = new AtomicLong(0);
public Task(DataBaseType dataBaseType) {
this.dataBaseType = dataBaseType;
}
public void init(Configuration writerSliceConfig) {
this.username = writerSliceConfig.getString(Key.USERNAME);
this.password = writerSliceConfig.getString(Key.PASSWORD);
this.jdbcUrl = writerSliceConfig.getString(Key.JDBC_URL);
//ob10的处理
if (this.jdbcUrl.startsWith(Constant.OB10_SPLIT_STRING)) {
String[] ss = this.jdbcUrl.split(Constant.OB10_SPLIT_STRING_PATTERN);
if (ss.length != 3) {
throw DataXException
.asDataXException(
DBUtilErrorCode.JDBC_OB10_ADDRESS_ERROR, "JDBC OB10格式错误，请联系askdatax");
}
LOG.info("this is ob1_0 jdbc url.");
this.username = ss[1].trim() + ":" + this.username;
this.jdbcUrl = ss[2];
LOG.info("this is ob1_0 jdbc url. user=" + this.username + " :url=" + this.jdbcUrl);
}
this.table = writerSliceConfig.getString(Key.TABLE);
this.columns = writerSliceConfig.getList(Key.COLUMN, String.class);
this.columnNumber = this.columns.size();
this.preSqls = writerSliceConfig.getList(Key.PRE_SQL, String.class);
this.postSqls = writerSliceConfig.getList(Key.POST_SQL, String.class);
this.batchSize = writerSliceConfig.getInt(Key.BATCH_SIZE, Constant.DEFAULT_BATCH_SIZE);
this.batchByteSize = writerSliceConfig.getInt(Key.BATCH_BYTE_SIZE, Constant.DEFAULT_BATCH_BYTE_SIZE);
writeMode = writerSliceConfig.getString(Key.WRITE_MODE, "INSERT");
emptyAsNull = writerSliceConfig.getBool(Key.EMPTY_AS_NULL, true);
INSERT_OR_REPLACE_TEMPLATE = writerSliceConfig.getString(Constant.INSERT_OR_REPLACE_TEMPLATE_MARK);
this.writeRecordSql = String.format(INSERT_OR_REPLACE_TEMPLATE, this.table);
BASIC_MESSAGE = String.format("jdbcUrl:[%s], table:[%s]",
this.jdbcUrl, this.table);
}
public void prepare(Configuration writerSliceConfig) {
Connection connection = DBUtil.getConnection(this.dataBaseType,
this.jdbcUrl, username, password);
DBUtil.dealWithSessionConfig(connection, writerSliceConfig,
this.dataBaseType, BASIC_MESSAGE);
int tableNumber = writerSliceConfig.getInt(
Constant.TABLE_NUMBER_MARK);
if (tableNumber != 1) {
LOG.info("Begin to execute preSqls:[{}]. context info:{}.",
StringUtils.join(this.preSqls, ";"), BASIC_MESSAGE);
WriterUtil.executeSqls(connection, this.preSqls, BASIC_MESSAGE, dataBaseType);
}
DBUtil.closeDBResources(null, null, connection);
}
// 替换原先的代码块
public void startWriteWithConnection(RecordReceiver recordReceiver, TaskPluginCollector taskPluginCollector, Connection connection) {
this.taskPluginCollector = taskPluginCollector;
List<String> columns = new LinkedList<>();
if (this.dataBaseType == DataBaseType.Oracle && writeMode.trim().toLowerCase().startsWith("update") ) {
String merge = this.writeMode;
String[] sArray = WriterUtil.getStrings(merge);
this.columns.forEach(column->{
if (Arrays.asList(sArray).contains(column)) {
columns.add(column);
}
});
this.columns.forEach(column->{
if (!Arrays.asList(sArray).contains(column)) {
columns.add(column);
}
});
}
columns.addAll(this.columns);
// 用于写入数据的时候的类型根据目的表字段类型转换
this.resultSetMetaData = DBUtil.getColumnMetaData(connection, this.table, StringUtils.join(columns, ","));
// 写数据库的SQL语句
calcWriteRecordSql();
List<Record> writeBuffer = new ArrayList<Record>(this.batchSize);
int bufferBytes = 0;
try {
Record record;
while ((record = recordReceiver.getFromReader()) != null) {
if (record.getColumnNumber() != this.columnNumber) {
// 源头读取字段列数与目的表字段写入列数不相等，直接报错
throw DataXException
.asDataXException(
DBUtilErrorCode.CONF_ERROR,
String.format(
"列配置信息有错误. 因为您配置的任务中，源头读取字段数:%s 与 目的表要写入的字段数:%s 不相等. 请检查您的配置并作出修改.",
record.getColumnNumber(),
this.columnNumber));
}
writeBuffer.add(record);
bufferBytes += record.getMemorySize();
if (writeBuffer.size() >= batchSize || bufferBytes >= batchByteSize) {
doBatchInsert(connection, writeBuffer);
writeBuffer.clear();
bufferBytes = 0;
}
}
if (!writeBuffer.isEmpty()) {
doBatchInsert(connection, writeBuffer);
writeBuffer.clear();
bufferBytes = 0;
}
} catch (Exception e) {
throw DataXException.asDataXException(
DBUtilErrorCode.WRITE_DATA_ERROR, e);
} finally {
writeBuffer.clear();
bufferBytes = 0;
DBUtil.closeDBResources(null, null, connection);
}
}
// TODO 改用连接池，确保每次获取的连接都是可用的（注意：连接可能需要每次都初始化其 session）
public void startWrite(RecordReceiver recordReceiver,
Configuration writerSliceConfig,
TaskPluginCollector taskPluginCollector) {
Connection connection = DBUtil.getConnection(this.dataBaseType,
this.jdbcUrl, username, password);
DBUtil.dealWithSessionConfig(connection, writerSliceConfig,
this.dataBaseType, BASIC_MESSAGE);
startWriteWithConnection(recordReceiver, taskPluginCollector, connection);
}
public void post(Configuration writerSliceConfig) {
int tableNumber = writerSliceConfig.getInt(
Constant.TABLE_NUMBER_MARK);
boolean hasPostSql = (this.postSqls != null && this.postSqls.size() > 0);
if (tableNumber == 1 || !hasPostSql) {
return;
}
Connection connection = DBUtil.getConnection(this.dataBaseType,
this.jdbcUrl, username, password);
LOG.info("Begin to execute postSqls:[{}]. context info:{}.",
StringUtils.join(this.postSqls, ";"), BASIC_MESSAGE);
WriterUtil.executeSqls(connection, this.postSqls, BASIC_MESSAGE, dataBaseType);
DBUtil.closeDBResources(null, null, connection);
}
public void destroy(Configuration writerSliceConfig) {
}
protected void doBatchInsert(Connection connection, List<Record> buffer)
throws SQLException
{
PreparedStatement preparedStatement = null;
try {
connection.setAutoCommit(false);
preparedStatement = connection
.prepareStatement(this.writeRecordSql);
if (this.dataBaseType == DataBaseType.Oracle && !"insert".equalsIgnoreCase(this.writeMode)) {
String merge = this.writeMode;
String[] sArray = WriterUtil.getStrings(merge);
for (Record record : buffer) {
List<Column> recordOne = new ArrayList<>();
for (int j = 0; j < this.columns.size(); j++) {
if (Arrays.asList(sArray).contains(this.columns.get(j))) {
recordOne.add(record.getColumn(j));
}
}
for (int j = 0; j < this.columns.size(); j++) {
if (!Arrays.asList(sArray).contains(this.columns.get(j))) {
recordOne.add(record.getColumn(j));
}
}
for (int j = 0; j < this.columns.size(); j++) {
recordOne.add(record.getColumn(j));
}
for (int j = 0; j < recordOne.size(); j++) {
record.setColumn(j, recordOne.get(j));
}
preparedStatement = fillPreparedStatement(
preparedStatement, record);
preparedStatement.addBatch();
}
}
else {
for (Record record : buffer) {
preparedStatement = fillPreparedStatement(
preparedStatement, record);
preparedStatement.addBatch();
}
}
preparedStatement.executeBatch();
connection.commit();
}
catch (SQLException e) {
LOG.warn("回滚此次写入, 采用每次写入一行方式提交. 因为: {}", e.getMessage());
connection.rollback();
doOneInsert(connection, buffer);
}
catch (Exception e) {
throw DataXException.asDataXException(
DBUtilErrorCode.WRITE_DATA_ERROR, e);
}
finally {
DBUtil.closeDBResources(preparedStatement, null);
}
}
public boolean needToDumpRecord() {
return dumpRecordCount.incrementAndGet() <= dumpRecordLimit;
}
public void doOneInsert(Connection connection, List<Record> buffer) {
PreparedStatement preparedStatement = null;
try {
connection.setAutoCommit(true);
preparedStatement = connection
.prepareStatement(this.writeRecordSql);
for (Record record : buffer) {
try {
preparedStatement = fillPreparedStatement(
preparedStatement, record);
preparedStatement.execute();
} catch (SQLException e) {
if (needToDumpRecord()) {
LOG.warn("ERROR : record {}", record);
LOG.warn("Insert fatal error SqlState ={}, errorCode = {}, {}", e.getSQLState(), e.getErrorCode(), e);
}
this.taskPluginCollector.collectDirtyRecord(record, e);
} finally {
// 最后不要忘了关闭 preparedStatement
preparedStatement.clearParameters();
}
}
} catch (Exception e) {
throw DataXException.asDataXException(
DBUtilErrorCode.WRITE_DATA_ERROR, e);
} finally {
DBUtil.closeDBResources(preparedStatement, null);
}
}
protected PreparedStatement fillPreparedStatement(PreparedStatement preparedStatement, Record record)
throws SQLException
{
for (int i = 0; i < record.getColumnNumber(); i++) {
int columnSqltype = this.resultSetMetaData.getMiddle().get(i);
String typeName = this.resultSetMetaData.getRight().get(i);
preparedStatement = fillPreparedStatementColumnType(preparedStatement, i,columnSqltype, typeName,record.getColumn(i));
}
return preparedStatement;
}
protected PreparedStatement fillPreparedStatementColumnType(PreparedStatement preparedStatement, int columnIndex,
int columnSqltype, String typeName, Column column) throws SQLException {
java.util.Date utilDate;
switch (columnSqltype) {
case Types.CHAR:
case Types.NCHAR:
case Types.CLOB:
case Types.NCLOB:
case Types.VARCHAR:
case Types.LONGVARCHAR:
case Types.NVARCHAR:
case Types.LONGNVARCHAR:
preparedStatement.setString(columnIndex + 1, column
.asString());
break;
case Types.SMALLINT:
case Types.INTEGER:
case Types.BIGINT:
case Types.NUMERIC:
case Types.DECIMAL:
case Types.FLOAT:
case Types.REAL:
case Types.DOUBLE:
String strValue = column.asString();
if (emptyAsNull && "".equals(strValue)) {
preparedStatement.setString(columnIndex + 1, null);
} else {
preparedStatement.setString(columnIndex + 1, strValue);
}
break;
//tinyint is a little special in some database like mysql {boolean->tinyint(1)}
case Types.TINYINT:
Long longValue = column.asLong();
if (null == longValue) {
preparedStatement.setString(columnIndex + 1, null);
} else {
preparedStatement.setString(columnIndex + 1, longValue.toString());
}
break;
// for mysql bug, see http://bugs.mysql.com/bug.php?id=35115
case Types.DATE:
if (typeName == null) {
typeName = this.resultSetMetaData.getRight().get(columnIndex);
}
if (typeName.equalsIgnoreCase("year")) {
if (column.asBigInteger() == null) {
preparedStatement.setString(columnIndex + 1, null);
} else {
preparedStatement.setInt(columnIndex + 1, column.asBigInteger().intValue());
}
} else {
java.sql.Date sqlDate = null;
try {
utilDate = column.asDate();
} catch (DataXException e) {
throw new SQLException(String.format(
"Date 类型转换错误：[%s]", column));
}
if (null != utilDate) {
sqlDate = new java.sql.Date(utilDate.getTime());
}
preparedStatement.setDate(columnIndex + 1, sqlDate);
}
break;
case Types.TIME:
java.sql.Time sqlTime = null;
try {
utilDate = column.asDate();
} catch (DataXException e) {
throw new SQLException(String.format(
"TIME 类型转换错误：[%s]", column));
}
if (null != utilDate) {
sqlTime = new java.sql.Time(utilDate.getTime());
}
preparedStatement.setTime(columnIndex + 1, sqlTime);
break;
case Types.TIMESTAMP:
java.sql.Timestamp sqlTimestamp = null;
try {
utilDate = column.asDate();
} catch (DataXException e) {
throw new SQLException(String.format(
"TIMESTAMP 类型转换错误：[%s]", column));
}
if (null != utilDate) {
sqlTimestamp = new java.sql.Timestamp(
utilDate.getTime());
}
preparedStatement.setTimestamp(columnIndex + 1, sqlTimestamp);
break;
case Types.BINARY:
case Types.VARBINARY:
case Types.BLOB:
case Types.LONGVARBINARY:
preparedStatement.setBytes(columnIndex + 1, column
.asBytes());
break;
case Types.BOOLEAN:
preparedStatement.setBoolean(columnIndex + 1, column.asBoolean());
break;
// warn: bit(1) -> Types.BIT 可使用setBoolean
// warn: bit(>1) -> Types.VARBINARY 可使用setBytes
case Types.BIT:
if (this.dataBaseType == DataBaseType.MySql) {
preparedStatement.setBoolean(columnIndex + 1, column.asBoolean());
} else {
preparedStatement.setString(columnIndex + 1, column.asString());
}
break;
default:
throw DataXException
.asDataXException(
DBUtilErrorCode.UNSUPPORTED_TYPE,
String.format(
"您的配置文件中的列配置信息有误. 因为DataX 不支持数据库写入这种字段类型. 字段名:[%s], 字段类型:[%d], 字段Java类型:[%s]. 请修改表中该字段的类型或者不同步该字段.",
this.resultSetMetaData.getLeft()
.get(columnIndex),
this.resultSetMetaData.getMiddle()
.get(columnIndex),
this.resultSetMetaData.getRight()
.get(columnIndex)));
}
return preparedStatement;
}
private void calcWriteRecordSql() {
if (!VALUE_HOLDER.equals(calcValueHolder(""))) {
List<String> valueHolders = new ArrayList<String>(columnNumber);
for (int i = 0; i < columns.size(); i++) {
String type = resultSetMetaData.getRight().get(i);
valueHolders.add(calcValueHolder(type));
}
boolean forceUseUpdate = false;
//ob10的处理
if (dataBaseType != null && dataBaseType == DataBaseType.MySql && OriginalConfPretreatmentUtil.isOB10(jdbcUrl)) {
forceUseUpdate = true;
}
INSERT_OR_REPLACE_TEMPLATE = WriterUtil.getWriteTemplate(columns, valueHolders, writeMode, dataBaseType, forceUseUpdate);
writeRecordSql = String.format(INSERT_OR_REPLACE_TEMPLATE, this.table);
}
}
protected String calcValueHolder(String columnType) {
return VALUE_HOLDER;
}
}
}
- 修改完后打包,将打包后的jar包复制,并启动
将[oraclewriter-0.0.1-SNAPSHOT.jar]替换到: datax\plugin\writer\oraclewriter
将[plugin-rdbms-util-0.0.1-SNAPSHOT.jar]替换到: datax\plugin\writer\oraclewriter\libs
- 调整文件(datax/conf/core.json),此步骤可视执行结果处理,若datax执行无报错则可不配置
core -> transport -> channel -> speed -> byte: 200000
- Datax调度json文件修改点 (writeMode)
{
"job": {
"setting": {
"speed": {
"byte": 1048576
},
"errorLimit": {
"record": 0,
"percentage": 0.02
}
},
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "${r_username}",
"password": "${r_password}",
"connection": [
{
"querySql": ["SELECT f_year,f_code,f_name,f_order FROM tableName"],
"jdbcUrl": ["${r_jdbcUrl}"]
}
]
}
},
"writer": {
"name": "oraclewriter",
"parameter": {
"writeMode": "update(f_year,f_code)",
"username": "${w_username}",
"password": "${w_password}",
"column": [
"f_year","f_code","f_name","f_order"
],
"session": [],
"preSql": [],
"connection": [
{
"jdbcUrl": "${w_jdbcUrl}",
"table": ["tableName"]
}
]
}
}
}
]
}
}
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
