# CREATE TABLE AS #### 功能描述 根据查询结果创建表。适用于数据迁移或复制场景。 CREATE TABLE AS创建一个表，并使用SELECT查询结果填充该表。该表的字段与SELECT输出字段的名称及数据类型关联。不过用户可以通过明确地给出一个字段名称列表来覆盖SELECT输出字段的名称。 CREATE TABLE AS对源表进行一次查询，然后将数据写入新表中，而查询视图结果会根据源表的变化而有所改变。相比之下，每次做查询的时候，视图都重新计算定义它的SELECT语句。 #### 注意事项 - 分区表不能采用此方式进行创建。 - 如果在建表过程中数据库系统发生故障，系统恢复后可能无法自动清除之前已创建的、大小非0的磁盘文件。此种情况出现概率小，不影响数据库系统的正常运行。 - UNLOGGED表无主备机制，在系统故障或异常断点等情况下，会有数据丢失风险，不可用来存储基础数据。 - UNLOGGED表不能保证数据的安全性，用户应该在确保数据已经做好备份的前提下使用，例如系统升级时进行数据的备份。 #### 语法格式 ``` CREATE [ [ GLOBAL | LOCAL | VOLATILE ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE table_name [ (column_name [, ...] ) ] [ WITH ( {storage_parameter = value} [, ... ] ) ] [ COMPRESS | NOCOMPRESS ] [ DISTRIBUTE BY { REPLICATION | ROUNDROBIN | { [HASH ] ( column_name ) } } ] [ COMMENT [=] 'text' ] AS query [ WITH [ NO ] DATA ]; ``` #### 参数说明 表1CREATE TABLE AS参数说明 | 参数 | 描述 | 取值范围 | |:---|:---|:---| | GLOBAL \| LOCAL \| VOLATILE | 指定临时表类型。 | 具体参考[参数说明](https://support.huaweicloud.com/sqlreference-dws/dws_06_0177.html#ZH-CN_TOPIC_0000001764675138__s1a0c1ce222bb46dfb68a016610aea3a5)。 | | TEMPORARY \| TEMP | 指定TEMP或TEMPORARY关键字时创建的表为临时表。 | - | | UNLOGGED | 指定表为非日志表。在非日志表中写入的数据不会被写入到预写日志中，这样就会比普通表快很多。但是，它也是不安全的，非日志表在冲突或异常关机后会被自动删截。非日志表中的内容也不会被复制到备用服务器中。在该类表中创建的索引也不会被自动记录。 当异常关机等操作导致非日志表上的索引发生数据丢失时，用户应该对发生错误的索引进行重建。 | 自9.1.0版本，UNLOGGED表默认存储在pg_unlogged表空间下，且不可迁移或指定到其他表空间；从低版本升级到9.1.0版本后，旧版本创建的UNLOGGED表还存储在原表空间下。 由于实例异常重启时会对UNLOGGED表进行重置，所以可能影响实例RTO。自9.1.0版本提供UNLOGGED表迁移脚本switch_unlogged_tablespace.py，配合GUC参数enable_unlogged_tablespace_compat，可以优化RTO。 | | table_name | 要创建的表名。 | 字符串，需符合[标识符命名规范](https://support.huaweicloud.com/sqlreference-dws/dws_06_0007.html#ZH-CN_TOPIC_0000001811634529__section1475018612353)。 | | column_name | 新表中要创建的字段名。 | 字符串，需符合[标识符命名规范](https://support.huaweicloud.com/sqlreference-dws/dws_06_0007.html#ZH-CN_TOPIC_0000001811634529__section1475018612353)。 | | WITH ( storage_parameter \[= value\] \[, ... \] ) | WITH子句设置表或其索引的存储选项。 | 参数的详细说明如[表2]所示。 | | COMPRESS \| NOCOMPRESS | - 指定关键字COMPRESS，对表进行批量插入时就会触发压缩特性。该特性会在页范围内扫描所有元组数据，生成字典、压缩元组数据并进行存储。 - 指定关键字NOCOMPRESS则不对表进行压缩。 缺省值：NOCOMPRESS，即不对元组数据进行压缩 | - | | DISTRIBUTE BY | 指定表如何在节点之间分布或者复制。 - REPLICATION：表的每一行存储在所有数据节点( DN )中，即每个数据节点都有完整的表数据。 - ROUNDROBIN：表的每一行被依次发送给各个DN，在这种分布策略下可以保证数据分布不会存在倾斜，但是因为数据分布节点是随机的，导致这类表在计算时会更大概率的触发此表的重分布。各列倾斜都比较严重的大表推荐使用此种分布策略。（ROUNDROBIN仅8.1.2及以上版本支持） - HASH (column_name ) ：对指定的列进行Hash，通过映射，把数据分布到指定DN。 默认值：由GUC参数default_distribution_mode控制。DISTRIBUTE BY的默认值选取规则请参考此表下方的须知内容。 | 以下数据类型支持作为分布列： - 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 | | COMMENT \[=\] 'text' | COMMENT子句可以在创建表时指定表注释。 | - | | AS query | 一个SELECT VALUES命令或者一个运行预备好的SELECT或VALUES查询的EXECUTE命令。 | - | | \[ WITH \[ NO \] DATA \] | 创建表时，是否也插入查询到的数据。默认是要数据，选择"NO"参数时，则不要数据。 | - | ![](https://support.huaweicloud.com/sqlreference-dws/public_sys-resources/notice_3.0-zh-cn.png) DISTRIBUTE BY参数说明： - 当指定DISTRIBUTE BY HASH (column_name)参数时，创建主键和唯一索引必须包含"column_name"列。 - 当被参照表指定DISTRIBUTE BY HASH (column_name)参数时，参照表的外键必须包含"column_name"列。 - 当default_distribution_mode=roundrobin时，DISTRIBUTE BY的默认值按如下规则选取： 1. 若建表时包含主键/唯一约束，则选取HASH分布，分布列为主键/唯一约束对应的列。 2. 若建表时不包含主键/唯一约束，则选取ROUNDROBIN分布。 - 当default_distribution_mode=hash时，DISTRIBUTE BY的默认值按如下规则选取： 1. 若建表时包含主键/唯一约束，则选取HASH分布，分布列为主键/唯一约束对应的列。 2. 若建表时不包含主键/唯一约束，但存在数据类型支持作分布列的列，则选取HASH分布，分布列为第一个数据类型支持作分布列的列。 3. 若建表时不包含主键/唯一约束，也不存在数据类型支持作分布列的列，选取ROUNDROBIN分布。 表2storage_parameter参数说明 | **storage_parameter** 参数 | 描述 | 取值范围 | |:---|:---|:---| | FILLFACTOR | 一个表的填充因子（fillfactor）是一个介于10和100之间的百分数。如果指定了较小的填充因子，INSERT操作仅按照填充因子指定的百分率填充表页。每个页上的剩余空间将用于在该页上更新行，这就使得UPDATE有机会在同一页上放置同一条记录的新版本，这比把新版本放置在其他页上更有效。对于一个从不更新的表将填充因子设为100是合适的选择，但是对于频繁更新的表，选择较小的填充因子则更加合适。 （）默认值为100，表示完全填充。 该参数只对行存表有效。 | 10\~100 | | ORIENTATION | 指定表数据的存储方式，即行存方式、列存方式，该参数设置成功后就不再支持修改。 默认值：ROW，即行存方式。 | - ROW，表示表的数据将以行式存储。 行存储适合于OLTP业务，此类型的表上交互事务比较多，一次交互会涉及表中的多个列，用行存查询效率较高。 - COLUMN，表示表的数据将以列式存储。 列存储适合于数据仓库业务，此类型的表上会做大量的汇聚计算，且涉及的列操作较少。 | | COMPRESSION | 指定表数据的压缩级别，它决定了表数据的压缩比以及压缩时间。一般来讲，压缩级别越高，压缩比也越大，压缩时间也越长；反之亦然。实际压缩比取决于加载的表数据的分布特征。 说明： 暂不支持行存表压缩功能。 | 该参数仅列存表支持，有效值为：LOW、MIDDLE或HIGH。默认值为LOW。 | | MAX_BATCHROW | 指定了在数据加载过程中一个存储单元可以容纳记录的最大数目。该参数只对列存表有效。 默认值：60000 | 10000\~60000 | | PARTIAL_CLUSTER_ROWS | 指定了在数据加载过程中进行局部聚簇存储的记录数目。该参数只对列存表有效。 默认值：4,200,000 | 600000\~2147483647 | | enable_delta | 指定了在列存表是否开启delta表。该参数只对列存表有效。 不推荐使用列存带Delta表，因为会出现MERGE处理不及时而导致的磁盘膨胀以及性能劣化等问题。 默认值：off | on或off | | COLVERSION | 指定列存存储格式的版本，支持不同存储格式版本之间的切换。 默认值：2.0 说明： 在建列存表时选择COLVERSION=2.0，相比于1.0存储格式，在以下场景中性能有明显提升： 1. 创建列存宽表场景下，建表时间显著减少。 2. roach备份数据场景下，备份时间显著减少。 3. build、catch up耗时显著减少。 | 取值范围： - 1.0：列存表的每列以一个单独的文件进行存储，文件名以relfilenode.C1.0、relfilenode.C2.0、relfilenode.C3.0等命名。 - 2.0：列存表的每列合并存储在一个文件中，文件名以relfilenode.C1.0命名 | | SKIP_FPI_HINT | 顺序扫描过程中，若需要写FPW（full page writes）日志时，该参数控制是否跳过设置HintBits操作。 默认值：false 说明： 设置SKIP_FPI_HINT=true时，在对某表执行checkpoint操作后，若对该表进行顺序扫描，将不再产生Xlog。适用于查询次数较少的中间表，有效减少Xlog的大小，提升查询性能。 | true或false | #### 示例 创建表customer。 ``` DROP TABLE IF EXISTS customer CASCADE; CREATE TABLE customer ( C_CUSTKEY BIGINT NOT NULL CONSTRAINT C_CUSTKEY_pk PRIMARY KEY , C_NAME VARCHAR(25) , C_ADDRESS VARCHAR(40) , C_NATIONKEY INT NOT NULL CHECK (C_NATIONKEY > 0) ) DISTRIBUTE BY HASH(C_CUSTKEY); ``` 创建表store_returns_t1并插入CUSTOMER表中C_CUSTKEY字段中大于4795的数值。 ``` DROP TABLE IF EXISTS store_returns_t1; CREATE TABLE store_returns_t1 AS SELECT * FROM CUSTOMER WHERE C_CUSTKEY > 4795; ``` 使用store_returns_t1复制一个新表store_returns_t2。 ``` DROP TABLE IF EXISTS store_returns_t2; CREATE TABLE store_returns_t2 AS table store_returns_t1; ``` #### 相关链接 [CREATE TABLE](https://support.huaweicloud.com/sqlreference-dws/dws_06_0177.html)，[SELECT](https://support.huaweicloud.com/sqlreference-dws/dws_06_0238.html)