Integrating WebSockets

Using s-Server, you can read from and write to WebSockets. To read and write from local locations, you configure and launch the adapter in SQL, using either server or foreign stream/table options. To read and write from remote locations, you configure such options using a properties file and launch the Extensible Common Data agent at the command line.

This topic contains the following subtopics:

Reading from WebSockets

You can read data over websockets using the Extensible Common Data Adapter (ECDA) or ECD Agent.

See Using the Extensible Common Data Framework for more details.

For example, you can use the ECD websocket adapter to read from Teradata Listener Broadcast streams. See Using the ECD Websocket Adapter to Listen to Teradata below.

To read from local locations, you configure and launch the adapter in SQL, using either server or foreign stream/table options. See Reading from WebSockets Using SQL below. To write from remote locations, you configure such options using a properties file and launch the agent at the command line. See Reading from WebSockets Using the ECD Agent below.

Many of the options for the ECD adapter and agent are common to all I/O systems. The CREATE FOREIGN STREAM topic in the Streaming SQL Reference Guide has a complete list of options for the ECD adapter.

The s-Server trace log includes information on readers' and parsers' progress. See Periodic Parser Statistics Logging in the Administering Guavus SQLstream guide. These errors are also logged in the Global Error Stream.

Reading from WebSockets Using SQL

To read from WebSockets, you need to create a foreign stream in SQL that references a prebuilt server object called WEBSOCKET_SERVER. The foreign stream's definition contains connection information for the WebSocket.

You will also need to specify a parser for the foreign stream, such as 'CSV'. Specifying "parser" as a foreign stream option tells s-Server that this foreign stream reads data. The following code first creates a schema in which to run the rest of the sample code below, then creates a foreign stream named WebSocketReaderStream.

CREATE OR REPLACE SCHEMA WebSocketSource;
SET SCHEMA 'WebSocketSource';

CREATE OR REPLACE FOREIGN STREAM WebSocketReaderStream
("recNo" INTEGER,
"ts" TIMESTAMP,
"accountNumber" INTEGER,
"loginSuccessful" BOOLEAN,
"sourceIP" VARCHAR(32),
"destIP" VARCHAR(32),
"customerId" INTEGER)
--Columns for the new stream
SERVER WEBSOCKET_SERVER
OPTIONS
(directory 'myDirectory',
--directory for the file
        parser 'CSV',
        "URL" 'wss://listener-streamer-services-poc.labs.myserver.com/84be767d-bc36-4d24-82c2-fa6c88e00c1b',
       "HEADER_Connection" 'Upgrade',
       "HEADER_Authorization" 'token d90621ca-9601-4a49-96f3-160f61082c34',
       "HEADER_Upgrade" 'websocket',
       "HEADER_Host" 'listener-myserver.com',
       "HEADER_Origin" 'listener-myserver.com'
);

Using the ECD Websocket Adapter to Read Teradata Listener Broadcast Streams

Because Teradata Listener uses websockets to communicate, you can use the ECD Websocket adapter with Teradata Listener Broadcast Streams. See https://listener.docs.teradata.com/eeh1499950147490.html for more details about Teradata Listener Broadcast Streams.

To do so, you set up a websocket server with information about the Teradata Listener Broadcast Stream, as follows. Note that the HEADER_ option includes host and token information.

CREATE OR REPLACE SERVER "WebSocketListenerServer" TYPE 'websocket'
FOREIGN DATA WRAPPER ECDA;

CREATE OR REPLACE FOREIGN STREAM device_stream (
        MESSAGE VARCHAR(1024)
    )
    SERVER "WebSocketListenerServer"
    OPTIONS (
        "SEPARATOR" '',
        "PARSER" 'CSV',
        "URL" 'wss://listener.teradata.com/v1//84be888d-bc36-4d24-82c2-fa6c88e00c1b',
        "HEADER_Connection" 'Upgrade',
        "HEADER_Authorization" 'token d90888ca-9601-4a49-96f3-160f61082c34',
        "HEADER_Upgrade" 'websocket',
        "HEADER_Host" 'listener.teradata.com',
        "HEADER_Origin" 'listener.teradata.com'
    );

Foreign Stream Options for Reading Over WebSockets

Option Description
URL URL for web socket.
HEADER_ Tells Web Socket reader to add a header called <name_of_header> to the request.
OPTIONS_QUERY Optional. Lets you query a table to update one or more adapter options at runtime. You can use this, for example, to set HEADER_xxx options using select HEADER_ABC, HEADER_XYZ from TEST.http_options. For more details see the topic Using the Options Query Property.

Reading from WebSockets Using the ECD Agent

You can use the ECD agent to read data from remote locations. See Reading Data from Remote Locations for more details.

The ECD agent takes similar options as the ones you format in SQL, but these options need to be formatted in a properties file along the lines of the following.

