def insertProjects():
"""
db insert method for persisting new projects in warehouse table idw.jira_projects_dim
Args:
No Arguments
Returns:
No return
"""
subtaskid = createSubTask(
"pull projects from JIRA and insert in warehouse table idw.jira_project_dim",
getMaintaskId())
try:
db_projects = getdbProjects()
jira_projects = getProjects()
jira_projects["project_id"] = jira_projects["project_id"].astype(int)
comparison_df = jira_projects.merge(db_projects,
indicator=True,
how='outer',
on='project_id')
comparison_df[comparison_df['_merge'] == 'left_only'].apply(
lambda project: executeQuery(
dq.insert_projects.format(project['project_id'], project[
'project_key'], project['project_name'])),
axis=1)
updateSubTask(subtaskid, "SUCCESS")
except (Exception) as error:
insertErrorLog(subtaskid, error)
def generateFacttable():
""" Execute the Fact table queries
Args:
No Arguments
Returns:
No return variable
"""
subtaskid = createSubTask("fact table generation and measure calculation",
getMaintaskId())
conn = dbConnection()
try:
cur = conn.cursor()
cur.execute(fact_table_update)
cur.execute(fact_table_insert)
cur.execute(fact_table_map_update_date)
cur.execute(fact_table_map_resolution_date)
cur.execute(create_guid_mapping_status)
cur.execute(update_contact_guid_mapping)
cur.execute(fact_table_update_test_incidents_as_inactive)
cur.execute(fact_table_update_BenE_incidents_as_inactive)
cur.execute(update_case_origin_fact)
conn.commit()
updateSubTask(subtaskid, "SUCCESS")
except (Exception, psycopg2.Error) as error:
insertErrorLog(subtaskid, error)
finally:
# closing database connection.
if (conn):
cur.close()
conn.close()
def getJiraTickets():
""" Provides condition for jira API JQL to fetch JIRA issue list
Conditions that can be used for like : DURING (startOfWeek(-1w), endOfWeek(-1w)) and created >= '2020-01-01' AND status='Open and AND created >= -28d
Args:
No Arguments
Returns:
Jsin_Dataframe(dataframe) : Pandas Dataframe with required fields
"""
subtaskid = createSubTask("pull service desk incidents from JIRA API", getMaintaskId())
try:
MyJira = Jira(**jira_api)
fromDate = getLastsuccessfulExtarctionDate()
# Condition JIRA API JQL to fetch the required issues
Condition_string = f"project='" + jira_project + "' "
if fromDate is not None:
Condition_string = Condition_string + "AND (created >'" + fromDate.strftime(
"%Y-%m-%d") + "' OR Updated > '" + fromDate.strftime("%Y-%m-%d") + "')"
JSON_List = MyJira.getIssues(condition=Condition_string)
Json_DataFrame = MyJira.create_df(JSON_List)
# Returning the pandas dataframe with JIRA issues
updateSubTask(subtaskid, "SUCCESS")
return Json_DataFrame
except (Exception, psycopg2.Error) as error:
insertErrorLog(subtaskid, error)
def sanity_check_counts():
""" Execute the sanity check queries and report the failed cases
Args:
No Arguments
Returns:
No return Arguments
"""
subtaskid = createSubTask("sanity check queries", getMaintaskId())
try:
conn = dbConnection()
cur = conn.cursor()
cur.execute("select query, name, expected_value from birepusr.etl_data_sanity_queries where project_name='servicedesk'")
rows = cur.fetchall()
for row in rows:
conn = dbConnection()
cur = conn.cursor()
cur.execute(row[0])
x = cur.fetchall()
if x == []:
actual_value = 0
else:
actual_value = x[0][0]
condition = row[1]
expected_value = row[2]
status = 'Success'
if not expected_value == actual_value:
status = 'Fail'
message = '''Expected vs Actual values not matching for check '{0}': expected {1} rows but found {2}'''.format(condition, expected_value, actual_value)
send_custom_mail('Service-Desk ETL : DATA SANITY TEST FAILED', message)
executeQuery('''insert into birepusr.etl_data_sanity_test_results(condition, created_date, expected_value, actual_value, status, task_id, project_name) values ('{0}', now(), {1}, {2}, '{3}', '{4}', 'servicedesk')'''.format(condition, expected_value, actual_value, status, subtaskid))
updateSubTask(subtaskid, "SUCCESS")
except (Exception, psycopg2.Error) as error:
insertErrorLog(subtaskid, error)
def insertEpics(df_epics, project_dim_id, product_dim_id):
"""
this method perform database insert operation in idw.epic_dim,
it insert epics returned by getProjectEpic() for given project
Args:
dataFrame: first argument is pandas dataframe which contain list of epics for given project
project_dim_id: second argument is project dim id for which epics will be isnerted in idw.epic_dim
product_dim_id: product dim id to which epics belong
Returns:
No Return
"""
subtaskid = createSubTask(
"insert epic worked on by project in warehouse table in idw.epic_dim",
getMaintaskId())
try:
df_epics.apply(lambda epic: executeQuery((dq.insert_epics.format(
re.sub('[^A-Za-z0-9 ]+', '', str(epic['epic_name'])), epic[
'epic_id'], epic['epic_key'], epic['aha_score'], epic[
'is_closed'], epic['date_created'], None
if epic['date_updated'] is None else "'" + epic['date_updated'] +
"'", None
if epic['date_resolved'] is None else "'" + epic['date_resolved'] +
"'", project_dim_id, "'" + epic['aha_reference'] + "'")).replace(
'None', 'null').replace('nan', 'null')),
axis=1)
updateSubTask(subtaskid, "SUCCESS")
except (Exception) as error:
insertErrorLog(subtaskid, error)
def stageJiraTickets():
""" Insert JIRA issue data into staging db schema
Args:
No Arguments
Returns:
No return variable
"""
subtaskid = createSubTask("extract JIRA data to staging", getMaintaskId())
try:
df = getJiraTickets()
executeQuery('DELETE FROM birepusr.jira_sd_stage;')
if not df.empty:
# Arranging the columns of pandas dataframe to insert into the DB
jiraData = df[['assignee', 'jira_case', 'components', 'created',
'creator', 'crm_account', 'crm_contact',
'description', 'fixVersions', 'issuetype', 'key',
'priority', 'reporter', 'resolution',
'resolution_date', 'status_description',
'status_name', 'subtask', 'summary', 'updated',
'versions', 'watches', 'Reporter_Email', 'channel',
'product_name', 'request_type', 'assignee_name']]
engine = dbConnection()
cur = engine.cursor()
# Truncating the existing staging data
output = io.StringIO()
jiraData.to_csv(output, sep='\t', header=True, index=False)
output.seek(0)
copy_query = "COPY birepusr.jira_sd_stage FROM STDOUT csv DELIMITER '\t' NULL '' ESCAPE '\\' HEADER "
# inserting data into the database
cur.copy_expert(copy_query, output)
engine.commit()
updateSubTask(subtaskid, "SUCCESS")
except (Exception, psycopg2.Error) as error:
insertErrorLog(subtaskid, error)
def insertSprintIssues(df_sprint_issues, sprint_number, team_dim_id):
"""
this method perform database insert operation in idw.sprint_issues,
it insert issues completed in a sprint by given team
Args:
dataFrame: first argument is pandas dataframe which contain list of issues for given sprint for respective team
sprint_number: second argument sprint number to map issues in destination table
team_dim_id: team dim id to which sprint and issues belong
Returns:
No Return
"""
subtaskid = createSubTask(
"insert sprint issues in datawarehouse staging table birepusr.stage_sprint_issues for sprint {0}"
.format(sprint_number), getMaintaskId())
try:
df_sprint_issues.apply(lambda sprint: executeQuery(
dq.insert_issues_in_stage.format(
team_dim_id, sprint_number, sprint['jira_ticket_id'], sprint[
'jira_ticket_key'],
str(sprint['ticket_details']).replace("\'", ""))),
axis=1)
executeQuery(
dq.update_issue_load_status.format(sprint_number, team_dim_id))
updateSubTask(subtaskid, "SUCCESS")
except (Exception) as error:
insertErrorLog(subtaskid, error)
def getProjects():
"""
getter method for jira projects with id and key
Args:
No Arguments
Returns:
returns pandas dataframe with list of jira projects
"""
col_names = ['project_id', 'project_key', 'project_name']
jira_projects = pd.DataFrame(columns=col_names)
subtaskid = createSubTask("generate feature list for a product",
getMaintaskId())
try:
response = requests.get(jira_project_request, auth=(jira_auth))
print(response)
projects = json.loads(response.text)
for project in projects:
jira_projects = jira_projects.append(
{
'project_id': project['id'],
'project_key': project['key'],
'project_name': project['name']
},
ignore_index=True)
updateSubTask(subtaskid, "SUCCESS")
except (Exception) as error:
insertErrorLog(subtaskid, error)
return jira_projects.drop_duplicates()
def getProjectEpic(projectKey, 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
"""
col_names = [
'epic_name', 'epic_id', 'epic_key', 'aha_score', 'is_closed',
'date_created', 'date_updated', 'date_resolved', 'aha_reference'
]
epics = pd.DataFrame(columns=col_names)
subtaskid = createSubTask("pull epics worked on by project from JIRA",
getMaintaskId())
try:
max_epic_id = getMaxEpicID(project_dim_id)
jira_epics_req = jira_epic_request.replace(
'@@project_name@@',
'{0}'.format(projectKey)).replace('@@epic_id@@',
'{0}'.format(max_epic_id))
response = requests.get(jira_epics_req, auth=(jira_auth))
jira_epics = json.loads(response.text)
for epic in jira_epics['issues']:
is_closed = False
if not epic['fields']['resolutiondate'] is None:
is_closed = True
epics = epics.append(
{
'epic_name':
epic['fields']['customfield_10416'],
'epic_id':
epic['id'],
'epic_key':
epic['key'],
'aha_score':
epic['fields']['customfield_12741'],
'is_closed':
is_closed,
'date_created':
epic['fields']['created'],
'date_updated':
epic['fields']['updated'],
'date_resolved':
epic['fields']['resolutiondate'],
'aha_reference':
'{0}'.format(epic['fields']['customfield_12740'])
},
ignore_index=True)
updateSubTask(subtaskid, "SUCCESS")
except (Exception) as error:
insertErrorLog(subtaskid, error)
return epics
def init_codgs_etl():
#createMainTask("Cost of Good sold Data Extraction", 'COGS')
insertProjects()
executeStaticQueries()
updateProductId()
initPutSprintSummary()
initSprintIssues()
generate_mapper()
initProjectEpicRequest()
updateEpicDimId()
backupStaging(getMaintaskId())
def generateJiraWorkflowMap():
"""
Execute the database insert to add jira transition map for each workflow status per jira key
Args:
No Arguments
Returns:
No return variable
"""
subtaskid = createSubTask("generate workflow mapping for each active jira key", getMaintaskId())
returnVal = executeQuery(insert_jira_workflow_map)
updateSubTask(subtaskid, "SUCCESS") if returnVal == 'SUCCESS' else insertErrorLog(subtaskid, returnVal)
def putSprintSummary(sprints_for_team):
"""
This method insert sprint summary for team in idw.sprint_summary table
Args:
Dataframe: pandas dataframe with all the latest sprints for team
Returns:
No Return
"""
subtaskid = createSubTask("initialize database insert of sprint summary", getMaintaskId())
try:
sprints_for_team.apply(lambda sprint: executeQuery(dq.insert_sprints.format(sprint['sprint_serial_id'], sprint['sprint_number'], sprint['sprint_name'], sprint['team_dim_id'], sprint['sprint_start_date'], sprint['sprint_end_date'], sprint['sprint_complete_date'])), axis=1)
updateSubTask(subtaskid, "SUCCESS")
except (Exception) as error:
insertErrorLog(subtaskid, error)
def generateAssigneeDimension():
"""
This method get list of valid jira assignee and upsert them in
idw.jira_user_dim
Args:
No Arguments
Returns:
No return variable
"""
subtaskid = createSubTask("Upsert jira users into idw.jira_user_dim ",
getMaintaskId())
engine = dbConnection()
try:
MyJira = Jira(**jira_api)
jira_assignee = MyJira.getJiraUsers()
jira_assignee = jira_assignee.drop_duplicates(['key'], keep='first')
cur = engine.cursor()
cur.execute('''
CREATE temp TABLE jira_assignee
(
user_id VARCHAR(100),
user_name VARCHAR(100),
email_id VARCHAR(100),
active boolean
)
''')
output = io.StringIO()
jira_assignee.to_csv(output, sep='\t', header=True, index=False)
output.seek(0)
copy_query = "COPY jira_assignee FROM STDOUT csv DELIMITER '\t' NULL '' ESCAPE '\\' HEADER "
# inserting data into the database
cur.copy_expert(copy_query, output)
cur.execute(upsert_assigne)
engine.commit()
cur.execute(insert_missing_assignee_from_api)
engine.commit()
updateSubTask(subtaskid, "SUCCESS")
except (Exception, psycopg2.Error) as error:
insertErrorLog(subtaskid, error)
finally:
# closing database connection.
if (engine.commit()):
engine.commit().close()
def updateProductId():
"""
update product dim id using product name matching with project name
Args:
No Arguments
Returns:
No return
"""
subtaskid = createSubTask(
"update product id for projects in idw.jira_project_dim",
getMaintaskId())
try:
executeQuery(dq.update_product_dim_id_for_project)
updateSubTask(subtaskid, "SUCCESS")
except (Exception) as error:
insertErrorLog(subtaskid, error)
def handler(event, context):
createMainTask("JIRA data extraction", "JIRA")
stageJiraTickets()
executeQuery(backup_raw_data)
generateContactDim()
updateRequestTypeDim()
updateDimCaseOrigin()
upsertJiraProduct()
getworkflowStatus()
generateAssigneeDimension()
generateFacttable()
persistJiraProductMap()
initSecondaryFactGenerator()
user_department_mapping()
executeQuery(remove_duplicate_transition_log)
sanity_check_counts()
updateMainTask(getMaintaskId(), "SUCCESS", "JIRA data extraction")
def updateEpicDimId():
"""
this method update epic_dim_id in idw.sprint_issues
Args:
No argument
Returns:
No Return
"""
subtaskid = createSubTask("update epic_dim_id in idw.sprint_issues",
getMaintaskId())
try:
executeQuery(dq.update_epic_dim_id)
updateSubTask(subtaskid, "SUCCESS")
except (Exception) as error:
insertErrorLog(subtaskid, error)
def updateDimCaseOrigin():
"""
Execute the database insert and update query to populate
missing case origin identified from source table in mscrm
Args:
No Arguments
Returns:
No return variable
"""
subtaskid = createSubTask(
"add missing case origin from source data into idw.jira_origin_dim ",
getMaintaskId())
returnVal = executeQuery(load_dim_case_origin)
updateSubTask(subtaskid,
"SUCCESS") if returnVal == 'SUCCESS' else insertErrorLog(
subtaskid, returnVal)
def generate_mapper():
"""
This method inserts data into the fact tables and map project and epic dim id
Args:
No Arguments
Returns:
No return variable
"""
subtaskid = createSubTask("pull epics worked on by project from JIRA",
getMaintaskId())
try:
executeQuery(insert_sprint_issues)
executeQuery(update_issue_status_on_sprint_closure)
updateSubTask(subtaskid, "SUCCESS")
except (Exception) as error:
insertErrorLog(subtaskid, error)
def user_department_mapping():
""" Map department ID to user, fact and transition map table
Args:
No Arguments
Returns:
No return variable
"""
subtaskid = createSubTask(
"Map department ID to user, fact and transition map table",
getMaintaskId())
try:
mapping = pd.read_csv('user_department_mapping.csv')
conn = dbConnection()
cur = conn.cursor()
cur.execute('''
CREATE TEMP TABLE mapping
(
displayName VARCHAR(200),
SamAccountName VARCHAR(100),
Department VARCHAR(150)
)
''')
output = io.StringIO()
mapping.to_csv(output, sep='|', header=True, index=False)
output.seek(0)
copy_query = "COPY mapping FROM STDOUT csv DELIMITER '|' NULL '' ESCAPE '\\' HEADER "
# inserting data into the database
cur.copy_expert(copy_query, output)
cur.execute(department_dim_insert)
cur.execute(department_mapping_in_user)
conn.commit()
cur.execute(update_department_id_in_fact)
cur.execute(department_mapping_in_transition)
conn.commit()
updateSubTask(subtaskid, "SUCCESS")
except (Exception, psycopg2.Error) 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 getSprints(maxSprintId, rapidBoardId, teamId):
"""
getter method for jira sprints for given team board
Args:
Integer: sprint board identifire to pull respective sprint details using jira api
Integer: team dim id to add reference in datawarehousetable while inserting sprint summary
Returns:
Dataframe: returns pandas dataframe with list of jira projects
"""
# creaet data frame to hold sprint details
col_names = ['sprint_serial_id', 'sprint_number', 'sprint_name', 'team_dim_id', 'sprint_start_date', 'sprint_end_date', 'sprint_complete_date']
sprints = pd.DataFrame(columns=col_names)
subtaskid = createSubTask("pull all latest sprints for SE teams from JIRA", getMaintaskId())
try:
# pull sprints for give rapid board id only
jira_sprint_req = jira_sprint_request.replace('@@rapid_board_id@@', '{0}'.format(rapidBoardId))
is_last = False
startAt = maxSprintId
sprint_serial_id = maxSprintId
# iterate through sprints till all the sprints are pulled
while(not is_last):
response = requests.get(jira_sprint_req + "&startAt={0}".format(startAt), auth=(jira_auth))
jira_sprints = json.loads(response.text)
is_last = jira_sprints['isLast']
for sprint in jira_sprints['values']:
startAt = startAt + 1
sprint_serial_id = sprint_serial_id + 1
if sprint['originBoardId'] == rapidBoardId:
sprints = sprints.append({'sprint_serial_id': sprint_serial_id, 'sprint_number': sprint['id'], 'sprint_name': sprint['name'], 'team_dim_id': teamId, 'sprint_start_date': sprint['startDate'], 'sprint_end_date': sprint['endDate'], 'sprint_complete_date': sprint['completeDate']}, ignore_index=True)
updateSubTask(subtaskid, "SUCCESS")
except (Exception) as error:
insertErrorLog(subtaskid, error)
return sprints.drop_duplicates()
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)
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 updateMeasuresInTransitionMap():
"""
This method initiate multiple update statements to update time_spent measure for status and assignee
It also insert missing assignee for those jira which never went trough assignee transition
It update resolution date for all jira in transition map
Args:
No Arguments
Returns:
No return variable
"""
subtaskid = createSubTask("calculate and update measures in transition map table", getMaintaskId())
returnVal = executeQuery(update_resolution_date_intransition_map)
returnVal = executeQuery(insert_unchanged_assignee_in_transition_map) if returnVal == 'SUCCESS' else insertErrorLog(subtaskid, returnVal)
returnVal = executeQuery(workflow_transition_time + " " + update_workflow_transition_time + " " + workflow_assignee_transition_time + " " + update_assignee_transition_time + " " + update_status_change_date_dim_id) if returnVal == 'SUCCESS' else insertErrorLog(subtaskid, returnVal)
updateSubTask(subtaskid, "SUCCESS") if returnVal == 'SUCCESS' else insertErrorLog(subtaskid, returnVal)
def updateIsCurrentFlag():
"""
Update is current workflow status and assignee flag for each jira key in secondary map
Args:
No Arguments
Returns:
No return variable
"""
subtaskid = createSubTask("update is current workflow status and assignee flag for each jira key in secondary map", getMaintaskId())
returnVal = executeQuery(update_is_current_status_flag + " " + update_is_current_assignee_flag)
updateSubTask(subtaskid, "SUCCESS") if returnVal == 'SUCCESS' else insertErrorLog(subtaskid, returnVal)
def getworkflowStatus():
subtaskid = createSubTask("get list of valid workflow status for SERVICDESK project from jira api", getMaintaskId())
MyJira = Jira(**jira_api)
df_workflow_status = MyJira.getworkflow()
df_resolution_status = MyJira.getworkflowResolution()
df_workflow_status = df_workflow_status.append(df_resolution_status, ignore_index=True)
engine = dbConnection()
cur = engine.cursor()
cur.execute('''
CREATE temp TABLE workflow_status
(
status VARCHAR(80),
id integer,
flag BOOLEAN
)
''')
output = io.StringIO()
df_workflow_status.to_csv(output, sep='\t', header=True, index=False)
output.seek(0)
copy_query = "COPY workflow_status FROM STDOUT csv DELIMITER '\t' NULL '' ESCAPE '\\' HEADER "
# inserting data into the database
cur.copy_expert(copy_query, output)
try:
cur.execute(workflow_status_dim_insert)
engine.commit()
updateSubTask(subtaskid, "SUCCESS")
except (Exception, psycopg2.Error) as error:
insertErrorLog(subtaskid, error)
finally:
# closing database connection.
if(engine.commit()):
engine.commit().close()
def persistJiraProductMap():
"""
get list of products tagged to a jira key and persist in jira_product map table
so that user can generate time_to_close metric for each product
Args:
No Arguments
Returns:
No return variable
"""
subtaskid = createSubTask("calculate and update time to close measures in jira_product map table", getMaintaskId())
returnVal = executeQuery(insert_jira_product_map)
updateSubTask(subtaskid, "SUCCESS") if returnVal == 'SUCCESS' else insertErrorLog(subtaskid, returnVal)
def updateWorkflowTransitionMap():
"""
update worflow transition status for each jira_key-workflow status mapping
in secondary fact table i.e idw.jira_sd_workflow_transition_map
This function select only active and open jira tickets in fact table
Args:
No Arguments
Returns:
No return variable
"""
subtaskid = createSubTask("update workflow transition log for each active and open jira key", getMaintaskId())
conn = dbConnection()
try:
cur = conn.cursor()
global df_status_ids, df_assignee_ids
df_status_ids = pd.read_sql_query(getValidWorkflowStatus, con=dbEngine())
df_status_ids = df_status_ids.append({'jira_status_dim_id': 0, 'jira_status': 'None', 'jira_api_id': 0}, ignore_index=True)
df_assignee_ids = pd.read_sql_query(getValidWorkflowAssignee, con=dbEngine())
df = pd.read_sql_query(getActiveandOpenJiraTickets, con=dbEngine())
if not df.empty:
MyJira = Jira(**jira_api)
df.apply(lambda jira_key: updateTransitionLog(MyJira.getChangeLog(jira_key[0]), cur), axis=1)
conn.commit()
updateSubTask(subtaskid, "SUCCESS")
except (Exception, psycopg2.Error) as error:
insertErrorLog(subtaskid, error)
finally:
# closing database connection.
if(conn):
cur.close()
conn.close()
def generateContactDim():
"""
Execute the database insert and update query to populate
contacts from mscrm.contact_entity table,
Args:
No Arguments
Returns:
No return variable
"""
subtaskid = createSubTask("copy data from mscrm.contact_entity to idw.contact_dim", getMaintaskId())
returnVal = executeQuery(contact_dim_insert + " " + contact_dim_update)
updateSubTask(subtaskid, "SUCCESS") if returnVal == 'SUCCESS' else insertErrorLog(subtaskid, returnVal)
