clickhouse表视图索引创建修改

表相关语句（建，删，改）

-- 建表语法
create table if not exists 表名
ENGINE = MergeTree ORDER BY tuple() 
as ( select * from tablename);

-- 实例
create table if not exists test.test_partition
    engine = MergeTree PARTITION BY toYYYYMM(parseDateTimeBestEffort(toString(BILLDATE)))
    ORDER BY (ID) SETTINGS allow_nullable_key = 1, index_granularity = 8192
as ( select * from test.tablename limit 10);
CREATE TABLE myTable  (`id` Int32,`value` String) ENGINE = Memory;

-- 删除表
drop table 表名;
-- 修改表名
rename table test.tablename to test.bak;
-- 添加/修改表注释
alter table test.tablename modify comment '注释内容';

-- 查看表的各个指标
select database,
       table,
       formatReadableSize(size)                    as size,
       formatReadableSize(bytes_on_disk)           as bytes_on_disk,
       formatReadableSize(data_uncompressed_bytes) as data_uncompressed_bytes,
       formatReadableSize(data_compressed_bytes)   as data_compressed_bytes,
       compress_rate,
       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,
             sum(bytes_on_disk)                                      as bytes_on_disk,
             sum(data_uncompressed_bytes)                            as data_uncompressed_bytes,
             sum(data_compressed_bytes)                              as data_compressed_bytes,
             (data_compressed_bytes / data_uncompressed_bytes) * 100 as compress_rate,
             max_date - min_date                                     as days,
             size / (max_date - min_date)                            as avgDaySize
      from system.parts
      where active
        and database = 'test'
        and table = 'tablename'
      group by database, table);


-- 查看表分区
select partition
from system.parts
where active
   and database = 'default'
   and table = 'user';

视图

CREATE VIEW test.viewname
            (
             `mbyue` Date,
             `dpdm` String,
             `dpmc` Nullable(String),
             `ymb` Nullable(decimal(38,10)),
             `mbts` Nullable(Int8)
                )
AS
SELECT *
FROM test.tablename;

-- 修改视图需要先删再建
DROP VIEW IF EXISTS test.viewname;
CREATE VIEW test.viewname
as
SELECT * from test.tablename;

索引

-- minmax适用于where后范围查找
ALTER TABLE test.tablename
    ADD INDEX idx_minmax_djrq (`日期`) TYPE minmax GRANULARITY 1;
    
ALTER TABLE test.tablename
    ADD INDEX idx_bloom_djrq `日期` TYPE bloom_filter(0.01) GRANULARITY 1;
-- set索引适用于 where后in/=这类bool值判断
ALTER TABLE test.tablename
    ADD INDEX idx_bloom_yyb (`部门`) TYPE set(1000) GRANULARITY 1;
SELECT _part AS part,
       COUNT(DISTINCT `ID`) AS unique_names -- 决定了set(n)
FROM test.tablename
GROUP BY _part
ORDER BY unique_names DESC;

-- 删除所索引
ALTER TABLE test.tablename DROP INDEX idx_bloom_qyjl;

-- 查看表索引
SELECT * FROM system.data_skipping_indices WHERE table = 'tablename';

-- 示例
ALTER TABLE test.tablename
    ADD INDEX idx_bloom_djrq `日期` TYPE bloom_filter(0.01) GRANULARITY 1;
-- set索引适用于 where后in/=这类bool值判断
ALTER TABLE test.tablename  ADD INDEX idx_bloom_djid (`ID`) TYPE set(4000) GRANULARITY 1;
ALTER TABLE test.tablename  ADD INDEX idx_bloom_djmxid (`MXID`) TYPE set(10000) GRANULARITY 1;