Integrating Sockets

Using s-Server, you can read from and write to network sockets.

This topic contains the following subtopics:

Reading from Sockets

Configured for a socket, the ECD Adapter and Agent (ECDA) reads or writes data streamed from a client program using TCP or UDP. The adapter listens or inserts at a configurable port awaiting client connections. Incoming packets are streamed as described 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 Sockets 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 Sockets 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.

See Using the Extensible Common Data Framework for more details.

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 Sockets Using SQL

To read from a network socket, you need to create a foreign stream in SQL that references a prebuilt server object called NET_SERVER. In the foreign stream's options, you configure how s-Server connects to a network socket. For more information on creating foreign streams, see the topic CREATE FOREIGN STREAM in the Streaming SQL Reference Guide.

You will also need to specify a parser for the foreign stream. Specifying "parser" as a foreign stream option tells s-Server that this foreign stream reads data. See Parsers for Reading in this guide for more details.

Streams, like most SQL objects (but unlike data wrappers and servers), must be created within a schema.

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

CREATE OR REPLACE SCHEMA NetworkSocketSchema;
SET SCHEMA 'NetworkSocketSchema';

CREATE OR REPLACE FOREIGN STREAM NetworkSocketStream
("recNo" INTEGER,
"ts" TIMESTAMP,
"accountNumber" INTEGER,
"loginSuccessful" BOOLEAN,
"sourceIP" VARCHAR(32),
"destIP" VARCHAR(32),
"customerId" INTEGER
)
--Columns for the new stream
SERVER NET_SERVER
OPTIONS
( parser 'CSV'
);

Foreign Stream Options for Reading from Sockets

The ECD framework can act as a server or client. When it acts a client, set REMOTE_HOST and REMOTE_PORT. When it acts a server, set SERVER_PORT and if desired SERVER_HOST.

Option Description
IS_IPV6 Whether or not the socket uses IPV6. Default is false.
PROTOCOL Whether the socket uses TCP or UDP. Default is TCP.
REMOTE_HOST Hostname to receive tuples from, when ECDA is acting as a client. You can override this to 'LOCALHOST' to listen to only local connections, or a specific ip address, such as 168.212.226.204. When you specify REMOTE_HOST and REMOTE_PORT, this tells the ECD socket code to start the network connection as a client.
REMOTE_PORT Port to sreceive tuples from when ECDA is acting as a client. REMOTE_ and SERVER_ tells ECDA's socket code how to start the network connection (as a server or a client).
SERVER_HOST The hostname or IP address to listen upon to receive tuples, when ECDA is acting as a server (defaults to 0.0.0.0). When you specify SERVER_HOST and SERVER_PORT, this tells the ECD socket code to start the network connection as a client.
SERVER_PORT the port to listen upon to receive tuples when ECDA is acting as a server.
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 the REMOTE_HOST and REMOTE_PORT options using select REMOTE_HOST, REMOTE_PORT from TEST.socket_options. For more details see the topic Using the Options Query Property.

Using Provenance Columns with the Sockets plugin

In reading from network sockets, you can declare provenance columns. These return metadata for the network socket from which you are reading.

These are as follows:

Data Type Name Value
VARCHAR(1024) SQLSTREAM_PROV_SOCKET_SOURCE_HOST Returns host name for socket.
INTEGER SQLSTREAM_PROV_SOCKET_SOURCE_PORT Returns port for socket.

Reading from Sockets 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.

#Port, format, host
PARSER=CSV
SERVER_PORT=5601
CHARACTER_ENCODING=UTF-8
#Schema, name, and parameter signature of origin stream
SCHEMA_NAME=SOCKETREADER
TABLE_NAME=NetReaderStream
ROWTYPE=RECORDTYPE(VARCHAR(2040) id, VARCHAR(2040) reported_at, VARCHAR(2040) shift_no, VARCHAR(2040) trip_no, VARCHAR(2040) route_variant_id)

Writing to Sockets

