如何使用CREATETABLEAS创建表和复制源表数据 - 实时数仓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（函数）
复制表结构（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中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;