dactopus-ossie-clickhouse

An implementation of the Apache Ossie semantic model standard for ClickHouse. It reads an Ossie description of your data, answers questions asked in business terms (metrics, dimensions, filters) with one ClickHouse query, and serves the description and the queries to AI agents over MCP, the Model Context Protocol.

Why

A company connects an AI assistant to its database and the assistant gets things wrong: hundreds of tables with no explanations, three columns with the same name, a different "revenue" in every department. Apache Ossie fixes this at the level of description: one open, portable definition of what tables and fields mean, how they relate and how metrics are computed. dactopus-ossie-clickhouse executes those descriptions on ClickHouse, which the standard does not cover itself, and handles the ClickHouse specifics (tables that keep change history, reference data in dictionaries, join resolution) so that neither the model author nor the agent has to.

Related MCP server: orionbelt-semantic-layer-mcp

Install

Python 3.11 or later. Not on PyPI yet: the apache-ossie package this builds on is a git dependency until upstream publishes it, and PyPI does not accept packages with git dependencies.

pip install "dactopus-ossie-clickhouse[mcp] @ git+https://github.com/Dactopus/dactopus-ossie-clickhouse"

Leave out [mcp] to skip the MCP server and its SDK; the library and the CLI work without them.

Until 0.4.0 the package and its command were ossie-clickhouse and the module ossie_clickhouse; the changelog says what to change.

Quick start

Point it at a model and a ClickHouse. The TPC-DS reference model from the Ossie repository is in tests/fixtures/tpcds.yaml. ClickHouse credentials go in the URL or in OSSIE_CLICKHOUSE_URL (default http://127.0.0.1:8123). A database in the URL path (.../analytics) is where a model's sources without a database are read.

dactopus-ossie-clickhouse validate model.yaml --url http://user:password@host:8123
dactopus-ossie-clickhouse sql model.yaml -m total_sales -d item.i_brand -f "date_dim.d_year = 1998"
dactopus-ossie-clickhouse query model.yaml -m total_sales -d item.i_brand -f "total_sales > 1000000" --json
dactopus-ossie-clickhouse serve model.yaml        # MCP over stdio, see docs/mcp-setup.md

From Python:

from dactopus_ossie_clickhouse import load_model
from dactopus_ossie_clickhouse.executor import Executor, connect
from dactopus_ossie_clickhouse.planner import Query

ex = Executor(connect("http://user:password@host:8123"), load_model("model.yaml"))
r = ex.execute(Query(metrics=("total_sales",), dimensions=("item.i_brand",)))
print(r.sql, r.columns, r.rows[:3])

That is the Python API: load_model, executor.connect, executor.Executor and planner.Query; a PlanError names the nearest known object. The other modules are internal.

How a question becomes SQL

Click the diagram for the interactive version: one question from an agent followed to its SQL, line by line, and two questions it refuses.

• Datasets come from source, joins from relationships. One root dataset, LEFT JOIN to the dimensions the question touches, only along relationships whose to_columns cover a primary or unique key of the target (many-to-one). SETTINGS join_use_nulls = 1 so unmatched rows get NULL, not ClickHouse defaults, and aggregate_functions_null_for_empty = 1 so a sum over no rows is NULL, as in SQL, not 0.

• Metrics of different facts, such as revenue and returns by region, are each computed from their own root and merged on the dimensions (stitching): every region of either fact appears, and a fact without rows in a region gives NULL for a sum and 0 for a count.

• Filters over fields go to WHERE; filters that name a metric or contain an aggregate (total_sales > 1000000) go to HAVING, and may only use the question's own dimensions besides aggregates. A window metric (RANK() OVER ...) cannot be filtered: select it and filter the rows. A filter is one expression, never a statement, and its functions reach ClickHouse as written: what a caller may run there is decided by ClickHouse grants and quotas, not by this library.

• Tables with a ReplacingMergeTree engine, replicated or on ClickHouse Cloud, and Distributed tables, materialized views and Merge tables over them, are read with FINAL; single-key dictionaries are read with dictGetOrNull instead of a join. Both are learned from system.tables and system.dictionaries, not configured.

• Expressions in OSSIE_SQL_2026 or ANSI_SQL are translated on the SQLGlot AST; the spec's function catalog is mapped to ClickHouse equivalents and checked by value against DuckDB and the spec text.

• Rows come sorted by the first metric, descending, unless order_by says otherwise, so the top rows come first and nobody has to rank them by hand. Rows without a value (NULL) come last in either direction. Ties are broken by the dimensions, ascending, so a limit keeps the same rows on every run.

• Names resolve case-insensitively; SQL uses the physical names as the model writes them. The same question and model always give the same SQL.

• Errors name the nearest known metric, field or dataset, so an agent can recover from a typo without help.

CLI

Question options for sql and query: -m metric (repeatable), -d dataset.field (repeatable), -f condition (repeatable; a metric name or an aggregate means HAVING), -o "name [desc]", -l limit. --url and --policy apply to anything that talks to ClickHouse.

Documentation

• Model authoring for ClickHouse: sources, keys, expressions, deduplication, dictionaries, validation.

• Access control: ClickHouse users, roles and row policies decide; the policy file hides more.

• MCP setup: Claude Desktop, Claude Code and other stdio MCP clients.

• Design decisions: what was measured or evaluated, and the remote server design that waits for demand.

• Changelog and contributing.

What it is not

No chat, no charts, no user interface: it provides access and understanding, the interface is someone else's job. No data loading: the data must already be in ClickHouse.

Limits

• One root dataset per group of metrics, with direct relationships to every dataset it touches. Chains through an intermediate dataset are not supported. Metrics of different facts are stitched, so each dimension and row filter must be reachable from each fact.

• Metrics are computed over the root's rows. A question whose metric would count a joined dataset's rows repeatedly, or only those the root references, is refused with a message, not answered with a wrong number; see model authoring.

• Joins are many-to-one only, inferred from primary and unique keys.

• No time grain and no derived-dimension syntax: declare a field per grain or band (see model authoring). The spec has no such attributes yet, and this project does not invent them.

• A metric cannot reference another metric by name; repeat the expression.

Status

Pre-release. The supported Ossie schema version is pinned (0.2.0.dev0) and checked on load, because the standard is still moving. Tested against the TPC-DS reference model on ClickHouse 26.x. Until the major version is 1, a minor release may change the Python API; the CLI and the MCP tools are the stable surface.

Where it goes from here: PyPI once apache-ossie is published there; the upstream Ossie SQL dialect and compliance suite once they merge; fan-out protection for one-to-many joins, source as a query and a remote multi-user MCP server on the first real request. Ask for one in the issue tracker.

research/ holds the experiments behind some decisions in this code and the data we share with the Apache Ossie community; nothing there is part of the library.

License

Apache License 2.0.