代码改变世界

Vertica 分区表设计(续)

2016-08-09 14:10  AlfredZhao  阅读(...)  评论(... 编辑 收藏

在上篇Vertica 分区表设计中,已经提过了Vertica的分区表创建和分区删除,但举例上并不系统,
本篇文章将系统的对分区表设计及后续的删除分区进行讲解。

概述:Vertica分区表(天和月)创建以及删除分区

1.分区表创建

Vertica分区表可以使用预定义函数创建,有特殊需求时,也可以自定义函数创建。
我这里测试均是以业务用户test登录建表: vsql -Utest

1.1 使用预定义函数创建分区表

按天分区(doy)

--按天分区(doy)
create table t_jingyu_doy(
col1 int,
col2 varchar,
col3 timestamp not null)
PARTITION BY (date_part('doy', col3));

按月分区(month)

--按月分区(month)
create table t_jingyu_month(
col1 int,
col2 varchar,
col3 timestamp not null)
PARTITION BY (date_part('month', col3));

1.2 使用自定义函数创建分区表

按天分区

--按天分区
create table t_day(
col1 int,
col2 varchar,
col3 timestamp not null)
PARTITION BY (EXTRACT(year FROM col3)*10000
+ EXTRACT(month FROM col3)*100 +
EXTRACT(day FROM col3));

按月分区

--按月分区
create table t_month(
col1 int,
col2 varchar,
col3 timestamp not null)
PARTITION BY (EXTRACT(year FROM col3)*100 +
EXTRACT(month FROM col3));

2.查询业务表的基本信息

查询业务表的基本信息:

--所有表
select table_schema, owner_name, table_name,
partition_expression from tables;
--未分区的表
select table_schema, owner_name, table_name,
partition_expression from tables where partition_expression='';
--分区的表
select table_schema, owner_name, table_name,
partition_expression from tables where partition_expression <> '';

3.Vertica入库测试数据

入库须知:
• 数据库字符集utf-8
• 入库文件编码格式: utf-8无BOM格式编码
• 主机系统字符集: zh_CN.UTF-8
• CRT软件字符集: utf-8

我们这里入库2年(每天1条)的测试数据供后面讲解删除分区数据用。 col3时间列是2年中每一天的时间。
入库具体方法可以参见:Vertica 业务用户指定资源池加载数据

4.删除历史分区数据

4.1 删除历史分区数据(使用预定义函数创建的分区表)

--按天分区(doy),删除”2015-08-01”这一时间的分区数据
SELECT DROP_PARTITION('test.t_jingyu_doy', EXTRACT('doy' FROM '2015-08-01'::date));
test=> SELECT DROP_PARTITION('test.t_jingyu_doy', EXTRACT('doy' FROM '2015-08-01'::date));
DROP_PARTITION
-------------------
Partition dropped
(1 row)
测试结论:实际删除2天的数据, 2015和2016年的这一天数据都被删除, 而且第二年不一定就是8月1日。

--按月分区(month),删除”2015-08”这一个月的分区数据
SELECT DROP_PARTITION('test.t_jingyu_month', EXTRACT('month' FROM '2015-08-01'::date));
test=> SELECT DROP_PARTITION('test.t_jingyu_month', EXTRACT('month' FROM '2015-08-01'::date));
DROP_PARTITION
-------------------
Partition dropped
测试结论:实际删除2个月的数据, 2015和2016年的8月份的数据。

4.2 删除历史分区数据(使用自定义函数创建的分区表)

--按天分区,删除2015-08-01这一时间的分区数据
SELECT DROP_PARTITION('test.t_day', 20150801);
test=> SELECT DROP_PARTITION('test.t_day', 20150801);
DROP_PARTITION
-------------------
Partition dropped
(1 row)

--按月分区,删除2015-08这一个月的分区数据
SELECT DROP_PARTITION('test.t_month', 201508);
test=> SELECT DROP_PARTITION('test.t_month',201508);
DROP_PARTITION
-------------------
Partition dropped

Reference

本篇内容其实之前在我给公司项目组的培训中已经提过,但未在博客中整理,现在更新到博客。
附当时培训视频和资料的云盘地址:https://pan.baidu.com/s/1c2LuZSW