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