Integrating HTTP

Using s-Server, you can read from and write over HTTP.

This topic contains the following subtopics:

Reading from HTTP

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

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.

The s-Server trace log includes information on readers' and parsers' progress. See Periodic Parser Statistics Logging in the Administering Guavus SQLstream guide.

See Using the Extensible Common Data Framework for more details.

Reading Over HTTP Using SQL

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

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), should 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 HTTPReaderStream.

CREATE OR REPLACE SCHEMA HTTPSource;
SET SCHEMA 'HTTPSource';

CREATE OR REPLACE FOREIGN STREAM HTTPReaderStream
("recNo" INTEGER,
"ts" TIMESTAMP,
"accountNumber" INTEGER,
"loginSuccessful" BOOLEAN,
"sourceIP" VARCHAR(32),
"destIP" VARCHAR(32),
"customerId" INTEGER)
--Columns for the new stream
SERVER HTTP_SERVER
OPTIONS (
	 parser 'CSV',
        "URL" 'http://stream.mysite.com/2/rsvps',
        "POLL_IN_MILLIS" '1000'
    );

Example 2: Open Weathermaps

CREATE OR REPLACE SERVER "HTTPReaderServer" TYPE 'HTTP'
FOREIGN DATA WRAPPER ECDA;

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

CREATE OR REPLACE FOREIGN STREAM "HTTP_ReaderStream"
("weather" VARCHAR(400))
SERVER "HTTPReaderServer"
OPTIONS (

"PARSER" 'JSON',

"ROW_PATH" '$',
--Note: You will need to get your own appid from openweathermap.org

"URL" 'http://api.openweathermap.org/data/2.5/weather?id=4509884&appid=ccdf5e55c3c13491cb8e2ee154386dee',
"POLL_IN_MILLIS" '600000'
)
;

Reading over HTTP 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=http://stream.mysite.com/2/rsvps
POLL_IN_MILLIS=1000
SCHEMA_NAME=HTTPSOURCE
TABLE_NAME=HTTPREADER_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 Over HTTP

You can use Guavus SQLstream to write data out of s-Server over HTTP. s-Server sends data over HTTP in any of the output formats for writing one row at a time. We support all standard HTTP headers

Note: You can also connect to an HTTP server through StreamLab. See "Adding a Streaming Sink" in the StreamLab Guide.

To write from local locations, you configure and launch the adapter in SQL, using either server or foreign stream/table options. See Writing Over HTTP 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 Over HTTP Using the ECD Agent below.

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 Over HTTP Using SQL

To configure s-Server to write over HTTP using SQL, you need to create a foreign stream that references the server 'HTTP_SERVER'. This server is preconfigured to write over HTTP.

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), should be created within a schema. The following code first creates a schema called HTTPschema in which to run the rest of the sample code below, then creates a foreign stream named _HTTPwriterStream.

CREATE OR REPLACE SCHEMA HTTPschema;

-- Explicitly list the columns for message fields.
CREATE OR REPLACE FOREIGN STREAM HTTPschema.HTTP_writerStream
    (    order_time TIMESTAMP, --Time order was reported.
    amount INTEGER, --amount of order.
    ticker VARCHAR(100) --ticker.
    )
    --predefined server for HTTP
    SERVER HTTP_SERVER
    OPTIONS (
    "FORMATTER" 'JSON',
    "URL" 'https://localhost:3000/',
    "HEADER_User-Agent" 'curl/7.16.3 (i686-pc-cygwin) libcurl/7.16.3 OpenSSL/0.9.8h zlib/1.2.3 libssh2/0.15-CVS',
    "HEADER_username" 'myusername',
    "HEADER_password" 'mypassword',
    "HEADER_content-type" 'text/plain,
    "DATE_FORMAT" 'mm/dd/yyyy'
    );


Foreign Stream Options for Writing Over HTTP

Format Name Name
URL URL for HTTP feed.
HEADER_<name_of_header> Tells HTTP reader to look for a header called .

Writing Over HTTP 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.

FORMATTER=CSV
URL=http://stream.mysite.com/2/rsvps
HEADER_User-Agent:curl/7.16.3 (i686-pc-cygwin) libcurl/7.16.3 OpenSSL/0.9.8h zlib/1.2.3 libssh2/0.15-CVS
POLL_IN_MILLIS=1000
SCHEMA_NAME=HTTPSOURCE
TABLE_NAME=HTTPWRITER_STREAM
ROWTYPE=RECORDTYPE(VARCHAR(2040) id, VARCHAR(2040) reported_at, VARCHAR(2040) shift_no, VARCHAR(2040) trip_no, VARCHAR(2040) route_variant_id)

Output Format

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