Semantic view (Beta)
A semantic view (SEMANTIC VIEW) declares a business-facing semantic layer inside the database. Its TABLES, RELATIONSHIPS, DIMENSIONS and METRICS clauses describe the underlying physical tables, the relationships between them, and your business dimensions and metrics. To query a semantic view, you name the dimensions you need and wrap each metric in AGG(metric_name). Hologres then prunes the tables that the query does not touch, builds the minimal join tree, and aggregates before it joins, so metrics are never inflated by aggregating after a detail-level join. Hologres also publishes the definition as a structured YAML model that BI tools and AI agents (NL2SQL) can read for business context. This topic describes the syntax, query patterns, introspection methods and limits of SEMANTIC VIEW.
SEMANTIC VIEW is in beta and is supported only in Hologres V5.0.0 or later.
Background information
Use cases
-
One definition per metric: pin
total_revenuetosum(o.amount)so that the same metric cannot be computed in several different ways. -
Simpler queries for business users: query data through dimensions and metrics without having to understand the physical tables and join relationships behind the star schema.
-
No double counting: the engine removes the inflation of amounts and counts that occurs when you aggregate after a one-to-many join.
-
Ready for AI and NL2SQL: the structured YAML model gives AI agents the business semantics they need as context.
How it works
When you query a semantic view, the engine does not join every physical table first and aggregate afterwards. It rewrites the query in the following steps:
-
For each referenced metric, build the minimal join subtree. Only the tables that metric needs are joined; unreferenced tables are pruned.
-
Merge metrics that share the same source, grain and join tree into one metric group, which is scanned and aggregated a single time.
-
Aggregate each metric group independently over the same output dimensions.
-
When there is more than one metric group, take the group with the finest grain as the primary result domain and stitch the remaining groups onto it with a NULL-safe
LEFT JOINover all output dimensions.
If the table that owns a metric can be duplicated by a join (fan-out), the engine first deduplicates on the output dimensions plus the owner table primary key and then aggregates again. Each record therefore contributes at most once to each output dimension.
For query shapes whose semantics cannot be resolved safely, such as an ambiguous relationship path or several independent many-side branches under the same parent table, the engine raises an error instead of returning a result that might be wrong.
Switches and observability
The following GUC parameters control semantic view query support and debug output.
|
GUC parameter |
Default value |
Description |
|
|
|
Specifies whether to enable semantic view query support. When this parameter is off, a query against a semantic view is resolved as an ordinary table and fails with |
|
|
|
Specifies whether to emit the physical SQL that is finally executed through |
For more information about GUC parameters, see GUC parameters.
Run the following commands to turn debug output on and off.
SET hg_semantic_view_rewrite_debug = on; -- Turn on while troubleshooting
RESET hg_semantic_view_rewrite_debug;
Create a semantic view
Syntax
Use the following syntax to create or drop a semantic view.
CREATE SEMANTIC VIEW [IF NOT EXISTS] [schema.]view_name
TABLES ( alias AS [schema.]table_name [PRIMARY KEY (col1[, ...])] , ... )
[RELATIONSHIPS ( [rel_name AS] from_alias(col[, ...]) REFERENCES to_alias[(col[, ...])] , ... )]
[DIMENSIONS ( owner_alias.dim_name AS <expression> [COMMENT = 'description'] , ... )]
[METRICS ( owner_alias.metric_name AS <aggregate_expression> [COMMENT = 'description'] , ... )]
[COMMENT = 'view description'];
DROP SEMANTIC VIEW [IF EXISTS] [schema.]view_name;
The following table describes the parameters.
|
Parameter |
Description |
|
|
The name of the semantic view. Use |
|
|
Declares the physical tables that take part in the model, in the form |
|
|
Declares the relationships between tables, in the form |
|
|
Declares the business dimensions, in the form |
|
|
Declares the business metrics, in the form |
|
|
The dimension expression: a row-level expression over a single physical alias. Columns, constants, built-in scalar functions and operators, |
|
|
The metric aggregate expression. Supported aggregate functions are |
|
|
A description for the view, a dimension or a metric. The |
Example
The following example is based on the existing public.orders and public.customers tables and creates a semantic view named sales_sv.
CREATE SEMANTIC VIEW sales_sv
TABLES (
o AS public.orders PRIMARY KEY (order_id),
c AS public.customers PRIMARY KEY (customer_id)
)
RELATIONSHIPS ( rel_oc AS o(customer_id) REFERENCES c(customer_id) )
DIMENSIONS (
o.status_dim AS o.status,
c.city_dim AS c.city COMMENT = 'Customer city'
)
METRICS (
o.total AS sum(o.amount) COMMENT = 'Order amount',
o.order_count AS count(*),
c.credit AS sum(c.credit_limit)
)
COMMENT = 'Sales analysis semantic view';
Query a semantic view
When you query a semantic view, wrap every metric in AGG(metric_name) and reference dimensions directly, listing them in GROUP BY. The following examples show the common query patterns.
-- Grouped aggregation
SELECT city_dim, AGG(total), AGG(credit) FROM sales_sv GROUP BY city_dim;
-- Global aggregation (no GROUP BY needed)
SELECT AGG(total), AGG(order_count) FROM sales_sv;
-- Filter on a dimension in WHERE (filters rows only; not part of the output grain)
SELECT city_dim, AGG(total) FROM sales_sv WHERE status_dim = 'completed' GROUP BY city_dim;
-- HAVING / ORDER BY / LIMIT
SELECT city_dim, AGG(total) AS revenue FROM sales_sv
GROUP BY city_dim HAVING AGG(total) > 100 ORDER BY revenue DESC LIMIT 10;
-- SELECT also accepts scalar expressions built from metrics
SELECT upper(city_dim) AS city,
AGG(total) / NULLIF(AGG(order_count), 0) AS avg_order_value,
CASE WHEN AGG(total) > 1000 THEN 'high' ELSE 'normal' END AS lvl
FROM sales_sv GROUP BY city_dim;
Keep the following points in mind when you query a semantic view.
-
GROUP BYmust list exactly the dimensions in the SELECT output, with no extra columns and none missing. -
When a dimension is nested inside an expression,
GROUP BYstill lists the dimension name. -
A metric cannot appear in
WHERE. To filter on an aggregated result, useHAVING AGG(metric_name).
Introspection and the YAML model
A semantic view definition is persisted in the system table hologres.hg_semantic_view_properties. The table has four columns, schema_name, view_name, property_key and property_value, and provides the following three types of property_key.
|
property_key |
Content |
|
|
The original |
|
|
The structured YAML semantic model, for BI tools and AI agents to consume. |
|
|
The owner of the view. |
Run the following SQL statements to list the semantic views in the current schema, or to read the YAML model of a specific semantic view.
-- List every semantic view in the current schema with its owner
SELECT view_name, max(property_value) FILTER (WHERE property_key = 'owner') AS owner
FROM hologres.hg_semantic_view_properties
WHERE schema_name = current_schema() GROUP BY view_name;
-- Read the YAML semantic model
SELECT property_value AS model_yaml
FROM hologres.hg_semantic_view_properties
WHERE schema_name = current_schema()
AND view_name = 'sales_sv' AND property_key = 'model_yaml';
The following example shows the structure of model_yaml. It maps one-to-one to the DDL, and the COMMENT text appears as the description field.
name: sales_sv
description: Sales analysis semantic view
tables:
- name: o
base_table: {database: mydb, schema: public, table: orders}
primary_key: {columns: [order_id]}
dimensions:
- {name: status_dim, expr: o.status}
metrics:
- {name: total, expr: sum(o.amount), description: Order amount}
relationships:
- name: rel_oc
left_table: o
right_table: c
relationship_columns: [{left_column: customer_id, right_column: customer_id}]
relationship_type: many_to_one
No YAML import function (hg_create_semantic_view_from_yaml()) is available at this time. To rebuild a semantic view in another environment, replay the original ddl_text.
Verify and troubleshoot
Follow these three steps to confirm that a semantic view is in effect and to investigate its query results.
-
Confirm that the object exists: query the system table
hologres.hg_semantic_view_propertiesand check that theview_nameis present with a correspondingddl_text. -
Inspect the SQL that actually runs: run
SET hg_semantic_view_rewrite_debug = on;and then run your query.NOTICEemits the rewritten physical SQL. With multiple metric groups you can see severalWITH __hg_sv_xxxCTEs, plus the outerLEFT JOIN ... ON NOT (q_0.dim IS DISTINCT FROM q_1.dim)that performs the NULL-safe stitch. -
Check that metrics are not inflated: write a baseline SQL statement that deduplicates the detail rows first and then aggregates, and compare its result with the semantic view result.
Limits
SEMANTIC VIEW has the following modeling and query limits.
Modeling limits
-
An expression cannot span more than one physical alias. Window functions, subqueries, parameter references, VOLATILE functions and set-returning functions are not supported.
-
Aggregates are limited to
count,sum,avg,minandmax. Derived metrics, ratio metrics and filtered metrics such assum(a)/count(*)are not supported. -
The target columns of
REFERENCESmust be thePRIMARY KEYof the target table. Many-to-many relationships and bridge tables are not supported. -
CREATE OR REPLACE SEMANTIC VIEWandALTER SEMANTIC VIEWare not supported yet. To change a definition,DROPthe view and create it again.
Query limits
-
Every metric must be wrapped in
AGG().AGG()accepts a bare metric name only; modifiers such asAGG(DISTINCT m),FILTERandOVERare not supported. -
SELECT *,SELECT DISTINCT, window functions,QUALIFYandFOR UPDATE/SHAREare not supported. -
A metric cannot be referenced in
WHERE, and you cannot write the underlying aggregate functions directly inHAVINGorORDER BY, for examplecount(*). -
A multi-table query must output at least one metric in
SELECT. A query that outputs dimensions only is rejected. When a query touches a single table, it is handled as a single-table query. -
CREATE VIEW ... AS SELECT ... FROM semantic_viewis not supported. UseCREATE TABLE AS SELECTto materialize the query result instead. -
Joining two semantic views, joining a semantic view to a physical table, and complex outer constructs such as
WITHand set operations are not supported. -
DML operations are not supported.
INSERT,UPDATE,DELETEandTRUNCATEagainst a semantic view fail, currently withrelation ... does not exist, because a semantic view is not registered as an ordinary table object.
Rejected multi-table patterns
-
Ambiguous relationship path: when more than one relationship exists between the same pair of tables, or more than one path can reach a table, the query is rejected with
ambiguous relationship path. -
Chasm trap: when two independent many-side branches hang off the same parent table, a query that uses dimensions or metrics from both branches is rejected with an error that asks for an explicit common domain.
Modeling recommendations
-
Declare a
PRIMARY KEYfor every table: this is the foundation of correct multi-table aggregation, because it drives the deduplication guard. -
Get the relationship direction right:
from_aliasmust be the many side andto_aliasmust be the one side. If you reverse them, the engine reads the relationship as one-to-many and aggregates the same rows more than once. -
Do not define more than one relationship between the same pair of tables: two
flights → airportsrelationships, one for the departure airport and one for the arrival airport, cause queries to be rejected for path ambiguity. If you need to tell them apart, model them as separate semantic views. -
Do not span sibling branches in one query: aggregating metrics from both the
ordersbranch and theaddressesbranch is currently rejected. Split the work into two queries. -
Use explicit object names and unambiguous literals in your expressions:
ddl_textis parsed again at query time, so avoid depending on session settings such assearch_pathor the time zone. -
Add a
COMMENTto every dimension and metric: theCOMMENTtext is written intomodel_yamlas thedescriptionfield, which markedly improves how accurately AI agents and NL2SQL interpret the model.
Common errors and workarounds
The following table lists the statements that commonly fail when you query a semantic view, the error each one raises, and the correct way to write it.
|
Incorrect statement |
Error message |
Correct statement |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
List the dimensions explicitly, together with |
|
Dimensions in |
|
Keep |