Skip to content

Query Metrics#

A metric query names metrics, the group-bys to split them by, and optional filters, ordering and a row limit. The semantic layer generates the SQL from the current build and runs it on Trino as the caller, or answers it from its result cache or from an export, see Who a query runs as. The examples query the sales semantic project from Compile and builds. dbt Semantic Layer clients, such as dbt-sl-sdk and the Arrow Flight SQL JDBC driver, query the same metrics, see Connect dbt clients, and so do BI tools, through tables, see Connect BI tools.

Group-by names#

Group-by Example
A dimension, <entity>__<dimension> customer__country, order__status
A time dimension with a grain order__order_ts__week
The metric time with a grain metric_time__day, metric_time__month
A date part metric_time__extract_dow
A custom granularity of the time spine metric_time__fiscal_quarter

A time group-by takes the time_granularity of its time dimension or any coarser grain, up to year. The date parts are year, quarter, month, day, dow and doy. A group-by is its column name in the result. A cumulative metric with a window and a metric read at an offset need metric_time in every query, see Metric types.

Find metrics and dimensions#

hops semantic metrics sales
hops semantic metrics sales --group-by customer__country
hops semantic dimensions sales --metrics revenue,revenue_per_order
hops semantic entities sales
hops semantic dimension-values sales customer__country --limit 20
hops search --type metric revenue
import hopsworks


project = hopsworks.login()
sales = project.get_semantic_layer_api().get_project("sales")
print([m.name for m in sales.list_metrics()])
print([g.name for g in sales.group_bys(metrics=["revenue"])])
print(sales.compatible_metrics(["customer__country"]))
print(sales.dimension_values("customer__country", limit=100))

dimensions and group_bys list only what every listed metric can be grouped by, and metrics --group-by lists only the metrics that can be grouped by every listed group-by. hops search --type metric finds metrics by name, label and description, each with its semantic project, as does the search box in the UI header.

Query from the CLI#

hops semantic query sales --metrics revenue,orders --group-by customer__country --order-by -revenue
customer__country  revenue  orders
-----------------  -------  ------
SE                 190.75   4
US                 125.75   2
DE                 75.5     2
hops semantic query sales --metrics revenue_per_order,completed_revenue_per_order \
  --group-by customer__country --order-by customer__country -o csv
customer__country,revenue_per_order,completed_revenue_per_order
DE,37.75,7.75
SE,47.6875,35.0625
US,62.875,27.875
Option Meaning
--metrics Metric names, comma-separated or repeated.
--saved-query A saved query to run in place of --metrics.
--group-by Group-bys, comma-separated or repeated.
--where A filter, repeated for AND: --where "{{ Dimension('customer__country') }} = 'SE'", see Filters.
--order-by Metric or group-by names; a leading - sorts descending.
--limit The most rows to return.
--start-time, --end-time Bounds on metric_time, both inclusive, each truncated to the query's time grain.
-o table (default), json or csv.
--explain Print the SQL instead of running it.
--no-cache Run the query on Trino even when the result cache holds it.

hops semantic sql takes the same options except -o, --explain and --no-cache, and prints the SQL without running it. A monthly query with --end-time 2026-02-01 includes all of February, since the bound is truncated to the month. NULLs sort last.

Query from Python#

import hopsworks


project = hopsworks.login()
sales = project.get_semantic_layer_api().get_project("sales")

df = sales.query(
    metrics=["revenue", "revenue_per_order"],
    group_by=["metric_time__month", "customer__country"],
    filters=[("order__status", "IN", ["completed", "shipped"])],
    having=[("revenue", ">", 100)],
    order_by=["-revenue"],
    time_range=("2026-01-01", "2026-03-31"),
    group_limit={"group_by": ["customer__country"], "top": 2, "metric": "revenue"},
    dataframe_type="pandas",
)
sql = sales.sql(metrics=["revenue"], group_by=["metric_time__month"])
Argument Meaning
where Text filters, combined with AND, see Filters.
filters Structured filters, (group_by, op, value), with op one of =, !=, <, <=, >, >=, IN, NOT IN, IS NULL, IS NOT NULL and LIKE. The values are bound as literals and never parsed as SQL.
having Filters on metric values, (metric, op, value).
order_by Metric or group-by names; a leading - sorts descending.
limit, offset The most rows to return, and the rows to skip.
time_range (start, end) bounds on metric_time, both inclusive, each truncated to the query's time grain; either may be None.
group_limit Keep the top groups by a metric, such as the two countries with the most revenue.
dataframe_type pandas (default), polars, pyarrow, or python for a list of dicts.
saved_query A saved query to run in place of metrics.
read_cache False runs the query on Trino even when the result cache holds it; True by default.

A group-by can also be a dict, {"name": "metric_time", "grain": "month"} or {"name": "metric_time", "date_part": "dow"}. Results come back as an Arrow stream when pyarrow is installed, which keeps the column types, and as JSON otherwise. The client works from outside the cluster with an API key, through the same host as hopsworks.login().

