def insertErrorLog(subtaskid, erroMessage): """ Method to create error log entry for sub task Args: 1. integer : accpets subtask name as first argument for which error log entry needs to be created 2. String : accepts error as second argument for respective sub task which needs to be created. this will allow to track all the errors occured during data extraction Returns: No return variable. it raise exception which is logged """ errorLoginsertquery = ( '''insert into birepusr.etl_error_log (audit_details_id,error_log,create_date) values({audit_details_id},'{errormessage}',now()) RETURNING error_log_id''' ).format(audit_details_id=subtaskid, errormessage=str(erroMessage).replace('\'', '')) executeQueryReturnId(errorLoginsertquery) subtask_name = executeQueryAndReturnDF( '''select sub_task_name from birepusr.etl_audit_details where audit_details_id={0};''' .format(subtaskid)) taskName = subtask_name.loc[0][0] updateSubTask(subtaskid, "FAILED") updateMainTask(maintaskid, "FAILED", "JIRA ETL") print('''Exception thrown by JIRA ETL : {0}'''.format(erroMessage)) send_completion_mail(erroMessage, "FAILED", taskName) raise Exception(erroMessage)
def getdbProjects(): """ getter method for existing projects in warehouse table idw.jira_projects_dim Args: No Arguments Returns: returns pandas dataframe with list of jira projects in idw.jira_projects_dim """ return executeQueryAndReturnDF(dq.get_db_projects)
def getMaxEpicID(project_dim_id): """ getter method for pulling epics from JIRA for given project Args: String: Project key for which method will pull all epics (only jan'2020 onwards) Returns: Dataframe: returns pandas dataframe with list of epics for given project """ maxEpicId = executeQueryAndReturnDF( dq.get_max_epic_id.format(project_dim_id)) return int(maxEpicId['epic_id'])
def initPutSprintSummary(): """ this method loop's through all the active teams in datawarehouse and insert sprint summary in idw.sprint_summary table Args: No Arguments Returns: No Return """ subtaskid = createSubTask("insert sprint summary in datawarehouse table idw.jira_sprint_summary", getMaintaskId()) try: team_rapid_board = executeQueryAndReturnDF(dq.get_max_sprint) team_rapid_board.apply(lambda teams: putSprintSummary(getSprints(int(teams['sprint_number']), int(teams['jira_rapid_view_id']), int(teams['team_dim_id']))), axis=1) updateSubTask(subtaskid, "SUCCESS") return team_rapid_board except (Exception) as error: insertErrorLog(subtaskid, error)
def upsertJiraProduct(): """ Execute the database insert and update query to insert new product or update existing ones based on response form jira rest api Args: No Arguments Returns: No return variable """ subtaskid = createSubTask("upsert service desk products in jira_product map table", getMaintaskId()) try: MyJira = Jira(**jira_api) df_products = MyJira.getServiceDeskProducts() df_product_dim = executeQueryAndReturnDF("select jira_product_name as product_name, jira_product_api_id as api_id from idw.jira_product_dim") comparison_df = df_products.merge(df_product_dim, indicator=True, on=['api_id'], how='outer') comparison_df[comparison_df['_merge'] == 'left_only'].apply(lambda product: executeQuery(insert_new_product.format(product['product_name_x'], product['api_id'])), axis=1) comparison_df = comparison_df[comparison_df['_merge'] == 'both'].query('product_name_x != product_name_y') comparison_df.apply(lambda product: executeQuery(update_product_name.format(product['product_name_x'], product['api_id'])), axis=1) updateSubTask(subtaskid, "SUCCESS") except (Exception) as error: insertErrorLog(subtaskid, error)
def initProjectEpicRequest(): """ this method get list of distinct projects worked on by team and initiate database insert for list of epics returned by getProjectEpic() Args: No Arguments Returns: No Return """ subtaskid = createSubTask( "pull epics worked on by project from JIRA and initiate data insert in idw.epic_dim", getMaintaskId()) try: team_projects = executeQueryAndReturnDF(dq.get_team_projects) team_projects.apply(lambda project: insertEpics( getProjectEpic(project['project_key'], int(project['project_dim_id'])), int(project['project_dim_id']), int(project['product_dim_id'])), axis=1) updateSubTask(subtaskid, "SUCCESS") except (Exception) as error: insertErrorLog(subtaskid, error)
def initSprintIssues(): """ this method loop's through all the active sprints for which issues are not pulled in datawarehouse, call getter method to get sprint issues and insert them in warehouse Args: No Arguments Returns: No Return """ subtaskid = createSubTask( "initialize insertion of sprint issues in datawarehouse", getMaintaskId()) try: team_sprints = executeQueryAndReturnDF(dq.get_sprints) team_sprints.apply(lambda sprint: insertSprintIssues( getSprintIssues(int(sprint['sprint_number']), subtaskid), int(sprint['sprint_number']), int(sprint['team_dim_id'])), axis=1) updateSubTask(subtaskid, "SUCCESS") except (Exception) as error: insertErrorLog(subtaskid, error)