How do I add missing (or update incorrect) records to the reporting_booking table?


23
views
0
10 weeks ago by
To force a TravelBookingUpdatedEvent to be created (which in turn updates the reporting_booking table) you simply need to create the following event in the environment of your choice:

{"Subject":"travel.UpdateReportingEvent","Message":"{\"bookingIds\":[\"100005152\",\"100004996\"]}"}

it accepts an array of BookingIds however i'd limit the amount you pass in to around 50 as this is what i've tested with successfully.

if you're interested in reconciling if there are missing records from the reporting_booking table you can use the following SQL commands against the relevant redshift environment:

N.B. it is best to increase all read throughputs on the travel.pbPersistence table before running this otherwise it will take toooooo long (200 is suggested)

BEGIN;BEGIN;
DROP TABLE IF EXISTS travel_pbpersistence;
CREATE TABLE travel_pbpersistence( id                        varchar PRIMARY KEY, itemType        varchar, travellerId_bookingState  varchar);
COPY travel_pbpersistence(id, itemType, travellerId_bookingState) FROM 'dynamodb://travel.pbPersistence' credentials 'aws_access_key_id=******KEY_ID******;aws_secret_access_key=******ACCESS_KEY******' readratio 90;
DROP TABLE IF EXISTS travel_pbpersistence_bookings;
CREATE TABLE travel_pbpersistence_bookings( id                        varchar PRIMARY KEY, bookingState              varchar);
INSERT INTO travel_pbpersistence_bookings (SELECT id, substring(travellerId_bookingState, CHARINDEX('_', travellerId_bookingState) + 1) FROM travel_pbpersistence WHERE itemType = 'BOOKING' AND substring(id, 1, 1) <> 'V');
DROP TABLE IF EXISTS travel_pbpersistence_bookings_final;
CREATE TABLE travel_pbpersistence_bookings_final( id                        varchar PRIMARY KEY, bookingState              varchar);
INSERT INTO travel_pbpersistence_bookings_final (SELECT * FROM travel_pbpersistence_bookings WHERE bookingState IN ('CONFIRMED', 'CANCELLING', 'CANCELLED', 'FULFILLED', 'AWAITING_TICKETS', 'AWAITING_MANUAL_CANCELLATION'));
COMMIT;

once this has completed you'll have a list of id / state in a table called travel_pbpersistence_bookings_final which contains all of the bookings that should exist within the reporting_booking table

use this query to identify a list of bookings that are missing:

SELECT id FROM travel_pbpersistence_bookings_final WHERE id NOT IN
(SELECT booking_id FROM reporting_booking WHERE reporting_booking.booking_id = travel_pbpersistence_bookings_final.id);


use this query to identify a list of bookings that exist but have an incorrect status:

SELECT id, bookingState FROM travel_pbpersistence_bookings_final WHERE id NOT IN
(SELECT booking_id FROM reporting_booking WHERE reporting_booking.booking_id = travel_pbpersistence_bookings_final.id AND reporting_booking.status = travel_pbpersistence_bookings_final.bookingState);

Please login to add an answer/comment or follow this question.

Similar posts:
Search »
  • Nothing matches yet.