Work with sharding in ClickHouse®

For these step-by-step instructions, let's assume that you've created the cluster called Test-Cluster with 5 shards and the database called examples.

Create a distributed table

  1. Connect to your database.

  2. Create a table with MergeTree engine . For the purpose of this step-by-step guide, let's call it player_ratings-A:

    CREATE TABLE examples.player_ratings-A ON CLUSTER default
    ( 
       `<table structure>` 
    )
    ENGINE = MergeTree()
    PARTITION BY toYYYYMM(EventDate)
    ORDER BY (CounterID, EventDate)
    

    Let's look through this query in detail:

    default is the cluster name.
    ON CLUSTER default automatically creates tables on all hosts.
    <table structure> - describe every column and its data type according to ClickHouse documentation .

  3. Create a distributed table player_ratings-A_distributed with Distributed engine :

    CREATE TABLE examples.player_ratings-A_distributed ON CLUSTER default AS examples.data_table1
    ENGINE = Distributed(default, examples, player_ratings-A, rand())
    

    In this case, instead of explicitly describing the table structure, you can use AS examples.player_ratings-A to copy the structure of the initial table you created earlier.

Distributed table health check

To check the availability of the distributed table you created, run a test query for a number of rows in the table:

SELECT count() FROM examples.player_ratings-A_distributed

You should receive a number as a response to this query.