Starburst OpenAPI connector#

Note

The Starburst OpenAPI connector is available as a public preview in Starburst Enterprise. Contact Starburst account team with questions or feedback.

The Starburst OpenAPI connector reads an OpenAPI description and automatically creates table functions that provide access to data available only through HTTP APIs. An OpenAPI description is a standard, language-agnostic interface to HTTP APIs that lets both humans and computers discover and understand the capabilities of a service.

Requirements#

To use the Starburst OpenAPI connector, you need:

A SEP 482-e or later cluster.
An OpenAPI description, version 3.0.x or earlier, provided as a file or a URL.
A valid Starburst Enterprise license.

Configuration#

Before you configure the connector, you need the following details:

The location of the OpenAPI description, specified with the openapi.description-location property. The location can be a file path or a URL, and it must be accessible by all coordinator and worker nodes. For example, /etc/starburst/specification.yaml or https://petstore3.swagger.io/api/v3/openapi.yaml.
The base URI that the connector prepends to the path items in the OpenAPI description, specified with the openapi.base-uri property. For example, to access an API at https://api.dictionaryapi.dev/api/v2/entries/en/word when the OpenAPI description defines the path /entries/en/{word}, set the base URI to https://api.dictionaryapi.dev/api/v2.

To configure the connector, create a catalog properties file that specifies the Starburst OpenAPI connector by setting the connector.name to starburst_openapi.

connector.name=starburst_openapi
openapi.description-location=/etc/starburst/specification.yaml
openapi.base-uri=https://petstore3.swagger.io/api/v3

The connector has the following configuration properties:

Starburst OpenAPI connector configuration properties#
Property name	Description	Default
`openapi.description-location`	Path or URL to the OpenAPI description. The location must be accessible by all coordinator and worker nodes. Required.
`openapi.base-uri`	Base URI that the connector prepends to the path items defined in the OpenAPI description. Required.
`openapi.cast-policy`	Determines how the connector handles response fields and table function parameters that do not have a supported type conversion. See Trino type mapping for the available values.	`fallback`
`openapi.pagination`	Pagination style used by the API to split large responses across multiple pages. See Pagination for the available values and their additional properties.	`none`

Secrets#

The connector supports two types of configurable secrets for authenticating to the HTTP API.

API key secrets#

To write a static secret to a header, query parameter, or cookie, set openapi.security-scheme.type to apikey and configure the following properties:

API key secret properties#
Property name	Description
`openapi.security-scheme.type`	OpenAPI security type. Set to `apikey` to use an API key secret.
`openapi.security-scheme.in`	Location of the secret. Set to `header`, `query`, or `cookie`.
`openapi.security-scheme.name`	Name of the header, query parameter, or cookie that carries the secret.
`openapi.security-scheme.secret`	Value of the secret.

For example, to pass a secret in a header named X-Api-Key, add the following to the catalog:

openapi.security-scheme.type=apikey
openapi.security-scheme.in=header
openapi.security-scheme.name=X-Api-Key
openapi.security-scheme.secret=MY_SECRET_KEY

OAuth 2.0 client credentials#

To have the connector participate in an OAuth 2.0 client credentials flow, set openapi.security-scheme.type to oauth2 and configure the following properties:

OAuth 2.0 client credentials properties#
Property name	Description
`openapi.security-scheme.type`	OpenAPI security type. Set to `oauth2` to use an OAuth 2.0 client credentials flow.
`openapi.security-scheme.token-url`	URL of the token endpoint used to request an access token.
`openapi.security-scheme.client-id`	Client ID used to authenticate to the token endpoint.
`openapi.security-scheme.client-secret`	Client secret used to authenticate to the token endpoint.
`openapi.security-scheme.scopes`	Comma-separated list of scopes to request.

For example:

openapi.security-scheme.type=oauth2
openapi.security-scheme.token-url=https://example.org/token
openapi.security-scheme.client-id=MY_CLIENT_ID
openapi.security-scheme.client-secret=MY_CLIENT_SECRET
openapi.security-scheme.scopes=comma,separated,list

Pagination#

Many HTTP APIs split large responses into pages and provide a mechanism to request the next page until all results are returned. Because the OpenAPI standard does not define a way to describe pagination, you must tell the connector which pagination style the API uses. Set the openapi.pagination property to one of the following values:

