AquaQ releases q-REST Web Interface

AquaQ Analytics is pleased to announce the release of q-REST v1.0 – a RESTful web interface for kdb+. This open source package has been designed as a convenient way for web clients to make requests to a kdb+ instance using the JSON format. The package can be easily integrated with Swagger UI, Docker and standard authentication methods such as SSO, allowing users to easily implement the package with their current set up. Database security can also be improved by only allowing certain requests to be run against a kdb+ instance and by setting additional permissions based on the username of the requester. Instructions for installation and deployment of the service can be found here.

AquaQ q-Rest Web Interface banner

Architecture

The API can send requests to a kdb+ process using either deferred synchronous or asynchronous calls. Deferred synchronous requests send a standard asynchronous call, followed by a synchronous call to collect the response as shown in Figure 1. Asynchronous calls work in a standard fashion. A dedicated connection gets allocated each time a request is sent through the service. By using deferred synchronous calls, multiple concurrent connections between a single REST service and kdb+ instance are possible. q-REST has a default limit of 200 concurrent connections imposed on the service, which is configurable within the application.

Figure 1: Deferred synchronous request

Due to the single threaded nature of kdb+, queries with excessive execution time tend to cause bottlenecks in throughput. Using the API in conjunction with a kdb+ gateway such as that provided within AquaQ Analytics’ TorQ framework for kdb+, gives the REST interface an ability to load balance requests. This can significantly reduce average response times. Typically the gateway can be set up to connect to multiple replica services in a design similar to that shown in Figure 2. The gateway will distribute requests based on pre-configured SLA’s (Service Level Agreements) and resource availability.

Figure 2: Connecting the REST Interface to kdb+ Gateway

Standard load balancing solutions using either hardware or software can also be used for cases where multiple REST interfaces are required. This set up can be seen in Figure 3.

Figure 3: Load balancing over multiple interfaces

Authentication

A simple authentication method using base64 encryption of username and password (in the format user:pass) is implemented by default. We would recommend however that the authentication layer be extended and implemented with clients own security/authentication infrastructure for any production system.

Public Methods and Usage

The following section outlines two typical uses of the REST API. The examples below use the utility Swagger UI which is included within the package from GitHub. The service can be operated in two modes- executeQuery and executeFunction. Both end points require the user to send a POST request with JSON-formatted body.

executeQuery

This end point allows users to freely query a kdb+ process. This feature can be restricted in the configuration settings so that free-form queries are not permissible. The user should provide the requests in the format below. In Figure 4, “sync” refers to deferred synchronous. You can also send asynchronous calls in cases where you don’t wish to receive a response.

{ 
"type":"sync",
"query":"select count i by sym from trade where date=2018.06.18",
"response":true 
}

{
"type":"async",
"query":"delete from `trades where date=2018.06.18",
"response":true
}

Figure 4: Example requests using free-form queries

executeFunction

This end point allows users to call pre-defined functions within the kdb+ process by specifying the server (q/kdb+) function name and function parameters as shown in Figure 5.

{ 
"function_name":".rest.plus", 
"arguments":{ 
"xarg":"7.3", 
"yarg":"8.7" 
 } 
}

Figure 5: Example request calling pre-defined function

If using a function request similar to Figure 5, there needs to a function called .rest.plus defined in the kdb+ instance which adds two numbers together. This will be called with the JSON argument, so should be set up to decode this into a usable form as in Figure 6.

q).rest.plus:{.[+;@["F"$.j.k x;(`xarg;`yarg)]]}

Figure 6: Example of how a pre-defined function should be set up in kdb+ instance

Regardless of whether the call is successful, the application will return a JSON object with 4 keys: requestTime, responseTime, result and success as shown in Figure 7.

Successful call

{  
"requestTime": "2018-07-05T13:30:25.719Z",  
"result": 16, 
"success": true,  
"responseTime": "2018-07-05T13:30:25.734Z"  
}

Failed call

{  
"requestTime": "2018-07-05T14:04:16.171Z", 
"result": "error: rank",  
"success": false,  
"responseTime": "2018-07-05T14:04:16.173Z" 
}

Figure 7: Request response for successful and failed calls

