ClickHouse catalog

The ClickHouse catalog connects Gravitino to a ClickHouse server over JDBC, exposing ClickHouse databases as schemas and ClickHouse tables as tables. Schemas and tables are both read-write. One catalog reaches every database on the server named in jdbc-url, and on a cluster it can create, alter, and drop objects ON CLUSTER.

The ClickHouse JDBC driver ships in the image, so there is no driver to supply.

Quick start

The example connects a ClickHouse server as the catalog clickhouse_sales, creates the schema sales, and creates a MergeTree table in it.

UI
REST

Start the catalog. Open Connect → Catalogs and click + Catalog. Enter clickhouse_sales as the Catalog name, keep Relational as the Catalog type, and choose ClickHouse under Databases and warehouses in Select provider.
Enter the connection. Under Connection, enter jdbc:clickhouse://{clickhouse_host}:8123 as the URL, and the User and Password Gravitino connects as. Advanced holds a Properties list for any other catalog property. The UI fills in jdbc-driver itself.
Test and create it. Click Test connection, then Create catalog.
Create a schema. In Explore, open clickhouse_sales and click + Schema. Enter sales as the Schema name and click Create schema. To create it on a cluster, add on-cluster with the value true and cluster-name with the cluster's name under Advanced → Properties.

The UI does not create tables. Create them through the REST API, as in the REST tab.

Create the catalog. The public REST endpoint does not fill in jdbc-driver, so the request names it.

Create a ClickHouse catalog
curl -sS -X POST "https://{gravitino_host}/api/metalakes/{metalake}/catalogs" \
  -H "Accept: application/vnd.gravitino.v1+json" \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer $TOKEN" \
  -d '{
    "name": "clickhouse_sales",
    "type": "RELATIONAL",
    "provider": "jdbc-clickhouse",
    "comment": "ClickHouse sales server",
    "properties": {
      "jdbc-url": "jdbc:clickhouse://{clickhouse_host}:8123",
      "jdbc-driver": "com.clickhouse.jdbc.ClickHouseDriver",
      "jdbc-user": "{user}",
      "jdbc-password": "{password}"
    }
  }'

Create a schema. The two cluster properties create the database ON CLUSTER; leave them out on a single server.

Create a schema on a cluster
curl -sS -X POST "https://{gravitino_host}/api/metalakes/{metalake}/catalogs/clickhouse_sales/schemas" \
  -H "Accept: application/vnd.gravitino.v1+json" \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer $TOKEN" \
  -d '{
    "name": "sales",
    "comment": "Sales database",
    "properties": {
      "on-cluster": "true",
      "cluster-name": "{cluster_name}"
    }
  }'

Create a table. A MergeTree table needs a sort order, which becomes its ORDER BY. The cluster properties must match the schema's.

Create a MergeTree table
curl -sS -X POST "https://{gravitino_host}/api/metalakes/{metalake}/catalogs/clickhouse_sales/schemas/sales/tables" \
  -H "Accept: application/vnd.gravitino.v1+json" \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer $TOKEN" \
  -d '{
    "name": "orders",
    "comment": "Orders table",
    "columns": [
      {"name": "order_id", "type": "integer", "nullable": false},
      {"name": "user_id", "type": "integer", "nullable": false},
      {"name": "amount", "type": "decimal(18,2)", "nullable": false},
      {"name": "created_at", "type": "timestamp", "nullable": false}
    ],
    "properties": {
      "engine": "MergeTree",
      "on-cluster": "true",
      "cluster-name": "{cluster_name}"
    },
    "sortOrders": [
      {"sortTerm": {"type": "field", "fieldName": ["order_id"]}, "direction": "asc"}
    ],
    "partitioning": [
      {"strategy": "month", "fieldName": ["created_at"]}
    ],
    "indexes": [
      {"indexType": "PRIMARY_KEY", "name": "PRIMARY", "fieldNames": [["order_id"]]}
    ]
  }'

ClickHouse receives the equivalent of the following statement.

