数据仓库服务 GAUSSDB(DWS)-CREATE TABLE PARTITION:参数说明
参数说明
- IF NOT EXISTS
如果已经存在相同名称的表,不会抛出一个错误,而会发出一个通知,告知表关系已存在。
- partition_table_name
分区表的名称。
取值范围:字符串,要符合标识符的命名规范。
- column_name
新表中要创建的字段名。
取值范围:字符串,要符合标识符的命名规范。
- data_type
字段的数据类型。
- COLLATE collation
COLLATE子句指定列的排序规则(该列必须是可排列的数据类型)。如果没有指定,则使用默认的排序规则。
可排列的数据类型有char、varchar、text、nchar、nvarchar。
- CONSTRAINT constraint_name
列约束或表约束的名字。可选的约束子句用于声明约束,新行或者更新的行必须满足这些约束才能成功插入或更新。
定义约束有两种方法:
- 列约束:作为一个列定义的一部分,仅影响该列。
- 表约束:不和某个列绑在一起,可以作用于多个列。
- LIKE source_table [ like_option ... ]
LIKE子句声明一个表,新表自动从声明的表中继承所有字段名及其数据类型和非空约束。
新表与原来的表之间在创建动作完毕之后是完全无关的。在源表做的任何修改都不会传播到新表中,并且也不可能在扫描源表的时候包含新表的数据。
字段缺省表达式只有在声明了INCLUDING DEFAULTS之后才会包含进来。缺省是不包含缺省表达式的,即新表中所有字段的缺省值都是NULL。
非空约束将总是复制到新表中,CHECK约束则仅在指定了INCLUDING CONSTRAINTS的时候才复制,而其他类型的约束则永远也不会被复制。此规则同时适用于表约束和列约束。
被复制的列和约束并不使用相同的名字进行融合。如果明确的指定了相同的名字或者在另外一个LIKE子句中,将会报错。
- 如果指定了INCLUDING INDEXES,则源表上的索引也将在新表上创建,默认不建立索引。
- 如果指定了INCLUDING STORAGE,则拷贝列的STORAGE设置也将被拷贝,默认情况下不包含STORAGE设置。
- 如果指定了INCLUDING COMMENTS,则源表列、约束和索引的注释也会被拷贝过来。默认情况下,不拷贝源表的注释。
- 如果指定了INCLUDING RELOPTIONS,则源表的存储参数(即源表的WITH子句)也将拷贝至新表。默认情况下,不拷贝源表的存储参数。
- 如果指定了INCLUDING DISTRIBUTION,则新表将拷贝源表的分布信息,包括分布类型和分布列,同时新表将不能再使用DISTRIBUTE BY子句。默认情况下,不拷贝源表的分布信息。
- INCLUDING ALL是INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS INCLUDING RELOPTIONS INCLUDING DISTRIBUTION的简写形式。
- WITH ( storage_parameter [= value] [, ... ] )
这个子句为表或索引指定一个可选的存储参数。参数的详细描述如下所示:
- FILLFACTOR
一个表的填充因子(fillfactor)是一个介于10和100之间的百分数。100(完全填充)是默认值。如果指定了较小的填充因子,INSERT操作仅按照填充因子指定的百分率填充表页。每个页上的剩余空间将用于在该页上更新行,这就使得UPDATE有机会在同一页上放置同一条记录的新版本,这比把新版本放置在其他页上更有效。对于一个从不更新的表将填充因子设为100是优良选择,但是对于频繁更新的表,选择较小的填充因子则更加合适。该参数对于列存表没有意义。
取值范围:10~100
- ORIENTATION
取值范围:
- COLUMN:表的数据将以列式存储。
- ROW(缺省值):表的数据将以行式存储。
- ORC:表的数据将以ORC格式存储(仅HDFS表)。
orientation不支持修改。
- COMPRESSION
- MAX_BATCHROW
指定了在数据加载过程中一个存储单元可以容纳记录的最大数目。该参数只对列存表有效。
取值范围:10000~60000
默认值:60000
- PARTIAL_CLUSTER_ROWS
指定了在数据加载过程中进行将局部聚簇存储的记录数目。该参数只对列存表有效。
取值范围:其有效值为大于等于10万。此值是MAX_BATCHROW的倍数。
- enable_delta
不推荐使用列存带Delta表,否则会出现由于来不及merge而导致的磁盘膨胀以及性能劣化等问题。
默认值:off
- DELTAROW_THRESHOLD
取值范围:0~60000,默认值为6000
- COLD_TABLESPACE
指定冷分区保存的obs tablespace,仅冷热表支持。该参数仅支持列存分区表,且该参数不支持修改,需与storage_policy同时使用。在指定STORAGE_POLICY时,可不设置该参数,默认为default_obs_tbs。
取值范围:有效的OBS TABLESPACE名。
- STORAGE_POLICY
指定冷热分区切换规则,仅冷热表支持。该参数需与cold_tablespace同时使用。
取值范围:"冷热切换策略名称:冷热切换的阈值",目前冷热切换的策略名称只支持LMT和HPN,LMT指按分区的最后更新时间切换,HPN指保留热分区的个数切换。
- LMT:[day]:表示切换[day]时间前修改的热分区数据为冷分区,将该数据迁至OBS表空间中。其中[day]为整型,范围[0, 36500],单位为天。
- HPN: [hot_partition_num]:表示保留[hot_partition_num]个有数据的分区为热分区。保留规则为查找出有数据的分区的最大的Sequence ID,大于Sequence ID的无数据分区为热分区,并按这个Sequence ID从大到小保留[hot_partition_num]个分区为热分区;分区Sequence ID小于保留的最小热分区的Sequence ID的分区为冷分区,在冷热切换时,需要将数据迁移至OBS表空间中。其中[hot_partition_num]为整型,范围为[0,1600]。
- 实时数仓(单机部署)暂不支持冷热分区切换功能。
- 对于LIST分区,建议谨慎使用HPN策略,否则可能出现新增分区不是热分区的情况。
- ENABLE_MULTI_TEMP_DISKCACHE
冷热表性能优化参数,仅支持冷热表设置,该参数仅9.1.0及以上版本支持。
取值范围:on/true,off/false
默认值:on/true
- PERIOD
指定分区管理中自动创建分区的周期,并开启自动创建分区功能。仅支持行存、列存范围分区表、时序表以及冷热表;分区键唯一并且类型仅支持TIMESTAMP[(p)] [WITHOUT TIME ZONE]、TIMESTAMP[(p)] [WITH TIME ZONE]、DATE;不支持存在maxvalue分区;(nowTime - boundaryTime) / PERIOD需要小于分区个数上限,其中nowTime为当前时间,boundaryTime为现有分区中最早的分区边界时间;不支持在小型机、加速集群、单机集群上使用。
取值范围:1 hour ~ 100 years
- 在兼容Teradata或MySQL的数据库中,分区键类型为DATE时,PERIOD不能小于1 day。
- 建分区表时,如果设置了PERIOD,则可以只指定分区键不指定分区。建表时将创建两个默认分区,这两个默认分区的分区时间范围均为PERIOD。其中,第一个默认分区的边界时间是大于当前时间的第一个整时/整天/整周/整月/整年的时间,具体选择哪种整点时间取决于PERIOD的最大单位;第二个默认分区的边界时间是第一个分区边界时间加PERIOD。假设当前时间是2022-02-17 16:32:45,各种情况的第一个默认分区的分区边界选择如表1。
有关默认分区的更多内容,请参见示例8。
- 实时数仓(单机部署)暂不支持自动创建分区功能。
- TTL
指定分区管理中分区过期的时间,并开启自动删除分区功能。不支持单独设置,必须要提前或同时设置PERIOD,并且要大于或等于PERIOD。
取值范围:1 hour ~ 100 years
- PERIOD指明按照时间划分的周期对数据进行分区,分区的大小可能对查询性能有影响,同时每隔周期时间会创建一个新的周期大小的分区,具体做法是以period周期,自动调用proc_add_partition (relname regclass, boundaries_interval interval)函数。TTL(Time To Live)指明该表的数据保存周期,超过TTL周期的数据将被清理,具体做法是以period周期,自动调用proc_drop_partition (relname regclass, older_than interval)函数。PERIOD和TTL的值为Interval类型,例如:“1 hour”, “1 day”, “1 week”, “1 month” ,“1 year”, “1 month 2 day 3 hour”。
- 实时数仓(单机部署)暂不支持自动删除分区功能。
- COLVERSION
指定列存存储格式的版本,支持不同存储格式版本之间的切换,但分区表不支持存储格式版本切换。
取值范围:
1.0:列存表的每列以一个单独的文件进行存储,文件名以relfilenode.C1.0、relfilenode.C2.0、relfilenode.C3.0等命名。
2.0:列存表的每列合并存储在一个文件中,文件名以relfilenode.C1.0命名。
默认值:2.0
需注意,OBS冷热表仅支持colversion 2.0格式。
在建列存表时选择COLVERSION=2.0,相比于1.0存储格式,在以下场景中性能有明显提升:
- 创建列存宽表场景下,建表时间显著减少。
- roach备份数据场景下,备份时间显著减少。
- build、catch up耗时显著减少。
- 占用磁盘空间大小显著减少。
- FILLFACTOR
- COMPRESS / NOCOMPRESS
创建一个新表时,需要在创建表语句中指定关键字COMPRESS,这样,当对该表进行批量插入时就会触发压缩特性。该特性会在页范围内扫描所有元组数据,生成字典、压缩元组数据并进行存储。指定关键字NOCOMPRESS则不对表进行压缩。
缺省值为NOCOMPRESS,即不对元组数据进行压缩。
- DISTRIBUTE BY
指定表如何在节点之间分布或者复制。
取值范围:
- REPLICATION:表的每一行存在所有数据节点( DN )中,即每个数据节点都有完整的表数据。
- ROUNDROBIN:表的每一行被轮番地发送给各个DN,因此数据会被均匀地分布在各个DN中。(ROUNDROBIN仅8.1.2及以上版本支持)
- HASH (column_name ) :对指定的列进行Hash,通过映射,把数据分布到指定DN。
- 当指定DISTRIBUTE BY HASH (column_name)参数时,创建主键和唯一索引必须包含“ column_name”列。
- 当被参照表指定DISTRIBUTE BY HASH (column_name)参数时,参照表的外键必须包含“ column_name”列。
默认值:由GUC参数default_distribution_mode控制。- 当default_distribution_mode=roundrobin时,DISTRIBUTE BY的默认值按如下规则选取:
- 若建表时包含主键/唯一约束,则选取HASH分布,分布列为主键/唯一约束对应的列。
- 若建表时不包含主键/唯一约束,则选取ROUNDROBIN分布。
- 当default_distribution_mode=hash时,DISTRIBUTE BY的默认值按如下规则选取:
- 若建表时包含主键/唯一约束,则选取HASH分布,分布列为主键/唯一约束对应的列。
- 若建表时不包含主键/唯一约束,但存在数据类型支持作分布列的列,则选取HASH分布,分布列为第一个数据类型支持作分布列的列。
- 若建表时不包含主键/唯一约束,也不存在数据类型支持作分布列的列,选取ROUNDROBIN分布。
以下数据类型支持作为分布列:- INTEGER TYPES:TINYINT,SMALLINT,INT,BIGINT,NUMERIC/DECIMAL
- CHARACTER TYPES:CHAR,BPCHAR,VARCHAR,VARCHAR2,NVARCHAR2,TEXT
- DATE/TIME TYPES:DATE,TIME,TIMETZ,TIMESTAMP,TIMESTAMPTZ,INTERVAL,SMALLDATETIME
- TO { GROUP groupname | NODE ( nodename [, ... ] ) }
TO GROUP指定创建表所在的Node Group,目前不支持hdfs表使用。TO NODE主要供内部扩容工具使用,一般用户不应该使用。
- PARTITION BY RANGE(partition_key)
指定范围分区策略语法,partition_key为分区键的名称。
(1)对于从句是VALUES LESS THAN的语法格式:
对于从句是VALUE LESS THAN的语法格式,范围分区策略的分区键最多支持4列,且分区键只能是列名。当存在多个分区键时,一个列名只能出现一次,且相邻的两个分区键要使用逗号隔开。
该情形下,分区键支持的数据类型为:SMALLINT、INTEGER、BIGINT、DECIMAL、NUMERIC、REAL、DOUBLE PRECISION、CHARACTER VARYING(n)、VARCHAR(n)、CHARACTER(n)、CHAR(n)、CHARACTER、CHAR、TEXT、NVARCHAR2、NAME、TIMESTAMP[(p)] [WITHOUT TIME ZONE]、TIMESTAMP[(p)] [WITH TIME ZONE]、DATE。
(2)对于从句是START END的语法格式:
对于从句是START END的语法格式,范围分区策略的分区键仅支持1列。
该情形下,分区键支持的数据类型为:SMALLINT、INTEGER、BIGINT、DECIMAL、NUMERIC、REAL、DOUBLE PRECISION、TIMESTAMP[(p)] [WITHOUT TIME ZONE]、TIMESTAMP[(p)] [WITH TIME ZONE]、DATE。
- PARTITION BY LIST (partition_key,[...])
指定列表分区策略语法,partition_key为分区键的名称。
列表分区策略的分区键最多支持4列。
列表分区策略分区键支持的数据类型为:TINYINT、SMALLINT、INTEGER、BIGINT、NUMERIC/DECIMAL、TEXT、NVARCHAR2、VARCHAR(n)、CHAR、BPCHAR、TIME、TIME WITH TIMEZONE、TIMESTAMP、TIMESTAMP WITH TIME ZONE、DATE、INTERVAL、SMALLDATETIME
- partition_less_than_item
1
PARTITION partition_name VALUES LESS THAN ( { partition_value | DEFAULT } )
范围分区策略下分区(简称为范围分区)的定义语法。partition_name为范围分区的名称。partition_value为范围分区的上边界,取值依赖于partition_key的类型。MAXVALUE表示分区的上边界,它通常用于设置最后一个范围分区的上边界。
- 每个分区都需要指定一个上边界。
- 分区上边界的类型应当和分区键的类型一致。
- 分区列表是按照分区上边界升序排列的,值较小的分区位于值较大的分区之前。
- 如果分区键由多个字段组成,比较大小时,先比较第一个字段,当第一个字段相等时比较第二个字段,以此类推。
- partition_start_end_item
1 2 3 4
PARTITION partition_name {START (partition_value) END (partition_value) EVERY (interval_value)} | {START (partition_value) END (partition_value|MAXVALUE)} | {START(partition_value)} | {END (partition_value| MAXVALUE)}
使用起始值以及间隔值定义范围分区的语法,各参数含义如下:
- partition_name:范围分区的名称或名称前缀,除以下情形外(假定其中的partition_name是p1),均为分区的名称。
- 若该定义是START+END+EVERY从句,则语义上定义的分区的名称依次为p1_1, p1_2, ...。例如对于定义“PARTITION p1 START(1) END(4) EVERY(1)”,则生成的分区是:[1, 2), [2, 3) 和 [3, 4),名称依次为p1_1, p1_2和p1_3,即此处的p1是名称前缀。
- 若该定义是第一个分区定义,且该定义有START值,则范围(MINVALUE, START)将自动作为第一个实际分区,其名称为p1_0,然后该定义语义描述的分区名称依次为p1_1, p1_2, ...。例如对于完整定义“PARTITION p1 START(1), PARTITION p2 START(2)”,则生成的分区是:(MINVALUE, 1), [1, 2) 和 [2, MAXVALUE),其名称依次为p1_0, p1_1和p2,即此处p1是名称前缀,p2是分区名称。这里MINVALUE表示最小值。
- partition_value:范围分区的端点值(起始或终点),取值依赖于partition_key的类型,不可是MAXVALUE。
- interval_value:对[START,END) 表示的范围进行切分,interval_value是指定切分后每个分区的宽度,不可是MAXVALUE;如果(END-START)值不能整除以EVERY值,则仅最后一个分区的宽度小于EVERY值。
- MAXVALUE:表示最大值,它通常用于设置最后一个范围分区的上边界。
- 在创建分区表若第一个分区定义含START值,则范围(MINVALUE,START)将自动作为实际的第一个分区。
- START END语法需要遵循以下限制:
- 每个partition_start_end_item中的START值(如果有的话,下同)必须小于其END值;
- 相邻的两个partition_start_end_item,第一个的END值必须等于第二个的START值;
- 每个partition_start_end_item中的EVERY值必须是正向递增的,且必须小于(END-START)值;
- 每个分区包含起始值,不包含终点值,即形如:[起始值,终点值),起始值是MINVALUE时则不包含;
- 一个partition_start_end_item创建的每个分区所属的TABLESPACE一样;
- partition_name作为分区名称前缀时,其长度不要超过57字节,超过时自动截断;
- 在创建、修改分区表时请注意分区表的分区总数不可超过最大限制(32767);
- 在创建分区表时START END与LESS THAN语法不可混合使用。
- 即使创建分区表时使用START END语法,备份(gs_dump)出的SQL语句也是VALUES LESS THAN语法格式。
- partition_name:范围分区的名称或名称前缀,除以下情形外(假定其中的partition_name是p1),均为分区的名称。
- list_partition_item
1
PARTITION partition_name VALUES ( { (partition_value) [, ... ] | DEFAULT } )
列表分区策略下分区(简称为列表分区)的定义语法。partition_name为分区的名称。partition_value为列表分区边界的一个枚举值,取值依赖于partition_key的类型。DEFAULT表示默认分区的边界。
对于列表分区表,存在以下约定和约束:
- 边界值为DEFAULT的分区,称之为默认分区。
- 每个列表分区表只能有一个DEFAULT分区。
- 分区表的所有分区数不超过32767个,所有分区的边界值个数不大于32767个。
- 不管分区键的个数,DEFAULT分区的边界只能是一个DEFAULT。
- 如果分区键由多个字段组成,每个partition_value需要包含所有分区键的值,当分区键只有一列时,partition_value两侧的括号可以省略,参见示例4:创建列表分区。
- 如果分区键由多个字段组成,比较大小时,先逐个字段比较大小,任何一个字段值不一样即可认为是不一样的键值。
- 边界中不同的partition_value值不能重复。
- 数据插入时,如果数据的分区键值能匹配任何非DEFAULT分区的边界,那么数据会写入对应的分区;否则数据会写入DEFAULT分区。
- { ENABLE | DISABLE } ROW MOVEMENT
行迁移开关。
如果进行UPDATE操作时,更新了元组在分区键上的值,造成了该元组所在分区发生变化,就会根据该开关给出报错信息,或者进行元组在分区间的转移。
取值范围:
- ENABLE:行迁移开关打开。
- DISABLE(缺省值):行迁移开关关闭。
- GaussDB(DWS)常用SQL_常用SQL命令_SQL语法
- 数据仓库服务GaussDB(DWS)_SQL on Anywhere
- DWS安全_数据仓库服务安全_DWS数据安全管理_DWS安全保障_DWS安全策略
- 调用GaussDB(DWS) API接口_数据仓库服务调用API_如何调用API_在DWS中调用API
- 云数据库Gaussdb数据库索引_华为gaussdb__gaussdb 数据库
- DWS资源管理_GaussDB(DWS)资源管理作用_DWS资源管控
- GaussDB行转列_数据中台架构pdf_高斯数据库行转列_华为云
- 什么是Flink OpenSource SQL_数据湖探索_Flink OpenSource SQL
- GaussDB版本查询_华为云GaussDB的作用_高斯数据库版本查询_华为云
- GAUSS(DWS)工具_gsql工具_DataStudio工具_DSC工具