REST

REST#

REST driver is bundled with Querona and is ready for use.

It lets you query a REST API using an operation-first approach: Querona generates tabular functions that wrap the REST operations, which you call with parameters and query with simple SELECT statements.

Overview#

The built-in REST provider offers the easiest and most natural way to access REST data using SQL. It hides the complexity of accessing REST data and handles the communication, authentication, and authorization, thus allowing for a seamless experience of querying the data.

Using the operation-first approach, Querona generates a tabular function for each REST operation described in the service’s OpenAPI specification. Each function is auto-parameterized from the operation’s parameters and returns a strongly typed result, so you query a REST API with simple SELECT statements against these functions.

Because these functions return ordinary tabular results, you can join them with other tables and views and use them as a source for materialization, just like any other table or view.

Note

Each query against a REST function makes a live HTTP call to the API, so the Querona host must have network access to the service, and results reflect the data at the moment of the call.

The key features of the provider:

  • Generates an auto-parameterized tabular function for each REST operation

  • Exposes operations as SQL functions with parameters and strongly typed results

  • Abstracts connecting to remote data as well as data processing: TLS/SSL, and authentication

  • Supports multiple authentication methods: API Key, HTTP Basic, JWT, OAuth2 and KSeF

  • Handles API paging automatically when the paging protocol is recognized

  • Flattens nested JSON responses into columns down to a defined depth, returning deeper structure in-row as JSON

  • Generated functions are editable — rewrite their definitions, including the REST-to-SQL type mapping, to match your needs

The provider relies on an OpenAPI specification for automatic configuration. To learn more about OpenAPI please see the OpenAPI guide.

From an operation to a function#

Each OpenAPI operation becomes one tabular function:

  • the function name is derived from the operation’s path (normalized — path segments joined, {placeholders} and special characters folded into underscores),

  • each operation parameter (path and query) becomes a function parameter, carrying its default value and mapped to a Querona type,

  • the response schema is flattened into the result columns, with nested fields exposed as dotted column names (parent.child) and mapped to Querona types,

  • the function is bound to the built-in REST provider through EXTERNAL NAME.

For example, this operation:

"/api/v2.11/tiktok/user/{username}/audience": {
  "get": {
    "parameters": [
      { "name": "username", "in": "path",  "type": "string",  "required": true,  "default": "billieeilish" },
      { "name": "endDate",  "in": "query", "type": "string",  "required": false, "default": "2021-05-04" },
      { "name": "period",   "in": "query", "type": "integer", "required": false, "default": "30" }
    ],
    "responses": { "200": { "schema": { "$ref": "#/definitions/UserAudienceCollectionResponse2" } } }
  }
}

is generated as the function (columns abbreviated):

CREATE FUNCTION [api_v2_11_tiktok_user_username_audience]
(
  @username nvarchar(2000) = N'billieeilish',
  @endDate  nvarchar(2000) = N'2021-05-04',
  @period   bigint         = 30
)
RETURNS TABLE
(
  [errors.code]         bigint,
  [errors.message]      nvarchar(2000),
  [items.date]          datetime2(7),
  [items.followerCount] bigint,
  [items.likeCount]     bigint,
  [page.limit]          bigint,
  [page.next]           nvarchar(2000),
  [page.total]          bigint,
  [related.username]    nvarchar(2000)
  -- ... remaining flattened columns ...
)
EXTERNAL NAME [BuiltIn].[Rest].[api_v2_11_tiktok_user_username_audience]

You then query it like any table-valued function, passing the operation’s parameters:

SELECT [items.date], [items.followerCount]
FROM   api_v2_11_tiktok_user_username_audience('billieeilish', '2021-05-04', 90);

Customizing the generated functions#

The tabular functions generated from the OpenAPI specification are a starting point, not a fixed contract. You can alter and rewrite a generated function definition to match your preferences — for example, to adjust its parameters, output columns, or behavior.

The mapping from the REST data types to Querona’s SQL Server–compatible data types is expressed in the function definition, so you can review and change how each value is typed right where the function is defined.

Automatic paging#

When the REST service uses a paging mechanism that Querona recognizes, paging is handled automatically: the provider fetches and combines successive pages behind the scenes, so a single SELECT returns the complete result set — there is no need to iterate pages yourself.

The following paging algorithms are supported:

  • HATEOAS — follows the hypermedia “next” links returned in the response

  • Parameterized offset — advances through pages using REST parameters (for example, offset and limit)

  • Designated URLs — uses an explicit next-page URL provided by the service

Automatic JSON-to-tabular flattening#

REST responses are typically JSON. Querona collapses nested JSON into a flat, tabular result automatically, expanding nested objects and arrays into columns down to a defined nesting depth. Any structure deeper than that level is returned in-row as JSON text, which you can query further with the built-in JSON functions (see JSON).

Authentication#

The REST provider supports the following authentication methods:

  • API Key

  • HTTP Basic

  • JWT (JSON Web Token)

  • OAuth2

  • KSeF (Polish National e-Invoicing System)

Select the method when configuring the connection and supply the parameters it requires.

Note

KSeF authentication is specific to the KSeF service and is more involved than the other methods — it requires a dedicated setup, including key generation and configuration, before the connection can authenticate.

Connecting to REST#

An OpenAPI specification is essential for automatic provider configuration. The recommended way of getting the specification is to retrieve it from the REST service’s documentation.

Create a connection using the REST provider as a source, and configure the REST-specific options:

  • Paste the OpenAPI specification text into the required “OpenAPI configuration” field - either JSON or YAML format will do

  • Specify the “Base URL”

  • Select the desired authentication method and provide the required parameters specific to the selected method, eg. a username and password if the HTTP Basic is selected, JWT, etc.

  • Test the connection to verify that the OpenAPI configuration is valid and REST service is reachable

If the configuration checks out, SAVE it.

Now we can proceed to Create a virtual database on top of the REST connection we created.

See also#