如何使用CREATETABLEAS创建表和复制源表数据_实时数仓 Hologres(Hologres)-阿里云帮助中心

Hologres从V1.3.21版本开始，支持使用CREATE TABLE AS语句创建表，复制表结构的同时也可以选择复制数据。本文为您介绍在Hologres中CREATE TABLE AS的用法。

背景信息

CREATE TABLE AS的功能为：创建一个与源表结构或者查询Query结果相同的新表，同时支持自动同步源表数据，但不会复制表属性。

CREATE TABLE AS与CREATE TABLE LIKE语法的区别如下，您可以根据实际业务场景选择合适的语法。

对比项	CREATE TABLE AS	CREATE TABLE LIKE（函数）

对比项	CREATE TABLE AS	CREATE TABLE LIKE（函数）
复制表结构（Schema和数据类型）	支持	支持
复制表属性（可空、默认值、索引、主键或注释）	不支持	有限支持
复制源表数据	支持	不支持
复制源表并同时手动设置新属性（索引、主键等）	有限支持，其中主键不支持	有限支持，其中主键不支持
复制分区表结构为非分区表	支持	支持
复制成分区表	不支持	有限支持（通过`partition_clause`手动创建）

使用限制

仅Hologres V1.3.21及以上版本支持CREATE TABLE AS语句。
说明
若您的实例是V1.3.21以下版本，请您使用自助升级或加入Hologres钉钉交流群反馈，详情请参见如何获取更多的在线支持？。
CREATE TABLE AS语句支持复制表结构，不能复制表属性（主键，索引等）。
CREATE TABLE AS语句支持创建新表时自动同步源表数据，但不保证数据导入的原子性。
使用CREATE TABLE AS语句时，如果源表中包含VARCHAR、BPCHAR、NUMERIC（DECIMAL）、BIT、VARBIT类型的列，那么必须手动在SQL语句中显式指定精度，否则会提示报错。
使用CREATE TABLE AS语句时，如果源表中包含INTERVAL、TIME、TIMETZ、TIMESTAMP、TIMESTAMPTZ的列，那么不可以对这些列指定精度，否则会提示报错。
CREATE TABLE AS语句支持从分区主表或分区子表创建新的非分区表，只能复制表结构并同步数据（从分区主表复制，会同步所有子表的数据），不支持复制分区结构（分区键约束、继承关系等）成为一张分区表。
自Hologres V3.0.9版本起，CTAS语句支持使用Serverless Computing资源执行。关于Serverless Computing资源详情，请参见Serverless Computing使用指南。
Hologres V3.0.9版本之前，CTAS在元仓（hologres.hg_query_log）中只有CTAS本身一条记录。自Hologres V3.0.9版本起，CTAS在元仓中会产生两条记录，包括CTAS本身及CTAS过程中产生的INSERT记录。二者可以通过Transaction ID实现关联，关联示例如下：
```
SELECT
    query_id,
    query,
    extended_info
FROM
    hologres.hg_query_log
WHERE
    extended_info ->> 'source_trx' = '<transaction_id>' -- 通过CTAS本身记录的trans_id字段可以取到transaction id
ORDER BY
    query_start
;
```

命令语法

Hologres中CREATE TABLE AS的命令语法如下。

--从一张源表复制一张新表
CREATE TABLE [ IF NOT EXISTS ] <new_table_name> AS TABLE <src_table_name> [ WITH [ NO ] DATA ]

--从select query复制一张新表
CREATE TABLE [ IF NOT EXISTS ] <new_table_name> AS <select_query> [ WITH [ NO ] DATA ]

参数说明

参数	说明

参数	说明
new_table_name	创建表的表名称（不能创建外部表），只支持固定字符串，不支持字符拼接或函数生成。
[ IF NOT EXISTS ]	判断新创建的表是否已存在，若存在则跳过。
src_table_name	源表或视图（VIEW）名称，即要复制的表或视图名称。说明 Hologres从 V2.1.21版本开始，支持将视图（VIEW）作为数据源，即支持复制视图的结构和数据创建表。
select_query	查询的SQL语句串，详情请参见SELECT。
[ WITH [ NO ] DATA ]	指定是否在创建表时自动同步源表数据，含义如下。 `WITH DATA`：自动同步数据 `WITH NO DATA`：不自动同步数据。如果不指定，默认为`WITH DATA`。

使用示例

将非分区表复制成一张新的非分区表。

准备一张源表和数据。

