clickhouse常用语法

1.创建表

--1) 复制表
CREATE TABLE test.sutdent 
(`name` String, 
 `pt` Date, 
 `id` Int32) 
ENGINE = MergeTree PARTITION BY toDate(pt) ORDER BY id SETTINGS index_granularity = 8192;
--2) 分布式表: 数据会被均匀分割到local表所在的ck服务器磁盘上
-- 总表
CREATE TABLE test.sutdent_all AS test.sutdent_local 
ENGINE = Distributed(zx_clickhouse_cluster, test, sutdent_local, sipHash64(id));
-- local表
CREATE TABLE test.sutdent_local
(`name` String, 
 `pt` Date, 
 `id` Int32) 
ENGINE = MergeTree PARTITION BY toDate(pt) ORDER BY id SETTINGS index_granularity = 8192;

2. 查看表大小

SELECT database
      ,table
      ,formatReadableSize(size) as size  
      ,rows   
      ,days  
      ,formatReadableSize(avgDaySize) as avgDaySize   
FROM (
    SELECT
        database,
        table,
        sum(bytes) AS size,
        sum(rows) AS rows,
        min(min_date) AS min_date,
        max(max_date) AS max_date,
        (max_date - min_date) AS days,
        size / (max_date - min_date) AS avgDaySize
    FROM system.parts
    WHERE active
    GROUP BY database,table
    ORDER BY size DESC,rows DESC
);

3. 查分区

-- 查某个表的分区
SELECT 
partition,
name,
active 
FROM system.parts WHERE table = 'xxx';

4.查看逻辑分区

select * from system.clusters;

5.创建视图

CREATE [OR REPLACE] VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER] AS SELECT

# test case
CREATE OR REPLACE VIEW tt.test01 as SELECT * FROM tt.test where pt = '2020-08-11';
CREATE TABLE test.test02
(
    `type` String,
    `pt` Date,
    `uid_cnt` Int32,
    `check_uid_cnt` Int32
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/test_test02', '{replica}')
PARTITION BY toDate(pt)
ORDER BY type
SETTINGS index_granularity = 8192;
insert into test.test02 select * from remote('10.20.17.23',tt.test01,'user','123456')