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)