Equivalent ClickHouse statement
CREATE TABLE `orders` ON CLUSTER `{cluster_name}` (
  `order_id` Int32,
  `user_id` Int32,
  `amount` Decimal(18,2),
  `created_at` DateTime,
  PRIMARY KEY (`order_id`)
)
ENGINE = MergeTree
ORDER BY `order_id`
PARTITION BY toYYYYMM(`created_at`)
COMMENT 'Orders table ...'

Catalog properties

The ClickHouse catalog defines no catalog properties of its own; it takes the connection, pool, and driver properties every JDBC catalog shares, listed in Shared properties.

jdbc-url points at the server's HTTP port, and jdbc-driver is com.clickhouse.jdbc.ClickHouseDriver. Cluster behavior is set per schema and per table rather than on the catalog.

Driver version compatibility

ClickHouse server	ClickHouse JDBC driver	Status
`24.8.14`	`0.7.1`	The combination Gravitino's integration tests run against

The image ships driver 0.7.1. A ClickHouse server outside the 24.8 line may need a different driver version, so validate a newer server against your own staging environment before relying on it.

Schemas

A schema in this catalog is a ClickHouse database. Create, list, load, and drop are supported; alter is not. Schema comments are supported.

Listing hides default, system, and information_schema, so tables in the default database are not reached through the catalog's schema list.

Property	Description	Default
`on-cluster`	Creates the database with `ON CLUSTER` when `true`	`false`
`cluster-name`	The cluster to create the database on; required for `on-cluster`	None

ON CLUSTER is used only when both properties are set. ClickHouse does not report ON CLUSTER in SHOW CREATE DATABASE, so Gravitino appends the cluster name to the database comment, as a final line of the form [Gravitino] ch.cluster={cluster_name}, and reads it from there. The comment a load returns has that line removed, and the two properties are returned when it is present.

A database created outside Gravitino carries no such line, so it loads without the cluster properties, and a drop through Gravitino does not use ON CLUSTER. That drop removes the database only from the node Gravitino is connected to and leaves it on the other nodes. To manage an existing cluster database through Gravitino, recreate it through Gravitino.

Dropping a schema that still holds tables fails unless the drop cascades. The emptiness check reads system.tables on the node Gravitino is connected to.

Tables

Table create, load, list, rename, alter, and drop are supported. Purge is not, and a purge request is rejected rather than being treated as a drop.

A table created on a cluster keeps the cluster name in its comment, the same way a schema does, so a rename, alter, or drop of that table runs ON CLUSTER, and a drop adds SYNC. The same limit applies: a table created on a cluster outside Gravitino is renamed, altered, and dropped only on the connected node.

Gravitino also records its own identifier in the comment of each table it creates, as text of the form (From Gravitino, DO NOT EDIT: gravitino.v1.uid{id}), and uses it to recognize the table on later loads. Leave both markers in place when you edit a table comment in ClickHouse directly.

A nullable column is written as Nullable({type}). A load reads MATERIALIZED and ALIAS columns from system.columns and returns their expressions as defaults.

Properties

Property	Description	Default
`engine`	The table engine	`MergeTree`
`on-cluster`	Creates the table with `ON CLUSTER` when `true`	None
`cluster-name`	The cluster for `ON CLUSTER`; also the cluster of a `Distributed` table	None
`cluster-remote-database`	The remote database of a `Distributed` table	None
`cluster-remote-table`	The remote table of a `Distributed` table	None
`cluster-sharding-key`	The sharding key of a `Distributed` table, a column or an expression	None
`engine_parameters`	The parameters of a `ReplacingMergeTree`, `SummingMergeTree`, `CollapsingMergeTree`, or `VersionedCollapsingMergeTree` table	None
`graphite.config`	The `graphite_rollup` configuration element of a `GraphiteMergeTree` table	None
`settings.{name}`	A table setting, written as `SETTINGS {name} = {value}`	None

Table properties cannot be set or removed after creation, so the engine, the cluster, and the settings are fixed when the table is created.

engine accepts these engines, matched without regard to case:

