Network Socket Adapter (NS Adapter)

<< Click to Display Table of Contents >>

Navigation:  Integrating SQLstream Blaze with Other Systems > Appendix A: Legacy Adapters and Agents >

Network Socket Adapter (NS Adapter)

Previous pageReturn to chapter overviewNext page

Note: This topic describes a legacy adapter. See the topics Reading from Other Sources and Writing to Other Destinations for current solutions.

The Network Socket Adapter ("Socket Adapter") receives data streamed from a client program using TCP or UDP. The adapter listens at a configurable port awaiting client connections. Incoming packets are streamed as described below.

UDP Data Handling

By default, incoming UDP packets are streamed one per row. If the packet is longer than the target MESSAGE column, the excess data is discarded. If an optional record separator pattern is supplied the incoming packet is divided into multiple records and each record is streamed in its own row.

When the socket is closed any buffered unstreamed data is streamed in one or more final rows.

TCP Data Handling

By default, incoming TCP packets are streamed as multiple rows, where the size of the target MESSAGE column determines how much data is streamed with each row. Incoming TCP packet data accumulates until the MESSAGE column is filled. Additional incoming data supplies subsequent rows, with each row being streamed once its MESSAGE column is filled.

If an optional record separator pattern is supplied the incoming packets are divided into multiple records and each record is streamed in its own row whether or not it completely fills MESSAGE.

When the socket is closed any remaining data is streamed whether or not it completely fills MESSAGE.

Data Format

A client can send data of any format, including raw binary data from sensors, to the Socket Adapter in either TCP or UDP mode. Multibyte record separators are recommended to prevent inadvertently matching a non-separator data byte. The message column can be either VARCHAR or VARBINARY depending on the downstream processing required. Typically a custom UDX is required to unpack and interpret binary sensor data payloads and to stream the resulting sensor readings as one or more rows for downstream analysis.

Record Separator Patterns

The record separator pattern is a Java regular expression that can include multibyte separators (useful for binary data) and OR'ed patterns as long as all OR'ed patterns are the same length.

Registering the plugin

The Network Socket Adapter is registered by default during the SQLstream installation process.

