Vertica 分区表设计(续)

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

概述：Vertica分区表（天和月）创建以及删除分区

• 1.分区表创建
• 2.查询业务表的基本信息
• 3.Vertica入库测试数据
• 4.删除历史分区数据
• Reference

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