CREATE TABLE
功能描述
在当前数据库中创建一个新的空白HStore表，该表由命令执行者所有。
实时数仓提供创建HStore表DDL语句。创建HStore表DDL需要指定enable_hstore为true, 同时需要将orientation属性设置为column。
- 如需使用实时数仓能力，请在DWS控制台创建集群时，选择存算一体1:4云盘规格，了解更多参见存算一体规格。
- 创建DWS集群选择1:8云盘规格为标准数仓，1:4云盘规格为实时数仓，以下内容描述的“实时数仓”、“标准数仓”即通过以上规格区分。
注意事项
- 创建HStore表时，必须确保数据库GUC参数设置满足以下条件：
- autovacuum设置为on。
- autovacuum_max_workers_hstore，autovacuum_max_workers，autovacuum_max_workers_col取值大于0。
- 创建HStore表的用户需要拥有schema cstore的USAGE权限。
- 表级参数enable_delta与enable_hstore无法同时开启，因为enable_delta用于控制普通列存表的delta开启，会与enable_hstore冲突。
- 每一个HStore表绑定一张delta表，delta表的oid记录在pg_class中reldeltaidx字段（reldelta字段被列存表的delta表使用）。
语法格式
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE [ IF NOT EXISTS ] table_name ({ column_name data_type | LIKE source_table [like_option [...] ] } } [, ... ]) [ WITH ( {storage_parameter = value} [, ... ] ) ] [ TABLESPACE tablespace_name ] [ DISTRIBUTE BY HASH ( column_name [,...])] [ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ] [ PARTITION BY { {RANGE (partition_key) ( partition_less_than_item [, ... ] )} } [ { ENABLE | DISABLE } ROW MOVEMENT ] ]; 其中like选项like_option为： { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | PARTITION | RELOPTIONS | DISTRIBUTION | ALL } |
列存表的Delta表差异
|
数仓类型 |
列存的delta表 |
HStore的delta表 |
HStore Opt的delta表 |
|---|---|---|---|
|
表结构 |
与列存主表的表定义一致。 |
与主表定义不一致。 |
不同于主表，与HStore表相同。 |
|
功能 |
用于暂存小批量insert的数据，满阈值后再merge到主表，避免直接insert到主表产生大量小CU。 |
用于持久化存储update/delete/insert信息。在产生故障后用于恢复内存更新链等管理并发更新的内存结构。 |
用于持久化存储update/delete/insert信息。在产生故障后用于恢复内存更新链等管理并发更新的内存结构。相比HStore进一步优化。 |
|
缺陷 |
来不及merge导致delta表膨胀，影响查询性能，同时无法解决并发update的锁冲突问题 |
依赖后台常驻autovacuum来做merge操作。 |
依赖后台常驻autovacuum来做merge操作。 |
|
规格差异 |
不支持同一CU的并发，适合并发更新较少的情况。 |
|
|
|
入库建议 |
|
||
|
点查建议 |
|
||
参数说明
- IF NOT EXISTS
指定IF NOT EXISTS时，若不存在同名表，则可以成功创建表。若已存在同名表，创建时不会报错，仅会提示该表已存在并跳过创建。
- table_name
要创建的表名。
表名长度不超过63个字符，以字母或下划线开头，可包含字母、数字、下划线、$、#。
- column_name
新表中要创建的字段名。
字段名长度不超过63个字符，以字母或下划线开头，可包含字母、数字、下划线、$、#。
- data_type
字段的数据类型。
- LIKE source_table [ like_option ... ]
LIKE子句声明一个表，新表自动从这个表中继承所有字段名及其数据类型。
新表与原表之间在创建动作完毕之后是完全无关的。在原表做的任何修改都不会传播到新表中，并且也不可能在扫描原表的时候包含新表的数据。
被复制的列并不使用相同的名字进行融合。如果明确的指定了相同的名字或者在另外一个LIKE子句中，将会报错。
HStore表只能从HStore表中进行继承。
- WITH ( { storage_parameter = value } [, ... ] )
这个子句为表指定一个可选的存储参数。
- ORIENTATION
指定表数据的存储方式，即时序方式、行存方式、列存方式，该参数设置成功后就不再支持修改。对于HStore表，应当使用列存方式，同时设置enable_hstore为on。
取值范围：
- TIMESERIES，表示表的数据将以时序方式存储。
- COLUMN，表示表的数据将以列存方式存储。
- ROW，表示表的数据将以行方式存储。
默认值：ROW。
- COMPRESSION
指定表数据的压缩级别，它决定了表数据的压缩比以及压缩时间。一般来讲，压缩级别越高，压缩比越大，压缩时间也越长；反之亦然。实际压缩比取决于加载的表数据的分布特征。
取值范围：HStore表和列存表的有效值为LOW、MIDDLE或HIGH。默认值为LOW。
暂不支持行存表压缩。
- COMPRESSLEVEL
指定表数据同一压缩级别下的不同压缩水平，它决定了同一压缩级别下表数据的压缩比以及压缩时间。对同一压缩级别进行了更加详细的划分，为用户选择压缩比和压缩时间提供了更多的空间。总体来讲，此值越大，表示同一压缩级别下压缩比越大，压缩时间越长；反之亦然。该参数只对时序表和列存表有效。
取值范围：0~3
默认值：0
- MAX_BATCHROW
指定了在数据加载过程中一个存储单元可以容纳记录的最大数目。该参数只对时序表和列存表有效。
取值范围：10000~60000
默认值：60000
- PARTIAL_CLUSTER_ROWS
指定了在数据加载过程中进行将局部聚簇存储的记录数目。该参数只对时序表和列存表有效。
取值范围：600000~2147483647
默认值：4,200,000
- enable_delta
指定了在列存表是否开启delta表。对HStore表不能开启该参数。
默认值：off
- enable_hstore
指定了是否创建为HStore表（基于列存表实现）。该参数只对列存表有效。该参数仅8.2.0.100及以上集群版本支持。
默认值：off
打开该参数时必须设置以下GUC参数用于保证HStore表的清理，推荐值如下：
autovacuum=on，autovacuum_max_workers=6，autovacuum_max_workers_hstore=3。
- enable_hstore_opt
enable_hstore_opt表级参数打开时会默认同时打开enable_hstore表级参数，该参数仅8.3.0及以上集群版本支持。
默认值：false
- enable_disaster_cstore
指定了列存表是否开启细粒度容灾功能。该参数仅适用于COLVERSION为2.0的普通列存表，不支持hstore表、行存表、临时表、unlog表、外表、时序表、冷热表，物化视图以及开启delta表功能，并且不能和enable_hstore，enable_delta同时打开。
默认值：off
- SUB_PARTITION_COUNT
指定二级分区的个数。该参数用于设置在导入阶段二级分区个数。在建表时进行设置，建表后不支持修改。不建议用户随意设置该默认值，可能会影响导入和查询的性能。
取值范围：1～1024
默认值：32
- DELTAROW_THRESHOLD
指定HStore表导入时小于多少行(SUB_PARTITION_COUNT * DELTAROW_THRESHOLD)的数据进入delta表。
取值范围：0～60000
默认值：60000
- COLVERSION
指定存储格式的版本。 HStore表和enable_hstore_opt表只支持2.0版本。
取值范围：
1.0：列存表的每列以一个单独的文件进行存储，文件名以relfilenode.C1.0、relfilenode.C2.0、relfilenode.C3.0等命名。
2.0：列存表的每列合并存储在一个文件中，文件名以relfilenode.C1.0命名。
默认值：2.0
- enable_binlog
用于控制hstore表是否开启binlog功能。该参数仅9.1.0及以上集群版本支持。
取值范围：on/off
默认值：off
- enable_binlog_timestamp
用于控制hstore表是否开启带时间戳的binlog功能。该参数与enable_binlog无法同时打开，且仅9.1.0.200及以上集群版本支持。
取值范围：on/off
默认值：off
- enable_light_update
指定Hstore opt表是否开启轻量化更新。该参数仅9.1.1.100及以上集群版本支持。
取值范围：on/off
默认值：off
- 仅支持Hstore opt表开启此参数。
- 该参数不支持ALTER操作，仅支持建表时指定。
- 不支持REPLICATION分布表开启该参数。
- 建议创建轻量化更新表时指定主键。
- 建表时若不指定PCK列，会默认使用主键列（若存在）第一列作为PCK列。
- 创建PCK约束时表必须包含主键，且PCK列必须为主键列。
- 轻量化update的表只支持创建一个索引，该索引只能为主键或唯一索引。
- storage_mode
指定Hstore opt表建立行列混存表的方式。该参数仅9.1.1.100及以上版本支持。
取值范围：col/row/mix
默认值：不指定，仍为hstore opt表
- light update，binlog和大宽表不能和行列混存表共同开启，不支持物化视图。
- col模式：行列混存的列模式，同hstore opt相同。
- row模式：行列混存的行模式，支持压缩，该模式为全新的行模式，与行存表不同。
- mix模式：同时存储col模式和row模式两份数据。
- DISTRIBUTE BY
取值范围：
HASH (column_name ) ：对指定的列进行Hash，通过映射，把数据分布到指定DN。
- TO { GROUP groupname | NODE ( nodename [, ... ] ) }
TO GROUP指定创建表所在的Node Group，目前不支持hdfs表使用。TO NODE主要供内部扩容工具使用，一般用户不应该使用。
- PARTITION BY
- secondary_part_column
指定列存表二级分区列的列名，仅能指定一列作为二级分区列且只适用于HStore列存表。该参数仅8.3.0及以上集群版本支持。
- 被指定为二级分区的列不能被删除或者修改。
- 只能在建表时指定二级分区列，建表后不支持修改。
- 不建议指定分布列作为二级分区列。
- 指定二级分区列后，该表将会在DN上按照二级分区列进行逻辑上的HASH分区存储，从而有效提升该列等值查询的性能。
- secondary_part_num
指定列存表二级分区的数量，仅适用于HStore列存表。该参数仅8.3.0及以上集群版本支持。
取值范围：1~32
默认值：8
- 只有secondary_part_column被指定时，该参数才可以指定。
- 只能在建表时指定二级分区的数量，建表后不支持修改。
- 不建议用户随意设置该默认值，否则会影响导入和查询的性能。
- bitmap_columns
bitmap index只适用于新版hstore（hstore_opt表），只有开启表级参数enable_hstore_opt且开启bitmap_columns="指定列"，才能生成bitmap index映射关系。该参数仅8.3.0及以上集群版本支持。
- ORIENTATION
示例
创建简单的HStore表:
CREATE TABLE warehouse_t1
(
W_WAREHOUSE_SK INTEGER NOT NULL,
W_WAREHOUSE_ID CHAR(16) NOT NULL,
W_WAREHOUSE_NAME VARCHAR(20) ,
W_WAREHOUSE_SQ_FT INTEGER ,
W_STREET_NUMBER CHAR(10) ,
W_STREET_NAME VARCHAR(60) ,
W_STREET_TYPE CHAR(15) ,
W_SUITE_NUMBER CHAR(10) ,
W_CITY VARCHAR(60) ,
W_COUNTY VARCHAR(30) ,
W_STATE CHAR(2) ,
W_ZIP CHAR(10) ,
W_COUNTRY VARCHAR(20) ,
W_GMT_OFFSET DECIMAL(5,2)
)WITH(ORIENTATION=COLUMN, ENABLE_HSTORE=ON);
CREATE TABLE warehouse_t2 (LIKE warehouse_t1 INCLUDING ALL);
