Exchanging a Leaf Child Partition with an External Table(分区表与外部表分区交换)

Exchanging a Leaf Child Partition with an External Table

You can exchange a leaf child partition of a partitioned table with a readable external table. The external

table data can reside on a host file system, an NFS mount, or a Hadoop file system (HDFS).

For example, if you have a partitioned table that is created with monthly partitions and most of the queries

against the table only access the newer data, you can copy the older, less accessed data to external tables

and exchange older partitions with the external tables. For queries that only access the newer data, you

could create queries that use partition elimination to prevent scanning the older, unneeded partitions.

Exchanging a leaf child partition with an external table is not supported if the partitioned table contains a

column with a check constraint or a NOT NULL constraint.（限制，分区表不能有check约束或者Not null的列）

For information about exchanging and altering a leaf child partition, see the ALTER TABLE command in

the Greenplum Database Command Reference .

For information about limitations of partitioned tables that contain a external table partition, see Limitations

of Partitioned Tables .

Example Exchanging a Partition with an External Table

This is a simple example that exchanges a leaf child partition of this partitioned table for an external table.

The partitioned table contains data for the years 2010 through 2013.

 

CREATE TABLE sales (id int, year int, qtr int, day int, region text)

DISTRIBUTED BY (id)

PARTITION BY RANGE (year)

( PARTITION yr START (2010) END (2014) EVERY (1) ) ;

 

There are four leaf child partitions for the partitioned table. Each leaf child partition contains the data for a

single year. The leaf child partition table sales_1_prt_yr_1 contains the data for the year 2010. These

steps exchange the table sales_1_prt_yr_1 with an external table the uses the gpfdist protocol:

1.

Ensure that the external table protocol is enabled for the Greenplum Database system.

This example uses the gpfdist protocol. This command starts the gpfdist protocol.

$ gpfdist

2. 创建可写外部表

CREATE WRITABLE EXTERNAL TABLE my_sales_ext ( LIKE sales_1_prt_yr_1 )

LOCATION ( 'gpfdist://gpdb_test/sales_2010' )

FORMAT 'csv'

DISTRIBUTED BY (id) ;

3. 创建可读外部表

CREATE EXTERNAL TABLE sales_2010_ext ( LIKE sales_1_prt_yr_1)

LOCATION ( 'gpfdist://gpdb_test/sales_2010' )

FORMAT 'csv' ;

4. 将分布表数据写入可写的外部表，这样 sales_2010_ext 就可以进行读取（可写和可读的外部表指向为同一个地址）

INSERT INTO my_sales_ext SELECT * FROM sales_1_prt_yr_1 ;

5. 交换子分区和外部表，外部表变成了子分区

ALTER TABLE sales ALTER PARTITION yr_1

EXCHANGE PARTITION yr_1

WITH TABLE sales_2010_ext WITHOUT VALIDATION;

 

Warning: In order to ensure queries against the partitioned table return the correct results, the

external table data must be valid against the CHECK constraints on the leaf child partition. In this

case, the data was taken from the child leaf partition table on which the CHECK constraints were

defined.

6.删除外部表

Drop the table that was rolled out of the partitioned table.

DROP TABLE sales_2010_ext ;

You can rename the name of the leaf child partition to indicate that sales_1_prt_yr_1 is an external

table.

This example command changes the partitionname to yr_1_ext and the name of the child leaf

partition table to sales_1_prt_yr_1_ext .

ALTER TABLE sales RENAME PARTITION yr_1 TO yr_1_ext ;