ClickHouse 按天设置分区

背景

ClickHouse 默认为每月一个分区

问题

如何实现每天一个分区呢，或者每周一个分区 :)

方法

在 54310 版本中，支持了这个功能。

升级到版本 > 54310

具体略

转为开发模式

    SET experimental_allow_extended_storage_definition_syntax = 1;
​

或者改配置文件

    <profiles>
        <default>
            <experimental_allow_extended_storage_definition_syntax>1</experimental_allow_extended_storage_definition_syntax>
        </default>
    </profiles>    
​

具体的 SQL

SELECT '*** Partitioned by week ***';

DROP TABLE IF EXISTS test.partitioned_by_week;
CREATE TABLE test.partitioned_by_week(d Date, x UInt8) ENGINE = MergeTree PARTITION BY toMonday(d) ORDER BY x;

-- 2000-01-03 belongs to a different week than 2000-01-01 and 2000-01-02
INSERT INTO test.partitioned_by_week VALUES ('2000-01-01', 1), ('2000-01-02', 2), ('2000-01-03', 3);
INSERT INTO test.partitioned_by_week VALUES ('2000-01-03', 4), ('2000-01-03', 5);

SELECT 'Parts before OPTIMIZE:';
SELECT partition, name FROM system.parts WHERE database = 'test' AND table = 'partitioned_by_week' AND active ORDER BY name;
OPTIMIZE TABLE test.partitioned_by_week PARTITION '2000-01-03' FINAL;
SELECT 'Parts after OPTIMIZE:';
SELECT partition, name FROM system.parts WHERE database = 'test' AND table = 'partitioned_by_week' AND active ORDER BY name;

SELECT 'Sum before DROP PARTITION:';
SELECT sum(x) FROM test.partitioned_by_week;
ALTER TABLE test.partitioned_by_week DROP PARTITION '1999-12-27';
SELECT 'Sum after DROP PARTITION:';
SELECT sum(x) FROM test.partitioned_by_week;
​

更多 SQL 见[4].

删除分区

ALTER TABLE [db.]table DROP PARTITION 'name'
​

注：如果有多个副本，删除分区操作只能在主副本上操作，否则会报一个异常。

参考

1. Custom partitioning in ClickHouse 1.1.54310
2. Arbitrary partitioning key for MergeTree engine family
3. how to delete data at specific hour
4. 00502_custom_partitioning_local.sql
5. Custom partitioning.