Connect dbt Semantic Layer Clients#
The semantic gateway serves the two APIs of dbt's Semantic Layer: Arrow Flight SQL with dbt's JDBC dialect, which runs queries, and GraphQL, which answers metadata and the SQL of a query. A client written for dbt's Semantic Layer connects with a host, an API key, and an environment id, which in Hopsworks is the id of a semantic project. A query runs on Trino as the API key's user, as a query from Python or the CLI does, unless the result cache or an export answers it, see Who a query runs as. BI tools and SQL clients that browse tables read the same port as tables, see Connect BI tools.
The examples use the sales semantic project of Define metrics, with id 7, in the project with id 119. The Hopsworks host is hopsworks.example.com, and the load balancer of the Flight SQL port is semantic.hopsworks.example.com.
Endpoints#
| API | Address | Used by |
|---|---|---|
| GraphQL | https://<hopsworks host>/semantic-layer/api/graphql | dbt-sl-sdk for metadata and compile_sql, GraphQL clients |
| Arrow Flight SQL | grpc+tls://<flight host>:18446 | dbt-sl-sdk for queries, ADBC, the Arrow Flight SQL JDBC driver, BI tools |
| Both, dbt's layout | https://<layout host>/api/graphql and grpc+tls://<layout host>:443 | dbt's Tableau and Power BI connectors, and tools that take a dbt host, when an administrator turns on the dbt-compatible endpoint layout |
- The environment id is the semantic project's id, which
hops semantic listprints underID. - The Flight SQL host is the load balancer of the semantic gateway, see For administrators. Inside the cluster, it is
semantic-gateway.service.consulorsemantic-gateway.<namespace>.svc, on the same port. - Both APIs take an API key with the
FEATURESTOREandTRINOscopes, asAuthorization: Bearer <api key>, the form dbt's clients send, or asAuthorization: ApiKey <api key>. The caller must be a member of the semantic project's project. - Both APIs use TLS. The Flight SQL port presents a certificate issued by the cluster's CA, so a client outside the cluster needs that CA unless it already trusts it.
Connection details#
Hopsworks hands out what a client needs, apart from the API key.
Environment id: 7
GraphQL (metadata, compileSql): https://hopsworks.example.com/semantic-layer/api/graphql
ADBC (queries): grpc+tls://semantic.hopsworks.example.com:18446
JDBC (queries): jdbc:arrow-flight-sql://semantic.hopsworks.example.com:18446?environmentId=7
CA certificate: /tmp/hopsworks_semantic_119_7_ca.pem
export DBT_SL_GRAPHQL_URL_FORMAT='https://{server_host}/semantic-layer/api/graphql'
export DBT_SL_ADBC_URL_FORMAT=grpc+tls://semantic.hopsworks.example.com:18446
export SSL_CERT_FILE=/tmp/hopsworks_semantic_119_7_ca.pem
export REQUESTS_CA_BUNDLE=/tmp/hopsworks_semantic_119_7_ca.pem
import hopsworks
project = hopsworks.login()
sales = project.get_semantic_layer_api().get_project("sales")
info = sales.connection_info()
print(info["environment_id"], info["jdbc_url"])
print(info["env"])
7 jdbc:arrow-flight-sql://semantic.hopsworks.example.com:18446?environmentId=7
{'DBT_SL_GRAPHQL_URL_FORMAT': 'https://{server_host}/semantic-layer/api/graphql', 'DBT_SL_ADBC_URL_FORMAT': 'grpc+tls://semantic.hopsworks.example.com:18446', 'SSL_CERT_FILE': '/tmp/hopsworks_semantic_119_7_ca.pem', 'REQUESTS_CA_BUNDLE': '/tmp/hopsworks_semantic_119_7_ca.pem'}
hops semantic connect prints the export lines on standard output and the rest on standard error, so eval "$(hops semantic connect sales)" sets the variables in the shell. It writes the cluster's CA to /tmp/hopsworks_semantic_<project id>_<semantic project id>_ca.pem, or to --ca-path, and connection_info(ca_path=...) does the same. hops semantic connect sales --json prints every field.
DBT_SL_ADBC_URL_FORMAT is a template with {server_host} only when the Flight SQL port is on the Hopsworks host, and that host has no port; otherwise it is the full URL of the load balancer.
In the UI, open the semantic project and click Connect a client, next to Query metrics. The dialog shows the Environment ID, the GraphQL URL, the ADBC URL, the JDBC URL, the export lines for dbt-sl-sdk under dbt-sl-sdk environment, and the Cluster CA with Download ca.pem.
Over REST, GET /hopsworks-api/api/project/<project id>/semantic/projects/<semantic project id>/connection answers:
{
"environmentId": 7,
"graphqlUrl": "https://hopsworks.example.com/semantic-layer/api/graphql",
"adbcUrl": "grpc+tls://semantic.hopsworks.example.com:18446",
"jdbcUrl": "jdbc:arrow-flight-sql://semantic.hopsworks.example.com:18446?environmentId=7",
"flightHost": "semantic.hopsworks.example.com",
"flightPort": 18446,
"env": {
"DBT_SL_GRAPHQL_URL_FORMAT": "https://{server_host}/semantic-layer/api/graphql",
"DBT_SL_ADBC_URL_FORMAT": "grpc+tls://semantic.hopsworks.example.com:18446"
},
"caPem": "-----BEGIN CERTIFICATE-----\n..."
}
Data owners and data scientists of the project read it; an API key needs the FEATURESTORE scope. caPem is null when Hopsworks could not read the CA.
dbt-sl-sdk#
dbt-sl-sdk reads metadata over GraphQL and runs queries over ADBC, on Arrow Flight SQL. It reads DBT_SL_GRAPHQL_URL_FORMAT and DBT_SL_ADBC_URL_FORMAT when it is imported, so set them before the Python process starts:
pip install "dbt-sl-sdk[sync]==0.13.4"
eval "$(hops semantic connect sales)"
export HOPSWORKS_API_KEY=<api key>
python sales_metrics.py
sales_metrics.py:
import os
from dbtsl import SemanticLayerClient
client = SemanticLayerClient(
environment_id=7,
auth_token=os.environ["HOPSWORKS_API_KEY"],
host="hopsworks.example.com",
)
with client.session():
print([m.name for m in client.metrics()])
table = client.query(
metrics=["revenue", "orders"],
group_by=["customer__country"],
order_by=["-revenue"],
)
print(table.to_pandas())
['revenue', 'orders', 'revenue_per_order', 'completed_revenue_per_order', 'cancelled_orders']
customer__country revenue orders
0 SE 190.75 4
1 US 125.75 2
2 DE 75.50 2
hostis the Hopsworks host, with its port when it has one.- dbt-sl-sdk has no option for a CA. With a private CA, ADBC reads it from
SSL_CERT_FILEand the GraphQL client fromREQUESTS_CA_BUNDLE, which theevalsets. Each variable replaces the client's default trust store, so the file must also hold the CA of the Hopsworks host's certificate when another CA issued it. client.query(..., read_cache=False)runs the query on Trino even when the gateway's result cache holds it.query,query(saved_query=...)anddimension_valuesrun over ADBC;metrics,dimensions,entities,measures,saved_queriesandcompile_sqlare answered over GraphQL.- dbt-sl-sdk's GraphQL client used on its own,
SyncGraphQLClient, reads metadata andcompile_sql; itscreate_queryandqueryare refused, see GraphQL.
The GraphQL API follows the metadata operations of dbt-sl-sdk 0.13.4, and the examples use it with adbc-driver-flightsql 1.13.0.
Arrow Flight SQL JDBC driver#
The Apache Arrow Flight SQL JDBC driver, org.apache.arrow:flight-sql-jdbc-driver, connects with dbt's JDBC URL:
With a private CA, add the CA file:
jdbc:arrow-flight-sql://semantic.hopsworks.example.com:18446?environmentId=7&token=<api key>&useEncryption=true&tlsRootCerts=/tmp/hopsworks_semantic_119_7_ca.pem
The driver sends environmentId as the environmentid header and token as Authorization: Bearer <token>. The examples use version 19.0.0 of the driver. Every statement is a {{ semantic_layer.<call>(...) }} of dbt's JDBC dialect:
select * from {{
semantic_layer.query(metrics=['revenue', 'orders'],
group_by=[Dimension('metric_time').grain('month'), 'customer__country'],
where="{{ Dimension('order__status') }} != 'cancelled'",
order_by=['-revenue'],
limit=10)
}}
The driver's DatabaseMetaData lists the catalog semantic_layer, the semantic project's id as its schema, and the metrics table and a table per saved query in it, which a statement without {{ }} queries with plain SQL, see Connect BI tools.
ADBC#
adbc-driver-flightsql connects to grpc+tls://<flight host>:18446, with the API key and the environment id as headers:
pip install "adbc-driver-flightsql==1.13.0" pyarrow
export SSL_CERT_FILE=/tmp/hopsworks_semantic_119_7_ca.pem
export HOPSWORKS_API_KEY=<api key>
import os
import adbc_driver_flightsql.dbapi as flight_sql
from adbc_driver_flightsql import DatabaseOptions
conn = flight_sql.connect(
"grpc+tls://semantic.hopsworks.example.com:18446",
db_kwargs={
DatabaseOptions.AUTHORIZATION_HEADER.value: "Bearer "
+ os.environ["HOPSWORKS_API_KEY"],
DatabaseOptions.RPC_CALL_HEADER_PREFIX.value + "environmentid": "7",
},
)
with conn.cursor() as cur:
cur.execute(
"select * from {{ semantic_layer.query(metrics=['revenue'], "
"group_by=['customer__country'], order_by=['-revenue']) }}"
)
print(cur.fetch_arrow_table().to_pandas())
conn.close()
ADBC reads a private CA from SSL_CERT_FILE.
dbt's JDBC dialect#
A statement holds exactly one {{ semantic_layer.<call>(...) }}, alone or inside SQL around it. The arguments are Python literals as dbt writes them: strings in single or double quotes, numbers, True, False, None, lists, and the calls Dimension(...), TimeDimension(...), Entity(...) and Metric(...).
| Call | Arguments | Columns |
|---|---|---|
metrics() | page_number, page_size | NAME, DESCRIPTION, TYPE, TYPE_PARAMS, FILTER, DIMENSIONS, QUERYABLE_GRANULARITIES, LABEL, REQUIRES_METRIC_TIME |
dimensions(metrics=[...]) | metrics, paging | NAME, DESCRIPTION, TYPE, TYPE_PARAMS, IS_PARTITION, EXPR, LABEL, QUALIFIED_NAME, QUERYABLE_GRANULARITIES |
entities(metrics=[...]) | metrics, paging | NAME, DESCRIPTION, TYPE, EXPR, ROLE |
measures(metrics=[...]) | metrics, paging | NAME, AGG, EXPR |
queryable_granularities(metrics=[...]) | metrics | QUERYABLE_GRANULARITIES, the grains of metric_time |
metrics_for_dimensions(group_by=[...]) | group_by, paging | as metrics() |
saved_queries() | paging | NAME, DESCRIPTION, LABEL, METRICS, GROUP_BY, WHERE_FILTER, EXPORTS |
dimension_values(metrics=[...], group_by='...') | metrics, one group_by | The group-by's distinct values |
query(...) | metrics, group_by, where, order_by, limit, compile, saved_query, read_cache | One column per group-by and metric; with compile=True, one row SQL |
page_numberstarts at 1; withoutpage_size, a call returns every row.DIMENSIONS,QUERYABLE_GRANULARITIES,METRICS,GROUP_BYandWHERE_FILTERare lists of strings;TYPE_PARAMSandEXPORTSare JSON text.- In
query, a metric is a name orMetric('revenue', alias='sales'), which names its columnsales. - A group-by is a name such as
'metric_time__month',Dimension('metric_time').grain('month'),TimeDimension('metric_time', 'month')orEntity('customer'). whereis a filter, or a list of filters combined withAND, in the call syntax of queries.- An order-by is a metric or group-by name, with a leading
-for descending, orMetric('revenue').descending(True)orDimension('customer__country').descending(True). saved_queryruns a saved query, refined by the other arguments as in Refine a saved query.read_cache=Falseruns the query on Trino even when the result cache holds it.
Refused statements fail with INVALID_ARGUMENT and the reason:
| Statement | Message |
|---|---|
semantic_layer.query_with_all_group_bys(...) | query_with_all_group_bys is not supported |
| Any other call | unknown call semantic_layer.<call> |
| An argument the call does not take | semantic_layer.<call> takes no argument <name> |
No call, or a call without {{ }}, with the BI tables off | the statement must hold one {{ semantic_layer.<call>(...) }} |
| Two calls | a statement holds exactly one {{ semantic_layer.<call>(...) }} |
SQL around a call#
select customer__country, revenue
from {{ semantic_layer.query(metrics=['revenue'], group_by=['customer__country']) }}
where revenue > 100
order by revenue desc
- The SQL is one
SELECT, with an optionalORDER BY. - It reads the call's result and nothing else: a table name is refused, as the call's result is the only relation it can see.
- Column names are matched without regard to case.
- Around a
query, the whole statement runs on Trino, with standard SQL functions andTRY_CAST,IF,REGEXP_LIKE,SPLIT_PART,DAY_OF_WEEK,JSON_EXTRACT_SCALAR,DATE_FORMATandAPPROX_PERCENTILE. - Around a metadata call, it runs in the gateway, with standard SQL functions.
A statement that does not parse, or names another relation, fails with the SQL around the semantic_layer call ... and the parser's or validator's message.
GraphQL#
The GraphQL API is dbt's, for metadata and the SQL of a query: the operations dbt-sl-sdk 0.13.4 sends for them, plus the paginated listings and queryableGranularities. Queries run over Arrow Flight SQL, with ADBC, the JDBC driver or dbt-sl-sdk, or over REST.
| Kind | Operations |
|---|---|
| Query | environmentInfo, metrics, metricsPaginated, dimensions, dimensionsPaginated, measures, entities, entitiesPaginated, savedQueries, savedQueriesPaginated, queryableGranularities |
| Mutation | compileSql |
Every operation takes environmentId, the semantic project's id. compileSql answers the SQL of a query without running it:
curl -s -X POST "https://hopsworks.example.com/semantic-layer/api/graphql" \
-H "Authorization: Bearer $HOPSWORKS_API_KEY" -H "Content-Type: application/json" \
-d '{"query": "mutation($metrics: [MetricInput!], $groupBy: [GroupByInput!]) { compileSql(environmentId: 7, metrics: $metrics, groupBy: $groupBy) { sql } }",
"variables": {"metrics": [{"name": "revenue"}], "groupBy": [{"name": "customer__country"}]}}'
compileSqltakesmetrics,groupBy,where,orderBy,limitandsavedQuery; it also takesreadCache, as dbt's clients send it, which has no effect.createQuery,query,createDimensionValuesQuery,cancelQuery,queryRecordsandmetricsForDimensionsare not in the schema, and a request for one answers a GraphQL validation error.semantic_layer.metrics_for_dimensions(...)of dbt's JDBC dialect lists the metrics of group-bys over Arrow Flight SQL.- An operation selects at most 500 fields, each alias counted; a larger one is refused before it runs.
- A refused request answers with HTTP 200 and an
errorsentry whoseextensions.codeis the gateway's error code, such asSEMANTIC_LAYER_INVALID_QUERY; a refused API key isUser is not authorized.
Timestamps#
metric_time and the time dimensions come back as timestamps without a time zone, Arrow timestamp[ms], holding UTC wall time: the queries run on Trino in UTC. pyarrow and pandas show them as they are. The Arrow Flight SQL JDBC driver 19.0.0 shows them shifted by the client JVM's offset from UTC, such as 2019-12-31 23:00 for 2020-01-01 00:00 on a client in CET; run the client JVM with -Duser.timezone=UTC to read the values as stored.
Query handles#
A Flight SQL ticket and a prepared statement are signed handles that carry the query, the build it was planned on, the user and the semantic project. Any replica of the gateway serves a handle, so a client can fetch from another replica than the one that planned the statement.
| Handle | Valid for | When it ends |
|---|---|---|
| Flight SQL ticket, prepared statement | 1 hour after it was issued | UNAUTHENTICATED: the query handle expired, prepare the statement again. |
| Flight SQL handle whose build was deleted | NOT_FOUND: the query handle expired, prepare the statement again; a semantic project keeps its 20 newest builds. |
- A prepared statement answers from the build it was prepared on, even after a newer build answers queries, so its columns do not change under a client.
- A handle used by another user, or with another environment id, is refused with
UNAUTHENTICATED.
Limits#
| API | Most rows | When a result is larger |
|---|---|---|
| Arrow Flight SQL: dbt-sl-sdk queries, ADBC, JDBC, BI tables | 1,000,000 | The call fails after the first 1,000,000 rows with INVALID_ARGUMENT: the result has more than 1000000 rows (MAX_ROWS) and was cut; add a limit or filters, so a client that reads the whole result gets the error, not the rows. |
| REST, JSON | 100,000 | The result stops there, with truncated: true and the X-Semantic-Truncated: true header. |
| REST, Arrow | 1,000,000 | The result stops there; X-Semantic-Truncated: true is set when the cut is known before the first rows are sent, and X-Semantic-Max-Rows gives the cap. |
- Every API shares the query slots of a gateway replica, 16 by default, and the query time, 300 seconds, of Query metrics. Through the dbt-compatible layout, nginx also ends a call after
hopsworks.semanticLayer.dbtCompatibleLayout.timeoutSeconds, 360 by default, see The dbt-compatible endpoint layout. - A replica's Arrow buffers are bounded, half the JVM heap by default; a query that finds them full fails with 503
SEMANTIC_LAYER_BUSY, orUNAVAILABLEover Flight SQL. - A statement whose handle would be larger than 64 KiB is refused with
the statement is too large.
For administrators#
The semantic gateway is installed with global._hopsworks.semanticLayer.enabled: true. GraphQL needs nothing more: the hopsworks chart adds an Ingress for /semantic-layer/api/graphql on the ingress hosts. Clients outside the cluster reach the Flight SQL port through a load balancer.
semantic-gateway:
externalLoadBalancer:
enabled: true
hostname: semantic.hopsworks.example.com
signingKey:
existingSecret: semantic-gateway-signing-key
hopsworks:
variables:
loadbalancer_external_domain_semantic_gateway: semantic.hopsworks.example.com
The Flight SQL load balancer#
semantic-gateway.externalLoadBalancermakes the Servicesemantic-gateway-externalonflightPort, 18446, as the other external load balancers of the chart do:enabled,classandannotations,managed: falsefor a NodePort Service with an optionalnodePort, and on AWS a TargetGroupBinding toglobal.unmanagedLoadBalancers.semanticGateway.targetGroupId.- Clients check the name they dial against the gateway's certificate, so put the load balancer's DNS name, or IP address, in
externalLoadBalancer.hostname. The chart adds it to the gateway's certificate, which otherwise holds only the names inside the cluster. Set it at install: the names of the certificate do not change on an upgrade unless the HopsworksCertsemantic-gatewayis deleted first. - Set
hopsworks.variables.loadbalancer_external_domain_semantic_gatewayto the same name, so the connection details carry it. Left unset, thehopsworks-update-lb-domainsjob sets it from the load balancer's address, which is not in the certificate. hopsworks.variables.semantic_gateway_flight_port,"18446", is the port the connection details carry; change it withflightPort, or when the load balancer listens on another port.
The signing key#
The gateway signs query handles with a key from a Secret, under current, and also accepts a key under previous. Each key is at least 32 characters.
- With
signingKey.existingSecret: nullandglobal._hopsworks.mode: auto, the chart creates the Secretsemantic-gateway-signingwith a random key and keeps it across upgrades. - In any other mode, such as under ArgoCD, the chart cannot read the Secret back, so the render fails until
existingSecretnames a Secret you create:
kubectl -n hopsworks create secret generic semantic-gateway-signing-key \
--from-literal=current="$(openssl rand -hex 32)"
Rotate the key in two rollouts, restarting the gateway after each change of the Secret with kubectl -n hopsworks rollout restart deployment/semantic-gateway:
- Add the new key under
previous, so every replica accepts it. - Move the new key to
currentand the old key toprevious.
Handles signed with the old key keep working until they expire, after handleTtlSeconds; then the old key can be removed.
Gateway values#
semantic-gateway value | Default | Meaning |
|---|---|---|
flightPort | 18446 | The Flight SQL port. |
handleTtlSeconds | 3600 | How long a Flight SQL ticket or prepared statement stays valid. |
arrowMemoryLimitBytes | null | The Arrow buffers a replica's queries hold at once; null is half the JVM heap. |
maxRows | 1000000 | The rows of an Arrow result, over Flight SQL and REST. |
hopsworks.semanticLayer.graphqlIngress.annotations sets the annotations of the GraphQL Ingress. The result cache has its own values, and Semantic Layer administration lists every value of the service. The dbt-compatible endpoint layout serves both APIs on port 443 of one host, for dbt's Tableau and Power BI connectors.