You can verify that it is present under the $SQLSTREAM_HOME/s-Server Plugins folder as NSAdapter.jar. Use CREATE OR REPLACE FOREIGN DATA WRAPPER to install the Network Socket adapter. (See Sample Code(installing).

Note: $SQLSTREAM_HOME refers to the installation directory for s-Server, such as /opt/sqlstream/5.0.XXX.

Define the Server

Define a Server for using the CREATE OR REPLACE SERVER command. (See Sample Code (defining server).)

Define the Foreign Stream

The Foreign Stream can be defined with one or two columns of data. The data columns are IP varchar (ip address of the source), MESSAGE (text of the message) must be varchar or varbinary. (See Sample Code (defining foreign stream).)

SQL/MED options

The following table shows the options that can be defined as part of Server Definition or Foreign Stream.
 

Stream options

Option

Description

Possible Values (default in bold)

COLUMN NAME

Socket Adapter can be used with one or two columns defined, using the column names "message" or "ip".
 

The type must be declared: it can be VARCHAR or VARBINARY.

MESSAGE

TYPE VARCHAR or TYPE VARBINARY

 
IP TYPE VARCHAR
(If less than 15 characters, a runtime error can result.)

MAX_CONNECTIONS

Maximum number of threads used to create connect pool.

5

Must be greater than zero

BUFFER_COUNT

Number of buffers to use

for outstanding input messages

10

Must be greater than zero

BUFFER_SIZE

Size of buffer area which is reserved to hold data in transit

Buffer size must be between min and max.

MAX = 1024*60

MIN=1024

DEFAULT = 1024

Troubleshooting

The following lines can be added to the /var/log/sqlstream/Trace.propertiesTrace.properties to enable tracing for the NS Adapter.

com.sqlstream.plugin.nsa.level = [INFO,WARNING,SEVERE,FINE,FINER,FINEST]

com.sqlstream.plugin.nsa.data.level= [INFO,WARNING,SEVERE,FINE,FINER,FINE]

 

Tracing with .data shows what is inside the packet itself; without .data it shows information about the packet.

Sample Code

Installing the Network Socket Adapter

-- Install the network socket adapter

CREATE OR REPLACE FOREIGN DATA WRAPPER "SocketWrapper"

   LIBRARY 'class com.sqlstream.plugin.nsa.NSAdapter'

   LANGUAGE JAVA;

Defining an external server

--Define the external server

CREATE OR REPLACE SERVER "SocketServer"

   FOREIGN DATA WRAPPER "SocketWrapper";

Defining Foreign Streams

--Example Foreign Streams

--Define a TCP stream with 2 columns and 2 character record separator

CREATE OR REPLACE FOREIGN STREAM "RadarData" (

   ip VARCHAR(15) NOT NULL,

   message VARCHAR(4096) NOT NULL)

   SERVER "SocketServer"

   OPTIONS (

       type 'reader',

       port '9000',

       protocol 'tcp',

       buffer_count '100',

       buffer_size '1024',

       -- override default UTF-8 encoding which would damage binary data

       encoding 'ISO-8859-1',

       record_separator_pattern u&'\00FF\00AA'

   ) DESCRIPTION 'Binary character payload with 2 character record separator ';

-- Define a UDP stream with two columns and a single NL record separator

CREATE OR REPLACE FOREIGN STREAM "RecordUdpStream" (

   ip VARCHAR(15) NOT NULL,

   message VARCHAR(1024) NOT NULL)

   SERVER "SocketServer"

   OPTIONS (

       type 'reader',

        port '8890',

       protocol 'udp',

        record_separator_pattern u&'\000A'

        )DESCRIPTION '2 Column UDP Stream';

Example

Here's an example using the other options through the adapter.

CREATE OR REPLACE FOREIGN STREAM "RawIncomingDataStream" (

    ip VARCHAR(15) NOT NULL,

    message VARBINARY(5120) NOT NULL)

    SERVER "SocketReader"

    OPTIONS (

        type 'reader',

        port '8871',

        protocol 'tcp',

        buffer_count '100',

        buffer_size '5120',

        record_fixed_size '2',

        record_variable_size_offset '0',

        record_variable_size_bytes '2',

        record_variable_size_bigendian 'true',

        record_variable_size_min '16',

        record_variable_size_max '5120',

        record_start_pattern u&'\0002',

        record_end_pattern u&'\0003'

    ) DESCRIPTION 'Command packets, as binary payload with a 2-byte

(0x02, aka STX) separator';

 

 
RECORD_START_PATTERN and RECORD_END_PATTERN can be specified instead of RECORD_SEPARATOR_PATTERN to delimit messages.

Here's the record format this is describing:

Packet Format

Command Packets

A packet in this protocol (in each direction) consists of two sections.

The first section is common to all messages and consists of fixed length fields. The fields within this section are shown in white in the table below.

The second section (shown in grey) is variable in length, based on the message type. This section may contain a number of pre-specified fields, in which case these fields will be variable length text separated by commas.

Note that all values within these fields must be represented and interpreted in Network Byte Order. This is defined as "Big Endian" within the TCP/IP Protocol suite.

The general command packet format consists of the following fields:

Start

Length

Time Stamp

Field Device ID

Message Type

Message Body

 

End

 

The following table describes the size and content of each field:

Format

Size

Comments/Value

Encoding

Start

1 Byte

<STX> 0x02

Binary

Length

2 Bytes (short integer)

Length of the total message excluding Start, Length and End.

Binary

Time Stamp

8 bytes (a long integer)

UTC time – the number of milliseconds since GMT 00:00:00, January 1, 1970. NB: Time within a field device is to be kept synchronised with the GPS time while a valid GPS connection is maintained.

Binary

Field Device ID

6 Bytes

The field device Id is a unique identifier associated with the field device itself. This is the 48-bit MAC Address (MAC-48) of the field device NIC, as defined by the Ethernet standard [3].

Binary

Message Type

2 Bytes

The first character is the command while the second character is a sub-command or response.

US-ASCII

Message Body

Depends on message Type but maybe zero length.

Optional. Message fields are comma separated

US-ASCII

End

1 Byte

<ETX> 0x03

Binary