Skip to content

Databases and tables

Create and delete databases through EZGH, create ReplicatedMergeTree tables in them with SQL, and browse them in the console.

Updated View as Markdown

A warehouse’s databases use ClickHouse’s Replicated database engine: a CREATE TABLE, ALTER or DROP TABLE inside one, run on any node, reaches every shard and replica. The databases themselves are created and deleted through EZGH (the console, the CLI, the API, Terraform or Deploy), not with CREATE DATABASE, so that every node has them. A warehouse starts with default.

Create a database

Open the warehouse’s Schema tab and choose Create database.

ezgh warehouse databases create whs_4k2m9x0a7qpe events --wait

Names match ^[A-Za-z_][A-Za-z0-9_]{0,63}$. system, information_schema, INFORMATION_SCHEMA and names starting ezgh_ are reserved.

Create tables

Create tables with SQL, as a user with CREATE on the database. Use ReplicatedMergeTree() (or another Replicated*MergeTree) with no arguments: the warehouse fills in where each shard’s replicas keep it.

CREATE TABLE events.page_views
(
    ts DateTime,
    user_id UInt64,
    path String
)
ENGINE = ReplicatedMergeTree()
ORDER BY (user_id, ts);

Tables in a Replicated database have to be replicated, so other engines that keep data on one node, such as MergeTree, Log and Memory, are refused: the error names TABLE ENGINE ON the engine. Distributed, Kafka, View and the table functions’ engines are allowed.

A materialized view keeps its rows in a table of its own. Give it a Replicated*MergeTree engine, or write into a replicated table you created with MATERIALIZED VIEW … TO <table>:

CREATE MATERIALIZED VIEW events.views_per_day
ENGINE = ReplicatedSummingMergeTree() ORDER BY (day, path)
AS SELECT toDate(ts) AS day, path, count() AS views FROM events.page_views GROUP BY day, path;

A materialized view whose table has another engine keeps its rows on one node, and the Schema tab marks its table “Stored on one node”.

On a warehouse with more than one shard, each shard holds its own rows of page_views. To spread rows across shards and query them all at once, add a Distributed table: see Sharding.

Browse

The warehouse’s Schema tab lists its databases (tables, rows and bytes), then a database’s tables (engine, rows and bytes), as of the time it shows. Query this table opens the query editor with a SELECT from it.

ezgh warehouse databases list whs_4k2m9x0a7qpe
ezgh warehouse databases tables whs_4k2m9x0a7qpe events

Up to 10,000 tables are listed.

Delete a database

Deleting a database drops it and every table in it, on every node. Its name goes in the request as well as the path:

ezgh warehouse databases delete whs_4k2m9x0a7qpe events --wait

The console asks you to type the name. A delete is refused while the warehouse’s deletionProtection is on.

Navigation

Type to search…

↑↓ navigate↵ selectEsc close