Each shard of a warehouse holds its own rows. A Distributed table over a local table spreads the
rows you insert across every shard by its sharding key, and a query on it runs on every shard and
combines the results. The warehouse’s cluster is named default.
Create one
Create the local table first, then a Distributed table over it, as a user with the admin preset:
CREATE TABLE events.page_views
(
ts DateTime,
user_id UInt64,
path String
)
ENGINE = ReplicatedMergeTree()
ORDER BY (user_id, ts);
CREATE TABLE events.page_views_all AS events.page_views
ENGINE = Distributed('default', 'events', 'page_views', cityHash64(user_id));Both reach every node, since events is a Replicated database.
- Insert into
page_views_all. Each row goes to the shard its key picks: here, all of a user’s rows land on one shard. - Query
page_views_allto read every shard. Queryingpage_viewsreads only the shard of the node you’re connected to.
Pick a sharding key that spreads rows evenly and keeps rows you join or group together on one shard,
such as a customer or user ID. rand() spreads rows evenly with no grouping.
Without a Distributed table
The cluster() table function reads a local table on every shard. It needs a user with the admin
preset:
SELECT path, count() FROM cluster('default', events.page_views) GROUP BY path;Adding shards
Existing data stays on the current shards. Rows inserted through a Distributed table spread across all shards by its sharding key. See Scaling.