GreenPlum创建和管理表

0    26    1

Tags:

👉 本文共约21323个字,系统预计阅读时间或需81分钟。

Greenplum数据库的表与任何一种关系型数据库中的表类似,不过其表中的行被分布在系统中的不同Segment上。 当用户创建一个表时,用户会指定该表的分布策略。

创建一个表

CREATE TABLE命令创建一个表并且定义它的结果。当用户创建一个表示,用户需要定义:

选择列的数据类型

一个列的数据类型决定了该列能包含的数据的类型。 选择的数据类型应使用最少的空间,但仍能容纳用户的数据并且能最好地约束数据。 例如,对字符串使用character数据类型,对于日期使用date或者timestamp数据类型,而对数字使用numeric数据类型。

对于包含文本数据的表列,应指定数据类型为VARCHAR或者TEXT。 不推荐指定数据类型为CHAR。 在Greenplum数据库中数据类型VARCHAR或者TEXT会把加在数据后面的边距(在最后一个非空白字符后面增加的空白字符)处理为有效字符,而数据类型CHAR不会这样做。 关于character数据类型的信息,请见Greenplum数据库参考指南中的CREATE TABLE命令。

使用能容纳用户的数字型数据的且允许未来扩张的最小数字数据类型。 例如,为适合INT或SMALLINT的数据使用BIGINT会浪费存储空间。 如果用户预期用户的数据值将会随着时间扩张,应该考虑到在装载大量数据后从较小的数据类型更改成较大的数据类型需要很大的代价。 例如,如果用户当期的数据值适合SMALLINT,但是很可能值会扩张,这样INT就是更好的长期选择。

对用户计划要用在交叉表连接中的列使用相同的数据类型。 交叉表连接通常使用一个表中的主键和其他表中的外键。 当数据类型不同时,数据库必须转换其中之一以便数据值能被正确地比较,这会增加不必要的开销。

Greenplum数据库为用户提供了丰富的本地数据类型集合。 有关内建数据类型的信息请见Greenplum数据库参考指南。

设置表和列约束

用户可以在列和表上定义约束来限制表中的数据。 Greenplum数据库支持和PostgreSQL相同的约束,但是有一些限制,包括:

  • CHECK约束只能引用它所在的表。

  • UNIQUE和PRIMARY KEY约束必须和它们所在表的分布键和分区键(如果有)兼容。

    Note: 在追加优化表上不允许UNIQUE和PRIMARY KEY约束,因为追加优化表上不允许这些约束创建的UNIQUE索引。

  • 允许FOREIGN KEY约束,但不会被强制。

  • 用户在分区表上定义的约束将作为整体应用到分区表上。用户不能在该表的单独的部分上定义约束。

检查约束

检查约束允许用户指定一个特定列中的值必须满足一个布尔(真值)表达式。例如,要求正的产品价格:

非空约束

非空约束指定一个列不能有空值。非空约束总是被写作为列约束。例如:

唯一约束

唯一约束确保一列或者一组列中包含的数据对于表中所有的行都是唯一的。 该表必须是哈希分布或复制表(不可以是DISTRIBUTED RANDOMLY)。 如果表是哈希分布的,约束列必须是该表的分布键列(或者是一个超集)。例如:

主键

主键约束是一个UNIQUE约束和一个NOT NULL约束的组合。 该表必须是哈希分布(非DISTRIBUTED RANDOMLY)的,并且约束列必须是该表的分布键列(或者是一个超集)。 如果一个表具有主键,这个列(或者这一组列)会被默认选中为该表的分布键。 例如:

外键

不支持外键。用户可以声明它们,但是参照完整性不会被实施。

外键约束指定一列或者一组列中的值必须匹配出现在另一个表的某行中的值,以此来维护两个相关表之间的参照完整性。 参照完整性检查不能在一个Greenplum数据库的分布表段之间实施。

选择表分布策略

所有的Greenplum数据库表都会被分布。 当用户创建或者修改一个表时,用户可以有选择地指定DISTRIBUTED BY(哈希分布), DISTRIBUTED RANDOMLY(随机分布),或DISTRIBUTED REPLICATED(全分布)来决定该表的行分布。

Note: 如果创建表时没有指定DISTRIBUTED BY, Greenplum数据库服务器配置参数gp_create_table_random_default_distribution控制表的分布策略。

更多有关该参数的信息,请见Greenplum数据库参考指南的“服务器配置参数”部分。

在决定表分布策略时,请考虑以下几点。

  • 均匀数据分布 — 为了最好的性能,所有的Segment应该包含等量的数据。 如果数据不平衡或者倾斜,具有更多数据的Segment就必须做更多工作来执行它那一部分的查询处理。 请选择对于每一个记录都唯一的分布键,例如主键。
  • 本地和分布式操作 — 本地操作比分布式操作更快。 在Segment层面上,如果与连接、排序或者聚集操作相关的工作在本地完成,查询处理是最快的。 在系统层面完成的工作要求在Segment之间分布元组,其效率会低些。 当表共享一个共同的分布键时,在它们共享的分布键列上的连接或者排序工作会在本地完成。 对于随机分布策略来说,本地连接操作就行不通了。
  • 均匀查询处理 — 为了最好的性能,所有的Segment应该处理等量的查询负载。 如果一个表的数据分布策略与查询谓词匹配不好,查询负载可能会倾斜。 例如,假定一个销售事务表按照客户ID列(分布键)分布。 如果查询中的谓词引用了一个单一的客户ID,该查询处理工作会被集中在一个Segment上。

复制表分布策略(DISTRIBUTED REPLICATED)应该在小表上使用。 将大表数据复制到每个节点上无论在存储还是维护上都是有代价的。 复制表最基本的用例是:

  • 删除用户定义的函数可以对节点执行的操作的限制
  • 频繁使用的表不需要广播到所有节点可以提高查询性能。

声明分布键

