Reading over HTTP

<< Click to Display Table of Contents >>

Navigation:  Integrating SQLstream Blaze with Other Systems > Reading Data into s-Server > Reading from Other Sources  >

Reading over HTTP

Previous pageReturn to chapter overviewNext page

You can read data over HTTP using the Extensible Common Data Adapter (ECDA) or ECD Agent. This feature is new in s-Server 5.2.

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 Streaming SQL Reference Guide has a complete list of options for the ECD adapter.

To read over HTTP, you need to create a server object which references the data wrapper ECDA and is of type 'HTTP'.

CREATE OR REPLACE SERVER "HTTPReaderServer" TYPE 'HTTP'

FOREIGN DATA WRAPPER ECDA;

 

Note: ECD Adapter server definitions need to reference the ECD foreign data wrapper. You can do so with the syntax FOREIGN DATA WRAPPER ECDA.

Unlike server objects, all foreign streams need to be created in 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 "HTTP_ReaderStream"

("recNo" INTEGER,

"ts" TIMESTAMP,

"accountNumber" INTEGER,

"loginSuccessful" BOOLEAN,

"sourceIP" VARCHAR(32),

"destIP" VARCHAR(32),

"customerId" INTEGER)

SERVER "HTTPReaderServer"

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'

   )

;

 

 

Using the ECD Agent to Read Over HTTP

You can read data from remote locations using the Extensible Common Data Agent. See Reading Files in Remote Locations for more details. 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.

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.