Introducing the OpenAPI Connector

Every organization is sitting on data they cannot easily reach. Not because it does not exist. But because getting to it requires engineering work that never quite makes it to the top of the queue. Instead, work is completed manually, whether it involves scripts, pipelines, or data migration. The questions still get answered eventually, but using a data centralization approach is never as fast as the business needs it.

Starburst helps solve the problem of data access. It lets people ask questions about their organization’s data, based on all of their data, wherever it lives. But this raises a problem where some questions are so specific that they’re hard to account for at the outset. How do you build for that? For example, how do organizations see interview patterns in Greenhouse, query Datadog incident rates or compare Metronome invoices to customer tickets in Jira? They are forced to use scripts, pipelines, and manual data copy/pastes.

With Starburst, we’re creating a better way in the form of our new OpenAPI connector.

What are connectors?

First, let’s start at the beginning. What are connectors? 

Connectors are the way that Starburst manages universal data access. Our 50+ connectors are how Starburst helps you reach into your object storage, your hosted data services (such as BigQuery, Redshift, or Snowflake), your CRMs (such as Salesforce), and your internal databases (such as Oracle, MySQL, or SQL Server) for your organization’s data. This data is then usable from SQL to power your queries, dashboards, and prompts.

SELECT s.account_name, o.total_orders, o.total_revenue
FROM salesforce.crm.account s
JOIN oracle.sales.customer_orders o ON s.account_id = o.crm_account_id
WHERE o.total_revenue > 10000;

This new OpenAPI connector can tap any data source with an API and an OpenAPI description.

What is OpenAPI?

The OpenAPI Specification (OAS) defines a standard, programming language-agnostic interface description for HTTP APIs. This format is human-browsable and machine-readable. Because it’s machine-readable, this format also enables generation of documentation, MCP servers, and client implementations. This same principle also enables the creation of a low-configuration connector that can get started with only an API root URI and that API’s description.

How to get started

Let’s show how this works in practice by creating a catalog pointed at this free dictionary API using an OpenAPI description. The SQL for this looks something like this. 

CREATE CATALOG dictionary
USING starburst_openapi
WITH (
  "openapi.description-location" = '/path/to/dictionary.yaml',
  "openapi.base-uri" = 'https://api.dictionaryapi.dev/api/v2'
);

The specification can be loaded from a URL for quick hacking, or from a local filesystem location for private assets. After the specification is loaded it is transformed into something unique among Starburst connectors: a series of new table functions.

SHOW FUNCTIONS IN dictionary.default;

Why table functions work alongside the OpenAPI connector

The OpenAPI connector works alongside table functions. Here at Starburst, we’ve previously leveraged table functions to implement storage functions. Table functions let us define details of a data source (or sink) at query time. In the OpenAPI connector we re-use this powerful construct to represent HTTP endpoints that take parameters and return data. 

For example, here is how you would query our dictionary catalog for the phonetics of the word “lead”.

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

The OpenAPI connector generates a new table function for each HTTP endpoint that has a GET operation, a successful response code (200), and a JSON response.

In the above example, “entries_en_word” is generated for the endpoint with path “/entries/en/{word}”. The name is derived by transforming slashes to underscores and erasing the curly braces of path parameters (see parameter locations).

How OpenAPI schemas become SQL types

OpenAPI specifications not only provide the names and parameters of HTTP APIs, but the rigid shape of responses (typically JSON). For example, compare the JSON response that powered the query above and the abbreviated shape of the response (the schema).

ResponseSchema:
  type: array
  items:
    type: object
    description: A single dictionary entry for a word.
    properties:
      word:
        type: string
        description: The word that was looked up.
        example: hello
      phonetic:
        type: string
        description: A primary phonetic transcription of the word.
        example: həˈləʊ

[
  {
    "word": "lead",
    "phonetic": "/lɛd/",
    [...]
  },
  {
    "word": "lead",
    "phonetic": "/liːd/",
    [...]
  }
]

The OpenAPI connector losslessly transforms these shapes into SQL-native schemas to eliminate transformation bottlenecks and get you working with your data faster.

• Endpoints returning JSON arrays are transformed into table functions producing rows equivalent to each value.
• If that JSON value is an object, then the entries are transformed into the table function’s columns.
• Further nested JSON values are transformed into supported Trino types.

For example, here is how the full output schema of the entries API is converted to Trino rows and columns viewed through a new system table “table_functions”.

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';

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

array(
  row(
    "audio" varchar,
    "text" varchar
  )
)

Unleash the full power of SQL

These transformations allow you to start using Starburst’s full suite of SQL tools right away. For example, here’s how you could dig deeper into the JSON arrays and JSON objects returned by the entries API. Automatic conversion to Trino ARRAY and ROW types unlocks tools like UNNEST syntax, and field reference operators.

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;

What happens when a schema doesn’t fit?

