public void CopyTable(string sourceDB, string sourceTableName, string schema, string destDB, int timeout, string destTableName = null, string originalTableName = null)
{
//by default the dest table will have the same name as the source table
destTableName = (destTableName == null) ? sourceTableName : destTableName;
originalTableName = originalTableName ?? sourceTableName;
//drop table at destination and create from source schema
CopyTableDefinition(sourceDB, sourceTableName, schema, destDB, destTableName, originalTableName);
var cols = GetColumns(sourceDB, sourceTableName, schema, originalTableName);
var bcpSelect = string.Format("SELECT {0} FROM {1}..{2};",
string.Join(",", cols.Select(col => col.ColExpression())),
sourceDB, sourceTableName);
if (bcpSelect.Length > 3800)
{
//BCP commands fail if their text length is over 4000 characters, and we need some padding
//drop view CTVWtablename if exists
//create view CTVWtablename AS $bcpSelect
string viewName = "CTVW" + sourceTableName;
sourceDataUtils.RecreateView(sourceDB, viewName, bcpSelect);
bcpSelect = string.Format("SELECT * FROM {0}..{1}", sourceDB, viewName);
}
string directory = Config.BcpPath.TrimEnd('\\') + @"\" + sourceDB.ToLower();
CreateDirectoryIfNotExists(directory);
string password = new cTripleDes().Decrypt(Config.RelayPassword);
var bcpArgs = string.Format(@"""{0}"" queryout {1}\{2}.txt -c -S{3} -U {4} -P {5} -t""|"" -r\n",
bcpSelect,
directory,
destTableName,
Config.RelayServer,
Config.RelayUser,
password
);
logger.Log("BCP command: bcp " + bcpArgs.Replace(password, "********"), LogLevel.Trace);
var outputBuilder = new StringBuilder();
var errorBuilder = new StringBuilder();
var bcp = new Process();
bcp.StartInfo.FileName = "bcp";
bcp.StartInfo.Arguments = bcpArgs;
bcp.StartInfo.UseShellExecute = false;
bcp.StartInfo.RedirectStandardError = true;
bcp.StartInfo.RedirectStandardOutput = true;
bcp.OutputDataReceived += delegate(object sender, DataReceivedEventArgs e) {
lock (outputBuilder) {
outputBuilder.AppendLine(e.Data);
}
};
bcp.ErrorDataReceived += delegate(object sender, DataReceivedEventArgs e) {
lock (errorBuilder) {
errorBuilder.AppendLine(e.Data);
}
};
bcp.Start();
bcp.BeginOutputReadLine();
bcp.BeginErrorReadLine();
bool status = bcp.WaitForExit(Config.DataCopyTimeout * 1000);
if (!status)
{
bcp.Kill();
throw new Exception("BCP timed out for table " + sourceTableName);
}
if (bcp.ExitCode != 0)
{
string err = outputBuilder + "\r\n" + errorBuilder;
logger.Log(err, LogLevel.Critical);
throw new Exception("BCP error: " + err);
}
logger.Log("BCP successful for " + sourceTableName, LogLevel.Trace);
string plinkArgs = string.Format(@"-ssh -v -batch -l {0} -i {1} {2} {3} {4} {5}",
nzUser,
nzPrivateKeyPath,
nzServer,
Config.NzLoadScriptPath,
destDB.ToLower(),
destTableName);
logger.Log("nzload command: " + Config.PlinkPath + " " + plinkArgs, LogLevel.Trace);
var plink = new Process();
outputBuilder.Clear();
errorBuilder.Clear();
plink.StartInfo.FileName = Config.PlinkPath;
plink.StartInfo.Arguments = plinkArgs;
plink.StartInfo.UseShellExecute = false;
plink.StartInfo.RedirectStandardError = true;
plink.StartInfo.RedirectStandardOutput = true;
plink.OutputDataReceived += delegate(object sender, DataReceivedEventArgs e) {
lock (outputBuilder) {
outputBuilder.AppendLine(e.Data);
}
};
plink.ErrorDataReceived += delegate(object sender, DataReceivedEventArgs e) {
lock (errorBuilder) {
errorBuilder.AppendLine(e.Data);
}
};
plink.Start();
plink.BeginOutputReadLine();
plink.BeginErrorReadLine();
status = plink.WaitForExit(Config.DataCopyTimeout * 1000);
if (!status)
{
plink.Kill();
throw new Exception("plink/nzload timed out for table " + sourceTableName);
}
//plink seems to make odd decisions about what to put in stdout vs. stderr, so we just lump them together
string output = outputBuilder + "\r\n" + errorBuilder;
if (plink.ExitCode != 0)
{
logger.Log(output, LogLevel.Critical);
throw new Exception("plink error: " + output);
}
if (output.Contains("Disconnected: User aborted at host key verification"))
{
throw new Exception("Error connecting to Netezza server: Please verify host key as the user that runs Tesla");
}
if (Regex.IsMatch(output, "Cannot open input file .* No such file or directory") ||
!output.Contains("completed successfully"))
{
throw new Exception("Netezza load failed: " + output);
}
logger.Log("nzload successful for table " + destTableName, LogLevel.Trace);
}
public void CopyTable(string sourceDB, string sourceTableName, string schema, string destDB, int timeout, string destTableName = null, string originalTableName = null)
{
//by default the dest table will have the same name as the source table
destTableName = (destTableName == null) ? sourceTableName : destTableName;
originalTableName = originalTableName ?? sourceTableName;
//drop table at destination and create from source schema
CopyTableDefinition(sourceDB, sourceTableName, schema, destDB, destTableName, originalTableName);
var cols = GetColumns(sourceDB, sourceTableName, schema, originalTableName);
var bcpSelect = string.Format("SELECT {0} FROM {1}..{2};",
string.Join(",", cols.Select(col => col.ColExpression())),
sourceDB, sourceTableName);
if (bcpSelect.Length > 3800)
{
//BCP commands fail if their text length is over 4000 characters, and we need some padding
//drop view CTVWtablename if exists
//create view CTVWtablename AS $bcpSelect
string viewName = "CTVW" + sourceTableName;
sourceDataUtils.RecreateView(sourceDB, viewName, bcpSelect);
bcpSelect = string.Format("SELECT * FROM {0}..{1}", sourceDB, viewName);
}
string bcpDirectory = Config.BcpPath.TrimEnd('\\') + @"\" + sourceDB.ToLower();
string bcpFileName = bcpDirectory + @"\" + destTableName + ".txt";
CreateDirectoryIfNotExists(bcpDirectory);
string password = new cTripleDes().Decrypt(Config.RelayPassword);
var bcpArgs = string.Format(@"""{0}"" queryout {1} -c -S{2} -U {3} -P {4} -t""|"" -r\n",
bcpSelect,
bcpFileName,
Config.RelayServer,
Config.RelayUser,
password
);
logger.Log("BCP command: bcp " + bcpArgs.Replace(password, "********"), LogLevel.Trace);
var outputBuilder = new StringBuilder();
var errorBuilder = new StringBuilder();
var bcp = new Process();
bcp.StartInfo.FileName = "bcp";
bcp.StartInfo.Arguments = bcpArgs;
bcp.StartInfo.UseShellExecute = false;
bcp.StartInfo.RedirectStandardError = true;
bcp.StartInfo.RedirectStandardOutput = true;
bcp.OutputDataReceived += delegate(object sender, DataReceivedEventArgs e) {
lock (outputBuilder) {
outputBuilder.AppendLine(e.Data);
}
};
bcp.ErrorDataReceived += delegate(object sender, DataReceivedEventArgs e) {
lock (errorBuilder) {
errorBuilder.AppendLine(e.Data);
}
};
bcp.Start();
bcp.BeginOutputReadLine();
bcp.BeginErrorReadLine();
bool status = bcp.WaitForExit(Config.DataCopyTimeout * 1000);
if (!status)
{
bcp.Kill();
throw new Exception("BCP timed out for table " + sourceTableName);
}
if (bcp.ExitCode != 0)
{
string err = outputBuilder + "\r\n" + errorBuilder;
logger.Log(err, LogLevel.Critical);
throw new Exception("BCP error: " + err);
}
logger.Log("BCP successful for " + destTableName, LogLevel.Trace);
// Use COPY command to copy data from data file to Vertica database
string verticaCopyDirectory = Config.VerticaCopyPath.TrimEnd('/') + "/" + sourceDB.ToLower();
string verticaCopyFileName = verticaCopyDirectory + "/" + destTableName + ".txt";
CopyDataFromQuery(verticaCopyFileName, destDB, destTableName, timeout);
logger.Log("Vertica COPY successful for table " + destTableName, LogLevel.Trace);
}
