clickhouse几种create table的情况

• 1、普通建表：

CREATE TABLE dis_j.D_F1_shard on cluster cluster_demo (
`product_code` String,
`package_name` String
) ENGINE = MergeTree ORDER BY package_name SETTINGS index_granularity = 8192

 

• 2、分布表：

CREATE TABLE dis_j.D_F1_all on cluster cluster_demo as dis_j.D_F1_shard
ENGINE = Distributed('cluster_demo', 'dis_j', D_F1_shard, rand())

• 3、复制已有的一个表创建表。如果不指定engine，默认会复制源表engine。

CREATE TABLE dis_j.tmp1 as dis_j.D_F1_shard

• 4、复制已有的一个表创建表。在集群上执行，要把on cluster写在as前面。

CREATE TABLE dis_j.tmp1 on cluster cluster_demo as dis_j.D_F1_shard

• 5、使用select查询结果来创建一个表，需要指定engine。字段列表会使用查询结果的字段列表。

CREATE TABLE dis_j.tmp1 ENGINE = MergeTree ORDER BY package_name AS select * from dis_j.D_F1_shard

• 6、最后，在分布表之上再创建分布表可以吗？

–在ck中创建表：

create table dis_j.t_area_shard on cluster cluster_demo
(
area_id String,
area_name String
)ENGINE = MergeTree ORDER BY area_id SETTINGS index_granularity = 8192

–分布表

CREATE TABLE dis_j.t_area_all on cluster cluster_demo as dis_jiakai.t_area_shard
ENGINE = Distributed('cluster_demo', 'dis_j', t_area_shard, rand())

CREATE TABLE dis_jiakai.t_area_all2 on cluster cluster_demo as dis_jiakai.t_area_all
ENGINE = Distributed('cluster_demo', 'dis_jiakai', t_area_all, rand())

执行成功！

试着查询一下：

select * from dis_jiakai.t_area_all2

SQL 错误 [48]: ClickHouse exception, code: 48, host: 10.9.20.231, port:
8123; Code: 48, e.displayText() = DB::Exception: Distributed on
Distributed is not supported (version 19.9.2.4 (official build))

表可建，但不可用！

————————————————
版权声明：本文为CSDN博主「阿爵」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/sojer/article/details/104692485