OpenAPI connector overview

Konstantin Alpashkin

The Trino OpenAPI connector allows querying OpenAPI-compatible REST APIs via HTTP. OpenAPI is a standard way to describe the functionality of a REST API — available endpoints, supported HTTP methods, response types, request parameters, etc. The connector accepts an OpenAPI schema (JSON/YAML) and maps detected endpoints to tables. Then, the connector translates SQL queries into HTTP requests, handles the response, and returns the result set to the Trino client.

NOTE
It is strongly recommended to use the connector only for read operations. Inserts and updates are implemented in experimental mode.

Trino OpenAPI connector is bundled with ADH and can be used out of the box. To get started with the connector, see OpenAPI connector usage example. For configuration details, see OpenAPI connector configuration.

OpenAPI schema parsing

The connector maps an OpenAPI schema to SQL entities (tables, columns, requests) according to the following rules:

  • Endpoint paths are mapped to separate tables. For example, the [BaseURL]/transactions endpoint is available for querying as a table named transactions. The camel case (fooBar) is converted to snake case (foo_bar) and special characters like / are converted to _.

  • SQL operations are mapped to HTTP methods as follows:

    • SELECT ⇒ GET or POST (if GET is unavailable);

    • INSERT ⇒ POST/PUT;

    • UPDATE ⇒ PATCH/POST;

    • DELETE ⇒ DELETE.

  • All request parameters are mapped to columns, including path, query, and header parameters.

  • Fields of the HTTP OK (200) response type are mapped to columns.

  • All columns are disambiguated:

    • If a field with the same name but a different data type appears in the response, it is postfixed with _2, _3, etc.

    • If a field with the same name appears both in the request and response, the request body field is postfixed with _req.

OpenAPI extensions

OpenAPI supports custom extensions, which are defined in the OpenAPI schema as blocks prefixed with x-. Such extensions can be used for fine-tuning and adding extra capabilities to the connector (for example, x-unwrap, x-pagination).

TIP
If an OpenAPI schema returned by a REST service cannot be modified to include x- extensions, you can save the schema file locally and modify it as needed.

Error messages

When the connector makes an API call and gets an error, the connector returns an SQL error to the Trino client. Typically, such errors contain only the HTTP status code, and the response message is not displayed (it can be non-human-readable or might contain sensitive data).

To extract human-readable error messages from error responses, set the errorPath property in the x-trino block of the OpenAPI schema. For example:

paths:
  /records:
    get:
      responses:
        # ...
      x-trino:
       errorPath: "$response.body#/message"

Features and limitations

The connector implementation distributed with ADH includes several improvements and features, namely:

  • Array unwrapping. Allows for automatic expansion of array fields in the HTTP response.

  • Pagination for the LIMIT pushdown. Allows querying paginated responses from a REST API.

Limitations

The connector has the following limitations:

  • Limited support for INSERT/DELETE/UPDATE operations. The connector is recommended only for read operations.

  • Limited predicate pushdown support. Predicate pushdown works well only for APIs that support query parameters filtering, and which are fully described in the OpenAPI schema. The connector may cause performance issues on large datasets, since due to the lack of predicate pushdowns, the connector fetches all the dataset and only then applies in-memory filtering.

Found a mistake? Seleсt text and press Ctrl+Enter to report it