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.
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).
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) |
All of the requests below are appended to an http request, as in
where "myserver:5580"
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 |
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.
{“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>]
}
{"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":[]}
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.
Name | Type | Default | Description |
---|---|---|---|
schema | string | all schemas | restrict output to given schema |
{“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>
},
… ]
}
http://myserver:5580/getmetadata
{"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"},]}
Requests metadata for the columns of a SQLstream object.
Name | Type | Default | Description |
---|---|---|---|
schema | string | required | schema name |
sqlobject | string | required | SQL object (stream, view, table, etc.) |
“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>
},
… ]
http://myserver:5580/getcolumns?schema=SALES&sqlobject=BIDS
{"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},]}
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.
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 |
If includecols is false:
[[row as array], …]
If includecols is true:
[{row as object}, …]
http://myserver:5580/get?schema=SALES&sqlobject=EMPS
[[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]]
http://myserver:5580/get?schema=SALES&sqlobject=EMPS&includecols=true
[{"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}]
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.
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 |
{“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>
},
… ]
}
http://myserver:5580/getcontinuous?schema=SALES&sqlobject=BIDS
{"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}]}
Given a session ID as returned by /getcontinuous, close the session.
{“error”: <error code>,
“message”: <error message>
}
http://myserver:5580/endcontinuous/3ccd342f-4df1-4ffb-ad92-95f1c385673f
{"error":0, "message":""}
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.
{“error”: <error code>,
“message”: <error message>,
“rowCount”: <rows in this response>,
“rows”: [
[<rows values as array>]
… ]
}
http://myserver:5580/getcontinuous/3ccd342f-4df1-4ffb-ad92-95f1c385673f
{"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"],
…
]}
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) |
{“success”: <true if authorized and websocket opened>, “ws”: <path for websocket>}
http://myserver:5580/sqlstream
{"success":true,"ws":”/ws/0a46c064-4870-40db-b6ff-22c54ae1525f”}
WebSocket
ws://myserver:5580/<path>
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>}
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.
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.
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).
Name | Type | Default | Description |
---|---|---|---|
user | string | user name (currently ignored) | |
password | string | user password (currently ignored) | |
multiplex | boolean | false | Enables multiplexing option |
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. |
{“success”: <true if authorized and websocket opened>, “ws”: <path for websocket>}
{"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
}
{"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>
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).
{“success”: <true if authorized and websocket opened>,
“ws”: <path for websocket>}
{"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|””}
{"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> …]
[“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.
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.
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) |
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