Writing Data Out of s-Server

This page contains the following topics:

Once you have analyzed, enriched, or otherwise modified data, you will often want to write this data out of s-Server into other locations, or “sinks”. This integration guide describes a number of ways to egress data. All data egress moves through an s-Server foreign stream*. A foreign stream is an s-Server object that serves as a “tank” to hold streaming data. Once you have established a foreign stream, you can pump data into the foreign stream in order to move it into the external location.

There are two basic categories for writing data out of s-Server:

  • Writing data into RDBMS locations (or sinks). You can use s-Server to write directly to RDBMS tables. See Writing Data to RDBMS Sources in this guide.
  • Writing data to all other locations using the Extensible Common Data framework. This framework lets you identify a location and write data to it in one of four formats: CSV, Avro, XML, JSON, or BSON.

Configuring Output

For all of the above sources, you can configure input in three ways. This guide focuses on the third option, creating a foreign stream by hand in SQL, but the principles described within apply to both StreamLab and s-Studio.

  • Using StreamLab, a graphical interface, to set up a connection with a source. For more details, see Adding Sources in StreamLab in the StreamLab Guide. (You can still connect with sources that you’ve created in StreamLab using SQL.)
  • Creating a foreign stream in s-Studio, SQLstream’s Integrated Development Environment. For more details, see Creating Objects in s-Studio in the s-Studio Guide.
  • Creating a foreign stream by hand in SQL, then running this SQL in s-Server using sqlline. This process is described below under topics for each source. The SQLstream Streaming SQL Reference Guide provides configuration details in the topic CREATE FOREIGN STREAM. For information on using sqlline, see the Streaming SQL Reference Guide.

Writing to Other Destinations

Drawing on data in s-Server, the Extensible Common Data file writer writes data in a variety of forms into designated locations. See Output Formats for Writing for information on output formats.

  • Writing to the File System
  • Writing from Sockets
  • Writing to AMQP
  • Writing to Kafka
  • Writing over HTTP
  • Writing to WebSockets
  • Writing to Amazon Kinesis
  • Writing to Snowflake
  • Writing to MongoDB
  • Writing to Mail Servers
  • Writing to Hadoop

To write to files with the Extensible Common Data Adapter, you set up an adapter instance through SQL. To write to files with the Extensible Common Data agent, you launch the agent from its directory at the command line, using the –output flag.

All adapter or agent implementations involve configuring options. 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.

Output Formats for Writing

In s-Server, the destination for data writing and format for data are separate, meaning you can, for example, write JSON formatted data to the file system, a network socket, AMQP, Kafka, Amazon Kinesis, Snowflake, MongoDB, over HTTP, and to a WebSocket. s-Server supports the following data formats:

  • CSV
  • XML
  • JSON
  • BSON (new in 5.2)

You set data format through the FORMATTER parameter of the foreign stream for a data source. For example, the following AMQP foreign stream formats data as CSV.

CREATE OR REPLACE FOREIGN STREAM amqp_stream (
line VARCHAR(4096))
SERVER AMQPSERVER
OPTIONS (DESTINATION 'amq.topic',
CONNECTION_URL 'amqp://guest:guest@clientid/?brokerlist=''tcp://localhost:5672'',
FORMATTER 'CSV');

To change formatting to XML, enter FORMATTER ‘XML’, and so on.