# CREATE TABLE #### 功能描述 在当前数据库中创建一个新的空白HStore表，该表由命令执行者所有。 实时数仓提供创建HStore表DDL语句。创建HStore表DDL需要指定enable_hstore为on，同时需要将orientation属性设置为column。 9.1.0及以上版本对HStore表做了优化，为保持前向兼容，保留了老的HStore表，优化后的HStore表为HStore_opt表。除了微批copy无更新入库性能要求高的场景外，HStore表的场景都可以使用HStore_opt表代替，性能更优。 HStore表一旦创建好，无法直接转换为HStore_opt表，若要使用HStore_opt表，需要重新建表。 ![](https://support.huaweicloud.com/devg-dws/public_sys-resources/note_3.0-zh-cn.png) - 如需使用实时数仓能力，请在DWS控制台创建集群时，选择**存算一体1:4云盘规格** ，了解更多参见[存算一体规格](https://support.huaweicloud.com/productdesc-dws/dws_01_00018.html)。 - 创建DWS集群选择**1:8云盘规格** 为标准数仓，**1:4云盘规格**为实时数仓，以下内容描述的"实时数仓"、"标准数仓"即通过以上规格区分。 #### 注意事项 - 创建HStore表时，必须确保数据库GUC参数设置满足以下条件： - autovacuum设置为on。 - autovacuum_max_workers_hstore取值大于0。 - autovacuum_max_workers取值大于autovacuum_max_workers_hstore的取值。 - 创建HStore表的用户需要拥有schema cstore的USAGE权限。 - 表级参数enable_delta与enable_hstore_opt无法同时开启，因为enable_delta用于控制普通列存表的delta开启，会与enable_hstore_opt冲突。 - 每一个HStore表绑定一张delta表，delta表的oid记录在pg_class中reldeltaidx字段（reldelta字段被列存表的delta表使用）。 #### 语法格式 ``` 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表差异 表1HStore表与列存表的辅助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的并发，适合并发更新较少的情况。 | 1. 插入和更新限制 - MERGE INTO操作不支持并发更新同一行或对同一键进行重复更新。 - 不支持并发update或delete同一行，会触发报错。 2. 索引和查询限制 - 索引不支持数组条件过滤、IN表达式过滤、部分索引或表达式索引。 - 索引不支持失效处理。 3. 表结构和操作限制 - 在进行分区交换或relfilenode操作时，确保交换的表都是HStore表。 - 不支持update修改分布列，不建议update修改分区列（不报错，但性能非常差）。 | 1. 插入和更新限制 - MERGE INTO操作不支持并发更新同一行或对同一键进行重复更新。 - 不支持并发update或delete同一行，会触发报错。 - hstore_opt不支持跨分区upsert更新。 2. 索引和查询限制 - 支持bitmap索引。 - 支持全局字典。 - bitmap_columns必须在建表时确定，一旦设置后不允许修改。 - opt版本不支持SMP streaming透传参数。在需要分区剪枝的多表关联查询中，避免使用复制表或设置query_dop。 3. 表结构和操作限制 - 不支持update修改分布列，不支持update修改分区列。 - enable_hstore_opt属性需在创建表时设定，且不可更改。 | | 入库建议 | 1. 综合入库、查询和空间情况，建议选择hstore_opt表，微批copy无更新入库场景性能要求高的情况下选择hstore表。 2. HStore/HStore Opt共同点： - update入库性能差，建议修改为upsert。 - delete入库，确定计划走索引扫描即可，用JDBC batch方式入库最佳。 - merge into入库建议在单次入库数据量超过100W/dn，且无并发数据保证无重复的情况下使用。 - 尽量避免对冷分区的数据进行修改和新增。 ||| | 点查建议 | 1. 综合点查场景，建议使用HStore Opt表。 2. HStore/HStore Opt共同点： 在等值过滤条件使用最多且distinct值分布相对均匀的一个列上创建二级分区。 3. HStore的点查建议： - 使用主键之外的索引加速不能得到预期效果，不建议开启。 - 如果数据类型多为numeric或者16字节以内的字符串，建议使用turbo加速。 4. HStore Opt的点查建议： - 除了二级分区之外的等值过滤列，如果过滤条件涉及的列在查询中基本固定，使用cbtree索引，如果不断变化建议使用GIN索引，注意创建索引的列数不要超过5列。 - 所有涉及等值过滤的字符串列，都可以建表时指定bitmap索引，不限列数，但后续不可修改。 - 时间范围过滤的列，指定为分区列。 - 点查返回数据量超过10W/dn的场景，索引扫描的性能提升有限，建议针对这种情况使用guc参数enable_seqscan对比测试，灵活选择。 ||| #### 参数说明 - **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 ![](https://support.huaweicloud.com/devg-dws/public_sys-resources/note_3.0-zh-cn.png) 打开该参数时必须设置以下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 ![](https://support.huaweicloud.com/devg-dws/public_sys-resources/caution_3.0-zh-cn.png) 设置该参数为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 - enable_binlog 用于控制hstore opt表是否开启binlog功能。该参数仅9.1.0及以上集群版本支持。 取值范围：on/off 默认值：off - enable_binlog_timestamp 用于控制hstore opt表是否开启带时间戳的binlog功能。该参数与enable_binlog无法同时打开，且仅9.1.0.200及以上集群版本支持。 取值范围：on/off 默认值：off - 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 opt列存表。该参数仅9.1.0及以上集群版本支持，其中v3表不支持该参数，会走hashbucket剪枝。 ![](https://support.huaweicloud.com/devg-dws/public_sys-resources/note_3.0-zh-cn.png) - 被指定为二级分区的列不能被删除或者修改。 - 只能在建表时指定二级分区列，建表后不支持修改。 - 不建议指定分布列作为二级分区列。 - 指定二级分区列后，该表将会在DN上按照二级分区列进行逻辑上的HASH分区存储，从而有效提升该列等值查询的性能。 - secondary_part_num 指定列存表二级分区的数量，仅适用于HStore opt列存表。该参数仅9.1.0及以上集群版本支持，v3表不支持该参数，会走hashbucket剪枝。 取值范围：1\~32 默认值：8 ![](https://support.huaweicloud.com/devg-dws/public_sys-resources/note_3.0-zh-cn.png) - 只有secondary_part_column被指定时，该参数才可以指定。 - 只能在建表时指定二级分区的数量，建表后不支持修改。 - 不建议用户随意设置该默认值，否则会影响导入和查询的性能。 #### 示例 创建简单的HStore_opt表: ``` 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_OPT=ON); CREATE TABLE warehouse_t2 (LIKE warehouse_t1 INCLUDING ALL); ```