private static string ParseAwsS3Path(FileTransferDetails obj, string inputSearchPath) { inputSearchPath = inputSearchPath.Substring("s3://".Length); // extract bucketname var idx = inputSearchPath.IndexOf('/'); if (idx < 0) { throw new Exception("Invalid S3 file search path"); } obj.Location = FileTransferDetails.FileLocation.S3; obj.BucketName = inputSearchPath.Substring(0, idx); // find file wildcard: var wildCard1 = inputSearchPath.IndexOf('*'); var wildCard2 = inputSearchPath.IndexOf('?'); if (wildCard1 > 0 || wildCard2 > 0) { int endPos; if (wildCard1 > 0 && wildCard2 > 0) { endPos = Math.Min(wildCard1, wildCard2); } else if (wildCard1 > 0) { endPos = wildCard1; } else { endPos = wildCard2; } obj.UseWildCardSearch = true; obj.SearchPattern = WildcardToRegex(inputSearchPath.Substring(idx + 1)); obj.FilePath = inputSearchPath.Substring(idx + 1, endPos - (idx + 1)); } else { obj.UseWildCardSearch = false; obj.FilePath = inputSearchPath.Substring(idx + 1); } return(inputSearchPath); }
private IEnumerable <string> GetFilesFromS3(AWSS3Helper s3, FileTransferDetails parsed) { // get file from s3 if (parsed.UseWildCardSearch) { var rgx = new System.Text.RegularExpressions.Regex(parsed.SearchPattern, System.Text.RegularExpressions.RegexOptions.IgnoreCase | System.Text.RegularExpressions.RegexOptions.Singleline); foreach (var f in s3.GetFileList(parsed.FilePath, true, true).Where(f => !f.EndsWith("/"))) { if (rgx.IsMatch(f)) { yield return(f); } } } else { foreach (var f in s3.GetFileList(parsed.FilePath, true, true).Where(f => !f.EndsWith("/"))) { yield return(f); } } }
public static FileTransferDetails ParseSearchPath(string inputSearchPath) { FileTransferDetails obj = new FileTransferDetails(); // sanity check if (string.IsNullOrWhiteSpace(inputSearchPath)) { return(obj); } inputSearchPath = inputSearchPath.Trim().Replace("\\", "/"); // s3://BucketName/directory_like_path/*.sql var prefix = GetPrefix(inputSearchPath); if (String.IsNullOrEmpty(prefix)) { throw new ArgumentException("Invalid path", "inputSearchPath"); } switch (prefix) { case "s3": inputSearchPath = ParseAwsS3Path(obj, inputSearchPath); break; case "http": case "https": case "ftp": case "ftps": case "sftp": default: throw new NotImplementedException(); } return(obj); }
public bool Execute(ISessionContext context) { var logger = context.GetLogger(); var options = context.Options; AWSS3Helper s3 = null; List <string> files = null; FileTransferDetails parsedErrorLocation = null; try { var inputSearchPath = options.Get("inputSearchPath", ""); if (String.IsNullOrEmpty(inputSearchPath)) { throw new ArgumentNullException("inputSearchPath"); } var backupLocation = options.Get("backupLocation", ""); if (String.IsNullOrEmpty(backupLocation)) { throw new ArgumentNullException("backupLocation"); } var loadScript = options.Get("sqlScriptPath", ""); if (String.IsNullOrEmpty(loadScript)) { throw new ArgumentNullException("sqlScriptPath"); } var errorLocation = options.Get("errorLocation", ""); if (String.IsNullOrEmpty(errorLocation)) { throw new ArgumentNullException("errorLocation"); } var customCSharpScriptPath = options.Get("customCSharpScriptPath", ""); if (String.IsNullOrEmpty(customCSharpScriptPath)) { throw new ArgumentNullException("customCSharpScriptPath"); } // prepare paths var parsedInput = FileTransferDetails.ParseSearchPath(inputSearchPath); var parsedLoadScript = FileTransferDetails.ParseSearchPath(loadScript); var parsedBackupLocation = FileTransferDetails.ParseSearchPath(backupLocation); parsedErrorLocation = FileTransferDetails.ParseSearchPath(errorLocation); var parsedCustomCSharpScriptPath = FileTransferDetails.ParseSearchPath(customCSharpScriptPath); // open s3 connection s3 = new AWSS3Helper(options.Get("awsAccessKey", ""), options.Get("awsSecretAccessKey", ""), parsedInput.BucketName, Amazon.RegionEndpoint.USEast1, true); var csharpScript = s3.ReadFileAsText(parsedCustomCSharpScriptPath.FilePath, true); // generate code var evaluator = ScriptEvaluator.CompileAndCreateModel(csharpScript); if (evaluator.HasError || evaluator.Model == null) { throw new Exception("Script compilation error. " + (evaluator.Message ?? "<empty>")); } // 1. check if there is any new file files = GetFilesFromS3(s3, parsedInput).ToList(); if (files.Any()) { logger.Info("Files found: " + files.Count); } else { logger.Debug("No file found"); return(false); } var connectionString = RedshiftHelper.GetConnectionString(context); foreach (var f in files) { var sqlScript = s3.ReadFileAsText(parsedLoadScript.FilePath, true); if (String.IsNullOrEmpty(sqlScript)) { throw new Exception("invalid sql script"); } using (var conn = new Npgsql.NpgsqlConnection(connectionString)) { conn.Open(); var fullFilename = System.IO.Path.Combine("s3://", parsedInput.BucketName, f.Trim()).Replace('\\', '/'); options.Set("InputFilename", fullFilename); evaluator.Model.Initialize(conn, s3, context); evaluator.Model.BeforeExecution(); sqlScript = evaluator.Model.PrepareSqlCOPYCommand(sqlScript); // Create a PostgeSQL connection string. ExecuteRedshiftLoad(connectionString, logger, sqlScript, new List <string> () { f }, parsedInput); logger.Debug("Moving files to backup folder"); evaluator.Model.AfterExecution(); // move files var destName = System.IO.Path.Combine(parsedBackupLocation.FilePath, System.IO.Path.GetFileName(f)); s3.MoveFile(f, destName, false); } logger.Success("Done"); } } catch (Exception ex) { context.Error = ex.Message; logger.Error(ex); try { if (files != null && s3 != null) { // move files foreach (var f in files) { var destName = System.IO.Path.Combine(parsedErrorLocation.FilePath, System.IO.Path.GetFileName(f)); s3.MoveFile(f, destName, false); } } } catch { } return(false); } return(true); }
private void ExecuteRedshiftLoad(string connectionString, IActionLogger logger, string script, List <string> files, FileTransferDetails filesDetails) { var dtStart = DateTime.UtcNow; // prepare files full name HashSet <string> fileMap = new HashSet <string> (files .Select(f => System.IO.Path.Combine("s3://", filesDetails.BucketName, f.Trim()).Replace('\\', '/')), StringComparer.OrdinalIgnoreCase); // execute redshift load. logger.Debug("SQL Script start"); RedshiftHelper.RunLoad(connectionString, script); logger.Debug("SQL Script end"); int num_files = files.Count; do { num_files = 0; foreach (var i in stv_load_state.Read(connectionString)) { if (fileMap.Contains(i.current_file)) { num_files++; } } // wait a while if (num_files > 0) { // sanity check... 2 hours to run a load operation is too long... if ((DateTime.UtcNow - dtStart) > TimeSpan.FromHours(3)) { break; } // wait a while System.Threading.Thread.Sleep(10000); } }while (num_files > 0); // check load errors table foreach (var i in stl_load_errors.Read(connectionString, dtStart)) { if (fileMap.Contains(i.filename.Trim())) { throw new Exception("Load error detected <stl_load_errors>: " + Newtonsoft.Json.JsonConvert.SerializeObject(i)); } } // check commited files table var filesLoaded = new HashSet <string> (stl_load_commits.ReadCommitedFiles(connectionString, dtStart).Select(f => f.Trim()), StringComparer.OrdinalIgnoreCase); foreach (var f in fileMap) { if (!filesLoaded.Contains(f)) { throw new Exception("Load error; file not commited <stl_load_commits>: " + f); } } logger.Debug("Files loaded"); }
public bool Execute(ISessionContext context) { var logger = context.GetLogger(); var options = context.Options; AWSS3Helper s3 = null; try { var loadScript = options.Get("sqlScriptPath", ""); var customCSharpScriptPath = options.Get("customCSharpScriptPath", ""); if ((!String.IsNullOrWhiteSpace(loadScript)) && (!String.IsNullOrWhiteSpace(customCSharpScriptPath))) { throw new Exception("No action configured"); } // prepare paths var parsedLoadScript = FileTransferDetails.ParseSearchPath(loadScript); var parsedCustomCSharpScriptPath = FileTransferDetails.ParseSearchPath(customCSharpScriptPath); // open s3 connection s3 = new AWSS3Helper(options.Get("awsAccessKey", ""), options.Get("awsSecretAccessKey", ""), parsedLoadScript.BucketName, Amazon.RegionEndpoint.USEast1, true); // load sql script string sqlScript = null; if (!String.IsNullOrWhiteSpace(loadScript)) { sqlScript = s3.ReadFileAsText(parsedLoadScript.FilePath, true); } // generate code IAWSRedshiftPluginDynamicScript customCode = null; if (!String.IsNullOrWhiteSpace(customCSharpScriptPath)) { // load custom code var csharpScript = s3.ReadFileAsText(parsedCustomCSharpScriptPath.FilePath, true); var evaluator = ScriptEvaluator.CompileAndCreateModel(csharpScript); if (evaluator.HasError || evaluator.Model == null) { throw new Exception("Script compilation error. " + (evaluator.Message ?? "<empty>")); } customCode = evaluator.Model; } // execute commands using (var conn = new Npgsql.NpgsqlConnection(RedshiftHelper.GetConnectionString(context))) { conn.Open(); if (customCode != null) { logger.Debug("Custom csharp code Initialize"); customCode.Initialize(conn, s3, context); logger.Debug("Custom csharp code BeforeExecution"); customCode.BeforeExecution(); logger.Debug("Custom csharp code PrepareSqlCOPYCommand"); if (!String.IsNullOrEmpty(sqlScript)) { sqlScript = customCode.PrepareSqlCOPYCommand(sqlScript); } } if (!String.IsNullOrEmpty(sqlScript)) { logger.Debug("SQL command start"); try { conn.Execute(sqlScript); } catch (Exception ex) { // do nothing in case of timeout... some operations may take a while to complete... if (ex.Message.IndexOf("timeout", StringComparison.OrdinalIgnoreCase) < 0) { throw ex; } logger.Info("SQL command executed, but is still running (connection timeout)..."); } logger.Debug("SQL command end"); } if (customCode != null) { logger.Debug("Custom csharp code AfterExecution"); customCode.AfterExecution(); } } logger.Success("Done"); } catch (Exception ex) { context.Error = ex.Message; logger.Error(ex); return(false); } return(true); }
public bool Execute(ISessionContext context) { var logger = context.GetLogger(); var options = context.Options; AWSS3Helper s3 = null; List <string> files = null; FileTransferDetails parsedErrorLocation = null; try { var inputSearchPath = options.Get("inputSearchPath", ""); if (String.IsNullOrEmpty(inputSearchPath)) { throw new ArgumentNullException("inputSearchPath"); } var backupLocation = options.Get("backupLocation", ""); if (String.IsNullOrEmpty(backupLocation)) { throw new ArgumentNullException("backupLocation"); } var loadScript = options.Get("sqlScriptPath", ""); if (String.IsNullOrEmpty(loadScript)) { throw new ArgumentNullException("sqlScriptPath"); } var errorLocation = options.Get("errorLocation", ""); if (String.IsNullOrEmpty(errorLocation)) { throw new ArgumentNullException("errorLocation"); } // prepare paths var parsedInput = FileTransferDetails.ParseSearchPath(inputSearchPath); var parsedLoadScript = FileTransferDetails.ParseSearchPath(loadScript); var parsedBackupLocation = FileTransferDetails.ParseSearchPath(backupLocation); parsedErrorLocation = FileTransferDetails.ParseSearchPath(errorLocation); // open s3 connection s3 = new AWSS3Helper(options.Get("awsAccessKey", ""), options.Get("awsSecretAccessKey", ""), parsedInput.BucketName, Amazon.RegionEndpoint.USEast1, true); // 1. check if there is any new file files = GetFilesFromS3(s3, parsedInput).Where(f => !f.EndsWith("/")).ToList(); if (files.Any()) { logger.Info("Files found: " + files.Count); var sqlScript = s3.ReadFileAsText(parsedLoadScript.FilePath, false); if (String.IsNullOrEmpty(sqlScript)) { throw new Exception("Invalid sql script: " + parsedLoadScript.FilePath); } // Create a PostgeSQL connection string. var connectionString = RedshiftHelper.GetConnectionString(context); ExecuteRedshiftLoad(connectionString, logger, sqlScript, files, parsedInput); logger.Debug("Moving files to backup folder"); // move files // TODO: what happens if move fails? foreach (var f in files) { var destName = System.IO.Path.Combine(parsedBackupLocation.FilePath, System.IO.Path.GetFileName(f)); if (s3.MoveFile(f, destName, false)) { System.Threading.Thread.Sleep(250); if (s3.MoveFile(f, destName, false)) { logger.Error(String.Format("Error moving file {0}, {1}", f, s3.LastError)); } } } logger.Success("Done"); return(true); } else { logger.Debug("No file found"); return(false); } } catch (Exception ex) { context.Error = ex.Message; logger.Error(ex); try { if (files != null && s3 != null) { // move files foreach (var f in files) { var destName = System.IO.Path.Combine(parsedErrorLocation.FilePath, System.IO.Path.GetFileName(f)); s3.MoveFile(f, destName, false); } } } catch {} return(false); } return(true); }