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.

Install

Python 3.11 or later:

pip install "dactopus-ossie-clickhouse[mcp]"

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). https:// connects with TLS on any port, http:// without it; no other scheme is accepted. A database in the URL path (.../analytics) is where a model's sources without a database are read. --time-zone or OSSIE_CLICKHOUSE_TIME_ZONE names the zone of today and of times without an offset when the deployment's differs from the model's (Time zone).

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 (its time_zone argument is the CLI's --time-zone), executor.connect, executor.Executor and planner.Query; a PlanError names the nearest known object. Executor takes the document load_model returns, or one built with dactopus_ossie_clickhouse._vendor.ossie, never with an installed ossie. 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, and transform_null_in = 0 so a NULL matches nothing in IN.
• A filter on rows that reference the root, such as sessions with a purchase of some value, is a semi-join: each session with at least one such purchase counts once. So is a filter on rows that reference what the root references, such as order lines whose product is in a category, and, with the order lines named in a filter, orders with a line in a category (semi-joins).
• 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.
• A metric declared on a dataset (datasets[].metrics) is asked for as dataset.metric and computed over that dataset's rows, COUNT(*) included (model authoring).
• 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 sql (OSSIE_SQL_2026), or in the OSSIE_SQL_2026 or ANSI_SQL dialect, 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. Any other function reaches ClickHouse as written (toString, sumIf, DAYOFWEEK, a user-defined function), and validate --url checks the server has it.
• 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; dataset.metric for one declared on a dataset), -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. <model>... is one Ossie file or several read as one model, such as one file per subject area (docs/model-authoring.md).

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, which reaches every dataset it touches through many-to-one relationships, along one path to each. Two paths to the same dataset are refused as ambiguous. 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 and the spec's query compliance cases 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: the upstream Ossie SQL dialect and compliance suite once they merge; metrics that read both sides of a one-to-many relationship, 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.