Running Web Agent

<< Click to Display Table of Contents >>

Navigation:  Using s-Dashboard > s-Dashboard Overview >

Running Web Agent

Previous pageReturn to chapter overviewNext page

The s-Server Web Agent accepts certain HTTP requests which represent sql queries, and send the queries to an s-Server. The agent replies with the query results, as json objects.

The Web Agent functions as a simple, miniature Web server, and is an integral part of SQLstream StreamLab, s-Dashboard, and s-Visualizer. (See json.org for more details on JSON objects.) SQLstream WebAgent is delivered as part of the SQLstream Client Tools, in the shell script webagent.sh.

To run WebAgent, you initiate its shell script along the following lines:

webagent.sh –-port <port number> -a -w

 

When run with no arguments, the WebAgent runs on port 5580 and uses "localhost" and 5570 as the default host and port for the JDBC connection to s-Server. Each request to WebAgent can specify the s-Server host and port, so a single instance of WebAgent can communicate with multiple s-Server instances. See Parameters Common to All Requests Below.

The script accepts the arguments described below.

If the –a flag is given, the server provides a monitoring & testing web application viewable by pointing a HTML5-capable browser at the root (such as http://localhost:5580). See Using the WebAgent Test Tool. Otherwise, visiting / returns the same as /status. The /sqlstream and /write requests are disabled by default. You enable them using the –w flag. This flag must be specified at startup. When running Web Agent as a service, both the test tool and writing to Web Agent can be enabled through a script located at /etc/default/webagentd. See Running Webagentd as a Service for more details.

Browser Requirements

In order to run the \read \write or \sqlstream APIs, you need to be running one of the following browsers: Microsoft Internet Explorer 11 (or later), Google Chrome 31 (or later), Mozilla Firefox 17 (or later), or Apple Safari 7 (or later).

Webagent.sh Command-Line Options

Option

Alias

Description

-a

--enable-app

Enables Web Agent Test tool. See Using the WebAgent Test Tool for more details.

-p <port>

--port <port>

Selects WebAgent port. Default is 5580

-sn <user>

--SQLstream-name <user>

Specifies s-Server user name, default is “”

-sp <password>

--SQLstream-password <password>

Specifies s-Server password, default is “”

-su <url>

--SQLstream-url <url>

Specifies s-Server JDBC URL, default is “jdbc:sqlstream:sdp://localhost:5570”

-w

--enable-write

Enables writable services (/sqlstream and /write)

-g <port>

--debug <port>

Sets a debug port for WebAgent

-v

--verbose

Enables verbose mode

 

--help

Shows help message

-D<java property>

 

Sets a java property, such as:

webagent.sh -Dwebagent.stream.fetchTimeout=30

or

webagent.sh -Dwebsocket.maxwaitforwrite=N (where N is seconds. Default is 10 seconds)

Requests

All of the requests below are appended to an http request, as in

http://myserver:5580/status

 

where "myserver:5580"

/status
/getmetadata
/getcolumns
/get
/getcontinuous
/sqlstream
/read
/write

Parameters Common to All Requests

Name

Type

Default

Description

host

string

localhost

s-Server hostname or IP

port

number

5570

s-Server port

format

string

json

Format for request. Currently json is only option.

callback

string

none

jsonp callback function name

/status

Requests a status update from WebAgent itself. Includes the amount of memory it is using and lists of the active threads, WebAgent sessions, and SQLstream connections.

Reply

{“message”: <status message>,
“allocatedMemory”: <total memory in the JVM>,

“maxMemory”: <maximum memory available to the JVM>,

“freeMemory”: <free memory in the JVM>,

“threads”: [<list of thread names>],

“sessions”: [<list of WebAgent session IDs>],

“connections”: [<list of SQLstream connections>]

}

Example

Request

http://myserver:5580/status

Reply

{"message":"OK","maxMemory":129957888,"allocatedMemory":85000192,"freeMemory":78921232,"threads":["main","Poller SunPKCS11-Darwin","HashSessionScavenger-0","qtp1405643749-13 Acceptor0 SocketConnector@0.0.0.0:5580 STARTED","qtp1405643749-14","qtp1405643749-15","qtp1405643749-16","qtp1405643749-17","qtp1405643749-18","qtp1405643749-19","qtp1405643749-20"],"sessions":[],"connections":[]}

/getmetadata

Requests metadata for the contents of the SQLstream catalog. Replies with a list of the SQL objects present, either for the entire catalog or for a specified schema.

Parameters

Name

Type

Default

Description

schema

string

all schemas

restrict output to given schema

Reply Format

{“host”: <SQLstream host>,
“port”: <SQLstream port>,
“errorcode”: <error code>,
“SQLstate”: <SQL state code>,
“exceptionClass”: <exception thrown>,
“message”: <error message>,
“sqlobjects”: [
 {“schema”: <schema name>,
      “name”: <object name>,
      “type”: <type name>
 },
 … ]
}

Example

Request

http://myserver:5580/getmetadata

 

Reply

{"host":"myserver","port":5570,"errorCode":0,"SQLState":"00000","exceptionClass":"","message":"","sqlobjects":[{"schema":"AccessLog","name":"FrequentLocationsDescCSV","type":"FOREIGN STREAM"},{"schema":"AccessLog","name":"HitsPerHourCSV","type":"FOREIGN STREAM"},{"schema":"AccessLog","name":"HitsPerLocationCSV","type":"FOREIGN STREAM"},{"schema":"AccessLog","name":"LogStreamLFAd","type":"FOREIGN STREAM"},{"schema":"AccessLog","name":"LocationStream_","type":"STREAM"},{"schema":"AccessLog","name":"LogStreamRaw","type":"STREAM"},{"schema":"AccessLog","name":"LogStreamValid","type":"VIEW"},{"schema":"AccessLog","name":"LogStreamWithLocation","type":"VIEW"},]}

/getcolumns

Requests metadata for the columns of a SQLstream object.

Note: the precision and scale values returned for numerical columns are not correct. As an alternative, you can use /getcontinuous to get the column info and then close the session immediately with /endcontinuous.

Parameters

Name

Type

Default

Description

schema

string

required

schema name

sqlobject

string

required

SQL object (stream, view, table, etc.)

Reply Format

“columns”: [
 {“name”: <column name>,
      “index”: <column index (starts with 1)>,
      “type”: <SQL type of column>,
      “precision”: <SQL precision>,
      “scale”: <SQL scale>,
      “nullable”: <true if column accepts nulls>
 },
 … ]

Example

Request

http://myserver:5580/getcolumns?schema=SALES&sqlobject=BIDS

Reply

{"columns":[{"name":"time","index":1,"type":"BIGINT","precision":0,"scale":0,"nullable":true},{"name":"ticker","index":2,"type":"VARCHAR","precision":5,"scale":0,"nullable":true},{"name":"shares","index":3,"type":"INTEGER","precision":0,"scale":0,"nullable":true},]}

 

/get

Retrieves the contents of a static table.

If includecols is false (default) the rows will be output as an array of arrays, one per row. If true, the rows will be an array of objects, each with properties matching the column names for the stream.

Parameters

Name

Type

Default

Description

schema

string

required

schema name

sqlobject

string

required

SQL object (table, view, etc.)

includecols

Boolean

false

Include column names in rows; rows will be objects instead of arrays

timeout

int

15

Optional. Timeout can be by this parameter for a single /get request, if no parameter has been set by the java property webagent.stream.fetchTimeout

Reply Format

If includecols is false:

   [[row as array], …]

If includecols is true:

   [{row as object}, …]

Example

Request

http://myserver:5580/get?schema=SALES&sqlobject=EMPS

Reply

[[100,”Fred”,10,””,””,30,25,”Abc”,true,false],

[110,”Eric”,20,”M”,”San Francisco”,3,80,”Abc”,null,false],

[120,”Wilma”,20,”F”,””,1,50,””,null,true],

[110,”John”,40,”M”,”Vancouver”,2,null,”Xyz”,false,true]]

Request

http://myserver:5580/get?schema=SALES&sqlobject=EMPS&includecols=true

Reply

[{"EMPID":30,"MANAGER":false,"NAME":"Fred","AGE":25,"DEPTNO":10,"PUBLIC_KEY":"Abc","EMPNO":100},

{"EMPID":3,"MANAGER":false,"NAME":"Eric","AGE":80,"DEPTNO":20,"PUBLIC_KEY":"Abc","GENDER":"M","CITY":"San Francisco","EMPNO":110},

{"EMPID":1,"MANAGER":true,"NAME":"Wilma","AGE":50,"DEPTNO":20,"PUBLIC_KEY":"","GENDER":"F","CITY":"","EMPNO":120}]

 

/getcontinuous

Initiates a session for continuous HTTP GETs from a SQLstream stream or view. The schema and SQL object are required parameters. You can use additional parameters, which are listed below, to control the minimum and maximum number of rows returned per request, how often to poll s-Server, and how long to wait before timing out. The response includes a session ID used when reading rows (see /getcontinuous/:sessionid below) along with status indicators and a list of the columns in the SQL object.

Parameters

Name

Type

Default

Description

schema

string

required

schema name

sqlobject

string

required

SQL object (stream, view, etc.)

minrows

integer

64

minimum # of rows in buffer before a reply is sent

maxrows

integer

512

maximum # of rows in a reply

poll

integer (seconds)

2

how often to poll SQLstream for rows

timeout

integer (seconds)

30

maximum time for reply. A reply containing 0 to minrows-1 rows will be sent after this period.

maxqueue

integer

8192

maximum # of rows retained in queue

skiprows

boolean

true

Controls what happens when the queue is full. If true, old rows are discarded. If false, we assume the client has abandoned the session, and subsequent reads will indicate an error.

includecols

Boolean

false

Include column names in rows; rows will be objects instead of arrays

Reply Format

{“host”: <SQLstream host>,
“port”: <SQLstream port>,
“schema”: <name of schema>,
“sqlobject”: <name of SQL object>,
“sessionid”: <unique session ID>,

“errorcode”: <error code>,
“SQLstate”: <SQL state code>,
“exceptionClass”: <exception thrown>,
“message”: <error message>,
“columns”: [
 {“name”: <column name>,
      “index”: <column index (starts with 1)>,
      “type”: <SQL type of column>,
      “precision”: <SQL precision>,
      “scale”: <SQL scale>,
      “nullable”: <true if column accepts nulls>
 },
 … ]
}

Example

Request

http://myserver:5580/getcontinuous?schema=SALES&sqlobject=BIDS

 

Reply

{"host":"myserver","port":5570,"schema":"SALES","sqlobject":"BIDS","sessionid":"3ccd342f-4df1-4ffb-ad92-95f1c385673f","errorCode":0,"SQLState":"00000","exceptionClass":"","message":"","columns":[{"name":"ROWTIME","index":1,"type":"TIMESTAMP","precision":0,"scale":0,"nullable":false},{"name":"time","index":2,"type":"BIGINT","precision":19,"scale":0,"nullable":true},{"name":"ticker","index":3,"type":"VARCHAR","precision":5,"scale":0,"nullable":true},{"name":"shares","index":4,"type":"INTEGER","precision":10,"scale":0,"nullable":true},{"name":"price","index":5,"type":"REAL","precision":7,"scale":0,"nullable":true},{"name":"expiryMs","index":6,"type":"BIGINT","precision":19,"scale":0,"nullable":true},{"name":"comment","index":7,"type":"VARCHAR","precision":1024,"scale":0,"nullable":true}]}

/endcontinous

Given a session ID as returned by /getcontinuous, close the session.

Reply Format

{“error”: <error code>,

“message”: <error message>

}

Example

Request

http://myserver:5580/endcontinuous/3ccd342f-4df1-4ffb-ad92-95f1c385673f

 

Reply

{"error":0, "message":""}

/getcontinous/:sessionid

Given a session ID as returned by /getcontinuous, read from SQLstream. If at least minrows are queued before the timeout expires, you get a reply containing an error code and the rows as an array of arrays. After the timeout, you get a reply containing 0 to minrows – 1 rows.

If skiprows is true, Web Agent will read continuously from SQLstream and discard old rows to prevent more than maxqueue rows from accumulating.

If skiprows is false, Web Agent stops reading when the queue is full.

The client then has 16 times the timeout period to request data from this session, if no request is made in that time the session is abandoned and subsequent requests will return an error.

If ‘includecols’ is false (default) the rows will be output as an array of arrays, one per row. If true, the rows will be an array of objects, each with properties matching the column names for the stream.

Reply Format

{“error”: <error code>,

“message”: <error message>,
“rowCount”: <rows in this response>,

“rows”: [

     [<rows values as array>]
   … ]
}

Example

Request

http://myserver:5580/getcontinuous/3ccd342f-4df1-4ffb-ad92-95f1c385673f

 

Reply

{"error":0, "message":"", "rowCount":144,"rows":

[

["Jan 23, 2012 10:04:58 PM",1327361539418,"MSFT",593,18.66,13323515,"sample comment B-2"],

["Jan 23, 2012 10:04:58 PM",1327359486053,"MSFT",443,15.18,13335116,"sample comment B-2"],

["Jan 23, 2012 10:04:58 PM",1327356654079,"SQLS",677,16.12,16721538,"sample comment C-2"],

["Jan 23, 2012 10:04:58 PM",1327361469393,"MSFT",401,16.9,4142586,"sample comment B-2"],

["Jan 23, 2012 10:04:58 PM",1327363275810,"ADBE",465,16.43,8830800,"sample comment A-1"],

]}

Note: If no rows are available, the result will be {“error”: 0, “message”: “”, “rowCount”: 0} If the session ID is invalid or the session has been abandoned, the result will be {“error”: 1, “message”: “invalid session ID”, “rowCount”: 0}.

/sqlstream

Using this request, you can send SQL commands to s-Server through a websocket. The socket you get from /sqlstream accepts SQL commands and returns the result. This functions roughly like using the SQLline client through a websocket.

To use /sqlstream, you submit an http request to open a socket and receive a unique ID, which is used to construct a websocket URL, as above, as in

ws:///ws/0a46c064-4870-40db-b6ff-22c54ae1525f

 

Once you submit a http://myserver:5880/sqlstream request, the return message contains the path for the websocket. The websocket accepts messages consisting of a token  and a SQL command. The token is used to identify the reply, is generated by your client, and might be a string containing a serial number, for example. The reply will contain the same token and the response from s-Server.

Note: This option is enabled only if the –w or –-enable-write option was specified at startup.

Note: in order to use this option, you need to be running one of the following browsers: Microsoft Internet Explorer 11 (or later), Google Chrome 31 (or later), Mozilla Firefox 17 (or later), or Apple Safari 7 (or later).

Name

Type

Default

Description

user

string

 

user name (currently ignored)

password

string

 

user password (currently ignored)

Reply Format

{“success”: <true if authorized and websocket opened>, “ws”: <path for websocket>}

Example

http://myserver:5580/sqlstream

 

{"success":true,"ws":”/ws/0a46c064-4870-40db-b6ff-22c54ae1525f”}

WebSocket

ws://myserver:5580/<path>

 

Example:

ws:///ws/0a46c064-4870-40db-b6ff-22c54ae1525f

 

Receive

If the result contains a table (if the SQL was a SELECT statement):

{“token”: <token corresponding to SQL command>,
“nrows”: <number of rows in result>

“columns”: [
 {“name”: <column name>,
      “index”: <column index (starts with 1)>,
      “type”: <SQL type of column>,
      “precision”: <SQL precision>,
      “scale”: <SQL scale>,
      “nullable”: <true if column accepts nulls>
 },
 … ],

 “rows”: [

     [<rows values as array>]

  … ]

}

 

Otherwise, only the number of rows affected is sent:

{“token”: <token corresponding to SQL command>,
“nrows”: <number of rows affected by statement>}

 

If there is an error with the statement, the error is also returned:

{“token”: <token corresponding to SQL command>,
“nrows”: 0,

“error”: <SQL error message>}

/read        

Requests a websocket for receiving rows from SQLstream. The /read socket accepts SQL SELECT statements and replies with a JSON record describing the columns of the table/stream, then each of the rows of the table or stream as separate JSON records.

To open or close and reopen a stream, send the SQL Select statement as the command.
To stop a stream, send “stop” as the command.

Using the Multiplex Option

By default, each socket supports one query at a time. In order to combine multiple queries over the same websocket, you can add the multiplex parameter to the /read URL and set it to true. Each query must then be accompanied by an arbitrary string tag or “token”. Sending a new query with an existing token cancels the old query associated with that token, then starts the new query. The rows received will arrive inside an object that includes the token, and the token will be included in all other messages (which are already JSON objects).

Note: This option is preferable to opening multiple websockets.

Process

You may send a non-streaming select statement to read from a table or view on a table. The response will be the column info for the table followed by each of the rows of the table, each as a separate message. This stops any stream select that may have been running, so no further output will be sent until another select statement is sent. If multiplexing is enabled, you will receive an end-of-table message when the table has been exhausted. You may select from the same table multiple times, the full table will be sent each time (in contrast, if you issue a select stream command and then issue the same command again, you will not get a new columns description, you will continue to get rows from the same stream.

Note: in order to use this option, you need to be running one of the following browsers: Microsoft Internet Explorer 11 (or later), Google Chrome 31 (or later), Mozilla Firefox 17 (or later), or Apple Safari 7 (or later).

Parameters

Name

Type

Default

Description

user

string

 

user name (currently ignored)

password

string

 

user password (currently ignored)

multiplex

boolean

false

Enables multiplexing option

Command Options

Option

Default

Description

token

undefined

Should be provided with all commands sent on sockets with multiplexing enabled. This token identifies responses pertaining to the SELECT statement. Rows return as JSON objects that include the token as well as the row array. You need to send a unique token, because existing SELECT statements on this socket that have the same token (or no token) will be stopped.

skip

false

If true, enables load shedding so that rows are skipped when the load is above the defined loadLimit. If no token is provided for the SELECT statement, the client receives rows as arrays and skipped rows just won’t be sent. If a token is provided, the client will receive JSON objects that include a skipped property which indicates the number of rows that were skipped since the last row was sent.

 

If false, back pressure can build up in the s-Server pipeline if the messages are delayed in the network or not accepted by the webclient.

loadLimit

 

The limit to the number of row sends that haven’t completed. If skip is true and the load reaches this level, WebAgent will start skipping rows.

If the load reaches this level and skip is false, the agent will block, but at the time defined by maxwaitforwrite it will presume the webclient is dead and will close the websocket.

maxwaitforwrite is set on the webagent commandline by -Dwebsocket.maxwaitforwrite=N where N is seconds. Default is 10 seconds.

Reply

{“success”: <true if authorized and websocket opened>, “ws”: <path for websocket>}

Example

http://myserver:5580/read

 

{"success":true,"ws":”/ws/0a46c064-4870-40db-b6ff-22c54ae1525f”}

 

http://localhost:5580/read?multiplex=true

 

{"success":true,"ws":”/ws/0a46c064-4870-40db-b6ff-22c54ae1525f”}

 

WebSocket

ws://myserver:5580/<path>

 

Send

{“command”: <SQL select statement>|stop|””}

{"command":"select stream ROWTIME, * from \"SALES\".\"BIDS\"", "token": 1}

{"command":"select stream ROWTIME, * from \"SALES\".\"ASKS\"",

"token": 2,

“skip”: true,

“loadLimit”: 12

}

 

Example:

{"command":"select stream ROWTIME, * from \"SALES\".\"BIDS\""}

 

Receive

Once, each time a new SELECT statement is sent:

{“token”: <token corresponding to SELECT command>,

{“columns”: [  

 {“name”: <column name>,
      “index”: <column index (starts with 1)>,
      “type”: <SQL type of column>,
      “precision”: <SQL precision>,
      “scale”: <SQL scale>,
      “nullable”: <true if column accepts nulls>
 },
 … ]}

 

For each row in the stream:

[<value>,<value> …]

 

For each row in the stream (multiplexing):

{“token”: <token corresponding to SELECT command>,

“skipped”: <number of rows skipped due to load shedding>,

“row”: [<value>,<value> …]

}

 

At the end of a table select (must be multiplexing):

{“token”: <token corresponding to SELECT command>,

“total_skipped”: <total number of rows skipped due to load shedding>,

“total_rows”: <total number of rows in table>

}

If an error occurs with the SQL statement:

{“token”: <token corresponding to SELECT command>,

{“errorcode”: <some non-zero value>,

“message”: <error message>

 

/write

Requests a websocket for sending rows to SQLstream. Once you receive the websocket, the client sends a message containing a SQL INSERT statement to open and configure the stream and then sends subsequent messages each containing a single row. A message with “stop” as the command closes the stream.

Note: This option is enabled only if the –w or –-enable-write option was specified at startup.

Note: in order to use this option, you need to be running one of the following browsers: Microsoft Internet Explorer 11 (or later), Google Chrome 31 (or later), Mozilla Firefox 17 (or later), or Apple Safari 7 (or later).

Reply

{“success”: <true if authorized and websocket opened>,
“ws”: <path for websocket>}

Example

Request

http://myserver:5580/write

Reply

{"success":true,"ws":”/ws/0a46c064-4870-40db-b6ff-22c54ae1525f”}

 

WebSocket

ws://myserver:5580/<path>

 

Note: wss is currently not implemented.

Send

{“command”: <SQL insert statement>|stop|””}

 

example:

{"command":"insert into \"SALES\".\"BIDS\" (\"ROWTIME\", \"time\", \"ticker\", \"shares\", \"price\", \"expiryMs\", \"comment\") values (?,?,?,?,?,?,?)"}

 

Receive

Once, each time a new INSERT statement is sent:

{“params”: [  

 {“index”: <column index (starts with 1)>,

      “mode”: <insert mode>,
      “type”: <SQL type of parameter>,
      “precision”: <SQL precision>,
      “scale”: <SQL scale>,
      “nullable”: <true if parameter accepts nulls>
      “signed”: <true if parameter is signed>
 },
 … ]}

 

If an error occurs with the SQL statement:

{“errorcode”: <some non-zero value>,

“message”: <error message>

}

Send a row

[<value>,<value> …]

example:

[“2012-03-09 01:34:56.87",1331262368627,"SQLS",589,19.98,12347529,"sample comment B-2"]

Note: the web UI provided by WebAgent supports specifying a row that will contain random values (it can also repeatedly send rows at a given interval). The codes it recognizes are:

{{now}}

Current time as quoted string

{{nowms}}

Current time as integer milliseconds

{{A|B|C}}

One of A, B, C

{{x..y}}

A random value between x and y

For example, this produces a row similar to the one in the example above:

[{{now}},{{nowMS}},{{"SQLS"|"APPL"|"PEAR"}},{{940..950}},{{42.0..44.0}},12347529,"sample comment {{A|B|C}}-{{1..3}}"]

 

Note that the web UI is doing the substitution. The write socket still expects a standard JSON array.

Enter topic text here.