def get_asmt_rec_id(guid_batch, tenant_name, asmt_rec_id_info):
'''
Returns asmt_rec_id from dim_asmt table
Steps:
1. Get guid_asmt from integration table INT_SBAC_ASMT
2. Select asmt_rec_id from dim_asmt by the same guid_amst got from 1. It should have 1 value
'''
source_table_name = asmt_rec_id_info['source_table']
guid_column_name_in_source = asmt_rec_id_info['guid_column_in_source']
target_table_name = asmt_rec_id_info['target_table']
guid_column_name_in_target = asmt_rec_id_info['guid_column_name']
rec_id_column_name = asmt_rec_id_info['rec_id']
# connect to integration table, to get the value of guid_asmt
with get_udl_connection() as udl_conn:
int_table = udl_conn.get_table(source_table_name)
query = select([int_table.c[guid_column_name_in_source]], from_obj=int_table, limit=1)
query = query.where(int_table.c['guid_batch'] == guid_batch)
results = udl_conn.get_result(query)
if results:
guid_column_value = results[0][guid_column_name_in_source]
# connect to target table, to get the value of asmt_rec_id
with get_target_connection(tenant_name, guid_batch) as target_conn:
dim_asmt = target_conn.get_table(target_table_name)
query = select([dim_asmt.c[rec_id_column_name]], from_obj=dim_asmt, limit=1)
query = query.where(dim_asmt.c[guid_column_name_in_target] == guid_column_value)
query = query.where(and_(dim_asmt.c['batch_guid'] == guid_batch))
results = target_conn.get_result(query)
if results:
asmt_rec_id = results[0][rec_id_column_name]
return asmt_rec_id
def get_edware_asmt_score_avgs(self, tenant, schema):
with get_target_connection(tenant, schema) as conn:
fact = conn.get_table('fact_asmt_outcome_vw')
query = select([
func.avg(fact.c.asmt_score),
func.avg(fact.c.asmt_score_range_min),
func.avg(fact.c.asmt_score_range_max),
func.avg(fact.c.asmt_claim_1_score),
func.avg(fact.c.asmt_claim_1_score_range_min),
func.avg(fact.c.asmt_claim_1_score_range_max),
func.avg(fact.c.asmt_claim_2_score),
func.avg(fact.c.asmt_claim_2_score_range_min),
func.avg(fact.c.asmt_claim_2_score_range_max),
func.avg(fact.c.asmt_claim_3_score),
func.avg(fact.c.asmt_claim_3_score_range_min),
func.avg(fact.c.asmt_claim_3_score_range_max),
func.avg(fact.c.asmt_claim_4_score),
func.avg(fact.c.asmt_claim_4_score_range_min),
func.avg(fact.c.asmt_claim_4_score_range_max)
],
from_obj=fact)
result = conn.execute(query)
for row in result:
star_asmt_avgs = row
return star_asmt_avgs
def handle_duplicates_in_dimensions(tenant_name, guid_batch):
"""Handle duplicate records in dimensions by marking them as deleted
Steps:
1. Soft delete records (Mark rec_status as 'S') in pre-prod dimensions that are already existing in production database
The match is done based on natural_key columns of the table and some other key columns listed
2. Update the rec_id of the record marked for delete with the id of the matching record found in prod. This is needed for
step which updates foreign keys in fact asmt outcome
:param tenant_name: tenant name, to get target database connection
:param guid_batch: batch buid
@return: Number of affected rows
"""
affected_rows = 0
with get_target_connection(tenant_name,
guid_batch) as target_conn, get_prod_connection(
tenant_name) as prod_conn:
tables = get_tables_starting_with(target_conn.get_metadata(),
Constants.DIM_TABLES_PREFIX)
for table_name in tables:
target_db_helper = HandleUpsertHelper(target_conn, guid_batch,
table_name)
prod_db_helper = HandleUpsertHelper(prod_conn, guid_batch,
table_name)
for record in target_db_helper.find_all():
matched = prod_db_helper.find_by_natural_key(record)
if not matched:
continue
# soft delete the record and set its pk as the pk of the matched record
target_db_helper.soft_delete_and_update(record, matched)
affected_rows += 1
return affected_rows
def validate_udl_stats(self):
with StatsDBConnection() as conn:
table = conn.get_table('udl_stats')
query = select([table.c.load_status])
result = conn.execute(query).fetchall()
expected_result = [('migrate.ingested', ), ('migrate.failed', )]
# Validate that one migration batch is failed and one is successful.
self.assertEquals(result, expected_result)
# Validate that schema clean up is successful for both the scenario: Migration.ingested and migration.failed
schema_name = select([table.c.batch_guid])
schema_name_result = conn.execute(schema_name).fetchall()
tpl_schema_name_1 = schema_name_result[0]
tpl_schema_name_2 = schema_name_result[1]
schema_name_1 = tpl_schema_name_1[0]
schema_name_2 = tpl_schema_name_2[0]
with get_target_connection(self.tenant) as connector:
query = select(['schema_name'],
from_obj=['information_schema.schemata'
]).where('schema_name in :a')
params = [bindparam('a', (schema_name_1, schema_name_2))]
new_query = text(str(query), bindparams=params)
result = connector.execute(new_query).fetchall()
self.assertEqual(
len(result), 0,
"Schema clean up after migration is unsuccessful")
def explode_data_to_fact_table(conf, source_table, target_table,
column_mapping, column_types):
'''
Main function to explode data from integration table INT_SBAC_ASMT_OUTCOME to star schema table fact_asmt_outcome_vw
The basic steps are:
0. Get two foreign keys: asmt_rec_id and student_rec_id
1. Insert data from INT_SBAC_ASMT_OUTCOME to fact_asmt_outcome_vw. But for columns inst_hier_rec_id and student_rec_id ,
put the temporary value as -1
2. Update foreign key inst_hier_rec_id by comparing district_id, school_id and state_code
3. Update foreign key student_rec_id by comparing student_id, batch_guid
'''
with get_target_connection(conf[mk.TENANT_NAME],
conf[mk.TARGET_DB_SCHEMA]) as conn:
# Drops FK
conn.execute(
enable_trigger_query(conf[mk.TARGET_DB_SCHEMA], target_table,
False))
# create insertion insert_into_fact_table_query
result = conn.execute(
create_insert_query(conf, source_table, target_table,
column_mapping, column_types, False))
affected_rows = result.rowcount
# update fact table's foreign key columns' id
fact_table = conn.get_table(target_table)
for fk in edschema_util.get_foreign_key_reference_columns(fact_table):
for query in update_foreign_rec_id_query(fk):
result = conn.execute(query)
return affected_rows
def explode_data_to_fact_table(conf, source_table, target_table, column_mapping, column_types):
'''
Main function to explode data from integration table INT_SBAC_ASMT_OUTCOME to star schema table fact_asmt_outcome_vw
The basic steps are:
0. Get two foreign keys: asmt_rec_id and student_rec_id
1. Insert data from INT_SBAC_ASMT_OUTCOME to fact_asmt_outcome_vw. But for columns inst_hier_rec_id and student_rec_id ,
put the temporary value as -1
2. Update foreign key inst_hier_rec_id by comparing district_id, school_id and state_code
3. Update foreign key student_rec_id by comparing student_id, batch_guid
'''
with get_target_connection(conf[mk.TENANT_NAME], conf[mk.TARGET_DB_SCHEMA]) as conn:
# Drops FK
conn.execute(enable_trigger_query(conf[mk.TARGET_DB_SCHEMA], target_table, False))
# create insertion insert_into_fact_table_query
result = conn.execute(create_insert_query(conf, source_table, target_table, column_mapping, column_types, False))
affected_rows = result.rowcount
# update fact table's foreign key columns' id
fact_table = conn.get_table(target_table)
for fk in edschema_util.get_foreign_key_reference_columns(fact_table):
for query in update_foreign_rec_id_query(fk):
result = conn.execute(query)
return affected_rows
def explode_data_to_dim_table(conf, source_table, target_table, column_mapping,
column_types):
'''
Main function to move data from source table to target tables.
Source table can be INT_SBAC_ASMT, and INT_SBAC_ASMT_OUTCOME. Target table can be any dim tables in star schema.
@param conf: one dictionary which has database settings, and guid_batch
@param source_table: name of the source table where the data comes from
@param target_table: name of the target table where the data should be moved to
@param column_mapping: list of tuple of:
column_name_in_target, column_name_in_source
@param column_types: data types of all columns in one target table
'''
# create database connection to target
with get_target_connection(conf[mk.TENANT_NAME],
conf[mk.GUID_BATCH]) as conn:
# create insertion query
# TODO: find out if the affected rows, time can be returned, so that the returned info can be put in the log
# send only data that is needed to be inserted (such insert, update) to dimenstion table
query = create_insert_query(
conf, source_table, target_table, column_mapping, column_types,
True, 'C' if source_table in op_table_conf else None)
logger.info(compile_query_to_sql_text(query))
# execute the query
affected_rows = execute_udl_queries(
conn, [query],
'Exception -- exploding data from integration to target ' +
'{target_table}'.format(target_table=target_table),
'move_to_target', 'explode_data_to_dim_table')
return affected_rows
def explode_data_to_dim_table(conf, source_table, target_table, column_mapping, column_types):
'''
Main function to move data from source table to target tables.
Source table can be INT_SBAC_ASMT, and INT_SBAC_ASMT_OUTCOME. Target table can be any dim tables in star schema.
@param conf: one dictionary which has database settings, and guid_batch
@param source_table: name of the source table where the data comes from
@param target_table: name of the target table where the data should be moved to
@param column_mapping: list of tuple of:
column_name_in_target, column_name_in_source
@param column_types: data types of all columns in one target table
'''
# create database connection to target
with get_target_connection(conf[mk.TENANT_NAME], conf[mk.GUID_BATCH]) as conn:
# create insertion query
# TODO: find out if the affected rows, time can be returned, so that the returned info can be put in the log
# send only data that is needed to be inserted (such insert, update) to dimenstion table
query = create_insert_query(conf, source_table, target_table, column_mapping, column_types, True,
'C' if source_table in op_table_conf else None)
logger.info(compile_query_to_sql_text(query))
# execute the query
affected_rows = execute_udl_queries(conn, [query],
'Exception -- exploding data from integration to target ' +
'{target_table}'.format(target_table=target_table),
'move_to_target', 'explode_data_to_dim_table')
return affected_rows
def handle_duplicates_in_dimensions(tenant_name, guid_batch):
"""Handle duplicate records in dimensions by marking them as deleted
Steps:
1. Soft delete records (Mark rec_status as 'S') in pre-prod dimensions that are already existing in production database
The match is done based on natural_key columns of the table and some other key columns listed
2. Update the rec_id of the record marked for delete with the id of the matching record found in prod. This is needed for
step which updates foreign keys in fact asmt outcome
:param tenant_name: tenant name, to get target database connection
:param guid_batch: batch buid
@return: Number of affected rows
"""
affected_rows = 0
with get_target_connection(tenant_name, guid_batch) as target_conn, get_prod_connection(tenant_name) as prod_conn:
tables = get_tables_starting_with(target_conn.get_metadata(), Constants.DIM_TABLES_PREFIX)
for table_name in tables:
target_db_helper = HandleUpsertHelper(target_conn, guid_batch, table_name)
prod_db_helper = HandleUpsertHelper(prod_conn, guid_batch, table_name)
for record in target_db_helper.find_all():
matched = prod_db_helper.find_by_natural_key(record)
if not matched:
continue
# soft delete the record and set its pk as the pk of the matched record
target_db_helper.soft_delete_and_update(record, matched)
affected_rows += 1
return affected_rows
def move_data_from_int_tables_to_target_table(conf, task_name, source_tables,
target_table):
"""Move student registration data from source integration tables to target table.
Source tables are INT_STU_REG and INT_STU_REG_META. Target table is student_registration.
@param conf: Configuration for particular load type (assessment or studentregistration)
@param task_name: Name of the celery task invoking this method
@param source_tables: Names of the source tables from where the data comes
@param target_table: Name of the target table to where the data should be moved
@return: Number of inserted rows
"""
column_and_type_mapping = get_column_and_type_mapping(
conf, task_name, target_table, source_tables)
with get_target_connection(conf[mk.TENANT_NAME],
conf[mk.GUID_BATCH]) as conn_to_target_db:
insert_query = create_sr_table_select_insert_query(
conf, target_table, column_and_type_mapping)
logger.info(insert_query)
affected_rows = execute_udl_queries(
conn_to_target_db, [insert_query],
'Exception -- moving data from integration {int_table} to target {target_table}'
.format(int_table=source_tables[0], target_table=target_table),
'move_to_target', 'move_data_from_int_tables_to_target_table')
return affected_rows
def get_star_schema_demographic_counts(self, tenant, schema):
demographics = [
'dmg_eth_hsp', 'dmg_eth_ami', 'dmg_eth_asn', 'dmg_eth_blk',
'dmg_eth_pcf', 'dmg_eth_wht', 'dmg_eth_2om', 'dmg_prg_iep',
'dmg_prg_lep', 'dmg_prg_504', 'dmg_sts_ecd', 'dmg_sts_mig'
]
results_dict = {}
with get_target_connection(tenant, schema) as conn:
fact = conn.get_table('fact_asmt_outcome_vw')
for entry in demographics:
demo_query = select(
[func.count(fact.c[entry])],
from_obj=fact).where(fact.c[entry] == true())
result = conn.execute(demo_query)
for row in result:
demo_count = row[0]
results_dict[entry] = demo_count
#get derived ethnicity
eth_query = select(
[func.count(fact.c.dmg_eth_derived)],
from_obj=fact).where(fact.c.dmg_eth_derived is not None)
result = conn.execute(eth_query)
for row in result:
derived_count = row[0]
results_dict['dmg_eth_derived'] = derived_count
return results_dict
def test_1_load_int_to_star(self):
self.load_int_sbac_asmt()
self.load_int_sbac_asmt_outcome()
# explode to dim tables
table_map, column_map = move_to_target_setup.get_table_and_column_mapping(
self.conf, self.load_to_dim_task_name, self.dim_table_prefix)
for target in table_map.keys():
target_columns = column_map[target]
column_types = move_to_target_setup.get_table_column_types(
self.conf, target, list(target_columns.keys()))
move_to_target.explode_data_to_dim_table(self.conf,
table_map[target], target,
target_columns,
column_types)
# explode to fact table
table_map, column_map = move_to_target_setup.get_table_and_column_mapping(
self.conf, self.load_to_fact_task_name, self.fact_table_prefix)
for fact_table, source_table in table_map.items():
column_types = move_to_target_setup.get_table_column_types(
self.conf, fact_table, list(column_map[fact_table].keys()))
move_to_target.explode_data_to_fact_table(self.conf, source_table,
fact_table,
column_map[fact_table],
column_types)
# handle deletion case
move_to_target.handle_updates_and_deletes(self.conf)
# check star schema table counts
with get_target_connection(self.tenant_code, self.guid_batch) as conn:
tables_to_check = {
'dim_asmt': 1,
'dim_inst_hier': 99,
'dim_student': 94,
'fact_asmt_outcome_vw': 71
}
for entry in tables_to_check.keys():
table = conn.get_table(entry)
query = select([count()], from_obj=table)
result = conn.execute(query)
#self.assertEqual(int(result.fetchall()[0][0]), tables_to_check[entry])
# check asmt score avgs
int_asmt_avgs = self.get_integration_asmt_score_avgs()
star_asmt_avgs = self.get_edware_asmt_score_avgs(
self.tenant_code, self.guid_batch)
self.assertEqual(int_asmt_avgs, star_asmt_avgs)
# check demographic counts
int_demo_dict = self.get_integration_demographic_counts()
star_demo_dict = self.get_star_schema_demographic_counts(
self.tenant_code, self.guid_batch)
self.assertEqual(int_demo_dict, star_demo_dict)
def clean_up_unused_schemas():
with get_target_connection("cat") as conn:
schemas = conn.execute(
"select schema_name from information_schema.schemata")
for schema in schemas:
schema_name = schema[0]
if re.match('^\w{8}-\w{4}-\w{4}-\w{4}-\w{12}$', schema_name):
drop_schema_by_name(conn, schema_name)
def test_update_pre_prod_for_records_to_be_deleted(self):
with get_target_connection(MatchAndDeleteFTest.tenant_code, MatchAndDeleteFTest.guid_batch) as target_conn, get_prod_connection(MatchAndDeleteFTest.tenant_code) as prod_conn:
records_to_be_deleted = move_to_target.get_records_marked_for_deletion(self.conf, target_conn, 'fact_asmt_outcome_vw')
proxy_rows = move_to_target.yield_records_to_be_deleted(prod_conn, 'fact_asmt_outcome_vw', records_to_be_deleted, batch_size=10)
for rows in proxy_rows:
move_to_target.update_rec_id_for_records_to_delete(self.conf, target_conn, 'fact_asmt_outcome_vw', rows)
mismatches = move_to_target.get_records_marked_for_deletion(self.conf, target_conn, 'fact_asmt_outcome_vw')
self.assertEqual(0, len(mismatches))
def test_match_all_records_to_be_deleted_with_prod(self):
with get_target_connection(MatchAndDeleteFTest.tenant_code, MatchAndDeleteFTest.guid_batch) as target_conn, get_prod_connection(MatchAndDeleteFTest.tenant_code) as prod_conn:
records_to_be_deleted = move_to_target.get_records_marked_for_deletion(self.conf, target_conn, 'fact_asmt_outcome_vw')
proxy_rows = move_to_target.yield_records_to_be_deleted(prod_conn, 'fact_asmt_outcome_vw', records_to_be_deleted, batch_size=10)
result = []
for rows in proxy_rows:
result.extend(rows)
self.assertEqual(4, len(result))
def count_rows(self, status=None):
with get_target_connection(MatchAndDeleteFTest.tenant_code, MatchAndDeleteFTest.guid_batch) as conn:
fact = conn.get_table('fact_asmt_outcome_vw')
query = select([count(fact.c.asmt_outcome_vw_rec_id).label('count')], from_obj=fact)
if status:
query = query.where(fact.c.rec_status == status)
result = conn.get_result(query)
return int(result[0]['count'])
def validate_edware_database(self, schema_name):
with get_target_connection(self.tenant, schema_name) as ed_connector:
fact_table = ed_connector.get_table('fact_asmt_outcome_vw')
pre_prod_data = select([
fact_table.c.student_rec_id, fact_table.c.inst_hier_rec_id,
fact_table.c.asmt_rec_id
])
pre_prod_table = ed_connector.execute(pre_prod_data).fetchall()
self.expected_pk_val_for_student = pre_prod_table[0][0]
self.expected_pk_val_for_inst = pre_prod_table[0][1]
self.expected_pk_val_for_asmt = pre_prod_table[0][2]
lean_table = ed_connector.get_table('fact_asmt_outcome')
pre_prod_data = select([
lean_table.c.student_rec_id, fact_table.c.inst_hier_rec_id,
fact_table.c.asmt_rec_id
])
pre_prod_table = ed_connector.execute(pre_prod_data).fetchall()
self.lean_pk_val_for_student = pre_prod_table[0][0]
self.lean_pk_val_for_inst = pre_prod_table[0][1]
self.lean_pk_val_for_asmt = pre_prod_table[0][2]
dim_student = ed_connector.get_table('dim_student')
dim_student_data = select([dim_student.c.student_rec_id])
result_student_id = ed_connector.execute(
dim_student_data).fetchall()
self.expected_student_id = result_student_id[0][0]
dim_inst_hier = ed_connector.get_table('dim_inst_hier')
dim_inst_hier_data = select([dim_inst_hier.c.inst_hier_rec_id])
dim_inst_hier_result = ed_connector.execute(
dim_inst_hier_data).fetchall()
self.expected_inst_hier_rec_id = dim_inst_hier_result[0][0]
dim_asmt = ed_connector.get_table('dim_asmt')
dim_asmt_data = select([dim_asmt.c.asmt_rec_id])
dim_asmt_result = ed_connector.execute(dim_asmt_data).fetchall()
self.expected_asmt_rec_id = dim_asmt_result[0][0]
self.assertEqual(
self.expected_student_id, self.expected_pk_val_for_student,
"Error:student_rec_id of dim table not maching to fact table")
self.assertEqual(
self.expected_inst_hier_rec_id, self.expected_pk_val_for_inst,
"Error:student_rec_id of dim table not maching to fact table")
self.assertEqual(
self.expected_asmt_rec_id, self.expected_pk_val_for_asmt,
"Error:student_rec_id of dim table not maching to fact table")
self.assertEqual(
self.expected_student_id, self.lean_pk_val_for_student,
"Error:student_rec_id of dim table not maching to fact table")
self.assertEqual(
self.expected_inst_hier_rec_id, self.lean_pk_val_for_inst,
"Error:student_rec_id of dim table not maching to fact table")
self.assertEqual(
self.expected_asmt_rec_id, self.lean_pk_val_for_asmt,
"Error:student_rec_id of dim table not maching to fact table")
def test_get_records_for_deletion(self):
self.load_int_to_star()
self.assertEqual(4, self.count_rows())
with get_target_connection(
MatchAndDeleteFTest.tenant_code,
MatchAndDeleteFTest.guid_batch) as target_conn:
records_to_be_deleted = move_to_target.get_records_marked_for_deletion(
self.conf, target_conn, 'fact_asmt_outcome_vw')
self.assertEqual(4, len(records_to_be_deleted))
def validate_edware_database(self, schema_name):
with get_target_connection('cat') as ed_connector:
ed_connector.set_metadata_by_reflect(schema_name)
edware_table = ed_connector.get_table(FACT_TABLE)
output = select([edware_table.c.batch_guid]).where(edware_table.c.batch_guid == self.batch_id)
output_data = ed_connector.execute(output).fetchall()
row_count = len(output_data)
self.assertGreater(row_count, 1, "Data is loaded to star shema")
truple_str = (self.batch_id, )
self.assertIn(truple_str, output_data, "assert successful")
def count_rows(self, status=None):
with get_target_connection(MatchAndDeleteFTest.tenant_code,
MatchAndDeleteFTest.guid_batch) as conn:
fact = conn.get_table('fact_asmt_outcome_vw')
query = select(
[count(fact.c.asmt_outcome_vw_rec_id).label('count')],
from_obj=fact)
if status:
query = query.where(fact.c.rec_status == status)
result = conn.get_result(query)
return int(result[0]['count'])
def validate_edware_database(self, schema_name):
with get_target_connection(self.tenant, schema_name) as ed_connector:
fact_table_vw = ed_connector.get_table('fact_asmt_outcome_vw')
prod_output_data = select([fact_table_vw.c.rec_status]).where(fact_table_vw.c.student_id == '3efe8485-9c16-4381-ab78-692353104cce', )
prod_output_table = ed_connector.execute(prod_output_data).fetchall()
expected_status_val_D = [('D',)]
self.assertEquals(prod_output_table, expected_status_val_D, 'Status is wrong in fact table for delete record')
fact_table = ed_connector.get_table('fact_asmt_outcome')
prod_data = select([fact_table.c.rec_status]).where(fact_table.c.student_id == '3efe8485-9c16-4381-ab78-692353104cce', )
prod_table = ed_connector.execute(prod_data).fetchall()
self.assertEquals(prod_table, expected_status_val_D, 'Status is wrong in fact_asmt_table_primary for delete record')
def test_1_load_int_to_star(self):
self.load_int_sbac_asmt()
self.load_int_sbac_asmt_outcome()
# explode to dim tables
table_map, column_map = move_to_target_setup.get_table_and_column_mapping(self.conf,
self.load_to_dim_task_name,
self.dim_table_prefix)
for target in table_map.keys():
target_columns = column_map[target]
column_types = move_to_target_setup.get_table_column_types(self.conf, target, list(target_columns.keys()))
move_to_target.explode_data_to_dim_table(self.conf, table_map[target], target, target_columns, column_types)
# explode to fact table
table_map, column_map = move_to_target_setup.get_table_and_column_mapping(self.conf,
self.load_to_fact_task_name,
self.fact_table_prefix)
for fact_table, source_table in table_map.items():
column_types = move_to_target_setup.get_table_column_types(self.conf,
fact_table,
list(column_map[fact_table].keys()))
move_to_target.explode_data_to_fact_table(self.conf,
source_table,
fact_table,
column_map[fact_table],
column_types)
# handle deletion case
move_to_target.handle_updates_and_deletes(self.conf)
# check star schema table counts
with get_target_connection(self.tenant_code, self.guid_batch) as conn:
tables_to_check = {'dim_asmt': 1, 'dim_inst_hier': 99, 'dim_student': 94, 'fact_asmt_outcome_vw': 71}
for entry in tables_to_check.keys():
table = conn.get_table(entry)
query = select([count()], from_obj=table)
result = conn.execute(query)
#self.assertEqual(int(result.fetchall()[0][0]), tables_to_check[entry])
# check asmt score avgs
int_asmt_avgs = self.get_integration_asmt_score_avgs()
star_asmt_avgs = self.get_edware_asmt_score_avgs(self.tenant_code, self.guid_batch)
self.assertEqual(int_asmt_avgs, star_asmt_avgs)
# check demographic counts
int_demo_dict = self.get_integration_demographic_counts()
star_demo_dict = self.get_star_schema_demographic_counts(self.tenant_code, self.guid_batch)
self.assertEqual(int_demo_dict, star_demo_dict)
def handle_updates_and_deletes(conf):
"""Main handler for updates and deletes
The method grabs tables to be processed for updates and deletes (Fact tables)
and runs process_records_to_be_deleted for each table
:param conf: udl configuration object
"""
with get_target_connection(conf[mk.TENANT_NAME], conf[mk.TARGET_DB_SCHEMA]) as target_conn, get_prod_connection(conf[mk.TENANT_NAME]) as prod_conn:
tables = get_tables_starting_with(target_conn.get_metadata(), Constants.FACT_TABLES_PREFIX)
for table_name in tables:
process_records_to_be_deleted(conf, target_conn, prod_conn, table_name)
def validate_edware_database(self):
with get_target_connection('cat', self.guid_batch_id) as ed_connector:
edware_table = ed_connector.get_table(FACT_TABLE)
output = select([edware_table.c.batch_guid]).where(edware_table.c.batch_guid == self.guid_batch_id)
output_val = select([edware_table.c.student_rec_id]).where(edware_table.c.batch_guid == self.guid_batch_id)
output_data = ed_connector.execute(output).fetchall()
output_data1 = ed_connector.execute(output_val).fetchall()
# Velidate that student_rec_id not containing -1
self.assertNotIn(-1, output_data1, "Student rec id in not -1 in fact_asmt")
# Velidate that data loaded into fact_Table after pipeline
row_count = len(output_data)
self.assertGreater(row_count, 1, "Data is loaded to star shema")
truple_str = (self.guid_batch_id, )
self.assertIn(truple_str, output_data, "assert successful")
def validate_prepod_tables(self, schema_name):
with get_target_connection(self.tenant, schema_name) as connection:
fact_table = connection.get_table('fact_asmt_outcome_vw')
dim_inst_hier = connection.get_table('dim_inst_hier')
dim_student = connection.get_table('dim_student')
dim_asmt = connection.get_table('dim_asmt')
dim_tables = [dim_asmt, dim_student, dim_inst_hier]
for table in dim_tables:
query = select([table.c.rec_status])
result = connection.execute(query).fetchall()
expected_status = [('S',)]
self.assertEqual(result, expected_status, "Error: In Dim_tables rec_status have not change to S")
fact_table_data = select([fact_table.c.rec_status])
fact_table_rows = connection.execute(fact_table_data).fetchall()
expected_status = [('C',)]
self.assertEquals(fact_table_rows, expected_status, "Error")
def get_counts(self, schema_name):
with get_target_connection(self.tenant_code, schema_name) as conn:
fact_vw_select = select([func.count()]).select_from(conn.get_table('fact_asmt_outcome_vw'))
fact_select = select([func.count()]).select_from(conn.get_table('fact_asmt_outcome'))
asmt_selct = select([func.count()]).select_from(conn.get_table('dim_asmt'))
inst_select = select([func.count()]).select_from(conn.get_table('dim_inst_hier'))
stud_select = select([func.count()]).select_from(conn.get_table('dim_student'))
sr_select = select([func.count()]).select_from(conn.get_table('student_reg'))
fact_count = conn.execute(fact_vw_select).fetchall()[0][0]
fact_primary_count = conn.execute(fact_select).fetchall()[0][0]
asmt_count = conn.execute(asmt_selct).fetchall()[0][0]
inst_count = conn.execute(inst_select).fetchall()[0][0]
stud_count = conn.execute(stud_select).fetchall()[0][0]
sr_count = conn.execute(sr_select).fetchall()[0][0]
return fact_count, fact_primary_count, asmt_count, inst_count, stud_count, sr_count
def get_table_column_types(conf, target_table, column_names):
'''
Main function to get column types of a table by querying the table
@return a dictionary, which has same ordered keys in the input column_names.
The values are column types with maximum length if it is defined in table.
The pattern of the value is: <column_name data_type(length)> or <column_name data_type>
'''
column_types = OrderedDict([(column_name, '') for column_name in column_names])
tenant = conf[mk.TENANT_NAME]
with get_target_connection(tenant, conf[mk.GUID_BATCH]) as conn:
table = conn.get_table(target_table)
for column in table.columns:
if column.name in column_types.keys():
column_types[column.name] = column.name + " " + str(column.type)
return column_types
def test_match_all_records_to_be_deleted_with_prod(self):
with get_target_connection(
MatchAndDeleteFTest.tenant_code, MatchAndDeleteFTest.guid_batch
) as target_conn, get_prod_connection(
MatchAndDeleteFTest.tenant_code) as prod_conn:
records_to_be_deleted = move_to_target.get_records_marked_for_deletion(
self.conf, target_conn, 'fact_asmt_outcome_vw')
proxy_rows = move_to_target.yield_records_to_be_deleted(
prod_conn,
'fact_asmt_outcome_vw',
records_to_be_deleted,
batch_size=10)
result = []
for rows in proxy_rows:
result.extend(rows)
self.assertEqual(4, len(result))
def test_update_pre_prod_for_records_to_be_deleted(self):
with get_target_connection(
MatchAndDeleteFTest.tenant_code, MatchAndDeleteFTest.guid_batch
) as target_conn, get_prod_connection(
MatchAndDeleteFTest.tenant_code) as prod_conn:
records_to_be_deleted = move_to_target.get_records_marked_for_deletion(
self.conf, target_conn, 'fact_asmt_outcome_vw')
proxy_rows = move_to_target.yield_records_to_be_deleted(
prod_conn,
'fact_asmt_outcome_vw',
records_to_be_deleted,
batch_size=10)
for rows in proxy_rows:
move_to_target.update_rec_id_for_records_to_delete(
self.conf, target_conn, 'fact_asmt_outcome_vw', rows)
mismatches = move_to_target.get_records_marked_for_deletion(
self.conf, target_conn, 'fact_asmt_outcome_vw')
self.assertEqual(0, len(mismatches))
def validate_prepod_tables(self, schema_name):
with get_target_connection(self.tenant, schema_name) as connection:
fact_table = connection.get_table('fact_asmt_outcome_vw')
dim_inst_hier = connection.get_table('dim_inst_hier')
dim_student = connection.get_table('dim_student')
dim_asmt = connection.get_table('dim_asmt')
dim_tables = [dim_asmt, dim_student, dim_inst_hier]
for table in dim_tables:
query = select([table.c.rec_status])
result = connection.execute(query).fetchall()
expected_status = [('S', )]
self.assertEqual(
result, expected_status,
"Error: In Dim_tables rec_status have not change to S")
fact_table_data = select([fact_table.c.rec_status])
fact_table_rows = connection.execute(fact_table_data).fetchall()
expected_status = [('C', )]
self.assertEquals(fact_table_rows, expected_status, "Error")
def handle_updates_and_deletes(conf):
"""Main handler for updates and deletes
The method grabs tables to be processed for updates and deletes (Fact tables)
and runs process_records_to_be_deleted for each table
:param conf: udl configuration object
"""
with get_target_connection(
conf[mk.TENANT_NAME],
conf[mk.TARGET_DB_SCHEMA]) as target_conn, get_prod_connection(
conf[mk.TENANT_NAME]) as prod_conn:
tables = get_tables_starting_with(target_conn.get_metadata(),
Constants.FACT_TABLES_PREFIX)
for table_name in tables:
process_records_to_be_deleted(conf, target_conn, prod_conn,
table_name)
def validate_edware_database(self):
with get_target_connection('cat', self.guid_batch_id) as ed_connector:
edware_table = ed_connector.get_table(FACT_TABLE)
output = select([
edware_table.c.batch_guid
]).where(edware_table.c.batch_guid == self.guid_batch_id)
output_val = select([
edware_table.c.student_rec_id
]).where(edware_table.c.batch_guid == self.guid_batch_id)
output_data = ed_connector.execute(output).fetchall()
output_data1 = ed_connector.execute(output_val).fetchall()
# Velidate that student_rec_id not containing -1
self.assertNotIn(-1, output_data1,
"Student rec id in not -1 in fact_asmt")
# Velidate that data loaded into fact_Table after pipeline
row_count = len(output_data)
self.assertGreater(row_count, 1, "Data is loaded to star shema")
truple_str = (self.guid_batch_id, )
self.assertIn(truple_str, output_data, "assert successful")
def validate_student_data(self, file_to_load):
with get_target_connection('cat', self.batch_id) as conn:
student = self.student_reg_files[file_to_load]['test_student']
target_table = conn.get_table(Constants.SR_TARGET_TABLE)
query = select([target_table.c.state_name, target_table.c.district_name, target_table.c.school_id,
target_table.c.sex, target_table.c.birthdate, target_table.c.dmg_eth_hsp,
target_table.c.dmg_prg_504, target_table.c.academic_year, target_table.c.reg_system_id],
and_(target_table.c.student_id == student['student_id'], target_table.c.batch_guid == self.batch_id))
result = conn.execute(query).fetchall()
student_data_tuple = result[0]
self.assertEquals(student_data_tuple[0], student['state_name_col'], 'State Name did not match')
self.assertEquals(student_data_tuple[1], student['district_name_col'], 'District Name did not match')
self.assertEquals(student_data_tuple[2], student['school_id_col'], 'School Id did not match')
self.assertEquals(student_data_tuple[3], student['sex_col'], 'Sex did not match')
self.assertEquals(student_data_tuple[4], datetime.strptime(student['dob_col'], "%Y-%m-%d").strftime("%Y%m%d"), 'Date of Birth did not match')
self.assertEquals(student_data_tuple[5], student['eth_hsp_col'], 'Hispanic Ethnicity should be true')
self.assertEquals(student_data_tuple[6], student['sec504_col'], 'Section504 status should be false')
self.assertEquals(student_data_tuple[7], student['year_col'], 'Academic Year did not match')
self.assertEquals(student_data_tuple[8], student['reg_sys_id_col'], 'Test registration system\'s id did not match')
def validate_udl_stats(self):
with StatsDBConnection() as conn:
table = conn.get_table('udl_stats')
query = select([table.c.load_status])
result = conn.execute(query).fetchall()
expected_result = [('migrate.ingested',), ('migrate.failed',)]
# Validate that one migration batch is failed and one is successful.
self.assertEquals(result, expected_result)
# Validate that schema clean up is successful for both the scenario: Migration.ingested and migration.failed
schema_name = select([table.c.batch_guid])
schema_name_result = conn.execute(schema_name).fetchall()
tpl_schema_name_1 = schema_name_result[0]
tpl_schema_name_2 = schema_name_result[1]
schema_name_1 = tpl_schema_name_1[0]
schema_name_2 = tpl_schema_name_2[0]
with get_target_connection(self.tenant) as connector:
query = select(['schema_name'], from_obj=['information_schema.schemata']).where('schema_name in :a')
params = [bindparam('a', (schema_name_1, schema_name_2))]
new_query = text(str(query), bindparams=params)
result = connector.execute(new_query).fetchall()
self.assertEqual(len(result), 0, "Schema clean up after migration is unsuccessful")
def validate_edware_database(self, schema_name):
with get_target_connection(self.tenant, schema_name) as ed_connector:
fact_table_vw = ed_connector.get_table('fact_asmt_outcome_vw')
prod_output_data = select([fact_table_vw.c.rec_status]).where(
fact_table_vw.c.student_id ==
'3efe8485-9c16-4381-ab78-692353104cce', )
prod_output_table = ed_connector.execute(
prod_output_data).fetchall()
expected_status_val_D = [('D', )]
self.assertEquals(
prod_output_table, expected_status_val_D,
'Status is wrong in fact table for delete record')
fact_table = ed_connector.get_table('fact_asmt_outcome')
prod_data = select([fact_table.c.rec_status]).where(
fact_table.c.student_id ==
'3efe8485-9c16-4381-ab78-692353104cce', )
prod_table = ed_connector.execute(prod_data).fetchall()
self.assertEquals(
prod_table, expected_status_val_D,
'Status is wrong in fact_asmt_table_primary for delete record')
def get_star_schema_demographic_counts(self, tenant, schema):
demographics = ['dmg_eth_hsp', 'dmg_eth_ami', 'dmg_eth_asn', 'dmg_eth_blk', 'dmg_eth_pcf', 'dmg_eth_wht', 'dmg_eth_2om',
'dmg_prg_iep', 'dmg_prg_lep', 'dmg_prg_504', 'dmg_sts_ecd', 'dmg_sts_mig']
results_dict = {}
with get_target_connection(tenant, schema) as conn:
fact = conn.get_table('fact_asmt_outcome_vw')
for entry in demographics:
demo_query = select([func.count(fact.c[entry])], from_obj=fact).where(fact.c[entry] == true())
result = conn.execute(demo_query)
for row in result:
demo_count = row[0]
results_dict[entry] = demo_count
#get derived ethnicity
eth_query = select([func.count(fact.c.dmg_eth_derived)], from_obj=fact).where(fact.c.dmg_eth_derived is not None)
result = conn.execute(eth_query)
for row in result:
derived_count = row[0]
results_dict['dmg_eth_derived'] = derived_count
return results_dict
def move_data_from_int_tables_to_target_table(conf, task_name, source_tables, target_table):
"""Move student registration data from source integration tables to target table.
Source tables are INT_STU_REG and INT_STU_REG_META. Target table is student_registration.
@param conf: Configuration for particular load type (assessment or studentregistration)
@param task_name: Name of the celery task invoking this method
@param source_tables: Names of the source tables from where the data comes
@param target_table: Name of the target table to where the data should be moved
@return: Number of inserted rows
"""
column_and_type_mapping = get_column_and_type_mapping(conf, task_name, target_table, source_tables)
with get_target_connection(conf[mk.TENANT_NAME], conf[mk.GUID_BATCH]) as conn_to_target_db:
insert_query = create_sr_table_select_insert_query(conf, target_table, column_and_type_mapping)
logger.info(insert_query)
affected_rows = execute_udl_queries(conn_to_target_db, [insert_query],
'Exception -- moving data from integration {int_table} to target {target_table}'
.format(int_table=source_tables[0], target_table=target_table),
'move_to_target', 'move_data_from_int_tables_to_target_table')
return affected_rows
def validate_edware_database(self, schema_name):
with get_target_connection(self.tenant, schema_name) as ed_connector:
fact_table = ed_connector.get_table('fact_asmt_outcome_vw')
pre_prod_data = select([fact_table.c.student_rec_id, fact_table.c.inst_hier_rec_id, fact_table.c.asmt_rec_id])
pre_prod_table = ed_connector.execute(pre_prod_data).fetchall()
self.expected_pk_val_for_student = pre_prod_table[0][0]
self.expected_pk_val_for_inst = pre_prod_table[0][1]
self.expected_pk_val_for_asmt = pre_prod_table[0][2]
lean_table = ed_connector.get_table('fact_asmt_outcome')
pre_prod_data = select([lean_table.c.student_rec_id, fact_table.c.inst_hier_rec_id, fact_table.c.asmt_rec_id])
pre_prod_table = ed_connector.execute(pre_prod_data).fetchall()
self.lean_pk_val_for_student = pre_prod_table[0][0]
self.lean_pk_val_for_inst = pre_prod_table[0][1]
self.lean_pk_val_for_asmt = pre_prod_table[0][2]
dim_student = ed_connector.get_table('dim_student')
dim_student_data = select([dim_student.c.student_rec_id])
result_student_id = ed_connector.execute(dim_student_data).fetchall()
self.expected_student_id = result_student_id[0][0]
dim_inst_hier = ed_connector.get_table('dim_inst_hier')
dim_inst_hier_data = select([dim_inst_hier.c.inst_hier_rec_id])
dim_inst_hier_result = ed_connector.execute(dim_inst_hier_data).fetchall()
self.expected_inst_hier_rec_id = dim_inst_hier_result[0][0]
dim_asmt = ed_connector.get_table('dim_asmt')
dim_asmt_data = select([dim_asmt.c.asmt_rec_id])
dim_asmt_result = ed_connector.execute(dim_asmt_data).fetchall()
self.expected_asmt_rec_id = dim_asmt_result[0][0]
self.assertEqual(self.expected_student_id, self.expected_pk_val_for_student, "Error:student_rec_id of dim table not maching to fact table")
self.assertEqual(self.expected_inst_hier_rec_id, self.expected_pk_val_for_inst, "Error:student_rec_id of dim table not maching to fact table")
self.assertEqual(self.expected_asmt_rec_id, self.expected_pk_val_for_asmt, "Error:student_rec_id of dim table not maching to fact table")
self.assertEqual(self.expected_student_id, self.lean_pk_val_for_student, "Error:student_rec_id of dim table not maching to fact table")
self.assertEqual(self.expected_inst_hier_rec_id, self.lean_pk_val_for_inst, "Error:student_rec_id of dim table not maching to fact table")
self.assertEqual(self.expected_asmt_rec_id, self.lean_pk_val_for_asmt, "Error:student_rec_id of dim table not maching to fact table")
def get_asmt_rec_id(guid_batch, tenant_name, asmt_rec_id_info):
'''
Returns asmt_rec_id from dim_asmt table
Steps:
1. Get guid_asmt from integration table INT_SBAC_ASMT
2. Select asmt_rec_id from dim_asmt by the same guid_amst got from 1. It should have 1 value
'''
source_table_name = asmt_rec_id_info['source_table']
guid_column_name_in_source = asmt_rec_id_info['guid_column_in_source']
target_table_name = asmt_rec_id_info['target_table']
guid_column_name_in_target = asmt_rec_id_info['guid_column_name']
rec_id_column_name = asmt_rec_id_info['rec_id']
# connect to integration table, to get the value of guid_asmt
with get_udl_connection() as udl_conn:
int_table = udl_conn.get_table(source_table_name)
query = select([int_table.c[guid_column_name_in_source]],
from_obj=int_table,
limit=1)
query = query.where(int_table.c['guid_batch'] == guid_batch)
results = udl_conn.get_result(query)
if results:
guid_column_value = results[0][guid_column_name_in_source]
# connect to target table, to get the value of asmt_rec_id
with get_target_connection(tenant_name, guid_batch) as target_conn:
dim_asmt = target_conn.get_table(target_table_name)
query = select([dim_asmt.c[rec_id_column_name]],
from_obj=dim_asmt,
limit=1)
query = query.where(
dim_asmt.c[guid_column_name_in_target] == guid_column_value)
query = query.where(and_(dim_asmt.c['batch_guid'] == guid_batch))
results = target_conn.get_result(query)
if results:
asmt_rec_id = results[0][rec_id_column_name]
return asmt_rec_id
def get_edware_asmt_score_avgs(self, tenant, schema):
with get_target_connection(tenant, schema) as conn:
fact = conn.get_table('fact_asmt_outcome_vw')
query = select([func.avg(fact.c.asmt_score),
func.avg(fact.c.asmt_score_range_min),
func.avg(fact.c.asmt_score_range_max),
func.avg(fact.c.asmt_claim_1_score),
func.avg(fact.c.asmt_claim_1_score_range_min),
func.avg(fact.c.asmt_claim_1_score_range_max),
func.avg(fact.c.asmt_claim_2_score),
func.avg(fact.c.asmt_claim_2_score_range_min),
func.avg(fact.c.asmt_claim_2_score_range_max),
func.avg(fact.c.asmt_claim_3_score),
func.avg(fact.c.asmt_claim_3_score_range_min),
func.avg(fact.c.asmt_claim_3_score_range_max),
func.avg(fact.c.asmt_claim_4_score),
func.avg(fact.c.asmt_claim_4_score_range_min),
func.avg(fact.c.asmt_claim_4_score_range_max)], from_obj=fact)
result = conn.execute(query)
for row in result:
star_asmt_avgs = row
return star_asmt_avgs
def validate_edware_database(self, schema_name):
with get_target_connection('cat', schema_name) as ed_connector:
fact_table = ed_connector.get_table('fact_asmt_outcome_vw')
delete_output_data = select([fact_table.c.rec_status]).where(fact_table.c.student_id == '3efe8485-9c16-4381-ab78-692353104cce')
delete_output_table = ed_connector.execute(delete_output_data).fetchall()
expected_status_val_D = [('D',)]
#verify delete record
self.assertEquals(delete_output_table, expected_status_val_D, 'Status is wrong in fact table for delete record')
#Verify Update record
update_output_data = select([fact_table.c.rec_status]).where(fact_table.c.student_id == '34b99412-fd5b-48f0-8ce8-f8ca3788634a')
update_output_table = ed_connector.execute(update_output_data).fetchall()
self.assertIn(('D',), update_output_table, "Delete status D is not found in the Update record")
self.assertIn(('C',), update_output_table, "Insert status C is not found in the Update record")
# Validate that upadte of asmt_score(1509 to 1500) is successful for student with student_id =779e658d-de44-4c9e-ac97-ea366722a94c
update_asmt_score = select([fact_table.c.asmt_score], and_(fact_table.c.student_id == '34b99412-fd5b-48f0-8ce8-f8ca3788634a', fact_table.c.rec_status == 'C'))
new_asmt_score = ed_connector.execute(update_asmt_score).fetchall()
expected_asmt_score = [(1500,)]
self.assertEquals(new_asmt_score, expected_asmt_score)
# Validate that delete and update also works for fact_asmt_outcome
fact_asmt = ed_connector.get_table('fact_asmt_outcome')
output_data = select([fact_asmt.c.rec_status]).where(fact_asmt.c.student_id == '3efe8485-9c16-4381-ab78-692353104cce')
output_table = ed_connector.execute(output_data).fetchall()
#verify delete record
self.assertEquals(output_table, expected_status_val_D, 'Status is wrong in fact table for delete record')
#Verify Update record
update_data = select([fact_asmt.c.rec_status]).where(fact_asmt.c.student_id == '34b99412-fd5b-48f0-8ce8-f8ca3788634a')
update_table = ed_connector.execute(update_data).fetchall()
self.assertIn(('D',), update_table, "Delete status D is not found in the Update record")
self.assertIn(('C',), update_table, "Insert status C is not found in the Update record")
# Validate that upadte of asmt_score(1509 to 1500) is successful for student with student_id =779e658d-de44-4c9e-ac97-ea366722a94c
update_score = select([fact_asmt.c.asmt_score], and_(fact_asmt.c.student_id == '34b99412-fd5b-48f0-8ce8-f8ca3788634a', fact_asmt.c.rec_status == 'C'))
new_score = ed_connector.execute(update_score).fetchall()
self.assertEquals(new_score, expected_asmt_score)
def test_check_mismatched_deletions(self):
with get_target_connection(self.tenant_code, self.guid_batch) as conn:
self.assertRaises(DeleteRecordNotFound, move_to_target.check_mismatched_deletions(self.conf, conn, 'fact_asmt_outcome_vw'))
def drop_target_schema(tenant, schema_name):
with get_target_connection(tenant) as connector:
try:
connector.execute(DropSchema(schema_name, cascade=True))
except:
pass
def truncate_edware_tables(self, schema, tenant='cat'):
with get_target_connection(tenant, schema_name=schema) as conn:
metadata = conn.get_metadata()
for table in reversed(metadata.sorted_tables):
conn.execute(table.delete())
def validate_edware_database(self, schema_name):
with get_target_connection('cat', schema_name) as ed_connector:
fact_table = ed_connector.get_table('fact_asmt_outcome_vw')
delete_output_data = select([
fact_table.c.rec_status
]).where(fact_table.c.student_id ==
'3efe8485-9c16-4381-ab78-692353104cce')
delete_output_table = ed_connector.execute(
delete_output_data).fetchall()
expected_status_val_D = [('D', )]
#verify delete record
self.assertEquals(
delete_output_table, expected_status_val_D,
'Status is wrong in fact table for delete record')
#Verify Update record
update_output_data = select([
fact_table.c.rec_status
]).where(fact_table.c.student_id ==
'34b99412-fd5b-48f0-8ce8-f8ca3788634a')
update_output_table = ed_connector.execute(
update_output_data).fetchall()
self.assertIn(('D', ), update_output_table,
"Delete status D is not found in the Update record")
self.assertIn(('C', ), update_output_table,
"Insert status C is not found in the Update record")
# Validate that upadte of asmt_score(1509 to 1500) is successful for student with student_id =779e658d-de44-4c9e-ac97-ea366722a94c
update_asmt_score = select(
[fact_table.c.asmt_score],
and_(
fact_table.c.student_id ==
'34b99412-fd5b-48f0-8ce8-f8ca3788634a',
fact_table.c.rec_status == 'C'))
new_asmt_score = ed_connector.execute(update_asmt_score).fetchall()
expected_asmt_score = [(1500, )]
self.assertEquals(new_asmt_score, expected_asmt_score)
# Validate that delete and update also works for fact_asmt_outcome
fact_asmt = ed_connector.get_table('fact_asmt_outcome')
output_data = select([
fact_asmt.c.rec_status
]).where(fact_asmt.c.student_id ==
'3efe8485-9c16-4381-ab78-692353104cce')
output_table = ed_connector.execute(output_data).fetchall()
#verify delete record
self.assertEquals(
output_table, expected_status_val_D,
'Status is wrong in fact table for delete record')
#Verify Update record
update_data = select([
fact_asmt.c.rec_status
]).where(fact_asmt.c.student_id ==
'34b99412-fd5b-48f0-8ce8-f8ca3788634a')
update_table = ed_connector.execute(update_data).fetchall()
self.assertIn(('D', ), update_table,
"Delete status D is not found in the Update record")
self.assertIn(('C', ), update_table,
"Insert status C is not found in the Update record")
# Validate that upadte of asmt_score(1509 to 1500) is successful for student with student_id =779e658d-de44-4c9e-ac97-ea366722a94c
update_score = select([fact_asmt.c.asmt_score],
and_(
fact_asmt.c.student_id ==
'34b99412-fd5b-48f0-8ce8-f8ca3788634a',
fact_asmt.c.rec_status == 'C'))
new_score = ed_connector.execute(update_score).fetchall()
self.assertEquals(new_score, expected_asmt_score)
def validate_stu_reg_target_table(self, file_to_load):
with get_target_connection('cat', self.batch_id) as conn:
target_table = conn.get_table(Constants.SR_TARGET_TABLE)
query = select([func.count()]).select_from(target_table)
record_count = conn.execute(query).fetchall()[0][0]
self.assertEqual(record_count, self.student_reg_files[file_to_load]['num_records_in_data_file'], 'Unexpected number of records in target table.')
def verify_multi_tenancy(self):
with get_target_connection('nc', self.guid_batch_id) as ed_connector:
edware_table = ed_connector.get_table('dim_asmt')
query_dim_table = select([edware_table])
result_dim_table = ed_connector.execute(query_dim_table).fetchall()
self.assertEquals(len(result_dim_table), 1, "Data not loaded into preprod")
def drop_target_schema(tenant, schema_name):
with get_target_connection(tenant) as connector:
try:
connector.execute(DropSchema(schema_name, cascade=True))
except:
pass
def test_get_records_for_deletion(self):
self.load_int_to_star()
self.assertEqual(4, self.count_rows())
with get_target_connection(MatchAndDeleteFTest.tenant_code, MatchAndDeleteFTest.guid_batch) as target_conn:
records_to_be_deleted = move_to_target.get_records_marked_for_deletion(self.conf, target_conn, 'fact_asmt_outcome_vw')
self.assertEqual(4, len(records_to_be_deleted))
def test_check_mismatched_deletions(self):
with get_target_connection(self.tenant_code, self.guid_batch) as conn:
self.assertRaises(
DeleteRecordNotFound,
move_to_target.check_mismatched_deletions(
self.conf, conn, 'fact_asmt_outcome_vw'))
def drop_target_schema(cls, tenant, guid_batch):
with get_target_connection(tenant) as connector:
drop_schema(connector, guid_batch)
def create_target_schema_for_batch(tenant, schema_name):
"""
creates the target star schema needed for this batch
"""
with get_target_connection(tenant) as conn:
create_schema(conn, generate_ed_metadata, schema_name)
def create_target_schema_for_batch(tenant, schema_name):
"""
creates the target star schema needed for this batch
"""
with get_target_connection(tenant) as conn:
create_schema(conn, generate_ed_metadata, schema_name)
def create_schema_for_target(cls, tenant, guid_batch):
with get_target_connection(tenant) as connector:
create_schema(connector, generate_ed_metadata, guid_batch)
def create_schema_for_target(cls, tenant, guid_batch):
with get_target_connection(tenant) as connector:
create_schema(connector, generate_ed_metadata, guid_batch)
def drop_target_schema(cls, tenant, guid_batch):
with get_target_connection(tenant) as connector:
drop_schema(connector, guid_batch)
