更新时间:2024-05-11 GMT+08:00

ClickHouse表创建

ClickHouse依靠ReplicatedMergeTree引擎与ZooKeeper实现了复制表机制,用户在创建表时可以通过指定引擎选择该表是否高可用,每张表的分片与副本都是互相独立的。

同时ClickHouse依靠Distributed引擎实现了分布式表机制,在所有分片(本地表)上建立视图进行分布式查询,使用很方便。ClickHouse有数据分片(shard)的概念,这也是分布式存储的特点之一,即通过并行读写提高效率。

CPU架构为鲲鹏计算的ClickHouse集群表引擎不支持使用HDFS和Kafka。

查看ClickHouse服务cluster等环境参数信息

  1. 使用ClickHouse客户端连接到ClickHouse服务端,具体请参考从零开始使用ClickHouse
  2. 查询集群标识符cluster等其他环境参数信息。

    select cluster,shard_num,replica_num,host_name from system.clusters;
    SELECT 
        cluster, 
        shard_num, 
        replica_num, 
        host_name
    FROM system.clusters
    
    ┌─cluster───────────┬─shard_num─┬─replica_num─┬─host_name──────── ┐
    │ default_cluster_1             │         1   │           1   │ node-master1dOnG           │
    │ default_cluster_1             │         1   │           2   │ node-group-1tXED0001       │
    │ default_cluster_1             │         2   │           1   │ node-master2OXQS           │
    │ default_cluster_1             │         2   │           2   │ node-group-1tXED0002       │
    │ default_cluster_1             │         3   │           1   │ node-master3QsRI           │
    │ default_cluster_1             │         3   │           2   │ node-group-1tXED0003       │
    └─────────────── ┴────── ┴─────── ┴──────────────┘
    
    6 rows in set. Elapsed: 0.001 sec. 

  3. 查询分片标识符shard和副本标识符replica。

    select * from system.macros;
    SELECT *
    FROM system.macros
    
    ┌─macro───┬─substitution─────┐
    │ id          │ 76                     │
    │ replica     │ 2                      │
    │ shard       │ 3                      │
    └────── ┴────────────┘
    
    3 rows in set. Elapsed: 0.001 sec. 

创建本地复制表和分布式表

  1. 客户端登录ClickHouse节点,例如:clickhouse client --host node-master3QsRI --multiline --port 9440 --secure;

    node-master3QsRI 参数为查看ClickHouse服务cluster等环境参数信息2对应的host_name参数的值

  2. 使用ReplicatedMergeTree引擎创建复制表。

    例如,如下在default_cluster_1集群节点上和default数据库下创建表名为test的ReplicatedMergeTree表:

    CREATE TABLE default.test ON CLUSTER default_cluster_1

    (

    `EventDate` DateTime,

    `id` UInt64

    )

    ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/default/test', '{replica}')

    PARTITION BY toYYYYMM(EventDate)

    ORDER BY id;

    参数说明如下:

    • ON CLUSTER语法表示分布式DDL,即执行一次就可在集群所有实例上创建同样的本地表。
    • default_cluster_1为查看ClickHouse服务cluster等环境参数信息2查询到的cluster集群标识符。
      ReplicatedMergeTree引擎族接收两个参数:
      • ZooKeeper中该表相关数据的存储路径。

        该路径必须在/clickhouse目录下,否则后续可能因为ZooKeeper配额不够导致数据插入失败

        为了避免不同表在ZooKeeper上数据冲突,目录格式必须按照如下规范填写:

        /clickhouse/tables/{shard}/default/test,其中/clickhouse/tables/{shard}为固定值,default为数据库名,test为创建的表名。

      • 副本名称,一般用{replica}即可。
    CREATE TABLE default.test ON CLUSTER default_cluster_1
    (
        `EventDate` DateTime, 
        `id` UInt64
    )
    ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/default/test', '{replica}')
    PARTITION BY toYYYYMM(EventDate)
    ORDER BY id
    
    ┌─host─────────────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
    │ node-group-1tXED0002                   │  9000  │      0   │         │                   5   │                3   │
    │ node-group-1tXED0003                   │  9000  │      0   │         │                   4   │                3   │
    │ node-master1dOnG                       │  9000  │      0   │         │                   3   │                3   │
    └────────────────────┴────┴─────┴──── ┴─────────── ┴──────────┘
    ┌─host─────────────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
    │ node-master3QsRI                       │  9000  │      0   │         │                   2   │                0   │
    │ node-group-1tXED0001                   │  9000  │      0   │         │                   1   │                0   │
    │ node-master2OXQS                       │  9000  │      0   │         │                   0   │                0   │
    └────────────────────┴────┴─────┴──── ┴─────────── ┴──────────┘
    
    6 rows in set. Elapsed: 0.189 sec. 

  3. 使用Distributed引擎创建分布式表。

    例如,以下将在default_cluster_1集群节点上和default数据库下创建名为test_all 的Distributed表:

    CREATE TABLE default.test_all ON CLUSTER default_cluster_1

    (

    `EventDate` DateTime,

    `id` UInt64

    )

    ENGINE = Distributed(default_cluster_1, default, test, rand());

    CREATE TABLE default.test_all ON CLUSTER default_cluster_1
    (
        `EventDate` DateTime, 
        `id` UInt64
    )
    ENGINE = Distributed(default_cluster_1, default, test, rand())
    
    ┌─host─────────────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
    │ node-group-1tXED0002                   │  9000  │      0   │         │                   5   │                0   │
    │ node-master3QsRI                       │  9000  │      0   │         │                   4   │                0   │
    │ node-group-1tXED0003                   │  9000  │      0   │         │                   3   │                0   │
    │ node-group-1tXED0001                   │  9000  │      0   │         │                   2   │                0   │
    │ node-master1dOnG                       │  9000  │      0   │         │                   1   │                0   │
    │ node-master2OXQS                       │  9000  │      0   │         │                   0   │                0   │
    └────────────────────┴────┴─────┴──── ┴─────────── ┴──────────┘
    
    6 rows in set. Elapsed: 0.115 sec. 
    

    Distributed引擎需要以下几个参数:

    • default_cluster_1为查看ClickHouse服务cluster等环境参数信息2查询到的cluster集群标识符。
    • default本地表所在的数据库名称。
    • test为本地表名称,该例中为2中创建的表名。
    • (可选的)分片键(sharding key)

      该键与config.xml中配置的分片权重(weight)一同决定写入分布式表时的路由,即数据最终落到哪个物理表上。它可以是表中一列的原始数据(如site_id),也可以是函数调用的结果,如上面的SQL语句采用了随机值rand()。注意该键要尽量保证数据均匀分布,另外一个常用的操作是采用区分度较高的列的哈希值,如intHash64(user_id)。

