Creating a Foreign Stream to Connect with a Log File
The most important concept for Streaming SQL is the stream. A stream is a continually updating data object. A stream is like a table with no end, but that begins when the stream was established. As s-Server intakes information, new rows are continually added to the stream.
Streams can be written to by multiple writers and read from by multiple readers.
When you run a SELECT query on a conventional database table, the query iterates through the result set until there are no more rows to return. But when you run a SELECT query on an s-Server stream, there's no end of rows. Instead, the "get next row" call continues to run in s-Server until the statement is closed by the client application. In complex systems, this open-ended SELECT needs to be managed in order to maximize performance. Here, though, we can just let it run.
The following code block creates a stream that references the server object "BusFileReaderServer". Remember, in the server object, we defined how to connect with the data source, which in this case is a log file on a local machine. The stream is a virtual object with the columns listed below. When you query the stream, it splits the queried log file into columns based on the separator.
Enter the following code into the SQLline prompt:
CREATE OR REPLACE SCHEMA "buses";
SET SCHEMA '"buses"';
CREATE OR REPLACE FOREIGN STREAM "buses_stream"
"id" DOUBLE, --Identification number for the bus.
"reported_at" TIMESTAMP, --Time location was reported.
"shift_no" DOUBLE, --Shift number for the bus's driver.
"trip_no" VARCHAR(4096), --Trip number for the bus.
"route_variant_id" VARCHAR(4096), --ID number for bus route.
"waypoint_id" VARCHAR(4096), --ID number for bus waypoint.
"last_known_location_state" VARCHAR(4096), --Location state.
"lat" VARCHAR(4096), --Latitude of location.
"lon" VARCHAR(4096), --Longitude of location.
"speed" DOUBLE, --Reported speed of bus.
"bearing" VARCHAR(4096), --Navigational bearing for bus.
"driver_no" DOUBLE, --Driver identification for number.
"prescribed" VARCHAR(4096), --The direction on the motorway,
--(into Sydney or out of Sydney).
"highway" DOUBLE, --Highway number, if available.
"created_at" TIMESTAMP, --Timestamp for when this bus
--started reporting locations.
"updated_at" TIMESTAMP --Timestamp for when this bus
--last reported a location.
--Server created in the previous step.
--Provides connection information for the log file.
All streams must be created within schemas. A schema is lets you logically group s-Server objects, such as streams, tables, and views.
Once the stream is created, you can query it as you would a table, using code along the following lines:
SELECT STREAM * from "buses_stream";
Note that the query uses the STREAM keyword. You need to use this keyword whenever you query a stream. We will use the STREAM keyword throughout the rest of this tutorial.