Filters#

A text filter is a SQL condition over group-bys. It names a group-by with a call, {{ Dimension('customer__country') }}, {{ TimeDimension('metric_time', 'month') }} or {{ Entity('customer') }}, or by its column name alone, customer__country or metric_time__month, which means the same group-by:

hops semantic query sales --metrics revenue --group-by customer__country \
  --where "customer__country IN ('SE', 'DE')" --order-by -revenue
customer__country  revenue
-----------------  -------
SE                 190.75
DE                 75.5
  • A filter reads the group-bys it names and nothing else: a name that is not a group-by, such as a feature's, and a qualified name, such as orders.amount, are refused with SEMANTIC_LAYER_INVALID_QUERY.
  • A group-by in a filter need not be a group-by of the query: a filter on order__status keeps the rows of that status, whatever the query groups by.
  • {{ TimeDimension('metric_time') }} without a grain takes the grain metric_time would have as a group-by of the query, the same for every metric of the query.
  • String values take any Unicode text, such as 'Malmö', 'Łódź' or '東京', in text filters, structured filters and metric filters alike.
  • A filter on a metric's value is a having, or a Metric() filter.

Query without metrics#

A query without metrics returns the distinct values of its group-bys under its filters:

import hopsworks


project = hopsworks.login()
sales = project.get_semantic_layer_api().get_project("sales")
print(
    sales.query(
        group_by=["customer__country", "order__status"],
        order_by=["customer__country", "order__status"],
    )
)
  customer__country order__status
0                DE     completed
1                DE       shipped
2                SE     cancelled
3                SE     completed
4                SE       shipped
5                US     cancelled
6                US     completed

The rows come from the semantic model that reaches every group-by and filter with the fewest joins, and metric_time from the time spine. Over REST, the query body takes "minMaxOnly": true for the smallest and largest value of its one group-by, and "applyGroupBy": false for every row instead of the distinct ones. The CLI's hops semantic query needs --metrics or --saved-query; hops semantic dimension-values lists the values of one group-by.

Query in the UI#

Open Semantic Layer in the project sidebar, pick a semantic project, and click Query metrics. The Metrics card of the overview lists the metrics of the current build; clicking a metric shows its definition, its type, and what it can be grouped by. The Saved queries card lists the saved queries, each with Run, which opens the query workbench on it.

The query workbench takes:

  • Metrics, one or more;
  • Group by, offered only from what every picked metric can be grouped by, with a Grain for each time group-by; picking a metric that needs metric_time puts it first, at the metric's grain;
  • Filters, a group-by, an operator and a value each; numbers and true or false are sent as such, a quoted value is sent as text, and IN values are separated with commas;
  • Order by, a metric or group-by and a direction;
  • Limit, from 1 to 1,000 rows, 100 by default.

Run shows the rows under Results, and Show SQL shows the generated SQL under SQL without running it. The rows are read on Trino as you, unless the result cache or an export answers the query.

Query over REST#

Other clients query the gateway at https://<hopsworks host>/semantic-layer/v1/projects/<semantic project id>/, with an API key:

curl -H "Authorization: ApiKey $HOPSWORKS_API_KEY" \
  "https://<hopsworks host>/semantic-layer/v1/projects/7/metrics"

curl -X POST -H "Authorization: ApiKey $HOPSWORKS_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"metrics": ["revenue"], "groupBy": ["customer__country"], "orderBy": [{"name": "revenue", "descending": true}]}' \
  "https://<hopsworks host>/semantic-layer/v1/projects/7/query"
Method Path Returns
GET metrics The metrics of the current build.
GET dimensions?metrics=a,b The dimensions every listed metric can be grouped by.
GET entities?metrics=a,b The entities every listed metric can be grouped by.
GET group-bys?metrics=a,b Dimensions, entities and metric_time.
POST compatible-metrics The metrics that can be grouped by every group-by in {"groupBy": [...]}.
POST sql The SQL of a query, without running it.
POST query The rows of a query: JSON by default, an Arrow IPC stream with Accept: application/vnd.apache.arrow.stream, or CSV with Accept: text/csv.
GET query?metrics=a,b&groupBy=c&format=csv The same with URL parameters, for clients that send only a URL and headers, such as Excel's From Web, see Excel and ODBC.
POST dimension-values The distinct values of {"groupBy": ..., "limit": 1000}.
GET saved-queries The saved queries of the current build.

