Integrating HTTP

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

This topic contains the following subtopics:

Reading over 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. These errors are also logged in the Global Error Stream.

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.

Example 1: Reading Login Attempts

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
)
SERVER HTTP_SERVER
OPTIONS (
    parser 'CSV',
    "URL" 'http://stream.mysite.com/2/rsvps',
    "POLL_IN_MILLIS" '1000'
);

Example 2: Reading from OpenWeathermaps

This example shows how to poll the openweathermap.org API.

You will need to get your own appid from openweathermap.org/api. There are subscription options which allow you to make a limited number of api calls at no cost. Make sure you understand what costs your calls might incur before selecting from "HTTP_ReaderStream".

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" '$',
    "URL" 'http://api.openweathermap.org/data/2.5/weather?id=4509884&appid=ccdf5e55c3c13491cb8e2ee154386dee',
    "POLL_IN_MILLIS" '600000'
)
;

Foreign Stream Options for Reading Over HTTP

Option Description
URL URL for HTTP feed.
HEADER_<name_of_header> Tells HTTP reader to add a header to the request called <name_of_header>, with the option value as the value.
PARAM_<name_of_param> Tells HTTP reader to add a query parameter to the request called <name_of_param> with the option value as the value.
POLL_IN_MILLIS How often to request new data, in milliseconds.
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 and PARAM_xxx options using select HEADER_ABC, PARAM_XYZ from TEST.http_options. For more details see the topic Using the Options Query Property.

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)

The code sample above expects to parse input CSV data. To use other input formats, see the Parser Types 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 writer to add a header to the request called <name_of_header>, with the option value as the value.
PARAM_<name_of_param> Tells HTTP writer to add a query parameter to the request called <name_of_param> with the option value as the value.
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 and PARAM_xxx options using select HEADER_ABC, PARAM_XYZ from TEST.http_options. For more details see the topic Using the Options Query Property.

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)

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