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.

Try it yourself: the companion notebook works through this page in Python with requests and pandas: it discovers the demo datasets, reads a schema from $metadata, filters, sorts and counts the NVD vulnerability data, pages with @odata.nextLink, and aggregates CISA's known-exploited list with $apply. It needs no account or token. Open in Colab or download the notebook.

What You'll Be Able to Do

By the end of this page you'll be able to:

• explain how the OData service differs from taking an extract
• connect Power BI, Excel or another OData client to a dataset, with or without a token
• discover the datasets available to you, and query one with filters and paging via @odata.nextLink
• aggregate server-side with $apply, and read a tagged or prior snapshot
• recognise the errors the service returns and what each one means

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 - with one exception. The demo workspace is readable by anyone, with no token at all: it's what the examples on this page query, so every curl here works as written. Everything else, including the public workspace, needs a token.

Authorization: Bearer <token>

A bearer token must be a JWT access token - see the Authentication API for how to mint one. A raw Personal Access Token isn't accepted as a bearer token: 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.

Clients that can only store a user name and password - Power BI and Excel among them - can use HTTP Basic authentication instead, with the client_id as the user name and the PAT as the password. The service exchanges them for an access token on each request. Iceberg in Power BI and Excel walks through it.

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": "demo.geopolitics.countries",
      "kind": "EntitySet",
      "url": "demo/geopolitics/countries",
      "source": "Table",
      "role": "reader",
      "policy": "always-allow-read-global-unauthenticated",
      "ordered": false,
      "orderBy": null,
      "orderDirection": null
    },
    {
      "name": "demo.security.cisa_kev",
      "kind": "EntitySet",
      "url": "demo/security/cisa_kev",
      "source": "Table",
      "role": "reader",
      "policy": "always-allow-read-global-unauthenticated",
      "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 demo/geopolitics/countries is workspace demo, 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, and policy names the grant it comes from - always-allow-read-global-unauthenticated for demo, readable by anyone, and always-allow-read-any-authenticated-user for public, readable by any signed-in account. source distinguishes ordinary tables from Views, MaterializedViews, Tasks (scheduled jobs that write to another dataset, whose $metadata carries the task's definition), 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/demo/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.

A URL can't contain a literal space, and curl rejects one before sending anything. For options with spaces in them, such as most $filter and $apply expressions, pass -G and one --data-urlencode per option, and curl encodes each for you. An option that contains OData's own single quotes needs double quotes instead, with the $ escaped as \$.

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 -G 'https://odata.opteryx.app/api/v4/demo/geopolitics/countries' \
  --data-urlencode "\$filter=region eq 'Europe'" \
  --data-urlencode '$orderby=country_name_common' \
  --data-urlencode '$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/demo/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 100,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/demo/geopolitics/gdelt_daily_topic@previous?$top=5'
curl 'https://odata.opteryx.app/api/v4/demo/geopolitics/gdelt_daily_topic@1794067860939?$top=5'
curl 'https://odata.opteryx.app/api/v4/acme/security/findings@YOUR_TAG?$top=5' \
  -H 'Authorization: Bearer YOUR_TOKEN'

A snapshot id is only good for as long as that snapshot is retained, so the second line may need an id from the dataset's $metadata (below) by the time you run it. None of the demo datasets carry a tag, so the last line is a placeholder: substitute a dataset of your own and a tag it holds.

A dataset's metadata shows the snapshot it currently resolves to in Custom.Snapshot.Id, and how many tags it holds in Custom.Tags.Count:

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

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

An unknown tag, or @previous on a dataset with no earlier version, returns 404. An unknown snapshot id currently returns 503 with the message dataset could not be read from storage, not 404; its $metadata does return 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 -G 'https://odata.opteryx.app/api/v4/demo/security/cisa_kev' \
  --data-urlencode '$filter=date_added ge 2025-01-01' \
  --data-urlencode '$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: column *cisa_kev.date_added* (`DATE`) and literal `2025-01-01` (`VARCHAR`) cannot be compared, because their types do not match. Date and time literals are unquoted in OData v4: write `date_added ge 2024-01-01T00:00:00Z`, not `date_added ge '2024-01-01T00:00:00Z'` -- a quoted literal is a string."
  }
}

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 six digits, so the output of Python's datetime.isoformat() can be used as it is. Seven or more are rejected:

curl -G 'https://odata.opteryx.app/api/v4/demo/security/nvd_vulnerabilities' \
  --data-urlencode '$filter=published_at ge 2026-07-01T00:00:00.1234567Z'

{
  "error": {
    "code": "BadRequest",
    "message": "Invalid query: Invalid datetime literal `2026-07-01T00:00:00.1234567Z`: at most 6 fractional-second digits (microseconds) are supported."
  }
}

Truncate to six 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 -G 'https://odata.opteryx.app/api/v4/demo/security/nvd_vulnerabilities' \
  --data-urlencode "\$filter=published_at ge now() sub duration'P30D'" \
  --data-urlencode '$count=true' \
  --data-urlencode '$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.

Converting types with cast()

cast(col, Edm.Type) converts a value before it's compared, for when a column holds data in a different type from the one you need. iso_numeric in the countries dataset is text, zero-padded to three digits, so iso_numeric eq '4' matches nothing - Afghanistan is stored as '004'. Compared as a number, it matches:

curl -G 'https://odata.opteryx.app/api/v4/demo/geopolitics/countries' \
  --data-urlencode '$filter=cast(iso_numeric, Edm.Int32) eq 4' \
  --data-urlencode '$select=country_name_common,iso_numeric'

A value that doesn't convert becomes null, as the OData standard defines it, rather than failing the query - Kosovo has no ISO numeric code, and its empty string simply doesn't match. To find the values that don't convert, compare the cast with null: cast(iso_numeric, Edm.Int32) eq null.

The type name is unquoted, as the standard writes it. cast() works in $compute as well as $filter, so $compute=cast(iso_numeric, Edm.Int32) as iso_code makes the converted value available to $select and $orderby.

Edm.TimeOfDay, Edm.Duration, Edm.Binary and Edm.Guid are valid OData targets but aren't supported, and return a 400 naming what to write instead. Neither is the one-argument form cast(Edm.Type), which casts the entity itself to a derived type - no entity type here has one. isof() isn't implemented.

Don't reach for cast() to compare against a quoted literal: write the literal in the column's own type instead. date_added ge 2025-01-01 is right; cast(date_added, Edm.String) ge '2025-01-01' happens to work for ISO dates but compares text, and can't skip files using their stored min/max.

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 and max are available:

curl -G 'https://odata.opteryx.app/api/v4/demo/geopolitics/countries' \
  --data-urlencode '$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.

$orderby can name an aggregate alias, so $orderby=country_count desc sorts the result above by its count.

Try It

This queries demo.geopolitics.countries live - 250 rows, no token needed. 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. Your OData request is not translated into SQL before it runs; it's compiled directly into an execution plan. Type-mismatch errors are restated for OData - a quoted date or number is told to drop the quotes, and any other mismatch is given the OData cast() to write, in your own column - but other messages can still name SQL types or syntax. Read those as a description of the underlying 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

• OData notebook in Google Colab - query the public NVD and CISA KEV datasets with no account
• Load and Query Data
• Running a Query via the API
• Connecting via Arrow Flight SQL
• Authentication API
• Access and Permissions