CREATE TABLE命令的可选子句DISTRIBUTED BY, DISTRIBUTED RANDOMLY和DISTRIBUTED REPLICATED决定了表的分布策略。 默认的哈希分布策略使用PRIMARY KEY(如果有的话)或表的第一列作为分布键。 几何信息列或用户自定义数据类型的列是不能作为Greenplum数据库分布列的。 如果找不到合适的哈希分布的列,Greenplum数据库就选择随机分布策略。

复制表没有分布列,因为每行都分布在Greenplum数据库所有节点上。

为了保证哈希分布数据的均匀分布,最好选一个唯一键作为分布列。 如果找不到,则选择DISTRIBUTED RANDOMLY。例如:

Important: 主键总是表的分布列。如果没有主键,但是有唯一索引存在,则选择它为分布键。

自定义分布键哈希函数

用于哈希分布策略的哈希函数由列的数据类型的哈希运算符类定义。 由于默认的Greenplum数据库使用数据类型的默认哈希运算符类,因此用于哈希连接和哈希聚合的运算符类相同,适用于大多数用例。 但是,您可以在DISTRIBUTED BY子句中声明非默认的哈希运算符类。

使用自定义哈希运算符类可以用于支持与默认相等运算符(\=)不同的运算符上的共存连接。

自定义哈希操作符类用例

此示例为整数数据类型创建自定义哈希运算符类,该类用于提高查询性能。 运算符类比较整数的绝对值。

创建一个函数和一个等于运算符,如果两个整数的绝对值相等,则返回true。

现在,创建一个使用运算符的哈希函数和运算符类。

并且,为它们创建小于和大于运算符和B树运算符类。 我们的查询不需要它们,但是Greenplum数据库的Postgres查询优化器必须依赖它们做连接的co-location。

现在,您可以在表中使用自定义哈希运算符类。

执行使用自定义相等运算符|=|的连接的查询 可以利用co-location。 使用默认的整数opclass,此查询将需要Redistribute Motion节点,但使用自定义opclass,可以实现更高效的计划。

查看分步键

Greenplum introduced pg_get_table_distributedby() function for developers so that they can get the distribution key of a database table by passing the "oid" object id value in their SQL queries as follows.

参数gp_create_table_random_default_distribution

使用不包含DISTRIBUTED BY子句的CREATE TABLE或CREATE TABLE AS创建Greenplum数据库表时,控制表的创建。

对于CREATE TABLE,如果参数的值为off(缺省值), 并且创建表命令不包含DISTRIBUTED BY子句,Greenplum数据库将根据以下命令选择表分布键:

  • 如果指定了LIKE或INHERITS子句,则Greenplum将从源表或父表复制分布键。
  • 如果指定了PRIMARY KEY或UNIQUE约束,则Greenplum会选择所有键列的最大子集作为分布键。
  • 如果既没有指定约束也没有指定LIKE或INHERITS子句,则Greenplum会选择第一个合适的列作为分布键,一般都是将第1列作为分步键。 (具有几何或用户定义数据类型的列不符合Greenplum分布键列的条件。)

如果参数的值设置为on,则当未指定DISTRIBUTED BY子句时,Greenplum数据库将遵循这些规则来创建表:

  • 如果未指定PRIMARY KEY或UNIQUE列,则表的分布是随机的(DISTRIBUTED RANDOMLY)。 即使表创建命令包含LIKE或INHERITS子句,表分发也是随机的。
  • 如果指定了PRIMARY KEY或UNIQUE列,则还必须指定DISTRIBUTED BY子句。 如果未将DISTRIBUTED BY子句指定为表创建命令的一部分,则该命令将失败。

对于不包含分布子句的CREATE TABLE AS命令:

  • 如果Postgres查询优化器创建表,并且参数的值为off,则根据该命令确定表分发策略。
  • 如果Postgres查询优化器创建表,并且参数的值为on,则表分发策略是随机的。
  • 如果GPORCA创建表,则表分发策略是随机的。 参数值没有影响。

有关Postgres查询优化器和GPORCA的信息,请参阅Greenplum数据库管理员指南中的“查询数据”。

取值范围默认值设置分类
booleanoffmaster system reload

CREATE TABLE

定义一个新表。

Note: 引用完整性语法(外键约束)被接受但未强制执行。

概要

其中column_constraint是:

table_constraint是:

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

like_option是:

UNIQUE和PRIMARY KEY中的index_parameters约束为:

列的storage_directive是:

表的storage_parameter是:

key_action是:

partition_type是:

partition_specification是:

partition_element是:

其中subpartition_spec或template_spec是:

subpartition_element是:

其中分区的storage_parameter是:

描述

CREATE TABLE在当前数据库中创建一个最初为空的表。 执行命令的用户拥有该表。

为了能够创建表,您必须分别对所有列类型或OF子句中的类型具有USAGE特权。

如果指定模式名称,Greenplum将在指定的模式中创建表。 否则,Greenplum将在当前模式中创建表。 临时表存在于特殊的模式中,因此在创建临时表时不能指定模式名称。 表名称必须与同一模式中的任何其他表,外部表,序列,索引,视图或外部表的名称不同。

CREATE TABLE还会自动创建一个数据类型,该数据类型表示与表的一行相对应的复合类型。 因此,表不能与同一模式中的任何现有数据类型具有相同的名称。

可选的约束子句指定新行或更新行必须满足的条件才能成功执行插入或更新操作。 约束是一个SQL对象,可以通过多种方式帮助定义表中的有效值集。 约束适用于表,而不适用于分区。 您不能将约束添加到分区或子分区。

引用完整性约束(外键)被接受但不强制执行。 该信息保留在系统catalog中,否则将被忽略。

有两种定义约束的方法:表约束和列约束。 列约束被定义为列定义的一部分。 表约束定义不与特定列绑定,并且可以包含多个列。 每个列约束也可以写为表约束。 当约束仅影响一列时,使用列约束只是一种符号上的方便。

