def process_files_district(dno_files): for file in dno_files: try: target_cols = [ "RDate", "Schedule", "Route", "fRun", "DNO", "DNOLost", "DNC", "DNCLost", "Type", "Prob Location", "Prob Description", "Base", "Miles" ] parse_dates = ["RDate"] df = pd.read_excel(s3_bucket_prefix + file, usecols=target_cols, parse_dates=parse_dates) df['Source'] = "District" except Exception as e: failed_files.append({ 'file_name': file, 'reason': eC.INVALID_FILE, 'time': datetime.datetime.now() }) log_msg, sns_msg = util.get_log_template( dag=dag, task=task, status=sC.RUNNING, level=lC.ERROR, error=repr(e), remarks='Column Validation failed for dno district ' + file, reason=eC.INVALID_FILE, sns=True) logger.critical(log_msg) sns.publish(TopicArn=topicarn, Subject=subject, Message=sns_msg) continue try: write_to_s3(df, file) except Exception as e: failed_files.append({ 'file_name': file, 'reason': eC.S3_WRITE_FAILED, 'time': datetime.datetime.now() }) log_msg, sns_msg = util.get_log_template( dag=dag, task=task, status=sC.FAILED, level=lC.ERROR, error=repr(e), remarks='s3 Write:Failed ' + file, reason=eC.S3_WRITE_FAILED, sns=True) logger.critical(log_msg) sns.publish(TopicArn=topicarn, Subject=subject, Message=sns_msg)
def process_dno_cub(cub_files, month=None): res_list = [] for file in cub_files: # print(file) try: data = pd.ExcelFile(s3_bucket_prefix + file) sheet_df_mapping = get_df_by_sheet_mapping(data, month) new_mapping = parse_sheets(sheet_df_mapping) # print("keys",new_mapping.keys()) res_list.append(new_mapping) if len(new_mapping.keys()) < 1: # print("No sheets") pass else: write_dno_cub_to_s3(new_mapping, file) except Exception as e: failed_files.append({ 'file_name': file, 'reason': eC.CSV_PROCESSING_FAILED, 'time': datetime.datetime.now() }) log_msg, sns_msg = util.get_log_template( dag=dag, task=task, status=sC.RUNNING, level=lC.ERROR, error=repr(e), remarks=' CSV_PROCESSING_FAILED:Failed ' + file, reason=eC.CSV_PROCESSING_FAILED, sns=True) logger.critical(log_msg) sns.publish(TopicArn=topicarn, Subject=subject, Message=sns_msg)
def write_dno_cub_to_s3(new_mapping, file): filename = (file.split("/")[-1]).split(".")[-2] print("heyy:", filename) for date in new_mapping: try: month = datetime.datetime.strftime(date, '%b') year = date.year target_path_csv = processing_csv_path + 'CUB' + '/' + str( year) + '/' + str(month) + '/' + 'CUB_' + str( year) + '_' + str(month) + '_' + filename + '.csv' print("cub path", target_path_csv) # processing_path = processed_path.replace('processed','incoming/processing') # print(processing_path) df = new_mapping[date] # util.write_df_to_csv_on_s3(new_mapping[date],bucket,processed_path) df.to_csv(s3_bucket_prefix + target_path_csv, index=False) # util.write_df_to_csv_on_s3(new_mapping[date],bucket,processing_path) # df.to_csv(s3_bucket_prefix+processed_path,index=False) except Exception as e: log_msg, sns_msg = util.get_log_template( dag=dag, task=task, status=sC.RUNNING, level=lC.ERROR, error=repr(e), remarks='CUB CSV Write from sheet:Failed for ' + date, reason=eC.CSV_PROCESSING_FAILED, sns=True) logger.critical(log_msg) sns.publish(TopicArn=topicarn, Subject=subject, Message=sns_msg)
def check_input_ready(data): inputs = {} try: inputs["source_sf_ud"] = data["paths"]["clipper_sftp_sf_ud"] inputs["source_sys_cd"] = data["paths"]["clipper_sftp_sys_cd"] except Exception as e: log_msg, sns_msg = util.get_log_template(dag=dag, task=task, status=sC.FAILED, level=lC.CRITICAL, error=repr(e), remarks=None, reason=eC.CONFIG_ERROR, exit_code=1, sns=True) logger.error(log_msg) sns.publish(TopicArn=topicarn, Subject=subject, Message=sns_msg) sys.exit(1) try: inputs["host"] = data['sftp']['host'] inputs["user"] = data['sftp']['user'] inputs["sshk"] = data['sftp']['sshk'] return inputs except Exception as e: log_msg, sns_msg = util.get_log_template(dag=dag, task=task, status=sC.FAILED, level=lC.CRITICAL, error=repr(e), remarks=None, reason=eC.NO_SFTP_CREDENTIALS, exit_code=1, sns=True) logger.error(log_msg) sns.publish(TopicArn=topicarn, Subject=subject, Message=sns_msg) sys.exit(1)
def get_sftp_connection(host, user, SSHK): try: sshcon = paramiko.SSHClient() # will create the object sshcon.set_missing_host_key_policy( paramiko.AutoAddPolicy()) # no known_hosts error sshcon.connect(host, username=user, key_filename=SSHK) # no passwd needed logger.info('SUCCEEDED:CONNECTION to SFTP') return sshcon except Exception as e: log_msg, sns_msg = util.get_log_template(dag=dag, task=task, status=sC.FAILED, level=lC.CRITICAL, error=repr(e), remarks=None, reason=eC.SFTP_CONN_ERROR, exit_code=1, sns=True) logger.error(log_msg) sns.publish(TopicArn=topicarn, Subject=subject, Message=sns_msg) sys.exit(1)
def main(): inputs = check_input_ready(data) copy_cmd_ud = ('aws s3 cp %s %s --recursive --exclude "newfiles"') % ( inputs["source_sf_ud"], destination_sf_ud) copy_cmd_cd = ('aws s3 cp %s %s --recursive --exclude "newfiles"') % ( inputs["source_sys_cd"], destination_sys_cd) list_file_cmd_ud = ('ls {}').format(inputs["source_sf_ud"]) list_file_cmd_cd = ('ls {}').format(inputs["source_sys_cd"]) sshcon = get_sftp_connection(inputs["host"], inputs["user"], inputs["sshk"]) try: stdin, stdout, stderr = sshcon.exec_command(list_file_cmd_ud) stdin, stdout, stderr = sshcon.exec_command(list_file_cmd_cd) output = stdout.read().decode('ascii') input_files = parse_bash_out(output) msg = util.get_log_template(dag=dag, task=task, status=sC.RUNNING, level=lC.INFO, remarks='Bash List cmd Executed') logger.info(msg) except Exception as e: log_msg, sns_msg = util.get_log_template( dag=dag, task=task, status=sC.FAILED, level=lC.CRITICAL, error=repr(e), remarks='SFTP file listing failed', reason=eC.BASH_ERROR, exit_code=1, sns=True) logger.error(log_msg) sns.publish(TopicArn=topicarn, Subject=subject, Message=sns_msg) sys.exit(1) if len(input_files) < 1: log_msg, sns_msg = util.get_log_template(dag=dag, task=task, status=sC.FINISHED, level=lC.WARNING, reason=eC.NO_FILES_FOUND, remarks='No Files to Process', exit_code=1, sns=True) logger.warning(log_msg) sns.publish(TopicArn=topicarn, Subject=subject, Message=sns_msg) sys.exit(1) else: try: logger.info(msg) stdin, stdout, stderr = sshcon.exec_command(copy_cmd_ud) stdin, stdout, stderr = sshcon.exec_command(copy_cmd_cd) output = stdout.read() stderr_out = stderr.read() if len(stderr_out) == 0: log_msg, sns_msg = util.get_log_template( dag=dag, task=task, status=sC.FINISHED, level=lC.INFO, remarks='SUCCESS-COPY SFTP to S3', sns=True) sns.publish(TopicArn=topicarn, Subject=subject, Message=sns_msg) else: log_msg, sns_msg = util.get_log_template( dag=dag, task=task, status=sC.FAILED, level=lC.CRITICAL, error=repr(stderr_out), remarks='Copy Command sftp to s3 failed', reason=eC.S3_COPY_FAILED, exit_code=1, sns=True) logger.error(log_msg) sns.publish(TopicArn=topicarn, Subject=subject, Message=sns_msg) sys.exit(1) except Exception as e: log_msg, sns_msg = util.get_log_template( dag=dag, task=task, status=sC.FAILED, level=lC.CRITICAL, error=repr(e), remarks='Copy Command sftp to s3 failed', reason=eC.S3_COPY_FAILED, exit_code=1, sns=True) logger.error(log_msg) sns.publish(TopicArn=topicarn, Subject=subject, Message=sns_msg) sys.exit(1) log_msg = util.get_log_template(dag=dag, task=task, status=sC.FINISHED, level=lC.INFO) logger.info(log_msg)
def main(): all_files = [] try: # print(incoming_xls_prefix) all_files = util.get_s3_keys_by_prefix(bucket, incoming_xls_prefix) # print(all_files) except Exception as e: log_msg, sns_msg = util.get_log_template( dag=dag, task=task, status=sC.FAILED, level=lC.ERROR, error=repr(e), remarks='In S3 Key fetch - all_files', reason=eC.S3_CONNECTION_ERROR, sns=True) logger.error(log_msg) sns.publish(TopicArn=topicarn, Subject=subject, Message=sns_msg) sys.exit(1) if len(all_files) > 1: try: dno_dist_files = get_dno_dist_files(all_files) dno_cub_files = get_dno_cub_files(all_files) except Exception as e: log_msg, sns_msg = util.get_log_template( dag=dag, task=task, status=sC.FAILED, level=lC.WARNING, remarks='ERROR IN getting cub/district file', exit_code=1, reason=eC.ERROR_IN_PARSING_FILENAME, sns=True) logger.error(log_msg) sns.publish(TopicArn=topicarn, Subject=subject, Message=sns_msg) sys.exit(1) try: # print(dno_cub_files) useful_files = dno_cub_files + dno_dist_files # print("useful",useful_files) bad_files = set(all_files) - set(useful_files) util.delete_file_list(bucket, list(bad_files)) except Exception as e: log_msg, sns_msg = util.get_log_template( dag=dag, task=task, status=sC.FAILED, level=lC.WARNING, remarks='ERROR in removing unwanted files', reason=eC.S3_DELETE_FAILED, sns=True) logger.error(log_msg) sns.publish(TopicArn=topicarn, Subject=subject, Message=sns_msg) sys.exit(1) if len(dno_dist_files) < 1: log_msg, sns_msg = util.get_log_template( dag=dag, task=task, status=sC.RUNNING, level=lC.CRITICAL, error=repr(e), remarks= 'DNO District Files to be processed missing in incoming', reason=eC.NO_FILES_FOUND, sns=True) logger.error(log_msg) sns.publish(TopicArn=topicarn, Subject=subject, Message=sns_msg) else: try: process_files_district(dno_dist_files) pass except Exception as e: log_msg, sns_msg = util.get_log_template( dag=dag, task=task, status=sC.RUNNING, level=lC.CRITICAL, error=repr(e), remarks='ERROR in process_files_district', reason=eC.CSV_PROCESSING_FAILED, sns=True) logger.error(log_msg) sns.publish(TopicArn=topicarn, Subject=subject, Message=sns_msg) if len(dno_cub_files) < 1: log_msg, sns_msg = util.get_log_template( dag=dag, task=task, status=sC.RUNNING, level=lC.CRITICAL, error=repr(e), remarks='DNO CUB Files to be processed missing in incoming', reason=eC.NO_FILES_FOUND, sns=True) logger.error(log_msg) sns.publish(TopicArn=topicarn, Subject=subject, Message=sns_msg) else: try: if len(cub_date_selection) > 0: year = cub_date_selection[0]['year'] month = cub_date_selection[0]['month'] for file in dno_cub_files: if str(year) in file: process_dno_cub([file], month) else: print("processing dno_cub_files") process_dno_cub(dno_cub_files) except Exception as e: log_msg, sns_msg = util.get_log_template( dag=dag, task=task, status=sC.RUNNING, level=lC.CRITICAL, error=repr(e), remarks='ERROR in process_dno_cub', reason=eC.CSV_PROCESSING_FAILED, sns=True) logger.error(log_msg) sns.publish(TopicArn=topicarn, Subject=subject, Message=sns_msg) if len(failed_files) > 0: print("moving_failed_files") move_failed_files(failed_files) else: pass else: log_msg, sns_msg = util.get_log_template( dag=dag, task=task, status=sC.FINISHED, level=lC.WARNING, remarks='Files to be processed missing in incoming', reason=eC.NO_FILES_FOUND, sns=True) logger.error(log_msg) sns.publish(TopicArn=topicarn, Subject=subject, Message=sns_msg)
def parse_sheets(sheet_dict): cols_of_interest = [ "Unnamed: 0", "Unnamed: 1", "Unnamed: 2", "Unnamed: 3", "Unnamed: 4", "Unnamed: 5", "Unnamed: 6", "Unnamed: 7", "Unnamed: 8", "Unnamed: 9" ] new_dic = {} dno_dnc_cols = [ "date", "run", "schedule_number", "dno_miles", "dnc_miles", "dno", "dnc", "prob_location" ] for key in sheet_dict.keys(): try: if sheet_dict[key].empty or key == "Sept 2017": log_msg, sns_msg = util.get_log_template( dag=dag, task=task, status=sC.RUNNING, level=lC.WARNING, remarks='Empty Sheet for ' + key, reason=eC.CSV_PROCESSING_FAILED, sns=True) logger.warning(log_msg) sns.publish(TopicArn=topicarn, Subject=subject, Message=sns_msg) else: # print(key) # print(sheet_dict[key].columns) df = sheet_dict[key] col_map = get_col_map(df) new_df = df.rename(columns=col_map) # print("new cols",new_df.columns) # print("dno cols",dno_dnc_cols) if "prob_location" not in col_map.keys(): dno_dnc_col = [ "date", "run", "schedule_number", "dno_miles", "dnc_miles", "dno", "dnc" ] new_df = new_df[dno_dnc_col] else: new_df = new_df[dno_dnc_cols] final_df = new_df[new_df['date'].notna()] final_df = final_df[final_df['date'].apply( lambda x: isinstance( dateparser.parse(str(x), settings={'STRICT_PARSING': True}), datetime.datetime))] final_df["source"] = "CUB" final_df['route'] = final_df.apply( lambda row: str(row.schedule_number)[:3] if len(str(row.schedule_number)) > 3 else " ", axis=1) final_df['miles'] = final_df.apply(lambda row: row.dnc_miles if row.dnc == 1 else (row.dno_miles if row.dno == 1 else " "), axis=1) final_df['dno'] = final_df.apply(lambda row: True if row.dno == 1 else False, axis=1) final_df['dnc'] = final_df.apply(lambda row: True if row.dnc == 1 else False, axis=1) # print(final_df[["dno",'date']]) date = final_df['date'].iloc[0] new_dic[date] = final_df except Exception as e: log_msg, sns_msg = util.get_log_template( dag=dag, task=task, status=sC.FAILED, level=lC.ERROR, error=repr(e), remarks='Sheet parsing failed for CUB: ' + key, reason=eC.INVALID_FILE, sns=True) logger.critical(log_msg) sns.publish(TopicArn=topicarn, Subject=subject, Message=sns_msg) return new_dic