Skip to content

Sharding and Distributed tables

Spread rows across a warehouse's shards with a Distributed table and a sharding key, and query every shard at once.

Updated View as Markdown

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_all to read every shard. Querying page_views reads 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.

Navigation

Type to search…

↑↓ navigate↵ selectEsc close