To write to files to a socket, you use the Extensible Common Data Adapter (ECDA) or ECD Agent. To write data, you first define a server object with connection information. Once you define this server object, you can write to the file system by referencing it*. See the topic - CREATE SERVER* in the s-Server Streaming SQL Reference Guide for more details.

For adapters, you configure and launch the adapter in SQL, using either server or foreign stream/table options. For agents, you configure such options using a properties file and launch the agent at the command line. 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 Streaming 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 Sockets Using SQL

To write to a network socket, you need to create a foreign stream in SQL that references a prebuilt server object called NET_SERVER. In the foreign stream's options, you configure how s-Server connects to a network socket. This foreign stream contains connection information for the socket, such as format type, port, character encoding, whether or not to write a header, and the host name.

For more information on creating foreign streams, see the topic CREATE FOREIGN STREAM in the Streaming SQL Reference Guide.

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.

Streams, like most SQL objects (but unlike data wrappers and servers), must be created within a schema

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

CREATE OR REPLACE SCHEMA NetWriterSchema
SET SCHEMA 'NetWriterSchema';

CREATE OR REPLACE FOREIGN STREAM NetWriterStream
("recNo" INTEGER,
"ts" TIMESTAMP,
"accountNumber" INTEGER,
"loginSuccessful" BOOLEAN,
"sourceIP" VARCHAR(32),
"destIP" VARCHAR(32),
"customerId" INTEGER)
--Columns for the new stream
SERVER NET_SERVER
OPTIONS
(formatter 'CSV',
server_port '5601',
character_encoding 'UTF-8'
);

To begin writing data to a socket, you INSERT into NetWriterSchema.NetWriterStream. When NetWriterSchema.NetWriterStream receives rows, s-Server writes data to the socket you have configured in the foreign stream options.

In most cases, you will want to set up a pump that writes data to NetWriterSchema.NetWriterStream.

Pumps are INSERT macros that continually pass data from one point in a streaming pipeline to the other. See the topic CREATE PUMP in the s-Server Streaming SQL Reference Guide for more details.

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

--We recommend creating pumps as stopped
--then using ALTER PUMP "Pumps"."writerPump" START to start it

CREATE OR REPLACE PUMP writerPump STOPPED AS
INSERT INTO NetWriterSchema.NetWriterStream
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 Sockets

The ECD framework can act as a server or client. When it acts a client, set REMOTE_HOST and REMOTE_PORT. When it acts a server, set SERVER_PORT and if desired SERVER_HOST.

Name Description
IS_IPV6 Whether or not the socket uses IPV6. Default is false.
PROTOCOL Whether the socket uses TCP or UDP. Default is TCP.
REMOTE_HOST Hostname to send tuples to when ECDA is acting as a client. You can override this to 'LOCALHOST' to listen to only local connections, or a specific ip address, such as 168.212.226.204. When you specify REMOTE_HOST and REMOTE_PORT, this tells the ECD socket code to start the network connection as a client.
REMOTE_PORT Port to send tuples to when ECDA is acting as a client. REMOTE_ and SERVER_ tells ECDA's socket code how to start the network connection (as a server or a client).
SERVER_HOST The hostname or IP address to listen upon to send tuples, when ECDA is acting as a server (defaults to 0.0.0.0). When you specify SERVER_HOST and SERVER_PORT, this tells the ECD socket code to start the network connection as a client.
SERVER_PORT the port to listen upon to send tuples when ECDA is acting as a server.
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 the REMOTE_HOST and REMOTE_PORT options using select REMOTE_HOST, REMOTE_PORT from TEST.socket_options. For more details see the topic Using the Options Query Property.

Writing to Sockets 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
SERVER_PORT=5601
CHARACTER_ENCODING=UTF-8
# Schema, name, and parameter signature of origin stream
SCHEMA_NAME=SOCKETWRITER
TABLE_NAME=NetWriterStream
ROWTYPE=RECORDTYPE(VARCHAR(2040) id, VARCHAR(2040) reported_at, VARCHAR(2040) shift_no, VARCHAR(2040) trip_no, VARCHAR(2040) route_variant_id)