如何使用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以下版本，請您使用自助升級或加入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;