How do I add missing (or update incorrect) records to the reporting_booking table?
4 months ago by
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)
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'));
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);
Community: Travel Team Engineers
Please login to add an answer/comment or follow this question.