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

结语

  以上就是普通表拆分为分区表的方式，建议在设计表时，对存储的数据进行了解。