代码改变世界

Vertica 分区表设计(续)

2016-08-09 14:10  AlfredZhao  阅读(3502)  评论(1编辑  收藏  举报

在上篇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


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