clickhouse 建表创建二级索引 clickhouse create table

1.在集群的每个机器上面建立本地表
1)方式一：在集群每台机器上运行代码

CREATE TABLE st_center.st_pc_office_all_user_retention_v1
(
    `apptypeid` String,
    `qid` String,
    `os` String,
    `ver` String,
    `type` String,
    `user_type` String,
    `install_day` UInt64,
    `dau` UInt64,
    `day1_dau` UInt64,
    `day2_dau` UInt64,
    `day3_dau` UInt64,
    `dt` String
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/{table}', '{replica}')
PARTITION BY dt
ORDER BY (dt, qid, ver, os, type, user_type, apptypeid)
SETTINGS index_granularity = 8192;

2)方式二：一次性在集群每个机器上建立本地表

CREATE TABLE IF NOT EXISTS st_center.st_pc_office_all_user_retention_v1 ON CLUSTER default_cluster 
(
  ...
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/{table}', '{replica}')
PARTITION BY dt
ORDER BY (dt, qid, ver, os, type, user_type, apptypeid)
SETTINGS index_granularity = 8192;

关键词：ON CLUSTER

• [1] /clickhouse/tables/是一般性前缀
• [2] {layer}-{shard}是分片识别符，同一个分片内的所有机器应该保持相同
• [3] /clickhouse/tables/{shard}/{table}’, '{replica}'作为整体可以理解为表在zookeeper中的定位和识别符，因此每个表必须不同

2.在集群的每个机器上面建立分布式表
1)方式一：在集群的每台机器上运行代码

CREATE TABLE st_center.st_pc_office_all_user_retention_v1_dist
(
    `apptypeid` String,
    `qid` String,
    `os` String,
    `ver` String,
    `type` String,
    `user_type` String,
    `install_day` UInt64,
    `dau` UInt64,
    `day1_dau` UInt64,
    `day2_dau` UInt64,
    `day3_dau` UInt64,
    `dt` String
)
ENGINE = Distributed('default_cluster', 'st_center', 'st_pc_office_all_user_retention_v1');

2）方式二：一次性在集群每个机器上建立分布式表

CREATE TABLE st_center.st_pc_office_all_user_retention_v1_dist as st_center.st_pc_office_all_user_retention_v1 ON CLUSTER default_cluster 

ENGINE = Distributed('default_cluster', 'st_center', 'st_pc_office_all_user_retention_v1');

注：Distributed表引擎后面依次是集群名、库名、表名、数据分配方式

关于分布式表我划重点如下：

• 分布式表本身并不存储数据，只是提供了一个可以分布式访问数据的框架，查询分布式表的时候clickhouse会自动去查询对应的每个本地表中的数据
• 注意AS bank_replica，它表明了分布式表所对应的本地表（本地表是存储数据的）
• 可以配置Distributed表引擎中的最后一个参数来设置数据条目的分配方式
• 可以直接往分布式表中写数据，clickhouse会自动按照上一点所说的方式来分配数据和自平衡
• 也可以自己写算法，然后往本地表中写数据（当然这个就比较高级了）

实操1：往112的本地表中插入数据

结论：只有同一分片112和171的本地数据会同步一致，其他的分片的副本表没有数据。但是分布式表数据各集群一致

112和171为同一个分片

112 :) select count(1) from test.apptype_appname_config_test1;

SELECT count(1)
FROM test.apptype_appname_config_test1

┌─count(1)─┐
│      131 │
└──────────┘

1 rows in set. Elapsed: 0.002 sec. 

112 :) select count(1) from test.apptype_appname_config_test1_dist;

SELECT count(1)
FROM test.apptype_appname_config_test1_dist

┌─count(1)─┐
│      131 │
└──────────┘

1 rows in set. Elapsed: 0.005 sec.

171 :) select count(1) from test.apptype_appname_config_test1

SELECT count(1)
FROM test.apptype_appname_config_test1

┌─count(1)─┐
│      131 │
└──────────┘

1 rows in set. Elapsed: 0.002 sec. 

171 :) select count(1) from test.apptype_appname_config_test1_dist;

SELECT count(1)
FROM test.apptype_appname_config_test1_dist

┌─count(1)─┐
│      131 │
└──────────┘

1 rows in set. Elapsed: 0.005 sec.

别的分片：

143 :) select count(1) from test.apptype_appname_config_test1;

SELECT count(1)
FROM test.apptype_appname_config_test1

┌─count(1)─┐
│        0 │
└──────────┘

1 rows in set. Elapsed: 0.002 sec. 

143 :) select count(1) from test.apptype_appname_config_test1_dist;

SELECT count(1)
FROM test.apptype_appname_config_test1_dist

┌─count(1)─┐
│      131 │
└──────────┘

1 rows in set. Elapsed: 0.005 sec.

注：同一个分片，本地表是有数据，不同的分片只有分布式表有数据

实操2：往分布式表中插入数据

结论：往分布式表插入数据时，各集群副本表根据sharding_key写入数据

112机器：

112 :) select count(1) from test.apptype_appname_config_test2_dist;

SELECT count(1)
FROM test.apptype_appname_config_test2_dist

┌─count(1)─┐
│      262 │
└──────────┘

1 rows in set. Elapsed: 0.005 sec. 

112 :) select count(1) from test.apptype_appname_config_test2;

SELECT count(1)
FROM test.apptype_appname_config_test2

┌─count(1)─┐
│      157 │
└──────────┘

1 rows in set. Elapsed: 0.002 sec.

副本机器171：

171 :) select count(1) from test.apptype_appname_config_test2;

SELECT count(1)
FROM test.apptype_appname_config_test2

┌─count(1)─┐
│      157 │
└──────────┘

1 rows in set. Elapsed: 0.002 sec. 

171 :) select count(1) from test.apptype_appname_config_test2_dist;

SELECT count(1)
FROM test.apptype_appname_config_test2_dist

┌─count(1)─┐
│      262 │
└──────────┘

1 rows in set. Elapsed: 0.005 sec.

别的分片：

143 :) select count(1) from test.apptype_appname_config_test2;

SELECT count(1)
FROM test.apptype_appname_config_test2

┌─count(1)─┐
│       30 │
└──────────┘

1 rows in set. Elapsed: 0.002 sec. 

143 :) select count(1) from test.apptype_appname_config_test2_dist;

SELECT count(1)
FROM test.apptype_appname_config_test2_dist

┌─count(1)─┐
│      262 │
└──────────┘

1 rows in set. Elapsed: 0.005 sec.