def test_process_flight_id_with_within_flight_creative_conflict_populates_expected(connection): connection.execute("TRUNCATE vendor_ids.maps;") connection.execute("TRUNCATE vendor_ids.alignment_conflicts;") vendor_ids_maps_insert_within_flight_conflict_query = """ INSERT INTO vendor_ids.maps (li_code, creative_rtb_id, date_start, date_end, vendor, vendor_id, is_deleted) VALUES ('LI-123456', 1111111, '2018-04-30', '2018-05-03', 'doubleclick', '12121212', 'f'), ('LI-123456', 2222222, '2018-04-30', '2018-05-03', 'doubleclick', '12121212', 'f'); """ connection.execute(vendor_ids_maps_insert_within_flight_conflict_query) vendor_ids_alignment_conflict_within_flight_conflict_query = """ INSERT INTO vendor_ids.alignment_conflicts (li_code, li_code_2, date_start, date_end, creative_ids) VALUES ('LI-123456', 'LI-123456', '2018-04-30', '2018-05-03', ARRAY[1111111, 2222222]); """ connection.execute(vendor_ids_alignment_conflict_within_flight_conflict_query) h.process_processing_id(connection, 'flight_id', '123456') results = select_all_from_output_table(connection) result_dates = set() for result in results: assert result[1] == '123456' assert result[2] == None result_dates.add(result[0]) assert result_dates == set([datetime.date(2018, 4 , 30), datetime.date(2018, 5 , 1), datetime.date(2018, 5 , 2), datetime.date(2018, 5 , 3)])
def test_process_import_id_with_no_resulting_data_populate_nothing(connection): connection.execute("UPDATE vendor_ids.maps SET is_deleted = TRUE;") h.process_processing_id(connection, 'import_id', '1') results = select_all_from_output_table(connection) assert results == set()
def test_process_li_code_with_new_data_populate_new_data(connection): insert_standard_output_data(connection) connection.execute("DELETE FROM {} WHERE flight_id = '123456' AND date = '2018-05-01';".format(OUTPUT_TABLE_FULL_NAME)) h.process_processing_id(connection, 'li_code', 'LI-123456') results = select_all_from_output_table(connection) assert results == get_standard_output_data()
def test_process_li_code_with_undeleted_rows_populate_marks_as_not_deleted(connection): insert_standard_output_data(connection) connection.execute("UPDATE {} SET is_deleted = TRUE WHERE flight_id = '123456';".format(OUTPUT_TABLE_FULL_NAME)) h.process_processing_id(connection, 'li_code', 'LI-123456') results = select_all_from_output_table(connection) assert results == get_standard_output_data()
def test_process_flight_ids_with_deleted_vendor_ids_maps_populate_marks_as_deleted(connection): connection.execute("UPDATE vendor_ids.maps SET is_deleted = TRUE WHERE li_code = 'LI-123456';") h.process_processing_id(connection, 'flight_id', '123456') h.process_processing_id(connection, 'flight_id', '7891011') results = select_all_from_output_table(connection) expected = get_standard_output_data_flight7891011() assert results == expected
def test_process_flight_id_with_no_dcm_data_doesnt_delete_old_backfilled_sizmek_data(connection): connection.execute(""" INSERT INTO {} ("date", flight_id, creative_id, impressions, clicks, provider, time_zone, is_deleted) VALUES ('2018-05-03', '9', '1111111', 50714, 7, 'mediamind', 'America/New_York', 'f') """.format(OUTPUT_TABLE_FULL_NAME)) h.process_processing_id(connection, 'flight_id', '9') results = select_all_from_output_table(connection) assert results == {(datetime.date(2018, 5, 3), '9', '1111111', 50714, 7, 'mediamind', 'America/New_York', False)}
def test_process_li_code_with_previous_within_flight_creative_conflict_populates_expected(connection): # add previous within flight creative conflict record to output table connection.execute(""" INSERT INTO {} (date, flight_id, creative_id, impressions, clicks, provider, time_zone, is_deleted) VALUES ('2018-05-01', '123456', NULL, 999, 999, 'doubleclick', 'America/New_York', 'f'); """.format(OUTPUT_TABLE_FULL_NAME)) h.process_processing_id(connection, 'li_code', 'LI-123456') results = select_all_from_output_table(connection) expected = get_standard_output_data_flight123456() expected.add((datetime.date(2018, 5, 1), '123456', None, 999, 999, 'doubleclick', 'America/New_York', True)) assert results == expected
def test_process_li_code_with_deleted_rows_populate_marks_as_deleted(connection): insert_standard_output_data(connection) connection.execute(""" INSERT INTO {} (date, flight_id, creative_id, impressions, clicks, provider, time_zone, is_deleted) VALUES ('2018-05-05', '123456', '1111111', 999, 999, 'doubleclick', 'America/New_York', 'f'); """.format(OUTPUT_TABLE_FULL_NAME)) h.process_processing_id(connection, 'li_code', 'LI-123456') results = select_all_from_output_table(connection) expected = get_standard_output_data() expected.add((datetime.date(2018, 5, 5), '123456', '1111111', 999, 999, 'doubleclick', 'America/New_York', True)) assert results == expected
def test_upsert_with_null_creative_id(connection): connection.execute(""" INSERT INTO {} (date, flight_id, creative_id, impressions, clicks, provider, time_zone, is_deleted) VALUES ('2018-05-05', '123456', null, 999, 999, 'doubleclick', 'America/New_York', 'f'), ('2018-05-05', '123456', null, 222, 222, 'doubleclick', 'Europe/London', 'f'); """.format(OUTPUT_TABLE_FULL_NAME)) h.process_processing_id(connection, 'li_code', 'LI-123456') results = select_all_from_output_table(connection) expected = get_standard_output_data_flight123456() expected.add((datetime.date(2018, 5, 5), '123456', None, 999, 999, 'doubleclick', 'America/New_York', True)) expected.add((datetime.date(2018, 5, 5), '123456', None, 222, 222, 'doubleclick', 'Europe/London', True)) assert results == expected
def test_process_li_code_with_alignment_conflict_populate_deletes_corresponding(connection): insert_standard_output_data(connection) connection.execute("""INSERT INTO vendor_ids.alignment_conflicts (li_code, date_start, date_end) VALUES ('LI-123456', '2018-04-30', '2018-05-03');""") h.process_processing_id(connection, 'li_code', 'LI-123456') results = select_all_from_output_table(connection) standard_results = get_standard_output_data() expected = set() for tup in standard_results: if tup[1] == '123456': tuplst = list(tup) tuplst[-1] = True tup = tuple(tuplst) expected.add(tup) assert results == expected
def test_process_li_code_with_no_resulting_data_populate_deletes_all_corresponding(connection): insert_standard_output_data(connection) # if no resulting data generated from upstream, previously corresponding data must be deleted connection.execute("UPDATE vendor_ids.maps SET is_deleted = TRUE WHERE li_code = 'LI-123456';") h.process_processing_id(connection, 'li_code', 'LI-123456') results = select_all_from_output_table(connection) standard_results = get_standard_output_data() expected = set() for tup in standard_results: if tup[1] == '123456': tuplst = list(tup) tuplst[-1] = True tup = tuple(tuplst) expected.add(tup) assert results == expected
def test_process_li_code_with_within_flight_creative_conflict_twice_no_error(connection): connection.execute("TRUNCATE vendor_ids.maps;") connection.execute("TRUNCATE vendor_ids.alignment_conflicts;") vendor_ids_maps_insert_within_flight_conflict_query = """ INSERT INTO vendor_ids.maps (li_code, creative_rtb_id, date_start, date_end, vendor, vendor_id, is_deleted) VALUES ('LI-123456', 1111111, '2018-04-30', '2018-05-03', 'doubleclick', '12121212', 'f'), ('LI-123456', 2222222, '2018-04-30', '2018-05-03', 'doubleclick', '12121212', 'f'); """ connection.execute(vendor_ids_maps_insert_within_flight_conflict_query) vendor_ids_alignment_conflict_within_flight_conflict_query = """ INSERT INTO vendor_ids.alignment_conflicts (li_code, li_code_2, date_start, date_end, creative_ids) VALUES ('LI-123456', 'LI-123456', '2018-04-30', '2018-05-03', ARRAY[1111111, 2222222]); """ connection.execute(vendor_ids_alignment_conflict_within_flight_conflict_query) h.process_processing_id(connection, 'li_code', 'LI-123456') h.process_processing_id(connection, 'li_code', 'LI-123456')
def test_process_import_id_within_flight_creative_conflict_populates_expected(connection): vendor_ids_maps_set_within_flight_conflict_query = """ UPDATE vendor_ids.maps SET vendor_id = '12121212' WHERE li_code = 'LI-123456'; """ connection.execute(vendor_ids_maps_set_within_flight_conflict_query) vendor_ids_alignment_conflict_within_flight_conflict_query = """ INSERT INTO vendor_ids.alignment_conflicts (li_code, li_code_2, date_start, date_end, creative_ids) VALUES ('LI-123456', 'LI-123456', '2018-04-30', '2018-05-03', ARRAY[1111111, 2222222]); """ connection.execute(vendor_ids_alignment_conflict_within_flight_conflict_query) h.process_processing_id(connection, 'import_id', '1') standard_output_data_flight_7891011 = get_standard_output_data_flight7891011() results = select_all_from_output_table(connection) for result in results: if result[1] == '123456': assert result[2] == None if result[2] == '7891011': assert result in standard_output_data_flight_7891011
def test_process_li_code_both_within_and_not_within_flight_creative_conflict_populates_none(connection): connection.execute("TRUNCATE vendor_ids.maps;") connection.execute("TRUNCATE vendor_ids.alignment_conflicts;") vendor_ids_maps_insert_within_flight_conflict_query = """ INSERT INTO vendor_ids.maps (li_code, creative_rtb_id, date_start, date_end, vendor, vendor_id, is_deleted) VALUES ('LI-123456', 1111111, '2018-04-30', '2018-05-03', 'doubleclick', '12121212', 'f'), ('LI-123456', 2222222, '2018-04-30', '2018-05-03', 'doubleclick', '12121212', 'f'); """ connection.execute(vendor_ids_maps_insert_within_flight_conflict_query) vendor_ids_alignment_conflict_within_flight_conflict_query = """ INSERT INTO vendor_ids.alignment_conflicts (li_code, li_code_2, date_start, date_end, creative_ids) VALUES ('LI-123456', 'LI-123456', '2018-04-30', '2018-05-03', ARRAY[1111111, 2222222]), ('LI-123456', 'LI-999999', '2018-04-30', '2018-05-03', ARRAY[1111111, 9999999]); """ connection.execute(vendor_ids_alignment_conflict_within_flight_conflict_query) h.process_processing_id(connection, 'li_code', 'LI-123456') results = select_all_from_output_table(connection) assert results == set()
def lambda_handler(event, context): # wrap all processing within try/except because we don't want failures to halt further processing try: # we only expect one record to arrive at a time, but leaving this loop for best practice for record in event['Records']: # Kinesis data is base64 encoded so decode here payload = base64.b64decode(record['kinesis']['data']) decoded_payload = payload.decode("utf-8") logger.info("Processing payload: " + decoded_payload) json_payload = json.loads(decoded_payload) processing_id_type = json_payload[PROCESSING_ID_TYPE_JSON_HEADER] processing_id = json_payload[PROCESSING_ID_JSON_HEADER] # attempt to process, retrying up to MAXIMUM_RETRY_ON_DEADLOCK times retries_left = MAXIMUM_RETRY_ON_DEADLOCK while retries_left >= 0: try: connection = get_connection() process_processing_id(connection, processing_id_type, processing_id) break except OperationalError as e: if LOCK_ERROR_MESSAGE in traceback.format_exc( ) and retries_left > 0: logger.warn( 'Lock timeout trying to process {0}. Number of attempts left: {1}' .format(decoded_payload, retries_left)) else: raise finally: connection.close() retries_left -= 1 except Exception as e: logger.error(traceback.format_exc()) return 'Failed to process {} records'.format(len(event['Records'])) return 'Successfully processed {} records.'.format(len(event['Records']))
def test_deletion_and_upsert_allows_multiple_time_zone(connection): connection.execute(""" INSERT INTO double_click.raw_delivery (import_record_id, placement_id, "date", impressions, clicks, campaign_id, ad_id, advertiser, advertiser_id, campaign, placement_rate, site_keyname) VALUES (3, 12121212, '2018-05-03', 555, 555, 000, 001, 'junkus', 000, 'junkus', 000, 'junkus') """) connection.execute(""" INSERT INTO double_click.import_metadata (import_record_id, report_time_zone, s3_path, credential, profile_id) VALUES (3, 'Europe/London', 'junk', 'junk', 0); """) connection.execute(""" INSERT INTO {} (date, flight_id, creative_id, impressions, clicks, provider, time_zone, is_deleted) VALUES ('2018-05-03', '123456', '1111111', 999, 999, 'doubleclick', 'SomewhereRandom', 'f') """.format(OUTPUT_TABLE_FULL_NAME)) h.process_processing_id(connection, 'li_code', 'LI-123456') results = select_all_from_output_table(connection) expected = get_standard_output_data_flight123456() expected.add((datetime.date(2018, 5, 3), '123456', '1111111', 555, 555, 'doubleclick', 'Europe/London', False)) expected.add((datetime.date(2018, 5, 3), '123456', '1111111', 999, 999, 'doubleclick', 'SomewhereRandom', True)) assert results == expected
def test_process_import_id_with_empty_table_populate_expected_output(connection): h.process_processing_id(connection, 'import_id', '1') results = select_all_from_output_table(connection) assert results == get_standard_output_data()
def test_process_flight_id_with_empty_table_populate_expected_output(connection): h.process_processing_id(connection, 'li_code', 'LI-123456') h.process_processing_id(connection, 'li_code', 'LI-7891011') results = select_all_from_output_table(connection) assert results == get_standard_output_data()