ClickHouse表数据操作

  1. 客户端登录ClickHouse节点。例如:

    clickhouse client --host node-master3QsRI --multiline --port 9440 --secure;

    node-master3QsRI 参数为查看ClickHouse服务cluster等环境参数信息2对应的host_name参数的值

  2. 参考创建本地复制表和分布式表创建表后,可以插入数据到本地表。

    例如插入数据到本地表:test

    insert into test values(toDateTime(now()), rand());

  3. 查询本地表信息。

    例如查询2中的表test数据信息:

    select * from test;

    SELECT *
    FROM test
    
    ┌───────────EventDate─┬─────────id─┐
    │ 2020-11-05 21:10:42             │ 1596238076           │
    └──────────────── ┴───────────┘
    
    1 rows in set. Elapsed: 0.002 sec. 
     

  4. 查询Distributed分布式表。

    例如3中因为分布式表test_all基于test创建,所以test_all表也能查询到和test相同的数据。

    select * from test_all;

    SELECT *
    FROM test_all
    
    ┌───────────EventDate─┬─────────id─┐
    │ 2020-11-05 21:10:42             │ 1596238076           │
    └──────────────── ┴───────────┘
    
    1 rows in set. Elapsed: 0.004 sec. 

  5. 切换登录节点为相同shard_num的shard节点,并且查询当前表信息,能查询到相同的表数据。

    例如,退出原有登录节点:exit;

    切换到节点node-group-1tXED0003:

    clickhouse client --host node-group-1tXED0003 --multiline --port 9440 --secure;

    通过2可以看到node-group-1tXED0003和node-master3QsRI的shard_num值相同。

    show tables;

    SHOW TABLES
    
    ┌─name─────┐
    │ test           │
    │ test_all       │
    └────────┘
     

  6. 查询本地表数据。例如在节点node-group-1tXED0003查询test表数据。

    select * from test;
    SELECT *
    FROM test
    
    ┌───────────EventDate─┬─────────id─┐
    │ 2020-11-05 21:10:42             │ 1596238076           │
    └──────────────── ┴───────────┘
    
    1 rows in set. Elapsed: 0.005 sec. 
    

  7. 切换到不同shard_num的shard节点,并且查询之前创建的表数据信息。

    例如退出之前的登录节点node-group-1tXED0003:

    exit;

    切换到node-group-1tXED0001节点。通过2可以看到node-group-1tXED0001和node-master3QsRI的shard_num值不相同。

    clickhouse client --host node-group-1tXED0001 --multiline --port 9440 --secure;

    查询test本地表数据,因为test是本地表所以在不同分片节点上查询不到数据。

    select * from test;

    SELECT *
    FROM test
    
    Ok.

    查询test_all分布式表数据,能正常查询到数据信息。

    select * from test_all;

    SELECT *
    FROM test
    
    ┌───────────EventDate─┬─────────id─┐
    │ 2020-11-05 21:12:19             │ 3686805070           │
    └──────────────── ┴───────────┘
    
    1 rows in set. Elapsed: 0.002 sec.