ClickHouse 按天设置分区

本贴最后更新于 2179 天前,其中的信息可能已经事过境迁

背景

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.

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...