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()