def load_fact_table(job_info, logger):
print('EZProxy loading fact table...')
logger.info('Loading to the fact table.... ')
stage2_table = dwetl.Base.classes['dw_stg_2_ezp_sessns_snap']
fact_table = dwetl.Base.classes['fact_ezp_sessns_snap']
processing_cycle_id = job_info.prcsng_cycle_id
# get max value for fact key from the reporting db
with dwetl.reporting_database_session() as session2:
reporting_fact_table = dwetl.ReportingBase.classes[
'fact_ezp_sessns_snap']
max_ezp_sessns_snap_fact_key = session2.query(
func.max(reporting_fact_table.ezp_sessns_snap_fact_key)).scalar()
if max_ezp_sessns_snap_fact_key is None:
max_ezp_sessns_snap_fact_key = 1
# load etl ezp fact table
with dwetl.database_session() as session:
reader = SqlAlchemyReader(session, stage2_table,
'em_create_dw_prcsng_cycle_id',
processing_cycle_id)
writer = SqlAlchemyWriter(session, fact_table)
processor = EzproxyFactProcessor(reader, writer, job_info, logger,
max_ezp_sessns_snap_fact_key)
processor.execute()
logger.info('Finished loading to the fact table.... ')
def intertable_processing(job_info, logger):
print('EZProxy transformations started...')
logger.info('EZProxy intertable processing starts...')
stage2_table = dwetl.Base.classes['dw_stg_2_ezp_sessns_snap']
processing_cycle_id = job_info.prcsng_cycle_id
with dwetl.database_session() as session:
reader = SqlAlchemyReader(session, stage2_table,
'em_create_dw_prcsng_cycle_id',
processing_cycle_id)
writer = SqlAlchemyWriter(session, stage2_table)
processor = EzproxyProcessor(reader, writer, job_info, logger)
processor.execute()
logger.info('Finished EZProxy intertable processing .... ')
def test_read_empty_table(self):
with dwetl.test_database_session() as session:
table_base_class = dwetl.Base.classes.dw_stg_1_mai01_z00
# Use negative processing cycle id, so any actual data in the tables
# won't interfere with the tests.
processing_cycle_id = -1
reader = SqlAlchemyReader(session, table_base_class,
'em_create_dw_prcsng_cycle_id',
processing_cycle_id)
results = []
for row in reader:
results.append(row)
self.assertEqual(0, len(results))
def load_stage_2(job_info, logger):
print('Loading stage 2...')
logger.info('Loading stage 2...')
stage1_to_stage2_table_mappings = {
"dw_stg_1_mai39_z13": "dw_stg_2_bib_rec_z13",
'dw_stg_1_mai01_z13': "dw_stg_2_bib_rec_z13",
"dw_stg_1_mai01_z13u": "dw_stg_2_bib_rec_z13u",
"dw_stg_1_mai01_z00": "dw_stg_2_bib_rec_z00",
"dw_stg_1_mai39_z00": "dw_stg_2_bib_rec_z00",
"dw_stg_1_mai39_z13u": "dw_stg_2_bib_rec_z13u",
# "dw_stg_1_mai60_z00": "dw_stg_2_lbry_holding_z00",
# "dw_stg_1_mai60_z13": "dw_stg_2_lbry_holding_z13",
# "dw_stg_1_mai60_z13u": "dw_stg_2_lbry_holding_z13u",
# "dw_stg_1_mai50_z30": "dw_stg_2_lbry_item_z30",
# "dw_stg_1_mai50_z35": "dw_stg_2_lbry_item_event_z35",
# "dw_stg_1_mai01_z00_field": "dw_stg_2_bib_rec_z00_field",
# "dw_stg_1_mai39_z00_field": "dw_stg_2_bib_rec_z00_field",
# "dw_stg_1_mai60_z00_field": "dw_stg_2_lbry_holding_z00_field",
# "dw_stg_1_mpf_mbr_lbry": "dw_stg_2_mpf_mbr_lbry",
# "dw_stg_1_mpf_lbry_entity": "dw_stg_2_mpf_lbry_entity",
# "dw_stg_1_mpf_collection": "dw_stg_2_mpf_collection",
# "dw_stg_1_mpf_item_status": "dw_stg_2_mpf_item_status",
# "dw_stg_1_mpf_item_prcs_status": "dw_stg_2_mpf_item_prcs_status",
# "dw_stg_1_mpf_matrl_form": "dw_stg_2_mpf_matrl_form"
}
processing_cycle_id = job_info.prcsng_cycle_id
for stage1_table, stage2_table in stage1_to_stage2_table_mappings.items():
print(stage1_table)
logger.info(stage1_table)
library = aleph_library(stage1_table)
with dwetl.database_session() as session:
stage1_table_class = dwetl.Base.classes[stage1_table]
stage2_table_class = dwetl.Base.classes[stage2_table]
reader = SqlAlchemyReader(session, stage1_table_class,
'em_create_dw_prcsng_cycle_id',
processing_cycle_id)
writer = SqlAlchemyWriter(session, stage2_table_class)
processor = CopyStage1ToStage2.create(reader, writer, job_info,
logger, library)
processor.execute()
def load_stage_2(job_info, logger):
print('EZProxy Loading stage 2...')
logger.info('EZProxy Loading stage 2...')
processing_cycle_id = job_info.prcsng_cycle_id
with dwetl.database_session() as session:
stage1_table_class = dwetl.Base.classes["dw_stg_1_ezp_sessns_snap"]
stage2_table_class = dwetl.Base.classes["dw_stg_2_ezp_sessns_snap"]
reader = SqlAlchemyReader(session, stage1_table_class,
'em_create_dw_prcsng_cycle_id',
processing_cycle_id)
writer = SqlAlchemyWriter(session, stage2_table_class)
# there is no aleph library for ez proxy data, but CopyStage1ToStage2 still will work
library = ''
processor = CopyStage1ToStage2.create(reader, writer, job_info, logger,
library)
processor.execute()
logger.info('Finished EZProxy loading stage 2 .... ')
def copy_new_facts_to_reporting_db(job_info, logger):
etl_fact_table = dwetl.Base.classes['fact_ezp_sessns_snap']
processing_cycle_id = job_info.prcsng_cycle_id
# query and select records from etl fact table
# should we use the create update processing cycle ID? or the Update processing cycle id?
with dwetl.database_session() as session:
reader = SqlAlchemyReader(session, etl_fact_table,
'em_create_dw_prcsng_cycle_id',
processing_cycle_id)
session.expunge_all()
# insert records into reporting db ezp fact table
with dwetl.reporting_database_session() as session2:
reporting_fact_table = dwetl.ReportingBase.classes[
'fact_ezp_sessns_snap']
writer = SqlAlchemyWriter(session2, reporting_fact_table)
processor = EzproxyReportingFactProcessor(reader, writer, job_info,
logger)
processor.execute()
def stage_2_intertable_processing(job_info, logger):
print("Stage 2 Intertable Processing...")
logger.info("Stage 2 Intertable Processing...")
STG_2_TABLE_CONFIG_MAPPING = {
'dw_stg_2_bib_rec_z00': 'bibliographic_record_dimension',
'dw_stg_2_bib_rec_z13': 'bibliographic_record_dimension',
'dw_stg_2_bib_rec_z13u': 'bibliographic_record_dimension',
'dw_stg_2_bib_rec_z00_field': 'bibliographic_record_dimension'
}
processing_cycle_id = job_info.prcsng_cycle_id
for table, dimension in STG_2_TABLE_CONFIG_MAPPING.items():
print(table)
logger.info(table)
# get json_config for current dimension
table_config_path = os.path.join('table_config', dimension + '.json')
json_config = load_table_config(table_config_path)
with dwetl.database_session() as session:
# gets SA base class for the current table
stage2_table_class = dwetl.Base.classes[table]
# gets list of PKs for the current table
pk_list = [pk.name for pk in stage2_table_class.__table__.primary_key]
reader = SqlAlchemyReader(session, stage2_table_class, 'em_create_dw_prcsng_cycle_id', processing_cycle_id)
writer = SqlAlchemyWriter(session, stage2_table_class)
'''
Preprocessing
'''
preprocessor = Preprocess(reader, writer, job_info, logger, json_config, pk_list)
preprocessor.execute()
'''
# Data Quality Checks
# '''
data_quality_checker = DataQualityProcessor(reader, writer, job_info, logger, json_config, pk_list)
data_quality_checker.execute()
def test_read_rows_from_table(self):
with dwetl.test_database_session() as session:
# Add some sample rows to the "dw_stg_1_mai01_z00" table
table_base_class = dwetl.Base.classes.dw_stg_1_mai01_z00
rows = [{
'rec_type_cd': 'D',
'db_operation_cd': 'U',
'rec_trigger_key': '000205993',
'z00_doc_number': '000205993',
'z00_no_lines': '0043',
'z00_data_len': '001583'
}, {
'rec_type_cd': 'D',
'db_operation_cd': 'U',
'rec_trigger_key': '000245526'
}]
# Use negative processing cycle id, so any actual data in the tables
# won't interfere with the tests.
processing_cycle_id = -1
for row in rows:
self.append_job_info(row, 'em_create_dw_prcsng_cycle_id',
processing_cycle_id)
self.setup_rows(session, table_base_class, rows)
reader = SqlAlchemyReader(session, table_base_class,
'em_create_dw_prcsng_cycle_id',
processing_cycle_id)
results = []
for row in reader:
results.append(row)
self.assertEqual(2, len(results))
self.assertEqual('000205993', results[0]['rec_trigger_key'])
self.assertEqual('000245526', results[1]['rec_trigger_key'])
def test_read_rows_from_table_with_multiple_processing_ids(self):
with dwetl.test_database_session() as session:
# Add some sample rows to the "dw_stg_1_mai01_z00" table
table_base_class = dwetl.Base.classes.dw_stg_1_mai01_z00
rows = [{
'rec_type_cd': 'D',
'db_operation_cd': 'U',
'rec_trigger_key': '000205993',
'z00_doc_number': '000205993',
'z00_no_lines': '0043',
'z00_data_len': '001583'
}, {
'rec_type_cd': 'D',
'db_operation_cd': 'U',
'rec_trigger_key': '000245526'
}]
# Use different em_create_dw_prcsng_cycle_ids for each row
#
# Using negative processing_cycle_ids so having real data in the
# tables won't interfere with the tests.
self.append_job_info(rows[0], 'em_create_dw_prcsng_cycle_id', -1)
self.append_job_info(rows[1], 'em_create_dw_prcsng_cycle_id', -2)
self.setup_rows(session, table_base_class, rows)
reader = SqlAlchemyReader(session, table_base_class,
'em_create_dw_prcsng_cycle_id', -2)
results = []
for row in reader:
results.append(row)
# Only the row matching the em_create_dw_prcsng_cycle_id should be returned
self.assertEqual(1, len(results))
self.assertEqual('000245526', results[0]['rec_trigger_key'])