Tutorial 2B: Coding Streaming SQL in SQLstream sqlLine

<< Click to Display Table of Contents >>

Navigation:  Building Streaming Applications > Tutorial 2: Introduction to Coding with Streaming SQL >

Tutorial 2B: Coding Streaming SQL in SQLstream sqlLine

Previous pageReturn to chapter overviewNext page

SQLstream s-Server lets you query streaming data by using SQL (Structured Query Language), the same language that developers have been using to query traditional databases for decades.

This tutorial describes the steps for setting up a log file source and analyzing it, by coding blocks of SQL. You can execute SQL directly through a command-line program called sqlLine. sqlLine executes SQL code against SQLstream s-Server. A version of sqlLine ships with s-Server, and comes pre-configured to connect with s-Server.

The tutorial includes the following steps:

1.Connecting to a Data Source

2.Creating a Foreign Stream to Connect with a Log File

3.Promoting a Column to Rowtime

4.Creating a VIEW on the Foreign Stream that Performs a Basic Calculation

5.Using s-Dashboard to show streaming data in dashboard.

Overview of Streaming SQL

The type of SQL that runs in s-Server is called streaming SQL. This SQL is based on the SQL:2008 ANSI standard, with some modifications. s-Server' streaming SQL is described in the Streaming SQL Reference Guide.

SQLstream’s main enhancement to the SQL standard concerns the STREAM object. The process of creating streams in streaming SQL is similar to the process of creating tables in a database system like PostgreSQL or Oracle. Like database tables, streams have columns with column types. Once you create a stream, you can query it with a SELECT statement, or insert into it using an INSERT statement.

A block of SQL that creates a stream looks something like the following.

CREATE OR REPLACE SCHEMA "buses_schema";

SET SCHEMA '"buses_schema"';

 

CREATE OR REPLACE FOREIGN STREAM "buses_stream"

(   "id" DOUBLE,

   "reported_at" VARCHAR(4096),

   "shift_no" DOUBLE,

   "trip_no" DOUBLE,

   "route_variant_id" DOUBLE,

   "waypoint_id" DOUBLE,

   "last_known_location_state" VARCHAR(4096),,

   "lat" DOUBLE,

   "lon" DOUBLE,

   "speed" DOUBLE,

   "bearing" VARCHAR(4096),

   "driver_no" VARCHAR(4096),

   "prescribed" VARCHAR(4096),

   "highway" VARCHAR(4096),

   "created_at" VARCHAR(4096),

   "updated_at" VARCHAR(4096),

)

   SERVER "Buses_Server"

;

 

 

Note that you declare types for each column. Data types determine what kind of data a column can contain. For example, a VARCHAR() column contains a character string of variable length.

Next: Connecting to a Data Source