Querying via OData

opteryx.app, the hosted service, exposes datasets as an OData v4 feed at odata.opteryx.app. OData is an OASIS standard for querying data over HTTP, so a large number of tools - Power BI and Excel among them - can read Opteryx data directly, with no driver to install and no export step.

If you want a plain HTTP/JSON API instead, see Running a Query via the API; for large result sets in Python, see Connecting via Arrow Flight SQL.

OData here is read-only - no INSERT/UPDATE/DELETE.

The query syntax used below is defined by the standard, not by Opteryx. For the full grammar see OData v4.01 Part 2: URL Conventions, and for $apply see the Data Aggregation extension.

A Live View, Not an Extract

Every request queries current data. There's no extract to schedule and no copy to keep in sync - a dashboard refreshing hourly shows the data as it stood at each refresh.

Each refresh is a real query, so push the work into it with $filter, $select and $apply rather than pulling everything back and reducing it client-side.

Connecting from Power BI, Excel, and Other Tools

Point any OData v4 client at the service root to browse the datasets available to you, or at a single dataset URL to go straight to one:

https://odata.opteryx.app/api/v4/

Power BI and Excel are the two clients we test against; Microsoft documents the connection steps for Power BI and Excel. Anything else implementing OData v4 should work the same way - the OData ecosystem list covers the wider set of clients and libraries.

Clients that implement server-driven paging follow @odata.nextLink for you, so a dataset larger than one page still loads in full without any extra configuration.

Authentication

Requests carry a bearer token, the same as the rest of the hosted service:

Authorization: Bearer <token>

This must be a JWT access token - see the Authentication API for how to mint one. A raw Personal Access Token isn't accepted here: if you hold a PAT (a client_id/client_secret pair, as used by opteryx_upload's PATAuthenticator), exchange it for an access token at that endpoint first. Access and Permissions covers how workspace policies decide what a token can see.

Discovering Datasets

GET the service document to list every EntitySet available to you:

curl https://odata.opteryx.app/api/v4/

{
  "@odata.context": "/api/v4/$metadata",
  "value": [
    {
      "name": "public.geopolitics.countries",
      "kind": "EntitySet",
      "url": "public/geopolitics/countries",
      "source": "Table",
      "role": "reader",
      "ordered": false,
      "orderBy": null,
      "orderDirection": null
    },
    {
      "name": "public.security.cisa_kev",
      "kind": "EntitySet",
      "url": "public/security/cisa_kev",
      "source": "Table",
      "role": "reader",
      "ordered": false,
      "orderBy": null,
      "orderDirection": null
    }
  ]
}

Each entry's url is the path to query, shaped {workspace}/{collection}/{dataset} - the same three-part addressing used elsewhere on the platform (for example the Upload API's Target(workspace=..., collection=..., dataset=...)). So public/geopolitics/countries is workspace public, collection geopolitics, dataset countries. One workspace name behaves specially: personal resolves collection to your own identity rather than a shared collection name.

role is the access level your credentials have on that entity set. source distinguishes ordinary tables from Views and the Virtual information_schema entities present in every workspace. When ordered is true, results come back sorted by orderBy (in orderDirection) rather than in arbitrary order.

@odata.context points at /api/v4/$metadata, the CSDL metadata document describing each EntitySet's schema. This is the document Power BI and Excel read to work out column names and types.

Querying a Dataset

Combine the base URL with an entry's url from the service document:

curl 'https://odata.opteryx.app/api/v4/public/geopolitics/countries?$top=10'

Use single quotes around the URL. In bash and zsh, a double-quoted "...?$top=10" makes the shell expand $top as a variable and send ?=10 instead.

For a dataset that needs a token, add the Authorization header:

curl 'https://odata.opteryx.app/api/v4/acme/security/findings?$top=10' \
  -H 'Authorization: Bearer YOUR_TOKEN'

Supported query options:

$search and $expand are not implemented and return 501 Not Implemented.

curl 'https://odata.opteryx.app/api/v4/public/geopolitics/countries?$filter=region eq %27Europe%27&$orderby=country_name_common&$top=5'

Paging Through a Full Result Set

When a query matches more rows than $top, the response carries exactly $top rows plus an @odata.nextLink for the next page. Results are never silently truncated, and exceeding $top is not an error:

{
  "@odata.context": "/api/v4/$metadata#public_geopolitics_countries",
  "value": [ "..." ],
  "@odata.nextLink": "/api/v4/public/geopolitics/countries?%24top=2&%24skip=2"
}

Two details matter if you're writing the paging loop yourself. The link is relative - resolve it against https://odata.opteryx.app - and the $ in the query string arrives percent-encoded as %24, which is equivalent and should be passed through unchanged rather than rewritten.