Pagination styles#
Style	Description
`none`	Default. The connector does not paginate and requires no additional configuration.
`offset`	The connector requests each page with an offset parameter.
`page_number`	The connector requests each page by page number.
`link_header`	The connector follows the link labeled `next` in the response’s link header. Requires no additional configuration.
`next_cursor_field`	The connector reads an opaque cursor from a field in the response body and passes it as a query parameter to request the next page.
`last_element_cursor_field`	The connector reads a cursor from a field on the last element of each page and passes it to request the next page.
`next_url_field`	The connector reads the absolute URL of the next page from a field in the response body.

Offset pagination#

When openapi.pagination is set to offset, configure the following properties:

Property name	Description
`openapi.pagination.offset.offset-parameter-name`	Name of the request parameter that the connector sets to the offset.
`openapi.pagination.offset.data-field-json-pointer`	JSON pointer to the response array whose last element provides the next cursor, for example `/data`. When omitted, the response body itself is treated as the array.

Page number pagination#

When openapi.pagination is set to page_number, configure the following properties:

Property name	Description
`openapi.pagination.page-number.page-parameter-name`	Name of the request parameter that the connector sets to the page number.
`openapi.pagination.page-number.is-last-page-field-json-pointer`	JSON pointer to the boolean field in the response body that indicates whether the last page has been reached, for example `/last`.

Next cursor field pagination#

When openapi.pagination is set to next_cursor_field, configure the following properties:

Property name	Description
`openapi.pagination.next-field-cursor.cursor-parameter-name`	Query parameter name for the pagination cursor.
`openapi.pagination.next-field-cursor.cursor-field-json-pointer`	JSON pointer to the field in the response body that contains the next cursor, for example `/response_metadata/next_cursor`.

Last element cursor field pagination#

When openapi.pagination is set to last_element_cursor_field, configure the following property:

openapi.pagination.last-element-cursor-field.cursor-parameter-name: Parameter name used to pass the cursor to the next page request. The cursor is taken from a field on the last element of the current page.

Next URL field pagination#

When openapi.pagination is set to next_url_field, configure the following property:

openapi.pagination.next-url-cursor.next-url-field-json-pointer: JSON pointer to the field in the response body that contains the absolute URL for the next page, for example /paging/next.

Table functions#

After you configure a catalog with an OpenAPI description, the connector automatically transforms the API paths into table functions. Use the SHOW FUNCTIONS statement to list the available table functions, and call them with the TABLE syntax to query the API. See Example for a walkthrough.

Unsupported paths#

The connector does not map the following paths to table functions:

The / path.
Paths without a GET operation.
Paths without an expected successful status code.
Paths with a successful status code that do not return a JSON response with a defined schema.

The connector provides an error message for all other unsupported paths.

System table#

The connector exposes a system metadata table called system.table_functions that you can query for richer metadata about the available table functions. Each row includes the function name, the API path it maps to, its description, and its input and output columns. Use the table to discover the available table functions and inspect their schemas without preparing a statement for each one.

SELECT * FROM example.system.table_functions;

   function_name    |     api_path      |     description      |                 input_columns                 |        output_columns
--------------------+-------------------+----------------------+-----------------------------------------------+-------------------------------
 pet_find_by_status | /pet/findByStatus | Finds Pets by status.| [{name=status, type=varchar, required=false}] | [{name=id, type=bigint}, ...]
 pet_pet_id         | /pet/{petId}      | Find pet by ID.      | [{name=petId, type=bigint, required=true}]    | [{name=id, type=bigint}, ...]
 store_inventory    | /store/inventory  | Returns inventories. | NULL                                          | [{name=..., type=...}, ...]

Trino type mapping#

The connector automatically maps the schema of the response data to the best supported Trino type. The connector applies the following mappings:

JSON arrays are mapped to ARRAY types. Use the UNNEST syntax to expand an array into separate rows for further processing.
JSON objects are mapped to ROW types. Use the field reference operator to access individual fields.
JSON booleans are mapped to the BOOLEAN type.
JSON numbers and strings are mapped to Trino types based on their OpenAPI format, as described in the following sections. An OpenAPI format specifies how to interpret a raw JSON value.

Number type mapping#

The connector maps JSON numbers to Trino types based on the following OpenAPI formats:

OpenAPI format	Trino type
No format	A NUMBER type with unlimited precision, because the JSON grammar allows numbers to have unlimited precision.
`float`	REAL
`double`	DOUBLE

String type mapping#