创建表时,还有一个附加子句来声明Greenplum数据库分发策略。 如果未提供DISTRIBUTED BY,DISTRIBUTED RANDOMLY或DISTRIBUTED REPLICATED子句, 则Greenplum数据库将通过使用PRIMARY KEY(如果表具有一个)或表的第一列作为分发键,向该表分配哈希分发策略。 几何或用户定义数据类型的列不符合Greenplum分布键列的要求。 如果表中没有符合条件的数据类型的列,则将根据轮询或随机分布来分配行。 为了确保数据在Greenplum数据库系统中的均匀分配, 您希望选择一个对于每个记录都是唯一的分配键,或者如果不可能,则选择DISTRIBUTED RANDOMLY。

如果提供了DISTRIBUTED REPLICATED子句,则Greenplum数据库会将表的所有行分配给Greenplum数据库系统中的所有segment。 如果用户定义的函数必须在segment上执行,并且这些函数需要访问表的所有行,则可以使用此选项。 复制函数还可以用于防止表的broadcast motions,从而提高查询性能。 DISTRIBUTED REPLICATED子句不能与PARTITION BY子句或INHERITS子句一起使用。 复制的表也不能被另一个表继承。 隐藏的系统列(ctid,cmin,cmax,xmin, xmax和gp_segment_id)无法在复制表的用户查询中引用,因为它们没有单一的,无歧义的值。

通过PARTITION BY子句,您可以将表分为多个子表(或部分),这些子表一起构成父表并共享其模式。 尽管子表作为独立表存在,但是Greenplum数据库以重要方式限制了它们的使用。 在内部,分区被实现为继承的一种特殊形式。 每个子表分区都是根据不同的CHECK约束创建的,该约束根据一些定义条件限制表可以包含的数据。 查询优化器还使用CHECK约束来确定要扫描哪些表分区以满足给定的查询谓词。 这些分区约束由Greenplum数据库自动管理。

参数

GLOBAL | LOCAL

提供这些关键字是为了实现SQL标准兼容性,但在Greenplum数据库中无效,并且已弃用。

TEMPORARY | TEMP

如果指定,该表将被创建为临时表。 临时表在会话结束时或在当前事务结束时自动删除(请参见ON COMMIT)。 临时表存在时,具有相同名称的现有永久表在当前会话中不可见,除非使用模式限定名称引用它们。 在临时表上创建的所有索引也会自动成为临时索引。

UNLOGGED

如果指定,该表将创建为未记录表。 写入未记录表的数据不会写入预写(WAL)日志,这使它们比普通表快得多。 但是,未记录表的内容不会复制到mirror实例。 同样,未记录的表也不是崩溃安全的。 segment实例崩溃或异常关闭后,该segment上未记录表的数据将被截断。 在未记录表上创建的所有索引也会自动成为未记录索引。

table_name

要创建的新表的名称(可以由模式指定)。

OF type_name

创建一个类型化的表,该表从指定的组合类型(名称可以由模式指定)获取其结构。 类型化的表与其类型相关联。 例如,如果删除了类型(使用DROP TYPE … CASCADE),则将删除该表。

当一个类型化的表被创建时,列的数据类型由底层的组合类型决定而没有在CREATE TABLE命令中直接指定。 但是CREATE TABLE命令可以对表增加默认值和约束,并且可以指定存储参数。

column_name

要在新表中创建的列的名称。

data_type

列的数据类型。这可能包括数组说明符。

对于包含文本数据的表列,请指定数据类型VARCHAR或TEXT。 不建议指定数据类型CHAR。 在Greenplum数据库中,数据类型VARCHAR或TEXT处理作为有效字符添加到数据 (在最后一个非空格字符之后添加的空格字符)的填充,而数据类型CHAR不处理。 请参阅注解

COLLATE collation

COLLATE子句为该列分配排序规则(该排序规则必须是可排序的数据类型)。 如果未指定,则使用列数据类型的默认排序规则。

Note: 仅当查询中的所有列使用相同的排序规则时,GPORCA才支持排序规则。 如果查询中的列使用不同的排序规则,则Greenplum使用Postgres查询优化器。

DEFAULT default_expr

DEFAULT子句为出现在其列定义中的列分配默认数据值。 该值是任何不带变量的表达式(不允许对当前表中的其他列进行子查询和交叉引用)。 默认表达式的数据类型必须与列的数据类型匹配。 默认表达式将在未为列指定值的任何插入操作中使用。 如果列没有默认值,则默认值为null。

ENCODING ( storage_directive [, …] )

对于列,可选的ENCODING子句指定列数据的压缩类型和块大小。 有关compresstype,compresslevel和blocksize值,请参见storage_options

该子句仅对追加优化的,面向列的表有效。

列压缩设置从表级别继承到分区级别再到子分区级别。 最低级别的设置具有优先权。

INHERITS ( parent_table [, …])

可选的INHERITS子句指定一个表列表,新表将从中自动继承所有列。 使用INHERITS将在新的子表及其父表之间创建持久关系。 对父级的模式修改通常也会传播到子级,默认情况下,子级表的数据包含在父级扫描中。

在Greenplum数据库中,创建分区表时不使用INHERITS子句。 尽管在分区层次结构中使用了继承的概念,但是使用PARTITION BY子句创建了分区表的继承结构。

如果一个以上的父表中存在相同的列名,则将报告错误,除非每个父表中的列的数据类型都匹配。 如果没有冲突,则将重复的列合并以在新表中形成一个列。 如果新表的列名列表包含一个也被继承的列名,则数据类型必须同样与继承的列匹配,并且列定义将合并为一个。 如果新表显式指定了该列的默认值,则该默认值将覆盖该列的继承声明中的所有默认值。 否则,为该列指定默认值的所有父项都必须指定相同的默认值,否则将报告错误。

CHECK约束基本上以与列相同的方式合并: 如果多个父表或新表定义包含名称相同的约束,则这些约束必须全部具有相同的校验表达式,否则将报告错误。 具有相同名称和表达式的约束将合并为一个副本。 不会考虑在父级中标记为NO INHERIT的约束。 请注意,新表中未命名的CHECK约束将永远不会被合并,因为将始终为其选择唯一的名称。

列STORAGE设置也会从父表中复制。

LIKE source_table like_option …]