The query body takes metrics, savedQuery, groupBy, where, filters, having, timeRange, orderBy, limit, offset and groupLimit, in the camel case of the Python arguments, and minMaxOnly and applyGroupBy for a query without metrics. "cache": false in a query or dimension-values body skips the result cache. "useExports": false in a query body computes the rows from the sources even when an export holds them. A JSON result carries columns, rows, rowCount, truncated and sql. A CSV result, with format=csv or Accept: text/csv, is a header row of the column names and a line per row, with timestamps as yyyy-MM-dd HH:mm:ss and an empty field for null. On GET, metrics, groupBy and orderBy are comma-separated or repeated, and given empty (groupBy=) to give a saved query none; where is repeated; limit, offset, savedQuery, cache and useExports (true or false) take one value; and format=csv asks for CSV. Any other parameter is refused with 400, so filters, having, timeRange and the other fields of a body need a POST. Every response carries the buildId that answered and whether it is stale; an Arrow result carries them in the X-Semantic-Build and X-Semantic-Stale headers.

Header On Value
X-Semantic-Max-Rows Arrow query The row cap of an Arrow result.
X-Semantic-Truncated query true when the row cap cut the result. An Arrow result carries it only when the cut is known before the first rows are sent, so treat one with X-Semantic-Max-Rows rows as cut.
X-Semantic-Feature-Groups query, sql The ids of the feature groups the query reads through its semantic models, comma-separated.
X-Semantic-Sql-Base64 Arrow query The SQL that ran, base64 encoded.
X-Semantic-Cache JSON query hit, miss or bypass, see Result cache.
X-Semantic-Served-From, X-Semantic-Export-Feature-Group query The export, and its feature group, an answer was read from, see Answer queries from an export.
X-Semantic-Source-Versions query The Delta version of each source feature group the rows were computed from, on an answer read from an export.
X-Semantic-Definition query The definition fingerprint of the saved query the request names.

Limits#

Limit Default When it is reached
Rows of an Arrow result 1,000,000 The result stops there; the Python client logs a warning when a result fills the cap.
Rows of a JSON result 100,000 The result stops there and sets truncated.
Size of a JSON or CSV result 64 MiB, counted as JSON text 413 SEMANTIC_LAYER_INVALID_QUERY: the result is larger than 67108864 bytes as JSON (MAX_JSON_BYTES); add a limit or filters, or ask for it as Arrow (Accept: application/vnd.apache.arrow.stream).
Query time 300 seconds 504 SEMANTIC_LAYER_TIMEOUT.
Queries running at once, per gateway replica 16 A query waits up to 30 seconds for a slot, then gets 503 SEMANTIC_LAYER_BUSY.
Request body 1 MiB The request is refused.
Rows in the UI workbench 1,000 The limit field accepts no more.

The semantic-gateway Helm values maxRows, maxJsonRows, maxJsonBytes, queryTimeoutSeconds and queryConcurrency change the first five limits, see Semantic gateway values. Add a limit, a filter or fewer group-bys to stay under the row caps. The limits of GraphQL and Arrow Flight SQL are in Connect dbt clients.

Errors#

Error Status Meaning Fix
SEMANTIC_LAYER_NO_VALID_BUILD 409 No build has compiled valid yet. hops semantic compile <name> --wait, and fix the issues.
SEMANTIC_LAYER_INVALID_QUERY 400 An unknown metric, group-by or filter, a group-by the metrics cannot reach, a metric that needs metric_time without it, or a shape under Limits. hops semantic dimensions <name> --metrics ... lists what they can reach.
SEMANTIC_LAYER_EXECUTION_FAILED 502 Trino refused or failed the generated SQL; its message follows. hops semantic sql shows the SQL.
SEMANTIC_LAYER_FORBIDDEN 403 Not a member of the project, or an API key without the FEATURESTORE and TRINO scopes. Add the member or the scopes.
SEMANTIC_LAYER_NOT_FOUND 404 No semantic project with that id, or no saved query of that name. hops semantic list, hops semantic saved-queries <name>.
SEMANTIC_LAYER_UNAUTHENTICATED 401 No Authorization header, a malformed one, or a credential Hopsworks refuses. Send the API key as ApiKey <api key> or Bearer <api key>.
SEMANTIC_LAYER_TIMEOUT 504 The query ran longer than the query time of Limits. Add a limit or filters, or fewer group-bys.
SEMANTIC_LAYER_BUSY 503 Every query slot of the gateway replica stayed taken for 30 seconds, or its Arrow buffers are full. Run the query again.
SEMANTIC_LAYER_UNAVAILABLE 503, 502 The gateway could not reach Hopsworks, 503, or Hopsworks answered with an error, 502. Run the query again once Hopsworks answers.
SEMANTIC_LAYER_INVALID_BUILD 502 The gateway could not load the build that answers queries. The gateway's log has the cause.
SEMANTIC_LAYER_INTERNAL 500 The gateway failed. The gateway's log has the cause.

In Python, each is a SemanticLayerException with the code in error_code. A replica keeps a caller's session for 60 seconds, so while Hopsworks restarts, as during an upgrade, queries keep running until the sessions they need are older than that, and then answer SEMANTIC_LAYER_UNAVAILABLE until Hopsworks is back.