ClickHouse实战--clickhouse sql入门大全一、ck建表语句四、创建分布式表五、DDL六、delete/update 不支持Distributed引擎七、分区表八、数据同步九、时间戳转换十、其他事项

81 阅读 0 评论 54 点赞

我是靠谱客的博主平淡柚子，最近开发中收集的这篇文章主要介绍ClickHouse实战--clickhouse sql入门大全一、ck建表语句四、创建分布式表五、DDL六、delete/update 不支持Distributed引擎七、分区表八、数据同步九、时间戳转换十、其他事项，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

关注微信公众号【BigData512】,了解更多大数据技术

一、ck建表语句

与数据库相比，创建表的语法要复杂得多（请参阅参考资料。通常，CREATE TABLE语句必须指定三个关键事项：

要创建的表的名称。
表模式，即列及其数据类型的列表。
表引擎及其设置，它确定有关如何物理执行对该表的查询的所有详细信息。

二、创建库

CREATE/ATTACH DATABASE zabbix ENGINE = Ordinary;

ATTACH 也可以建库，但是metadata目录下不会生成.sql文件，一般用于metadata元数据sql文件被删除后，恢复库表结构使用

三、创建本地表

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] 
( name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2], ... ) 
ENGINE = VersionedCollapsingMergeTree(sign, version) 
 [PARTITION BY expr]  
 [ORDER BY expr]
 [SAMPLE BY expr] 
 [SETTINGS name=value, ...]

案例

CREATE TABLE IF NOT EXISTS database.table_name ON cluster cluster_shardNum_replicasNum(
    'id' UInt64,
    'name' String,
    'time' UInt64,
    'age' UInt8,
    'flag' UInt8
)
ENGINE = MergeTree
PARTITION BY toDate(time/1000)
ORDER BY (id,name)
SETTINGS index_granularity = 8192

CREATE TABLE test02( id UInt16,col1 String,col2 String,create_date date ) ENGINE = MergeTree(create_date, (id), 8192); ENGINE：是表的引擎类型，

MergeTree：最常用的，MergeTree要求有一个日期字段，还有主键。
Log引擎没有这个限制，也是比较常用。
ReplicatedMergeTree：MergeTree的分支，表复制引擎。
Distributed：分布式引擎。
create_date：是表的日期字段，一个表必须要有一个日期字段。
id：是表的主键，主键可以有多个字段，每个字段用逗号分隔。
8192：是索引粒度，用默认值8192即可。

四、创建分布式表

CREATE TABLE distributed_table AS table ENGINE = Distributed(cluster, db, table, rand());

cluster：配置文件中的群集名称。

db：库名。

table：本地表名。

rand()：分片方式：随机。

intHash64():分片方式：指定字段做hash。

Distribute引擎会选择每个分发到的Shard中的”健康的”副本执行SQL

五、DDL

如果想按集群操作，需要借助zookeeper，在config.xml中添加配置

<distributed_ddl> <path>/clickhouse/task_queue/ddl</path> </distributed_ddl>

一个节点创建表，会同步到各个节点

CREATE TABLE db.table [ON CLUSTER cluster] (...)

添加、删除、修改列

ALTER TABLE [db].table [ON CLUSTER cluster] ADD|DROP|MODIFY COLUMN ...

rename 支持*MergeTree和Distributed

rename table db.table1 to db.table2 [ON CLUSTER cluster]

truncate table db.table;不支持Distributed引擎

六、delete/update 不支持Distributed引擎

ALTER TABLE [db.]table DELETE WHERE filter_expr...

ALTER TABLE [db.]table UPDATE column1 = expr1 [, ...] WHERE ...

七、分区表

按时间分区：

toYYYYMM(EventDate)：按月分区

toMonday(EventDate)：按周分区

toDate(EventDate)：按天分区

按指定列分区：

PARTITION BY cloumn_name

对分区的操作：

alter table test1 DROP PARTITION [partition] #删除分区

alter table test1 DETACH PARTITION [partition]#下线分区

alter table test1 ATTACH PARTITION [partition]#恢复分区

alter table .test1 FREEZE PARTITION [partition]#备份分区

八、数据同步

采用remote函数

insert into db.table select * from remote('目标IP',db.table,'user','passwd')

csv文件导入clickhouse

cat test.csv | clickhouse-client -u user --password password --query="INSERT INTO db.table FORMAT CSV"

同步mysql库中表

CREATE TABLE tmp ENGINE = MergeTree ORDER BY id AS SELECT * FROM mysql('hostip:3306', 'db', 'table', 'user', 'passwd') ;

4） clickhouse-copier 工具

九、时间戳转换

select toUnixTimestamp('2018-11-25 00:00:02');

select toDateTime(1543075202);

十、其他事项

clickhouse的cluster环境中，每台server的地位是等价的，即不存在master-slave之说，是multi-master模式。
各replicated表的宿主server上要在hosts里配置其他replicated表宿主server的ip和hostname的映射。
上面描述的在不同的server上建立全新的replicated模式的表，如果在某台server上已经存在一张replicated表，并且表中已经有数据，这时在另外的server上执行完replicated建表语句后，已有数据会自动同步到其他server上面。
如果zookeeper挂掉，replicated表会切换成read-only模式，不再进行数据同步，系统会周期性的尝试与zk重新建立连接。
如果在向一张replicated表insert数据的时候zookeeper挂掉，这时候会抛一个异常，等到与zk重新建立连接以后，系统（其他replicated表所在server）会检查本地文件与预期文件（保存在zk上）的差别，如果是轻微的差别，直接同步覆盖，如果发现有数据块损坏或者识别不了，则将这些数据文件移动到“detached”子目录，然后重新根据zk所记录的文件信息进行副本的同步。
drop掉某一台server上的replicated表，不会对其他server上面的replicated表造成影响。