Family	Engines
MergeTree	`MergeTree`, `ReplacingMergeTree`, `SummingMergeTree`, `AggregatingMergeTree`, `CollapsingMergeTree`, `VersionedCollapsingMergeTree`, `GraphiteMergeTree`
Log	`TinyLog`, `StripeLog`, `Log`
Integration	`ODBC`, `JDBC`, `MySQL`, `MongoDB`, `Redis`, `HDFS`, `S3`, `Kafka`, `EmbeddedRocksDB`, `RabbitMQ`, `PostgreSQL`, `S3Queue`, `TimeSeries`
Special	`Distributed`, `Dictionary`, `Merge`, `File`, `Null`, `Set`, `Join`, `URL`, `View`, `Memory`, `Buffer`, `KeeperMap`

Gravitino passes engine parameters only through engine_parameters, graphite.config, and the Distributed properties. An engine that needs other parameters, such as Join, Buffer, KeeperMap, or the integration engines, cannot be created through Gravitino, since ClickHouse rejects it without them.

engine_parameters is written inside the engine's parentheses, so "engine_parameters": "version_col" produces ENGINE = ReplacingMergeTree(version_col). Its parentheses and quotes must balance, and it is rejected on any other engine. GraphiteMergeTree requires graphite.config, written as ENGINE = GraphiteMergeTree('{config}').

A settings.{name} value is written as given, without quoting. Settings are read back from the table's engine definition under the same settings. keys.

A Distributed table requires cluster-name, cluster-remote-database, cluster-remote-table, and cluster-sharding-key. Its columns may be omitted, in which case the table is created AS the remote table and the sharding key is checked against the remote table's columns. A sharding key that is a bare column must name a non-nullable integer column.

warning

A Memory table keeps its data only in server memory. The table definition survives a ClickHouse restart, so it still loads in Gravitino, but every row is lost. Use a MergeTree or Log family engine for data that must survive a restart.

Indexes

Gravitino index type	ClickHouse DDL	Settable through an alter
`PRIMARY_KEY`	`PRIMARY KEY ({columns})`	No, create only
`DATA_SKIPPING_MINMAX`	`INDEX {name} {columns} TYPE minmax GRANULARITY {n}`	Add and delete
`DATA_SKIPPING_BLOOM_FILTER`	`INDEX {name} {columns} TYPE bloom_filter GRANULARITY {n}`	Add and delete
`DATA_SKIPPING_SET`	`INDEX {name} {columns} TYPE set({max}) GRANULARITY {n}`	Add and delete

ClickHouse names its primary key PRIMARY, so any other primary key name is ignored with a warning.

A data skipping index takes two index properties. granularity sets GRANULARITY and must be at least 1, with a default of 1. set_max_values sets the maximum of a set index and must be at least 0, with a default of 0, which ClickHouse treats as unlimited.

Define a set index with custom granularity
{
  "indexType": "DATA_SKIPPING_SET",
  "name": "idx_status",
  "fieldNames": [["status"]],
  "properties": {"granularity": "4", "set_max_values": "100"}
}

A load reads the primary key from system.tables and data skipping indexes from system.data_skipping_indices. granularity is returned only when it is not 1, and set_max_values is not returned. A data skipping index of any other type, such as ngrambf_v1, is left out of the load and a warning is logged.

Partitioning and sort order

Partitioning and sort orders apply only to MergeTree family engines; any other engine rejects both.

Gravitino transform	ClickHouse expression
`identity`	`{column}`
`year`	`toYear({column})`
`month`	`toYYYYMM({column})`
`day`	`toDate({column})`

Several transforms are combined as tuple(...). On load, Gravitino reads the partition key back into these transforms; a partition key in any other form is left out of the load and a warning is logged.

A MergeTree family table requires at least one sort order, which becomes ORDER BY. A sort term is a column or a function of columns, and several terms are combined as ({terms}). A descending direction is written as DESC, and a null ordering is ignored with a warning. A load reads the sorting key from system.tables and returns every term as ascending.

Distribution is not supported, and a create carrying one is rejected. A Distributed table covers sharding through its properties instead.

Auto increment

Auto-increment columns are not supported, at create time or through an alter.

Supported changes

All changes in one request other than a rename are issued as a single ALTER TABLE statement, with ON CLUSTER when the table was created on a cluster.

