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 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 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 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 getLastsuccessfulExtarctionDate(): """ Method to get last successful data extraction date. This date is used to get new jira incidents created after last successful data insert This allows to pull jira incidents incrementally Args: No arguments Returns: returns start datetime for last successful data extraction audit log """ subtaskid = createSubTask("get last successful data extraction date", maintaskid) conn = dbConnection() try: cur = conn.cursor() cur.execute( "SELECT MAX(start_date) from birepusr.etl_audit WHERE status='SUCCESS' and product_name='JIRA';" ) fromDate = cur.fetchone() updateSubTask(subtaskid, "SUCCESS") return fromDate[0] except (Exception, psycopg2.Error) as error: insertErrorLog(subtaskid, error) finally: # closing database connection. if (conn): cur.close() conn.close()
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 test_case_origin_id_compare(self): conn = dbConnection() cur = conn.cursor() cur1 = conn.cursor() cur.execute("select jira_origin_dim_id from idw.jira_sd_fact where jira_key = 'SERVICDESK-10021'") cur1.execute("select jira_origin_dim_id FROM mscrm.incident_entity cs LEFT JOIN idw.jira_origin_dim od ON COALESCE(cs.caseorigincodename,'None')= od.jira_origin_name where ticketnumber= 'CAS-07276-J4Q4G0'") fact_id_origin = cur.fetchall()[0] source_id_origin = cur1.fetchall()[0] self.assertEqual(source_id_origin, fact_id_origin)
def test_number_of_devs(self): # check number of developers conn = dbConnection() cur = conn.cursor() cur.execute( "select number_of_devs from idw.jira_sprint_summary where sprint_number=2506" ) rows = cur.fetchall() summary = round(rows[0][0]) cur.execute( "select count(u.user_dim_id) as no_of_devs from idw.jira_sprint_summary ss inner join idw.user_team_map u on ss.team_dim_id=u.team_dim_id and u.team_entry_date < ss.sprint_start_date and coalesce(u.team_exit_date, now()) > ss.sprint_end_date where ss.sprint_number=2506 group by u.team_dim_id, ss.sprint_number order by u.team_dim_id, ss.sprint_number" ) row1 = cur.fetchall() issues = round(row1[0][0]) self.assertEqual(summary, issues)
def test_jira_api_contact_compare(self): MyJira1 = Jira(**jira_api) # Condition JIRA API JQL to fetch the required issues Condition_string1 = f"project='SERVICDESK' and key='SERVICDESK-20310'" List_var1 = MyJira1.getIssues(condition=Condition_string1) list_DataFrame1 = MyJira1.create_df(List_var1) jira_con = (list_DataFrame1['crm_contact']) jira_contact = str(jira_con[0]) conn = dbConnection() cur = conn.cursor() cur.execute( "select b.contactid from idw.jira_sd_fact a inner join idw.contact_dim b on a.contact_dim_id=b.contact_dim_id where a.jira_key='SERVICDESK-20310' and a.is_active=true" ) rows = cur.fetchall() fact_contact = str(rows[0][0]) self.assertEqual(jira_contact, fact_contact)
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 test_jira_api_dao_compare(self): s_date = datetime(2020, 4, 8) jira_date = str(s_date) st_date = s_date - timedelta(hours=5, minutes=0) st = str(st_date.strftime('%Y-%m-%d %H:%M')) en_date = s_date + timedelta(hours=24, minutes=0) en = str(en_date.strftime('%Y-%m-%d')) MyJira1 = Jira(**jira_api) # Condition JIRA API JQL to fetch the required issues Condition_string1 = f"project='SERVICDESK' and created >= '" + st + "' and created < '" + en + "'" List_var1 = MyJira1.getIssues(condition=Condition_string1) list_DataFrame1 = MyJira1.create_df(List_var1) conn = dbConnection() cur = conn.cursor() cur.execute( "select count(*) from idw.jira_sd_fact a left outer join idw.date_dimension b on a.date_created_dim_id=b.sk_date where a.is_active='true' and b.dt='" + jira_date + "'") rows = cur.fetchall() self.assertEqual(list_DataFrame1.shape[0], rows[0][0])
def test_sprint_completed_points(self): # check sprint completed non maintenance points conn = dbConnection() cur = conn.cursor() cur.execute("""select non_maintenance_feature_points from idw.jira_sprint_summary where sprint_number=2448""") rows = cur.fetchall() summary = round(rows[0][0]) cur.execute("""select sum(points) as completed_points from (select sprint_number, team_dim_id, epic_dim_id, (ticket_details -> 'fields' ->> 'resolutiondate')::timestamp as resolutionDate, (ticket_details -> 'fields' ->> 'customfield_10411')::numeric as points from idw.sprint_issues where is_active=true and sprint_number=2448 and is_maintenance=false) s inner join idw.jira_sprint_summary e on s.sprint_number=e.sprint_number and s.team_dim_id=e.team_dim_id where resolutionDate between sprint_start_date and sprint_end_date""") row1 = cur.fetchall() issues = round(row1[0][0]) self.assertEqual(summary, issues)
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 test_data_extractor_dao(self): # connect to postgresql (w/ psycopg2) conn = dbConnection() self.assertIsNotNone(conn) conn.close()