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 ;