LIKE子句指定一个表,新表将从该表中自动复制所有列名,其数据类型,非空约束和分发策略。 不会复制诸如追加优化或分区结构之类的存储属性。 与INHERITS不同,新表和原始表在创建完成后完全解耦。

仅当指定INCLUDING DEFAULTS时,才会复制复制的列定义的默认表达式。 默认行为是排除默认表达式,导致新表中复制的列具有空默认值。

非空约束始终会复制到新表中。 仅当指定INCLUDING CONSTRAINTS时,才会复制CHECK约束。 列约束和表约束之间没有区别。

仅在指定INCLUDING INDEXES子句的情况下,才会在新表上创建原始表的索引, PRIMARY KEY和UNIQUE约束。 不论原始名称如何命名,都会根据默认规则选择新索引和约束的名称。 (此行为避免了新索引可能出现的重复名称错误。)

除非指定了INCLUDING INDEXES子句,否则不会在新表上创建原始表上的任何索引。

仅当指定了INCLUDING STORAGE时,才会复制复制的列定义的STORAGE设置。 默认行为是排除STORAGE设置,导致新表中复制的列具有特定于类型的默认设置。

仅当指定INCLUDING COMMENTS时,才会复制复制的列,约束和索引的注释。 默认行为是排除注释,导致新表中复制的列和约束没有注释。

INCLUDING ALL 是 INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS的缩写形式.

请注意,与INHERITS不同,LIKE复制的列和约束不会与名称相似的列和约束合并。 如果显式指定了相同的名称,或者在另一个LIKE子句中指定了相同的名称,则将指示错误。

LIKE子句还可用于从视图,外部表或复合类型中复制列。 不适用的选项(例如,从视图INCLUDING INDEXES)将被忽略。

CONSTRAINT constraint_name

列或表约束的可选名称。 如果违反了约束,那么约束名称将出现在错误消息中,因此可以使用约束名称(例如列必须为正)来将有用的约束信息传达给客户端应用程序。 (需要双引号指定包含空格的约束名称。)如果未指定约束名称,则系统将生成一个名称。

Note: 指定的constraint_name用于约束,但系统生成的唯一名称用于索引名。 在某些以前的版本中,提供的名称同时用于约束名称和索引名称。

NULL | NOT NULL

指定是否允许该列包含空值。默认值为NULL。

CHECK (expression) [ NO INHERIT ]

CHECK子句指定一个生成布尔结果的表达式,新的或更新的行必须满足才能使插入或更新操作成功。 计算为TRUE或UNKNOWN的表达式会成功。 如果插入或更新操作的任何行都产生FALSE结果,则会引发错误异常,并且插入或更新不会更改数据库。 指定为列约束的检查约束应仅引用该列的值,而出现在表约束中的表达式可以引用多个列。

标有NO INHERIT的约束不会传播到子表。

当前,CHECK表达式不能包含子查询,也不能引用当前行的列以外的变量。

UNIQUE ( column_constraint )

UNIQUE ( column_name [, … ] ) ( table_constraint )

UNIQUE约束指定表的一组一个或多个列只能包含唯一值。 唯一表约束的行为与列约束的行为相同,但具有跨多个列的附加功能。 出于唯一约束的目的,空值不视为相等。 唯一的列必须包含Greenplum分布键的所有列。 此外,如果表已分区,则必须包含分区键中的所有列。 请注意,分区表中的约束与简单的UNIQUE INDEX不同。

有关唯一约束管理和限制的信息,请参见注解

PRIMARY KEY ( column constraint )

PRIMARY KEY ( column_name [, … ] ) ( table constraint )

PRIMARY KEY约束指定表的一列或多列只能包含唯一(非重复),非null值。 只能为一个表指定一个主键,无论是作为列约束还是表约束。

要使一个表具有主键,它必须是哈希分布的(不是随机分布的),并且主键(唯一的列)必须包含Greenplum分布键的所有列。 此外,如果表已分区,则必须包含分区键中的所有列。 请注意,分区表中的约束与简单的UNIQUE INDEX不同。

PRIMARY KEY强制执行与UNIQUE和NOT NULL相同的组合数据约束, 但是将一组列标识为主键也可以提供有关模式设计的元数据,因为主键标识其他表可以依赖这一个列集合作为行的唯一标识符。

有关主键管理和限制的信息,请参阅注解

REFERENCES reftable [ ( refcolumn ) ]

[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]

[ON DELETE | ON UPDATE] [key_action]

FOREIGN KEY (column_name [, …])

REFERENCES和FOREIGN KEY子句指定引用完整性约束(外键约束)。 Greenplum接受PostgreSQL语法中指定的参照完整性约束,但不强制执行。 有关引用完整性约束的信息,请参见PostgreSQL文档。

DEFERRABLE

NOT DEFERRABLE

[NOT] DEFERRABLE子句控制约束是否可以被推迟。 一个不可推迟的约束将在每个命令后立即进行检查。 可以推迟检查约束,直到事务结束(使用SET CONSTRAINTS命令)。 默认值是NOT DEFERRABLE。 当前,只有UNIQUE和PRIMARY KEY约束是可推迟的。 NOT NULL和CHECK约束不可延迟。 REFERENCES(外键)约束接受此子句,但不强制执行。

INITIALLY IMMEDIATE

INITIALLY DEFERRED

如果约束是可延迟的,则此子句指定检查约束的默认时间。 如果该约束是INITIALLY IMMEDIATE,则在每个语句之后对其进行检查。 这是默认值。 如果约束是INITIALLY DEFERRED,则仅在事务结束时进行检查。 可以使用SET CONSTRAINTS命令更改约束检查时间。

WITH ( storage_parameter=value )

WITH子句可以为表以及与UNIQUE或PRIMARY约束关联的索引指定存储参数。 请注意,您还可以通过在分区规范中声明WITH子句来在特定分区或子分区上设置存储参数。 最低级别的设置具有优先权。

某些表存储选项的默认值可以使用服务器配置参数gp_default_storage_options指定。 有关设置默认存储选项的信息,请参阅注解

可以使用以下存储选项:

appendoptimized — 设置为TRUE可将表创建为追加优化的表。 如果为FALSE或未声明,则将表创建为常规堆存储表。

blocksize — 设置为表中每个块的大小(以字节为单位)。 blocksize必须介于8192和2097152字节之间,并且是8192的倍数。默认值为32768。

orientation — 设置为column以用于列式存储,或设置为row(默认)以用于行式存储。 仅当appendoptimized=TRUE时,此选项才有效。堆存储表只能是面向行的。

checksum — 此选项仅对追加优化的表(appendoptimized=TRUE)有效。 值TRUE是默认值,并为追加优化表启用CRC校验和验证。 校验和是在块创建期间计算的,并存储在磁盘上。 在块读取期间执行校验和验证。 如果在读取期间计算出的校验和与存储的校验和不匹配,则事务中止。 如果将值设置为FALSE以禁用校验和验证,将不会执行检查表数据是否存在磁盘损坏的操作。

compresstype — 设置为ZLIB(默认值),ZSTD,RLE_TYPE或QUICKLZ1以指定使用的压缩类型。 值NONE禁用压缩。 Zstd提供速度或良好的压缩率,可通过compresslevel选项进行调整。 提供QuickLZ和zlib是为了向后兼容。 在通常的工作负载上,Zstd的性能优于这些压缩类型。 仅当appendoptimized=TRUE时,compresstype选项才有效。

Note: 注意:1QuickLZ压缩仅在商业版本的Pivotal Greenplum数据库中可用。

仅当指定了orientation\=column时才支持值RLE_TYPE,它启用游程编码(RLE)压缩算法。 当相同的数据值出现在许多连续的行中时,RLE的压缩数据比Zstd,zlib或QuickLZ压缩算法更好。

对于BIGINT,INTEGER,DATE, TIME或TIMESTAMP类型的列,如果将compresstype选项设置为RLE_TYPE压缩,则还将应用增量压缩。 增量压缩算法基于连续行中列值之间的增量,旨在改善按排序顺序加载数据或将压缩应用于按排序顺序的列数据时的压缩。

有关使用表压缩的信息,请参阅Greenplum数据库管理员指南中的“选择表存储模型”。

compresslevel — 对于追加优化表的Zstd压缩,请将其设置为1(最快压缩)到19(最高压缩率)之间的整数值。 对于zlib压缩,有效范围是1到9。QuickLZ压缩级别只能设置为1。 如果未声明,则默认值为1。 对于RLE_TYPE,压缩级别可以是1(最快压缩)到4(最高压缩率)之间的整数值。

仅当appendoptimized=TRUE时,compresslevel选项才有效。

fillfactor — 有关此索引存储参数的更多信息,请参见CREATE INDEX

oids — 设置为oids=FALSE(默认值),以便不为行分配对象标识符。 Greenplum强烈建议您在创建表时不要启用OIDS。 在大型表上(例如典型的Greenplum数据库系统中的表),对表行使用OID可能会导致32位OID计数器的折回。 一旦计数器回绕,就不能再认为OID是唯一的,这不仅使它们对用户应用程序无用, 而且还会在Greenplum数据库系统catalog表中引起问题。 此外,从表中排除OID会使表每行存储在磁盘上所需的空间减少了每行4个字节,从而略微提高了性能。 分区表或追加优化的面向列的表上不允许使用OIDS。

WITH OIDS

WITHOUT OIDS

它们是分别等同于WITH (OIDS)和WITH (OIDS=FALSE)的过时语法。 如果希望同时提供OIDS设置和存储参数,则必须使用WITH ( … )语法;往上看。 Greenplum强烈建议您在创建表时不要启用OIDS。 有关使用oids的更多信息,请参见oids参数说明。

ON COMMIT

可以使用ON COMMIT控制事务块末尾的临时表的行为。 这三个选项是:

PRESERVE ROWS - 临时表的事务结束时不会采取任何特殊操作。这是默认行为。

DELETE ROWS - 临时表中的所有行将在每个事务块的末尾删除。 本质上,每次提交都会自动执行一次TRUNCATE。

DROP - 临时表将在当前事务块的末尾删除。

TABLESPACE tablespace

要在其中创建新表的表空间的名称。如果未指定,则使用数据库的默认表空间。

USING INDEX TABLESPACE tablespace

该子句允许选择将在其中创建与UNIQUE或PRIMARY KEY约束关联的索引的表空间。 如果未指定,则使用数据库的默认表空间。

DISTRIBUTED BY (column [opclass], [ … ] )

DISTRIBUTED RANDOMLY

DISTRIBUTED REPLICATED

用于声明表的Greenplum数据库分布策略。 DISTRIBUTED BY使用具有一个或多个声明为分布键的列的哈希分布。 为了获得最均匀的数据分配,分布键应为表的主键或唯一列(或一组列)。 如果无法做到这一点,则可以选择DISTRIBUTED RANDOMLY,它将数据轮询发送到segment实例。 此外,可以指定运算符类opclass,以使用非默认哈希函数。

如果在创建表时未指定DISTRIBUTED BY子句, 则Greenplum数据库服务器配置参数gpcreatetable_random_default_distribution将控制表默认分布策略。 如果未指定分布策略,Greenplum数据库将遵循以下规则来创建表。

如果参数的值是off(默认值),则Greenplum数据库根据以下命令选择表分布键:

  • 如果指定了LIKE或INHERITS子句,则Greenplum从源表或父表复制分布键。
  • 如果指定了PRIMARY KEY或UNIQUE约束,则Greenplum选择所有键列中最大的子集作为分布键。
  • 如果既未指定约束,也未指定LIKE或INHERITS子句,则Greenplum选择第一个合适的列作为分布键。 (具有几何或用户定义数据类型的列不符合作为Greenplum分布键列的条件。)

如果参数的值设置为on,则Greenplum数据库遵循以下规则:

  • 如果未指定PRIMARY KEY或UNIQUE列, 则表的分布是随机的(DISTRIBUTED RANDOMLY)。 即使表创建命令包含LIKE或INHERITS子句,表分布也是随机的。
  • 如果指定了PRIMARY KEY或UNIQUE列,则还必须指定DISTRIBUTED BY子句。 如果在表创建命令中未指定DISTRIBUTED BY子句,则该命令将失败。