The connector maps JSON strings to Trino types based on the following OpenAPI formats:

OpenAPI format	Trino type
No format	Unbounded VARCHAR
`byte`	VARBINARY
`uuid`	UUID
`date`	DATE
`date-time`	TIMESTAMP(12) WITH TIME ZONE, with picosecond precision. Any digits more precise than picoseconds are dropped.

Unsupported type conversions#

When a response field or table function parameter does not have a supported type conversion, the openapi.cast-policy property controls how the connector behaves:

Value	Description
`error`	The connector raises an error for each field or parameter that does not have a supported conversion.
`drop`	The connector drops fields or parameters that do not have a supported conversion. If a field or parameter cannot be dropped, the connector drops the affected table function.
`fallback`	The connector converts unsupported fields or parameters to a fallback type, such as JSON, VARCHAR, or NUMBER. Because the connector maps values to their natural Trino type as format support improves, table function schemas that rely on fallback types can change between releases.

You can inspect the resulting types with a prepared statement and the DESCRIBE OUTPUT statement, or by querying the system table, as shown in Example.

Example#

The following example uses a catalog named dictionary that is configured to access a public dictionary API:

connector.name=starburst_openapi
openapi.description-location=https://api.dictionaryapi.dev/openapi.yaml
openapi.base-uri=https://api.dictionaryapi.dev/api/v2

Use the SHOW FUNCTIONS statement to list the available table functions:

SHOW FUNCTIONS IN dictionary.default;

       Function        | Return Type |  Argument Types  | Function Type | Deterministic | Description
-----------------------+-------------+------------------+---------------+---------------+-------------
 entries_en_word       | unknown     | varchar, varchar | table         | false         |
(1 row)

The connector maps the /entries/en/{word} path item of the OpenAPI description to the entries_en_word table function. This API takes one parameter, word, which the connector maps to a table function argument. Pass the argument to query the API:

SELECT word, phonetic
FROM TABLE(dictionary.default.entries_en_word(
  WORD => 'lead'
));

 word | phonetic
------+----------
 lead | /lɛd/
 lead | /liːd/

To inspect the types that the connector maps from the response schema, use a prepared statement with the DESCRIBE OUTPUT statement:

PREPARE my_select FROM
SELECT *
FROM TABLE(dictionary.default.entries_en_word(
  WORD => 'star'
));

DESCRIBE OUTPUT my_select;

 Column Name | Catalog | Schema | Table | Type
-------------+---------+--------+-------+------------------------------------------------------------------
 meanings    |         |        |       | array(row("definitions" array(row("antonyms" array(varchar), ...
 origin      |         |        |       | varchar
 phonetic    |         |        |       | varchar
 phonetics   |         |        |       | array(row("audio" varchar, "text" varchar))
 word        |         |        |       | varchar

Alternatively, query the system.table_functions table to retrieve the output column types in a single query, without preparing a statement:

SELECT output_column.name, output_column.type
FROM dictionary.system.table_functions
CROSS JOIN UNNEST(output_columns) AS output_column
WHERE function_name = 'entries_en_word';

The following is the type of the meanings column, with adjusted whitespace for clarity:

array(
  row(
    "definitions" array(
      row(
        "antonyms" array(varchar),
        "definition" varchar,
        "example" varchar,
        "synonyms" array(varchar)
      )
    ),
    "partofspeech" varchar
  )
)

The connector maps JSON arrays to ARRAY types. Use the UNNEST syntax to expand these arrays for further processing:

SELECT antonym
FROM TABLE(dictionary.default.entries_en_word(
  WORD => 'antonym'
))
CROSS JOIN UNNEST(meanings)
CROSS JOIN UNNEST(definitions)
CROSS JOIN UNNEST(antonyms) AS t(antonym)
WHERE antonym IS NOT NULL;

In the following output, antonym is an unbounded VARCHAR, the default type for unformatted strings:

 antonym
---------
 synonym

The connector maps JSON objects to ROW types. Use the field reference operator to access individual fields:

SELECT meaning.partofspeech, definition_entry.definition
FROM TABLE(dictionary.default.entries_en_word(
  WORD => 'laconic'
)) AS response
CROSS JOIN UNNEST(response.meanings) AS meaning
CROSS JOIN UNNEST(definitions) AS definition_entry;

 partofspeech |                     definition
--------------+----------------------------------------------------
 adjective    | Using as few words as possible; pithy and concise.