大数据-ClickHouse技术三（SQL参考）

ClickHouse支持以下形式的查询:

SELECT

INSERT INTO

CREATE

ALTER

其他类型查询

数据表使用

一、合并树 MergeTree()

用途：用于插入极大量的数据到一张表，数据以数据片段一个接一个快速写入，数据片段按照规则合并。相比在插入时不断修改已存储的数据，这种策略会高效很多

Clickhouse可以做到指定一个跟排序键不一样的主键，此时排序键用于在数据片段中进行排序，主键用于在索引文件中进行标记的写入。这种情况下，主键表达式元组必须是排序键表达式元组的前缀(即主键为(a,b)，排序列必须为(a,b,**))。

1、插入数据：INSERT into table test_a (user_id,score)with(select ['A','B','C','D','E','F','G'])AS dictselect number as user_id,dict[number%7+1] from numbers(100000000);2、删除数据alter table test_a delete where user_id=0;3、修改数据alter table test_a update score='BBBBB' where user_id='1';

查看操作日志：SELECT *from system.mutations m ;

跳数索引：

2、版本折叠树

VersionedCollapsingMergeTree(sign, version)

用途：允许快速写入不断变化的对象状态，删除后台中旧对象状态，降低存储体积

CREATE TABLE UAct(
UserID UInt64,
PageViews UInt8,
Duration UInt8,
Sign Int8,
Version UInt8
)ENGINE = VersionedCollapsingMergeTree(Sign, Version)ORDER BY UserID

sign — 指定行类型的列名: 1 是一个 “state” 行, -1 是一个 “cancel” 行列数据类型应为 Int8.version — 指定对象状态版本的列名。列数据类型应为 UInt*.

查询需要聚合或者强制折叠

聚合：

SELECT
UserID,
sum(PageViews * Sign) AS PageViews,
sum(Duration * Sign) AS Duration,
Version
FROM UActGROUP BY UserID, VersionHAVING sum(Sign) > 0

强制折叠

SELECT * FROM UAct FINAL

3、CollapsingMergeTree 折叠行

CollapsingMergeTree 会异步的删除（折叠）这些除了特定列 Sign 有 1 和 -1 的值以外，其余所有字段的值都相等的成对的行。没有成对的行会被保留。

特性：引擎可以显著的降低存储量并提高 SELECT 查询效率

CREATE TABLE UAct
(
    UserID UInt64,
    PageViews UInt8,
    Duration UInt8,
    Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID

在写入行的时候使用特定的列 Sign。如果 Sign = 1 则表示这一行是对象的状态，我们称之为«状态»行。如果 Sign = -1 则表示是对具有相同属性的状态行的取消，我们称之为«取消»行。

查询聚合：

SELECT
    UserID,
    sum(PageViews * Sign) AS PageViews,
    sum(Duration * Sign) AS Duration
FROm UAct
GROUP BY UserID
HAVING sum(Sign) > 0；

强制折叠

SELECT * FROM UAct FINAL；

4、自定义分区

按月分区：PARTITION BY toYYYYMM(VisitDate)

分区键也可以是表达式元组:PARTITION BY (toMonday(StartDate), EventType)

我们设置按一周内的事件类型分区,新数据插入到表中时，这些数据会存储为按主键排序的新片段（块）。插入后 10-15 分钟，同一分区的各个片段会合并为一整个片段，不要超过1000个分区

查看分区表

SELECT
    partition,
    name,
    active
FROM system.parts
WHERE table = 'visits'

正如在示例中所看到的，同一分区中有几个独立的片段（例如，201901_1_3_1和201901_1_9_2）。这意味着这些片段尚未合并。ClickHouse 会定期的对插入的数据片段进行合并，大约是在插入后15分钟左右。此外，你也可以使用 OPTIMIZE 语句发起一个计划外的合并。例如

OPTIMIZE TABLE visits PARTITION 201902;

active 列为片段状态。1 代表激活状态；0 代表非激活状态

非激活片段会在合并后的10分钟左右被删除。

5、ReplacingMergeTree

该引擎和 MergeTree 的不同之处在于它会删除排序键值相同的重复项

数据的去重只会在数据合并期间进行。合并会在后台一个不确定的时间进行，因此你无法预先作出计划。有一些数据可能仍未被处理。尽管你可以调用 OPTIMIZE 语句发起计划外的合并，但请不要依靠它，因为 OPTIMIZE 语句会引发对数据的大量读写。

optimize table UAct3 强行合并表

CREATE TABLE UAct3
(
    UserID UInt64,
    Record_time DateTime DEFAULT toDateTime(0),
     District_Code       UInt8 ,
    Address String,
    Power UInt64,
    Deleted BOOLEAN DEFAULT 0 
)
ENGINE = ReplacingMergeTree(Record_time)
ORDER BY (UserID,Address)
PARTITION BY District_Code;

查询

SELECT 
UserID,
max(Record_time) as R_time,
District_Code,
Address,
argMax(Power,Record_time) as power,
argMax(Deleted,Record_time) as deleted
from UAct3
group by UserID,Address,District_Code
having deleted=0;

6、创建查询视图

CREATE VIEW PowerConsumption_view ON CLUSTER default_cluster AS SELECT    User_ID,    max(Record_Time) AS R_Time,    District_Code,    Address,    argMax(Power, Record_Time) AS Power,    argMax(Deleted, Record_Time) AS Deleted FROM default.PowerConsumption GROUP BY    User_ID,    Address,    District_Code HAVING Deleted = 0;

7、系统命令

查看分布式集群：select * from system.clusters where cluster='my_shard';

查看本地分片配置：SELECT *from system.macros;

查看zookeeper配置：select * from system.zookeeper where path = '/';