有关设置默认表分布策略的更多信息, 请参见gpcreatetable_random_default_distribution

DISTRIBUTED REPLICATED子句将整个表复制到所有Greenplum数据库segment实例。 当函数需要访问表中的所有行或需要通过阻止broadcast motion来提高查询性能时,必须在segment上执行用户定义的函数时可以使用它。

PARTITION BY

声明用于对表进行分区的一列或多列。

创建分区表时,Greenplum数据库使用指定的表名创建根分区表(根分区)。 Greenplum数据库还会根据您指定的分区选项创建表,子表的层次结构,这些表是子分区。 Greenplum数据库*pg_partition**系统视图包含有关子分区表的信息。

对于每个分区级别(表的每个层次结构级别),一个分区表最多可以有32,767个分区。

Note: Greenplum数据库将分区表数据存储在叶子表中,叶子表是子表层次结构中的最低级表,供分区表使用。

  • partition_type

    声明分区类型:LIST(值列表)或RANGE(数字或日期范围)。

    partition_specification

    声明要创建的各个分区。 可以单独定义每个分区,或者对于范围分区,可以使用EVERY子句(带有START和可选END子句)来定义用于创建单个分区的增量模式。

    DEFAULT PARTITION name — 声明默认分区。 当数据与现有分区不匹配时,会将其插入默认分区。 没有默认分区的分区设计将拒绝与现有分区不匹配的传入行。

    PARTITION name — 声明要用于分区的名称。 使用以下命名约定创建分区:parentname_level#_prt_givenname。

    VALUES — 对于列表分区,定义分区将包含的值。

    START — 对于范围分区,定义分区的起始范围值。 默认情况下,起始值为INCLUSIVE。 例如,如果您声明开始日期为’2016-01-01’, 则分区将包含所有大于或等于’2016-01-01’的日期。 通常,START表达式的数据类型与分区键列的类型相同。 如果不是这种情况,则必须显式转换为预期的数据类型。

    END — 对于范围分区,定义分区的结束范围值。 默认情况下,结束值为EXCLUSIVE。 例如,如果您声明结束日期为’2016-02-01’, 则分区将包含所有小于但不等于’2016-02-01’的日期。 通常,END表达式的数据类型与分区键列的类型相同。 如果不是这种情况,则必须显式转换为预期的数据类型。

    EVERY — 对于范围分区,定义如何将值从START递增到END以创建单个分区。 通常,EVERY表达式的数据类型与分区键列的类型相同。 如果不是这种情况,则必须显式转换为预期的数据类型。

    WITH — 设置分区的表存储选项。 例如,您可能希望将较旧的分区作为追加优化表,而将较新的分区作为常规堆表。

    TABLE\SPACE — 要在其中创建分区的表空间的名称。

SUBPARTITION BY

声明用于对表的第一级分区进行子分区的一个或多个列。 子分区的规范格式类似于上述分区的规范格式。

SUBPARTITION TEMPLATE

您可以选择声明一个用于创建子分区的子分区模板(低级别子表),而不是为每个分区分别声明每个子分区定义。 然后,此子分区规范将应用于所有父分区。

注解

  • 在Greenplum数据库(基于Postgres的系统)中, 数据类型VARCHAR或TEXT处理填充作为有效字符添加到文本的数据(最后一个非空格字符之后添加空格字符); 数据类型CHAR则没有。

    在Greenplum数据库中,CHAR(n)类型的值用尾随空格填充到指定的宽度n。 值将存储并显示为空格。 但是,填充空格在语义上无关紧要。 分配值时,将忽略尾随空格。 比较数据类型CHAR的两个值时,尾随空格在语义上也无关紧要, 而将字符值转换为其他字符串类型之一时,尾随空格也将被删除。

  • 不建议在新应用程序中使用OID:在可能的情况下,最好使用SERIAL或其他序列生成器作为表的主键。 但是,如果您的应用程序确实使用OID来标识表的特定行, 则建议在该表的OID列上创建唯一约束,以确保即使在计数器回绕后,表中的OID的确可以唯一地标识行。 避免假设OID在表之间是唯一的;如果需要数据库范围的唯一标识符,则可以使用表OID和行OID的组合。

  • Greenplum数据库对于主键和作为Greenplum表中的分布键的列的唯一约束具有一些特殊条件。 为了在Greenplum数据库中实施唯一约束,表必须是哈希分布的(不是DISTRIBUTED RANDOMLY), 并且约束列必须与表的分布键列相同(或作为其的超集)。 另外,分布键必须是约束列的左子集,并且列的顺序正确。 例如,如果主键是(a,b,c),则分布键只能是以下之一:(a),(a,b)或(a,b,c)。

    复制表(DISTRIBUTED REPLICATED)可以同时具有PRIMARY KEY和UNIQUE列约束。

    主键约束只是唯一约束和非空约束的组合。

    Greenplum数据库自动为每个UNIQUE或PRIMARY KEY约束创建一个UNIQUE索引,以强制执行唯一性。 因此,没有必要为主键列显式创建索引。 在追加优化表上不允许使用UNIQUE和PRIMARY KEY约束,因为在追加优化表上不允许通过约束创建的UNIQUE索引。

    Greenplum数据库中不支持外键约束。

    对于继承的表,当前实现中不会继承唯一约束,主键约束,索引和表特权。

  • 对于追加优化的表,可重复读或可序列化事务中不允许UPDATE和DELETE,这将导致事务中止。 CLUSTER,DECLARE…FOR UPDATE和触发器不支持追加优化的表。

  • 要将数据插入分区表中,请指定根分区表,即使用CREATE TABLE命令创建的表。 您还可以在INSERT命令中指定分区表的叶子表。 如果数据对于指定的叶子表无效,则返回错误。 不支持在INSERT命令中指定不是叶子表的子表。 不支持在分区表的任何子表上执行其他DML命令,例如UPDATE和DELETE。 这些命令必须在根分区表(使用CREATE TABLE命令创建的表)上执行。

  • 可以使用服务器配置参数gp_default_storage_option指定这些表存储选项的默认值。

    • appendoptimized
    • blocksize
    • checksum
    • compresstype
    • compresslevel
    • orientation

    可以为系统,数据库或用户设置默认值。 有关设置存储选项的信息,请参阅服务器配置参数gp_default_storage_options