Customisation

The service uses a wrapper function gateway.function. The default definition can be seen in Figure 8.

 gateway.function={[request;properties] 
 @[value;`.aqrest.execute;
 {[e;request;properties] @[neg .z.w;`status`result!@[{(1b;value x)};
 request;{(0b;"error: ",x)}]]}] . (request;properties)
 }

Figure 8: Default definition of gateway.function

It is set to check if .aqrest.execute has been defined within the kdb+ instance and if not it will apply the function arguments (in JSON format) to the function_name as the example above. This wrapper function is defined within the application.properties file and can be customised to the required behaviour.

Additional complexity can be achieved by defining a customised .aqrest.execute function within the kdb+ instance we are connecting to. When doing this we must ensure it is set up to:

Deal with the arguments in the format they are received.
Return the result in the format the REST service expects.

The x parameter is the functionRequest as a 2-element list with the first element being the function name and second element being the arguments for the function (still as a JSON object!). The y parameter is a dictionary of metadata about the request; the main field of interest is the user field.

x parameter
".rest.plus" 
"{\"xarg\":\"7.3\",\"yarg\":\"8.7\"}"

y parameter
user| michael

Figure 9: Example of parameters passed to .aqrest.execute

The result of .aqrest.execute must be formatted so that it returns a dictionary with keys `result and `status. This should be the case regardless of whether the call was successful or not. This is important so that errors generated on kdb+ are propagated back to the API with an unsuccessful status.

Connecting to TorQ Gateway

Setting up AquaQ Analytics’ open source TorQ gateway to be used with the REST interface is easy requiring only .aqrest.execute and .gw.formatresponse to be defined. This can be done by modifying the gateway.q script found in appconfig/settings to be similar to Figure 10.

user@aquaq:~/resttorq/deploy/appconfig/settings$ cat gateway.q 
// Bespoke Gateway config  : Finance Starter Pack  
\d .gw 
synccallsallowed:0b             // whether synchronous calls are allowed   

formatresponse:{[status;sync;result] 
`status`result!(status;result) 
}  
 
\d .aqrest 
execute:{[x;y] 
.gw.asyncexec[(first x;last x);`hdb] 
}

Figure 10: Configuration changes to set up TorQ gateway

Here the .aqrest.execute function simply breaks the two element list (the x parameter) into its constituent parts in the correct order. It then calls the gateway asynchronous function with these. The target location for this example is the hdb.

From this point the gateway can be used alongside the REST interface.

For example, if we define .rest.countbysym within the HDB to return the number of trades by sym for dates specified, we can then call it via the q-REST interface using the request body shown in Figure 11.

{ 
"function_name" : ".rest.countbysym", 
"arguments" :  
        { 
            "xarg" : "2018.06.18", 
            "yarg" : "2018.06.19" 
         } 
}

Figure 11: Example call using the executeFunction end point

We can now also build out the .aqrest.execute function on the gateway for added functionality, for example you may wish to:

Check that only functions within an allowed namespace are being called.
Use the username passed down to point a user to the correct database.
Check users have permissions to query certain processes.

Figure 12 displays the response returned to the client from both end points.

[ 
  { "requestTime": "2018-07-03T13:44:06.725Z", 
    "result": [ 
      { "sym": "AAPL",
     "x": 723430 
      }, 
      { "sym": "AIG",
        "x": 722986 
      }, 
      { "sym": "AMD",
        "x": 725461 
      } 
    ], 
    "success": true,
    "responseTime": "2018-07-03T13:44:07.815Z" 
  }
]

Figure 12: Example of the JSON response

Using the Swagger UI

The q-REST interface can be used by sending a request through a variety of methods such as a simple curl request or Postman. Swagger UI was used for these examples due to the convenience in set up. It also allows us to see what the curl request being sent is. Once the Java process is running, you can access the interface using https://myhostname:8090/swagger-ui.html (the port can be configured).

When using the executeFunction end point with the .rest.plus example, the functionRequest body will contain the JSON formatted as in Figure 13 and the response will be returned as shown in Figure 14.

Figure 13: Example call using the executeFunction end point through Swagger UI