Connect BI Tools#
Over Arrow Flight SQL, the semantic gateway shows each semantic project as tables: a metrics table with every group-by and every metric as columns, and one table per saved query. A BI tool or SQL client that browses tables lists them, and the gateway translates the SQL it sends over them into one metric query of the caller, see Who a query runs as. Statements in dbt's JDBC dialect, {{ semantic_layer.<call>(...) }}, keep working on the same port; a statement without such a call is read as SQL over the tables.
The examples use the sales semantic project of Define metrics, with id 7, and the Flight SQL host semantic.hopsworks.example.com of Connect dbt clients.
Experimental
The tables are tested with the Apache Arrow Flight SQL JDBC driver 19.0.0: its DatabaseMetaData calls, schema probes, prepared statements and the SQL shapes Tableau generates, which DBeaver and other JDBC tools use too. dbt's Tableau connector 1.3.0 and Power BI connector 1.0.0 are not tested against Hopsworks yet, and the SQL they generate can hold shapes the gateway refuses. An administrator can turn the tables off, see For administrators.
The tables#
| Level | Name |
|---|---|
| Catalog | semantic_layer |
| Schema | The environment id, the semantic project's id: 7 |
| Tables | metrics, and one table per saved query, named as the saved query |
- Every table has the type
VIEW; a tool that lists onlyTABLEobjects finds none. - A caller sees only the schema of the environment id it connects with, or, connected to a Hopsworks project, the schemas of its semantic projects.
- The tables have no primary or foreign keys.
- They are the tables of the semantic project's current build, and a prepared statement keeps the build it was prepared on.
One connection for a Hopsworks project#
A client that sends the header projectid: <Hopsworks project id> in place of environmentid connects to every semantic project of that project at once, which is how Superset connects:
| Level | Name |
|---|---|
| Catalog | semantic_layer |
| Schema | One per semantic project of the project, named as the semantic project: sales |
| Tables | In each schema, metrics and one table per saved query |
- A statement names its tables with their schema,
"sales"."metrics", and reads the tables of one schema. It runs as a metric query of that semantic project, which the result cache, the query history and the limits count it under. - A semantic project without a valid build has a schema and no tables, and a project without semantic projects has no schemas.
- Creating or deleting a semantic project changes the schemas, and needs no change to the connection.
- Statements in dbt's JDBC dialect need
environmentid. - A call with both headers, or neither, is refused with
UNAUTHENTICATED.
| Statement | Refused with |
|---|---|
| A table without its schema | name the statement's table with its schema, the semantic project, as "<schema>"."<table>"; the schemas are [...] |
| Tables of two schemas | a statement reads the tables of one schema, not of [...]; the schemas are [...] |
| A schema that is not a semantic project of the project | no schema <name>; the schemas are [...] |
A {{ semantic_layer.<call>(...) }} | with the projectid header the gateway answers SQL over the BI tables; send environmentid, the semantic project's id, for a {{ semantic_layer.<call>(...) }} statement |
Columns of metrics#
In this order:
metric_timeat each grain some metric can be queried at, from the finest:metric_time__day,metric_time__week,metric_time__month,metric_time__quarter,metric_time__year.- The other group-bys, named as in queries: entities such as
customer, dimensions such ascustomer__country, and each time dimension at each of its grains, such asorder__order_ts__daytoorder__order_ts__year. -
Every metric, such as
revenue,ordersandrevenue_per_order. -
Date parts are not columns:
EXTRACTreads them from a time column, see The SQL. - Each column has the type the planner gives it when it plans the column alone, such as
TIMESTAMPformetric_time__day,VARCHARforcustomer__countryandDOUBLEforrevenue, and every column is nullable. Discovery, a schema probe, a prepared statement and the result all declare that type. - Each column carries its description as its remarks, and the Arrow field metadata
semantic.kind,metricorgroup_by. - A column the planner cannot type alone is left out, and so is a metric whose name a group-by has; the gateway logs which and why.
A saved query's table has the saved query's columns: revenue_by_country has metric_time__month, customer__country, revenue and orders. Its rows are the saved query's metrics and group-bys under the saved query's filters; the saved query's order and limit do not apply, the statement's do. A saved query named metrics has no table.
A metric column is the metric#
Aggregates around a metric column mean the metric itself
SUM, AVG, MIN, MAX, COUNT and ANY_VALUE around a metric column all return the metric, computed by its definition at the grain of the GROUP BY. AVG(revenue) returns revenue, and COUNT(revenue) returns revenue too, not a count. dbt's Semantic Layer connectors work the same way: Tableau always wraps a metric in SUM, and Power BI sends the aggregate picked in the report.
SELECT customer__country, AVG(revenue) AS avg_revenue, AVG(revenue_per_order) AS rpo
FROM semantic_layer."7".metrics
GROUP BY customer__country
ORDER BY 2 DESC
avg_revenue is the revenue of each country, the same rows as SELECT customer__country, SUM(revenue) ..., and rpo is revenue_per_order computed per country. A statement selects each column once, so SUM(revenue) and AVG(revenue) in one statement are refused.
A statement over one table is one metric query:
- the metrics are the metric columns it references, with or without an aggregate around them;
- the group-bys are the
GROUP BYlist; - without
GROUP BYand without an aggregate, the group-by columns it selects are the group-bys, soSELECT * FROM revenue_by_countryreturns the saved query's rows, andSELECT customer__country, revenue FROM metricsreturns revenue per country; - without
GROUP BY,SELECT SUM(revenue) FROM metricsreturns the total.
A ratio or a count of distinct customers is right at every grain, since the gateway computes the metric for the grain asked for, and never adds up the rows of a finer one.
The SQL#
SELECT customer__country,
DATE_TRUNC('month', CAST(order__order_ts__day AS DATE)) AS m,
SUM(revenue)
FROM semantic_layer."7".metrics
WHERE customer__country IN ('SE', 'US')
GROUP BY customer__country, DATE_TRUNC('month', CAST(order__order_ts__day AS DATE))
HAVING SUM(revenue) > 100
ORDER BY 3 DESC
LIMIT 50
is the metric query metrics=[revenue], group_by=[customer__country, order__order_ts__month], the filter customer__country IN ('SE', 'US'), having=[revenue > 100], order_by=[-revenue] and limit=50, with the column order__order_ts__month returned as m.
| Clause | Accepted |
|---|---|
FROM | One table: metrics, "7".metrics, semantic_layer."7".metrics or a saved query's table, or "sales".metrics when connected to a Hopsworks project, with an optional alias; or one derived table that only renames the columns of one table. |
SELECT | Group-by columns, metric columns, the aggregates above around a metric column, *, and aliases. SELECT DISTINCT over group-bys returns their distinct values. |
| Time | DATE_TRUNC('<grain>', x) and Tableau's DATE_TRUNC(CAST(x AS DATE), '<grain>') over a time column, to that grain or a coarser one whose periods each hold whole periods of the column (a week column truncates only to week); EXTRACT(<part> FROM x) with YEAR, QUARTER, MONTH, DAY, ISODOW or DOY, the date parts year, quarter, month, day, dow and doy, from a column whose periods each fall in one period of the part (MONTH from a month column, not DAY). |
CAST | Only casts that keep every value: DATE to TIMESTAMP; TIMESTAMP to DATE for a column at day grain or coarser; TINYINT, SMALLINT and INTEGER to a wider integer, to DOUBLE, or to DECIMAL(p, 0) with enough digits; BIGINT to DECIMAL(p, 0) with p of at least 19; VARCHAR(n) to VARCHAR(m) with m of at least n, or to VARCHAR; a column to its own type. |
WHERE | Comparisons, IN, NOT IN, BETWEEN, LIKE, IS NULL and IS NOT NULL over group-bys and literals, joined with AND, OR and NOT; ILIKE and NOT ILIKE with a string pattern, which run as LOWER(<column>) LIKE the pattern in lower case, as Superset's filter search sends them. Literals are PostgreSQL style, such as DATE '2026-01-01' and TIMESTAMP '2026-01-01 00:00:00'. |
GROUP BY | Group-by columns and the time expressions above, by expression, alias or position. |
HAVING | Comparisons, IN, BETWEEN, LIKE, IS NULL and IS NOT NULL over metric columns, with or without an aggregate, and over group-bys. A metric only in HAVING is computed and not returned. |
ORDER BY | Columns, aggregates, aliases and positions, ASC or DESC; NULLS LAST is the order already. A metric only in ORDER BY is computed and not returned. |
LIMIT, OFFSET | Numbers. SQL Server's SELECT TOP n, which Power Query's ODBC connector can send, is read as LIMIT n. |
- Names match without regard to case; quote an identifier with
". HAVINGruns beforeLIMIT, in the metric query, so a limited result holds the groups that pass theHAVING.WHERE 1=0andLIMIT 0, the schema probes of BI tools, are answered from the table's columns without running a query.- A statement runs as a metric query: it uses the result cache, it fails when its result is over the row cap of Limits, and its time values are UTC wall time, see Timestamps. It is recorded in the query history with the surface
BI, and is never answered from an export.
What is refused#
Anything outside the grammar above is refused before it runs, with the Flight SQL status INVALID_ARGUMENT and a message that names the construct:
INVALID_ARGUMENT: the BI tables do not support joins; a statement over a BI table is one semantic query: group-by columns, metric columns (SUM, AVG, MIN, MAX, COUNT and ANY_VALUE around one mean the metric), DATE_TRUNC, EXTRACT and lossless CASTs of group-bys, WHERE on group-bys, GROUP BY, HAVING, ORDER BY, LIMIT and OFFSET
| Statement | The message names |
|---|---|
| A join | joins |
UNION, INTERSECT, EXCEPT | UNION, and so on |
A subquery in SELECT, WHERE or IN | subqueries |
A window function, WINDOW, QUALIFY | window functions, WINDOW clauses, QUALIFY |
| A derived table that filters, groups or sorts, or two derived tables | derived tables other than one that renames columns, more than one derived table |
| Another table, or another schema than the environment id | other tables (...), followed by the tables there are |
An expression over columns, such as revenue * 2 or UPPER(customer__country) | * (expressions over columns), UPPER (expressions over columns) |
COUNT(*), COUNT(DISTINCT revenue) | COUNT(*), COUNT(DISTINCT ...) |
| An aggregate around a group-by column | SUM over the group-by column customer__country |
A metric column in WHERE | metric columns in WHERE (revenue): filter metrics in HAVING |
A metric column in GROUP BY | GROUP BY a metric column (revenue) |
SELECT DISTINCT with a metric column | SELECT DISTINCT with metric columns |
ROLLUP, CUBE, GROUPING SETS | ROLLUP, and so on |
A cast that can change values, such as BIGINT to DOUBLE | CAST of <column> from BIGINT to DOUBLE, which can change values |
DATE_TRUNC to a finer grain than the column, or over a column that is not a time column | DATE_TRUNC to day, finer than metric_time__month |
DATE_TRUNC over a column whose periods can straddle the target, such as a week into a month | DATE_TRUNC to month over metric_time__week, whose periods can straddle two months |
EXTRACT of a part that varies within the column's periods, such as the day of a month | EXTRACT(DAY) over metric_time__month, whose periods do not each fall in one day |
EXTRACT(DOW FROM x) | EXTRACT(DOW), whose Sunday is 0 (ISODOW is supported) |
NULLS FIRST | NULLS FIRST: NULL sorts last |
A column selected twice, such as SUM(revenue), AVG(revenue) | selecting revenue twice |
A parameter marker, ? | parameter markers (?): prepared statements with parameters are not supported yet |
An ODBC function escape, such as {fn CONVERT(x, SQL_DOUBLE)} | {FN CONVERT} (expressions over columns) |
A statement the grammar accepts can still fail as a metric query, with INVALID_ARGUMENT and the reason: an unknown column, a group-by column selected but missing from GROUP BY, a group-by the metrics cannot reach, or a statement that does not parse. SELECT * over the metrics table asks for every metric by every group-by in one query, which usually fails this way; the message then adds SELECT * over the metrics table asks for every metric by every group-by in one query: select the metric and group-by columns you need, or read a saved query's table.
Connect a tool#
Every tool authenticates as dbt's clients do, with the environment id and an API key with the FEATURESTORE and TRINO scopes, see Endpoints.
DBeaver and other JDBC tools#
Add the Apache Arrow Flight SQL JDBC driver, org.apache.arrow:flight-sql-jdbc-driver 19.0.0, as a driver:
| Setting | Value |
|---|---|
| Driver class | org.apache.arrow.driver.jdbc.ArrowFlightJdbcDriver |
| URL | jdbc:arrow-flight-sql://semantic.hopsworks.example.com:18446?environmentId=7&useEncryption=true&tlsRootCerts=/home/me/hopsworks_semantic_119_7_ca.pem |
Driver property token | The API key |
tlsRootCertsis the cluster's CA, whichhops semantic connect sales --ca-path <file>writes, see Connection details; leave it out when the client already trusts the certificate.- In DBeaver, the tables show under the catalog
semantic_layer, the schema7, and Views. - Run the client JVM with
-Duser.timezone=UTCto see the time values as stored, see Timestamps.
ODBC: Power BI, Excel and other ODBC tools#
The Apache Arrow Flight SQL ODBC driver, shipped for Windows and Linux with Apache Arrow releases since 25.0.0, takes a connection string:
Driver={Apache Arrow Flight SQL ODBC Driver};host=semantic.hopsworks.example.com;port=18446;environmentId=7;token=<api key>;useEncryption=true;trustedCerts=C:\certs\hopsworks_semantic_119_7_ca.pem;useSystemTrustStore=false
The driver sends connection properties it does not know, such as environmentId, as headers. In Power BI and Excel, Get Data > ODBC with this connection string imports the tables, or the result of a SQL statement given under Advanced options, in Import mode. Install, a DSN, what the driver was tested with and what it refuses: Excel and ODBC.
dbt's Tableau and Power BI connectors#
dbt's connectors reach Arrow Flight SQL on port 443 of one host, the way they reach dbt Cloud, which the dbt-compatible endpoint layout serves. An administrator turns it on with a host name, such as semantic.example.com.
| Connector field | Value |
|---|---|
| Host | The layout's host. dbt's Tableau connector adds :443 after .com, so type <host>:443 for a host that does not end in .com. dbt's Power BI connector uses TLS on port 443 only for a host that contains .com. |
| Environment ID | The semantic project's id, 7. |
| Token | The API key. |
- dbt's Tableau connector turns off the check of the server certificate (
disableCertificateVerification), so it would not notice a server impersonating the gateway; use it on a network you trust. dbt's Power BI connector checks the server certificate against the Windows trust store, so the layout's TLS Secret needs a certificate Windows trusts. - The connectors list the
metricstable and one table per saved query; dbt Cloud names itsmetricstableALL. - Tableau sends every metric as
SUM(<metric>), which means the metric. - dbt's Power BI connector works in DirectQuery mode only.
Other tools#
| Tool | How |
|---|---|
| Tableau, without dbt's connector | Other Databases (JDBC) with the JDBC driver and URL above, browsing the tables, or with Custom SQL holding a {{ semantic_layer.query(...) }}. Tableau aggregates a Custom SQL result again, which is wrong for ratios and other metrics that do not add up; the tables do not have this problem. Not tested. |
| Dot | Its GraphQL URL is https://<hopsworks host>/semantic-layer/api/graphql, or https://<layout host>/api/graphql with the dbt-compatible layout. Not tested. |
| Mode, Hex | They send dbt's JDBC dialect to a host, which the dbt-compatible layout serves. Not tested. |
A tool that runs as a service, such as Tableau Cloud, Dot, Mode or Hex, reaches the gateway from the internet, and needs a certificate from a public CA.
The dbt-compatible endpoint layout#
dbt's Semantic Layer serves Arrow Flight SQL and GraphQL on port 443 of one host, with GraphQL at /api/graphql. The hopsworks chart serves the gateway the same way on a host of your choice, through the cluster's ingress-nginx controller:
hopsworks:
semanticLayer:
dbtCompatibleLayout:
enabled: true
host: semantic.example.com
tlsSecret: semantic-example-com-tls
timeoutSeconds: 360
hopsworks.semanticLayer.dbtCompatibleLayout value | Default | Meaning |
|---|---|---|
enabled | false | Render the two Ingresses below. Needs global._hopsworks.semanticLayer.enabled. |
host | "" | The host name the connectors are given; required when enabled. dbt's Power BI connector accepts only names that contain .com. |
tlsSecret | null | A kubernetes.io/tls Secret with the certificate and key for host. Null serves the ingress controller's default certificate. |
timeoutSeconds | 360 | Seconds nginx waits on a Flight call or a GraphQL request. Keep it at least semantic-gateway.queryTimeoutSeconds plus the 30 seconds a query may wait for a slot, or nginx ends long queries first. |
nginx annotations apply to a whole Ingress, so the layout is two Ingresses on host, both with tlsSecret:
| Ingress | Path | Routes to |
|---|---|---|
<release>-semantic-layer-dbt-flight | /arrow.flight.protocol.FlightService/, implementation specific, the path type ingress-nginx's path validation allows a dot in | The gateway's Flight port over gRPC with TLS, with gRPC read and send timeouts of timeoutSeconds. |
<release>-semantic-layer-dbt-graphql | /api/graphql, exact | The gateway's GraphQL handler at /semantic-layer/api/graphql, over HTTPS. |
<release> is the full name of the hopsworks release, hopsworks by default. Point the host's DNS name at the ingress controller. Arrow Flight SQL clients then connect to grpc+tls://semantic.example.com:443, and dbt-sl-sdk with host="semantic.example.com" and without DBT_SL_GRAPHQL_URL_FORMAT and DBT_SL_ADBC_URL_FORMAT, since its defaults are dbt's layout.
For administrators#
semantic-gateway value | Default | Meaning |
|---|---|---|
biTables.enabled | true | Serve the tables, BI_TABLES_ENABLED in the gateway. |
cors.allowedOrigins | [] | Browser origins whose pages may call the GraphQL API, such as a spreadsheet add-in's, CORS_ALLOWED_ORIGINS in the gateway; "*" allows any. Requests authenticate with the Authorization header, never cookies. |
With false, the gateway lists no schemas, tables or table types, and refuses a statement without a {{ semantic_layer.<call>(...) }} with the statement must hold one {{ semantic_layer.<call>(...) }}; dbt's JDBC dialect works as before.