Important: 当前的Greenplum数据库Postgres优化器允许使用具有多列(复合)分区键的列表分区。 GPORCA不支持复合键,因此不建议使用复合分区键。

示例

在名为baby的模式中创建一个名为rank的表, 并使用rank,gender和year列分发数据:

创建表files和表分配器(默认情况下,主键将用作Greenplum分布键):

创建一个gzip压缩的,追加优化的表:

使用子分区模板和每个级别的默认分区创建一个三级分区表:

兼容性

CREATE TABLE命令符合SQL标准,但以下情况除外:

  • Temporary *Table*s — 在SQL标准中,临时表仅定义一次,并在每个需要它们的会话中自动存在(从空内容开始)。 相反,Greenplum数据库要求每个会话为要使用的每个临时表发出自己的CREATE TEMPORARY TABLE命令。 这允许不同的会话出于不同的目的而使用相同的临时表名称,而标准的方法将给定临时表名称的所有实例约束为具有相同的表结构。

    全局和本地临时表之间的标准区别不在Greenplum数据库中。 Greenplum数据库将在临时表声明中接受GLOBAL和LOCAL关键字,但它们无效且已弃用。

    如果省略ON COMMIT子句,则SQL标准将默认行为指定为ON COMMIT DELETE ROWS。 但是,Greenplum数据库中的默认行为是ON COMMIT PRESERVE ROWS。 SQL标准中不存在ON COMMIT DROP选项。

  • Column Check Constraints — SQL标准说,CHECK列约束只能引用它们所适用的列。 只有CHECK表约束可以引用多个列。 Greenplum数据库不强制执行此限制;它对待列和表检查约束都一样。

  • NULL Constraint — NULL约束是对SQL标准的Greenplum数据库扩展, 为了与某些其他数据库系统兼容(以及对称的NOT NULL约束)。 由于它是任何列的默认值,因此不需要它的存在。

  • Inheritance — 通过INHERITS子句的多重继承是Greenplum数据库语言的扩展。 SQL:1999及更高版本使用不同的语法和语义定义了单个继承。 Greenplum数据库尚不支持SQL:1999样式的继承。

  • Partitioning — 通过PARTITION BY子句进行的表分区是Greenplum数据库语言的扩展。

  • Zero-column *table*s — Greenplum数据库允许创建不包含任何列的表(例如CREATE TABLE foo();)。 这是SQL标准的扩展,不允许使用零列表。 零列表本身并没有什么用,但是不允许使用零列表在ALTER TABLE DROP COLUMN时会产生奇怪的特殊情况, 因此Greenplum决定忽略此规范限制。

  • LIKE — 尽管SQL标准中存在LIKE子句, 但Greenplum数据库接受的许多选项都不在该标准中,并且Greenplum数据库并未实现该标准的某些选项。

  • WITH clause — WITH子句是Greenplum数据库扩展。 存储参数和OID都不在标准中。

  • Table\spaces — 表空间的Greenplum数据库概念不是SQL标准的一部分。 子句TABLESPACE和USING INDEX TABLESPACE是扩展。

  • Data Distribution — 并行或分布式数据库的Greenplum数据库概念不是SQL标准的一部分。 DISTRIBUTED子句是扩展。

另见

ALTER TABLE , DROP TABLE , CREATE EXTERNAL TABLE , CREATE TABLE AS

CREATE TABLE AS

根据查询结果定义一个新表。

概要

其中storage_parameter是:

描述

CREATE TABLE AS创建一个表, 并用SELECT命令计算的数据填充该表。 表列具有与SELECT的输出列关联的名称和数据类型,但是您可以通过提供新列名称的显式列表来覆盖列名称。

CREATE TABLE AS创建一个新表并仅对查询进行一次取值以填充一次新表。 新表将不会跟踪对查询源表的后续更改。

参数

GLOBAL | LOCAL

忽略兼容性。 这些关键字已弃用;有关详细信息,请参考CREATE TABLE

TEMPORARY | TEMP

如果指定,则将新表创建为临时表。 临时表在会话结束时或在当前事务结束时自动删除(请参见ON COMMIT)。 临时表存在时,具有相同名称的现有永久表在当前会话中不可见,除非使用模式限定名称引用它们。 在临时表上创建的所有索引也会自动成为临时索引。

UNLOGGED

如果指定,该表将创建为未记录表。 写入未记录表的数据不会写入预写(WAL)日志,这使它们比普通表快得多。 但是,未记录表的内容不会复制到mirror实例。 同样,未记录的表也不是崩溃安全的。 segment实例崩溃或异常关闭后,该segment上未记录表的数据将被截断。 在未记录表上创建的所有索引也会自动成为未记录索引。

table_name

要创建的新表的名称(可以由模式指定)。

column_name

新表中的列名。如果未提供列名,则它们取自查询的输出列名。

WITH ( storage_parameter=value )

WITH子句可用于设置表或其索引的存储选项。 请注意,您还可以通过在分区规范中声明WITH子句,在特定分区或子分区上设置不同的存储参数。 可以使用以下存储选项:

appendoptimized — 设置为TRUE可将表创建为追加优化的表。 如果为FALSE或未声明,则将表创建为常规堆存储表。

blocksize — 设置为表中每个块的大小(以字节为单位)。 blocksize必须介于8192和2097152字节之间,并且是8192的倍数。默认值为32768。

orientation — 设置为column以用于列式存储,或设置为row(默认)以用于行式存储。 仅当appendoptimized=TRUE时,此选项才有效。 堆存储表只能是面向行的。

