Using s-Server over the Internet

The s-Server webAgent 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.

webAgent 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 webAgent as a service, both the test tool and writing to webAgent 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 webAgent Test tool. See Using the webAgent Test Tool for more details.
-p --port Selects webAgent port. Default is 5580
-sn --SQLstream-name Specifies s-Server user name, default is “”
-sp --SQLstream-password Specifies s-Server password, default is “”
-su --SQLstream-url Specifies s-Server JDBC URL, default is “jdbc:sqlstream:sdp://localhost:5570”
-w --enable-write Enables writable services (/sqlstream and /write)
-g --debug Sets a debug port for webAgent
-v --verbose Enables verbose mode
--help Shows help message
-D Sets a java property, such as:webagent.sh -Dwebagent.stream.fetchTimeout=30orwebagent.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"

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, webAgentsessions, 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.

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, webAgent will read continuously from SQLstream and discard old rows to prevent more than maxqueue rows from accumulating.

If skiprows is false, webAgent 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, 2020 10:04:58 PM",1327361539418,"MSFT",593,18.66,13323515,"sample comment B-2"],
["Jan 23, 2020 10:04:58 PM",1327359486053,"MSFT",443,15.18,13335116,"sample comment B-2"],
["Jan 23, 2020 10:04:58 PM",1327356654079,"SQLS",677,16.12,16721538,"sample comment C-2"],
["Jan 23, 2020 10:04:58 PM",1327361469393,"MSFT",401,16.9,4142586,"sample comment B-2"],
["Jan 23, 2020 10:04:58 PM",1327363275810,"ADBE",465,16.43,8830800,"sample comment A-1"],
…
]}

/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/

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/

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:
[“2020-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.

Running webAgentd as a Service

When you install s-Server, the installer gives you the option of running webAgent as a service called webAgentd. This is the recommended way of running webAgent.

Managing Parameters for webAgentd

There are two variables defined in /etc/default/webAgentd

Variable Equivalent webAgent command line option Description
webAgent_ENABLE_WEBAPP -a Enables webAgent Test tool. See - Using the webAgent Test Tool for more details.
webAgent_ENABLE_WRITE -w Enables writable services (/sqlstream and /write)

Using the webAgent Test Tool

webAgent includes a browser-based test tool through which you can run and confirm webAgent requests. To access the tool, enable the -a option when you launch webAgent. The tool is available at port 5580 at whatever host is running webAgent. Each page features a Save to URL option. This lets you copy the URL in the browser and send a test's results to another user. The home page for the tool lets you enter a host and port for s-Server, and then test webAgent's connectivity to s-Server.

To open the API test tools, click the button in the upper right corner of the home page.

The API Tests page lists tests that correspond to each webAgent request. See webAgent requests in the topic webAgent for more details.

When you open a test, its page lets you enter parameters for the test. For example, the \/sqlstream test lets you first enter a user name and password for s-Server, then open a web socket to s-Server.

Once the socket is open, you can enter SQL and send it to s-Server, viewing the results in the box below. Details on parameters for all tests appear in the topic webAgent in this guide. for example, parameters for /sqlstream appear here