Vertica 分区表设计(续)
2016-08-09 14:10 AlfredZhao 阅读(3502) 评论(1) 编辑 收藏 举报在上篇Vertica 分区表设计中,已经提过了Vertica的分区表创建和分区删除,但举例上并不系统,
本篇文章将系统的对分区表设计及后续的删除分区进行讲解。
概述:Vertica分区表(天和月)创建以及删除分区
1.分区表创建
Vertica分区表可以使用预定义函数创建,有特殊需求时,也可以自定义函数创建。 我这里测试均是以业务用户test登录建表: vsql -Utest1.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
<h1 id="5">Reference</h1>
本篇内容其实之前在我给公司项目组的培训中已经提过,但未在博客中整理,现在更新到博客。
附当时培训视频和资料的云盘地址:https://pan.baidu.com/s/1c2LuZSW
AlfredZhao©版权所有「从Oracle起航,领略精彩的IT技术。」