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 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""&_+-!/=/=""", 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 filename = sourceDB.ToLower() + "/" + destTableName + ".txt"; destDataUtils.BulkCopy(filename, destDB, destTableName, 60 * 10, cols); }
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); }