Semantic view (Beta)

Updated at:

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.

Note

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_revenue to sum(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:

  1. For each referenced metric, build the minimal join subtree. Only the tables that metric needs are joined; unreferenced tables are pruned.

  2. Merge metrics that share the same source, grain and join tree into one metric group, which is scanned and aggregated a single time.

  3. Aggregate each metric group independently over the same output dimensions.

  4. 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 JOIN over 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

hg_enable_semantic_view_query

on

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 relation ... does not exist.

hg_semantic_view_rewrite_debug

off

Specifies whether to emit the physical SQL that is finally executed through NOTICE, for troubleshooting and verification. Do not leave this parameter on in production.

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

view_name

The name of the semantic view. Use [schema.]view_name to specify the schema that the view belongs to.

TABLES

Declares the physical tables that take part in the model, in the form alias AS [schema.]table_name. Each alias must be unique within the view. For a multi-table model, declare a PRIMARY KEY for every table so that relationships can be validated and duplicate counting prevented. You can model on tables, views (View) and materialized views (Materialized view).

RELATIONSHIPS

Declares the relationships between tables, in the form [rel_name AS] from_alias(col) REFERENCES to_alias(col). from_alias is the many side and to_alias is the one side. The target columns of REFERENCES must be the PRIMARY KEY declared for the target table. If you omit the column list, the columns are matched automatically.

DIMENSIONS

Declares the business dimensions, in the form owner_alias.dim_name AS <expression>. Queries reference the dim_name on the left.

METRICS

Declares the business metrics, in the form owner_alias.metric_name AS <aggregate_expression>. Queries reference the metric_name on the left.

<expression>

The dimension expression: a row-level expression over a single physical alias. Columns, constants, built-in scalar functions and operators, CASE and casts are supported.

<aggregate_expression>

The metric aggregate expression. Supported aggregate functions are count(*), count(expr), count(DISTINCT expr), sum, avg, min and max.

COMMENT

A description for the view, a dimension or a metric. The COMMENT text is written to the description field of the YAML model.

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 BY must list exactly the dimensions in the SELECT output, with no extra columns and none missing.

  • When a dimension is nested inside an expression, GROUP BY still lists the dimension name.

  • A metric cannot appear in WHERE. To filter on an aggregated result, use HAVING 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

ddl_text

The original CREATE SEMANTIC VIEW statement that you submitted.

model_yaml

The structured YAML semantic model, for BI tools and AI agents to consume.

owner

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
Note

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.

  1. Confirm that the object exists: query the system table hologres.hg_semantic_view_properties and check that the view_name is present with a corresponding ddl_text.

  2. Inspect the SQL that actually runs: run SET hg_semantic_view_rewrite_debug = on; and then run your query. NOTICE emits the rewritten physical SQL. With multiple metric groups you can see several WITH __hg_sv_xxx CTEs, plus the outer LEFT JOIN ... ON NOT (q_0.dim IS DISTINCT FROM q_1.dim) that performs the NULL-safe stitch.

  3. 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, min and max. Derived metrics, ratio metrics and filtered metrics such as sum(a)/count(*) are not supported.

  • The target columns of REFERENCES must be the PRIMARY KEY of the target table. Many-to-many relationships and bridge tables are not supported.

  • CREATE OR REPLACE SEMANTIC VIEW and ALTER SEMANTIC VIEW are not supported yet. To change a definition, DROP the view and create it again.

Query limits

  • Every metric must be wrapped in AGG(). AGG() accepts a bare metric name only; modifiers such as AGG(DISTINCT m), FILTER and OVER are not supported.

  • SELECT *, SELECT DISTINCT, window functions, QUALIFY and FOR UPDATE/SHARE are not supported.

  • A metric cannot be referenced in WHERE, and you cannot write the underlying aggregate functions directly in HAVING or ORDER BY, for example count(*).

  • 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_view is not supported. Use CREATE TABLE AS SELECT to materialize the query result instead.

  • Joining two semantic views, joining a semantic view to a physical table, and complex outer constructs such as WITH and set operations are not supported.

  • DML operations are not supported. INSERT, UPDATE, DELETE and TRUNCATE against a semantic view fail, currently with relation ... 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 KEY for every table: this is the foundation of correct multi-table aggregation, because it drives the deduplication guard.

  • Get the relationship direction right: from_alias must be the many side and to_alias must 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 → airports relationships, 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 orders branch and the addresses branch is currently rejected. Split the work into two queries.

  • Use explicit object names and unambiguous literals in your expressions: ddl_text is parsed again at query time, so avoid depending on session settings such as search_path or the time zone.

  • Add a COMMENT to every dimension and metric: the COMMENT text is written into model_yaml as the description field, 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

SELECT city_dim, total FROM sv

is a metric and must be wrapped with AGG()

SELECT city_dim, AGG(total) ...

WHERE total > 100

metric ... cannot be used in WHERE clause

HAVING AGG(total) > 100

HAVING count(*) > 1

raw aggregate functions are not allowed

HAVING AGG(order_count) > 1

SELECT * FROM sv

column reference must be a bare dimension name

List the dimensions explicitly, together with AGG(metric_name).

Dimensions in GROUP BY that the SELECT output does not include

GROUP BY columns must match the dimensions in SELECT

Keep GROUP BY identical to the dimensions in the SELECT output.