PARSER=CSV
URL=wss://listener.myserver.com/84be767d-bc36-4d24-82c2-fa6c88e00c1b
HEADER_Connection=Upgrade
HEADER_Authorization=token d90621ca-9601-4a49-96f3-160f61082c34',
HEADER_Upgrade=websocket
HEADER_Host=listener-myserver.com
HEADER_Origin=listener-myserver.com
SCHEMA_NAME=MY_SCHEMA
TABLE_NAME=WEBSOCKETREADER_STREAM
ROWTYPE=RECORDTYPE(VARCHAR(2040) id, VARCHAR(2040) reported_at, VARCHAR(2040) shift_no, VARCHAR(2040) trip_no, VARCHAR(2040) route_variant_id)

Input Format

The code sample above uses CSV as a format. To use other file options, see the Input Formats for Reading topic in this guide.

Writing to WebSockets

You can use Guavus SQLstream to write data out of s-Server over WebSockets. s-Server sends data over WebSockets in any of the output formats for writing. We support all standard WebSocket headers. s-Server keeps a WebSocket connection open as long as the associated foreign stream is being INSERTed into.

The WebSocket adapter and agent send one row at a time. You can configure all standard WebSocket headers using the adapter or agent.

To write from local locations, you configure and launch the adapter in SQL, using either server or foreign stream/table options. See Writing to WebSockets Using SQL below. To write from remote locations, you configure such options using a properties file and launch the agent at the command line. See Writing to WebSockets Using the ECD Agent below.

Many of the options for the ECD adapter and agent are common to all I/O systems. The CREATE FOREIGN STREAM topic in the SQLstream SQL Reference Guide has a complete list of options for the ECD adapter.

Note On Writing Pumps

Because of the nature of streaming data, you will need to set up a pump in order to move rows continually from an s-Server stream to another stream, file, Kafka topic, RDBMS table or other location. Pumps are INSERT macros that continually pass data from one point in a streaming pipeline to the other. A model for setting up a pump is provided below. See the topic CREATE PUMP in the s-Server Streaming SQL Reference Guide for more details.

Writing to WebSockets Using SQL

To write data to a web socket, you need to create a foreign stream that references a prebuilt server object called WEBSOCKET_SERVER.

You will also need to specify a formatter for the foreign stream. Specifying "formatter" as a foreign stream option tells s-Server that this foreign stream writes data. See Output Formats for Writing in this guide for more details.

The following code first creates a schema in which to run the rest of the sample code below, then creates a foreign stream named "WebSocketStream".

CREATE OR REPLACE SCHEMA "WebSocketSchema";
SET SCHEMA '"WebSocketSchema"';

CREATE FOREIGN STREAM "WebSocketStream"
("id" VARCHAR(2040),
"reported_at" VARCHAR(2040),
"shift_no" VARCHAR(2040),
"trip_no" VARCHAR(2040)'
"route_variant_id" DOUBLE)
SERVER WEBSOCKET_SERVER
OPTIONS (
        FORMATTER 'JSON',
        --This needs to be a web socket accessible from s-Server
        "URL" 'wss://listener-streamer-services-poc.labs.myserver.com/84be767d-bc36-4d24-82c2-fa6c88e00c1b',
        "HEADER_Connection" 'Upgrade',
        "HEADER_Authorization" 'token d90621ca-9601-4a49-96f3-160f61082c34',
        "HEADER_Upgrade" 'websocket',
        "HEADER_Host" 'listener-myserver.com',
        "HEADER_Origin" 'listener-myserver.com'
    );

Again, to get data moving, you need to create and start a pump. You do so with code along the following lines:

CREATE OR REPLACE SCHEMA "Pumps";
SET SCHEMA '"Pumps"';

CREATE OR REPLACE PUMP "writerPump" STOPPED AS
--We recommend creating pumps as stopped
--then using ALTER PUMP "Pumps"."writerPump" START to start it
INSERT INTO "WebSocketWriterSchema"."WebSocketWriterStream"
SELECT STREAM * FROM "MyStream";
--where "MyStream" is a currently existing stream

To start writing data, use the following code:

ALTER PUMP "Pumps"."writerPump" START;

Foreign Stream Options for Writing to WebSockets

Format Name Name
URL URL for web socket.
HEADER_<name_of_header> Tells Web Socket writer to add a header called <name_of_header> to the request.
OPTIONS_QUERY Optional. Lets you query a table to update one or more adapter options at runtime. You can use this, for example, to set HEADER_xxx options using select HEADER_ABC, HEADER_XYZ from TEST.http_options. For more details see the topic Using the Options Query Property.

Writing to WebSockets Using the ECD Agent

The ECD agent takes similar options, but these options need to be formatted in a properties file along the lines of the following. These properties correspond to those defined for the adapter above.

# Port, format, host
FORMATTER=CSV
URL=wss://listener-streamer-services-poc.labs.myserver.com/84be767d-bc36-4d24-82c2-fa6c88e00c1b
HEADER_Connection=Upgrade
HEADER_Authorization=token d90621ca-9601-4a49-96f3-160f61082c34
HEADER_Upgrade=websocket
HEADER_Host=listener-myserver.com
HEADER_Origin=listener-myserver.com
SCHEMA_NAME='SOCKETWRITER'
TABLE_NAME=WebSocketStream
ROWTYPE=RECORDTYPE(VARCHAR(2040) id, VARCHAR(2040) reported_at, VARCHAR(2040) shift_no, VARCHAR(2040) trip_no, DOUBLE route_variant_id)