compresstype — 设置为ZLIB(默认值),ZSTD或QUICKLZ1以指定使用的压缩类型。 值NONE禁用压缩。 Zstd提供速度或良好的压缩率,可通过compresslevel选项进行调整。 提供QuickLZ和zlib是为了向后兼容。 在通常的工作负载上,Zstd的性能优于这些压缩类型。 仅当appendoptimized=TRUE时,compresstype选项才有效。

Note: 1QuickLZ压缩仅在Pivotal Greenplum数据库的商业版本中可用。

compresslevel — 对于附加优化表的Zstd压缩,请将其设置为1(最快压缩)到19(最高压缩率)之间的整数值。 对于zlib压缩,有效范围是1到9。 QuickLZ压缩级别只能设置为1。 如果未声明,则默认值为1。 compresslevel选项仅在appendoptimized=TRUE时有效。

fillfactor — 有关此索引存储参数的更多信息,请参见CREATE INDEX

oids — 设置为oids=FALSE(默认值),以便不为行分配对象标识符。 Greenplum强烈建议您在创建表时不要启用OIDS。 在大型表上(例如典型的Greenplum数据库系统中的表),对表行使用OID可能会导致32位OID计数器的折回。 一旦计数器回绕,就不能再认为OID是唯一的,这不仅使它们对用户应用程序无用, 而且还会在Greenplum数据库系统catalog表中引起问题。 此外,从表中排除OID会使表每行存储在磁盘上所需的空间减少了每行4个字节,从而略微提高了性能。 面向列的表不允许使用OIDS。

ON COMMIT

可以使用ON COMMIT控制事务块末尾的临时表的行为。 这三个选项是:

PRESERVE ROWS — 临时表的事务结束时不会采取任何特殊操作。这是默认行为。

DELETE ROWS — 临时表中的所有行将在每个事务块的末尾删除。 本质上,每次提交都会自动执行一次TRUNCATE。

DROP — 临时表将在当前事务块的末尾删除。

TABLESPACE tablespace_name

tablespace_name参数是要在其中创建新表的表空间的名称。 如果未指定,则使用数据库的默认表空间。

AS query

SELECTTABLEVALUES命令, 或运行准备的SELECT或VALUES查询的EXECUTE命令。

DISTRIBUTED BY ({column [opclass]}, [ … ] )

DISTRIBUTED RANDOMLY

DISTRIBUTED REPLICATED

用于声明表的Greenplum数据库分发策略。 DISTRIBUTED BY使用具有一个或多个声明为分发键的列的哈希分发。 为了获得最均匀的数据分配,分发键应为表的主键或唯一列(或一组列)。 如果无法做到这一点,则可以选择DISTRIBUTED RANDOMLY,它将数据轮询发送到segment实例。

DISTRIBUTED REPLICATED将表中的所有行复制到所有Greenplum数据库segment。 它不能与分区表或从其他表继承的表一起使用。

如果在创建表时未指定DISTRIBUTED BY子句, 则Greenplum数据库服务器配置参数gp_create_table_random_default_distribution将控制默认表分发策略。 如果未指定分发策略,Greenplum数据库将遵循以下规则来创建表。

  • 如果Postgres查询优化器创建了表,并且该参数的值是off,则根据命令确定表分配策略。
  • 如果Postgres查询优化器创建了表,并且该参数的值为on,则表分配策略是随机的。
  • 如果GPORCA创建表,则表分配策略是随机的。参数值无效。

有关设置默认表分发策略的更多信息, 请参见gp_create_table_random_default_distribution。 有关Postgres查询优化器和GPORCA的信息, 请参阅Greenplum数据库管理员指南中的查询数据

注解

该命令在功能上类似于SELECT INTO,但它是首选的, 因为它不太可能与SELECT INTO语法的其他用法混淆。 此外,CREATE TABLE AS提供了SELECT INTO提供的功能的超集。

CREATE TABLE AS可用于从外部表数据源快速加载数据。 请参阅CREATE EXTERNAL TABLE

示例

创建一个新表films_recent,该表仅包含表films中的最新条目:

使用预编译语句创建一个新的临时表films_recent,该表仅包含表films中的最新条目。 新表具有OID,并将在提交时删除:

兼容性

CREATE TABLE AS符合SQL标准,但以下情况除外:

  • 该标准要求在子查询子句两边加上括号;在Greenplum数据库中,这些括号是可选的。
  • 该标准定义了WITH [NO] DATA子句;Greenplum数据库当前未实现此功能。 Greenplum数据库提供的行为等同于标准的WITH DATA情况。 可以通过将LIMIT 0附加到查询来模拟WITH NO DATA。
  • Greenplum数据库对临时表的处理方式与标准处理方式有所不同。 有关详细信息,请参见CREATE TABLE。
  • WITH子句是Greenplum数据库扩展。 storage参数和OIDs都不在标准中。
  • 表空间的Greenplum数据库概念不是该标准的一部分。 TABLESPACE子句是扩展。

另见

CREATE EXTERNAL TABLE, CREATE EXTERNAL TABLE, EXECUTE, SELECT, SELECT INTO, VALUES

gp_distribution_policy

gp_distribution_policy表包含有关Greenplum数据库表及其分布表数据的策略的信息。 该表仅在Master上填充。该表不是全局共享的,这意味着每个数据库都有自己的副本。

类型参考描述
localoidoidpg_class.oid表对象标识符(OID)。
policytypechar表分布策略:
p - 分区策略。表数据在segment实例中分布。
r - 复制策略。表数据在每个segment实例中冗余分布。
numsegmentsinteger有表数据分布的节点数量。
distkeyint2vectorpg_attribute.attnum分布列的列编号。
distclassoidvectorpg_opclass.oid分布列的运算符类标识符。

查看GreenPlum表的分布键和压缩类型

参考

https://www.bookstack.cn/read/greenplum-admin_guide-6.0-zh/46d688e7f1f60ed6.md
https://www.bookstack.cn/read/greenplum-admin_guide-6.0-zh/fef92e1686f67daa.md

https://www.kodyaz.com/greenplum/display-greenplum-database-table-distribution-key-data-distribution.aspx

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

3 × 3 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部