CREATE TABLE

功能描述

在当前数据库中创建一个新的空白HStore表，该表由命令执行者所有。

实时数仓提供创建HStore表DDL语句。创建HStore表DDL需要指定enable_hstore为true, 同时需要将orientation属性设置为column。

注意事项

• 创建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表差异

参数说明

• 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表和列存表的有效值为YES/NO和/LOW/MIDDLE/HIGH，默认值为LOW。
    • 行存表的有效值为YES/NO，默认值为NO。
  • COMPRESSLEVEL

    指定表数据同一压缩级别下的不同压缩水平，它决定了同一压缩级别下表数据的压缩比以及压缩时间。对同一压缩级别进行了更加详细的划分，为用户选择压缩比和压缩时间提供了更多的空间。总体来讲，此值越大，表示同一压缩级别下压缩比越大，压缩时间越长；反之亦然。该参数只对时序表和列存表有效。

    取值范围：0~3

    默认值：0

  • MAX_BATCHROW

    指定了在数据加载过程中一个存储单元可以容纳记录的最大数目。该参数只对时序表和列存表有效。

    取值范围：10000~60000

    默认值60000

  • PARTIAL_CLUSTER_ROWS

    指定了在数据加载过程中进行将局部聚簇存储的记录数目。该参数只对时序表和列存表有效。

    取值范围：600000~2147483647

  • enable_delta

    指定了在列存表是否开启delta表。对HStore表不能开启该参数。

    默认值：off

  • enable_hstore

    指定了是否创建为HStore表（基于列存表实现）。该参数只对列存表有效。该参数仅8.2.0.100及以上集群版本支持。

    默认值：off

    

    打开该参数时必须设置以下GUC参数用于保证HStore表的清理，推荐值如下：

    autovacuum=true，autovacuum_max_workers=6，autovacuum_max_workers_hstore=3。

  • enable_disaster_cstore

    指定了列存表是否开启细粒度容灾功能。该参数仅适用于COLVERSION为2.0的列存表，并且不能和enable_hstore同时打开。该参数仅8.2.0.100及以上集群版本支持。

    默认值：off

    

    设置该参数为on前需要先设置GUC参数enable_metadata_tracking=on，否则可能开启细粒度容灾功能失败。

  • SUB_PARTITION_COUNT

    指定二级分区的个数。该参数用于设置在导入阶段二级分区个数。在建表时进行设置，建表后不支持修改。不建议用户随意设置该默认值，可能会影响导入和查询的性能。

    取值范围：1～1024

    默认值：32

  • DELTAROW_THRESHOLD

    指定HStore表导入时小于多少行(SUB_PARTITION_COUNT * DELTAROW_THRESHOLD)的数据进入delta表。

    取值范围：0～60000

    默认值为60000

  • COLVERSION

    指定存储格式的版本。 HStore表只支持2.0版本，enable_hstore_opt表支持2.0和3.0版本。

    取值范围：

    1.0：列存表的每列以一个单独的文件进行存储，文件名以relfilenode.C1.0、relfilenode.C2.0、relfilenode.C3.0等命名。

    2.0：列存表的每列合并存储在一个文件中，文件名以relfilenode.C1.0命名。

    默认值：2.0

  • DISTRIBUTE BY

    指定表如何在节点之间分布或者复制。

    取值范围：

    HASH (column_name ) ：对指定的列进行Hash，通过映射，把数据分布到指定DN。

  • TO { GROUP groupname | NODE ( nodename [, ... ] ) }

    TO GROUP指定创建表所在的Node Group，目前不支持hdfs表使用。TO NODE主要供内部扩容工具使用，一般用户不应该使用。

  • PARTITION BY

    指定HStore表的初始分区。

  • secondary_part_column

    指定列存表二级分区列的列名，仅能指定一列作为二级分区列且只适用于HStore列存表。该参数仅8.3.0及以上集群版本支持，其中v3表不支持该参数，会走hashbucket剪枝。

    

    • 被指定为二级分区的列不能被删除或者修改。
    • 只能在建表时指定二级分区列，建表后不支持修改。
    • 不建议指定分布列作为二级分区列。
    • 指定二级分区列后，该表将会在DN上按照二级分区列进行逻辑上的HASH分区存储，从而有效提升该列等值查询的性能。
  • secondary_part_num

    指定列存表二级分区的数量，仅适用于HStore列存表。该参数仅8.3.0及以上集群版本支持，v3表不支持该参数，会走hashbucket剪枝。

    取值范围：1~32

    默认值：8

    

    • 只有secondary_part_column被指定时，该参数才可以指定。
    • 只能在建表时指定二级分区的数量，建表后不支持修改。
    • 不建议用户随意设置该默认值，否则会影响导入和查询的性能。

示例

创建简单的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);