CREATE TABLE AS

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以下版本，請您使用自助升級或加入HologresDingTalk交流群反饋，詳情請參見如何擷取更多的線上支援？。
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;