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 --waitNames 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 eventsUp 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 --waitThe console asks you to type the name. A delete is refused while the warehouse’s deletionProtection
is on.