BEGIN;
CREATE TABLE public.src_table (
 "a" int8 NOT NULL,
 "b" text NOT NULL,
PRIMARY KEY (a)
);
CALL SET_TABLE_PROPERTY('public.src_table', 'orientation', 'column');
CALL SET_TABLE_PROPERTY('public.src_table', 'bitmap_columns', 'b');
CALL SET_TABLE_PROPERTY('public.src_table', 'dictionary_encoding_columns', 'b:auto');
CALL SET_TABLE_PROPERTY('public.src_table', 'time_to_live_in_seconds', '3153600000');
CALL SET_TABLE_PROPERTY('public.src_table', 'distribution_key', 'a');
CALL SET_TABLE_PROPERTY('public.src_table', 'storage_format', 'segment');
COMMIT;
INSERT INTO public.src_table VALUES (1,'qaz'),(2,'wsx');

场景1：从源表创建新表，并自动同步数据。

CREATE TABLE public.new_table AS TABLE public.src_table;

执行完成后，查看新表的数据：

SELECT * FROM public.new_table;

-------
a | b
--|-----
1 | qaz
2 | wsx

查看新表的DDL，可以看到，如果源表有主键、not null属性，复制出的新表将不会继承：

--新表的DDL如下：
select hg_dump_script('public.new_table');
-------------------------------------------
BEGIN;
CREATE TABLE public.new_table (
    a int,
    b text
);
CALL set_table_property('public.new_table', 'orientation', 'column');
CALL set_table_property('public.new_table', 'storage_format', 'orc');
CALL set_table_property('public.new_table', 'bitmap_columns', 'b');
CALL set_table_property('public.new_table', 'dictionary_encoding_columns', 'b:auto');
CALL set_table_property('public.new_table', 'time_to_live_in_seconds', '3153600000');
COMMENT ON TABLE public.new_table IS NULL;
END;

场景2：从源表创建新表，并自动同步数据。若表已经存在则跳过，数据也不会同步。

CREATE TABLE IF NOT EXISTS public.new_table AS TABLE public.src_table;

NOTICE: relation "new_table" already exists, skipping

场景3：仅复制表结构，不同步数据。

CREATE TABLE public.new_table AS TABLE public.src_table WITH NO DATA;

场景4：复制新表并同步select查询的数据。

CREATE TABLE public.new_table_2 AS SELECT * FROM public.src_table WHERE a = 1 ;

从分区父表或者子表复制出一张新的非分区表，分区表只能复制成非分区表。

准备一张分区表、分区子表以及表数据。

BEGIN;
CREATE TABLE public.src_table_partitioned (
 "a" int NOT NULL,
 "b" text ,
PRIMARY KEY (a)
) PARTITION BY LIST(a);
CREATE TABLE public.src_table_child1 PARTITION OF public.src_table_partitioned FOR VALUES IN (1);
CREATE TABLE public.src_table_child2 PARTITION OF public.src_table_partitioned FOR VALUES IN (2);
CREATE TABLE public.src_table_child3 PARTITION OF public.src_table_partitioned FOR VALUES IN (3);
COMMIT;

INSERT INTO src_table_child1 VALUES (1,'aaa');
INSERT INTO src_table_child2 VALUES (2,'bbb');
INSERT INTO src_table_child3 VALUES (3,'ccc');

场景1：将分区父表复制成一张非分区表，并同步所有分区的数据。

CREATE TABLE public.new_table_2 AS TABLE public.src_table_partitioned;

查询新表，将会有所有分区的数据，且新表是非分区表：

SELECT * FROM public.new_table_2;
----------------------------------
a | b
--|-----
2 | bbb
1 | aaa
3 | ccc

场景2：复制某个分区子表及其数据到新的非分区表。

--从分区子表创建新表，并自动同步数据，新表只会同步这一张子表的数据
CREATE TABLE public.new_table_3 AS TABLE public.src_table_child1;

从Select Query的结果复制一张新表，同时设置新表属性。

--创建源表
BEGIN;
CREATE TABLE public.src_table (
 "a" int8 NOT NULL,
 "b" text NOT NULL,
PRIMARY KEY (a)
);
CALL SET_TABLE_PROPERTY('public.src_table', 'orientation', 'column');
COMMIT;

--从select query创建新表，并设置新表属性，自动同步数据
BEGIN;
CREATE TABLE public.new_table AS select * from public.src_table;
CALL SET_TABLE_PROPERTY('public.new_table', 'bitmap_columns', 'b');
CALL SET_TABLE_PROPERTY('public.new_table', 'dictionary_encoding_columns', 'b:auto');
CALL SET_TABLE_PROPERTY('public.new_table', 'time_to_live_in_seconds', '3153600');
CALL SET_TABLE_PROPERTY('public.new_table', 'distribution_key', 'a');
COMMIT;