greenplum分区表

最新推荐文章于 2025-01-17 10:19:21 发布

原创最新推荐文章于 2025-01-17 10:19:21 发布 · 1.6k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#greenplum分区表

greenplum 专栏收录该内容

9 篇文章

订阅专栏

本文详细介绍了在Greenplum数据库中如何创建和操作分区表，包括按时间、列表和范围进行分区的方法，并提供了具体的SQL语句示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Greenplum中分区表的实现基本上与PostgreSQL中实现的原理是一样的，都是通过表的继承、规则、约束来实现的。

1、创建分区表

建表规则

[PARTITION by partition_type (COLUMN)]
	[SUBPARTITION BY partition_type (COLUMN)]
		[SUBPARTITION TEMPLATE (template_spec)]
	[...]
	(partition_spec)
		| [SUBPARTITION BY partition_type (COLUMN)]
			[...]
	(partition_spec
		[ (subpartition_spec
				[(...)]
				)])
and partition_element is:

	DEFAULT PARTITION name
	| [PARTITION name VALUES (list_value [,...])
	| [PARTITION name]
		START ([datatype] 'start_value') [inclusive | exclusive]
		[ END ([datatype] 'end_value') [inclusive | exclusive] ]
	| [PARTITION name]
		END ([datatype] 'end_value') [inclusive | exclusive]
		| EVERY ([datatype] [number | INTERVAL] 'interval_value') ]
[ WITH (partition_storage_parameter=value [,...])]
[ TABLESPACE tablespace]

按时间进行分区：

CREATE TABLE "public".partition_test(
	id int,
	name VARCHAR,
	partition_date date
)distributed by (id)
PARTITION by range(partition_date)
(
	PARTITION p20190301 START ('2019-03-01'::date) END ('2019-03-02'::date),
	PARTITION p20190302 START ('2019-03-02'::date) END ('2019-03-03'::date),
	PARTITION p20190303 START ('2019-03-03'::date) END ('2019-03-04'::date)
);

使用Every创建分区

CREATE TABLE "public".partition_test_every(
	id int,
	name VARCHAR,
	partition_date date
)distributed by (id)
PARTITION by range(partition_date)
(
	PARTITION p201903 START ('2019-03-01'::date) END ('2019-03-03'::DATE) EVERY ('1 days'::interval)
);

创建List分区

CREATE TABLE "public".partition_test_list(
	id int,
	city VARCHAR,
	partition_date date
)distributed by (id)
PARTITION by list(city)
(
	partition chongqing VALUES ('chongqing'),
	partition shanghai VALUES ('shanghai'),
	DEFAULT PARTITION other_city
);

2、分区表的操作

修改规则

ALTER DEFAULT PARTITION
DROP DEFAULT PARTITION [IF EXISTS]
DROP PARTITION [IF EXISTS] { partition_name |
	FOR (RANK(number)) | FOR (value) } [CASCADE]
TRUNCATE DEFAULT PARTITION
TRUNCATE PARTITION { partition_name | FOR (RANK(number)) |
	FOR (VALUE) }
RENAME DEFAULT PARTITION TO new_partition_name
RENAME PARTITION { partition_name | FOR (RANK(number)) |
	FOR (value) } TO new_partition_name
ADD DEFAULT PARTITION name [ (subpartition_spec) ]
ADD PARTITION [name] partition_element
	[ (subpartition_spec) ]
exchange PARTITION { partition_name | FOR (RANK(number)) |
	FOR (value) } WITH TABLE table_name
	[ WITH | WITHOUT validation]
exchange DEFAULT PARTITION WITH TABLE table_name 
	[ WITH | WITHOUT validation]
SET subpartition TEMPLATE (subpartition_spec)
split DEFAULT PARTITION
	{ AT (list_value) 
	| START ([datetype] range_value) [inclusive | exclusive]
		END ([datetype] range_value) [inclusive | exclusive] }
	| INTO ( PARTITION new_partition_name,PARTITION default_partition_name) ]
split PARTITION { partition_name | FOR (RANK(name)) |
	FOR (value) } AT (value)
	[INTO (PARTITION partition_name,PARTITION partition_name)]

新增分区

ALTER TABLE "public".partition_test add PARTITION p20190304 START ('2019-03-04'::date) END ('2019-03-06'::date);

删除分区

ALTER TABLE "public".partition_test drop PARTITION p20190301;

清空数据

ALTER TABLE "public".partition_test TRUNCATE PARTITION p20190302;

拆分分区

ALTER TABLE "public".partition_test split partition p20190304 at(('2019-03-05')::date) into (partition p20190304,partition p20190305);

对default分区进行拆分

ALTER TABLE "public".partition_test_list split DEFAULT partition at('hubei') into (partition hubei,DEFAULT partition);