Change	Supported
Rename table	Yes, alone in its request
Update table comment	Yes; the identifier and cluster markers are kept
Add column	Yes, at any position, with nullability, default, and comment
Delete column	Yes
Rename column	Yes
Update column type	Yes; nullability, default, and comment are kept
Update column position	Yes
Update column comment	Yes
Update column nullable	Yes, except to `NOT NULL` on a column whose default is null
Update column default	Yes
Update column auto increment	No
Add index	Yes, for data skipping indexes
Delete index	Yes
Set or remove property	No

Nested column names are rejected by every column change.

Identifier case and quoting

Names are case-sensitive in Gravitino and are passed to ClickHouse exactly as given, never folded. Schema, table, and column names must match ^[\w\p{L}-$/=]{1,64}$: 1 to 64 characters drawn from letters in any script, digits, underscore, -, $, /, and =.

Gravitino quotes identifiers with backticks in generated SQL. Names returned by a list operation come back exactly as ClickHouse stores them.

Type mapping

Gravitino reads each ClickHouse type as the Gravitino type in its row, and creates each Gravitino type as the ClickHouse type in its row, except where the list under the table says otherwise.

Nullable(...) and LowCardinality(...) wrappers are removed before mapping, including LowCardinality(Nullable(...)).

ClickHouse type	Gravitino type
`Int8`, `Int16`, `Int32`, `Int64`	Byte, Short, Integer, Long
`UInt8`, `UInt16`, `UInt32`, `UInt64`	Unsigned Byte, Short, Integer, Long
`Int128`, `Int256`, `UInt128`, `UInt256`	External type
`Float32`	Float
`Float64`	Double
`BFloat16`	External type
`Decimal(p,s)` with p up to 38	Decimal(p, s)
`Decimal(p,s)` with p from 39 to 76	External type `Decimal(p,s)`
`String`	String
`FixedString(n)`	Char(n)
`Date`	Date
`Date32`	External type
`DateTime`	Timestamp without time zone, precision 0
`DateTime(p)`, `DateTime64(p)`	Timestamp without time zone, precision p
`Bool`	Boolean
`UUID`	UUID
`IPv4`, `IPv6`, `Enum8`, `Enum16`	External type
Any other type	External type carrying the ClickHouse type name

A DateTime64 that also names a time zone, such as DateTime64(3, 'UTC'), maps to an external type.

When Gravitino creates a column:

A timestamp with precision 0 or none becomes DateTime, and one with precision p above 0 becomes DateTime64(p), never DateTime(p).
A timestamp's time zone is not carried, so a timestamp with time zone maps the same way as one without.
Time becomes time.
An external type becomes the ClickHouse type string it carries, so any ClickHouse type can be created by name.
Varchar is rejected, since ClickHouse strings have no length limit; use String, or Char for a fixed length.
Any other Gravitino type is rejected, including Binary, Fixed, the interval types, and list, map, struct, and union types.

Column default values

A function default is written inside parentheses, a numeric literal is written as is, and any other literal is written in single quotes. A string literal given already wrapped in single quotes, such as 'active', is unwrapped first, so it is not quoted twice.

On load, literal defaults on integer, unsigned integer, float, decimal, date, DateTime, String, and FixedString columns are parsed into typed literals, and CURRENT_TIMESTAMP on a DateTime column is recognized as the current timestamp. Any other default, and any default that fails to parse, is preserved as an unparsed expression.

Error mapping

ClickHouse error codes are translated into Gravitino exceptions so a caller sees the same failure types it would from any other catalog.

ClickHouse error	Gravitino exception
82 database already exists	Schema already exists
57 table already exists	Table already exists
81 unknown database	No such schema
218 table is dropped	No such table

Any other error produces a generic runtime exception carrying the ClickHouse message.

Quick start​

Catalog properties​

Driver version compatibility​

Schemas​

Tables​

Properties​

Indexes​

Partitioning and sort order​

Auto increment​

Supported changes​

Identifier case and quoting​

Type mapping​

Column default values​

Error mapping​

Related​