This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT * FROM flight_status_view ORDER BY flight_id; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 | ORD | 0 | 2024-07-20T09:15:00Z | 2024-07-20T11:30:00Z | <nil> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
{"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" } | |
… |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
{"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": "ORD", "scheduled_dep": "2024-07-20 09:15:00", "scheduled_arr": "2024-07-20 11:30:00"} | |
… |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE MATERIALIZED VIEW flight_status_view AS | |
SELECT | |
* | |
FROM | |
enriched_flight_updates_log; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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" |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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( | |
( |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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' | |
); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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', |
NewerOlder