By default, if the connector does not have an appropriate cast for a parameter or output column, then it throws an error.

 * paths./repos/{owner}/{repo}/pages/builds/{build_id}.get.responses.200.content.application/json.schema.$ref.page-build.properties."url".format: Unsupported string format uri
 * paths./repos/{owner}/{repo}/pages/deployments/{pages_deployment_id}.get.parameters[2].$ref.pages-deployment-id.schema: Schema uses unsupported boolean keywords [oneOf]
 * paths./repos/{owner}/{repo}/properties/values.get.responses.200.content.application/json.schema.items.$ref.custom-property-value.properties."value": Schema uses unsupported boolean keywords [oneOf]

This excerpt highlights common issues: an unsupported string format, a oneOf construct in a parameter, and a similar issue in a response. These are unsupported in the initial release of the OpenAPI connector.

These errors are an excerpt from trying to load the GitHub API. This isn’t easy. There are around 800 endpoints representing the entire surface area of a platform that hosts code, issues, and actions. But what if accessing engineering data is so powerful that you can’t wait for total support?

That’s exactly why we’ve added a configurable “cast-policy”. You can choose to “drop” unsupported columns, parameters, and table functions. Or you can “fallback” to string parameters and JSON types.

CREATE CATALOG github
USING starburst_openapi
WITH (
  "openapi.description-location" = 'https://raw.githubusercontent.com/github/rest-api-description/refs/heads/main/descriptions/api.github.com/api.github.com.2022-11-28.json',
  "openapi.base-uri" = 'https://api.github.com',
  "openapi.cast-policy" = 'fallback'
);

Here’s how you would query the README API to get more information about a repository. The url column wouldn’t normally be supported since it uses the URI format. Since the connector is configured with a fallback cast policy, the URL gets a string representation.

SELECT url, size, sha
FROM TABLE(github.default.repos_owner_repo_readme(
  owner=>'apache',
  repo=>'iceberg'
));

What about authenticated data?

What if you want to use non-public information from engineering? There’s support for authentication too. Here is how you would configure to use a personal access token as an API key. 

CREATE CATALOG github
USING starburst_openapi
WITH (
  "openapi.description-location" = 'https://raw.githubusercontent.com/github/rest-api-description/refs/heads/main/descriptions/api.github.com/api.github.com.2022-11-28.json',
  "openapi.base-uri" = 'https://api.github.com',
  "openapi.cast-policy" = 'fallback',
  "openapi.security-scheme.type" = 'apikey',
  "openapi.security-scheme.name" = 'Authorization',
  "openapi.security-scheme.secret" = 'Bearer ${env:GITHUB_TOKEN}'
);

Here’s how you’d access Starburst’s organization info.

SELECT blog FROM TABLE(github.default.orgs_org(org=>'starburstdata'));

What if the response doesn’t fit on a page?

What if you want to aggregate engineering data? What if there’s so much data that it can’t be fit into a single API response? For these use cases we’ve added configuration for “pagination”. GitHub, for example, attaches a Link header the connector follows for the next page of results. 

CREATE CATALOG github
USING starburst_openapi
WITH (
    "openapi.description-location" = 'https://raw.githubusercontent.com/github/rest-api-description/refs/heads/main/descriptions/api.github.com/api.github.com.2022-11-28.json',
  "openapi.base-uri" = 'https://api.github.com',
  "openapi.cast-policy" = 'fallback',


  "openapi.security-scheme.type" = 'apikey',
  "openapi.security-scheme.name" = 'Authorization',
  "openapi.security-scheme.secret" = 'Bearer ${env:GITHUB_TOKEN}',

  "openapi.pagination" = 'link_header'
);

You can use this to answer questions like “what is the approximate median, 90th and 99th percentiles of issue-close-time in the Apache Iceberg repository?”

SELECT
  COUNT(*),
  APPROX_PERCENTILE(
    DATE_DIFF('day', created_at, closed_at),
    ARRAY[0.5, 0.9, .99]
  ) AS percentiles
FROM TABLE(github.default.repos_owner_repo_issues(
  owner=>'apache',
  repo=>'iceberg',
  state=>'closed',
  per_page=>100
))
WHERE pull_request IS NULL;

What about performance?

The aggregation query showcased above processed around 16,500 rows from GitHub in just under two minutes. Unfortunately, link header pagination is inherently sequential (which has performance implications we discuss here). Keep this in mind when comparing solutions for high volumes of data.

When exploring APIs, consider using LIMIT to download just the first, for example, five rows of an endpoint. This query finished in less than a second.

SELECT sha
FROM TABLE(github.default.repos_owner_repo_commits(
  owner=>'trinodb',
  repo=>'trino'
))
LIMIT 5;

Public preview of OpenAPI connector 

The OpenAPI connector will be available in public preview starting with Starburst Enterprise 482, so keep an eye out for that release. We’re looking for feedback on which OpenAPI features matter the most for your use case. 

Want to know more about Starburst? Contact us today to see how we can help you access all of your data.