New feature: REST API Pagination

In our Logical Data Warehouse 2.0 release we added another method for 3rd party tools to connect to DataVirtuality: HTTP REST. This allows virtually any programming language to interact with DataVirtuality. Based on your feedback, we implemented a major improvement of the REST API by adding support for pagination. This allows to consistently split retrieval of data into smaller chunks over multiple HTTP requests.

The Problem

When retrieving data from a view or table in DataVirtuality you might only want to fetch a small amount of rows to display in your application, e.g. for showing a first page of search results. So you apply a LIMIT 30 to your SQL statement. When the user requests the next page of search results, you issue the same SQL query again with LIMIT 30 OFFSET 30. However, the amount of rows returned by the SQL query might already have changed due to updates on the data source. Therefore, you cannot guarantee data consistency across multiple HTTP requests and risk missing rows.

The Solution

On the first request, instead of only fetching the first batch of 30 rows, DataVirtuality will already fetch the whole resultset and store it in a cache. When the next batch is requested from the HTTP client, data is served from the cache instead of recalculating the resultset of the actual query.

In order to identify subsequent batches, the REST API now returns an additional HTTP header requestid as well as pointers to the previous and next page. Following those pointers will automatically ensure that your requests will be served from the cache.

This feature is available since DataVirtuality 2.0.38.