charles-tan

SELECT * FROM flight_status_view ORDER BY flight_id;

charles-tan

  flight_id |         event_ts         | origin | destination | mins_delayed |  current_departure   |   current_arrival    |          description
------------+--------------------------+--------+-------------+--------------+----------------------+----------------------+---------------------------------
  Flight_1  | 2024-05-29T12:52:13.837Z | LAX    | YVR         |          177 | 2024-05-29T13:30:00Z | 2024-05-29T16:34:00Z | More Thunderstorms
  Flight_2  | 2024-05-29T12:30:13.837Z | JFK    | SFO         |           60 | 2024-05-29T13:30:00Z | 2024-05-29T20:10:00Z | Waiting for connecting
            |                          |        |             |              |                      |                      | passengers
  Flight_3  | 2024-05-30T06:52:13.837Z | SIN    | NRT         |          185 | 2024-05-30T12:30:00Z | 2024-05-30T20:35:00Z | Mechanical delays
  Flight_4  | 2024-05-30T15:52:13.837Z | AUS    | CHI         |            0 | 2024-07-20T09:15:00Z | 2024-07-20T11:30:00Z | <nil>

charles-tan

{"flight_id": "Flight_1", "event_ts": "2024-05-28 15:52:13.837", "updated_departure": "2024-05-29 12:30:00", "description": "Thunderstorms" }
{"flight_id": "Flight_2", "event_ts": "2024-05-29 12:30:13.837", "updated_departure": "2024-05-29 13:30:00", "description": "Waiting for connecting passengers" }
{"flight_id": "Flight_1", "event_ts": "2024-05-29 12:52:13.837", "updated_departure": "2024-05-29 13:30:00", "description": "More Thunderstorms" }
{"flight_id": "Flight_3", "event_ts": "2024-05-30 06:52:13.837", "updated_departure": "2024-05-30 12:30:00", "description": "Mechanical delays" }
…

charles-tan

{"flight_id": "Flight_1", "event_ts": "2024-03-28 10:12:13.489", "origin": "LAX", "destination": "YVR", "scheduled_dep": "2024-05-29 10:33:00", "scheduled_arr": "2024-05-29 13:37:00"}
{"flight_id": "Flight_2", "event_ts": "2024-04-11 11:58:56.489", "origin": "JFK", "destination": "SFO", "scheduled_dep": "2024-05-29 12:30:00", "scheduled_arr": "2024-05-29 19:10:00"}
{"flight_id": "Flight_3", "event_ts": "2024-04-23 10:12:13.489", "origin": "SIN", "destination": "NRT", "scheduled_dep": "2024-05-30 09:25:00", "scheduled_arr": "2024-05-30 17:30:00"}
{"flight_id": "Flight_4", "event_ts": "2024-05-30 15:52:13.837", "origin": "AUS", "destination": "CHI", "scheduled_dep": "2024-07-20 09:15:00", "scheduled_arr": "2024-07-20 11:30:00"}
…

charles-tan

CREATE MATERIALIZED VIEW flight_status_view AS 
SELECT 
  * 
FROM 
  enriched_flight_updates_log;

charles-tan

INSERT INTO enriched_flight_updates_log 
SELECT 
  flight_id, 
  event_ts, 
  origin, 
  destination, 
  CAST(0 AS BIGINT) AS mins_delayed, 
  scheduled_dep AS current_departure, 
  scheduled_arr AS current_arrival, 
  CAST(NULL AS VARCHAR) AS "description" 

charles-tan

CREATE CHANGELOG enriched_flight_updates_log (
  flight_id VARCHAR, 
  event_ts TIMESTAMP, 
  origin VARCHAR, 
  destination VARCHAR, 
  mins_delayed BIGINT, 
  current_departure TIMESTAMP, 
  current_arrival TIMESTAMP, 
  "description" VARCHAR, 
  PRIMARY KEY (flight_id)

charles-tan

CREATE STREAM enriched_flight_updates AS 
SELECT 
  u.flight_id, 
  u.event_ts, 
  f.origin, 
  f.destination, 
  (DS_TOEPOCH(u.updated_departure) - DS_TOEPOCH(f.scheduled_dep)) / 60000 AS mins_delayed, 
  u.updated_departure AS current_departure, 
  CAST(TO_TIMESTAMP_LTZ(
      (

charles-tan

CREATE STREAM flight_updates (
  flight_id VARCHAR, 
  event_ts TIMESTAMP, 
  updated_departure TIMESTAMP, 
  "description" VARCHAR
) WITH (
  'topic' = 'flight_updates',
  'value.format' = 'json', 
  'timestamp' = 'event_ts'
);

charles-tan

CREATE CHANGELOG flights (
  flight_id VARCHAR, 
  event_ts TIMESTAMP, 
  origin VARCHAR, 
  destination VARCHAR, 
  scheduled_dep TIMESTAMP, 
  scheduled_arr TIMESTAMP, 
  PRIMARY KEY (flight_id)
) WITH (
  'topic' = 'flights',