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.

Reading a Snapshot, Tag, or Prior Version

Every request queries current data by default - but a dataset's path segment can carry a @{label} version selector to read a different point in its history instead:

curl 'https://odata.opteryx.app/api/v4/public/geopolitics/countries@previous?$top=5'
curl 'https://odata.opteryx.app/api/v4/public/geopolitics/countries@release_2026_q1?$top=5'
curl 'https://odata.opteryx.app/api/v4/public/geopolitics/countries@1755000000000?$top=5'

Tags and snapshot ids are visible in a dataset's metadata - Custom.Tags lists every tag a dataset holds, alongside the snapshot id each one names:

curl 'https://odata.opteryx.app/api/v4/public/geopolitics/countries/$metadata'

Requesting $metadata for a labelled dataset (.../countries@release_2026_q1/$metadata) describes that version rather than the current one - its Custom.Snapshot.* and Custom.Tags annotations reflect the snapshot the label resolved to.

An unknown tag or snapshot id, or @previous on a dataset with no earlier version, returns 404. A malformed selector - dataset@ with nothing after the @, for instance - returns 400.

@{tag} and @{snapshot_id} are immutable, so paging with $skip/$top through one is fully consistent from first page to last. @current and @previous are resolved fresh on every request, so a page fetched partway through a paging loop can reflect a write that landed after the loop started - the same caveat that applies to any live query, not something specific to naming a version.

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.

Date functions and rolling windows

now() is the current UTC instant. It takes no arguments and is evaluated once per query, so every row is compared against the same moment rather than a clock that drifts as the scan proceeds.

Combined with an ISO 8601 duration literal and add/sub, it expresses a rolling window server-side, which keeps the query correct when it's re-run tomorrow:

curl "https://odata.opteryx.app/api/v4/public/security/ghsa_advisories?\$filter=published_at ge now() sub duration'P30D'&\$count=true&\$top=5"

The duration is ISO 8601: P<n>D days, P<n>M months, P<n>Y years, and after a T, PT<n>H hours, PT<n>M minutes, PT<n>S seconds. Parts combine - P1DT2H30M is a day and a half-past-two. Years and months are calendar-aware: P1Y is a calendar year, not 365.25 days. Weeks (P2W) are not accepted; write the equivalent in days.

The date component functions return integers, and work on DATE and TIMESTAMP columns alike:

hour(), minute() and second() are only meaningful on a TIMESTAMP column. date() narrows a timestamp to its day, so date(published_at) eq 2026-08-03 matches the whole day where published_at eq 2026-08-03 matches only midnight.

Prefer a literal range over a component function where both express the same question. date_added ge 2025-01-01 and date_added lt 2026-01-01 can skip whole files using their stored min/max, while year(date_added) eq 2025 has to read every row to evaluate the function.

time(), mindatetime() and maxdatetime() parse - they're part of the OData v4 grammar - but aren't implemented, and return a 400 naming what to write instead. There's no cast from a timestamp to a time, and the extremes of Edm.DateTimeOffset fall outside the range an Opteryx timestamp can hold.

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