Greenplum 将表未分区的数据修改为分区表
前言
在生产业务环境中，经常由于设计不规范，导致在数据库中存储的数据体量越来越庞大，那么带来的问题就是查询效率的低下和维护任务的增加。
在 Greenplum 数据库中，通过使用分区，可以实现大规模并行处理，并且子分区也可以拥有自己的分区，比如数据按照年份分区，再按照月份分区。在内部处理表分区的过程中，
Greenplum 在父级表和子表之间是通过被继承实现，类似于 PostgreSQL 中的继承。
如何决定分区策略
· 表是否足够大
通常在数据仓库中，会区分维度表和事实表，而事实表通常需要存储大量的数据，数据体量可能是百万或者数十亿条记录，对于这样的表，应该选择分区表，通过分区带来更高的性能优势。
· 查询是否满足预期
查询数据时返回慢，那么如果在一般的优化下无法提升查询性能，那么分区是优化的良好选择。
· 查询谓词筛选
在 WHERE 条件中，例如，倾向于按照日期筛选结果的表，建议使用分区表。
对未分区的表进行分区
--定义未分区表 tab_sales
create table tab_sales(id bigserial,gen_date date,amt decimal(10,2));
假设该表按照 gen_date 进行分区
--插入模拟数据数据(我这里使用 shell插入的模拟数据，略过)
#!/bin/bash CONNINFO="psql -U gpadmin -d postgres -Atq -c" for m in {1..12};do for d in {1..28};do $CONNINFO "insert into tab_sales(gen_date,amt) values('2020-$m-$d',10.00)" done done
-查询数据
postgres=# select count(*) from tab_sales; count ------- 336 (1 row)
在进行分区表创建之前，备份原表
create table tab_salesbak as select * from tab_sales;
--删除原表
drop table tab_sales;
创建分区表
CREATE TABLE tab_sales (id bigserial, gen_date date, amt decimal(10,2)) DISTRIBUTED BY (id) PARTITION BY RANGE (gen_date) ( PARTITION Jan16 START (date '2020-01-01') INCLUSIVE , PARTITION Feb16 START (date '2020-02-01') INCLUSIVE , PARTITION Mar16 START (date '2020-03-01') INCLUSIVE , PARTITION Apr16 START (date '2020-04-01') INCLUSIVE , PARTITION May16 START (date '2020-05-01') INCLUSIVE , PARTITION Jun16 START (date '2020-06-01') INCLUSIVE , PARTITION Jul16 START (date '2020-07-01') INCLUSIVE , PARTITION Aug16 START (date '2020-08-01') INCLUSIVE , PARTITION Sep16 START (date '2020-09-01') INCLUSIVE , PARTITION Oct16 START (date '2020-10-01') INCLUSIVE , PARTITION Nov16 START (date '2020-11-01') INCLUSIVE , PARTITION Dec16 START (date '2020-12-01') INCLUSIVE END (date '2021-01-01') EXCLUSIVE );
将备份表中的数据加载到新的分区表
postgres=# insert into tab_sales select * from tab_salesbak ; INSERT 0 336
--验证分区表中是否包含数据
postgres=# select * from tab_sales_1_prt_dec16; id | gen_date | amt -----+------------+------- 324 | 2020-12-16 | 10.00 318 | 2020-12-10 | 10.00 334 | 2020-12-26 | 10.00 321 | 2020-12-13 | 10.00
--验证结果，分区已经包含数据
除此之外，还有两种方式
第一种
--使用 pg_dump
备份单个表数据，如果表非常大，那么使用 pg_dump 中的 -Fc 或者 -Ft 参数进行压缩备份，还原时使用 pg_restore
[gpadmin@mdw ~]$ pg_dump -U gpadmin -t tab_sales -a postgres -f tab_sales
备份原来的表
postgres=# create table tab_salesbak as select * from tab_sales; NOTICE: Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy entry. SELECT 336
创建分区表
CREATE TABLE tab_sales (id bigserial, gen_date date, amt decimal(10,2)) DISTRIBUTED BY (id) PARTITION BY RANGE (gen_date) ( PARTITION Jan16 START (date '2020-01-01') INCLUSIVE , PARTITION Feb16 START (date '2020-02-01') INCLUSIVE , PARTITION Mar16 START (date '2020-03-01') INCLUSIVE , PARTITION Apr16 START (date '2020-04-01') INCLUSIVE , PARTITION May16 START (date '2020-05-01') INCLUSIVE , PARTITION Jun16 START (date '2020-06-01') INCLUSIVE , PARTITION Jul16 START (date '2020-07-01') INCLUSIVE , PARTITION Aug16 START (date '2020-08-01') INCLUSIVE , PARTITION Sep16 START (date '2020-09-01') INCLUSIVE , PARTITION Oct16 START (date '2020-10-01') INCLUSIVE , PARTITION Nov16 START (date '2020-11-01') INCLUSIVE , PARTITION Dec16 START (date '2020-12-01') INCLUSIVE END (date '2021-01-01') EXCLUSIVE );
-使用 psql 恢复数据，如果是使用 -Ft 或者 -Fc 备份的表数据，那么使用 pg_restore
[gpadmin@mdw ~]$ psql -U gpadmin -d postgres<tab_sales< div="">
-验证数据
postgres=# select * from tab_sales_1_prt_nov16; id | gen_date | amt -----+------------+------- 308 | 2020-11-28 | 10.00 292 | 2020-11-12 | 10.00 295 | 2020-11-15 | 10.00
第二种
--使用 COPY
登录到数据库，COPY 表到外部文件
postgres=# COPY tab_sales TO '/home/gpadmin/tab_sales' WITH DELIMITER '|' NULL AS ''; COPY 336
-备份原来的表
postgres=# CREATE TABLE tab_salesbak AS SELECT * FROM tab_sales; NOTICE: Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy entry. SELECT 336
删除原来的表，并创建分区表
DROP TABLE tab_sales; CREATE TABLE tab_sales (id bigserial, gen_date date, amt decimal(10,2)) DISTRIBUTED BY (id) PARTITION BY RANGE (gen_date) ( PARTITION Jan16 START (date '2020-01-01') INCLUSIVE , PARTITION Feb16 START (date '2020-02-01') INCLUSIVE , PARTITION Mar16 START (date '2020-03-01') INCLUSIVE , PARTITION Apr16 START (date '2020-04-01') INCLUSIVE , PARTITION May16 START (date '2020-05-01') INCLUSIVE , PARTITION Jun16 START (date '2020-06-01') INCLUSIVE , PARTITION Jul16 START (date '2020-07-01') INCLUSIVE , PARTITION Aug16 START (date '2020-08-01') INCLUSIVE , PARTITION Sep16 START (date '2020-09-01') INCLUSIVE , PARTITION Oct16 START (date '2020-10-01') INCLUSIVE , PARTITION Nov16 START (date '2020-11-01') INCLUSIVE , PARTITION Dec16 START (date '2020-12-01') INCLUSIVE END (date '2021-01-01') EXCLUSIVE );
--加载数据到新的分区表
postgres=# COPY tab_sales FROM '/home/gpadmin/tab_sales' WITH DELIMITER '|' NULL AS ''; COPY 336
-验证数据
postgres=# select * from tab_sales_1_prt_jul16 limit 5; id | gen_date | amt -----+------------+------- 175 | 2020-07-07 | 10.00 191 | 2020-07-23 | 10.00 172 | 2020-07-04 | 10.00 188 | 2020-07-20 | 10.00 169 | 2020-07-01 | 10.00
结语
以上就是普通表拆分为分区表的方式，建议在设计表时，对存储的数据进行了解。
