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#
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_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
- 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 withSEMANTIC_LAYER_INVALID_QUERY. - A group-by in a filter need not be a group-by of the query: a filter on
order__statuskeeps the rows of that status, whatever the query groups by. {{ TimeDimension('metric_time') }}without a grain takes the grainmetric_timewould 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 aMetric()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_timeputs it first, at the metric's grain; - Filters, a group-by, an operator and a value each; numbers and
trueorfalseare sent as such, a quoted value is sent as text, andINvalues 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.