Keep following @odata.nextLink until a response comes back without one; that response is the last page. There's no limit on how far $skip can reach, so a result set of any size can be read in full this way. The 25,000 cap applies only to the value of $top in a single request - a large result set is paged, never rejected.

Dates and Timestamps in $filter

Date and datetime literals are unquoted ISO 8601, per the OData standard:

curl 'https://odata.opteryx.app/api/v4/public/security/cisa_kev?$filter=date_added ge 2025-01-01&$top=5'

Quoting the value makes it a string, and comparing a string to a date or timestamp column is rejected rather than silently matching nothing:

{
  "error": {
    "code": "BadRequest",
    "message": "Invalid query: Incompatible types for column 'cisa_kev.date_added' (DATE) and literal '2025-01-01' (VARCHAR). Using `CAST(column AS type)` may help resolve."
  }
}

A date-only literal (2025-01-01) and a full datetime literal (2025-01-01T00:00:00Z) are both accepted against either a DATE or a TIMESTAMP column, and naming the same instant either way selects the same rows. Use whichever matches the precision you need.

Where the two sides differ in precision, a date is read as midnight UTC on that day - which decides what lands on the boundary. Against a DATE column, date_added lt 2026-08-03T12:00:00Z includes the rows dated 2026-08-03, because their midnight falls before noon, while date_added lt 2026-08-03 excludes them. The same widening applies in reverse: published_at lt 2026-07-01 excludes everything timestamped on the 1st, because those rows are at or after midnight.

A timezone designator is optional and is honoured when present. 2026-07-01T00:00:00Z, 2026-07-01T00:00:00+00:00, 2026-07-01T00:00:00 and 2026-07-01T05:00:00+05:00 all name the same instant and return the same rows.

Fractional seconds are accepted up to five digits. Six or more - which is what Python's datetime.isoformat() emits - aren't recognised as a datetime, so the literal is read as a string and rejected on type:

curl 'https://odata.opteryx.app/api/v4/public/security/ghsa_advisories?$filter=published_at ge 2026-07-01T00:00:00.123456Z'

{
  "error": {
    "code": "BadRequest",
    "message": "Invalid query: Incompatible types for column 'ghsa_advisories.published_at' (TIMESTAMP) and literal '2026-07-01T00:00:00.123456Z' (VARCHAR). Using `CAST(column AS type)` may help resolve."
  }
}

Truncate to five digits, or drop the fractional part, when building a literal from a machine-generated timestamp.

To filter a rolling window, compute the boundary in the calling code and interpolate it - OData has no now() and no relative-date syntax.

Aggregating with $apply

$apply groups and aggregates server-side, so you don't have to pull every row back to count or deduplicate it. $count, sum, average, min, max and countdistinct are available:

curl 'https://odata.opteryx.app/api/v4/public/geopolitics/countries?$apply=groupby((region),aggregate($count as country_count))'

{
  "@odata.context": "/api/v4/$metadata#public_geopolitics_countries",
  "value": [
    { "region": "Africa", "country_count": 59 },
    { "region": "Americas", "country_count": 56 },
    { "region": "Asia", "country_count": 50 },
    { "region": "Europe", "country_count": 53 },
    { "region": "Oceania", "country_count": 27 },
    { "region": "Antarctic", "country_count": 5 }
  ]
}

For a distinct list of values, group by the column without aggregating it further - that returns one row per distinct value, rather than every underlying row for you to deduplicate.

Note that $apply combined with $orderby on an aggregate alias is not currently supported; sort the aggregated result client-side.

Try It

This queries public.geopolitics.countries live - 250 rows. Edit the options and the request URL updates as you type.

Errors

Errors are JSON, shaped {"error": {"code": ..., "message": ...}}, with the HTTP status reflecting the failure:

A failed read is never returned as a 200 with an empty value array. An empty value means the query ran and matched no rows; anything else is a non-2xx status with an error body. This distinction is guaranteed, so a consumer can safely treat "empty" as a real result rather than having to guess whether the read failed.

Queries are executed by the Opteryx SQL engine, and some messages it raises are phrased for SQL - the type-mismatch error above suggesting CAST(column AS type) is one example. Your OData request is not translated into SQL before it runs; it's compiled directly into an execution plan. So read that kind of advice as a description of the underlying type problem - the Data Types reference explains the types being compared - and fix it in the OData expression rather than trying to pass SQL through a query option.

Related

• Load and Query Data
• Running a Query via the API
• Connecting via Arrow Flight SQL
• Authentication API
• Access and Permissions